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).

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

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

Total cubic miles of beach sand restored

Total minutes plowed

Average Driver rate by storm

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

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

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

HINTS

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)

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