Shelly Cashman Excel 2019 Module 4: End of Module Project 2 Banerjee Family CREATE AN INVESTMENT ANALYSIS GETTING STARTED Open the file SC_EX19_EOM4-2_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought3

Share With

Shelly Cashman Excel 2019 Module 4: End of Module Project 2

Banerjee Family

CREATE AN INVESTMENT ANALYSIS

GETTING STARTED

• 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”.
  • 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.
  • 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 (cell D8) by 12 to find monthly rate. Multiply the Years_Employed (cell D9) by 12 to provide the number of periods, and use a negative Monthly_Total (cell D13) 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.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Dashboard Worksheet

Final Figure 2: Traditional IRA Worksheet

Final Figure 3: Portfolio Worksheet