Part VI.Glenville Cost Analysis (35 points)- please upload this portion of the exam as a separate excel workbook in addition to providing written answers where necessary.

You have taken over as the director of the Glenville County Road Department that is composed of a division for Gravel Roads and a division for Paved Roads. The county executive hired you precisely because of your background in finance and budgeting. Please complete the following tasks. Your answer should be uploaded as an excel workbook for this part of the exam (no need for a memo). Your written answers can be simply provided below. Be sure to show your work in the excel sheet using formulas and format the provided startup sheet to make it more presentable (points for style).

Task I. Develop a budget (for a single fiscal year) for each mission center (and in total) using information from the table included below and in the attached spreadsheet (please format appropriately). 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). I have included an excel sheet to get you started and you can use any approach you feel comfortable with to calculate cost/revenue. I just need to see your work.

Task II.Create a budget (as a separate sheet within the same excel workbook) for just the gravel road division assuming that output varies by 20% above and below last year's output level. Remember to consider the shared costs for the gravel road division allocated in the original analysis and other items that may vary by service level.

Glenville Road Department Cost Data

Mission:To maintain the county's roads which are both paved and gravel.

Shared Costs:Both Gravel Road and Paved Road divisions share the cost of the Director, Secretary, and Maintenance Facility.

Supplies:Supplies are a variable cost that varies based on the number of miles of road repaired.

Personnel:You can assume that all personnel are full-time and work 225 days/year on average. All maintenance personnel receive the same wage, which will be $52,000 (including fringe costs) per year. There is a max number of miles per year that a single person can produce for each division as specified in the table that can be used to calculate the number of personnel required.

Equipment Lease:The cost of equipment for both Gravel and Paved Road divisions will be treated as a semi-variable cost.

Revenues:The state department of transportation will reimburse the county at a fixed rate per mile.

The following is a summary of the activity and cost information that was put together for the most recent fiscal year.

Glenville Road Department Actual Cost Data
Director (Salary and Fringe)	$82,000
Secretary (Salary and Fringe)	$40,000
Maintenance Facility Cost	$156,000
Other Information:	Gravel Road	Paved Road
Supply costs per mile	$260	$450
Employee (Salary and Fringe)	$52,000	$52,000
Miles Repaired (in a year)	250	180
Equipment lease:
Fixed charge/month	$5,000	$7,000
Variable rate/mile	$600	$1,500
State reimbursement/mile (revenue)	$3,000	$5,000
Max Miles Per Person Per Year	36	21