Testing in Excel

When you’ve created a new calculating web page in Excel, you may want to test it already in Excel before you convert and publish it on a server. Here are a few useful tips.

Excel offers a few simple tools to enhance the value of spreadsheets. The Scenario Manager can test the calculator with a number of predefined values. With Goal Seek, you can decide the result you want and have Excel use trial-and-error looking for the perfect input data.

Scenario Manager

One of the most powerful uses for the Scenario Manager is regression testing, i.e. to ensure that you don’t introduce errors in existing functionality as you develop a complicated calculator. If the output of your formulas is essentially linear, you can probably just throw in a few values and see that the result matches your expectations, but what if the formulas have thresholds that fundamentally change the behavior of the calculator for certain value ranges? As an example, a tax calculation can use very different formulas at various levels of income. In such cases, it’s good to prepare a structured set of values to test with and to know the correct result for each value in advance.

In this example, we have a muffins bakery. At the beginning of the day, we can sell blueberry muffins at full price, with a profit of £1.00. As the day goes on, the muffins become less fresh and people start looking for other products. We then reduce the price, which lowers our profit to £0.40 per muffin.

Screenshot of the muffins profit calculator with 20% of the muffins sold at the discounted price

As you can see in the calculation above, if we sell 20% of the muffins at the discount price we have a total profit of £88.00 from blueberry muffins every business day.

Now we want to know how much our total profits from these muffins will decrease if we increase the number that is sold at a discount. To do this what-if analysis, we will use Excel’s Scenario Manager.

The Scenario Manager is started from the Data section of the ribbon, Forecasts group, What-If Analysis menu.

Screenshot of the Scenario Manager shortcut in the Forecasts group in the Data section of the ribbon

The Scenario Manager window allows you to create the scenarios you want. In our case, we will create five different scenarios with 0%, 5%, 10%, 15%, and 20% of the muffins sold at the discounted price. To add the first of the five scenarios, we click on the Add button in the Scenario Manager window.

Screenshot of the Add Scenario button in the Scenario Manager window

We give the scenario a name and select the cell that we want to assign a value to in the scenario.

Screenshot of a Scenario set up for 0% muffins sold at the discount price

When you press OK, the Scenario Manager wants to know the value to use for the changing cell C4 in the scenario. For this scenario, the value is 0%.

Screenshot of the Scenario Values window where we enter 0% for this scenario

Now repeat these steps to add the scenarios for 5%,  10%, 15%, and 20%.

Screenshot of the Scenario Manager with five scenarios for 0%, 5%. 10%, 15%, and 20% sold at the discounted price

To test the calculator with each scenario, select the scenario in the list and press Show. Excel inserts the corresponding value in C4 and calculates the result.

If you want to test all the scenarios simultaneously, click on Summary. Excel asks you which of the cells in the spreadsheet you consider the most important to save for each scenario, the Result cell. In our case, this is the Total profit in D10.

Screenshot of the Scenario Summary prompt asking for the result cell

Click OK. Excel inserts the scenario values, one by one into C4, and for each input value, it saves the result value returned in D10. The resulting table is saved as a new worksheet.

Screenshot of the Scenario Summary report with the outcome of each scenario

You may think that this was much work for something you know several ways to do much quicker. The benefit of using the Scenario Manager is that you can save as many scenarios as you like together with the spreadsheet you want to test. You can perform these tests as often as you like – every time you edit the spreadsheet, if you like – and it takes almost no time. There is hardly any manual work involved even for very extensive testing of the calculator, except during the initial setup.

Goal Seek

Simply speaking, Excel’s Goal Seek does the opposite of what the Scenario Manager does. Using trial-and-error, Goal Seek goes directly searching for the perfect value that satisfies your request, without showing you all the values that figured in-between. Like with the Scenario Manager, if the output of your formulas is essentially linear, you can probably just improvise a few values and pretty quickly navigate manually to the result that best matches your expectations.

In the complex world of real life, however, there are lots of situations where a result changes direction and suddenly starts to decrease with higher input values. As an example, this behavior is typical of workload calculations, where total productivity first increases with added load, but then starts to decrease when the subsystem is overloaded. Using Goal Seek, you could immediately find the sweet spot for the subsystem, where productivity cannot be increased further.

Using Goal Seek, we will now ask Excel to use the muffins calculator to find out how many of the muffins we can sell at the discounted price and still make 66% of the maximum profit.

The Goal Seek function is started from the Data section of the ribbon, Forecasts group, What-If Analysis menu.

Screenshot of the Goal Seek shortcut in the Forecasts group in the Data section of the ribbon

To run Goal Seek, Excel needs to know

  1. What cell you want to use as the result of the test, in our case the Total result, in D10.
  2. What value you want the result cell to contain when the goal is reached. In our case, the maximum profit from muffins is £100.00 if they’re all sold at the full price. If we’re willing to sacrifice one-third of that, the goal value is £66.00.
  3. What value in the spreadsheet you want Excel to vary until it reaches the goal, in this case, the number of muffins sold at a discount, in C4.

Enter these values into the Goal Seek prompt.

Screenshot of the Goal Seek prompt asking for the result cell, the goal value and the cell to seek with

Click OK. Excel tries different values in D10 until it reaches the goal value of £66.00 in C4 and then proudly presents the results.

Screenshot of the result of the Goal Seek operation

What everybody wants to know now is the input value that gave the goal value as a result. One day, Excel may use the Goal Seek Status window to give us this directly. Until then, we have to look in cell C4 ourselves. We now know that we can sell 57% or more than half of the blueberry muffins at the discounted price, and still make two-thirds of the maximum profit.