Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Town of Madeira Joe Smith, supervisor of the beach sand restoration division at the Public Works department in Madeira Beach, received the following memo from

Town of Madeira

Joe Smith, supervisor of the beach sand restoration division at the Public Works department in Madeira Beach, received the following memo from the new Finance Director for the city in October.

"As you know, I have instituted a new responsibility accounting system. From now on, you will receive quarterly reports that compare the costs of operating your department with your operating budget. The reports will highlight the variances from budget. Basically, responsibility accounting means that you are to keep the costs within your department within your budget. Your first report is attached (Exhibit 1).

Joe, your report indicates that several of your costs are significantly above budget. Please get back to me next week with a plan for making needed reductions."

Joe knew he needed a plan, yet he was at a loss for suggestions for improvements. He wondered why he felt as if his department had worked so hard yet were still under the microscope.

BACKGROUND

Madeira Beach is a beach town located on the west coast of Florida. It never had a large public works staff, but tourism had grown so that that department was now considered critical for the town's continued clean image. The new Director of Finance clearly understood budgets and responsibility accounting. Previously, budget and cost information had been presented to department heads infrequently, but the new director was certain more frequent information would be useful as a performance and motivational tool.

The new director had prepared all the department's budgets based on prior year data. He computed quarterly budgets as one-fourth of each department's annual budget. To prepare the budgets, he had analyzed the prior three years' costs, and, in doing so, had learned that almost all of the departments had increased their costs each year. His first thought had been to establish the average of the three years budgets as the current year's budget, but in view of the rapid increases, he chose the prior budget less three percent as the current year's budget. For the sand restoration department, he also estimated the cubic miles of sand to be restored, which he set at an average of the past three years.

Sand is restored to the beaches when storms pull the sand out to sea. Madeira had been doing more restoration work as tourism had increased. The town felt clean beaches, with plenty of sugar white sand, was critical to its continued draw as a tourist destination. Crews use front-end loaders to push the sand back over the beach as quickly as possible after a heavy storm.

Joe received the report shown in Exhibit 1 in early October. He reflected on its content:

Some of my costs don't change, even if there's a change in the miles of sand restored. On the other hand, drivers, supplies, fuel and maintenance vary almost directly with changes in miles restored. Shouldn't my budget reflect this distinction? Also, my budget didn't include my January salary increase - was I supposed to refuse it to help keep my budget in balance?

Finally, I think it's important to note that I had to pay my drivers overtime because of some heavy storms we had back in August. I don't know the average rate, but it's likely higher than the standard of $18 per hour. I also think the drivers were very efficient. You have a standard number of minutes per mile of beach sand plowed at 48 minutes, but I bet we beat that. And we had a lot of storms! I just wish I could quantify the effects of these items.

ASSIGNMENT:

You are provided with an Excel workbook with data (w below).

1.What is your assessment of the method the public works finance director used to construct the budget?

2.Construct a pivot table from the data given in the data table In the Excel workbook. Use the storm numbers as your rows. Calculate

a.Total cubic miles of beach sand restored

b.Total minutes plowed

c.Average Driver rate by storm

3.Based on the results of 2 above, prepare a flexible budget for the sand restoration department.

4.Based on the results of 2 above, prepare price and efficiency variances for the drivers costs.

5.Summarize your results for management. What points should Joe make to the budget director?

HINTS

Set up your Excel workbook with 5 tabs (worksheets)

1. Input

2. Budget method discussion (Question 1) and plan for finance director (question 5)

3. Data and Pivot table - include the pivot table with the data

4. Flexible budget (Question 3)

5. Price and efficiency calculations (Question 4)

STORM ID Cubic miles restored Driver ID Minutes plowed Driver rate
1 7 1 339 19.5
3 14 3 656 22
7 17 2 797 21.5
4 8 1 374 19.5
1 11 1 527 19.5
6 18 2 844 21.5
5 9 1 421 19.5
5 12 3 562 24
9 15 2 703 21.5
12 10 3 468 22
9 13 3 609 22
9 16 2 750 21.5
1 7 1 339 19.5
12 14 1 656 19.5
3 17 3 797 22
9 8 2 374 23
5 11 1 515 19.5
6 18 1 844 19.5
12 9 2 421 21.5
8 12 1 562 19.5
9 15 3 703 22
3 10 2 468 21.5
11 13 3 609 22
12 16 3 750 22
6 7 2 327 21.5
2 14 1 656 19.5
3 17 1 797 19.5
9 8 3 374 22
5 11 2 515 21.5
6 18 1 844 19.5
12 9 1 421 19.5
8 12 2 562 21.5
9 15 1 703 22
3 10 3 468 22
11 13 2 609 21.5
5 16 3 750 22
1 7 3 339 22
2 14 2 656 21.5
8 17 1 797 19.5
4 8 1 374 19.5
5 11 3 515 23
11 18 2 844 21.5
7 9 1 421 19.5
1 12 1 574 19.5
9 15 2 703 22
10 10 1 468 19.5
4 13 3 609 22
12 16 2 750 21.5
1 16 3 762 22
7 14 3 656 22
3 17 2 797 21.5
4 8 1 374 19.5
10 11 1 515 19.5
6 18 3 844 22
7 9 2 421 21.5
1 12 1 574 19.5
9 15 1 703 20.5
3 10 2 468 21.5
11 13 1 609 19.5
12 16 3 750 22
6 7 2 327 21.5
2 14 3 656 22
3 17 3 797 22
9 8 2 374 21.5
5 11 1 515 19.5
11 18 1 844 19.5
7 9 3 421 22
8 12 2 562 21.5
2 15 1 723 19.5
10 10 1 468 19.5
4 13 2 609 21.5
12 16 1 750 19.5
1 15 3 730 22
2 18 2 844 21.5
8 9 3 421 22
4 16 3 750 22
5 8 2 374 21.5
11 10 1 468 19.5
7 13 1 609 19.5
1 9 3 433 22
9 12 2 562 22
10 14 1 656 19.5
4 17 1 797 19.5
12 13 2 609 21.5
6 16 1 750 19.5
2 18 3 844 22.5
3 18 2 844 21.5
4 9 3 421 22
10 12 3 562 22
6 7 2 327 21.5
7 10 1 468 19.5
2 13 1 609 19.5
9 16 3 750 22
4 11 2 535 21.5
11 14 1 656 20
12 17 1 797 19.5
7 20 2 938 21.5
3 15 1 703 20
9 18 3 844 23.1
5 16 2 750 22.3
6 8 3 394 22.5
7 11 3 515 22
1 14 2 668 21.5
9 9 1 421 20
10 32 2 1502 22
4 15 2 703 21.5
12 27 1 1267 20
6 13 1 609 19.5
2 11 1 535 21
3 9 3 434 22
9 12 2 562 21.6
5 17 1 797 20
11 18 1 844 19.5
7 11 2 515 21.5

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Beyond Agile Auditing Three Core Components To Revolutionize Your Internal Audit Practices

Authors: Clarissa Lucas

1st Edition

1950508676, 978-1950508679

More Books

Students also viewed these Accounting questions