Instructions For Completing Net/Gross Pay Calculator Page 8

1. Double-click on the Microsoft Office folder on your desktop.

2. Double-click on Microsoft Office Excel.

3. Click on OK and then again on OK.

4. Click on cell C1. Type in the title “Net/Gross Pay Calculator” and press enter.

It’s OK if it runs into cell D1.

5. Click on cell A3 and type in “Hourly Pay =.” Press tab. Double click the line between the Gray A and B at the top to adjust the column to the correct width.

6. After pressing tab you should be in cell B3. Right click on cell B3. Choose Format Cells. Inside Format Cells window– Choose Currency in the Number Tab as listed below. (do not click OK)

7. Now click on the 4th tab – Border.

8. Inside Format Cells window – Choose the Outline button in the Border Tab as shown above.

9. Click OK and enter 5.25 into Cell “B3”. You should get $5.25 with a box around it. Choose Center in the Alignment group to Center.

10. Click on Cell “A5” and enter “Hours Worked =” and press tab. In Cell “B5” use the same procedures as above to format the cell for border and centering – Don’t set it for Currency. Enter 22 into cell “B5”

11. Double Click the line between the A & B in the column header to adjust the column again.

12. Click on Cell “D3” (choose Right Alignment in the alignment group ) and then type in “Gross Pay =” (it can go into cell “C3”) Then press tab. In Cell “E3” use the same procedures as above to format the cell for money, border and centering. Type in an “= ” sign into the cell E3. Click on cell “B3” and press the times (*) key. Then click on cell “B5” and press enter. This will multiply your hourly pay by your hours worked. See the picture below.

13. After pressing enter the amount should be $115.50.

14. Click on Cell “D5” (choose Right Alignment ) and then type in “Net Pay =” (it can go into cell “C5”) Then press tab. In Cell “E5” use the same procedures as above to format the cell for money, border and centering. Don’t worry about entering in a formula yet that will come later.

15. Click on cell G1 and type in “Deductions.” Press tab. Double click between the G & H to adjust the column.

16. After pressing tab you should be in cell H1. Type in “Percent” and press tab. Double click between the H & I to adjust the column.

17. After pressing tab you should be in cell I1. Type in “Deduction” and press tab. Double click between the I & J to adjust the column.

18. Highlight all three column headings – Deductions Percent and Deduction. Center and Underline them. See below.

19. Click on cell G3 and type in “Federal Tax.” Press enter and type in “Social Security.” Press enter and type in “Medicare.” Press enter and type in “State Tax.” Press enter and type in “Unemployment.” Press enter and type in “Local Tax.” Press enter and type in “Dues?”. Press enter and type in “Retirement.” Press enter and type in “Occ. Priv. Tax.” Press enter and type in “ETC.” Highlight the entries and choose Align Right. See Below.

20. Click on cell H3 and type in “0.07” Press enter and type in “0.062” Press enter and type in “0.0145” Press enter and type in “0.0307” Press enter and type in “0.0002” Press enter and type in “0.0135” Press enter and type in “0” Press enter and type in “0” Press enter and type in “0” Press enter and type in “0”

21. Highlight the entries and choose Center Align in the alignment group.

22. Right click on one of the highlighted cells. Choose Format Cells. Inside Format Cells window– Choose Percent in the Number Tab as listed below. Then click OK.

23. After clicking OK, the percent signs should come in.

24. Highlight Cells I3 through I12. Format these cells to center aligned and currency as you learned previously.

25. Click on cell I3. Type an “=” sign in cell I3. Click on cell “E3” the $115.50. Press the times (*) key and then click on cell “H3” the 7.00%. See below.

26. Press enter and the result of the multiplication should be $8.09.

27. Click back up in cell “I3.” The $8.09 you just made and look in the fx bar. Click before the “E” in the formula and put in a “$” and click between the “E” and the 3 in “E3” and put in another “$.” See below.

28. Press enter and the result should still be $8.09. But now we are able to drag the formula down, because all of them need to be locked into the Gross Pay result in E3 and that’s what we just did.

29. Click on cell “I3” again. Get the regular looking plus sign on the dot in the lower right corner and drag it down to cell “I12” at the end of the table. See below.

Results after letting go of the mouse.

30. Click on cell “H14” (choose Align Right) and Type in “Total Amount Deducted.” It doesn’t matter if it runs in G14. Press tab. Go to Format cells and do the same procedures as before for Currency, Centering and Border.

31. Click on fx and then click OK for Sum. The computers guess should be I3 to I13, if so. Click OK.

32. The result of the sum should be $21.74.

33. Click on cell “E5.” Type in an equal (=) sign and then click on cell “E3” the $155.50. Then press the Minus (-) sign and click on cell “I14” the $21.74. Press enter. See Below.

34. The result of the subtraction should be $93.76.

35. Click the Insert tab and choose Pie in the Graph group.

36. Choose a PIE graph as shown below.

37. Move the window down so you can see your data.

38. Click on Chart Tools that is at the top of the screen. Click Select Data in the Data Group.

39. Click Edit.

40. Click the small box with the red arrow under Series Name.

41. Click on cell “C1.”

42. The result in the entry box should be: =Sheet1!$C$1. Press enter and the title of your Pie Graph should be the same as the title of the Spreadsheet: Net/Gross Pay Calculator.

43. Click the small box with the red arrow under Series values. Click on cell “E5” - $93.76 and then press the comma key. Now highlight your deductions “I3” to “I12.” See Below.

44. The result in the entry blank should be: =Sheet1!$E$5,Sheet1!$I$3:$I$12. Press enter and your pie graph should form. Click OK.

45. Click Edit under Horizontal (Category) Axis Labels

46. Click the small box with the red arrow under Axis Label Range:

47. Click Cell D5 then the comma key on your keyboard, click Cell G3 comma, click Cell G4 comma, click cell G5 comma, click cell G6 comma, click cell G7 comma, click cell G8 comma, click cell G9 comma, click cell G10 comma, click cell G11 comma, click cell G12. Hit Enter and your key Category labels should change as seen below. Click OK.

48. Your pie chart should now look like the one above. Click Ok.

49. Try entering in some other hourly pays and hours worked to see how things change.

50. Play with the Chart Layouts to add percentages to you Pie Chart. Resize your chart, print preview to make sure it all fits on one page.