Financial Model, Retail

5-year Financial Projections, Accounting, Average Order Value (AOV), Break-Even, Budget, Business Valuation, Cash Flow Analysis, Cash Flow Projections, Cash on Cash Yield, Cash-on-Cash Multiple, CFO, Consolidation, Controlling, DCF Model, Debt Service Coverage, Discount Rate, Excel, Financial Feasibility, Financial Model, Financial Modeling, Financial Planning, Financial Projections, Financing, Forecast, Forecasting, Inventory, Investment Multiple, Investor Cash Flows, Investors, IRR (Internal Rate of Return), Management, NPV (Net Present Value), Private Equity, Pro-Forma, Profitability Analysis, Return on Investment (ROI), Scenario Analysis, Sensitivity Analysis, Startup Financial Models, Terminal Value, Valuation

Video Tutorial:

If a user were looking into acquiring or building retail locations for any kind of category, one of the most important things is planning out the cash flow needs. To do this, the timing of acquisitions, the cost per new location development, and the revenue from existing locations all have to be accounted for. Overlaying all those pieces will give a final cash requirement that is needed.

The complex part about this model is how the logic allows for each location to individually be defined on all of its specific assumptions.

For each of the 25 locations, the user will define:
– Up to 3 average ticket values (a ticket just means order, and it could represent everything from a haircut to a take-out order to furniture sales)
– Average tickets per day
– Percentage of tickets at each average value
– Annual growth following the first 12 months
– Capacity reached in month 1 to 12
– Labor headcount in month 1 to 12
– Start the month of each location
– Active days per month on average

All of the assumptions above have to then flow into a matrix, so there is a reference available to know what active month a given location is at as well as what year it is for a given location. This is because month 36 of the actual model may be month 8 for a given location that starts later or month 20 or what have you, and so it is not possible to use the same reference for the timing of growth on each location. It all has to be dynamic for things to actually scale accurately.

The one thing that is on a global scale is general annual growth after the first 12 months of the life of each location. This would apply to year 2+ in the lifetime of each location, so even though the starting capacity growth is dynamic, the yearly growth after that launch phase will apply across all locations. What a location ends month 12 on for their stabilized revenue will be the basis for the stabilized annual growth rate.

It is assumed the cash required to open/develop/acquire a given location happens in the month prior to the ‘start month’ defined for each. In the first 12 months, there is an option to enter a percentage that defines how many actual ticket sales are achieved relative to the max defined. This is important because if you need to show that it is actually 0 sales for the first ‘x’ months, you can control that with this model all in one spot.

There is a cost of goods sold percentage defined for each of the three ticket values in order to arrive at a gross profit. Labor headcount is completely dynamic for each location, including headcount over time, fully loaded average hourly pay rate, and average hours per week.

Inventory was also another complex structure. Some retail businesses may need to order for ‘x’ months in advance, while others may do it just in time. The logic in this model will assume that all cash going out for the inventory happens 1 month prior to when it is needed. However, the user can define how many months of inventory will be purchased each time. So, based on the expected sales and the resulting cost of goods sold, the cash needed for inventory for the next ‘x’ months will go out in month x-1. You can set this to ‘1’, and that just means the cash will go out 1 month before the resulting inventory is used. It could be ‘5’, and in that case, you are buying the inventory in month n-1 for month n to n+4.

If the business plan doesn’t require inventory, then the user can simply enter 0% for the cost of goods sold input, and all of that will zero out.

I put over 100 slots for the next section. It is fixed expenses. The user will define the start month and monthly expenses for all relative operating expenses here. These would not include the labor overhead or cost of goods sold that has already been defined.

After all of those assumptions, we are down to EBITDA. I did put in logic for depreciation if needed and EBIT as well as EBT, taxes, and Earnings after Taxes. As always, there is the logic for debt, investor funding, owner/operator funding, and the potential for an exit at a given month. Anything that is not applicable can be zeroed out. The high-level summary shows a DCF analysis for each party and for the project as a whole. IRR, ROI, and equity multiple are also shown for each.

Over 15 visuals were included so that the result of the dynamic scaling assumptions could be easily seen.

Be sure to read all the notes, so it is easy to follow the more complex logic.

One of the most common uses of this type of model is to test various pricing levels to see what is actually possible given a set of assumptions.