Group and Outline – Essential tool for large financial models

There is a setting in the Options -> Edit -> Setting section called ‘Edit directly in cell.

The ‘Edit directly in cell’ related to the way Excel shows formulas in the cell and in the toolbar when you are in Edit mode (which is what you get when you click F2).

Edit directly in cell –activated
When Edit directly in cell is activated the formula in the toolbar does not get colour-coded but the formula in the cell does.

Edit directly in cell – activated
If you choose to uncheck the Edit directly in cell option then you will get the formula in the toolbar colour-coded but the formula in the cell is not.

Which setting is better?
I find it more efficient to work with the ‘Edit directly in cell’ option unchecked. There are two main advantages
1. If you are editing a formula that is longer than the cell (which is almost always the case) then you can see the whole colour-coded formula in the standard Excel toolbar.
2. It is easier for the eye to focus on the same area of the spreadsheet (the toolbar) every time you are editing a formula. When you are copy-pasting sections of a formula or working with the Insert Function dialogs this makes life a lot easier.

This is a functionality that only takes seconds to implement so try both options and find out for yourself which option you prefer.
On of the most common mistakes in project finance modelling is incorrect coding of delay scenarios. The main reason for this is that it is not implemented as part of the core functionality of the model when it is first constructed, cheap but rather squeezed in by a bank analyst as part of the credit analysis.

Since the person coding the delay functionality is often different from the person who originally constructed the financial model it can be challenging to understand all the implications of changing the model logic in something as complicated as a delay scenario.

Example of delay scenarios

  • Delay of start date of construction
  • Delay of completion (i.e. construction takes longer than planned)
  • Delay of divestment date
  • Delay of acquisition date
  • Delay of capital raising
  • Delay of debt refinancing

How can we quickly estimate the accuracy of a delay scenario?

One quick method of getting an estimate of the accuracy of a delay scenario is to prepare a data table (manual or dynamic using the Excel Data Table functionality) with cashflows from a number of scenarios. If these cashflows are plotted then it is often possible to visually identify big picture problems.

This method can save you hours of work if you are on performing your own credit analysis but are not responsible for the modelling on the transaction. You can simply prepare the outputs as illustrated below and send this back to the modelling bank or the sponsor for clarification.

Preparing a table of cashflows for chosen scenarios

In many cases it makes sense to choose the scenarios that you will be including in the credit or investment analysis. In the example below I have illustrated the concept with CFADS (Cashflow Available for Debt Service) but in your specific case it may be more useful with another metric or a combination of several metrics.

Suggested cashflow metrics

  • EBITDA
  • Distributions
  • Revenue
  • Operational Expenses
  • Capex
  • Debt Repayments
  • Cash account movements
Image 1: Cashflow Available for Debt Service for four scenarios

Image 1: Cashflow Available for Debt Service for four scenarios

Image 2: Graph of cashflows from the investigated scenarios

Image 2: Graph of cashflows from the investigated scenarios

Identify problems in financial modelling of scenarios

Generally speaking cashflow movements in scenarios or sensitivities should be smooth and without spikes. In the example in Image 2 there is a big jump in CFADS in periods Ops 1 and Ops 2 which would be a reason to further investigate the correctness of this scenario.

Of course there could still be problems in all the other scenarios too and this method is a top down approach to finding BIG mistakes first (while you can still get someone else to fix them for you!).
On of the most common mistakes in project finance modelling is incorrect coding of delay scenarios. The main reason for this is that it is not implemented as part of the core functionality of the model when it is first constructed, ampoule
but rather squeezed in by a bank analyst as part of the credit analysis.

Since the person coding the delay functionality is often different from the person who originally constructed the financial model it can be challenging to understand all the implications of changing the model logic in something as complicated as a delay scenario.

Example of delay scenarios
• Delay of start date of construction
• Delay of completion (i.e. construction takes longer than planned)
• Delay of divestment date
• Delay of acquisition date
• Delay of capital raising
• Delay of debt refinancing

How can we quickly estimate the accuracy of a delay scenario?

One quick method of getting an estimate of the accuracy of a delay scenario is to prepare a data table (manual or dynamic using the Excel Data Table functionality) with cashflows from a number of scenarios. If these cashflows are plotted then it is often possible to visually identify big picture problems.

This method can save you hours of work if you are on performing your own credit analysis but are not responsible for the modelling on the transaction. You can simply prepare the outputs as illustrated below and send this back to the modelling bank or the sponsor for clarification.

Preparing a table of cashflows for chosen scenarios
In many cases it makes sense to choose the scenarios that you will be including in the credit or investment analysis. In the example below I have illustrated the concept with CFADS (Cashflow Available for Debt Service) but in your specific case it may be more useful with another metric or a combination of several metrics.

Suggested cashflow metrics
• EBITDA
• Distributions
• Revenue
• Operational Expenses
• Capex
• Debt Repayments
• Cash account movements

Image 1: Cashflow Available for Debt Service for four scenarios

Image 2: Graph of cashflows from the investigated scenarios

Identify problems in financial modelling of scenarios
Generally speaking cashflow movements in scenarios or sensitivities should be smooth and without spikes. In the example in Image 2 there is a big jump in CFADS in periods Ops 1 and Ops 2 which would be a reason to further investigate the correctness of this scenario.

Of course there could still be problems in all the other scenarios too and this method is a top down approach to finding BIG mistakes first (while you can still get someone else to fix them for you!).
Depreciation is a key component of a financial model to calculate the tax payable. Of course it is often critical to calculate the Profit and Loss but in project finance many would argue that this has a minor importance when compared to the Cashflow Waterfall which drives all credit analysis.

Clear presentation of the depreciation inputs

Having a standard structure for the assumptions regarding depreciation can save a lot of time. The assumptions can vary slightly depending on the applications but generally you will need a combination of the following assumptions:

  • Asset Category
  • Method of depreciation for Tax
  • Method of depreciation for Accounting
  • Asset life
  • Multiplier (for accelerated Reducing Balance method)

One suggested structure for the layout of the depreciation assumptions for a typical vanilla project finance model is presented below:

depreciation-1

Use Data Validation to facilitate easy updating

Some of the cells above can be coded using Data Validation which greatly simplifies the editing of assumptions from an end user’s perspective.

depreciation-2

A user can simply point and click to choose between Straight Line Depreciation of Reducing Balance.

To set up the Data Validation, visit this site
click Data – > Data Validation and select Allow: List and set the Source to “SL, physician
RB”.

depreciation-3

Linking the Depreciation assumptions to the financial model

This is a bigger topic that will be covered in a separate article but the screenshot below will give you an indication of the flow of information in the Depreciation calculations worksheet of the model:

depreciation-4

Both methods (Straight Line AND Reducing Balance) are calculated for all asset categories but only the selected depreciation method flows through to the outputs. In the illustrated case above only the Straight Line values for the Depreciation Charge flows through to the Profit and Loss.

This structure would be repeated for all categories and for Tax and Accounting.
Depreciation is a key component of a financial model to calculate the tax payable. Of course it is often critical to calculate the Profit and Loss but in project finance many would argue that this has a minor importance when compared to the Cashflow Waterfall which drives all credit analysis.

Clear presentation of the depreciation inputs
Having a standard structure for the assumptions regarding depreciation can save a lot of time. The assumptions can vary slightly depending on the applications but generally you will need a combination of the following assumptions
n Asset Category
n Method of depreciation for Tax
n Method of depreciation for Accounting
n Asset life
n Multiplier (for accelerated Reducing Balance method)

One suggested structure for the layout of the depreciation assumptions for a typical vanilla project finance model is presented below.

Use Data Validation to facilitate easy updating
Some of the cells above can be coded using Data Validation which greatly simplifies the editing of assumptions from an end user’s perspective.

A user can simply point and click to choose between Straight Line Depreciation of Reducing Balance.

To set up the Data Validation, nurse click Data – > Data Validation and select Allow: List and set the Source to “SL, RB”.

Linking the Depreciation assumptions to the financial model
This is a bigger topic that will be covered in a separate article but the screenshot below will give you an indication of the flow of information in the Depreciation calculations worksheet of the model

Both methods (Straight Line AND Reducing Balance) are calculated for all asset categories but only the selected depreciation method flows through to the outputs. In the illustrated case above only the Straight Line values for the Depreciation Charge flows through to the Profit and Loss.

This structure would be repeated for all categories and for Tax and Accounting.
Building a financial model that is cost efficient to audit is a lesson that many Analysts learn the hard way when they receive a quote exceeding their annual salary.

In the worst cases this happens only a very short time before bid date / financial close and it is too late to improve the model and you have to accept what appears to be an outrageous fee for what you thought was a pretty good financial model.

What is the biggest cost-driver for a financial model audit?

The fee for a financial model audit is at large a function of the complexity and the number of unique formulas in the model. The complexity is a big topic in itself and this article will only deal with the topic of minimizing the number of unique formulas.

A unique formula in the world of financial model audits is represented by one range of cells (horizontal and/or vertical) that can be copied from left to right, order
or down the page, page
and the formula remains intact.

In an ideal world you should be able to copy the first column of your financial model (assuming a horizontal timing structure) and copy to the left and the integrity should be intact. If that is the case you have achieved perfect ‘left-right-consistency’.

How to identify unique formulas in a financial model audit?

Using spreadsheet investigation tools this work is done a lot easier. There are a number of third party tools available on the market:

  • Spreadsheet Detection
  • Spreadsheet Advantage
  • Operis Analysis Toolkit (OAK)

The outputs from these tools vary slightly but one quite useful component is what is called the ‘maps’. Theses maps identify the unique formulas to highlight left-right inconsistencies.

Image 1: Perfect left-right consistency in a financial model

Image 1: Perfect left-right consistency in a financial model

Image 2: Unique formulas (and hard-coding) scatted throughout the financial model

Image 2: Unique formulas (and hard-coding) scatted throughout the financial model

How can you keep the costs down in your next financial model audit?

The best way of keeping costs under control in a financial model audit is to work with your model auditor to bring the price down. I would recommend you to ask them to run the first iteration well in advance of a financial decision so that there is plenty of time to fix up and repair structural issues like hard-coded sections.

Another way is to simply run the maps, viagra
or ask your model auditor to do it for you (they should definitely do that for free if you have a good relationship with them) and then work through all rows/columns where there are unique cells but there shouldn’t be any. This will not only save you a lot of money, but more importantly, avoid stress on the Analyst at the last critical stage of the transaction.
Building a financial model that is cost efficient to audit is a lesson that many Analysts learn the hard way when they receive a quote exceeding their annual salary. In the worst cases this happens only a very short time before bid date / financial close and it is too late to improve the model and you have to accept what appears to be an outrageous fee for what you thought was a pretty good financial model.

What is the biggest cost-driver for a financial model audit?

The fee for a financial model audit is at large a function of the complexity and the number of unique formulas in the model. The complexity is a big topic in itself and this article will only deal with the topic of minimizing the number of unique formulas. A unique formula in the world of financial model audits is represented by one range of cells (horizontal and/or vertical) that can be copied from left to right, capsule
or down the page, prescription
and the formula remains intact. In an ideal world you should be able to copy the first column of your financial model (assuming a horizontal timing structure) and copy to the left and the integrity should be intact. If that is the case you have achieved perfect ‘left-right-consistency’.

How to identify unique formulas in a financial model audit?
Using spreadsheet investigation tools this work is done a lot easier. There are a number of third party tools available on the market:

n Spreadsheet Detection
n Spreadsheet Advantage
n Operis Analysis Toolkit (OAK)

The outputs from these tools vary slightly but one quite useful component is what is called the ‘maps’. Theses maps identify the unique formulas to highlight left-right inconstistencies.

Image 1: Perfect left-right consistency in a financial model

Image 2: Unique formulas (and hard-coding) scatted throughout the financial model

How can you keep the costs down in your next financial model audit?
The best way of keeping costs under control in a financial model audit is to work with your model auditor to bring the price down. I would recommend you to ask them to run the first iteration well in advance of a financial decision so that there is plenty of time to fix up and repair structural issue like hard-coded sections.

Another way is to simple run the maps, physician or ask your model auditor to do it for you (they should definitely do that for free if you have a good relationship with them) and then work through all rows/columns where there are unique cells but there shouldn’t be any. This will not only save you a lot of money, but more importantly, avoid stress on the Analyst at the last critical stage of the transaction.
A clear presentation of Sources and Uses (or Applications) of funds is critical for any transaction financial model, this
and in particular in a project finance transaction.

Sometimes this calculation is over-complicated with a large number of off-sheet links and complex lookups. Due to the importance of the Sources and Uses it is even more important than in other sections of the financial model to keep these calculations simple to avoid errors.

What is the link between a line summary and Sources and Uses of funds?

If the cashflow waterfall has been presented in the right way then you should be able to generate you output table of sources and uses of funds by simply calculating the line summaries.

sources-1

As illustrated in the screenshot the Sources of funds are calculated as the line summaries of the relevant cashflow lines in the financial model. Exactly the same approach would apply to calculate the uses of the funds.
A clear presentation of Sources and Uses (or Applications) of funds is critical for any transaction financial model, cheap
and in particular in a project finance transaction.

Sometimes this calculation is over-complicated with a large number of off-sheet links and complex lookups. Due to the importance of the Sources and Uses it is even more important than in other sections of the financial model to keep these calculations simple to avoid errors.

What is the link between a line summary and Sources and Uses of funds?
If the cashflow waterfall has been presented in the right way then you should be able to generate you output table of sources and uses of funds by simply calculating the line summaries.

As illustrated in the screenshot the Sources of funds are calculated as the line summaries of the relevant cashflow lines in the financial model. Exactly the same approach would apply to calculate the uses of the funds.
Preparing an Excel spreadsheet for distribution to other people requires an additional focus on presentation. This article outlines a very quick trick to significantly improve the alignment in Excel which can improve presentation from good to excellent in seconds.

What is ‘Snap to Grid’?

Excel objects (images, doctor
charts, controls, etc) are placed on top of the grid lines instead of being aligned to the grid in the same was normal cells are. The ‘snap to grid’ functionality enhances the standard Excel setting to make sure that all objects are perfectly aligned to the cell grid lines which improves the professional look and feel of your spreadsheets.

If you look carefully at the image below you will see that the graph does not align with the grid lines in the corners. Some people argue that this is only a subtle difference, but in my view this is as important as having professional presentation in your Word documents.

snap-grid-1

How do you activate ‘snap to grid’ in Excel?

Activating ‘snap to grid’ is easy. If you don’t already have the drawing menu activated the click View -> Toolbars -> Drawing and select the Drawing Toolbar.

snap-grid-2

You can now in the Drawing Toolbar select Snap -> To Grid.

snap-grid-3

If you now try and resize the graph from our first example you will note that the corners are ‘sticky’ and can only be resized in quantum steps which achieves alignment with the grid lines.

snap-grid-4

The image below is zoomed in on the chart to show the effect of ‘snap to grid’ on the corners of the graph.

snap-grid-5

Shortcut key to ‘snap to grid’

Instead of going through the Drawing Toolbar – > Snap – To Grid process, you can hold the ALT key while clicking and dragging the corners of the chart.

In a similar spirit you can also hold the Shift key while re-sizing any object in Excel to keep the height-width ratio unchanged.
Preparing an Excel spreadsheet for distribution to other people requires an additional focus on presentation. This article outlines a very quick trick to significantly improve the alignment in Excel which can improve presentation from good to excellent in seconds.
What is ‘Snap to Grid’?
Excel objects (images, more about
charts, approved
controls, etc) are placed on top of the grid lines instead of being aligned to the grid in the same was normal cells are. The ‘snap to grid’ functionality enhances the standard Excel setting to make sure that all objects are perfectly aligned to the cell grid lines which improves the professional look and feel of your spreadsheets.

If you look carefully at the image below you will see that the graph does not align with the grid lines in the corners. Some people argue that this is only a subtle difference, but in my view this is as important as having professional presentation in your Word documents.

How do you activate ‘snap to grid’ in Excel?

Activating ‘snap to grid’ is easy. If you don’t already have the drawing menu activated the click View -> Toolbars -> Drawing and select the Drawing Toolbar.

You can now in the Drawing Toolbar select Snap -> To Grid.

If you now try and resize the graph from our first example you will note that the corners are ‘sticky’ and can only be resized in quantum steps which achieves alignment with the grid lines.

The image below is zoomed in on the chart to show the effect of ‘snap to grid’ on the corners of the graph.

Shortcut key to ‘snap to grid’
Instead of going through the Drawing Toolbar – > Snap – To Grid process, you can hold the ALT key while clicking and dragging the corners of the chart.

In a similar spirit you can also hold the Shift key while re-sizing any object in Excel to keep the height-width ratio unchanged.
Most financial models contain serious errors. The challenge is to find out before you rely upon a financial model for an investment decision!

There are a number of quick checks one can apply before going into detailed analysis, remedy
and below are three spot checks you can apply within a minute:

Do assets get completely depreciated over the life of the project?
Simply calculate the total of the depreciation line over the life of the project and compare it to the total capital expenditure. In a corporate finance environment this won’t apply as easily and you will need to be a touch smarter but in a project finance model this is a good test.

Does debt get fully repaid in a downside scenario without negative cash balances?
If the model ensures that the project finance debt gets repaid even in the worst downside scenario then it is worth looking into how this is funded. It is not uncommon to see the repayment of debt being ‘funded’ by a negative cash balance which is clearly not logical. This is effectively representing the cash account as a source of capital but without the interest and margin of a true debt account.

Has escalation been applied correctly?
Create a line chart of the key cashflow items and compare the growth in the base case. If this is looking sensible then switch off (or set to zero) all escalation indexes and look at the variations of the chart. In some projects correct calculation of escalation is critical to the success of the project (PPP, seek
infrastructure, utilities) and this is a way to quickly get a sense for the importance and accuracy of the implementation.
Most financial models contain serious errors. The challenge is to find out before you rely upon a financial model for an investment decision!

There are a number of quick checks one can apply before going into detailed analysis, more about
and below are three spot checks you can apply within a minute

Do assets get completely depreciated over the life of the project?
Simply calculate the total of the depreciation line over the life of the project and compare it to the total capital expenditure. In a corporate finance environment this won’t apply as easily and you will need to be a touch smarter but in a project finance model this is a good test.

Does debt get fully repaid in a downside scenario without negative cash balances?
If the model ensures that the project finance debt gets repaid even in the worst downside scenario then it is worth looking into how this is funded. It is not uncommon to see the repayment of debt being ‘funded’ by a negative cash balance which is clearly not logical. This is effectively representing the cash account as a source of capital but without the interest and margin of a true debt account.

Has escalation been applied correctly?
Create a line chart of the key cashflow items and compare the growth in the base case. If this is looking sensible then switch off (or set to zero) all escalation indexes and look at the variations of the chart. In some projects correct calculation of escalation is critical to the success of the project (PPP, and
infrastructure, utilities) and this is a way to quickly get a sense for the importance and accuracy of the implementation.
All of us have bad habits when it comes to financial modelling. Some of us are self-taught and lack fundamental discipline and others have attended too many ‘Advanced Excel’ training course and are getting kicks out of showing off new fancy skills.

There are different levels of bad habits though, ambulance
and below is my list of the seven deadly sins of financial modelling.

  1. You have developed a custom Excel colour-scheme with a white font on black background and removed grid lines.
  2. You are using ISERROR on every line instead of figuring out why you are getting so many errors
  3. The number of named ranges in your model is greater than your number of friends.
  4. The auto-open macro disables tabs and Toolbars and navigation is possible only with your own custom-built super-navigation system
  5. The logic of your nested IF functions resemble the Amazon delta
  6. You are using Excel’s default colour scheme for graphs (who designed that anyway!?)
  7. Your balance sheet is not balancing (or worse – has a balancing item!)

All of us have bad habits when it comes to financial modelling. Some of us are self-taught and lack fundamental discipline and others have attended too many ‘Advanced Excel’ training course and are getting kicks out of showing off new fancy skills.

There are different levels of bad habits though, clinic and below is my list of the seven deadly sins of financial modelling.

1. You have developed a custom Excel colour-scheme with a white font on black background and removed grid lines.
2. You are using ISERROR on every line instead of figuring out why you are getting so many errors
3. The number of named ranges in your model is greater than your number of friends.
4. The auto-open macro disables tabs and Toolbars and navigation is possible only with your own custom-built super-navigation system
5. The logic of your nested IF functions resemble the Amazon delta
6. You are using Excel’s default colour scheme for graphs (who designed that anyway!?)
7. Your balance sheet is not balancing (or worse – has a balancing item!)
There are numerous tools and add-ins in the market constructed to enhance the ability to critically review formulas in Excel. Many of these tools build on the native functionality of Excel – trace dependents and trace precedents.

Mastering the native Excel tracking functions can make a big difference if you are not privileged to purchase one of the many tools (which I would recommend you to though if you are in the professional financial modelling space).

‘Standard’ trace precedents functionality

Most people know about the Formula Auditing toolbar and how to use it. It can be found in View -> Toolbars

trace-1

The Trace Precedents functionality highlights with blue lines which cells have been directly used to calculate the active cell.

trace-2

If you press the Trace Dependents multiple time then you get to see the precious level of precedents.

trace-3

This can of course be quite useful but it can sometimes be very slow as a large financial model for a complex project may have 10, approved
20 or even 100 levels of precedents for a high level output such as NPV or IRR.

Find anomalies (spreadsheet errors) with Trace ALL Precedents

In a small calculation module like the one above it is pretty easy to find errors, order but when looking at larger spreadsheets we need to make this process more efficient. In a formal financial model audit the independent review process includes a cell-by-cell review of every unique cell in the financial model but when working internally there may not be enough time for this process to be completed.

The original example could be expanded to an example with five orders and a calculated total as per below:

trace-4

If this had been coded correctly then every order section should have the exact same structure as Order 1 in the original example.

One way of quickly identifying structural problems in an Excel section (the method doesn’t work on off-sheet references) is to us the Trace All Precedents functionality.

Trace All Precedents – CTRL + SHIFT + [

The Trace All Precedents functionality can be used via the Go To -> Special -> Precedents -> All Levels, but it is far too slow to be useful. The only way to use this efficiently is to use the Excel shortcut CTRL + SHIFT + [

trace-5

In order for this to work you need to select a high level summary cell that is based on as much of the section you are reviewing as possible. In our example this cell is the ‘Total Order Value’ – the yellow cell with the value 1,950.

Select the yellow cell and press CTRL + SHIFT + [ and you get the following results:

trace-6

By visual inspection we can now identify that the calculation patterns or Orders 3 and 4 are different to the others. Order 4 appears to be excluded altogether and all the assumptions of Order 3 are not used.

Based upon the visual inspection we can progress with a more detailed spreadsheet review, but focus on the identified areas alone. Tracing Precedents (the traditional way) yields the following result:

trace-7

The Excel calculation errors can be identifies as

  1. The Total Order Value sum does not include all Order Values
  2. The Order Value 3 (b) is using the Volume of Order Value 3 (a)

Limitations of the Trace All Precedents functionality in Excel

This spreadsheet review functionality is very powerful if used in the right context but it is also very important to know the limitations.

  1. It doesn’t follow links to other sheets or workbooks
  2. It can only be used to detect structural problems and to specific calculation errors. A hardcoded number within a formula would remain undetected until additional methods are used
  3. It works best when used in areas where there are section summaries available (which is the case in most well-built financial models) as the reviewed are may otherwise be too large to inspect by the visual method.

Excel trace functionality – further techniques

If you have found this useful then you should definitely review the Trace All Dependents functionality and the Row Differences and Column Differences tricks (They are all available from the Go To menu but it is by using the Excel shortcuts you can make this really efficient.

The Keyboard Shortcuts are available in this Excel Keyboard Shortcuts summary from Navigator Project Finance.
There are numerous tools and add-ins in the market constructed to enhance the ability to critically review formulas in Excel. Many of these tools build on the native functionality of Excel – trace dependents and trace precedents.

Mastering the native Excel tracking functions can make a big difference if you are not privileged to purchase one of the many tools (which I would recommend you to though if you are in the professional financial modelling space).

‘Standard’ trace precedents functionality
Most people know about the Formula Auditing toolbar and how to use it. It can be found in View -> Toolbars.

The Trace Precedents functionality highlights with blue lines which cells have been directly used to calculate the active cell.

If you press the Trace Dependents multiple time then you get to see the precious level of precedents.

This can of course be quite useful but it can sometimes be very slow as a large financial model for a complex project may have 10, page
20 or even 100 levels of precedents for a high level output such as NPV or IRR.
Find anomalies (spreadsheet errors) with Trace ALL Precedents
In a small calculation module like the one above it is pretty easy to find errors, but when looking at larger spreadsheets we need to make this process more efficient. In a formal financial model audit the independent review process includes a cell-by-cell review of every unique cell in the financial model but when working internally there may not be enough time for this process to be completed.

The original example could be expanded to an example with five orders and a calculated total as per below.

If this had been coded correctly then every order section should have the exact same structure as Order 1 in the original example.

One way of quickly identifying structural problems in an Excel section (the method doesn’t work on off-sheet references) is to us the Trace All Precedents functionality.

Trace All Precedents – CTRL + SHIFT + [
The Trace All Precedents functionality can be used via the Go To -> Special -> Precedents -> All Levels, but it is far too slow to be useful. The only way to use this efficiently is to use the Excel shortcut CTRL + SHIFT + [.

In order for this to work you need to select a high level summary cell that is based on as much of the section you are reviewing as possible. In our example this cell is the ‘Total Order Value’ – the yellow cell with the value 1,950.

Select the yellow cell and press CTRL + SHIFT + [and you get the following results.

By visual inspection we can now identify that the calculation patterns or Orders 3 and 4 are different to the others. Order 4 appears to be excluded altogether and all the assumptions of Order 3 are not used.

Based upon the visual inspection we can progress with a more detailed spreadsheet review, but focus on the identified areas alone. Tracing Precedents (the traditional way) yields the following result.

The Excel calculation errors can be identifies as
1. The Total Order Value sum does not include all Order Values
2. The Order Value 3 (b) is using the Volume of Order Value 3 (a)

Limitations of the Trace All Precedents functionality in Excel
This spreadsheet review functionality is very powerful if used in the right context but it is also very important to know the limitations.
1. It doesn’t follow links to other sheets or workbooks
2. It can only be used to detect structural problems and to specific calculation errors. A hardcoded number within a formula would remain undetected until additional methods are used
3. It works best when used in areas where there are section summaries available (which is the case in most well-built financial models) as the reviewed are may otherwise be too large to inspect by the visual method.

Excel trace functionality – further techniques
If you have found this useful then you should definitely review the Trace All Dependents functionality and the Row Differences and Column Differences tricks (They are all available from the Go To menu but it is by using the Excel shortcuts you can make this really efficient.

The Keyboard Shortcuts are available in this Excel Keyboard Shortcuts (add link) summary from Navigator Project Finance.
The world of professional financial modellers have most core values aligned – simplicity, here
transparency, health
flexibility etc – but are divided when it comes to deep or shallow sheets.

As with everything else in the world there are pros and cons with both approaches.

What is a deep / shallow sheet?

A financial model is said to have a shallow tab structure when there are a smaller number of calculation rows on every tab. Some modellers work with the rule of thumb that every sheet should read like one page in a book and print on one page.

Image 1: Deep tab structure (smaller number of tabs)

Image 1: Deep tab structure (smaller number of tabs)

Image 2: Shallow tab structure (larger number of tabs)

Image 2: Shallow tab structure (larger number of tabs)

A deep sheet structure is based on a categorization of content into chapters instead of pages. Every sheet could contain thousands of rows.

Financial models with deep sheets

  • It is easy for a user to get an overview of the model structure without scrolling
  • Navigation is simple as you can see all sheets on one screen
  • There is no need to create a Table of Contents sheet or similar with hyperlinks to certain parts of the model
  • It is easy to assign responsibility for one sheet to one person
  • There is a risk that you loose transparency if Grouping is not performed successfully

Financial models with shallow sheets

  • There is a risk that excessive repetition of information is required
  • The number of off-sheet references may increase
  • With a good Table of Contents and navigation system the navigation can be very easy to use, more about
    in particular for novice users

Which structure is better?

You can’t say that one structure is definitely better. My preference is definitely to work with deep sheets but many very talented modellers do a good job with shallow sheet structure – it is just not the way I like it!

Work with Grouping to improve deep sheets

Working in a financial model where some sheets contains thousands of rows will quickly frustrate users (and make their scrolling wheel on the computer mouse red hot!). To avoid this I prefer to use the Grouping functionality.

The Grouping functionality introduces small ‘+’ symbols in the left hand column of Excel and clicking these Expands the particular section.

Image 3: A deep worksheet with Grouping

Image 3: A deep worksheet with Grouping

How to apply Grouping in Excel

Select the rows you want to group (Shift+ Space is a great Excel shortcut for this).

deep-tabs-2

Select Data -> Group and Outline -> Group

deep-tabs-3

Done!

The Excel keyboard shortcut for Grouping is Shift + Alt + Arrow Right. To Ungroup it is Shift + Alt + Arrow Left.

Recent posts by Rickard Wärnelid

Tags: ,

Comment on this Article