Med Supply Online Warehouse, excel assignment help

Unit 4: Assignment Unit outcomes addressed in this unit:  Use name cell references in formulas.  Use the financial functions PMT and PV to determine the monthly payment and present value of a loan.  Use a chart to display data elements.  Use the data table command on the W hat -If Analysis gallery.  Protect an Excel worksheet using the lock feature. Course outcome(s) practiced in this unit: IT153 -2: Use formulas and functions to perform calculations. Scenario : 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. Instructions : 1. Download the Unit 4 data file from Doc Sharing . 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) e. 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. f. 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. 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 W hat -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 (or similar pointer) that shows the row that equates to the years in cell C9 to the Years column in the data table. Use a white font color for the pointer. Change the sheet tab name and color to light green or pale green color 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 three 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 Dat a 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% 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 . 12. Save as Unit _4_ Assignment_Your Name and submit the Assignment to the Unit 4 Dropbox. Unit 4 Assignment grading rubric = 65 points Assignment Requirements Points Possible Points Earned Use the Create from Selection button on the Formula tab of the Ribbon (5) to assign the row titles to the adjacent cells. (5) 0–10 Formulas are created (5) using the names defined. (5) 0–10 Use the Data Table command on the W hat -If Analysis gallery on the Data tab on the Ribbon to define the range B12:F22 as a one -input data table. (5) Use cell C9 as the column input cell. (5) 0–10 Use the Conditional Formatting button on the Home tab on the Ribbon to add an orange (or similar) pointer that shows the row that equates 0–10 the years in cell C9 to the Years column in the data table. (5) Use a white font color for the pointer. (5) Unlock the cells in the range C3:C8. (4) Protect the worksheet. (3) Allow users to select only unlocked cells. (3) 0–10 Rename worksheet as Retirement Planning and Change color of Tab. (1) Delete any unused worksheets. (1) 0–2 Create three copies of the Financial Analysis worksheet into the workbook. (2) Rename the copied worksheets as Data Set 1, Data Set 2, and Data Set 3. (1) 0–3 Determine the future value of investments for three employees. (2 points for each employee) Use the data shown in the table from the Assignment to determine the value in the respective Data Set worksheets. (4 points) 0–10 Total Points 0–65