Question

Provide an example of when the Goal Seeking function of the What If tool in Excel would be a good analysis tool to use?

Answer 1

Goal seek is used to adjust a value used in the formula to attain a specific goal. It determines input values so as to get to the target value.It helps to make decisions for future based on current data.One of the common examples is that of loan.

Figure 1 shows a simple loan calculator where all variables are known: repay a 5% loan on $100,000 over 10 years, The monthly payment must be $1,060.66.

Figure 1 Rate 5% Term Principal Payment 100000 ($1,060.66) 120

But you have kept aside $900. In this case it is better to use goal seek in order to determine the rate and term to reduce the payment. In the figure below we have set cell E3 to 900 and made B3 as variable.

F G H BC Figure 1 Rate Term Principal Payment 5% 120 100000 ($1,060.66) Goal Seek ? r Set cell: E3 To value: -900 By changing cell: B3 D OK Cancel

The resultant figure shows for 900 payment the rate should be 2%

с E F J G H Goal Seek Status I ? X Figure 1 Rate 2% Term Principal Payment 120 100000 ($900.00 Step Pause Goal Seeking with Cell E3 found a solution. Target value: -900 Current value: ($900.00) C OK Cancel

Goal seek hence provides the results ahead of time and hence helps to plan and execute in a better manner.