You are the manager of the Public Works Department for the city of Winston, NJ. One of your duties as a manager is to prepare an annual budget. Information for preparing the budget is shown below.

Public Works Employee Costs with Benefits:

• Salary of department manager: $90,000 (exempt)

• Salary of administrative assistant: $52,000 (exempt)

• Public Works employees (hourly): $24 per hour (average wage); 10 FTE (Full-time employees, equivalents); hours worked=1780 per year

• Holiday pay: 10 holidays per year

• Average vacation per FTE: 20 days

• Average day worked=8 hours (paid)

• Overtime budgeted: 5% of hours worked above

• Overtime is paid at 1.5 times the average wage

• Medical cost per employee=$14,000 per year

• Dental cost per employee=$1500 per year

• Sick leave pay accrues at 1 day per month per employee (hourly only)

• Retirement (401 k); 9% of base wage only; do not allocate to overtime

Operating Costs:

• $1.25 per mile operated (trucks—heavy); estimated miles=1,000,000

• $ 0.75 per mile operated (trucks—light); estimated miles=1,580,000

• The rental charge for facilities (Public Works)—paid to an outside owner: $10,000 per month

• Rental for equipment (internal fund): $2000 per month

Supplies:

• Blacktop Patch and other related supplies=$750,000

• Salt=$30,000 per application; 5 estimated applications for the year

City Department Budget

Formulate a statement of activities budget (based on the information provided above) on an Excel spreadsheet or Word document. Please list a computation for each charge above. Show and label all computations. You will be graded on accuracy and also on your presentation. The City Department Budget file should be attached as a separate file with your last name on the file.