CASE STUDY Mead Meals on Wheels Center Input Average Average Fixed Cost Cost per week Revenue per client week Maximum Clients per week number of weeks in a quarter
Difference between bid abd break even Budgeted per client weekly food costs Actual Meals Delivered Delivered 1st Quarter Unit Cost Difference in 1st Quarter Actual Cost in 1st Quarter Additional fixed fixed weekly costs in 1st Quarter Cost of Additional Kitchen Equipment Cost of Capital Interest Rate on Loan Life of Equipment in years Maximum number under the contract Assumed Residual Value
Quarterly Fixed Costs per week
$ $
36,000 32.00 4,800 13
$ $
0.50 24.00
$ $ $ $
4,600 0.75 23.25 2,000 700,000 9% 8% 5 5,200 10%
Qua rte r1 Qua rte r 2 Qua rte r 3 Qua rte r 4 $ 38,000 $ 34,000 $ 35,000 $ 37,000
Question 1 is a variation of the break-even problem from Chapter 4. It asks you to calculate the maximum amount that MMWC can spend per person per week on food. In other words, what is the largest variable cost that MMWC can afford to pay and still cover all of its fixed costs.
From the case you know that the Unit Revenue is $32 per week. To earn that amount, MMWC must feed one person 2 meals per day for seven days or 14 total meals per week. Let's call that the number of contract meals. We know that MMWC's daily capacity is 9,600 meals per day. since the contract calls for feeding each person two meals per day, the means MMWC can feed 4,800 people per day (Q). (9,600 meals / 2 meals per person per day). It turns out that is also the number of people they can feed each week. Here is why: weekly capacity in meals 9,600 meals x 7 days People fed per week = --------------------------------------- = -----------------------------contract meals per person fed 2 meals per day x 7 days
= 4,800 people fed per week @ $ 32 per person
1
To find the formula for how much MMWC can spend per week per person on food, we need to do a little algebra. The base break-even formula is:
Break-even Quantity (Q) =
Where:
Fixed Cost (FC) ----------------------------------------------------Unit revenue (P) - Unit Variable Cost (VC)
Q = 4,800 P = $32 FC = $36,000
Q=
people fed per week per person fed for a week per week
FC --------------P - VC
multiplying both sides of the equation by P - VC we get: Q x (P - VC) = FC expanding the term on the left side we get: Q x P - Q x VC = FC Subtracting Q x P from both sides we get: -Q x VC = FC - Q x P Multiplying both sides by -1 we get: Q x VC = Q x P - FC Dividing both sides by Q we get the formula for the Break-Even Variable Cost:
VC =
Q x P - FC ------------------Q
Substituting the values above, we find that the Break-Even Variable Cost is: 4,800 x $32 - $36,000 VC = ------------------------------- = $24.50 4,800 $24.50 per person per week is the maximum amount that MMWC can spend for food.
If you think about this equation, it makes sense. The numerator says that you can only spend what you have left after paying your fixed costs on food. That is total revenue less fixed costs or $117,600 per week. Dividing that number by the maximum number of people MMWC can feed (the denominator 4,800) gives you the average cost per person fed. That is also the maximum MMWC can spend since FC + VC = TC = TR at break and even every dollar of revenue will have been spent.
2
The spreadsheet excerpt below shows the calculations: BE Variable Food Cost = (Total Revenue - Fixed Cost) / Maximum Meals per week - Q Total Revenue Fixed Cost Maximum Weekly Capacity in Clients Maximum Food Cost
$ $
153,600 36,000 4,800
$
24.50
Question 2 is a budgeting question based on Chapter 2. It asks you to generate a quarterly budget with a full-year summary using the information from Question 1 along with some additional data on the seasonality of MMWC's fixed costs and the fact that the lowest food-supply bid was $.50 below the break-even cost that was calculated in Question 1 or $24 per person per week. All of the input to the budget calculations is shown in the spreadsheet excerpt above.
Revenue Quarterly revenue equals the number of people served per week (4,800) times the number of weeks in a quarter (given as 13) times the amount Millbridge pays MMWC for each person it feeds ($32). That makes Quarterly revenue $1,996,800. Expenses Quarterly fixed expenses : The case tells us that fixed costs vary by quarter. They are $38,000 per week in the winter (1st Quarter), $34,000 per week in the second quarter, $35,000 in the third quarter and $37,000 in the fourth quarter. The calculations for quarterly fixed costs involves multiplying the weekly fixed costs per quarter by the number of weeks in a quarter. For the first quarter that is $38,000 13, or $494,000. Quarterly variable food costs are calculated by multiplying the number of people fed (4,800) by the number of weeks in a quarter (13) by the cost of the food ($24.50 – $.50 $24.00). This is equal to $1,497,000. MMWC's Quarterly budget is shown below. Notice that each quarter is shown as a column with the annual summary in the extreme right-hand column. That is standard practice.
Revenue
Annual Total $ 1,996,800 $ 1,996,800 $ 1,996,800 $ 1,996,800 $ 7,987,200
Expenses Variable Food Costs Fixed Costs
1,497,600 1,497,600 1,497,600 1,497,600 5,990,400 $ 494,000 $ 442,000 $ 455,000 $ 481,000 $ 1,872,000
Total Expenses
$ 1,991,600
Surplus/(Deficit)
$
Budget
Quarter1
Quarter 2
Quarter 3
Quarter 4
$ 1,939,600 $ 1,952,600 $ 1,978,600 $ 7,862,400
5,200 $
57,200 $
3
44,200 $
18,200 $ 124,800
Let's make sure these calculations are correct. Here is what we know: At average food costs of $24.50 per person per week, MMWC breaks even - the expected surplus would be zero. However, budgeted food costs were $24. That means MMWC will earn $.50 for every person it feeds x 4,800 people x 52 weeks = $124,800. That is exactly what our budget shows. Question 3 asked you to prepare variance analysis for the first quarter of the year. That was covered in Chapter 8. A complete analysis of variance requires that we compute: (i) a revenue variance , (ii) a cost variance , and add in the fixed-cost variance to get the full impact of the weather on MMWC's operations.
Lets start with the revenue variance . To do the analysis, we need to identify three factors: the actual and budgeted Volumes, the actual and budgeted Quantities, and the actual and budgeted Rates. We know the budgeted numbers from Question 1. Expected volume was 4,800 people x 13 weeks or 62,400 people fed while the actual volume was 4,600 people fed x 13 weeks. The budgeted rate was $32 as was the actual rate. Quantity is a bit less obvious. You can either say that there is no measure of resources used per person fed or us the variance convention that where there is no variation in resource use, Quantity = 1. The revenue variance analysis for MMWC is in the spreadsheet excerpt below. Column A
Column B
Column C
Actual Numbers Shown in Bold
Actual
Budget
Volume Quantity (NA = 1) Rate per unit Total Revenue
59,800 62,400 1 1 32.00 $ 32.00 $ $ 1,913,600 $ 1,996,800
Total = V * Q * R
Total Variance - Due to Volume
$
Total C - Total B
(83,200)
U
The total revenue variance is an Unfavorable ($83,200). All variance analyses always show the amount of the variance and indicate whether that variance was favorable or Unfavorable. Note that all of the revenue variance was due to volume. The solution to the variance analysis shown below uses a spreadsheet variation on the methodology shown in the text. . The numbers shown in bold are actual volume, quantity and cost/rate numbers. The ones shown in regular type are budgeted values. The formulas used to calculate each of the variance are shown in the column next to those calculations. Each calculation is based on the differences between the total in each column that is indicated. Those totals equal volume * quantity * cost /rate. The second component of the first-quarter variance was due to food costs. Here, there were two things at work. The first was the weather which reduced Volume from the budgeted level of 4,800 people per week to an actual of 4,600. The second was cost. budgeted Variable Cost was $24 per person while actual Variable Cost was $23.25. As in part one, Quantity was equal to one for both budget and actual. The expense variance analysis for MMWC is in the spreadsheet excerpt below.
4
Column A Actual Numbers Shown in Bold
Column B Actual Change in Cost
Column C
Column D
Column E
Change in Quantity
Change in Volume
Budget
59,800 59,800 59,800 Volume 62,400 1 1 Quantity (NA = 1) 1 1 $ 23.25 $ Cost per unit 24.00 $ 24.00 $ 24.00 $ 1,390,350 $ 1,435,200 $ 1,435,200 $ 1,497,600 Total Food Expense Total Food Expense = Volume * Quantity * Cost Calculations Total Variance $ 107,250 F Total E - Total B
Volume Variance Quantity Variance Cost Variance
$ $ $
62,400 44,850
F F F
Total E - Total D Total D - Total C Total C - Total B
Total Variance
$
107,250
F
Sum of flexible Variances
The total expense variance is a favorable $107,250. Of that amount, a favorable $62,400 was due to volume and there was a favorable $44,850 variance to cost. Note that the sum of the volume, quantity and cost variances is equal to the total variance. Notice that total variance appears twice in the both spreadsheet solutions. The first total-variance calculation is based on the difference between the total budgeted revenue or expense and the actual. The second calculation shows the sum of the Volume, Quantity and Rate or Cost variances. The second total variance calculation is meant as a check on the flexible variance calculations. These two number must be equal. If they are not, there is a mistake in your analysis. The fixed-cost variance was equal to $2,000 per week times 13 weeks for an Unfavorable total variance of ($26,000). The real question is what the overall impact of these factors was on MMWC's operations and whether is helped or hurt them. Notice, when we bring all of the variances together, we can talk about helping or hurting rather than just referring to the variances as Favorable or Unfavorable. That is because we now know the overall impact. The aggregate impact of the weather and lower-thanexpected food prices is shown below. Impact of Weather & Food Prices Change in Revenue $ (83,200) Change in Fixed Costs $ (26,000) Change in Food Costs $ 107,250 Profit Increased/(Fell by) $ (1,950) Impact as % of Budgeted Profit/(Loss)
U U F U -37.5%
We now know that the weather cost MMWC $109,200 ($86,200 + $26,000) while lower food prices offset $107,250 of that impact. Overall, MMWC, was $1,950 worse off than it expected to be. While a $1,950 overall variance is not a significant amount in terms of projected annual profits $124,800, it is 37.5% of expected 1st Quarter Profits of $5,200. That is a significant amount.
5
Question 4 is a capital budgeting problem from Chapter 5. You were asked to decide whether MMWC should spend $700,000 for some new kitchen equipment. The equipment will allow MMWC to prepare 10,400 meals per day. This is an increase of 800 meals per day. That means that MMWC can feed 400 more people per week with the addition of the equipment.
Notice that we have only included the marginal cash flows from the additional people MMWC can feed as a result of adding the equipment in our analysis. Marginal analysis was discussed in Chapter 4. If we were include the cash flow MMWC expects to generate during the period by feeding the original 4,800 people, we would be including amounts that were not generated as a result of the decision we are trying to evaluate. Capital budgeting analysis must be based on relevant cash flows. Whether or not MMWC were to decide to acquire the equipment, it would still have cash flow equal to the difference between its maximum break even food cost of $24.50 and the new food cost of $23.25 - a total of $1.25 per week per person fed. The relevant cash flows for the kitchen-equipment acquisition decision are those generated by the marginal people fed by the equipment. If that were to prove to not be enough, MMWC's management could choose to subsidize the equipment acquisition with funds from its core operation. However, it should only make that decision after looking at whether the equipment can be justified on a stand-alone basis and it knows how much of a subsidy it will need to provide. Each of the additional 400 people that MMWC feeds will generate gross revenues of $32. Offsetting this is the marginal (variable) cost of providing them with food. We know from the director’s instructions to use the new food bid that the marginal cost of food is $23.25 per person per week. ($24.50 – $1.25). So, each additional person that MMWC feeds results in a marginal “cash flow” (the excess of marginal revenue over marginal expenses) of $32 – $23.25, or $8.75 per person per week. Because the new equipment will let you feed 400 additional people per week, purchasing the equipment generates $3,500 ($8.75 per person times 400 people) worth of cash flow each week. To find out if this is enough to justify the purchase of the equipment, you need to do a net-presentvalue analysis. Buying the equipment gives MMWC $3,500 of cash flow per week or $45,500 per quarter (using 13 weeks per quarter). This is an annuity. The case tells you that your interest cost is 9% per annum, 2.25% per quarter (9% interest per annum divided by 4 quarters in a year). The other calculations for the periodic discount rate are similar. Here is the problem setup: Outflows
Purchase price = $700,000 Net Inflows for Quarterly Payments and Collections PMT = Cash Flow = $3,500 number of weeks in the period that you have chosen. (e.g., for a quarterly model $3,500 13 weeks = $45,500 per quarter) I interest rate = 9% divided by the number of periods in the year ( n) (e.g., for a quarterly model 9%/4 quarters per year = 2.25% per quarter)
Present Value of the benefits = Present Value of Annuity (pmt)
Cash Flow per period
Net Present Value = Present Value of the benefits minus purchase price
6
Based on weekly, quarterly and annual cash flows, the net present value of investing in the kitchen equipment is greater than zero. In other words, the equipment more than pays for itself even after we take the cost of capital into account. So, the organization should buy the equipment. Note that the net present values derived from the weekly and quarterly calculations are different. This is due to the timing and frequencies of cash flows in the two models. Theoretically, if MMWC gets paid weekly, the weekly model is more correct. In a real world example, the timing of the collections and disbursements from a capital investment could differ. In that case, you would lay the cash flows out on a timeline in your spreadsheet and calculate the PV of each inflow and outflow - either separately or as annuities if the timing allows for that - before finding the Net Present Value. Weekly Quarterly Annual Collections Collections Collections & Payments & Payments & Payments
Capital Budget Analysis
PV of Equipment Purchase Weekly Revenue from New Equipment Annual Cost of Capital Number of Compounding Periods per Year Periodic Discount Rate (I) Number of Periods (N) Present Value of Benefit from Equipment
$ $
$
700,000 $ 3,500 $ 9% 52 0.17% 260 732,295 $
700,000 $ 45,500 $ 9% 4 2.25% 20 726,349 $
Net Present Value
$
32,295 $
26,349 $
700,000 182,000 9% 1 9.00% 5 707,917 7,917
Question 5 asks you to prepare an incremental budget for the coming year. Let’s review what has happened:
Weekly people fed — up to 5,200 from 4,800, an increase of 400 per week Revenue per person-week — unchanged at $32 per person per week Food cost per person-week — $23.25, down from $24.00 Acquired $700,0000 of new equipment with a 5-year useful life Depreciation is on a straight-line basis Borrowed $700,000 @ 8% interest with interest only payments in the 1st year First quarter fixed costs — up by $2,000 per week because of actual experience Other quarterly fixed costs — unchanged
MMWC will add depreciation on $700,000 worth of equipment with a 5-year life and a 10% residual value. The depreciable basis for the equipment will be $700,000 - 10% x $700,000 = $630,000. Quarterly depreciation will be the depreciable base ($630,000) divided by the number of quarterly depreciation periods or 5 years x 4 quarters per year = 20 periods. Quarterly depreciation expense will be $31,500 ($630,000/20 quarters). MMWC borrowed $700,000 to acquire the equipment at an annual interest rate of 8%. Quarterly interest expenses will be $700,000 x 8%/4 quarters = $14,000 per quarter. The depreciation and interest calculations are shown below. Notice that both depreciation and interest expenses are shown quarterly and aggregated for the full year. That illustrates the accrual concept from Chapter 2. Under accrual accounting, expenses are shown when resources are used regardless of when they are actually paid for.
7
Depreciation Expenses Cost of Equipment Residual Value Depreciable Base Useful Life Annual Straight-Line Depreciation Quarterly Straight-Line Depreciation
$ $ $ $ $
700,000 70,000 630,000 5 126,000 31,500
$ $ $
700,000 56,000 14,000
Interest Expense Loan Amount Annual Interest Expense Quarterly Interest Expense
All of the rest of the calculations are the same as they are in Question 1. Using that methodology and the revised data, we get the following budget: Question 6: MMWC cannot expand beyond 5,200 people without adding more equipment and perhaps adding additional space and staff.
Revenue
Annual Total $ 2,163,200 $ 2,163,200 $ 2,163,200 $ 2,163,200 $ 8,652,800
Enpenses Variable Food Costs Fixed Costs Interest Expense Additional Depreciation
$ 1,571,700 $ 1,571,700 $ 1,571,700 $ 1,571,700 $ 6,286,800 520,000 442,000 455,000 481,000 1,898,000 14,000 14,000 14,000 14,000 56,000 31,500 31,500 31,500 31,500 126,000
Total Expenses
$ 2,137,200
Surplus/(Deficit)
$
Revised Budget
Quarter1
Quarter 2
Quarter 3
Quarter 4
$ 2,059,200 $ 2,072,200 $ 2,098,200 $ 8,366,800
26,000 $ 104,000 $
91,000 $
65,000 $
286,000
Reference Finkler, S.A., Purtell, R.M., Calabrese, T.D., & Smith, D.L. (2013). Financial Management for Public, Health, and Not-for-Profit Organizations, 4th Edition. Upper Saddle River, NJ: Pearson.
8
