Spreadsheet Activity 3: Your Own Business!
Spreadsheet
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:
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. 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 15% 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!
You're finished.
Have your 3 parts graded. :-)
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:
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. 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 15% 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!
You're finished.
Have your 3 parts graded. :-)