RELIABLE PAPERS ONLY PLEASE

Unit 4    [IT153: Spreadsheet Applications] 

 

Assignment Details and Rubric Outcomes addressed in this activity:

Unit Outcomes:

 Use name cell references in formulas  Use the financial functions PMT and PV to determine the monthly payment and present value

of a loan  Create a Chart

Course outcome: IT153-2: Create formulas and functions.

Watch the Assignment Tutorial here http://extmedia.kaplan.edu/IST/media/IT153/u4/IT153_u4tutorial.html The tutorial will show you the steps to take in Excel to help you complete your Assignment.

Instructions:

Analyzing Retirement Savings Problem: You have been asked by the Employee Relations and Resource department to develop a retirement planning worksheet that will allow each current and prospective employee to see the effect ( dollar accumulation) of investing a percent of his or her monthly salary over a period of years ( Figure 1 below). The plan calls for the company to match an employee’s investment, dollar for dollar, up to 2.50%. Thus, if an employee invests 5.00% of his or her annual salary, then the company matches the first 2.50%. If an employee invests only 1.75% of his or her annual salary, then the company matches the entire 1.75%. The Employee Relations and Resource department wants a one- input data table to show the future value of the investment for different years.

Unit 4    [IT153: Spreadsheet Applications] 

 

Instructions: 1. Download the Unit 4 data file from Doc Sharing and save the workbook using the file name:

Unit 4 Assignment Your Name.

2. Use the Create from Selection button (Formulas tab | Defined Names group) to assign the row titles in column B (range B4: B9) as cell names for the adjacent cells in column C and the row titles in column D (D5: D8) as cell names for the adjacent cells in column E5: E8. Use these newly created names to assign formulas to cells in the range E5: E8. Step 6e formats the displayed results of the formulas:

a. Employee Monthly Contribution ( cell E5) = Annual_ Salary * Percent_ Invested / 12. b. Employer Monthly Contribution ( cell E6) = IF( Percent_ Invested < Company_ Match,

(then)Percent_ Invested * Annual_ Salary / 12,(else)Company_ Match * Annual_ Salary / 12) .

c. Total Monthly Contribution ( cell E7) = SUM( E5: E6). d. Future Value (cell E8)=-FV(Annual_Return/12,12*Years,Total)

The Future Value function (FV) in Step 6d returns to the cell the future value of the investment. The future value of an investment is its value at some point in the future based on a series of payments of equal amounts made over a number of periods earning a constant rate of return.

e. If necessary, use the Format Painter button (Home tab | Clipboard group) to assign the Currency style format in cell C5 to the range E5: E8.

Unit 4    [IT153: Spreadsheet Applications] 

 

3. Use the concepts and techniques developed in this chapter to add the data table to the range

B11: F22 as follows:

a. In cell C12, enter = Future Value as the formula. In cell D12, enter = 12 times Employee Contribution times Years as the formula (recall that using cell references in the formulas means Excel will copy the formats). In E12, enter = 12 times Employer Contribution times Years as the formula. In F12, enter = 12 times Total Contribution times Years as the formula.

b. Use the Data Table command on the What- If Analysis gallery (Data tab | Data tools group) to define the range B12: F22 as a one- input data table. Use cell C9 as the column input cell.

4. Use the Conditional Formatting button (Home tab | Styles group) to add an orange pointer that shows the row that equates to the years in cell C9 to the Years column in the data table. Change the sheet tab name and color to light green as shown in Figure above.

5. Remove gridlines by clicking View (Page Layout tab | Sheet Options group).

6. Change the worksheet header with your name (Right side), course number (left Side), set it for Landscape and make sure it fits on a single page (Use print Preview).

7. Unlock the cells in the range C4: C9. Protect the worksheet. Allow users to select only unlocked cells.

8. Name the Worksheet Retirement Planning, Color the tab (your choice of color) and Save your changes to the workbook.

9. Make 3 copies of the worksheet, name the new worksheets Data Set 1, Data Set 2 and Data Set 3 and change the Tab colors to Blue, Green and Red.

10. Determine the future value for the data in Table 2 below for each of the worksheets. The following Future Value results should be displayed in cell E8: Data Set 1 = $ 395,756.16; Data Set 2 = $ 693,470.35; and Data Set 3 = $ 1,069,822.41.

Table 2: Future Value What- If Analysis Data

Data Set 1 Data Set 2 Data Set 3

Employee Name John Roe Dante Dacy Janek Madhu

Annual Salary $ 119,500.00 $ 65,000.00 $ 39,000.00

Percent Invested 2.50% 5.00% 6.00%

Unit 4    [IT153: Spreadsheet Applications] 

 

Company Match 2.50% 3.00% 2.00%

Annual Return 4.75% 6.50% 7.25%

Years 30 35 45

11. Delete any blank worksheets, Save and Submit the assignment to the Unit 4 Dropbox.

Unit 4 Assignment grading rubric = 50 points

Assignment Requirements Points possible

Points earned

Use the Create from Selection button on the Formula tab of the Ribbon to assign the row titles to the adjacent cells.

0 - 6

Formulas are created using the Names defined. 0 - 10

Use the Data Table command on the What-If Analysis gallery on the Data tab on the Ribbon to define the range B12:F22 as a one-input data table. Use cell C9 as the column input cell.

0 - 5

Use the Conditional Formatting button on the Home tab on the Ribbon to add an orange pointer that shows the row that equates the years in cell C9 to the Years column in the data table. Use a white font color for the pointer.

0 - 5

Unlock the cells in the range C3:C8. Protect the worksheet. Allow users to select only unlocked cells.

0 - 5

Rename worksheet as Retirement Planning and Change color of Tab. Delete any unused worksheets.

0 - 2

Create 3 copies of the Financial Analysis worksheet into the workbook. Rename the copied worksheets as Data Set 1, Data Set 2 and Data Set 3.

0 - 3

Determine the future value of investments for three employees. Use the data shown in the table from the Assignment to determine the value in the respective Data

0 - 9

Unit 4    [IT153: Spreadsheet Applications] 

 

Set worksheets.

Assignment reflects the concepts from the Readings and Step-by-steps of the text.

0 - 5

TOTAL POSSIBLE POINTS: 0 - 50

Points deducted for spelling or formatting

Adjusted total points