SaaS Financial Model Excel Template: 3 Pricing Tiers + Actuals Opt-in by Henry Sheykin - Financial Models https://finmodelslab.com/ Last version published: 13/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, Currency and Denomination Time Series tab to define the starting Year and ending Month. Fiscal Year end in the 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 and Denominator 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 upto-date 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 and Revenue Launch
5
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 the deals through the 3tier conversion. From visitors to sales opportunities, from sales opportunities to engaged sales opportunities and finally from engaged sales opportunities to deals. Finally, the total deals should be allocated to 3 products. The model has an option to shift the revenue generation launch. Use Revenue Launch Date when revenue should start later than the expenses.
Seasonality Setup
Here at this tab, you may spread the marketing budget across the 12 months. So the seasonality factors will be applied to marketing budget assumptions and in this way, the new customers' acquisition will be affected. The 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.
6
Pricing & billing inputs
The subscription pricing covers one contract length: monthly. This means all your subscribers will be billed once a month. Using Upsells you can increase the regular price for $$$ applied to some % of your customers.
7
Customers Churn & Upgrade Setup
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 product by years. Additionally, you have the feature to upgrade/downgrade some portion of your customers within each offering. Use a percentage of customers movement between products to forecast customer upgrade from one offering to another.
8
Existing Customers Setup
9
You may input the historical stats broken down by offerings.
The model will interweave entered data to forecast the behavior of old customers as well as new custom acquired.
Cost Of Services Sold
The model is built to be easy to use to enter expenses. Simply 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.
10
Valuation
Enter in the list of variable expenses you want to forecast in the rows Variable expense There are 3 sections: • as % from Total Revenue, € • as € per Active Customer • as € per New Customer 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
11
Fixed Expenses
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 a One-time, daily, weekly, bi-weekly, monthly, quarterly and annually. This gives you the flexibility to manage expenses.
12
Wages
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.
13
Dashboard - Debt
14
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).
Assets
You may purchase some assets on a specific date and depreciate it across typed in years using this tab
15
Cap Table
16
Cap Table
17
Cap tables require some manual manipulation to make accurate The structure enables you necessary modifications.
to
make
the
The Cap Table model takes you through multiple rounds of financings Showing how issued shares to new investors and cost per share impact the investment received. After each round, you will see the ownership, % changes. This is how dilution works.
Valuation
The model can do valuation of distribution of current cash flow to Investors - Discounted Cash Flow, and valuation based Perpetuity Growth method.
18
Assets
You may purchase some assets on a specific date and depreciate it across typed in years using this tab
19
Reports: Dashboard 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. Basically, it summarizes the reports and gives us a profit and loss statement. The table in the left lower part is called Core Financials. 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).
20
Reports: Burn
21
Burn tab is calculated as average cash balance divided by monthly average operating expenses to give you for reporting and planning. Burn ratio shows how much time operating expenses will burn an average cash balance.
Reports: SaaS Charts
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, - Average CAC (Customer Acquisition Cost) and - ARPU (Average Revenue Per User), as well as Lifetime value/months All these graphs are for the first 2 years and for all 5 years by month.
22
Reports: Financial Charts
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.
23
Reports: Customers Charts
On this tab, you can track customers count broken down by offerings as well as overall customers movement (lost, new, active).
24
Reports: Top Revenue/Expenses
25
Top Revenue - Here you can track how much money each offering generates. Top Expenses - Here you can track the biggest 4 expense categories ranked as a sum of 5 years and the "other" which is the rest of the expenses.
Reports: Break Even Analysis
Here you can track when your company is supposed to move to a whole new level. When its overall revenues become significantly bigger than expenses.
26
Reports: CapTable Prints
Angel Round CapTable - The ANGEL round captable outputs are shown there. Seed Round CapTable - The SEED round captable outputs are shown there. CapTable Print - The total outputs about capitalization table are shown there.
27
Formatting and color scheme Formatting and color scheme will unlock the door of productive function management! Colors show which table cells are open for editing and which are unavailable for the user. Yellow cells are all yours to edit, but the white and grey ones are not. The pages also have a certain color scheme: •
Blue – reports
•
Grey – calculations
•
Green – instructions (i.e. ‘read me first’)
•
White – cover page
Green tabs include Contents, Info, How To and Changelog sections. Contents describe each page and contain a link to it. If you want to go back to the table of contents, click Go to Table of Contents in the left. This way, you can easily navigate the file. Info is the tool general description. Here you can find whom to address in case you have questions. How to is a mini-guide on how to complete your restaurant financial model.
28
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