Advanced Data Tables
- 02:00
Learn advanced techniques to make your assumptions drive both the main calculation and the sensitivity variables of the data tables
Downloads
No associated resources to download.
Transcript
Here's a technique you can use if you want your assumptions to drive both the main calculation and the sensitivity variables of the data table whilst avoiding a circularity, The first thing you need to do is create some linked cells. So these copied cells here, they link directly to the price, in this case, and to the unit cost, in this case. The linked cells or the copied cells, they are used in the calculation of the profit. So the price is what's driving revenue. The unit cost is what's driving variable cost, in this case. And that is, then, ultimately driving the profits which is what links into the top left-hand side of our data table. The main assumptions can be used to drive the values on the edge of the data table that we're using to sensitize. So we've got our price, which is driving the four here, and we've got our 0.45, which is driving the unit cost here. And, within these other cells, we've just got a small calculation going on. So we're just varying the unit cost by 0.05, in this case, and we'll be varying the price by 0.25. So we just have to drive this, the cell's highlighted in yellow, and then everything else updates automatically. And this means we can change the assumptions here and here, and the edge of the data table will update automatically, as will the profit, as will the value that drives the corner cell. So finally, then, when creating the data table itself, we must use these copied cells as the inputs. So the price, the price is what we are sensitizing vertically. Okay, so that's the column input cell C5. Okay, you'll notice that this is the copied cell, not the main assumption. And, similarly, for the row input cell, the row input cell is what we are sensitizing horizontally. And that's the unit cost. And, again, this is linking across to the 0.45 in the copied cell column, not to the main assumption itself, but the copied cell.