Project One Problem Two

Activity-Based Budgeting

The public works department of the local city government utilizes activity-based costing (activity-based budgeting)

for the following programs: electrical, water, buildings and grounds, highway and road maintenance. The following

information has been extracted from the records of highway and road maintenance budgeted data.

Direct materials of asphalt (per ton)

$80

Direct materials of concrete (per cubic yard)

$90

Estimated labor hours per activity

Pothole repair activities

6,844

Resurfacing activities

14,861

Sidewalk repair activities

10,378

Sealing cracks

2,708

Total

34,791

Estimated overhead per activity

Setup hours

1,000

Maintenance hours

500

Total inspections

650

Estimated overhaed cost per activity

Setup cost (per setup hour)

$25

Maintenance cost (per hour)

$35

Inspection cost (per inspection)

$250

The department projects 4,500 tons of asphalt, 2,700 cubic yards of concrete, and an average labor cost of $18 per hour.

Requirement: Determine the total budgeted costs for highway and road maintenance for the upcoming year of operations.

Total direct materials cost

Total direct labor costs

Total overhead setup costs

Total overhead maintenance costs

Total inspection costs

in excel:

1-Run a correlation analysis on this data set and determine the correlation coefficient between the city's population and revenues.

2- Calculate the revenue per capita for each year and the average revenue per capita over the ten year period for the the city.

3-If the city desires to have 445,000,000 in the next year what is the estimated number of new residents needed to achieve this goal?

4-If the city desires to have 430,000,000 in the next year what is the estimated number of new residents needed to achieve this goal?

5-If the city desires to have 420,000,000 in the next year what is the estimated number of new residents needed to achieve this goal?