# Use formulas and functions to perform calculations 4

see attachments
unit_4_data_file.xlsx

it153_unit4_assignment.pdf

Don't use plagiarized sources. Get Your Custom Essay on
Use formulas and functions to perform calculations 4
Just from \$10/Page

Unformatted Attachment Preview

Jeff Quasney
In the Lab 4-2
Retirement Savings
Employee Name
Annual Salary
Percent Invested
Company Match
Annual Return
Years
Javier Soto Monthly Contribution
\$92.000,00
Employee
5,00%
Employer
2,50%
Total
7,25% Future Value
25
Future
Value
Years
5
10
15
20
25
30
35
40
45
50
Employee
Investment
Employer
Investment
Total
Investment
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.
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
f.
amounts made over a number of periods earning a constant rate of
return.
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
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 Data
Data Set 1
Data Set 2
Data Set 3
Employee Name
John Roe
Dante Dacy
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
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
defined. (5)
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
Points
Possible
0–10
0–10
0–10
0–10
Points
Earned
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.
(4 points)
Total Points
0–10
0–2
0–3
0–10
0–65

Purchase answer to see full
attachment

## Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
\$26
The price is based on these factors:
Number of pages
Urgency
Basic features
• Free title page and bibliography
• Unlimited revisions
• Plagiarism-free guarantee
• Money-back guarantee
On-demand options
• Writer’s samples
• Part-by-part delivery
• Overnight delivery
• Copies of used sources
Paper format
• 275 words per page
• 12 pt Arial/Times New Roman
• Double line spacing
• Any citation style (APA, MLA, Chicago/Turabian, Harvard)

# Our guarantees

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.

### Money-back guarantee

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.

### Zero-plagiarism guarantee

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.

### Free-revision policy

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.