The following instructions are specifically prepared for our simple financial plan, which can be requested here. Once you order the financial plan and fill in the information, you will receive a ready financial plan for your business where all information has been checked for accuracy and reliability: this process, carried out by an expert financial consultant is necessary to make your financials realistic. However, you are free to update your financial plan as necessary after delivery. The instructions below will allow you to do this: the instructions are based on Microsoft Excel offline, if you use another program, we cannot ensure that the instructions will match. In addition, other programs may have incompatibility issues with IFERROR formulas, and if present, macros, as well as the viewability of hidden cells: the online versions of software also have less features available and some graphs may appear different. For any additional support, by email or call, revision and additions to the financial plan, you can access the hourly services here (if you have not purchased additional support directly with your order).
- Financial Plan Setup
- How the financial model works
- Revenue & COGS
- Customer Acquisition Costs
- Customer Retention Costs
Financial Plan Setup
Since you may change your strategy on a regular basis and make mistakes from time to time, we’d recommend saving a new version of the financial plan everyday that you make changes to it, with today’s date in the file name.
You can make the financial plan your own by changing brand colours very quickly. Just go into Page Layout > Colors > Customize Colors, change the colour codes to your own brand colour codes and save the new theme colour. Just pay attention not not substitute a dark colour with a light colour and the other way round, as in that case some text may become no longer visible.
How the financial model works
- The financial model works entirely with assumptions: the only page where to make changes is the Assumptions page.
- The assumptions cells that you can change are highlighted in grey as shown below (beware that if you have changed the colour grey in the original palette, this colour will have changed as well). White cells are not to change (unless you become very familiar with the formulas and financial modelling). Even though this financial plan does not include past values, if you keep using this model over time (let’s say one year later), you can start inserting all past values in the relevant monthly cells in each calculation tab and highlight them in beige or another recognisable colour.
- The financial plan also relies on dates: to prevent any inaccuracy in some of the formulas, use dates with the 1st day of month
- The model uses hidden rows and columns: use the numbers in the small square button in the top left corner of each page to automatically hide and expand the row and columns: this provides a summary or expanded view to make it easy to view key information (this doesn’t work with Apple or potentially other non-Microsoft software)
- This model contains graphs and tables that you can edit as you like and paste directly into presentations
- This model uses formulas, for example IFERROR, that may not be recognised in other spreadsheet programmes such as Apple Numbers and old 2003 Excel versions.
The cover page is simply an overview of the financial plan’s key information, with quick links to the different sections and the instructions mentioned earlier.
Here you can fill in the basic information about your company: all of this information are linked to the financial plan (the name of the company, currency, financial plan start date and today’s date) and therefore should be up-to-date to ensure the accuracy of the financials and of the fundraising period. If your financial plan comes with a valuation, today’s date and the incorporation date affects the result of the valuation.
The following instruction detail how to fill in every part of the Assumptions page, that also have a corresponding Calculation tab. This is the only page that you need to fill in (in addition to the few details in the Cover page). The remaining tabs all contain automatic formulas based on these assumptions. The assumptions page also contains ‘Checks’ to verify if your calculations are realistic.
The assumptions pages contains key instructions in Column B for founders. Specific-company notes and sources can be included in Column C, for example links to the sources used or assumptions that have been taken, as it’s easy over time to forget how certain figures were estimated. Alternatively, you can add comments in the single cell, but writing on the side is a cleaner approach if you want to copy-paste the assumptions tables.
As mentioned, the financial plan works with dates that can be changed in every section. You can see that we selected 5 phases and their start dates that change colour throughout the page: You can change these dates, or even the wording used, according to when these phases apply to your business model. The colour of the dates in each section will then change automatically. This feature does not replace a timeline, that you can prepare in more detail in your business plan, but helps spot dates that are inconsistent when planning your financials.
Something that shouldn’t be done in the assumptions is to include other text in the assumptions cells, as it could result in errors showing up in the model. Leave empty any unused cells and do not delete them.
Each section in the assumptions page contains quick links to the calculation page where the assumptions are used to calculate the financial plan figures. As illustrated in the example here, the Market section under Assumptions is linked to the Market tab with calculations by clicking on cell A4:
Follow the instructions below to complete each subject area in the assumptions page:
Assumptions – Market
One of the most difficult parts of any business plan is to correctly define the market size and market growth. That’s where the vast majority of unrealistic assumptions comes from. To learn more about this, see some information about Sizing the Market here. In this model, we use a more simplified approach than the one we use for more advanced financial plan, where we size every single market entered and the revenue of each market. This requires significant research time and modelling skills, therefore it is only included in our Advanced Financial Plan. You can also add this feature to your simple financial plan by accessing the Hourly Services. It is recommended to use this services when you are raising more than €500k, and instead to simplify your approach until you have the budget to spend on consulting and financial planning. If necessary, you can build a separate overview of the markets that you plan to entry and market size split that is not directly connected to the financial plan (but that has consistent figures.
The market size here is measured in terms of number of customers, not in terms in market value. This approach is important to measure marketing costs based on each customer gained. In the Simple Financial Plan, you can plan the number of customers that you would like to (realistically) reach gradually, as follows:
- The date when you plan to first target the market and gain customers
- How many customers you plan to reach by the end of the year (registered customers)
- The growth rate at which you plan to grow your total registered customers
- How the growth rate changes over time (it would usually decrease over time as you grow)
- The monthly retention of customer (the definition of active customers would usually be sector-specific)
- How the monthly retention rate grows over time as your brand becomes stronger: in successful business model this can vary between 89-99%, but it can be much lower for mobile apps
- Set the maximum monthly retention rate that is feasible for your sector, with a maximum of 100% (obviously) to avoid calculation errors.
This approach is usually good for small and local businesses, or in small businesses operating in very large markets where calculating a market penetration does not bring much. The bottom-up approach for non-startup businesses is acceptable. However, it’s not a suitable approach for startups raising equity, as in this case a large market penetration, as well as competition, is expected. For startups, a top-down approach provides much more reliable figures: in this Simple Financial Plan, we do this by setting an upper limit to the market size and achievable penetration over time as follows, starting with inputting an X next to “Limit the number of customers to the market penetration limit”. This will use the following market sizing approach as an upper limit in your customers’ expansion.
There is also a third option, that is to only use the market sizing a penetration figures below to estimate the number of customers. In this case input an X next to “Use only Market Data below”.
You don’t have to select this option and the following market sizing instructions are fully optional. If you don’t want to set a market size limit since you already know how many customers you can gain, you can simply leave the “Limit the number of customers” cell empty.
The market, in this case, is just one, so if you plan to enter multiple markets, add the up together, while the time to reach these market should be consistent with the time you plan your expansion into all markets to last.
Subsequently enter the following information:
- The Total Addressable Market (TAM), meaning the sum of all the target markets’ individuals or businesses that fit a certain description, by location, type or person, type of business, etc.
- The CAGR (Compound Annual Growth Rate %) of the Market used in the TAM, preferably a growth rate that appears sustainable over the next 5 years at least. This information could be found for free searching the right keywords.
- The portion % of Serviceable Available Market (SAM), meaning the portion of the market that you can realistically target. In this step, you can further reduce the market size by focusing only on the part of the market that is suitable to the company, often by using assumptions based on user behaviour.
- If there is any clear change in usage that would make the usage grow faster than the total market, or that would reduce the usage over time, please include the change in usage here. If the growth is already accounted for in the total market growth, leave the SAM Growth with 0%
We calculated the Market Size, the Serviceable Available Market (SAM) this way: TAM x (1+CAGR) x SAM x (1+CAGR) for every financial plan month. The SOM was calculated based on the estimated penetration of this market.
To calculate the Serviceable Obtainable Market (SOM), we narrow down the market by the competition:
- № of Competitors (and substitutes) that you are sharing the identified SAM market with (please enter the competitors in the market, if you write 0 or 1, investors will think that you’re delusional and that you didn’t do your research, in most circumstances you will not have less than 5 competitors unless you are in a highly niche market)
- Change in the rate of competition, that is growth in the number of competitors per year % (if you are entering, particularly as a startup, in a profitable of trendy market, you will need to account for increasing competition), we recommend using rates from -5% to +50%
- Initial competitiveness indicates your competitiveness (compared to the average competitor) at market entry, which is likely close to 0 due to low brand awareness. This determines how quickly you will gain new customers when you enter the market. Most founders believe that they can gain the same amount of customers as their competitors in the first year: while you may have a superior offering, your brand awareness and your budget may limit you from doing so. It’s recommended to use figures between 0-150%, but more likely under 5-10% at market entry
- Competitiveness increase: just as you start with low competitiveness, it can grow very fast on a yearly basis, particularly if your company is VC-funded. You can use a yearly increase in competition between 0-100%. The lower your initial competitiveness is, the more likely it is that you can grow at over 50% in competitiveness p.a.; the higher you set it initially, instead, the slower you are likely to keep growing, as there’s always an upper limit.
- Max penetration indicates the maximum penetration that you can achieve in the market. So if you have used competition figures that are unrealistic, this will at least set an upper limit of the market penetration. This will depend on the size of the market, but in most circumstances, it is unlikely to grow past 10%
We calculated the Serviceable Obtainable Market (SOM) this way: SAM x Market Penetration. The Penetration Rate was calculated with the n° of competitors sharing the same market, the entry of new competitors, the current Competitiveness of the company and its increase thanks to growth and strategy: The formula is SAM x Market Penetration [(n° of competitors x (1 + competitors’ growth) ^Time ) : [ Minimum of MaxPenetration and (Competitiveness x (1+ competitiveness growth) ^Time) ] }
In addition to this, we include a calculation that naturally smooths out the development of customers into realistic numbers, also due to the typical overestimation of first year customers, by adding:
- The № of years to reach all available market – since in this case you are including all target markets, it’s best to use the higher values (7-10 or in some cases more). This indicates the time during which you will target and gain all the selected SOM customers. After this period of time, you may keep growing but only with market size growth and penetration growth (if it hasn’t reached the set limit yet). However, realistically, since this financial plan is suitable for pre-seed companies, once you start growing, you can increase the sophistication of your financial plan and divide all different target markets, that you will enter at different points in time, which will give you a more realistic expansion over the course of 5-10 years.
This approach may seem complex, but many of our financial plan clients have revealed that their budgeted number of customer were very close to the actual number of customers they gained, which is a very rare achievement in startup forecasting.
Assumptions – Revenue
In order to plan your revenue, you need to know what your business model is and how it is defined. These article may help you understand where your revenue model belongs to:
- Sale of services and products
- Subscription revenue and other monthly changes
- Transaction, Licences and Advertising Fees
In our simple financial plan, you’ll be able to include 5 different revenue streams. These may all be in the same category, but they may also be in different ones: however, be aware that investors do not like to see unfocused business models, it’s best to have a maximum of 2-3 different categories of revenue streams and not more.
If you have different products or services in the same category, such as membership fees, you can either enter them in different lines or input them as averages, when the products or services only have small differences in income. If you want to add additional revenue streams, you can click on the button “Add new revenue Stream” to automatically add a new revenue line across the spreadsheet. The macro has only been tested in Microsoft Excel and may not work well in Mac Numbers. If you go on to edit the rows in the spreadsheet’s current tabs, the macro may stop working correctly.
The ‘Revenue & COGS’ tab takes the previously calculated market data as basis for your revenue calculations.
TYPE – Here you can fill in the basic information about your revenue stream. In Cell A42 you will be able to freely name your revenue stream. In Cell D42 you will need to select the type of revenue that you are using, choosing one out of the 11 categories listed, and inputting the number only. Each type of revenue will them be calculated differently. You can then include the date when you will start offering or charging for this product/service. Finally, you can choose the order number in which your different revenue streams will be displayed in the final income statement, depending on how many revenue streams you will have.
CUSTOMERS – Here you can enter basic information about how the revenue streams uses customers-related information. In the first cell, enter R to use Active Customers that produce recurring revenue, and U to use only New Customers that have a one-off revenue stream. The vast majority of revenue streams with be based on Active Customers (“R”). In some cases, such as for Sign-up Fees or business models based on non-returning customers (tourism for example, or long-lasting products), you will use New Customers (“U”).
You can then select the portion of total customers (active or new, based on your selection) that purchases this specific product/service. If you have only one revenue stream, this will likely be 100%, but if you have multiple revenue streams, you will have some customers purchasing one product/service and other customers purchasing others: in this case the total may be over 100%, as some customers may purchase multiple types of products/services.
Customer Acquisition Costs – CAC
See some information about Customer Acquisition Costs here.
Here you can select the direct acquisition channels used to acquire customers and partners, as you have calculate them in the market section. Use the main types of sales & marketing channels selected: you can change the name of the channels, but the calculations of each channel is different.
If you don’t want to use a channel, simply set the percentage % of users gained through the channel to 0, without the need to remove all information. You can set the percentage for every channel except for digital advertising, which is set as the automatic remaining % to arrive to 0, to ensure that the sum of the customers targeted is 100%.
Then you can enter:
- The date when you will start using and paying for each channel
- The reach of each channel per month or per ad or per event, which is the number of customers reached; the exact definition will vary for each channel
- The lead rate, which is the number of interested leads gained when approaching a certain number of potential customers
- The conversion rate is the number of leads that will turn into customers
- The change in conversion indicates the increasing ability of the company to gain customers with increasing brand awareness, and the lowering CAC over time
- When the resulting capacity needed does not correspond to the initial marketing plan, you can set a minimum or maximum n° of staff, ads, events etc. for each category
- You can now set a cost for each channel, depending on the chosen unit: 1 monthly sales staff salary, 1 ad campaign, 1 event, etc. with the corresponding inflation
- Additionally, for sales staff you can include a bonus
You can do the same exercise for partners acquisition costs, if you have any.
Customer Retention Costs – CRC
Complete the information about your market on the editable cells on the first 1-6 columns.
We call these customer retention costs as they relate to recurring marketing and branding expenses, but they are usually aimed at both new and existing customers.
For marketing costs, please enter:
- If you have a marketing launch, please enter the cost and the month
- You can enter a start date and monthly cost for each type of recurring marketing expense, with a yearly inflation
- For each type of marketing expense you can also select a minimum cost per user, so that marketing costs realistically grow in line with the size of the company: ensure that this number is low, usually just a few cents
Marketing & other sales staff is also part of recurring marketing expenses, enter:
- The start date of each position, if any
- The minimum number of staff for each position
- if you want the number of staff to grow in line with the company’s growth, enter the number of customers per staff position
- Include a negative economies of scale rate % in order to avoid a fast growth of staff; usually staff grows in line with number of customers but at a lower rate
- The salary and salary inflation per year
You must be logged in to post a comment.