Software as a Service (SaaS) Businesses have experienced quite a boom in the last years due to the technological development and customer demand for flexible outsourced IT applications via cloud-hosted services. Thus especially SaaS businesses are in high favor in the Startup scene and many companies are seeking financing. Thus they will be required to present their investors, partners and co-founders a solid financial model showing how the company can generate revenue and profits in the long-term.
Thus let us explain how to build such a financial model for SaaS businesses on an Excel spreadsheet and which are key topics to be covered in order to come up with a solid SaaS financial model serving as basis for the business plan presented to investors.
This article will go through each topic of relevance to SaaS businesses and explains how to construct a simple, easy to use financial model for a SaaS business. The full Excel model template is available here.
- Subscription Packages / Pricing
- Payment Options
- Churn Rates
- Monthly Recurring Revenues (MRR)
- Customer Lifetime Value
- Customer Acquisition Costs
- Operating Costs (OPEX)
- Net Working Capital
- Cash Flows
- Software Development Costs
- Fixed Assets
- Payback Period
- Internal Rate of Return (IRR)
- Yearly Financials
A SaaS business is driven by the number of subscribers as the software is sold as a service through subscriptions. So we have to prepare a financial plan most likely on a monthly basis where we project how many new subscribers can be added as new customers to the business per month. Obviously, there will be only a few at the beginning and more monthly additions later on, once the business gets bigger. For our modeling purposes, we keep the assumptions simple and take a view on how many new subscribers can be obtained per month for the first five years.
So we use above assumptions and plot them on a monthly operational plan. We leave another row empty to count for existing subscribers who choose to renew their subscriptions later on. Thus this allows us to know how many subscriptions can be sold in which month. If more precision is needed, the assumptions could be finetuned further by e.g. increasing the new subscribers steadily on a monthly basis or vary them as we like. For our purposes and to keep it simple, we just go with above assumptions.
Most SaaS businesses in practice offer minimum three different subscription packages in order to attract different buyer segments. So we can input the planned pricing of the subscription packages. We also need assumptions what will be the expected % split of packages purchased by the company’s customers. We do that by taking an educated guess of Basic, Standard and Premium packages requested from similar businesses.
The % package split purchased will then also go into the monthly financial projections used to calculate the number of the different packages sold for each month. Also here the % split assumption could be varied over time if more precision is needed. For our demonstration purposes, we keep the assumptions as they are. This creates a table where we can easily understand what subscription types will be sold each month and where the subscribers come from (new or from renewal).
Subscription businesses such as SaaS normally face a financing gap as their investment (software development and marketing) mostly is upfront while revenues arrive later on. For this reason, it makes a lot of sense for SaaS companies to ask their customers for advance payments of up to 1 year (or even more) by providing attractive discounts in order to motivate their customers to subscribe on a long-term basis. Advance payments are thus a key tool to close the financing gap for SaaS businesses.
Our financial model allows to count for prepayment discounts, and we simply can input how much discount we are willing to give for a 1 year, 6 month or 3 months subscription period instead of a monthly subscription. Now all we need is to take a view on the expected payment preferences made by our customers and estimate the % breakdown of the subscription periods requested by those customers. Thus we assume a % split of subscriptions paid as yearly, 6 months, 3 months and monthly subscriptions and the result will be that we know how much discounts we need to give to motivate our customers to subscribe to us.
Using above assumptions allows us to project the estimated number of subscription sold broken down for each payment option. The total will correspond again with the total of subscriptions sold in that month.
We can even be more precise now and provide a more detailed breakdown how many of the Basic, Standard and Premium Subscriptions are sold under which payment method. This will allow us, later on to attribute different prices for each combination of subscription / payment option and calculate the expected monthly revenues.
While the calculation might not hold for each and every month, it can serve as a benchmark to compare effective results to. While we go along with the rollout of the business, we thus can steadily check our actual results to improve the accuracy of our assumptions in order to come up with an updated model version. Also, the model structure helps us to ask the right questions and forces us to think about new ways to move people from Basic to Premium subscriptions and motivate them to go for longer subscription periods.
The last factor we need to know when calculating the number of monthly subscribers, is the churn factor – the % of existing subscribers who will get lost as customers might decide not to renew the subscription for whatever reasons: E.g. they might be unhappy with the service or the pricing; the customer might need a different service or there might be other reasons which should be explored to better understand the decision-making process of the customers.
Thus we need to take a view on the churn factor (30%) which will then also give us the % of existing subscribers who will choose to renew their subscriptions with us (70%). The churn rate is an important factor which needs to be tracked going forward for a SaaS business as obviously, the goal will then be to find new ways to minimize churn.
We now can simply calculate how many of the expired subscription will renew (70% of expired subscriptions assumed) and how many will be lost due churn (30% of expired subscriptions). Subscriptions to be renewed we can add again to the new subscribers as we have discussed before. This way the financial model will give us all the data we need to know at which time how many subscribers we have, where they come from (new or renewal), which type of subscription packages are sold and how many discounts will need to be given.
We now use the assumptions from before and multiply the following factors to calculate the monthly revenues:
- number of subscribers added- as per their chosen subscription packages – by
- the order values of their subscriptions by
- the period length (the number of months subscribed) and
- the discounts given due paying in advance for longer time periods
To keep it simple we book the revenues as they are received without further accounting consideration as we are only interested to understand the cash flow implications of our business.
Recurring revenues provide a steady cash flow stream to the business. To investors recurring revenues are very valuable as they exclude one-time effects and give a more stable basis for their expectations how the business will perform so that they can base an offer to invest on MRR figures. Thus SaaS companies will need to focus on the Monthly Recurring Revenues (MRR) and build an investment case, where they demonstrate that in a certain period of time MRR will exceed the monthly costs and thus the business will become profitable so that investors can be exit at some point.
For financial modeling purposes, we calculate MRR by taking the new monthly subscriptions sold and divide them by the subscription period (measured in months). Afterwards, we calculate the total of the monthly equivalent amount of subscriptions sold (new MRR). MRR now simply is a function of the MRR of the previous month plus new MRRs obtained from subscriptions sold, and MRRs deducted as they expire.
Another interesting concept for SaaS businesses is the Customer Lifetime Value – corresponding to the total revenues or gross profit received from an average customer during his total lifetime. Why this metric is so important? – The reason is that we need to ensure that whatever we spend in acquiring a new customer is less than the Customer Lifetime Value less the operating costs this customer will create.
In order to calculate Customer Lifetime Value, we calculate the weighted average package price by multiplying
- the % breakdown of purchased packages sold by
- the different subscription packages and
- the payment discounts we need to give (see before).
Next, we need to calculate the weighted average number of subscribed months. Afterwards we use the renewal % assumption to calculate the length for each subsequent subscription period on average. We cap the calculation at 20 time periods as afterwards the incremental factor gets minimal. So we conclude that on average a subscriber will use the service for ca. 12 months as per the example model assumptions shown below.
Customer Lifetime Value can be calculated on a basis of revenues or also of Gross Profit by deducting the applicable direct costs (direct costs per month multiplied by the average subscription period.
Customer Acquisition Costs are the costs incurred to gain a new paying customer. So Customer Acquisition Costs are a function of the costs per lead and the average conversion rate (from lead to paying customer). In our example, a Google Adwords campaign costs USD 0.50 per click (Cost per Click – CPC) at 5% conversion rate, which equals USD 10 Customer Acquisition Costs. Customer Acquisition Costs do highly depend on the type of business. Some businesses need months of lead development time, maybe even requiring sales representatives to call the customers by phone or visit them in person, which can lead to very high customer acquisition costs. On the opposite online marketing campaigns via Google Adwords or Facebook Ads, today offer cheaper alternatives but those lead generation methods might only work for certain types of businesses.
For financial modeling purposes, we keep the assumption simple as we directly enter the assumption of the expected costs needed to acquire a new customer.
If there are any other direct attributable costs, we also need to include them in the Cost of Goods Sold (COGS). By deducting the Customer Acquisition Costs and the Direct Costs from Revenues, we can calculate Gross Profit.
In order to calculate operating profits, we need to estimate the ongoing operating costs (OPEX) of the SaaS business. These normally include employee expenses and all other operating costs such as webhosting, marketing, office rent, administrative expenses, support, etc.
We now project the number of required employees and their salaries for each period of the business. We can simply add a hiring plan to our assumption sheet.
Furthermore, we need to estimate the monthly salary estimations for the main employee categories to be able to estimate employee expenses.
Other operating Costs
For the other non-personnel expenses we enter the yearly expenses in our assumption sheet and simply divide the yearly amounts by 12 to get the monthly operating costs.
If wished so, monthly expenses now can be adjusted for inflation by multiplying with the cost inflation factor.
We know have all estimations we need to project Earnings before Interest, Taxes, Depreciation and Amortization (EBITDA).
Depending on the type of SaaS Business, there might also be net working capital items (Receivables, Inventory, Payables, Other Current Assets, Other Current Liabilities, etc. In case no working capital is needed, simply put the assumptions to zero and skip this section.
In the case you need working capital items, the simplest way to estimate Net Working Capital is to use Days Receivables, Days Inventory, and Days Payables assumptions to project the working capital positions. The formulas are as provided below:
- Receivables = Days Receivables * Revenues / 360.
- Inventory = Days Cost of Goods Sold * Cost of Goods Sold / 360
- Payables = Days Payables * Cost of Goods Sold / 360
If you want to project the net working capital positions on a monthly basis, simply divide the Revenues and Cost of Goods Sold by 12. This allows you to project the Balance Sheet and more importantly the Cash position on a monthly basis.
We want to track the monthly cash position as we need to ensure we raise sufficient financing to execute the business plan and use the model to simulate different scenarios which will impact the cash position. Therefore we need to build a detailed Cash Flow Statement for our financial model. Cash Flows from Operating Activities (CFO) are calculated by adding Interest, Depreciation & Amortization, Net Income and the change in the Net Working Capital positions of the Balance Sheet.
Cash Flow from Investment (CFI) activities include Software Development Costs and capital expenditures (CAPEX) related to Fixed Assets. Those are normally estimated on a yearly basis and for our modeling purposes, we need to allocate them on a monthly basis.
Software Development is an ongoing task, so we count for monthly cash expenses. CAPEX are related to purchases of hardware, PC, furniture, etc. which we assume to occur at the beginning of the year in order to be conservative.
Now the only missing section for the cash flow statement is the Cash Flow from Financing Activities (CFF). For new SaaS businesses, these will be mostly equity financing.
For SaaS businesses, a lot of effort is spent to develop and enhance the product which is a software. Thus from an accounting perspective, Software Development Costs either can be expensed or – depending on accounting rules – activated on the balance sheet. If development costs are activated, it provides a tax shield later on when more revenues occur and thus taxable income can be lowered.
In our example, we assume that a prototype has been built which today costs nearly next to nothing by relying on open source software and developing under the lean startup method (early prototype to be continuously tested by the market so the product can be improved). Software development costs thus can get more expensive once the source code needs to be rewritten and finetuned to optimize processes and the quality of the service. Thus in our example, USD 120k software development expenses per year are assumed. Below table allows to calculate the yearly amortization expenses and the intangible net asset position which goes into the balance sheet.
Other fixed assets which are created by capital expenditures (CAPEX) use a very similar depreciation schedule as software development costs. Instead of amortization, depreciation expenses are created (non-cash items). Thus we simply use a similar schedule to calculate depreciation. For SaaS businesses, CAPEX normally consists of furniture items, maybe some hardware items such as PCs, Network equipment or even webservers. However, today the need to invest in hardware got reduced as there are many providers which offer virtual servers, thus eliminating the need to invest and operate own server farms.
So we have calculated two more positions we need to build the balance sheet, Development Costs, and Fixed Assets. One can either build the Balance Sheet for Yearly Financials only or if more precision is needed can just add monthly Software Development Cost and CAPEX and deduct monthly amortization / depreciation expenses to calculate the respective balance sheet positions.
The next component needed to build the balance sheet is to include the capital raised (debt and equity). For a Startup it will be mostly equity financing as banks only lend towards more established companies with a track record and which can offer some form of hard assets to be liquidated in case of bankruptcy.
So the question is how much equity is needed to get the SaaS company going. For this purpose we need to figure out what is the missing cash position to cover all expenses, investments into software development and CAPEX by also taking into account first revenues received along the way. For this purpose, we calculate on a monthly basis the Free Cash Flows to Firm, (FCFF) which is calculated by taking the cash profit (EBIT + addback Depreciation & Amortization), the change in Net Working Capital (NWC) and the investment in Software Development Costs and CAPEX. We now add each monthly loss to the loss of previous months and figure out which is the highest cumulative loss in Free Cash Flows over the first 2 to 5 years.
In our example, the minimum cumulative Free Cash Flow gets USD 922k within 2 years. This balance could get more negative if one invests in growing the business further later on but in order to get going for the first two years, we just use the USD 922k as the funding required and will use this as the required equity financing as shown on the balance sheet.
Payback period is a tool to evaluate the financial attractiveness of a project from an investment point of view. We want to know by when our investment is being repaid through organic cash flows. Thus we calculate the Free Cash Flows to Firm and measure by when the cumulated Free Cash Flow to Firm become positive, below example results in month 46.
Another important metric is the IRR which can also be used to evaluate the attractiveness of a proposed investment. For this, we need the yearly Free Cash Flows and work back which discount rate is needed to make NPV = Zero, this will be the Project IRR.
SaaS companies want to know by when they reach breakeven, meaning by when their monthly revenues will cover their monthly costs. We thus focus on cash profits (EBITDA) and want to know from which month onward EBITDA will become positive. Below example shows that within a year first profitability can be achieved.
Once we have the monthly financials ready, we can build the full yearly financial statements. Thus we simply add the twelve month of each year to form the yearly revenues and expenses, creating the Income Statement item by item.
The Yearly Balance Sheet is a function of taking the projected balance sheet position of the last month of the year.
The yearly cash flow statement can be built by adding the monthly cash flows and checking back if the resulting cash generation matches the change of cash on the balance sheet.
One we have the yearly financial statements ready, we can also calculate certain financial ratios we want to keep track of.
Finally, as we have now have calculated the monthly and yearly financials we can summarize the business plan through several charts.
- Development of Revenues and EBITDA
- Monthly EBITDA indicates Breakeven
- Monthly Recurring Revenues and number of subscribers
- Cumulative Free Cash Flows showing the month of Payback
More analysis can be performed with above model when going into scenario and sensitivity analysis. Above description now should have given you an idea how a simple SaaS financial model can be built. The full Excel model template is available here. We hope you enjoyed and please leave your comments below.