Unformatted Attachment Preview
In the Lab 4-2
Javier Soto Monthly Contribution
7,25% Future Value
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 What-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.
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.
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 /
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
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 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 (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
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 =
Table 2: Future Value What-If Analysis Data
Data Set 1
Data Set 2
Data Set 3
11. Delete any blank worksheets.
12. Save as Unit_4_Assignment_Your Name and submit the Assignment to the Unit 4
Unit 4 Assignment grading rubric = 65 points
Use the Create from Selection button on the
Formula tab of the Ribbon (5) to assign the row
titles to the adjacent cells. (5)
Formulas are created (5) using the names
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. (5)
Use cell C9 as the column input cell. (5)
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
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)
Rename worksheet as Retirement Planning
and Change color of Tab. (1) Delete any
unused worksheets. (1)
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)
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.
Purchase answer to see full
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more