Multiple worksheets are a spreadsheets third dimension, an essential visual navigation aid and a defining property of a model that is efficient to use.
One of the most common ways in which a financial model can become, or be from the outset, ‘user unfriendly’ is the way in which the worksheets and their tabs are setup. By ‘setup’ I mean
- How many worksheets are there in a model
- In what order are the worksheets presented
- How are the tabs named
- How are the tabs coloured
Guidelines for optimising worksheet structure
Here are some good guidelines which I use to optimise the tabs structure of the model:
- Control the number of worksheets so that they are all visible without scrolling
- Name them appropriately
- Keep the names short
- Use colours to signify categories such as Inputs, Outputs, Calculations
- Group identical worksheets together
- Group worksheets of the same type (such as Inputs)
- Order them in the such a way that will make the most intuitive sense to a user
Worksheets in a Greenfield Project Finance Model
For a typical Greenfield project finance model following the tab order below has been proven to be able to accommodate changes in operational and capital structures as well as being intuitive and efficient to navigate. Different situations call for slight variations but this is a robust starting point.
- Cover page / disclaimer
- Change log
- Executive summary
- Annual financial statements
- Monthly / quarterly financial statements
- Inputs
- Scenario manager
- Construction and construction funding
- Operational calculations (volumes)
- Revenue
- Operating costs
- Tax and depreciation
- Debt financing, reserves and ratios
- Equity funding and returns