Start Excel and create a new blank workbook. Save your new workbook as rita.hangolla_Excel_Exam1 (including the underscore characters)
Add additional worksheets to your workbook so you have a total of 3 worksheets and name the worksheets from left to right as follows: Overview, Loan, Payroll
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 Theme Colors
Office Theme ——->
! Some systems may have variations in their Office theme.
If yours do not match then use the closest color to those listed in the assignment.
Review – Your worksheets tabs should look like this
Add 3 Document Properties via the Document Properties panel.
Author: rita.hangolla
Title: Excel Exam 1
Comments: location where you completed the exam.
Examples
Home: My Home PC
Cuyamaca College room E206 System 32: E206 System 32
Part 2 – Overview Worksheet – Enter and Format cells
Make the Overview worksheet the active worksheet.
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.
Use the Excel built-in elements (highlighted in red) for the remaining header / footer areas.
An example of the easy way to add headers and footers is available on the Exam Videos page.
Example header / footer
In cell A1 enter Southwest Mini-Market #211
Merge and center the text in cell A1 across columns A to E
In cell A1 change the font size and background color to an appropriate title combination.
Similar to what you have done in your Simnet projects.
In the Overview worksheet cell A3 enter the following shown below.
Income
Interest
Sales
Total Income
Expenses
Mortgage
Payroll
Taxes
Insurance
Phone
Internet
Utilities
Advertising
Total Expenses
Increase the font size for Income and Expenses then indent the other entries except Totals.
Format the worksheet to make it look business like and professional.
You will 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 the payment amount on the Overview worksheet. A loan payment calculation was covered as a topic in Excel chapter 2. Since this worksheet will have an area for user input and also an area where the calculated results will be displayed you need to design the worksheet appropriately and may want to refer to the Worksheet Design page to view example layouts.
In cell A1 of the Loan worksheet enter the text Loan Calculation.
Merge and center the text in cell A1 across columns A to E
In cell A1 change the font size and background color to an appropriate title combination.
Input area – Starting in cell A3 create the following. Use the following for your input area text and values.
Store Cost – 2100 Cuyamaca St.
721,100.00
Down Payment
32,100.00
Annual Percentage Rate
3.125%
Loan Term – Years
30
Output area – select an appropriate area in the worksheet to enter formulas to calculate the following for your output area calculations.
Loan Amount is the difference between the Store Cost and the Down Payment
Monthly Payment – using a function to calculate the payment. Payments are at the end of the month and displayed as a positive value.
Total Cost of Loan – the total of all payments
Total Interest – the difference between the Loan Amount and Total Cost of Loan
Loan Amount
Monthly Payment
Total Interest
Total Cost of Loan
Create the workbook range name Loan_Payment using the monthly payment amount cell.
Format the worksheet with a business and professional look.
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 those cells do not change 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 need to multiply this by 4 to get the monthly pay.
This assignment is a very simplified payroll example.
If you are interested you can download a full California example here however it is much more complex than this assignment.
Here is the unformatted example
In cell A1 enter Monthly Payroll, then merge and center the text across columns A to
In cell A1 change the font size and background color to an appropriate title combination.
Add the following columns for each employee starting in row 2 column A: Last Name, First Name, Rate, Hours, Gross Pay, SS Tax, Fed Tax, State Tax, Insurance, and Net Pay
Add 12 employees starting in row 3.
for the first employee use your name last name in column A and your first name in column B
Add 11 names to complete the employee list (use any names you want).
Create pay rate data for each employee
In column C, enter the hourly pay in the Rate,using values between 15 to 50
Create hours data for each employee
In column D, enter values for weekly Hoursusing any value from 20 – 40 hours
In cell A15 enter Total to use as a total row below the employees.
Create a formula to Calculate the monthlyGross Pay
In column E, monthlyGross Pay is 4 times the weekly hours times the rate.
Add the following tables to the worksheet starting below your payroll data and calculations
Insurance Table
Health Insurance Premium
520.75
Hours for Health Insurance
30
Tax Rate Table
Employee Social Security
7.65%
Fed Income
15.00%
State Income
4.00%
Employer Social Security
7.85%
Calculations Table
Total Employee Insurance
Total Employer Social Security Tax
Total Monthly Payroll
Columns containing the SS Tax, Fed Tax, State Tax formulas use references to values in the Insurance and Tax Rate Tables.
The calculated value is the Gross Pay times the specific tax listed in the Tax Rate Table. For example the SS Tax is the Gross Pay times the Employee Social Security value from the Tax Rate Table.
Use a Function to calculate the totals for the SS Tax, Fed Tax, and State Tax columns in the Total row 15.
Calculate Insurance premium – If an employee works 30 hours or more then they will have the insurance premium deducted from their pay.
Using an Excel function create a formula to calculate the insurance in the Insurance column for each employee.
Use the Hours column from payroll and the Hours for Health Insurance and the Health Insurance Premium from the Insurance table in your function.
Net Pay – add a formula for this column 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 should give you valid values if you use the correct type of cell references.)
(Self Check 2 – changing the Hours for Health Insurance to 0 in the Insurance Table for should display an insurance premium value for all employees.)Be sure to set the Hours for Health Insurance value back to 30 after checking
Stats – cells E15:E18, Use functions to calculate the Total, Maximum, Minimum, and Average for column E, the Gross Pay column.
In the Calculations Table enter 3 formulas for:
Employer Social Security Tax which is equal to the Total Gross Pay times the Employer Social Security Tax in the Calculations Table.
Total Employee Insurance which is equal to the total of the Insurance column in the Calculations Table.
Total Monthly Payroll which is equal to the Total Gross Pay plus the Employer Social Security Tax from the Calculations table.
Create a workbook range name Payroll_Total using the Total Monthly Payroll cell.
Freeze Panes so that only rows 1 and 2 plus column A are always visible when you scroll.
