Learn Goal Seek Method : Most Useful Forecasting Tool
Example with detailed explanation, on request from Mr Karthi Keyan
How Goal Seek Works.
Goal Seek feature allows you to alter the data used in a formula in order to find out what the results will be. The different results can then be compared to find out which one best suits your requirements.
To use Goal Seek, set up your worksheet to contain the following:
>> A formula that calculates your goal (for example, a formula that calculates the total Budget/Expense)
>> An empty variable cell for the unknown number that will produce the desired outcome (for example, a cell to hold the number of Items you can buy with $300)
>> Values in any cells (other than the empty cell) that the formula refers to (for example, a cell that stores the price of a item)
=> The empty cell should be referenced in your formula; it serves as the variable that Excel changes.
To forecast using the Goal Seek command, follow these steps:
>> Create a worksheet that contains a formula, an empty variable cell that will hold your solution, and any data you need to use in your calculation.
For example, the screenshot shows how you might set up a worksheet to determine the number of items priced at $2.75 that you could buy with gross $300.
1 The variable cell must be blank
2 The formula cell determines the value in the variable cell
>> In your worksheet, select the cell containing the formula.
>> On the Data menu, click What-If Analysis
Select Goal Seek.
The Goal Seek dialog box opens.
The dialog box asks you to supply three variables, “Set cell1 to value by changing cell2.” The cell name you selected before you chose the command will appear in the first box, and a marquee will appear around the cell in your worksheet.
>> Press TAB, and then type the goal that you want to reach in the To value box.
For example, to reach $300 in Total Budget, type 300 in the To value box.
>> Press TAB to select the By changing cell text box, collapse the Goal Seek dialog box, if necessary, and then click the blank cell that is to contain your answer (the variable cell).
The Goal Seek command will calculate the value for this blank cell by using your goal in the To value text box and the formula in the cell referenced in the Set cell text box. The variable cell will be indicated by a selection marquee (cell C5 in this example):
The Goal Seek command calculates the value of this cell
>> Click the OK button to find a solution for your sales goal.
Excel will display the Goal Seek Status dialog box when the iteration is complete, and the result of your forecast will appear in the worksheet, as shown in the image. This forecast shows that you can buy 1090 items at $2.75 per item to meet your budget goal of $300.
>> Click the OK button to close the Goal Seek Status dialog box.
And you are done…!! And as said in the beginning, the different results can then be compared to find out which one best suits your requirements.
Please feel free to ask if you have any questions…