Microsoft Excel Spreadsheet: Carl's Sneaker Shop

Carl's Sneaker Shop

Today we are going to be building a spreadsheet for Carl.  He owns a sneaker shop in Peekskill, NY [I know, right?] and would like to figure out how much he should pay his employee's every month.  He has trouble figuring out their commission, their taxes, and their net pay.

This is as far as Carl got when he gave up:

	A	B	C	D	E
1	Carl's Surf Shop
2	Commission Worksheet
3
4	Sales Rep	Sales	Commission	Taxes	Net Pay
5	James Thomas	$ 45,000.00
6	Jerrod Sanchez	$ 60,000.00
7	Steve Harrison	$ 56,000.00
8	Leah Larkin	$ 34,000.00
9	Casey Perkins	$ 29,500.00
10	Bert Dillon	$ 27,500.00
11	Miranda Parkinson	$ 43,250.00

1. Highlight the information in cells A1+A2 and change the font style and size.
2. Highlight the column titles:  Bold & center them.
3. Use a formula to calculate each sales reps commission.  Commission is 14.5% of their individual sales.
4. Use a formula to calculate the taxes for each sales rep.  Taxes are 22% of their individual commission.
5. Key the word Total in cell A12 and right-align it.
6. Use a formula to calculate the total of this month's payroll in cell E12.
7. Use a formula to calculate the net pay for each sales rep.
8. Use a formula to calculate the total of this month's payroll in cell E12.
9. Format "money columns" to currency style with two decimals.
10. Highlight the cell that has the highest commission amount for the month and fill in with light grey.  Highlight the sales reps name (with the highest commission): Italicize & Bold it.
11. Highlight row 12 and Bold it.
12. Highlight the information in cell A5:A11 and across through E5:E11.  Sort A-Z.
13. Make sure columns are wide enough to show all information.
14. Add your name to cell E1: Bold and right-align it.
15. Select the entire work area (cells A1:E12) and go to Page Layout > Print Area > Set Print Area.
16. Add formatting to make the document more personalized/attractive.
17. Do a Print Preview to make sure it fits on one page.
18. Print and turn in your spreadsheet.