This model has got a lot of bells and whistles. It starts off with assumptions about potential debt, investor equity, and owner equity. Owner equity is a calculation that is determined by the actual cash flow resulting from the assumptions. You can also define the month of exit and an exit valuation multiple.
You will see that the revenue assumptions below allow for MRR expansion and negative churn calculations to be done for each month.
The revenue assumptions start off by defining the start month of revenue and the number of locations added per month in each of the 5 years. It then gets into the 3 subscription services for monitoring services. Since you can have some locations with more advanced services and add-ons, the best way to build a model for this is to simply allow for up to 3 pricing tiers.
1. % of new locations that sign-up for a given pricing tier.
2. Monthly churn of each tier.
3. % of Installations that are low/mid/high budget.
4. Avg. equipment cost for low/mid/high budget.
5. Avg. installs a tech can do per month for low/mid/high budget installs.
6. Avg. hours per installation per type and the hourly rate paid to techs per the type of install a tech is working on.
7. Margin earned per installation per type.
8. Upsells (tier 1 subscription to tier 2 and tier 2 to tier 3.
9. Downsells (tier 3 to tier 2 and tier 2 to tier 1)
10. Service calls per location per year/hours per service call/charge per service call.
The costs involve defining the margin you make on the monitoring service. i.e. if you are charging $28/month, and you want to make 60% margin on that, then your cost is $11.20 per month. This is how the costs are defined for your monitoring service provider for each pricing tier.
The rest of the fixed monthly costs are defined by the start month, the monthly amount in year 1, and the yearly % increase.
There are also assumptions for credit card fees and income taxes as well as depreciation. I built a ‘depreciation helper’ tab.
There is a monthly and annual detail to show the result of the assumptions and their impact on cash flow.
There is a distribution/contribution summary to show the owner and investors cash requirements and cash returns as well as their IRR, DCF analysis and NPV as well as their equity multiples respectively.
File types: MS Excel – .xlsx