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%

   Office 2016 Theme Colors
   Office 2016 Theme ——->

4. Review – Your worksheets tabs should look like this
   
5. Add the following 3 document properties via the Document Properties panel.
   Author: xxxxxxxx
   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

1. Make the Overview worksheet the active worksheet
2. 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.
   
   Example header / footer
3. In cell A1 enter Southwest Mini-Market #181
4. Merge and center the text in cell A1 across columns A to E
5. Change the font size and background color of cell A1 to an appropriate combination for a title.
6. 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

7. Change the font size for Income and Expenses then indent the other entries except Total.
8. 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.

1. Enter the text Loan Calculation in cell A1
2. Merge and center the text in cell A1 across columns A to E
3. Change the font size and background color of cell A1 to an appropriate combination for a title
4. 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

5. 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

6. Create a range name for the Workbook using the monthly payment amount with the name Loan_Payment.
7. 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.

1. Enter Monthly Payroll in cell A1, then merge and center the text across columns A to J
2. Change the font size and background color of cell A1 to an appropriate combination for a title
3. 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.
4. Add a Total row below the employees.
5. 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
6. 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
7. Enter a formula in column E to calculate the monthly Gross Pay amount for each employee.
8. 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

9. 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.
10. Use a Function to calculate the totals for the SS Tax, Fed Tax, and State Tax in the total row.
11. 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.
12. Add a formula to calculate the Net Pay which is the Gross Pay minus the SS Tax, Fed Tax, State Tax, and Insurance.
    • (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.)
    • (Self Check 2 – changing the Hours for Health Insurance to 0 should display the insurance premium for all employees. Be sure to leave the value at 30)
13. Use functions to find the Payroll Total, Maximum, Minimum, and Average amount of Gross Pay. Place the formulas under the Gross Pay column values.
14. Add text next to your functions to clearly identify the Payroll Total, Maximum, Minimum, and Average values.
15. Enter a formula for the Employer Socical Security Tax which is equal to the Total Gross Pay times the Employer Social Security Tax
16. Enter a formula for the Total Employee Insurance which is equal to the total of the Insurance column.
17. Calculate the Total Monthly Payroll which is equal to the Total Gross Pay plus the Employer Social Secruity Tax.
18. Create the workbook range name for the Total Monthly Payroll cell named Payroll_Total.
19. Freeze Panes so that only rows 1 and 2 plus column A are always visible when you scroll.
20. Format the worksheet to make it look business like and professional.

Part 5 – Complete Overview Worksheet

1. Select the Overview worksheet
2. 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	187.22
   Internet	121.86
   Utilities	318.24
   Advertising	1813.77

3. Enter a formula to calculate total income, which is the sum of Sales and Interest
4. Enter a formula to calculate the total expenses to total all the expense values
5. In cell A19 enter the text Net Income
6. In cell B19 enter a formula to calculate the Net Income by subtracting the total expenses from total income.
7. Create a range name for the Workbook using the net income value with the name Net_Income.

Part 6 – Create Expenses Chart

1. Create a 3D pie chart of the Expenses from the Overview worksheet excluding the Total
2. Add a legend below the pie chart with text labels for each expense.
3. Add a chart title April 2018 Expense Analysis above the chart.
4. Add percentage data labels to the outside end for each slice of the pie.
   (these should be the only data labels for the chart)
5. Use the Move Chart command to move your chart to a new worksheet tab.
6. Change the tab name to Expenses Chart
7. Change the tab color as indicated below
   

   Expenses Chart

   Gold, Accent 4, Lighter 40%

8. Move the Expenses Chart tab so it is the last tab on the right