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 towns 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 departments budgets based on prior year data. He computed quarterly budgets as one-fourth of each departments 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 years budget, but in view of the rapid increases, he chose the prior budget less three percent as the current years 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 dont change, even if theres 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. Shouldnt my budget reflect this distinction? Also, my budget didnt include my January salary increase was I supposed to refuse it to help keep my budget in balance?

Finally, I think its important to note that I had to pay my drivers overtime because of some heavy storms we had back in August. I dont know the average rate, but its 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
   1. Total cubic miles of beach sand restored
   2. Total minutes plowed
   3. 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

1. Set up your Excel workbook with 5 tabs (worksheets)

• Input
• Budget method discussion (Question 1) and plan for finance director (question 5)
• Data and Pivot table include the pivot table with the data
• Flexible budget (Question 3)
• Price and efficiency calculations (Question 4)

You MUST link (cell reference) your inputs (from input tab) to your various computational worksheets. You should link key information from all worksheets to other worksheets as needed.

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