## Spreadsheet Activity 3: Your Own Business!

**Spreadsheet**

Activity 3

Activity 3

**50%**

In this final activity you will do all of the calculations and formula development on your own! You will be asked to create on your own a number of different formulas. Be SURE to do all calculations with these formulas. Any answers not involving formulas will not count!

You will use the same spreadsheet, but will just begin further down.

a.

**Company Name:**

In this 3rd activity, imagine YOU are the owner of a successful business with 12 employees.

In cell

**A60**type the name of YOUR company. Change the size to 14 and make it Blue &

**BOLD**

b. Type the following headings:

b. Type the following headings:

In cell

**A62**: Employee

In cell

**B62**: Salary

In cell

**C62**: Bonus

In cell

**D62**: Salary Inc. Bonus

In cell

**E62**: Taxes/Deductions

In cell

**F62**: Net

Center each of these headings and “

__underline__” them

c.

**Pick your company employees!**

Beginning in cell

**A64**and ending in cell

**A75**type the first and last names of any 12 people (real or fictional) who will work for you. Have fun with it.

Type your first and last name in

**A76**because you have to get paid too!

d.

**Pre-set for commas & dollar signs**

For this activity you will PRE-set for dollar signs and decimals.

Select/Hi-Light cells

**B64**to

**F80**

Go up to

**FORMAT**-

**CELLS**– Then go to

**Currency**and choose the option that allows for 2 places past the decimal point. Hit

__.__

**OK**e.

**Time to keep track of wages.**

Now that you have preset for dollar signs and commas, there is no need for you to add them the program will do it for you!

__The first 3 employees (__

**B64-B66**__) work part-time in delivery & maintenance.__

The first employee in cell

**B64**earns 14,456 dollars.

**You type:**

**14456**

The employee in

**B65**earns $1,000 dollars more than the employee in

**B64**.

The employee in

**B66**earns $1,000 dollars more than the employee in

**B65**.

__The next 3 employees (__

**B67-B69**__)work on the production/service line.__

The employee in cell

**B67**earns $34,456

The employee in cell

**B68**earns $1,000 more than the employee in

**B67**.

The employee in cell

**B69**earns $1,000 more than the employee in

**B68**.

__The next 3 employees (__

**B70-B71**__) work in sales/promotion/advertising.__

The employee in cell

**B70**earns $54,456.

The employee in cell

**B71**earns $1,000 more than the employee in

**B70**.

The employee in cell

**B72**earns $1,000 more than the employee in

**B71**

__The next 3 employees (__

**B73-B75**__) are your managers and assistants.__

The employee in cell

**B73**earns $74,456.

The employee in cell

**B74**earns $1,000 more than the employee in

**B73**.

The employee in cell

**B75**earns $1,000 more than the employee in

**B74**.

In cell

**B76**(your earnings) fill in $250,000. That's your salary!

**f.**

**It was a good year: Bonus time for the delivery/maintenance people!**

Find the employee in cell

**A64.**

Look at that employee's earnings in

**B64**

Create a formula to give that employee a

**5%**bonus and place that figure in that cell

**C64**

Repeat this process for employees in

**A65**&

**A66**and place their bonus figures in

**C65**&

**C66**

g.

g.

**Bonus time for the production/service people.**

Find the employee in cell

**A67.**

Look at that employee's earnings in

**B67**

Create a formula to give that employee a

**10%**bonus and place that figure in that cell

**C67**

Repeat this process for employees in

**A68**&

**A69**and place their bonus figures in

**C68**&

**C69**

**h**

**.**

**Bonus time for the sales/promotion/advertising people**

Find the employee in cell

**A70**.

Look at that employee's earnings in

**B70.**

Create a formula to give that employee a

**1**5

**%**bonus and place that figure in that cell

**C70.**

Repeat this process for employees in

**A71**&

**A72**and place their bonus figures in

**C71**&

**C72.**

**i.**

**Bonus time for your assistants / managers.**

Find the employee in cell

**A73.**

Look at that employee's earnings in

**B73.**

Create a formula to give that employee a

__20.5%__**(updated)**bonus and place that figure in that cell

**C73.**

Repeat this process for employees in

**A74 & A75**and place their bonus figures in

**C74 & C75.**

**j.**

**Your bonus!**

You've figured this out by now. Business has been good.

Create a formula to give yourself a

**66.33%****(updated)**bonus (and place that number in

**C76.**

**k.**

**Total the bonus and Salaries:**

Create a formula to combine the salaries and bonus columns.

Place these figures in cells

**D64**to

**D76.**

**l.**

**Calculate Taxes/Deductions:**

Life is also about taxes and other expenses. This includes: Federal U.S. Government Income Taxes, Massachusetts Income Taxes, health insurance and Social Security / Retirement.

It's amazing how much is deducted from a person's paycheck!

Random figures have been chosen for this activity. Follow the directions for each group:

aa. For employees in Row

**64, 65**&

**66**their Taxes/Deductions will be

**7%.**

Calculate a formula to do this and place these amounts in cells

**E64, E65**&

**E66**.

bb. For employees in Row

**67, 68**&

**69**their Taxes/Deductions will be

**22%.**

Calculate a formula to do this and place these amounts in cells

**E67, E68**&

**E69**.

cc. For employees in Row

**70, 71**&

**72**their Taxes/Deductions will be

**28%.**

Calculate a formula to do this and place these amounts in cells

**E70, E71**&

**E72**.

dd. For employees in Row

**73, 74**&

**75**their Taxes/Deductions will be

**33%.**

Calculate a formula to do this and place these amounts in cells

**E73, E74**&

**E75**.

ee. For you - in Row

**76**your Taxes/Deductions will be

**41%. You do make a lot!**

Calculate a formula to do this and place these amounts in cells

**E76**.

**m.**

**Calculating the NET SALARY:**

Net salary is what YOU get to KEEP after all the bonuses, taxes and deductions have been figured in. It is what is left for you to do whatever you want with.

Create a formula in column F to do this for yourself and all of your employees!

(Cells

**F65**to

**F76**)

**n.**

**Final Totals and Averages:**

In cell

**A78**type the heading: Totals

In cell

**A79**type the heading: Averages

Make both of these headings bold and blue in color.

Use what you have learned to total and average columns B, C, D, E & F.

Be sure these numbers have been formatted for dollar signs and commas.

Your formatting should allow for numbers to the right of the decimal point.

Make these total & average numbers

**bold**and

**blue**in color!

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

**At this point, you have a done a good job of understanding the basics of spreadsheets.**

Congratulations!

Congratulations!

**You're finished.**

Have your 3 parts graded. :-)

Have your 3 parts graded. :-)