Objective 6. Use Goal Seek

2017-11-03 09:05:06

Goal Seek is a method to find a specific value for a cell by adjusting the value of one other cell. With Goal Seek, you can work backward from the desired outcome to find the input necessary to achieve your goal. If you have a result in mind, you can try different numbers in one of the cells used as an argument in the function until you get close to the answer you want.

Activity 3.13. Using Goal Seek to Produce the Desired Result

Darron knows that his budget cannot exceed $4,500 per month for the truck loan. The trucks meet the specifications necessary to do the job and Darron has found the best price. Now he has two optionsborrow less money and get fewer trucks, or extend the time to repay the loan. To find out how much can be borrowed for three years to stay within the budget, or how much to increase the repayment period, you will use the Goal Seek command.

1.	Click cell B5. From the Tools menu, click Goal Seek. In the Set cell box, confirm that B5 is displayed. The cell address in this box is the cell that displays the desired result.

2.	Press . In the To value box, type the payment goal of 4500.00 Press . In the By changing cell box, type b2 which is the amount of the loan, and then compare your dialog box with Figure 3.30. Alternatively, you can click cell B2. Figure 3.30.
3.	In the displayed Goal Seek dialog box, click OK. In the displayed Goal Seek Status dialog box, click OK. Excel's calculations indicate that to achieve a monthly payment of $4,500.00, Darron can borrow only $145,739not $180,000.
4.	Click A7. Type Option #1 - Reduce the Loan and press . Click A7 and change the font to Arial Black and the font size to 12. Merge and center the title across A7:B7.

5.	Select the range A2:B5 and click Copy . Click cell A8, and then from the Edit menu, click Paste Special. In the Paste Special dialog box, under Paste, click Values and number formats, and then click OK. Save your workbook, click anywhere to deselect, and then compare your worksheet with Figure 3.31. Figure 3.31. Recall that with the Paste Special command, you can copy the value in a cell, rather than the formula, and the cell formats are retained. More Knowledge: Savings Accounts Using the Future Value Function Another commonly used financial function, similar to the PMT function, is the Future Value function. The function has three required arguments: Rate, Nper, and Pmt. The Rate argument is the interest rate paid to you by the financial institution; the Nper is the number of periods; and Pmt is the amount you deposit into the account each period. The function also has two optional argumentsPv and Type. The Pv argument is the amount you start with in the account. Excel assumes this is zero if you do not provide a starting amount. The Type argument assumes that the payment is made at the end of the time period.

Activity 3.14. Using Goal Seek to Find an Increased Period

For Darron's truck purchases, an alternative to borrowing less moneywhich would mean buying fewer truckswould be to increase the number of years of payments.

1.	In cell B2, type 180000 and then press to restore the original loan amount. Click cell B5. From the Tools menu, display the Goal Seek dialog box.
2.	In the Set cell box, confirm that B5 is displayed. Press . In the To value box, type 4500.00 Press . In the By changing cell box, type b3 which is the number of years for the loan, and then compare your dialog box with Figure 3.32. Figure 3.32.

3.	Click OK two times to close the two dialog boxes. Excel's calculations indicate that by making payments for 3.8 years3.806819743a monthly payment of $4,500.00 is achieved.
4.	Click A13. Type Option #2 - Increase Years and press . Click A7. On the Standard toolbar, click the Format Painter button , and then click cell A13.
5.	Select the range A2:B5, click Copy , and then click cell A14. From the Edit menu, display the Paste Special dialog box, and then paste the Values and number formats.
6.	Click B15. On the Formatting toolbar, click the Decrease Decimal button until the number of decimal places is two. Click cell B3. Type 3 and press to restore the original value. Compare your worksheet with Figure 3.33. Figure 3.33.
7.	Display the Page Setup dialog box. Center the worksheet Horizontally and create a Custom Footer with the file name in the left section. Close the dialog box and Save your workbook.

[Page 821 (continued)]

Objective 6. Use Goal Seek

Objective 7 Create a Data Table

Категории