• Open the file SC_EX19_EOM4-2_FirstLastName_1.xlsx, available for download from
  the SAM website.
• Save the file as SC_EX19_EOM4-2_FirstLastName_2.xlsx by changing the “1” to a
  “2”.
  o If you do not see the .xlsx file extension in the Save As dialog box, do not type it.
  The program will add the file extension for you automatically.
• With the file SC_EX19_EOM4-2_FirstLastName_2.xlsx still open, ensure that your
  first and last name is displayed in cell B6 of the Documentation sheet.
  o If cell B6 does not display your name, delete the file and download a new copy
  from the SAM website.

PROJECT STEPS

1. Madhu and Suvo Banerjee have investments that they maintain in an Excel workbook,
   and want to track the amount they invest in a retirement account and the varying rates
   of return they receive on the account. They also want to analyze their portfolio of other
   investments. Suvo has asked for your help in creating an investment analysis that he
   and Madhu can use to keep track of their accounts and forecast the balance they can
   expect in 30 years.
   Go to the Dashboard worksheet, which provides an overview of the Banerjees’
   investments. The charts and the value in cell F2 will be updated when you modify the
   other worksheets in the workbook. For now, change the color of the outside borders of
   the range B2:I5 to Blue-Gray, Accent 1 (5th column 1st row in the Theme colors palette)
   to match the borders of the charts.
2. Add a data table with legend keys to the Target and Actual Asset Allocation bar chart to
   include a table of percentage values for each bar. Remove the chart legend, which
   duplicates information provided in the data table.
3. Add a Blue-Gray, Accent 1 outline (5th column 1st row in the Theme colors palette) to
   the plot area of the Target and Actual Asset Allocation bar chart to more clearly define
   the plot area.
4. Go to the Traditional IRA worksheet. Unprotect the worksheet so you can make changes
   to the contents.
5. The cells in the range D3:D14 have defined names, but one could be clearer, another is
   unnecessary, and one is missing. Delete the Monthly_Contribution (cell D10) defined
   name because it will not be used in a calculation. [Mac Hint: Delete the existing defined
   name “Match” and add the new defined name.] Edit the name assigned to cell D6 to use
   Employer_Match as the defined name, which clarifies the contents of the cell. Use
   Future_Value as the name assigned to cell D14 so you can use the name in formulas.
6. Apply defined names into the Total Percent Invested formula (cell D7) to sum the
   Percent_Invested and the Employer_Match values to clarify which values are being
   totaled.
7. Suvo wants to know the future value of the Traditional IRA after 12 years. In cell D14,
   insert a formula using the FV function and defined names. For the rate argument, divide
   the Annual_Return by 12 to find monthly rate. Multiply the Years_Employed by 12
   to provide the number of periods, and use a negative Monthly_Total as the payment.
8. Suvo also wants to compare the future value of the IRA and the amounts he will invest
   in five years, 10 years, and so on up to 30 years. In the range C17:D17, he has already
   entered formulas to return the future value and his invested amount in the IRA.
   In the range B17:D23, create a one-input data table using cell D9 as the column input
   cell to vary the years listed in the range B18:B23 in the formulas that calculate the
   future value (cell C17) and the investment amount (cell D17).
9. Next, Suvo wants to calculate the amount of the Traditional IRA at an annual return rate
   varying from 3.5 percent to 13 percent with his invested amount varying from 6 percent
   to 9.5 percent. He has already set up the structure for a data table in the range F3:N23,
   and has entered a formula in cell F3 that references the future value amount in cell D14.
   Start by decreasing the displayed decimal places in the range F4:F23 to show the annual
   return percentages with only one decimal place to match the format of the amount
   invested percentages.
10. Based on the range F3:N23, create a two-variable data table that uses the annual rate
    of return (cell D8) as the row input cell and the employee percent invested (cell D5) as
    the column input cell.
    [Mac Hint: To see the data properly, use AutoFit to change the column width of columns
    G through N.]
11. To prepare for printing the Traditional IRA worksheet, select row 2 as the row containing
    print titles for the worksheet. Select the range B2:D14 as the print area.
12. Hide the Salary worksheet, which contains data Suvo wants to keep private.
13. Go to the Portfolio worksheet, which contains errors in cell F10 and in the range H4:H9.
    Use Error Checking to determine the problem in cell F10, and then edit the formula to
    correct the error. Trace the precedents to the formula in cell H4, which should subtract
    the target percentage from the actual percentage. Correct the error, and then fill the
    range H5:H9 with the corrected formula.
14. In the range I4:I9, apply the Green-White Color Scale conditional formatting rule to
    compare the investment values and highlight the highest ones. Return to the Dashboard
    worksheet to verify that it now contains updated data.