Last week I covered a brief introduction to the Data Table functionality within Excel and also included a simple “how-to” get started as well. This week, I’m going to cover how we can make Data Tables work with multiple independent (changing) variables.
You can read last week’s Fuzzy Friday article here, but I also thought I’ll briefly cover the basics before we move onto the more advanced stuff.
Let’s assume you’re helping an avid property investor who wants to make another property purchase to add to his current portfolio. He wants to know how his Net Monthly Income and his savings will look like once he purchases the property. You’ve setup a model which calculates his savings and his Net Monthly Income based on the house value. He keeps changing the house value when he sees a property he’s considering, but since you had a read of our Fuzzy Friday article last week, you’ve helped him setup this Excel model – which indicates the Savings and Net Monthly Income for a variety of house values. It also gives him a chance to compare what his position would be for various options. I’ve included a screenshot below and uploaded the file here as well. Excel Data Table Example
Brief look back to one variable Data Table
Since Data Tables can only work with one (or two changing variables), you’ve setup the sheet so that he can change the Value of the house he’s thinking of buying in cell C12, marked in Light Yellow. The Data Tables for savings and Net Monthly Income are in columns E,F and H,I. The cells marked Red (F12 and I12) contain the formula for each Data Table, while E13-E16 and H13-H16 contains the values to be considered. As mentioned last week, we select these tables and indicate to the Data Table function that cell C12 (the House Value input cell) is the Column Input Cell that will be changed. Prior to setting up the Data Table, check to ensure that changing your input cell changes the value in the formula cell for the Data Table. This ensures the link exists between the two cells and that the correct values will be calculated in the Data Table.
More variables?
So in our example for this article, if the investor wants to change multiple variables, how do we setup the model for such a requirement? For example, he wants to change the House Value, Deposit %, Loan Rate and Loan Term. To do this, we’re going to need the help of the OFFSET function.
In summary, the OFFSET function allows you to define a reference cell (zero point) and then refer to cells relative to that reference cell. The function returns the values in the cell referenced by the function. The key arguments the OFFSET function needs is the reference cell and the number of rows and columns it needs to “move away” from the reference cell to get the value you require. In our use, we will simply get the OFFSET to move along one column for each variable, but get values from different rows. If you want to read up on the OFFSET function, the Microsoft site has more information
Looking at the screenshot of the multi-variable Data Table model, rows 5-10 contain the various scenarios being modelled where each row is a unique scenario. Columns C-F list the House Value, Deposit %, Loan Rate and Loan Term for each scenario. The cells marked in Green include the Data Tables – for easy comparison of the final results to the variables used for each scenario. We’ve also included a new input cell in H13. This is the Offset value.
Also changed, are the variables which need to be modified. Instead of being inputs, these variables now contain the OFFSET function.
If we take the House Value for example, the OFFSET function uses cell C5 as the reference (or starting cell) while H13 provides the number rows the function should “move down”. If H13 were to be 0, then cell C22 (the House Value) will equal cell C5 – $700,000. If the Offset value (H13) were to be changed to 2, then the returned value will be two cells below C5 – C7 which is $750,000. Similarly, Deposit %, Loan Rate and Loan Term are all changed to contain the OFFSET function which returns the value based on the number entered in cell H13. If your model is setup accurately, when changing the Offset Value, you should see the model variables and results change appropriately.
It is by using the Offset Value in the Data Table, that we can achieve Data Tables for multiple variables. The setup of Data Tables for each measure being tracked is the same, but in this instance the Column Input Cell will be cell H13 which is the Offset Value cell. Matching the Offset value cell, column G and column I of the Data Table, will provide the changing variable for the Data Table – which is now the Offset value.
As mentioned earlier, Data Tables seem to be the ideal way of creating one model and then being able to track the output of that model for various scenarios. While changing multiple variables help you create many scenarios, changing one variable also helps with sensitivity analysis.
Please feel free to contact us if you have any questions about what we have covered these last two weeks or if you want to learn more about how we can help improve your operations!
About the author: Yohan Fernando is the Manager – Systems & Data Science at Fuzzy LogX who are the leading warehouse, logistics, and process improvement consultants in Australia. Fuzzy LogX provide project management & consulting services, leading-edge data analytics, process improvements, concept design & validation, solution/software tendering, implementation and solution validation services to businesses with Storage & Distribution operations looking to improve their distribution centres.