Shelly Cashman Excel 365 | Module 4: End of Module Project 2 Core Technology

GETTING STARTED

1. Save the file SC_EX365_EOM4-2_FirstLastName_1.xlsx as SC_EX365_EOM4-2_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file SC_EX365_EOM4-2_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Omari Carter is a human resources officer for Core Technology, a company that manufactures chips and integrated circuit boards in Oakland, California. Omari is creating a workbook employees can use to keep track of their financial interests, and he asks for your help in correcting errors and completing the financial calculations using sample data. Go to the Investments worksheet, which employees can use to track the value of their investments. Unprotect the worksheet so you can make changes to the contents.

2. Change only the color of the borders in the range B2:D4 to Gray, Accent 1, Darker 25% (5th column, 5th row in the Theme Colors palette) to match the borders in the rest of the worksheet.

3. The cells in the range D8:D19 should have defined names, but one could be clearer and one is missing. Edit the name assigned to cell D11 to use Employer_Match as the defined name, which clarifies the contents of the cell. Use Future_Value as the name assigned to cell D19 so you can use the name in formulas.

4. In cell D12, enter a formula without using a function that sums the Percent_Invested and the Employer_Match values to calculate the total percent invested.

5. Omari asks you to determine the future value of the Traditional IRA after 12 years. In cell D19, enter a formula using the FV function to calculate the future value using defined names for the rate, nper, and pmt arguments. Divide the Annual_Return by 12 to use the monthly interest rate, multiply the Years_Employed by 12 to specify the number of months as the periods, and use a negative Monthly_Total as the payment.

6. Omari also wants you to compare the future value of the IRA and the amounts employees will invest in five years, 10 years, and so on up to 30 years. In the range C22:D22, he has already entered formulas to return the future value and the invested amount in the IRA. In the range B22:D28, create a one-input data table using cell D14 as the column input cell to vary the years listed in the range B23:B28 in the formulas that calculate the future value (cell C22) and the investment amount (cell D22).

7. Next, Omari wants you to calculate the amount of the Traditional IRA at an annual return rate varying from 2 percent to 7 percent with the invested amount varying from 6.5 percent to 10 percent. He has already set up the structure for a data table in the range F8:N19, and has entered a formula in cell F8 that references the future value amount in cell D19. Start by decreasing the displayed decimal places in the range F9:F19 to show the annual return percentages with only one decimal place to match the format of the amount invested percentages.

8. Based on the range F8:N19, create a two-variable data table that uses the annual rate of return (cell D13) as the row input cell and the employee percent invested (cell D10) as the column input cell. [Mac Hint: To see the data properly, use AutoFit to change the column width of columns G through N.]

9. The Other Investments table in the range F21:N29 contains errors, and Omari thinks that correcting them will also correct the error in cell D5. Use Error Checking to determine the problem in cell N29, which should sum the numbers in the Value column, and then edit the formula to correct the error. Verify that the error in cell D5 has been corrected.

10. Use Error Checking to determine the problem in cell M23, which should subtract the target percentage from the actual percentage. Correct the error, and then fill the range M24:M28 with the corrected formula.

11. Hide the Salary worksheet since it contains data employees may want to keep private.

12. Go to the Loans worksheet, which will provide calculations for various types of loans. It now contains only a calculator for a balloon loan, a loan that has low payments for the first few years and then requires a larger one-time payment at the end of the loan term. Omari asks you to assign names to the Loan Information cells and then complete the calculations for a sample loan. In the range C4:C7, define names based on the values in the range B4:B7.

13. In cell C10, enter a formula using the PMT function to calculate the monthly payment using defined names for the rate, nper, and pv arguments. Divide the Annual_rate by 12 to use the monthly interest rate, multiply the Years by 12 to specify the number of months as the periods, and use a negative Principal as the present value of the loan.

14. In cell C14, enter a formula using the PV function to calculate the balloon payment amount using defined names for the rate, nper, and pmt arguments. Divide the Annual_rate by 12 to use the monthly interest rate, subtract the Years_until_balloon from the Years and multiply the result by 12 to specify the number of months as the periods, and use a negative Monthly_payment as the payment amount.

15. To prepare for printing the Loans worksheet, set the range A1:D15 as the print area.

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 website to submit your completed project.

Final Figure 1: Investments Worksheet

Final Figure 2: Loans Worksheet