Building up your financial model for your business can be a frustrating process, particularly if you don’t have a lot of experience with using excel. The good news is that a lot of the actually calculations are very simple; plus, subtract, divide and multiply. Setting up your financial model correctly from the start can avoid a lot of headaches down the track as your model matures.
I have compiled a list of best practices to set up your model which are being used by business analysts across the financial sector. These will help you reduce the chance of errors and make your model look beautiful (or as beautiful as an excel file with a bunch of calculations can look) to impress investors and gain an advantage when seeking investment for your company.
- On the top of each page set up of time period flags (end of month, month flag, year flag).
These are important to set up to help us putting together different summaries with different period changes. Also it is easier to trigger payments that happen only during certain months (i.e. yearly tax payments, upgrading offices due to increase in staff etc).
- Color code your cells.
Using this color code system highlights for anyone using the model which cells they can change without breaking the model.
- Blue is an Input used for assumptions. When a number or text is entered into a cell – the text should be highlighted blue.
- Black is a Calculation, there are no numbers entered in this cell, they should be all referred to a blue or red colored cell.
- Red is a change in calculation, highlighting changes in the calculation which are not consistent with the surrounding calculations. Sometimes it is more efficient to enter a different calculation in a cell. These cells should be highlighted to make sure they are not accidentally copied over. (i.e. one time events like special dividends etc)
- Leave two columns for important assumptions.
When calculating certain line items it is useful to link the assumptions close to where the calculations are being made (as oppose to linking to them directly to assumptions page. It also helps later on when we want to perform a similar calculation. We can just copy and paste the lines of the calculation and enter the new assumptions in those two columns.
- Different pages for different objectives.
There should be different pages according to their uses.
- Assumption Pages – Includes all your core assumptions for your projections.
- Calculation Pages – utilizes the assumptions to make projections, calculate the financial statements (income statement, cash flow statement and balance sheet), valuation and returns calculations.
- Summary Pages – Links to the calculation page to provide a format that can be used to easily copy and paste into investor presentations.
- Use Font type Trebuchet MS.
This is more of a personal preference. I think it looks more professional.
Do you have any processes that you use to help you set up your financial model?