This past week, I’ve been working on modelling picking operations for a customer. Usually, my preference is to setup a model using Python, but to make the model more accessible and editable by anyone, the obvious choice was Excel. However, as part of this model, I needed the ability to change multiple variables, create multiple scenarios and compare the results of each of these. While I can easily set something up in Python, how do I do it in Excel?
Yes, I can keep a note of the values I change and copy the results of each combination of values, or I can create multiple copies of the sheets of my model where each set of copies is a scenario. But surely there’s a better way to achieve that?
Data Table
The Data Table is a tool located on the Data tab of the main ribbon of Excel. Within that tab, it’s located under the What-If Analysis button.
In brief, the Data Table allows you to provide a list of values and for that list, it will provide the answer to the equation you have provided. For simple equations, this will be as easy as creating a table with multiple values of the independent variable(s) (the values that change) and then performing the required operations on the values to get the answer for the respective combination of values (the dependent variable). For multiple scenarios, you can create multiple rows with different values of the independent variable(s) that produce the answers.
If we’re interested in a simple example of 2x + 10, the above can be achieved using the example shown below.
The real power and use case for the Data Table is in complicated models where it’s not easy to define the underlying model in one equation and hence can’t use a simple table to investigate your scenarios. Imagine you have a model spanning multiple sheets and your objective is to check the sum of two columns on each sheet. Depending on your situation, it may be difficult and troublesome to convert this model manually into a format that will allow you to create an entire table of values driving the underlying equation. Therefore, this is an ideal use case for the Data Table functionality.
How-to
To keep things simple, I’ll cover how to create the table for the example equation included above using the Data Table in Excel. Obviously, if your scenarios are as simple as this, you don’t need to use Data Table, but you can apply the same principles for your situation.
Referring to the screenshot above, cell C1 contains the equation of interest, while the value in cell A1 drives the value in cell C1.
In your own model, the value in cell C1 (or as applicable) should change when changing the value of A1. The link between A1 and C1 needs to exist for the Data Table to work.
Cells B2 to B12 are the varying values for which you want to calculate the answer to the equation. Again here, it’s important that the equation is located to the top-right of the column of values. (as shown in the screenshot above)
To setup a Data Table, select all the cells from B1 to C12.
Then select the Data Table function from the What-If Analysis button. Once presented with the Data Table box, select the Column Input Cell as cell A1.
Click Ok and you’ll be presented with a table of the results for your equation for each value of the independent variable.
It’s important to note that so long as the link between the equation cell (in this case C1) and the column input cell (A1) is maintained, you are free to alter the equation as required. The values in the table will automatically update. You are also free to create as many data tables as you wish on one sheet.
Unfortunately, Data Table only works with one or two variables. I.e. if you want to change more variables in your model, Data Table doesn’t allow you to do so directly and you may need to use the Scenario Manager (also located in the What-If Analysis button). But this is also limited to 32 variables and is quite tedious when setting up each scenario and its respective values.
However, there is a way you can get around the variable limits for the Data Table, which I hope to cover in my next Fuzzy Friday post. Hint: you need to use the Offset function. In the meantime, if you have any Warehouse Engineering or Warehouse Analytics questions, please feel free to contact us.
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.