Financial Modeling: Excel Best Practices
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.
Color Conversions
- 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
- Press ALT, T, O (in sequence, not at the same time)
- Click on the tab titled Formulas (in Excel 2007 and later only) or Calculation (in Excel 2003 and prior)
- Check the box for Enable Iterative Calculations (Excel 2007 and later only) or Iteration (in Excel 2003 and prior)
- 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.
- I recommend:
- Page
- Landscape orientation
- Fit to 1 page by 1 page (for short projections) or 50% normal size for longer projections
- Margins
- Top Margin: 1 inch
- Bottom Margin: 0.5”
- Left and Right Margins: 0.5”
- Center Horizontally on Page
- Header/Footer
- 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
- 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 future
- This 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.
Next: The Core Statements [not yet completed] (or go back to the beginning)
Category: Financial Modeling
