One of the best forecasting tools in Excel 2013 is the Goal Seek feature. Learn where to access and ways that you can use this What-If Analysis option.
Excel 2013’s Goal Seek tool uses existing formulas and data to calculate required future performance to meet a specified goal. As an example, say you have sales data for the first three quarters of a startup business and wanted to know how much in sales you need in the fourth quarter to reach $50,000 for the year.
This one’s actually pretty easy, but it illustrates the mechanism behind Goal Seek. You can also use Goal Seek to project totals that are more complicated, counts, rates, etc.
1. Select the Data tab, click What-If Analysis and choose Goal Seek.
2. Enter the cell reference containing the formula for calculating the goal in the Set Cell field and the projected value in the To Value field.
In the example, you are trying to calculate what’s needed in the fourth quarter to reach a goal of $50,000 total for the year. Therefore, Set Cell needs to reference cell F7 (or $F$7, it doesn’t matter in this example), because F7 is where the year’s total is calculated.
The Set Cell field must contain a calculable formula that references the projection cell (that is, the cell referenced in the By Changing Cell field) for Goal Seek to work.
3. Enter the target reference in the By Changing Cell field and click OK. This target reference should be the cell whose value you’re anticipating to reach a particular goal.
In this example, you’re trying to calculate the total fourth quarter value needed to reach your annual goal of $50,000, so cell E7 is the cell to be changed.
Note that this cell must either be blank or contain a value. It cannot contain a formula, even if that formula is valid. So in this example, the total 4th quarter calculation had to be changed to zero (or deleting the formula entirely would also work). This cell must also be referenced in some way by the goal calculating formula, i.e., the cell referenced by the Set Cell field.
4. Click OK to accept the new values in the cells referenced by the Set Cells and By Changing Cell fields. Alternatively, if you’re already satisfied with the projection, click Cancel to keep the original data in the referenced cells.
Even before clicking OK or Cancel, you can see that $7,060 is required in the 4th quarter to reach your goal of $50,000 for the year. If this is all you needed, click Cancel to return the values back to their original values. Otherwise, clicking OK keeps the values as you see above.
