The auto recalculation power of Excel provides another feature - What-if -Analysis. For example, you enter the values in two cells and the formula to add these values in the third cell. Any change in the values of the first two cells will change the result in the third cell.
Thus, we can say that, what-if-analysis means “What would the result be, if the cell value changes”. Creating Data Tables is one way of analysing the data. Other tools available for what-if-analysis are :
Goal Seek - determine the input required to produce the desired result.
Often you know the result you want a formula to return but not the input values the formula needs to reach that result. To solve such a formula, you can use goal seeking. With this, MS-Excel varies the value in a cell you specify until a formula dependent on that cell returns the result you want.
Goal Seek saves you from performing time-consuming trial-and-error analysis.
To seek a specific solution to a formula
1. Select the result for which the trail and error method applied to achieve desired result
2. Goto Data ribbon; Data tools ; What if analysis select the Goal seek
3. In the Set cell box, enter the reference or the name of the cell containing the formula for which you want to find a specific solution.4. In the To value box, enter the value you want as the result.
5. In the By changing cell box, enter the reference of the cell containing the value to be changed in solving for the result that you want
6. Choose the OK button.
| Selling Price | 1000 |
| Qty | 100 |
| Cost Price | 800 |
| Revenue | 100000 |
| Cost | 80000 |
| Overheads | 10000 |
| Operating profit | 10000 |
| Operating profit % | 10% |
In the above example we can use the Goal seek fuction to find the selling price which will yeild the 25% operating profit.
No comments:
Post a Comment