Use Excel to work through the following exercises.

1. You have taken over as the director of the Lake 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 $8,000 per mile 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 the reimbursement to $5,000 per mile 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 deficit of 26 percent.

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 (2017-2018), which you summarized in the attached table. The road crew supervisors have informed you that 200 miles of road need to be paved. You also know from past experience that you need 1 worker for every 25 miles of road paved throughout the year. Using this information, you have laid out a series of tasks that you need to complete.

1. How many employees will you need to hire in order to pave 200 miles? (3 points)

2. What are the departments total fixed costs (name each individual cost type and add up the dollar amounts)? Include step-fixed costs in the total. (5 points)

3. What are the departments total variable costs per mile of road paved (name each individual cost type and add up the dollar amounts in per mile terms)? (3 points)

4. What are the break-even miles of road that the department would have to repair to cover their costs? Is this workload greater than, lesser than, or equal to 200 miles? (Hint: Treat the state reimbursement rate as the price variable in the formula) (6 points)

Lake County Road Department FY 2017-2018 Actual Cost Data

Director Salary and Benefits	$95,000
Secretary Salary and Benefits	$50,000
Maintenance Facility (depreciation)	$52,000
Supplies per mile repaired	$1,350
Road Crew Salary and Benefits Per Worker	$50,000
Miles of Road Paved Per Worker	25
Equipment Lease, Annual Fixed charge	$76,000
Per-Mile Equipment Use Charge	$1,100
State reimbursement per mile	$5,000

2. The Childrens Museum of Millbridge is considering catering birthday parties for members as a way to generate additional revenue. The cost of food and party favors is $10 per child. The costs of staff and decorations total $120 regardless of the number of children who attend.

1. Identify the fixed costs (3 points):

2. Identify the variable costs (3 points):

3. Solve for the break-even price per child assuming that 12 children attend each party. (5 points)

3. The Millbridge High School Student Association (MHSSA) is planning a fund-raising event for the spring semester. The MHSSA is planning to hire the Dizzy Gillespie Heritage Band as entertainment for a fee of $750. The Fine Arts Museum was selected as the site for the event. The Museum will charge the MHSSA $600 for the use of its banquet room. Good Eats Restaurant was selected to cater the event. Good Eats will charge the MHSSA a flat fee of $600 for the event and $20 per meal. MHSSA is planning on charging attendees a ticket price of $27.

1. Identify the fixed costs (3 points):

2. Identify the variable costs (3 points):

3. How many tickets will the MHSSA need to sell in order to break even (5 points)?