Video Tutorial:This 10-year model was designed for quick analysis when scaling into 12 franchise locations or less. There is a single assumptions tab that lets the user drive everything from one spot. Much of the operating expenses were designed as a top-down style percentage of revenue. With this type of build-up, there are timing assumptions that must be accounted for (such as signing agreements / construction). All of those things are accounted for dynamically.
On the assumptions tab, the user defines the scaling strategy for up to 12 locations. For each location, there is an input for:
Date franchise agreement signed
Initial franchise fee
Cost to Develop
Occupancy Cost - monthly
Occupancy Start Month
Daily # of Sales
Monthly # of Sales (auto-populated)
Avg. Ticket Price
Starting Year Average Revenue
Estimated Annual Growth
Months of Growth Before Stabilizing
As far as the remaining inputs go, the following are defined:
Cost of Goods Sold (%)
Bank & CC Fees (%)
Royalty Fee (%)
Unit Opens (revenue starts)
Interest Only Rate
Field for Other Fixed Costs per Unit
Insurance or Other Fixed Cost per Unit
The employee headcount and fully loaded salaries are defined in their own schedule. Below this, there is an input for the amount of initial investment per new location that is financed through debt vs. equity and loan terms for the debt portion.
All of the assumptions come together on the same 10-year timeline and are shown on a granular basis per location and in aggregate for the monthly and annual pro forma and cash flow. This model dives down to cash requirement (per the total equity needed) and accumulates the total cash flow that must be contributed by the owner over time.
Return analysis includes IRR and ROI for the owner as well as the net cash returned per year as a 3D bar chart visualization.