Simple Data Table - One Input One Output
- 02:12
Learn the steps to build a one input, one output data table
Transcript
You can use data tables for any calculation involving input assumptions. So the first thing to do, is to calculate an answer that's dynamically linked to assumptions, which means when we change the assumptions, the value will change. So I'm going to calculate revenue first of all, which is price multiplied by demand. I'm then gonna calculate the variable cost, which would be the unit cost multiplied by demand and then I can get the profit by taking the revenue less the fixed cost, less the variable cost. So that's my profit number, 57,950. Now, what we want to do is see what happens when the price changes. So what if the price, what if the sales price was only $3.50, or what if it was $4.50? We wanna get a good range of profitability based on these different prices, these different price inputs. So what we're going to do there, is use a data table to help us. So for this to work, we need to link the profit into this cell here. We've got our range of prices along the left-hand side and then we can select all of these cells. So including this cell here that just says price in it. There's nothing has to be written in that one, but you do have to have all of your variable inputs in these cells here and you have to have the output that you want to sensitize in this cell here. Okay, let me select the range again, oops. There we go. The shortcut, the easiest shortcut to use for this is ALT + D + T. Okay, there is no row input cell in this simple example, we've just got a column input cell, and our column input cell is going to be the price that we want to sensitize. So link that in and hit OK. Now, if you go back down to the table, you'll see now how the profit is affected by different sale prices. So the lower the price intuitively, the lower the profit. The higher the price, the higher the profit. And it's conventional to include your original assumption in the middle of your price range so you can see that you've got the 57,950 profitability displayed here next to the $4 price.