Financial Model, Online & IT Businesses, SaaS

5-year Financial Projections, Accounting, Annual Recurring Revenue (ARR), Average Revenue per User (ARPU), Budget, Budgeting, Business Valuation, Cap Table, Cash Flow Analysis, Cash Flow Projections, Cash on Cash Yield, Cash-on-Cash Multiple, CFO, Churn, Controlling, Conversion, Cost Projections, Customer Acquisition Costs (CAC), Customer Lifetime Value, Dashboard, DCF Model, Debt Schedule, Debt Service Coverage, Discount Rate, Excel, Financial Feasibility, Financial Model, Financial Modeling, Financial Planning, Financial Projections, Financial Statements, Forecast, Forecasting, Income Statement, Investment Multiple, Investor Cash Flows, Investors, IRR (Internal Rate of Return), KPIs (Key Performance Indicators), Monthly Recurring Revenue (MRR), NPV (Net Present Value), Payback Period, Pro-Forma, Profitability Analysis, Return on Investment (ROI), Revenue Projections, Scenario Analysis, Sensitivity Analysis, Startup Financial Models, Terminal Value, Three Statement Model | Three Financial Statements, Valuation, Venture Capital

 

Video Tutorial:

Updates: Added a fully connected monthly and annual Income Statement, Balance Sheet, and Cash Flow Statement as well as a cap table, dynamic capex, and the ability to collect cash up front or evenly over the life of all contracts.

When trying to model enterprise SaaS companies, the main items that need to be configurable are average contract lengths, the renewal rate on those contracts, the total value of those contracts, and how that can change over time (both in initial value and added value). This financial model takes care of all those assumptions with dynamic inputs for up to 3 types of customers.

Each customer type has its own set of variables, including the start month, and the entire structure for defining customer growth/retention/value.

The actual contract length input for each customer type can be any number (1 would be month-to-month) but the robustness allows for anything term length up to 5 years. The average contract value for each customer type is configurable in each year i.e. in year 1, the user can define that all contracts for tier 1 are worth ‘x’ $ and have an average length of 18 months with 80% retention (out of 10 new customers, 8 will renew their contracts in that case). There is another variable that was built in so that negative churn is possible. It is a simple percentage input that defines how much the average contract value goes up upon each renewal. The base for that will always be the starting contract value (which can be different in each of the five years).

The other primary variable inputs for customers and revenues include two different ways that customers are on-boarded. The first is with an input that defines the amount of first month organically added customers. Organic can mean word of mouth, organic web search, or any other way that is not directly related to ad spend. Then, a percentage that can change each year defines how the amount of organically added customers will grow.

The second way new customers can onboard is through input for ad spend and the corresponding cost per customer acquisition. This is adjustable each year.

Much of the way variables are setup and can change is by defining the percentage improvement each year, rather than just randomly entering different values in each year.

In SaaS organizations, the two main headcount types that often need to scale based on customer count is Customer Service (CS) reps and Sales reps. In this model, there are two types of customer service rep configurations. Their headcount is determined by the total active customers in each customer tier. This allows for variation in the support that is needed for different kinds of customers.

For sales reps, there are also two possible types and their headcount is determined by a ratio related to the number of new customers added in each customer tier.

The fully loaded salaries of all 4 scaled headcount types are configurable and the amounts can change over the 5 years as needed.

The rest of the model is fairly similar to the things I like to wrap around complex revenues. The fixed expenses are setup for plenty of slots in each major operating category. That is General and Administrative, Sales and Marketing, and Research and Development. For all of these categories, the user can define the start month and the monthly fully loaded cost. For the cost of goods sold, there is a definable monthly cost per customer for each customer tier.

A separate section can also determine CapEx and one-time startup costs.

The control tab area lets the user define the start year, exit month, and exit multiple on trailing 12-month sales if applicable. Also, if a p+i loan is needed, that can be planned out as well as investor funding. The model then takes all assumptions into account on the monthly P&L detail and calculates the minimum amount of equity needed to stay afloat over the entire 5-year period.

A DCF analysis was done for the project as a whole and the owner’s equity contribution as well as the investor’s leg if applicable. Other capital return metrics include IRR and equity multiples for all equity sources.

Finally, there are key performance indicators on a monthly and average yearly basis. These KPI’s are shown in visual charts as well. They include: CaC, months to repay CaC, LTV, LTV to CaC, % customer churn, $ churn (potentially can be negative), and average monthly value per account.

Don’t be afraid of decimals in this model. It is perfectly acceptable and most accurate to have partial customers/reps when that is the actual result of the assumptions.