to be done in excel format
5. As the budget officer for Alexander State Prison you have to prepare the FY 2019 Operating Budget projections for the Security Division based on the FY 2018 budget estimates. Below you will find one portion of the budget history for the prison. Here are a few facts that you should know about the prison when preparing the operating budget. First, there are thirty staff members in the divi- sion. Twenty-seven of the staff persons are equal in rank (guards). The warden, budget officer and secretary are the last three staff members. Other than what is stated, why do think these changes occurred (see Exhibit 3.5)? Be rational and creative in your responses and justify the changes. Use bullet points to relay your justifications. Round all of your projections to the nearest dollar amoun Hint: You can round numbers in Excel using the round function as well as the "Decrease Decimal" function in the "Home" tool bar. Ten of the guards need training. Thus, training cost will ig- crease by 34% Energy costs are expected to rise 25% due to changes in global oil and gas prices. The cost of printing, pens, and pencils decreases by 2% from FY 2018 The cost of paper will increase by 5%. Telephone costs are expected to increase 4% No new adding machine, but we need a new printer which will cost $500.00 Miscellaneous supplies will increase 25% Operating Budget FY 2019 Object Code FY 2017 FY 2018 (proj. Item (est. $34,500.00 15,176.56 10,925.00 1,200.00 155.02 4,999.00 189.50 (act) $30,000.00 13,550.50 9,500.00 960.00 2004 Training 2005 Utilities 2006 Printing Misc. Supplies Pens and Pencils 2007 2008 2009 150.50 Paper Adding Machine Telephone Printer 3,800.00 2010 376.00 2011 4,200.50 O.00 4,347.52 O.00 2012 Exhibit 3.5 Agency Salary Projection Report FY 2018 (General Fund) Agency: Police Department Division: Homicide MED Pension Health $3,500 $85,347.91 3,500 3,500 FICA $4,313.26 $1,008.75 $6,956.90 2,804.26 2,417.19 2,218.92 1.681.63 Title Director Salary $69,569 45,230 38,987 35,789 27123 Tota 4,523.00 3,898.70 3,578.90 2.712.30 Captain Detective Detective Assistant TOTAL $216,698 $13,435.26 $3,142.12 $21,669.80 655.84 56,713.10 49,368.20 565.31 3,500 518.94 45,605.76 3.500 393.28 35.410.21 $17,500 $272,445.18 This report allows the viewer to determine the exact cost associated with position or an individual. Social security, Medicare, and retirement funds are based on formulas while health care cost for the individual employee is the same for everyone that has a particular characteristic, such as single, married with one dependent, and married with multiple dependents for health care benefits. Each of the employees listed here are current employees. The total cost included n Exhibit 3.5 is the total cost to the government. The employee contributions considered a part of the entire budget. That is, the employee contribution is considered in the FICA and Medicare columns because their contribution isu timately deducted from their salary. With that said, it is not necessary to so the contributions of both the employee and employer on a line item personne! Exhibit 3.5 Agency Salary Projection Report FY 2018 (General Fund) Agency: Police Department Division: Homicide MED Pension Health $3,500 $85,347.91 3,500 3,500 FICA $4,313.26 $1,008.75 $6,956.90 2,804.26 2,417.19 2,218.92 1.681.63 Title Director Salary $69,569 45,230 38,987 35,789 27123 Tota 4,523.00 3,898.70 3,578.90 2.712.30 Captain Detective Detective Assistant TOTAL $216,698 $13,435.26 $3,142.12 $21,669.80 655.84 56,713.10 49,368.20 565.31 3,500 518.94 45,605.76 3.500 393.28 35.410.21 $17,500 $272,445.18 This report allows the viewer to determine the exact cost associated with position or an individual. Social security, Medicare, and retirement funds are based on formulas while health care cost for the individual employee is the same for everyone that has a particular characteristic, such as single, married with one dependent, and married with multiple dependents for health care benefits. Each of the employees listed here are current employees. The total cost included n Exhibit 3.5 is the total cost to the government. The employee contributions considered a part of the entire budget. That is, the employee contribution is considered in the FICA and Medicare columns because their contribution isu timately deducted from their salary. With that said, it is not necessary to so the contributions of both the employee and employer on a line item personne! 5. As the budget officer for Alexander State Prison you have to prepare the FY 2019 Operating Budget projections for the Security Division based on the FY 2018 budget estimates. Below you will find one portion of the budget history for the prison. Here are a few facts that you should know about the prison when preparing the operating budget. First, there are thirty staff members in the divi- sion. Twenty-seven of the staff persons are equal in rank (guards). The warden, budget officer and secretary are the last three staff members. Other than what is stated, why do think these changes occurred (see Exhibit 3.5)? Be rational and creative in your responses and justify the changes. Use bullet points to relay your justifications. Round all of your projections to the nearest dollar amoun Hint: You can round numbers in Excel using the round function as well as the "Decrease Decimal" function in the "Home" tool bar. Ten of the guards need training. Thus, training cost will ig- crease by 34% Energy costs are expected to rise 25% due to changes in global oil and gas prices. The cost of printing, pens, and pencils decreases by 2% from FY 2018 The cost of paper will increase by 5%. Telephone costs are expected to increase 4% No new adding machine, but we need a new printer which will cost $500.00 Miscellaneous supplies will increase 25% Operating Budget FY 2019 Object Code FY 2017 FY 2018 (proj. Item (est. $34,500.00 15,176.56 10,925.00 1,200.00 155.02 4,999.00 189.50 (act) $30,000.00 13,550.50 9,500.00 960.00 2004 Training 2005 Utilities 2006 Printing Misc. Supplies Pens and Pencils 2007 2008 2009 150.50 Paper Adding Machine Telephone Printer 3,800.00 2010 376.00 2011 4,200.50 O.00 4,347.52 O.00 2012 Exhibit 3.5 Agency Salary Projection Report FY 2018 (General Fund) Agency: Police Department Division: Homicide MED Pension Health $3,500 $85,347.91 3,500 3,500 FICA $4,313.26 $1,008.75 $6,956.90 2,804.26 2,417.19 2,218.92 1.681.63 Title Director Salary $69,569 45,230 38,987 35,789 27123 Tota 4,523.00 3,898.70 3,578.90 2.712.30 Captain Detective Detective Assistant TOTAL $216,698 $13,435.26 $3,142.12 $21,669.80 655.84 56,713.10 49,368.20 565.31 3,500 518.94 45,605.76 3.500 393.28 35.410.21 $17,500 $272,445.18 This report allows the viewer to determine the exact cost associated with position or an individual. Social security, Medicare, and retirement funds are based on formulas while health care cost for the individual employee is the same for everyone that has a particular characteristic, such as single, married with one dependent, and married with multiple dependents for health care benefits. Each of the employees listed here are current employees. The total cost included n Exhibit 3.5 is the total cost to the government. The employee contributions considered a part of the entire budget. That is, the employee contribution is considered in the FICA and Medicare columns because their contribution isu timately deducted from their salary. With that said, it is not necessary to so the contributions of both the employee and employer on a line item personne! Exhibit 3.5 Agency Salary Projection Report FY 2018 (General Fund) Agency: Police Department Division: Homicide MED Pension Health $3,500 $85,347.91 3,500 3,500 FICA $4,313.26 $1,008.75 $6,956.90 2,804.26 2,417.19 2,218.92 1.681.63 Title Director Salary $69,569 45,230 38,987 35,789 27123 Tota 4,523.00 3,898.70 3,578.90 2.712.30 Captain Detective Detective Assistant TOTAL $216,698 $13,435.26 $3,142.12 $21,669.80 655.84 56,713.10 49,368.20 565.31 3,500 518.94 45,605.76 3.500 393.28 35.410.21 $17,500 $272,445.18 This report allows the viewer to determine the exact cost associated with position or an individual. Social security, Medicare, and retirement funds are based on formulas while health care cost for the individual employee is the same for everyone that has a particular characteristic, such as single, married with one dependent, and married with multiple dependents for health care benefits. Each of the employees listed here are current employees. The total cost included n Exhibit 3.5 is the total cost to the government. The employee contributions considered a part of the entire budget. That is, the employee contribution is considered in the FICA and Medicare columns because their contribution isu timately deducted from their salary. With that said, it is not necessary to so the contributions of both the employee and employer on a line item personne
