Freemium Business Model SaaS Excel Template by Henry Sheykin - Financial Models https://finmodelslab.com/ Last version published: 08/05/2019
COVER PAGE
The cover page is the name of your business project, company or Food Truck name. This name is put in a yellow cell and appears on every file page afterward.
2
Time Series Go to the TimeSeries tab Time Series tab to define the starting Year and ending Month. Fiscal Year end in the SaaS business model input the last month of the Fiscal Year. If you don't need this, simply type in Dec. Initial Fiscal Year in the business model - please, choose your first Fiscal Year. If you have chosen Dec in the Fiscal Year End than your fiscal year corresponds to the calendar year.
3
Dashboard - Currencies
Input Currency - currency for the assumptions and inputs. Insert your currency sign $/€/¥/£ or type in USD/EUR/GBP or any other abbreviation. Output Currency - currency for the outputs and graphs. Insert your currency sign $/€/¥/£ or type in USD/EUR/GBP or any other abbreviation. Currency exchange rate - enter your up-todate exchange rate of the currency in which you want to display your financial report. Denomination - use denomination if you want to show outputs in thousands, millions or billions
4
Dashboard - Customer Acquisition The model assumes that to acquire every new deal you are going to spend a marketing budget. The model has two acquisition channels: offline and online. In both sections, you can type in Monthly Marketing Budget and Cost Per Visit to estimate Visitors The model assumes that visits should be converted into Sales Opportunities. Next, Sales Opportunities should be converted to Freemium users and Paid customers Next, Freemium users should be converted into Paid customers
5
Dashboard - Deals Allocation
At this table, you may input the name for each offering, and the allocation of new customers between each offering. Additionally, you should enter allocation for monthly and annual contract length for each offering. In this way, you will set up new customer acquisitions for each contract types and offerings.
6
Pricing & billing inputs
7
Go to the Inputs tab At the Customer Pricing by offerings section, you may enter Prices for each tier. The SaaS subscription model pricing covers two contract length: monthly and annually. This means all your subscribers in a particular tier can be split by monthly and annual billings. Monthly contracts will be renewed and booked every month and annual every 12 months.
Upsell & Setup Fees
8
At the Upsell, Set up fee and revenue terms sections, you may enter assumptions which will affect your cash flow and overall profitability. Using Upsells you can increase the regular subscription price for $$$ applied to some % of your customers. In addition, you may charge a one-time fee (setup fee) at the time of customer onboarding. By entering revenue collection delay terms you can set up a payment delay for a month or more from your subscribers.
Advertising revenue
9
Go to the Inputs tab At the Advertisement Revenue Assumptions section, you may forecast Ad impressions per freemium user and add setup extra revenue stream from an advertisement by entering Cost per 1000 Impressions (CPM), $
Conversion From Freemium to Paying Customers
Go to the CustMnt tab Here you should enter a very important ratio of freemium user behavior. You have an option to enter conversion from free to paid users within 12 months.
10
Customers Churn
11
Go to the CustMnt tab The model takes into account the lost customers using churn rate typed in here. On this sheet, you may input the average monthly churn rate for each offering by years.
Customer Movement & Upgrade/Downgrade Setup
12
Go to the CustMnt tab In the Customer Movement section, you have the feature to upgrade/downgrade some portion of your customers within each offering. Use a percentage of customers movement between tiers to forecast customer migration from one tier to another. The entered percentage will be applied every month to the customer base for each offering.
Dashboard - Debt
13
Go to the Dashboard tab There are 3 debt lines: either choose one or go for all three. Put the amount of money, select launch date and repayment term, interest rate and debt type. There are two debt types: â–Ť
annuity (paid back in installments of the same size consisting of a loan repayment portion and an interest portion) and
â–Ť
usual (the same amount of money is paid for the debt principal, and the interest goes down every month).
In addition, there is also a grant or non-repayable funding. Usually, it is given by a government department.
Seasonality Setup
Here at this tab, you may spread the marketing budget across the 12 months. An entered allocation will be applied to all of 5 years in the model. Enter no more than 100% in total. You have a check feature to the right of seasonality inputs.
14
Wages
15
Go to the Wages tab The wages tab allows to easily calculate salaries for staff categories. Use 1 line per employee/category, and simply type in the hire date, fire date, and annual salary. Next, use the staff numbers section to type in FTE count for each year individually. An increase of FTEs means that you are going to hire additional FTE's. In case of decrease FTEs means you fired some FTEs. New FTEs will be hired/fired on Jan of the relevant year. If there are raises or changes in salaries, simply type in the annual Salary raise per position in the appropriate columns. The model will automatically calculate this cost going forward until the fire date. Finally, Monthly bonus as a % of monthly salary can be applied to every line as well as personal tax can be
Cost Of Services Sold
Go to the COSS tab The model is built to be easy to use to enter expenses. Enter in the list of expenses you want to forecast in the rows COSS expense. (i.e. "cost of selling the services that earn revenue"). And type in the percentage of revenue to be spent in the specific year.
16
Variable Expenses Go to the V_Expenses tab Enter in the list of variable expenses you want to forecast in the rows Variable expense. You have 3 different ways to manage your variable expenses: • As % of Total Revenues - when you pay % percent from all revenues as an expense • As $ per Active Customer - when you pay X dollars per each active client • As $ per New Customer - when you pay X dollar per each new client Customer Acquisition Cost is grey and not allowed to change. This is the expense calculated by the model based on the marketing assumptions you have entered on the Dashboard
17
Fixed Expenses
Go to the F_Expenses tab Enter in the list of fixed expenses (i.e. ""running the business"") you want to forecast in the rows Fixed expense. You have multiple options of when to start to accrue the expense and when to end accruing. You may define periodicity of the accrue for each expense. It could be One-time, daily, weekly, biweekly, monthly, quarterly and etc.. This gives you the flexibility to manage expenses.
18
Book Assets
Go to the Assets tab You may purchase some assets on a specific date and depreciate them across the entered amount of years using this tab.
19
CapTable Inputs
20
CapTable Inputs
21
Go to the CapTable tab At this tab firstly at the top, you may see four tables. In the first table, you have to input the name of shareholders, the amount of common stock, a common option for employees and the unissued employee stock options plan. In the following three tables, you have to input the name of investors, the amount of ESOP increase (Pre-funding), pre-money valuation, amount of investments and select the round close date for Angel Round, Seed Round, and Series A. Below you can see detailed information for each round of investment.
Reports - Dashboard
22
Reports - Dashboard
23
Go to the Dashboard tab Here you can see 4 charts and a table. The left upper chart gives you an active customers breakdown by offerings. The right upper one shows your EBITDA, yearly profitability and EBITDA percent. Left lower chart is for cash flow, which includes operating, investing and financing categories. Net Cash Flow is highlighted in green color. Right lower one depicts cumulative cash flow. The only difference from the ordinary cash flow is that it has an accumulative function starting from the first year and ending with the 5th one. The table in the left lower part is called Core Financials. Basically, it summarizes the reports and gives us a profit and loss statement. Last two rows in the table are from the cash flow chart. They show operating cash flow and cash (the amount of money left on your bank account).
Operational Charts
24
At this tab, you can track some internal operational indexes that can be useful for analysis of the internal activity of the hospital.
Financial Statements Summary
25
Go to the Summary tab The Summary sheet is a Summary of the key financial reports of the model. Moreover, you have the options to select the year to see detailed outputs. The summary presents 3 core reports: - profit and loss - balance sheet - cash flow You can review them in different ways: by each month of each year or check out 5 years altogether in the left upper table. Of course, all the reports are designed for printing in PDF. Just press CTRL+P or Print, and get the
Top Revenue
Go to the Top_Revenue tab Here you can track how much money each offering generates.
26
Top Expenses
27
Go to the Top_Expenses tab Here you can track the biggest 4 expense categories ranked as a sum of 5 years and the "other" which is the rest
Break Even Analysis
Go to the BE tab Here you can track when your company is supposed to move to a whole new level.
28
Valuation
Go to the Valuation tab The model can do the valuation of distribution of current cash flow to Investors Discounted Cash Flow method.
29
DCF sensitivity analysis Go to the DCF tab Discounted Cash sensitivity analysis.
Flow
The model will automatically generate 14 scenarios to forecast Enterprise value based on two factors assumed: • WACC • Multiplicator In the center of the table, you will get highlighted result of the Net Present Value calculated with the assumptions entered in the model. The same approach is used to generate 14 scenarios assuming for terminal perpetuity growth rate factor.
30
Negotiation Go to the Negotiation tab With the help of this tab, you may prepare your negotiations with the investor. Here you may enter investor's expectations of the ROI, %, amount, and month of investment to see what will be required a future value for the investor. Moreover, it shows you the investor's shares % in the future value of the company Knowing this you may negotiate the shares to be sold to the investor today..
31
Cash Burn Ratio
Burn tab calculates an average cash balance divided by monthly average operating expenses. Burn ratio shows how much time (months) needed to burn out a cash balance (average) spending on operating expenses.
32
SaaS Metrics
33
SaaS Metrics
34 The Key SAAS Metrics tab shows the break down of: ▫
MRR (Monthly Recurring Revenue) by offerings,
▫
MRR movement which is lost, new and ending MRR,
▫ ▫
Lifetime, month,- Lifetime, value, Average CAC (Customer Acquisition Cost),
▫
Magic number,
▫
ARPU (Average Revenue Per User),
▫
New visitors and new customers,
▫
MRR growth
▫
Quick Ratio and
▫
LTV to CAC ratio
Financial Charts
35
Financial Charts
36
Here you can visually track key financial indicators over the five years period as well as 24 months period. ▫
EBITDA/EBIT shows your company's operational performance
▫
OPERATING CASH FLOWS shows your company's inflows and outflows
▫
PRODUCTIVITY KPIs shows your average revenue per employee and average expenses per employee
▫
REVENUE BREAK DOWN shows you all your revenue streams
▫
CASH BALANCE this is the forecast of cash in hand you will have.
Customers Charts
37
Customer Charts
38
On this tab, you can track customers count broken down by offerings as well as overall customers movement (lost, new, active).
Angel Round CapTable
The ANGEL round captable outputs are shown there.
39
Seed Round CapTable
The SEED round captable outputs are shown there.
40
Seed Round CapTable
The CapTable outputs are shown there.
41
Thank you very much for your interest in my tools! If you enjoyed using my model, please let people know about your experience! Give 5-star feedback here & share this page across social networks If you have any suggestions or ideas of how to make this model more useful, don't hesitate, just send me a PM - Henry Sheykin