Bellingham School District

Suncoast Security Systems

Assignment

RESOURCES:  Online Tutorial

Formula Basics

ASSESSMENT:

Rubric for Spreadsheet ~ Suncoast Revised 12-4-06

Suncoast Security Systems is restructuring.  They have decided to hire 10 new full-time (40-hr) employees and provide health insurance.  Employees will be required to pay $100 per month for the healthcare plan, so $25 needs to be deducted from each employee's weekly gross pay, in addition to the FICA deduction already calculated on the gross pay.  Your task is to update their existing payroll worksheet to accommodate these changes.

Revise a copy of your worksheet:

1. Open your Suncoast Introduction spreadsheet (--Suncst.xls) and add a copy of the spreadsheet to your workbook, using Edit> Move or Copy Sheet.  Be sure to check the Create a copy box.  Double click on each sheet tab and rename them:

2. Modify the format of the Assignment spreadsheet:

   

 

• Insert a row for Deductions and merge as shown in the sample
• Insert a column for HEALTHCARE in the position shown
• Use shading as shown

 

3. Modify the Net Pay formula to include the $25 Healthcare deduction.  Fill down all formulas.
4. Add the new employees, their hourly wage and number of hours worked, at the bottom of the original list.

5. Fill down all formulas.
6. Using the AutoSum Function, total each column except HOURLY WAGE.
7. Sort all employees alphabetically:
   • Select all employees' names and financial data.
   • Go to Data > Sort, and click on Ascending.
8. Include all lines and borders.
9. Center the table on the page vertically and horizontally.
10. Add your name and class period in a Header or Footer, depending on teacher preference.
11. Check Print Preview: your spreadsheet should be centered and complete on one page.
12. Save your workbook.  Print the assignment spreadsheet if your teacher requests it.

Demonstrate your competency in using formulas:

Show your teacher your worksheet formulas at your computer, or print a copy of the worksheet displaying the formulas. There is a shortcut for viewing formulas on a spreadsheet:

Hold down the Ctrl key AND the ~ key.  (You can toggle between views using this key combination.)

To print the formula worksheet:

1. Copy the assignment spreadsheet to a new sheet in your workbook.  Name the sheet tab Formulas.
2. To show all formulas on your worksheet:

Select Tools> Options> View tab. Under Window options, check the Formulas box:

3. Adjust column width to show all of the formulas.  In Page Setup, change to Landscape and choose Fit to 1 page.