Scaling Up to 25 Retail Locations: Financial Model (Excel XLSX)

This is one of my favorite capacity-based models. It was built to account for opening brick-and-mortar locations (acquisition or new construction) and then selling things over time.

Model up to 25 locations over time and see the cash flow requirements and returns of that activity over the first 60 months. The idea is to get to a point of stabilization in this time. For each location, the user can define up to three ticket items, the average price per item, the average percentage of purchases that fall into each of the three categories, and the starting sales count capacity per day.

Then, for each location you can define the percentage of max capacity achieved in unit sales and on a global level you can increase the initial capacity as well if needed. Each location has its own labor buildout, wages, and cost of goods sold average percentage.

Inventory can be purchased at some amount of months ahead of time (input the month #) and this will drive cash requirements based on the amount of expected sales (and cost of goods sold therein) so you get a more accurate cash flow analysis.

Final output reports include an annual Executive Summary, DCF Analysis, and monthly / annual pro forma that drives down into each assumption detail and how that translates to EBITDA and cash flow.

The main purpose of this model is to forecast the financial requirements of opening retail storefronts that sell up to three things or can easily have items categorized in up to three categories based on price / sales volumes and growth over time.

I added an option to fund the minimum investment required based on the minimum cash position over time with debt and equity. The resulting amortization schedule will automatically populate into the monthly cash flow detail.

You can account for inside and outside investors as well as the resulting project-level IRR, investor IRR, and operator IRR and NPVs. All the assumptions have clean input callouts for maximum sensitivity analysis capabilities. Easily adjust various pricing, volume, and cost scenarios to see how the IRR and cash requirements change.