How to Create a Flexible Financial Model Based on the Roadmap

The terms “top-down” and “bottom-up” are not limited to market sizing but also apply to financial modeling. It is precisely when transitioning from market assessment to financial modeling that a realistic picture of the SOM (Serviceable Obtainable Market) begins to emerge — the share of the market that the company expects to capture. SOM in market sizing and revenue in a long-term financial model are one and the same, and ultimately, they should align.

During market research, you determine the total number of customers and the amount of money in your industry as a whole and make assumptions about the market share you can capture. The goal of a financial model is to describe the path and costs required to achieve that revenue and market share figure and whether it can be profitable.

The “top-down” approach to financial modeling is focused on manipulating the model to achieve revenue on the SOM level. Usually it means a simple financial model incorporating a growth rate that allows reaching the desired market share in the foreseeable future. To compare aspirations with reality, it’s worth transitioning to a “bottom-up” approach — where calculations start from the resources needed to create and sell the product.

In practice, a combination of these approaches is always used, often through several iterations, until a balance is struck between ambitious market share plans and the reality of achieving them.

It’s essential for market assessment to precede work on the financial model. This way, you have a clear benchmark for modeling. If “bottom-up” planning gives figures far from the estimated potential market share, it’s a signal to devise more dynamic growth strategies. Try different business models, marketing channels, and organizational methods. Market knowledge also guards against forecasting unrealistic numbers — for example, if your model projects 1 million customers in five years, while your target audience size is only 500 000, you will immediately spot the error.

The source of revenue and its growth are the result of specific actions to develop a company, rather than a desired growth percentage that can be simply forecasted. References to industry growth rates alone don’t work; you always need a plan to tap into a promising market.

Two fundamental components without which revenue cannot be generated are the product and its delivery to the customer. In other words, to initiate revenue, you must first invest money and resources into product creation, followed by its promotion and sales. The rate of business revenue growth depends on these expenses and the project’s roadmap.

Here, one can recall the concept of product testing and sales even before the products themselves are created, especially popular in the realm of technological startups. This practice is undeniably useful and has proven its advantages. However, firstly, even for such tests, you need to consider expenses for creating a landing page or MVP (Minimum Viable Product) and a budget for test advertising campaigns. Secondly, in this article, we are discussing a long-term financial model, which implies forecasting the product’s full commercial use. So there are no contradictions, and conducting tests before the full product launch allows you to make the model more accurate by using real figures, such as customer acquisition costs specific to your project.
For a company’s revenue growth, both the product and the process of its sales are significant. Even the best product remains unknown to customers without appropriate promotion. Similarly, excellent marketing with a weak product leads to low conversion and customer retention. In both cases, it results in resources being spent in vain and an inability to turn a profit.

Now, let’s move on to specific financial items that constitute a functioning, revenue-generating business. The costs of creating the product include expenses for its production. In the case of digital products, this primarily falls under the “Research & Development” (R&D) category. It may encompass expenses for in-house or external product teams and developers, acquiring intellectual property rights, and more. In the case of manufacturing, R&D remains an important category, but it involves significantly higher material costs. The “Capital Expenditures” (“CAPEX”) category includes expenses for purchasing equipment, vehicles, land, employee computers, in-house servers, and other property. The product’s direct cost, known as “Cost of Goods Sold” (“COGS”), includes materials and expenses for production, personnel in manufacturing and infrastructure costs for digital products.

The sale of goods is carried out through two main channels depending on the product type and business model — sales and marketing. Initially, you need customers to learn about your product; these are expenses for comprehensive marketing campaigns and PR efforts aimed at brand and product recognition. Afterward, you need to convert this awareness into actual purchases, which can be done through direct marketing and self-service purchasing mechanisms or through a sales department. In this context, our potential revenue will depend directly or indirectly on financial model items such as budgets for various marketing campaigns and the sales department’s payroll.

Moreover, revenue and, more importantly, profit do not grow uniformly. The company invests in product improvement, expansion into new markets, advertising launches, and receives a corresponding response in terms of customer numbers and revenue. Some actions have a greater impact, while others have a lesser one.
It’s worth noting that revenue comprises two components: the number of customers and the prices. In the financial model, we calculate the potential number of customers and their purchases, which are then multiplied by the prices we set for our product.

In this article, we are examining a financial model for a new business, aimed at forecasting potential revenues and expenses for the company, determining the breakeven point, and calculating the required investments. To achieve this, we utilize a “Three-Statement Model,” which forecasts movements in three standard financial reports: the “Profit and Loss” (P&L) statement, the “Cash Flow” statement, and the “Balance Sheet.”

At this stage, we put aside the assessment of the business’s value, investor ROI, and other interesting calculations, which, however, are of little use if the company lacks an understanding of its path to commercial success.

The objective of our model is to link the project’s roadmap, the necessary expenses for its implementation, and the dynamics of customer growth and revenues. We are creating a financial model in Google Sheets (the same algorithm works for Excel) that will be a flexible tool for planning a new business. Therefore, we separate all the input data into individual tabs, and the main forecasts are calculated automatically based on formulas.

Let’s begin with the structure of our financial model file in Google Sheets:

“Setting” Tab
Here, we set the general parameters for the business.
“Roadmap” Tab
We list the events on the business roadmap that will influence the dynamics of the company’s revenues and expenses.
“Payroll” Tab
We input information about salaried employees.
“Projections” Tab
We input the initial data to generate all income and expense streams. The onset of various income and expense items is linked to events on the roadmap, and revenue generation depends on direct expenses leading to sales. The “Monthly forecast” tab is based on the values entered here.
“Monthly Forecast” Tab
Here, we create a detailed financial forecast for the business on a monthly basis.
“P&L | Cash Flow | Balance” Tab
We compile financial statements: the Profit and Loss (P&L) statement, Cash Flow statement, and Balance Sheet on a monthly basis.
“Data for Charts” Tab
This is an auxiliary tab to facilitate the creation of charts for the “Charts” tab.
“Charts” Tab
This tab is for visualizing key business metrics. Charts are generated based on the data from the “Data for Charts” tab.

Here you can buy the template of the financial model described in this article.

This is the first tab of the financial model, and it’s where you specify the general parameters for the business: country, currency, and tax rates. Based on the tax rates provided here, the profit tax is calculated in the “P&L | Cash Flow | Balance” tab, and the payroll tax is calculated in the “Payroll” tab. Additionally, the initial capital contributed by the founders at the start of the business goes into the relevant sections of the “Cash Flow” and “Balance” tabs.

In this section of the financial model, we document the events on the business roadmap that will impact the company’s revenues and expenses. Here, you need to list these events and specify their planned dates. These events can include the launch of new products, expansion into new regions, fundraising, and other events, both major and less significant, that will influence the business.

You can set the dates by indicating how many months after fundraising or another specific event the event will occur. In Google Sheets and Excel, this is implemented using the EDATE function. The events listed in this tab are integrated into drop-down menus on the “Payroll” and “Projections” tabs. This integration allows you to link the introduction of new expenses or income and the hiring of specific employees to events on your roadmap. Depending on the selected event, the “Payroll” and “Projections” tabs will automatically populate with dates from which individual income and expenses originate. These data are then factored into the “Monthly Forecast” tab from the relevant month onwards.

In this part of the financial model, we input job titles, salary amounts, specify hiring dates, and plan for the anticipated annual salary increase percentage. The total employee expenses, including taxes, are calculated based on the rate specified in “Settings,” and the hiring dates are automatically populated from the “Roadmap” based on the selected event.

The approach of integrating the roadmap into the financial model and using simple Google Sheets tools allows us to approach the hiring plan consciously and helps us stay organized with dates. By visualizing events on the roadmap, we understand that, for example, to launch in a new region, we will need employees to make it possible — local marketers and sales managers, support specialists in the required language, and so on.

The data from this tab transition to the “Monthly Forecast” section of the financial model, where they start being factored into personnel expenses from the specified date, taking into account the annual salary increase.

The “Projections” tab serves as the “given” in our financial model task. Here, we gather all our assumptions about how customer numbers will grow, what prices we will set for products, and what operational and capital expenses we will incur during operations.

The general procedure is as follows: in each section, we set the initial values for the metrics, specify the dates from which they appear in the model, and determine the algorithm for the dynamics of these metrics.

We begin entering the input data in the same order they will be reflected in the forecast and profit and loss statement. This follows the classic structure: revenues, cost of goods sold, operating expenses, other expenses.

In the first section of the input data for our financial model, we focus on Revenue. As mentioned earlier, to calculate revenues, we need to determine the number of customers and prices. For various product types and business models, the number of customers will be calculated based on different key indicators. For most companies, customers will come from three main channels: organic (customers who discover the company on their own), paid direct marketing (customers who convert directly from paid advertisements), and sales (customers who purchase after direct interactions with sales managers).

In this article, we will explore revenue forecasting for a mobile application with a “Subscription” business model. Customers will come from organic and paid channels, with the sales department not being relevant in this case.

We begin by forecasting the base metric from which our customers will move towards making a purchase — the number of app installations. For products with online sales (such as SaaS platforms and online stores), this part will be very similar and starts with website traffic.

Organic traffic

We estimate the number of organic app installations in the first month of the product’s operation. We select from the dropdown list an event on the roadmap when our application becomes available to users. Next, we set the dynamics of the monthly increase in the number of app downloads. Once done, starting from the selected date in the “Monthly Forecast” tab, the number of app installations will be calculated monthly at the specified growth rate.

The impact of different efforts on organic traffic is not direct and linear, so we cannot set the app installation calculation based on expenditures in this section. However, it’s essential to remember that organic growth doesn’t happen by itself. It requires work on resources and content, marketing campaigns aimed at brand recognition, expenses for SEO agencies or in-house specialists, and more. All these costs need to be taken into account in the following sections of the financial model and consider whether the planned efforts will yield the organic growth you’ve envisioned.

Paid Installations

Calculating the number of app installations from paid direct marketing channels is more straightforward. It directly depends on the marketing budget and the average cost per installation (CPI). Benchmarks for CPI, both average and specific to your industry, can be found through online research — there are plenty of analytical articles with statistics from marketing agencies and advertising platforms.

In this section of the financial model, we start by specifying the monthly marketing budget and the event from the roadmap that marks the launch of the first campaign. Now, you need to set the growth rates of the budget. In practice, it’s challenging to imagine a situation where you evenly increase the budget every month by a small percentage. Usually, the growth of the marketing budget depends on specific events that make such an increase justified. We choose these events from the roadmap and specify the amounts of additional budgets. For example, it makes sense to strengthen marketing campaigns when launching new products, introducing new features, or expanding into new regions.

Pricing and Conversion

Now let’s start defining the conditions for generating the revenue streams themselves. In our example, this is revenue from subscription sales.

First, we need to decide on the price for the customer and the sales start date. In the case of a subscription business model, another critical metric is Churn. It indicates what percentage of your active subscribers will, on average, cancel their subscription each month.

Next, we need to specify the conversion rate to purchase for our product. Based on the data entered here, we will calculate both the revenue from one customer over their customer lifetime (LTV — lifetime value) and the customer acquisition cost (CAC).

Next, following the same principles, you input the data to calculate the Cost of Goods Sold (COGS) and Operating Expenses (SG&A — Selling, General, and Administrative Expenses).

The calculation of COGS will significantly differ for various types of businesses, while operating expenses are more universal. The main categories for operating expenses typically include marketing and administrative costs. Additionally, a significant expense is employee salaries, but in our financial model, we’ve separated it into a dedicated tab (the “Payroll” tab) for ease of use.

It’s essential to note that the “Payroll” tab includes only salaried employees. If you plan to work with freelancers and outsource work through service agreements, include these costs in the operating expenses section under the appropriate category. For instance, you can include “Design” costs in other marketing expenses or “Accounting Services” in administrative expenses.

By breaking down expenses in this manner, you can accurately reflect your cost structure and understand how each expense item contributes to the overall financial health of your business. This granularity is valuable for decision-making and financial analysis.

At the end of this section, you specify which tangible resources you’ll need to acquire for your business operations and set the depreciation timelines. These are your Capital Expenditures (CAPEX).

Currently, some digital businesses can be launched and operated without significant capital expenditures on tangible assets. However, if you plan to provide employees with computers or other workplace essentials, these costs should be included in your financial model. Using Google Sheets’ functions and a structured presentation of information, you can automatically add expenses for outfitting a new employee’s workplace according to your personnel hiring plan in the “Payroll” tab.

Having a clear understanding of your capital expenditures is crucial for managing your cash flow and ensuring that you allocate resources effectively in your business. It also helps you plan for growth and make informed financial decisions.

The “Monthly Forecast” tab is where all the data is calculated automatically. Your task here is to leverage the features of Google Sheets or Excel and write formulas for the dynamic calculation of all the indicators. The structure of your forecast is visible in the image above. In this tab of the financial model, you provide a detailed breakdown of what happens with all the revenue and expense items. This allows you to identify the strengths and weaknesses of your planned project and refine it to a workable version by adjusting the input data in the tabs described earlier.

The data from this detailed financial forecast tab is then aggregated in a concise form in the next tab, “P&L | Cash Flow | Balance,” where you compile the three main financial statements.

Creating a detailed financial forecast and accurately projecting your income and expenses is a critical part of financial planning for a business. It helps you understand the financial health of your business and make informed decisions about your operations and growth strategies. This level of detail allows you to identify trends, analyze your financial performance, and make adjustments as needed to achieve your business goals.

Начинается прогноз с подробного помесячного расчета количества потенциальных клиентов и выручки. Затем идут все разделы затрат.

Чтобы задать формулы, который свяжут прогноз с вводными данными из других вкладок используем следующий принцип:

1. Через функцию IF (ЕСЛИ) проверяем, надо ли нам в данном месяце учитывать выбранную статью финансовой модели, сравнивая значение месяца в "Monthly forecast" с датой старта в "Projections".
2. Если текущий месяц меньше месяца старта во вводных данных, то оставляем в ячейке "0".
3. Если текущий месяц больше или равен месяцу старта, вставляем в ячейку значение из "Projections".
4. Если необходимо задать динамику роста, то формула усложняется и добавляется динамика по одному из возможных алгоритмов.

This approach is systematic and helps you build a financial model that is flexible and responsive to changes in your business plan. It ensures that your forecasted numbers correspond to your assumptions, timelines, and any growth expectations you’ve set for your business. This level of detail and precision is essential for accurate financial planning and decision-making.

While it does require some initial effort and thought to set up the formulas and structure of a financial model, the benefits of having a well-organized and logically consistent model are substantial. It simplifies the process of making changes, analyzing various scenarios, and communicating your financial plan to your team and partners.

A well-structured financial model not only provides clarity but also minimizes the chances of errors and inconsistencies. It allows you to easily test different assumptions, consider “what if” scenarios, and adapt your financial plan as conditions change. It’s a crucial tool for decision-making and ensuring the financial viability of your business.
So, investing the time and effort into building a robust financial model is definitely a worthwhile endeavor, and it can save you a lot of headaches and confusion down the road.

In the screenshot above, we see an example of using complex formulas and organizing input data in separate tabs. In the “Payroll” section, formulas pull job titles, employee salaries, and their hiring dates based on the input data from the respective tab. In “Capital expenditures,” expenses for purchasing computers for new employees are automatically accounted for, and in “Depreciation,” their depreciation begins to be calculated.

We are now moving to the “P&L | Cash Flow | Balance” tab — these are the three pillars of financial accounting that no business can do without.
Here, we only include essential financial data, setting references in rows to the corresponding data from the “Monthly forecast” and adding some calculations.

The first section is the Profit and Loss statement (P&L). Here, we pull summarized data for revenues, cost of goods sold, and operating expenses from the “Monthly forecast” tab. We add the corporate income tax rate from the “Settings” tab. We also include calculations for several profit stages in both monetary terms and percentages. Now we can see key metrics such as gross profit, EBITDA — Earnings before interest, taxes, depreciation, and amortization, and net income.

This part of the financial model helps us understand the project’s commercial potential and when it can break even. To do that, we need to look at the month when net income becomes positive.

Next is the equally important Cash Flow statement. Here, we categorize three groups of cash flows and add a separate line for specifying investments to clearly track investment rounds if you plan to raise funds this way.

The Operating Cash Flow represents the income (or loss) from our core operations. It will be equal to net income plus depreciation. In this example, we do not calculate working capital, which is derived from the relationship between accounts payable, accounts receivable, and inventory. However, if your project involves significant payment delays and actual inventory, it’s essential to include these calculations in the financial model. Then, changes in working capital will also be deducted when calculating the operating cash flow.

Cash Flow from Investment Activities will include our capital expenditures from the last section of the “Monthly forecast” tab. Cash Flow from Financing Activities will include figures related to loans, deposits, and other financial operations, if applicable.
By calculating the cash flow, we can determine the minimum investment required for project implementation. This is the minimum value of the final cash flow.

Finally, we add the last of the key financial statements, the balance sheet. It provides a visual representation of the company’s assets and liabilities. It’s important to include a line for verification — assets and liabilities should be equal, and if they’re not, there’s an error somewhere in the formulas.

Visualization of key performance indicators significantly simplifies both the work on the financial model itself and its presentation to potential partners. Building a financial model in Google Sheets or Excel provides us with simple and diverse tools to add charts. When creating charts, it’s essential to consider the specific features of your business and display the most significant metrics.

I recommend separating the data used to create charts onto a separate auxiliary tab. This is simpler, faster, and more comprehensible than attempting to build charts based on individual rows from the main tables. The key is to keep the model flexible — when transferring data to the auxiliary tab, never copy values only from other tables; all cell values (including the header with the month labels) should be set using a regular function “=” while referencing the required range from the respective tab.

I’ll provide you with examples of visualization. For a mobile application, it’s interesting to track the dynamics of installations, active users, and the number of subscribers. Line charts from the Google Sheets or Excel chart options are suitable for these metrics.

To create a line chart, follow these steps:

1. In an empty cell on your auxiliary tab (where you collect data for the chart), enter the equal sign (=).
2. Then, go to the Monthly Forecast tab and select the range of dates and the data you want to display on the chart (e.g., dates in column A and installations in column B).
3. After selecting the data range, press Enter. This will link the data to your auxiliary tab.
4. Now, go to the auxiliary tab and select the linked range (the dates and installations).
5. In the Google Sheets menu, click on “Insert” and then choose “Chart.”
6. A chart editor will appear on the right side. You can customize your chart here. Make sure to choose the chart type (line chart), add labels, and adjust the chart’s title and axis labels.
7. Click “Insert” to add the chart to your auxiliary tab.

You can repeat this process for other data you want to visualize, like active users and the number of subscribers.

This way, you can create line charts for various key metrics, making it easier to analyze and present the financial model’s performance.

Visualizing the breakeven point and required investments is crucial for any business. It provides clarity and helps stakeholders understand the financial health of the project.

This chart will provide a visual representation of when the business reaches the breakeven point and how much investment is needed. When you update the input data in your financial model, the chart will reflect those changes and help you quickly assess the impact on your breakeven point and investment requirements.

Visualizing the structure of revenues and expenses and the dynamics of net profit is essential for assessing the financial health and sustainability of a project. You can create these visualizations in Google Sheets or Excel:

1. Set up a new tab or sheet in your financial model dedicated to this chart.
2. Input the data for the structure of revenues and expenses. You can categorize them as needed, such as fixed costs, variable costs, direct costs, etc.
3. Create columns to represent different months or time periods.
4. In the new tab, you can use Excel or Google Sheets’ built-in charting tools to create a stacked column chart or waterfall chart. This chart type will allow you to visualize the structure of your revenues and expenses over time.
5. Add data labels, axes labels, and a legend to make the chart easy to understand.

To visualize the dynamics of net profit, you can follow a similar process:

1. Create a new tab or sheet for this chart.
2. Input data for net profit, broken down by months or periods.
3. Use Excel or Google Sheets to create a line chart or area chart that shows the dynamics of net profit over time.
4. Label important points on the chart, such as the breakeven point and profit milestones.

These charts will provide a visual representation of how your revenues and expenses are structured and how net profit changes over time. When you update your financial model with new data or adjust assumptions, these charts will automatically reflect those changes, helping you assess the impact on the financial structure and net profit dynamics.