Tech Connections Home

Bellingham School District

Suncoast Security Systems

Introduction

 Graph image
GOAL: Develop an understanding of spreadsheet design, construction and use of formulas.

RESOURCES • Online Tutorial

Formula Basics

ASSESSMENT: Rubric for Spreadsheets ~ Suncoast

Suncoast Security Systems wants to make their accounting department more efficient. Each month the accountant has to figure out how much to pay each employee based on the number of hours worked, their hourly wage, and the amount withheld for Social Security. The Employees have 7.6% of their earnings deducted to pay FICA (Social Security). The accountant spends valuable time calculating how much to pay each employee.

Task:

1.)   Create a worksheet that calculates Suncoast's weekly payroll.  Format to look like this:

Table title, category and first data line rows

2.)   Name the sheet tab: Image of tab nammed Introduction

3.)   Add Suncoast employee information:

Abbot R. worked 40 hours at $8.00/hr. Hot Dog H. worked 15 hours at $14.00/hr.
Banana B. worked 20 hours at $6.00/hr. Lipski S. worked 25 hours at $25.00/hr.
Cashew T. worked 15 hours at $10.00/hr. Skillings L. worked 40 hours at $7.00/hr
Cheetos D. worked 30 hours at $11.00/hr. Smith R. worked 30 hours at $8.00/hr.
Hillman D. worked 32 hours at $9.00/hr. Taylor B. worked 20 hours at $9.00/hr.

4.)  Add a shaded separator row followed by a Total row:

Separator and totals row

5.)   Format appearance:

Content
Format
Company title

At top of table - All caps - Larger font - Bold - Center across columns

Column headings:

  •  See 1.) sample view

Center column headings, make bold, and use caps

Under Format> Cells:

  • Alignment tab: click Wrap text
  • Patterns tab: add gray shading

Calculate formulas for:

  • Gross Pay (hours x wages)
  • FICA (Social Security deduction--a percent amount of Gross Pay)
  • Net Pay (Gross Pay minus Soc. Sec. deduction)

ALL FORMULAS start with equals sign sign.

Enter formulas for Gross Pay, FICA and Net Pay in row 4.

Use the fill handle to copy formulas down the columns  fill handle diagram

(Note: You can select all 3 formula columns and fill down at the same time.)

 

Total columns B, D, E & F

Use AutoSum to total hours, pay and deduction columns:

Select cells in the column you want to total, then click the icon Autosum icon
Add borders

Use Format> Cells > Border tab:

Insert narrow border between data rows

Insert wider border between columns and setting off heading & total rows

Add a wider border all around the table, with Company title outside
  • Add your name in a header or footer. Use View> Header and Footer> Header/Footer tab> Custom Header (or Custom Footer) : Type your name and class period in the section of the Header (or Footer) preferred by your teacher.
  • Save the file in your H: drive.
  • Name the file with your first and last initials and suncst (--suncst.xls).
 

Copyright Notice: No materials on any of the Bellingham Schools' web pages may be copied without express written permission unless permission is clearly stated on the page.

Teacher Tips / Permission / Image Information / Return to Tech Connections Homepage / District Homepage

Bellingham Public Schools. 1306 Dupont St. Bellingham WA 98225-3198