23.4 One- and Two-Way Tables

Let us take a minute to review one-way and two-way tables. Again, we will run through an example to illustrate this feature of Excel. As background for the example, we are an Australian gold mining company using the Excel worksheet to take a bunch of inputs in order to calculate our profit. We are primarily processing ore, but as we do that, we are able to extract an amount of gold with each ton of ore that we find, which increases our revenue. We call that the gold grade. Gold prices go up and down, so we specify that as well as a different input value. We also will want to record the exchange rate from Australian dollars to US dollars. It also costs a certain amount to mine and process the ore per ton, so those are recorded and together sum up our costs of production. In all, these inputs make up the revenue and costs by which we can determine our profit. Furthermore, there are formulas to determine costs, revenue, and profit, but those formulas are given to us, so we will not adjust, change, or dive into those. Our one-way table will evaluate gold grades from 1.4 to 1.85 with units of grams of gold per ton. We also simplified by dividing the profit by one million so that it is easier to understand and see the differences.

To fill in the values of the one-way table, first we select the two-column table that has been highlighted in green and blue here. Then, we go to Data in the ribbon at the top, go to What-If Analysis, and select Data Table. Next, we have to decide what varies as we go across the row. Well, nothing varies—it is just a simple formula. So, we will leave the Row Input Cell option blank. In our case, it is the column that varies as it goes from 1.4 to 1.85, so it is the only parameter we will specify. To reiterate, as we go down the column, the gold grade varies. So, what would we put in as the Column Input Cell? This represents what we would substitute the cell for. Cell C6 is the gold grade, and this is the parameter we would enter. We would want to make sure that the blank says $C$6 with the $ to reference the same cell in each row. Then, we hit OK.

Two things may have occurred. Either the numbers changed, and it looks like it worked, or nothing changed, and it may look like it did not work. If the latter is the case, we need to change the calculation options. Go to the Data tab in the ribbon and go to Calculation Options. If it says, “Automatic except for data tables,” then we can click “Automatic,” hit F9, or click the Calculate Now option just to the right of the Calculation Options icon. And then you can see that it updates. With this complete, we can see from the graph that we break even at about 1.55 per ton. If the gold grade is different from the null value of 1.68, the impact on profit is statistically significant.

That was just a single value, but we can do it with two values as well. If we take a look at the same scenario, we can choose to vary both our gold grade and our exchange rate. The formulas for total cost, revenue, and profit remain the same by pulling from the same parameters in ore tonnes, gold grade, gold price, exchange rate, mine unit cost, and process unit cost.

With two-way tables, we put the formula in the top left corner. Next, we go to Data, then to What-If Analysis, then to Data Table. In the pop-up, we must determine which variable will vary, in which direction it will vary, and the source of the variable to place as our input cell. Exchange rate varies by row, and it is stored in cell $C$9, so that is what we would put in as the Row Input Cell. Gold grade again varies by column, so we would put $C$6 in as our Column Input Cell. After clicking OK, if the numbers are all the same and uncalculated, we need to repeat the calculation options that we learned in the one-way tables. As a reminder, go to the Formulas tab in the ribbon, go to Calculation Options, and then click the icon to the right that says Calculate Now, hit F9, or change the calculation options to “Automatic” rather than “Automatic except for data tables.” Looking at the completed table, we can easily see what combinations of gold grade and exchange rate will return a profit.