a. Open e09p2Retire and save it as e09p2Retire_LastFirst.
b. Ensure cell Al is the active cell in the Assumptions worksheet. Complete the following steps to fill the title and formats to the Calculations worksheet:
• Press Ctrl and click the Calculations sheet tab.
• Click Fill in the Editing group on the Home tab and select Across Worksheets.
• Click OK in the Fill Across Worksheets dialog box.
• Right-click the Calculations sheet tab and select Ungroup Sheets. Notice the formatted title in cell A1.
c. Click cell B4 in the Calculations sheet and insert a formula with 3-D references by completing the following steps:
• Type =
• Click the Assumptions sheet tab and click cell B8.
• Type - click cell B7, and press Enter in the Assumptions worksheet to calculate the number of years until retirement.
d. Click cell B5 in the Calculations sheet and insert a formula with 3-D references by completing the following steps:
• Type =
• Click the Assumptions sheet tab and click cell B6.
• Type *. click the Calculations sheet tab, click cell B4, and then press Enter to calculate the total amount contributed into your retirement account.
e. Click cell B7 in the Calculations sheet, click the Formulas tab, and then click Trace Precedents in the Formula Auditing group. Excel displays a worksheet icon with the tracer line to indicate the precedents are on another worksheet.
f. Click the Error Checking arrow in the Formula Auditing group and select Error Checking.
g. Click Show Calculation Steps in the Error Checking dialog box. The Evaluate Formula dialog box opens, showing the formula and stating that the next evaluation will result in an error. Complete the following steps:
• Click Evaluate to see the error replace the argument in the function: #DIV/0!.
• Click Step In to see the value and click Step Out to return to the evaluation.
• Repeat the Step In and Step Out process and click Close.
• Click Next in the Error Checking dialog box and click OK in the message box.
• Click the Assumptions sheet tab, click cell B12. type 12, and then press Enter.
h. Click the Calculation Sheet tab to notice that the function now calculates the monthly retirement income in cell B7.
i. Click Remove Arrows in the Formula Auditing group to remove the precedents arrow.
j. Click Watch Window in the Formula Auditing group and complete the following steps:
• Click Add Watch.
• Move the dialog boxes so that you can see the data.
• Select the range B4:B7 in the Calculations worksheet and click Add.
• Resize the dialog box so that you can see most of the details.
• Click the Assumptions sheet tab and move the Watch Window below the input area.
k. Create a data validation rule to ensure the retirement age is greater than or equal to 59.5 by completing the following steps:
• Click cell B8, click the Data tab, and then click Data Validation in the Data Tools group.
• Click the Settings tab, click the Allow arrow, and then select Decimal.
• Click the Data arrow and select greater than or equal to.
• Click in the Minimum box and type 59.5.
• Click the Input Message tab, click in the Title box, and then type Retirement Age.
• Click in the Input message box and type Federal law does not permit payout prior to 59.5.
• Click the Error Alert tab, click the Style arrow, and then select Warning.
• Click in the Title box and type Invalid Data.
• Click in the Error message box and type Age must be greater than or equal to 59.5. and then click OK.
l. Create a data validation rule to ensure the rate of return cannot exceed 6% by completing the following steps:
• Click cell B9, click the Data tab, and then click Data Validation in the Data Tools group.
• Click the Settings tab, click the Allow arrow, and then select Decimal.
• Click the Data arrow and select less than or equal to.
• Click in the Maximum box and type 0.06.
• Click the Input Message tab, click in the Title box, and then type Rate of Return
• Click in the Input message box and type The rate of return cannot exceed 6%.
• Click the Error Alert tab, click the Style arrow, and then select Warning.
• Click in the Title box and type Invalid Data. Click in the Error message box, type Rate must be less than or equal to 6%, and then click OK.
m. Adapt Step I to create a validation rule for cell B11 to ensure the rate of return during retirement will not exceed 5%. Include appropriate titles and messages.
n. Type 50 in cell B8 and press Enter. Click No when the error message displays, change the value to 60, and then press Enter.
o. Type 8.5% in cell B9. Click No when the error message displays, change the value to 6%, and then press Enter.
Login | Sign Up
