Microsoft excel

please see attachments it is 6 parts

please see attachments it is 6 parts

please se attachments it is 6 parts

11:53
cis.cuyamaca.edu
Part 1 – Create a new Workbook for the exam
1. Start Excel and create a new blank workbook.
Save your new workbook as
firstname.lastname_Exam3
Files not created in Microsoft Excel 2013 or 2016 may
not earn full credit.
2. Name the worksheets from left to right as follows:
Overview, Loan, Payroll
3. Using the standard Office theme change the tab colors as
follows:
Overview
Blue-Gray Text 2, Lighter 60%
Loan
Orange Accent 2, Lighter 40%
Payroll
Blue Accent 1, Lighter 40%
Theme Colors
Office 2016 Theme Colors
Standard Colors
No Fill
More Colors…
Office
Office 2016 Theme ——->
Office
Office 2007 – 2010
4. Review – Your worksheets tabs should look like this
Overview Loan Payroll
5. Add the following 3 document properties via the Document
Properties panel.
Author: branden.yousif
Title: Exam3
Comments: location where you completed the exam
examples
Important!: Your location in the comments must match the
location you submit you file from or you will have a
deduction.
if you completed it at home then list – “my home PC”
if you complete it on campus then list the room and
computer number
using room E206 system 32 would be entered as – “E206
system 32”
using college computers – “Cuyamaca Tech Mall system 206”
or Grossmont Lab system 308
Part 2 – Overview Worksheet – Enter and Format cells
6. Make the Overview worksheet the active worksheet
7. Insert the header and footer elements in the header /
footers areas as shown below.
Type your user name in the right side of the header where it
11:53
cis.cuyamaca.edu
Part 3 – Loan Worksheet – Calculate Payment
To add the Mortgage expense for the store we need to
calculate the mortgage payment on the Loan Worksheet
and then add a reference to it on the Overview
worksheet.
14. Enter the text Loan Calculation in cell A1
15. Merge and center the text in cell A1 across columns A to E
16. Change the font size and background color of cell A1 to
an appropriate combination for a title
17. Input area Starting in cell A3 create the following.
Use the following for your input area text and values
Store Cost – 9171 Cuyamaca St.
618,250.00
Down Payment
61,810.00
Annual Percentage Rate
4.25%
Loan Term – Years
30
18. Output area – select an appropriate area to enter
formulas to calculate the following for your output area
values.
Loan Amount is the difference between the cost of the
store and the down payment
Monthly Payment – payments are at the end of the month
and displayed as a positive value.
Total Cost of Loan which is the total of all payments
Total Interest which is the difference between the Loan
Amount and Total Cost of Loan
Loan Amount
Monthly Payment
Total Interest
Total Cost of Loan
19. Create a range name for the Workbook using the monthly
payment amount with the name Loan_Payment.
20. Format the worksheet to make it look business like and
professional.
Self check. Change the Loan Term to 15 years. You should
see the Monthly Payment, Total Interest and Total Cost of
Loan change. If any of them stay the same then you have a
problem.
When finished checking change the Loan Term back to
30 years.
Part 4 – Monthly Payroll Worksheet – Add Employees and
Calculations
You will calculate the monthly pay for your employees.
Since you have weekly hours you will need to multiply
this by 4 to get the monthly pay. This assignment is a
simplified payroll example. If you are interested you can
download a full California example here.
21. Enter Monthly Payroll in coll11 than merce and center
11:53
cis.cuyamaca.edu
Part 4 – Monthly Payroll Worksheet – Add Employees and
Calculations
You will calculate the monthly pay for your employees.
Since you have weekly hours you will need to multiply
this by 4 to get the monthly pay. This assignment is a
simplified payroll example. If you are interested you can
download a full California example here.
21. Enter Monthly Payroll in cell A1, then merge and center
the text across columns A to )
22. Change the font size and background color of cell A1 to
an appropriate combination for a title
23. Add the same 12 employees used in Exam 1 by adding their
last name in column A and first name in column B with the
column titles in row 2.
24. Add a Total row below the employees.
25. Add the following columns for each employee starting in row
2 column C:
Rate, Hours, Gross Pay, SS Tax, Fed Tax, State Tax,
Insurance, and Net Pay
Use the same Pay Rate you entered for your employees in
Exam 1
26. Enter values for Hours in column D with the following
guidelines:
Make up the weekly hours for each employee using any
value from 20 – 40 hrs
27. Enter a formula in column E to calculate the monthly
Gross Pay amount for each employee.
28. Add the following table to the worksheet starting below your
payroll data and calculations
Insurance and Tax Table
Health Insurance Premium
430.75
Hours for Health Insurance
30
Tax Rates
Social Security Tax Rate
7.65%
Fed Income Tax Rate
14.00%
State Income Tax Rate
4.55%
Employer Social Security Rate
7.65%
Calculations
Total Employee Insurance
Total Employer Social Security Tax
Total Monthly Payroll
29. Using the Insurance and Tax Table, add formulas to
calculate the values for the SS Tax, Fed Tax, State Tax
columns where the calculated value is the Gross Pay times
the tax listed in the table.
30. Use a Function to calculate the totals for the SS Tax, Fed
Tax, and State Tax in the total row.
31. Employees who work 30 hours or more will have the
insurance premium deducted from their nav Add a formula
11:53
cis.cuyamaca.edu
Part 2 – Overview Worksheet – Enter and Format cells
6. Make the Overview worksheet the active worksheet
7. Insert the header and footer elements in the header /
footers areas as shown below.
Type your user name in the right side of the header where it
says Your Name in the example.
Worksheet Header Elements
File Name
Sheet Name
Your Name
Worksheet
Current Date
Current Time
Worksheet Footer Elements
Page Number
Example header / footer
8. In cell A1 enter Southwest Mini-Market #181
9. Merge and center the text in cell A1 across columns A to E
10. Change the font size and background color of cell A1 to
an appropriate combination for a title.
11. Enter the following into the Overview worksheet starting in
cell A3.
Income
Interest
Sales
Total Income
Expenses
Mortgage
Payroll
Taxes
Insurance
Phone
Internet
Utilities
Advertising
Total Expenses
12. Change the font size for Income and Expenses then indent
the other entries except Total.
13. Format the worksheet to make it look business like and
professional.
You will come back and complete this worksheet after you
finish the Loan and Payroll worksheets.
Part 3 – Loan Worksheet – Calculate Payment
To add the Mortgage expense for the store we need to
calculate the mortgage payment on the Loan Worksheet
and then add a reference to it on the Overview
worksheet.
14. Enter the text Loan Guicuium H17-
11:53
cis.cuyamaca.edu
30. Use a Function to calculate the totals for the SS Tax, Fed
Tax, and State Tax in the total row.
31. Employees who work 30 hours or more will have the
insurance premium deducted from their pay. Add a formula
to calculate the insurance in the Insurance column for each
employee based on the value in the Hours column and the
Hours for Health Insurance in the Insurance and Tax
Table.
32. Add a formula to calculate the Net Pay which is the Gross
Pay minus the SS Tax, Fed Tax, State Tax, and Insurance.
o (Self Check 1 – copying the formulas for Gross Pay, SS
Tax, Fed Tax, and State Tax from the first employee to all
the rows below and have correct results.)
o (Self Check 2 – changing the Hours for Health Insurance
to O should display the insurance premium for all
employees. Be sure to leave the value at 30)
33. Use functions to find the Payroll Total, Maximum,
Minimum, and Average amount of Gross Pay. Place the
formulas under the Gross Pay column values.
34. Add text next to your functions to clearly identify the Payroll
Total, Maximum, Minimum, and Average values.
35. Enter a formula for the Employer Socical Security Tax
which is equal to the Total Gross Pay times the Employer
Social Security Tax
36. Enter a formula for the Total Employee Insurance which is
equal to the total of the Insurance column.
37. Calculate the Total Monthly Payroll which is equal to the
Total Gross Pay plus the Employer Social Secruity Tax.
38. Create the workbook range name for the Total Monthly
Payroll cell named Payroll_Total.
39. Freeze Panes so that only rows 1 and 2 plus column A are
always visible when you scroll.
40. Format the worksheet to make it look business like and
professional.
Part 5 – Complete Overview Worksheet
40. Select the Overview worksheet
41. Enter the text in column A and the values or, formulas, or
3D references in column B of your worksheet.
Note: the Tax and Insurance values here are for the
business.
Income
Interest
301.18
Sales
64181.00
Expenses
Mortgage
3D reference for Monthly Payment from Loan worksheet
Payroll
3D reference for Payroll Total from Payroll worksheet
Tax
formula for 26% of Income Total
Insurance
1622.50
Phone
101.22