When pivot tables appear in Excel, they are surrounded by possibilities.
Excel has a feature that can help with simple “what if” calculations. The feature is called Goal Seek and is located in the What If Analysis drop-down on the Data Ribbon tab.
Goal Seek allows you to define a target cell with a target value and then define a cell to change in order to achieve that target value.
See Figure 1 for a simple example.
Try the sample spreadsheet
The cell you change must have a calculation effect on the target cell.
The five yellow cells are all inputs and the report below uses those input cells to calculate the Profit % in cell B15.
Changing any of the five yellow cells will impact the Profit %.
Let’s assume that your target is a Profit % of 15%.
Goal Seek allows you to choose any one of the yellow input cells to modify to achieve the 15% in cell B15.
Figure 2 is modifying the Sales Volume in cell B2 to arrive at 15% in B15.
Figure 3 shows the result.
Goal Seek is simple in that you can only change one cell to achieve a target value in another cell. It is, however, quick and easy to use and can work with complex models.
Neale Blackwood CPA is CPA Australia’s resident Excel expert. He runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected].