Excel tips and tricks
It’s very important to follow best practices in Excel when building a model.
- Limit or eliminate the use of your mouse (keyboard shortcuts are much faster)
- Use a blue font for hard-codes and inputs (formulas can stay black)
- Keep formulas simple and break down complex calculations into steps
- Ensure you know how to use the most important Excel formulas and functions
- Use INDEX and MATCH instead of VLOOKUP to query data
- Use the CHOOSE function to build scenarios
Formatting
It’s important to clearly distinguish between inputs (assumptions) in a financial model, and output (calculations). This is typically achieved through formatting conventions, such as making inputs blue and formulas black. You can also use other conventions like shading cells or using borders.
Model layout and design
It’s critical to structure a financial model in a logical and easy to follow design. This typically means building the whole model on one worksheet and using grouping to create different sections. This way it’s easy to expand or contract the model and move around it easily.
The main sections to include in a financial model (from top to bottom) are:
- Assumptions and drivers
- Income statement
- Balance sheet
- Cash flow statement
- Supporting schedules
- Valuation
- Sensitivity analysis
- Charts and graphs