Downloads
Click buttons to view a downloadable, macro-free Excel file
Data Table Magic
Method for pushing multiple variables through a data table
This content mirrors that of ‘Data Table Magic’ in the Blog section. It’s purpose is to demonstrate a technique for effectively sensitising unlimited variables using a data table.
Excel provides functionality for one and two-way data tables – i.e. a specific result or output can be calculated many times given varying assumptions on the value of one or two of the variables in its calculation. Sometimes sensitising a maximum of two variables is insufficient to test an output for the purposes of decision-making.
To overcome this we can take an approach of creating scenarios, with different values for the same three or more variables in each. We can assign a scenario number for each scenario and then the model can ‘look up’ the values of the variables for a selected scenario and push them through the model. We can create a one-way data table with the variable tested being the scenario number. In the background Excel is cycling through each of the scenario numbers, looking up its variables, calculating them in the model and then displaying the result in the data table.
The associated file demonstrating this technique is Downloadable.
In Excel Online select 'Download', or [ . . . ] near the top right corner of page, or File - Save As.
INDEX and MATCH functions
Explanation, examples and optimisation of INDEX and MATCH in modelling
This content explains in detail the INDEX and MATCH functions and their application in looking-up data. It also introduces the technique of using Helper Cells to optimise the lookup calculation process.
Excel provides a range of functions to lookup and return data in different ways. The most commonly used are VLOOKUP, INDEX, OFFSET and CHOOSE. Whilst CHOOSE can have some useful functionality in scenario calculations, VLOOKUP and OFFSET should be replaced by INDEX wherever possible.
INDEX works by referencing an array of data and specifying a row and/or column coordinate in that array to return a single cell’s result. MATCH, a reference function, is often used in conjunction with INDEX to determine what the row and column coordinates are. Most commonly MATCH is embedded within the INDEX function calculation.
As a modeller advances their best practice modelling skills, MATCH is often separated out from the INDEX calculation and housed in a ‘helper cell’ from where it is referenced in the INDEX calculation – this breaks down calculations, reduces the number of calculations and increases the transparency of the model.
The associated file demonstrating INDEX and MATCH is Downloadable.
In Excel Online select 'Download', or [ . . . ] near the top right corner of page, or File - Save As.
Data Table Magic
Method for pushing multiple variables through a data table
This content mirrors that of ‘Data Table Magic’ in the Blog section. It’s purpose is to demonstrate a technique for effectively sensitising unlimited variables using a data table.
Excel provides functionality for one and two-way data tables – i.e. a specific result or output can be calculated many times given varying assumptions on the value of one or two of the variables in its calculation. Sometimes sensitising a maximum of two variables is insufficient to test an output for the purposes of decision-making.
To overcome this we can take an approach of creating scenarios, with different values for the same three or more variables in each. We can assign a scenario number for each scenario and then the model can ‘look up’ the values of the variables for a selected scenario and push them through the model. We can create a one-way data table with the variable tested being the scenario number. In the background Excel is cycling through each of the scenario numbers, looking up its variables, calculating them in the model and then displaying the result in the data table.
The associated file demonstrating this technique is Downloadable.
In Excel Online select 'Download', or [ . . . ] near the top right corner of page, or File - Save As.
INDEX and MATCH functions
Explanation, examples and optimisation of INDEX and MATCH in modelling
This content explains in detail the INDEX and MATCH functions and their application in looking-up data. It also introduces the technique of using Helper Cells to optimise the lookup calculation process.
Excel provides a range of functions to lookup and return data in different ways. The most commonly used are VLOOKUP, INDEX, OFFSET and CHOOSE. Whilst CHOOSE can have some useful functionality in scenario calculations, VLOOKUP and OFFSET should be replaced by INDEX wherever possible.
INDEX works by referencing an array of data and specifying a row and/or column coordinate in that array to return a single cell’s result. MATCH, a reference function, is often used in conjunction with INDEX to determine what the row and column coordinates are. Most commonly MATCH is embedded within the INDEX function calculation.
As a modeller advances their best practice modelling skills, MATCH is often separated out from the INDEX calculation and housed in a ‘helper cell’ from where it is referenced in the INDEX calculation – this breaks down calculations, reduces the number of calculations and increases the transparency of the model.
The associated file demonstrating INDEX and MATCH is Downloadable.
In Excel Online select 'Download', or [ . . . ] near the top right corner of page, or File - Save As.
