If you are like me, you are ready to jump into the constructing the financial model, but DO NOT IGNORE THIS POST! I guarantee that it will take you more than twice as long and you will make twice as many mistakes if you do not follow these simple best practices.
A fully functional financial model needs to be modular so you can later adapt it to more detailed schedules if necessary. For example, we will initially project revenue by making a percentage growth assumption, however in the future you can (or arguably should) make a detailed revenue schedule based on they key factors (macroeconomic, microeconomic or both) that drive revenue growth. Many of the To accomplish this, you need:
- Consistency – Is the value in thousands, millions, or ..? What does this color mean?
- Clarity – Where is this cell getting its data? What does this cell do?
- Repetition – Only enter any assumption once, and then reference it from other parts of the model.
- Blue – These are assumptions you enter
- Green – These are direct references to one or more sheets
- Black – These are calculations that occur on the active page or references within the active page
Turn Iterations ON – The circular model will not calculate if iterations are turned off
1. Press ALT, T, O (in sequence, not at the same time)
2. Click on the tab titled Formulas (in Excel 2007 and later only) or Calculation (in Excel 2003 and prior)
3. Check the box for Enable Iterative Calculations (Excel 2007 and later only) or Iteration (in Excel 2003 and prior)
4. Set the “Maximum Iterations” field to 100 and the “Minimum Change” to 0.001 (both of these are the default values)
Don’t hard code values into formulas – always reference the inputs you entered.
For example, in cell B3 (shown above) you reference the 8% growth rate in cell B1 rather than entering 8% directly into the formula
- This ensures that you model remains accurate when you update these values later
Adjust page formatting at the beginning of this process to ensure it is duplicated consistently.
- Landscape orientation
- Fit to 1 page by 1 page (for short projections) or 50% normal size for longer projections
- Top Margin: 1 inch
- Bottom Margin: 0.5”
- Left and Right Margins: 0.5”
- Center Horizontally on Page
- No Header
- Footer containing the file path and name, time and date
- Footer contents should be in 9 point font and *not* be scaled with page
Some final tips:
- Use “Center Across Selection” instead of “Merge Cells” when formatting
- Never “Hide” cells. If you don’t want them displayed, group them by pressing CTRL + Shift + Right Arrow, or Shift + F2)
- Make extensive use of Cell Comments (right click on cell and “Insert Comment”) to note where the input or formula came from. This prevents “What was I thinking syndrome” the day after.
- Utilize Custom Number Types for the type of number that will be in the cell. This format ensures that the numbers and percents will all line up nicely:
- For numbers: #,##0.0_);(#,##0.0)
- For percents: 0.0%_);(0.0%)
- In Excel 2007 and later, you may want to define this as a custom style and also include the color, so you would have:
* Input – Number
* Input – Percent
* Referenced – Number
* Referenced – Percent
* Calculated – Number
* Calculated – Percent
– Select New Style:
- When you need to hide a calculation for presentation purposes, use the custom number format ;;;
- Don’t forget to leave a comment, so you don’t accidentally delete the cell in the futureThis is better than setting the font color to be white because, when printed, white colored fonts print as black
I cannot urge you enough to keep up with the above recommendations. The time it will save you in the long run is incalculable.