Developing a business plan is like cooking a dish: first, you must get hold of all of the ingredients; then, you need to follow specific procedures to make each raw ingredient ready for use, by washing, chopping, crushing, dicing, grinding, peeling, carving, slicing, marinating, grating, melting, sifting, squeezing, and/or whisking; and, finally, you can put all of the prepared ingredients together by following a recipe.
We have the recipe for preparing a 3-statement business plan, i.e. an integrated financial model to forecast income statement, balance sheet and cash flow statement.
Do not believe in the one-template-fits-all myth, or the relevance of ready-made models. To use a non-culinary metaphor, a business plan is like a couture gown: for it to be perfect, it must be tailored to each client, almost starting from scratch each time. You may start off with a financial model that either you or another team member had previously built for another company, but each business is unique in its own way, and its characteristics must be reflected in the structure of your model.
In a series of articles, we aim to share with you what are generally-accepted best practices and standard techniques used by financial advisors to forecast the major items of the business plan, and how all of the different items are combined to arrive at integrated forecast financial statements.
Objectives & Uses
A financial model is developed to forecast future cash flows based on flexible parameters, and is designed to assist and support the end user(s) of the model in, among others:
making informed business decisions,
assessing the impact of changes in key parameters with different scenarios and sensitivity analyses,
obtaining financing, and/or
supporting a business valuation or restructuring.
The typical end users are your client's management and shareholders.
The model usually suits one sole purpose, which must be defined and agreed during the planning stage, i.e. before it is developed. The output and deliverables also need to be set out and agreed beforehand with the client, as well as the scenarios to be considered, and the sensitivities to be performed. The key assumptions underlying the financial projections generally come from the client, who will take full responsibility for them. All of these details will need to be included in the scope of work.
General Structure
A standard financial model is made up of the following sheets:
Index: this tab contains a list of all worksheets present in the financial model for ease of navigation, especially for large or complex models
Input: this sheet includes the general inputs processed by the model, such as the desired projection interval, the inflation rate, and the selection of base/best/worst case scenarios (where relevant)
Output: this tab contains the integrated income statement, balance sheet and cashflow statement, properly formatted to be copied and pasted to your report
Workings: it is common to have 1 tab per main item to forecast, so there will be several 'Workings' tabs in your model
Checks: this sheet includes error checks on balances and totals, e.g. total assets corresponding to total equity and liabilities, or balance sheet cash corresponding to the cash flow statement's end-of-period ("EoP") cash.
The 'Workings' tab is structured as follows:
Forecasting periods: across the top rows of the tab are the years of projections, followed by the selected forecast intervals (e.g. monthly, quarterly) as per the 'Input' sheet
Output: this is the first section of the worksheet, and it includes the projections of income statement, balance sheet and cash flow statement related to the specific item(s) being forecasted in the tab, which will be then linked to the 'Output' tab
Input: this section appears below the 'Output' section, and it comprises the inputs specific to the item(s) being forecasted in the tab (additional to the general inputs included in the 'Input' tab)
Calculations: this section appears last, below the 'Output' and 'Input' sections, and it is where all of the necessary calculations for forecasting the item(s) are performed.
NB: a good financial model is simple, flexible, consistent, efficient, and clear.
General Formatting
The cells in a financial model are color-coded to indicate the nature of their contents. Typical formatting rules include:
Blue font: the cell contains a hardcoded input (value, text or date)
Black font: the cell contains a formula.
For the rules to be relevant, you must maintain consistency across the whole model.
Useful Functions & Shortcuts
To build a financial model, you must be or become familiar with the following Excel functions, among others:
EOMONTH: returns the date at the end of a month, a number of months before (-) or after (+) a given date
MONTH: returns the month number of a selected date (i.e. January is 1, December is 12)
YEAR: returns the year of a selected date
YEARFRAC: returns the number of years between two selected dates
SUMIF and SUMIFS: sums the values in the selected range of cells that meet 1 or more conditions
COUNTIF and COUNTIFS: counts the number of cells in the selected range that meet 1 or more conditions
&: concatenation, to combine the content of 2 cells
AND: usually combined with the IF function, evaluates whether or not multiple conditions are met
OR: usually combined with the IF function, evaluates whether or not any of multiple conditions are met
INDEX MATCH: returns the content of a cell when 1 criterion is met; useful to search for a specific value in a column or row
INDEX MATCH MATCH: returns the content of a cell when 2 criteria are met; useful to search for a specific value in a table or database
Also, it really helps to be or become familiar with the following Excel shortcuts, among others:
CTRL + S (Windows) | cmd + S (Mac): workbook (and life) saver
CTRL + A (Windows) | cmd + A (Mac): select all cells in the worksheet
CTRL + ENTER (Windows) | cmd + ENTER (Mac): enter a copied formula or value in the selected cells
CTRL + arrow key (Windows) | cmd + arrow key (Mac): navigate around the worksheet
SHIFT + CTRL + arrow key (Windows): select cells in a row and/or column within a worksheet
CTRL + Page up/down (Windows) | alt + left/right arrow key (Mac): move to the previous/next worksheet
CTRL + Space bar: select the entire column in the worksheet
SHIFT + Space bar: select the entire row in the worksheet
SHIFT + Tab (Windows) | cmd + Tab (Mac): switch workbook
F4: anchor cells (when in the formula bar) or repeat your last command
F9: activate the recalculation of your workbook (if set to Manual) or of all the data tables present in the workbook (if set to Automatic Except for Data Tables)
CTRL + F (Windows) | cmd + F (Mac): find
CTRL + C/V/X/Z (Windows) | cmd + C/V/X/Z (Mac): everyone should already be familiar with the copying/pasting/cutting/undoing shortcuts
We hope you found this article useful. Stay tuned for the upcoming articles of the series, and make sure to download our Excel template with the basic financial model structure:
For questions or clarifications, feel free to reach out. Thanks for reading, and good luck!
Comments