Financial Model and DCF Analysis for Multiple (up to 25) Brick and Mortar Retail Locations
This model was designed so the user can plan out the building / acquisition or development and then operations of up to 25 retail locations opening up over time. There is scaling logic and the structure was meant to allow for fairly efficient financial modeling while still accounting for growth in revenues and costs.
Revenue assumptions are driven off of capacity attainment as you should know your max production i.e. how many burritos can be made and sold in a day or how many items can be dry cleaned a day. Also, you can grow your max capacity per location as well in a separate input area if you know that the max items you can sell per day will go up for whatever reason over time.
Additionally, revenue is defined by up to 3 pricing tiers (items of 3 different average prices) and the amount of total expected sales that fall into each tier. Labor wages are another dynamic assumption tab that lets the user define the starting labor count for each location separately and the growth in labor count per month over the first 12 months followed by a % increase in labor costs per year if applicable (raises / continued expansion of headcount or both).
Cost of goods sold is usually a prevalent metric in any brick and mortar retail location so this is defined as a percentage input for each of the three product categories. To better understand the initial equity investment that is required, prepaid inventory at launch was built as an input. This is because at launch it is likely that you will have a starting inventory amount ready to go and then future inventory should follow the same pattern i.e. you can define new inventory purchases happen every ‘x’ months and that will get accounted for in the final monthly cash flow against the COGS amount.
Fixed operating expenses like managers / utilities / legal / other overhead items are defined by their start month and cost amount per month in each of the five years with up to 113 slots.
The model drives all the way down to EBITDA, EBIT, and Net Income after tax as well as cash flow. There is a DCF Analysis showing on a project level as well as for investors (if applicable) and owners depending on how the required investment was funded. Logic is tied to all of this. Also, there is an executive summary that shows high level items from the pro forma detail tabs on an annual basis. Plenty of visualizations were built-in as well.