Financial forecast model: A Microsoft Excel template could become an ‘Open source’ project

June 12th, 2006

Over the years, I have developed an Excel spreadsheet which provides a financial forecast. This is integrated with a business plan/Information memorandum (word document), Investor presentation (powerpoint) and a Excel worksheet to manage the transaction process. I plan to publish these other elements. This financial forecast model is the easiest to publish and share. However, like all models it must be customised to your specific and unique requirements. To review all available templates, please view all posts in the Templates category . If these templates are valuable to you, please donate.

Most of my financial models have been built for high growth technology companies, specifically enterprise software. As a result, the initial structure of the models is particularly useful for high growth companies.

Financial modeling is a specialist task. A template is useless unless the skills are available to customise it for a specific need. The skills required include Microsoft Excel, a knowledge of accounting standards and being able to communicate the business model by using a financial model. I was reluctant to provide this model, but it does represent a “start”. Even with the instructions below, a quality outcome is not assured.

The model may be downloaded by clicking here.
This spreadsheet is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 license.

This particular model was originally derived on work for an Australasian enterprise software companies. It can, however, be modified for different jurisdictions and businesses quite quickly. I will update the model when new projects result in improvements. If you improve the spreadsheet, I would also welcome the opportunity to include those improvements here. Perhaps, we will create an “Open source” financial forecast model for the world. Maybe, the world only needs one and this can be our contribution to the world. We could use Google spreadsheets to develop the project.

Worksheet - Assumptions
The “Assumptions” page in the model allows many different aspects of the model to be changed quickly. This includes time periods, product/business lines (five allowed for), gross profit margin of each product/business line.

Company name
Please enter the Company name in Cell A1 on this worksheet. It will be reproduced throughout the model.

Time period
The model is set up for five time periods. These are typically calendar or financial years. Please enter the time periods. The usual approach is to forecast for the next five years. Although, I generally preferto forecast next five half-years. This should be integrated with the strategic plan which identifies the key tactics and objectives of each six month period.

Revenue
The model allows for five revenue categories. Please enter the names that represent the key products or business units of the business. These will need to be driven by units of business activity. Units sold. For each revenue category, revenue is divided between non-recurring revenue and recurring revenue. Recurring revenue is the annual revenue expected from the customer. Recurring revenue is calculated from a customer base which is the sum of sales through the life of the financial model. The direct cost of recuurring and non-recurring revenue is also required as an assumption.

Operating expense
The model is set up for five categories of operating expense. Please enter the most suitable. In the past, I have often used “sales and marketing”, “research and development”, “Management and admin” and “strategic opportunities”. Strategy often referred to those expenses that an investor was being asked to fund.

Proportion of new customer activity
This field determine the proportion of sales during the time period that will be recorded as revenue for that period. For example, if you sell a widget and it is invoiced immediately, then 100% of business activity should be recorded as revenue. If a large sale is sold and revenue is recognised over 18 months, then 66% the revenue should be recognised for the period. This area will need to be customised. I recommend you set it to 100%.

Worksheet - Business activity
The model is driven by business activity. Specifically, the number of units sold in each revenue line. Revenue will be caclulated by multiplying the unit by the assumed price.

Please go through the “Business activity” worksheet and enter the number of units sold in each time period.

Worksheet - Operating and capital expenditure
In the assumptions speadsheet, you were required to enter the operating expense categories. The details concerning the expenditure should be entered on this worksheet.

It is often difficult to outline specific marketing costs with any credibility. It may be prudent to research the industry the business is in and determine the industry average. Enter marketing expense as a formula based upon revenue. The same may be done for research and development.

Worksheet - Summary historical
This worksheet contains the historical information for the business. Please enter the details. You may need to adjust the graphs and other tables, if the model does not display all historical information.

Worksheet - Graphical summary
The “graphical summary” worksheet contains summary graphs of the key information in the financial model. These can be dynamically linked using Microsoft’s object linking and embedding feature to the Information Memorandum/Business plan and the Investor presentation. As the model changes, related information also changes.

Worksheet - Summary financial
This worksheet provides a single A4 page summary of the financial model. It is designed to be included in the business plan.

Worksheet - Mezzanine and IPO analysis
The financial model forecasts the potential performance of the business. However, the financial forecast may not be the primary purpose of the model. The primary purpose of the model is to estimate the potential value of the business in the future and to estimate the capital required from investors to achieve that value. An investor wants to know what their funds will be used for and what the outcome will be.

If business value is created, the investor and promoter of the investment opportunity may then negotiate the terms and valuation of a potential investment. The investment may, ofcourse, be structured to share the business value, or lack of it, to ensure shareholder value is fairly divided between investor, promoter, founder and management team.

Making it work
Financial forecasting is art and science. The template enclosed will help with the science. The art is left for you. I hope you find this model useful and you can make it work.

If you have trouble, just give me a call on Skype. I am more than happy to help. It is the least I can do, given I have released a “template”.

I plan to work toward the release of an integrated set of templates - the business plan/information memorandum, the investor presentation and financial model.

NOTE: To review all available templates, please view all posts in the Templates category .

Entry Filed under: Free software and Open source, Economic development, Private equity / venture capital, Templates