Goal Seek is one of the most important tool in Excel which helps in calculating the value input when the output is known. In other words, it calculates the value of input for the desired value of output.
Consider an Example, where a product of $ 50000 is bought on installment basis. The down payment made at the time of purchase was $ 10000. The remaining balance of $ 40000 has to be paid in monthly installment of $ 5000 each. Now, let us calculate the the number of months, the installment to paid back.
In cell B4 enter the below formula to calculate the number of months.
"=(B1-B2)/B3"
The number of months installment to be paid is 8.
Now, let us use goal seek to determine the change in monthly installment, if the buyer decides to pay installment in 4 months instead of 8.
1) Select Data > WhatIfAnalysis > Goal Seek>
2) Goal Seek pop up window has three fields namely set cell:, to value: and By changing Cell:
set cell:
set cell is always a cell that contains formula. In this example it is B4.
to value:
Enter 4 in to value field.
by changing cell:
As we are determining the value of monthly installment for which the the no. of months is 4, enter B3 in this field and click OK.
3) Goal Seek determines the monthly installment value and populates the same as shown below.
Note: Goal seek does not allow the changes to multiple cells simultaneously. Use solver AddIn to set criteria for multiple cells (or multiple variables).
Consider an Example, where a product of $ 50000 is bought on installment basis. The down payment made at the time of purchase was $ 10000. The remaining balance of $ 40000 has to be paid in monthly installment of $ 5000 each. Now, let us calculate the the number of months, the installment to paid back.
In cell B4 enter the below formula to calculate the number of months.
"=(B1-B2)/B3"
The number of months installment to be paid is 8.
Now, let us use goal seek to determine the change in monthly installment, if the buyer decides to pay installment in 4 months instead of 8.
1) Select Data > WhatIfAnalysis > Goal Seek>
2) Goal Seek pop up window has three fields namely set cell:, to value: and By changing Cell:
set cell:
set cell is always a cell that contains formula. In this example it is B4.
to value:
Enter 4 in to value field.
by changing cell:
As we are determining the value of monthly installment for which the the no. of months is 4, enter B3 in this field and click OK.
3) Goal Seek determines the monthly installment value and populates the same as shown below.
Note: Goal seek does not allow the changes to multiple cells simultaneously. Use solver AddIn to set criteria for multiple cells (or multiple variables).
0 comments:
Post a Comment