Cost analysis

You have taken over as the director of the Glenville County Road Department in the midst of turmoil. The previous director was forced to resign after a serious budget deficit in the previous year. The budget deficit was precipitated by a drop in the state reimbursement rate for maintenance of county roads. Previously, the state had been willing to reimburse up to $4,500 per mile for gravel roads, and $7,000 for paved roads. After a report by the state auditor indicated gross inefficiencies in many county road departments, the state department of transportation decided to cut rates to force counties to become more efficient. The previous director ran a pretty "loose ship", and did not have good visibility on what it actually cost to maintain roads. He and the county executive were caught completely off guard when the road department had a large deficit. The county executive hired you precisely because of your background in finance and budgeting, and she expects you to clean up this mess, and eliminate deficits while maintaining the level of service. After digging through piles of disorganized papers, you are able to put together some basic operating information on the department for last year, which you summarized in the attached table. Using this information, you have laid out a series of tasks that you need to complete before you can get a handle on the road department budget.

a) First, develop a budget for each mission center (and in total) using information from the table included below and in the attached spreadsheet (please format appropriately; points for style!). How large is the deficit, both for each division (gravel and paved) and in total? How did you allocate costs that are shared between the two divisions (note: be sure to clearly state your allocation factor; a 50/50 split is not an appropriate allocation factor in this instance). Be sure to format your budget to look presentable.

b) develop another budget (on a second sheet) assuming staff can work up to the maximum productivity rate. (hint: some costs will shift with an increase in output; you should assume that supply costs are variable)

c) develop a flexible budget (as a separate sheet within the same excel workbook) for just the gravel road center, assuming that output varies by 20% above and below last year's output level. please make sure you show the formulas on the excel worksheet you will develop.

Glenville Road Department Cost Analysis Mission: To maintain the county's 500 miles of roads which are both paved and gravel. You can assume that the construction of new roads is carried out by the state. Revenues: The state department of transportation will reimburse the county at a fixed rate per mile. Personnel: You can assume that all personnel are fulltime and work 225 days/year on average. All maintenance personnel receive the same wage, which will be $45,000 (including fringe costs) per year. The following is a summary of the activity and cost information that was put together for the most recent fiscal year. For simplicity no inflation was assumed in the initial costs analysis Glenville Road Department Actual Cost Data Director (Salary and Fringe) Secretary (Salary and Fringe Maintenance Facility Cost Other Information: Supplies expenditures Miles Repaired Personnel (FTE Equipment lease $75,000 $30,000 $187,000 Gravel Road $65,000 100 3 Paved Road $80,000 70 4 $5,000 $600 $3,000 0.17 $7,000 $1,500 $4,500 0.10 Fixed charge/month Variable rate/mile State reimbursement/mile Max Miles Per Day Per Person Glenville Road Department Cost Analysis Mission: To maintain the county's 500 miles of roads which are both paved and gravel. You can assume that the construction of new roads is carried out by the state. Revenues: The state department of transportation will reimburse the county at a fixed rate per mile. Personnel: You can assume that all personnel are fulltime and work 225 days/year on average. All maintenance personnel receive the same wage, which will be $45,000 (including fringe costs) per year. The following is a summary of the activity and cost information that was put together for the most recent fiscal year. For simplicity no inflation was assumed in the initial costs analysis Glenville Road Department Actual Cost Data Director (Salary and Fringe) Secretary (Salary and Fringe Maintenance Facility Cost Other Information: Supplies expenditures Miles Repaired Personnel (FTE Equipment lease $75,000 $30,000 $187,000 Gravel Road $65,000 100 3 Paved Road $80,000 70 4 $5,000 $600 $3,000 0.17 $7,000 $1,500 $4,500 0.10 Fixed charge/month Variable rate/mile State reimbursement/mile Max Miles Per Day Per Person