When the question is “What if”, the answer is a spreadsheet. That’s what they were made for: changing variables to see different results. Excel provides a seemingly little used tool called a data table that does just these types of “what if” scenarios.
This post deals with single-variable data tables. Let’s start with a couple of “constant” values that well use in our table. These values could be hard coded in the formulas that we’ll create later, but it’s nice for the user to see all the information, so we’ll put these values in cells outside the data table. We’ll also set up some column headings.
Cell A5 will be the input cell. You can use any cell, either within or outside of the data table. I generally use the cell directly above the substitution data. Enter the substitution data starting in A6. In this example I entered profit margin percentages from -2% to 10%.
The gross margin percentages is the data that will be substituted into the formula that we will be creating. It is the “what if” part as in “what if the gross margin percent was 2%”. The results of the table will be in the column next to the substitution data. To define the results, we must create a formula that includes the input cell (remember we said that A5 would be our input cell and we left it blank for that reason). The formula goes one cell up and one cell to the right of the first piece of substitution data. Since our substitution data starts in A6, the formula goes in B5.
We’ve defined the formula and the changing data. All that’s left is to define the input cell. From the Data menu, choose Table. Define the input cell when prompted and you’re done. Since our substitution data is in a column, define the “Column Input Cell” not the “Row Input Cell”. In this screen shot, I’ve selected the data table and selected Data>Table.
Click OK and the data table fills in all the answers.
Next, we can add another column to the table. We could have done this right from start, but I thought it would be more illustrative this way. Add a new column heading and a new formula to compute the net profit. The new formula will go in C5. All formulas must reference the input cell, so we have a little repetition in our formula.
Select the new expanded table and repeat the Data>Table steps defining A5 as the input cell. With a little formatting added, we can see that 6% gross margin is our break even.