Slide 1Slide 2Slide 3Slide 4Slide 5Slide 6Slide 7Slide 8Slide 9Slide 10Slide 11Slide 12Slide 13Slide 14Slide 15Slide 16Slide 17Slide 18Slide 19Slide 20Slide 21Slide 22Slide 23Slide 24Slide 25Slide 26Slide 27Slide 28Creating a Personnel Budget for a Hypothetical AgencyLooking ahead to the final productCreating a Personnel Budget, Slide 2 Copyright © 2004, Jim Schwab, University of Texas at AustinIn this exercise, we will create a personnel budget for our hypothetical agency. We will include position titles, salary basis, hours per week, and the monthly and annual salary for all persons in the position.In this exercise, we will create a personnel budget for our hypothetical agency. We will include position titles, salary basis, hours per week, and the monthly and annual salary for all persons in the position.Enter the column headers for personnel budgetCreating a Personnel Budget, Slide 3 Copyright © 2004, Jim Schwab, University of Texas at AustinEnter the headers for the columns: Position in A1, Number of Employees in B1, Salary in C1, Basis in D1, Hours per Week in E1, Annual Budget in F1, and Monthly Budget in G1.Enter the headers for the columns: Position in A1, Number of Employees in B1, Salary in C1, Basis in D1, Hours per Week in E1, Annual Budget in F1, and Monthly Budget in G1.Autofit the columns to make the headers visibleCreating a Personnel Budget, Slide 4 Copyright © 2004, Jim Schwab, University of Texas at AustinHighlight columns A through G and select the Column > AutoFit Selection command from the Format menu.Highlight columns A through G and select the Column > AutoFit Selection command from the Format menu.Enter the employee positionsCreating a Personnel Budget, Slide 5 Copyright © 2004, Jim Schwab, University of Texas at AustinEnter the positions of the employees in column A. Enter Administrator in A2, Assistant Administrator in A3, Director of Campus Life in A4, Child-care Supervisor in A5, Receptionist in A6, Secretary in A7, Bookkeeper in A8, Senior Clinical Social Worker in A9, Clinical Social Worker in A10, Recreation Leader in A11, Senior Child-Care Worker in A12, Full-time Child-care Worker in A13, Part-time Child-care Worker in A14, Cook in A15, and Maintenance Staf in A16.Enter the positions of the employees in column A. Enter Administrator in A2, Assistant Administrator in A3, Director of Campus Life in A4, Child-care Supervisor in A5, Receptionist in A6, Secretary in A7, Bookkeeper in A8, Senior Clinical Social Worker in A9, Clinical Social Worker in A10, Recreation Leader in A11, Senior Child-Care Worker in A12, Full-time Child-care Worker in A13, Part-time Child-care Worker in A14, Cook in A15, and Maintenance Staf in A16.Autofit the column to make the positions visibleCreating a Personnel Budget, Slide 6 Copyright © 2004, Jim Schwab, University of Texas at AustinHighlight column A select the Column > AutoFit Selection command from the Format menu.Highlight column A select the Column > AutoFit Selection command from the Format menu.Set the width of column BCreating a Personnel Budget, Slide 7 Copyright © 2004, Jim Schwab, University of Texas at AustinWhen we autofitted column A so the data entries were visible, column G moved to the right of the visible worksheet window. To return column G to the visible window, we will make columns B and E narrower.When we autofitted column A so the data entries were visible, column G moved to the right of the visible worksheet window. To return column G to the visible window, we will make columns B and E narrower.Column B is much wider than needed to display the number of employees. We will change its width to 10.Highlight column B and select the Column > Width command from the Format menu. In the Column Width dialog box, enter 10 for the width.Column B is much wider than needed to display the number of employees. We will change its width to 10.Highlight column B and select the Column > Width command from the Format menu. In the Column Width dialog box, enter 10 for the width.Set the width of column ECreating a Personnel Budget, Slide 8 Copyright © 2004, Jim Schwab, University of Texas at AustinColumn E is much wider than needed to display the hours per week. We will change its width to 8.Highlight column E and select the Column > Width command from the Format menu. In the Column Width dialog box, enter 8 for the width.Column E is much wider than needed to display the hours per week. We will change its width to 8.Highlight column E and select the Column > Width command from the Format menu. In the Column Width dialog box, enter 8 for the width.Wordwrap the column headersCreating a Personnel Budget, Slide 9 Copyright © 2004, Jim Schwab, University of Texas at AustinWhen we reduced the width of columns B and E, a portion of the column headers was no longer visible in the cell. To make all the column headers visible, we will word wrap the text in all headers.When we reduced the width of columns B and E, a portion of the column headers was no longer visible in the cell. To make all the column headers visible, we will word wrap the text in all headers.Highlight row 1 on the worksheet and select the Format > Cells command on the Edit menu. On the Alignment tab, mark the check box for Wrap text.If necessary, adjust the height of row 1 to 26.40.Highlight row 1 on the worksheet and select the Format > Cells command on the Edit menu. On the Alignment tab, mark the check box for Wrap text.If necessary, adjust the height of row 1 to 26.40.Enter the number of persons in each positionCreating a Personnel Budget, Slide 10 Copyright © 2004, Jim Schwab, University of Texas at AustinEnter the number of persons in each position in column B.Enter 1 in cells B2 through B8, 2 in B9 and B10, 1 in B11, 4 in B12, 8 in B13, 6 in B14, and 2 in B15 and B16.Enter the number of persons in each position in column B.Enter 1 in cells B2 through B8, 2 in B9 and B10, 1 in B11, 4 in B12, 8 in B13, 6 in B14, and 2 in B15 and B16.Enter the salary for each positionCreating a Personnel Budget, Slide 11 Copyright © 2004, Jim Schwab, University of Texas at AustinEnter the salary for each position in column C.Enter 75000 in C2, 62000 in C3, 65000 in C4, 45000 in C5, 21000 in C6, 23000 in C7, 25000 in C8, 40000 in C9, 34000 in C10, 24000 in C11, 450 in C12, 400 in C13, 185 in C14, 6 in C15, and 6.5 in C16.Enter the salary for each position in column C.Enter 75000 in C2, 62000 in C3, 65000 in C4, 45000 in C5, 21000 in C6, 23000 in C7, 25000 in