Required:

What is Year 4s budgeted operating income?

What is the contribution margin per customer meal sold?

What is the break-even point (in customer meals)

What is the margin of safety (in total customer meals)?

For this spreadsheet, enter formulas in the yellow cells linking to the assumptions on the right. Check figure : Total Variable Expenses (Cell F39) = $156,250
Master Budget	20%	24%	26%	30%			Assumptions
Sales Forecast	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total		Sales Mix
Single Serve Meals	750	900	975	1,125	3,750		15%
Dual Serve Meals	1,250	1,500	1,625	1,875	6,250		25%
Family Sized Meals	3,000	3,600	3,900	4,500	15,000		60%
Total Customer Meals	5,000	6,000	6,500	7,500	25,000		25000	Total Customer Meals
Total Donated Meals	5,000	6,000	6,500	7,500	25,000		1 to 1	Donated Meals
Number of Servings							Serving sizes
Single Serve Meals	750	900	975	1,125	3,750		1
Dual Serve Meals	2,500	3,000	3,250	3,750	12,500		2
Family Sized Meals	12,000	14,400	15,600	18,000	60,000		4
Total Servings	15,250	18,300	19,825	22,875	76,250
1. Sales Budget
Sales Revenue							$ 5.00	Sales price per serving
2. Ingredients Purchases Budget
Total Pounds of Ingredients Needed							0.5	pounds per serving
Ending Inventory of IngredientsRaw Materials				200	200		2%	of next quarter DM needs
Beginning Inventory of Ingredients	(200)				(200)		2%	of current quarter DM needs
Pounds of Ingredients Purchased
Cost per Pound	$ 2.00	$ 2.00	$ 2.00	$ 2.00	$ 2.00		$ 2.00	per pound of ingredients
Total Ingredients Purchased
3. Variable Manufacturing Cost Budget							Variable Cost Standards	SQ	SP	Unit Cost
DM (Ingredient) Costs	$ (15,250)	$ (18,300)	$ (19,825)	$ (22,875)	$ (76,250)		DM (Ingredient) Costs	0.5	$ 2.00	$ 1.00	per serving
Direct Labor	$ (4,000)	$ (4,800)	$ (5,200)	$ (6,000)	$ (20,000)		Direct Labor	0.04	20	$ 0.80	per meal
Variable Overhead (50% of DL)	$ (2,000)	$ (2,400)	$ (2,600)	$ (3,000)	$ (10,000)		Variable Overhead (50% of DL)	0.04	10	$ 0.40	per meal
Total Variable Manufacturing Costs	$ (21,250)	$ (25,500)	$ (27,625)	$ (31,875)	$ (106,250)
4. Variable Selling and Administrative Expenses
Donated Meals (Production Cost)	$ (6,250)	$ (7,500)	$ (8,125)	$ (9,375)	$ (31,250)		Donated Meals (Production Cost)		$ 1.25	per donated meal
Delivery Expense (Customer Meals)	$ (2,500)	$ (3,000)	$ (3,250)	$ (3,750)	$ (12,500)		Delivery Expense (Customer Meals)		$ 0.50	per customer meal
Delivery Expense (Donated Meals)	$ (1,250)	$ (1,500)	$ (1,625)	$ (1,875)	$ (6,250)		Delivery Expense (Donated Meals)		$ 0.25	per donated meal
Total	$ (10,000)	$ (12,000)	$ (13,000)	$ (15,000)	$ (50,000)
Total Variable Expenses	$ (31,250)	$ (37,500)	$ (40,625)	$ (46,875)	$ (156,250)
Contribution Margin	$ (31,250)	$ (37,500)	$ (40,625)	$ (46,875)	$ (156,250)
							Fixed Expenses
Fixed Production Overhead	$ (9,375)	$ (9,375)	$ (9,375)	$ (9,375)	$ (37,500)		Fixed Production Overhead	$ 37,500	per year
Fixed selling expenses (website, etc)	$ (7,250)	$ (7,250)	$ (7,250)	$ (7,250)	$ (29,000)		Fixed selling expenses (website, etc)	$ 7,250	per quarter
Fixed administrative expenses (office assistant)	$ (6,500)	$ (6,500)	$ (6,500)	$ (6,500)	$ (26,000)		Fixed administrative expenses (office assistant)	$ 26,000	per year
Total Fixed Expenses	$ (23,125)	$ (23,125)	$ (23,125)	$ (23,125)	$ (92,500)
Operating Profit	$ (54,375)	$ (60,625)	$ (63,750)	$ (70,000)	$ (248,750)
Contribution Margin per Customer Meal (Total Contribution Margin / Total Customer Meals)
BE Units (-Total Fixed Costs / CM per Customer Meal)		Note: Make sure fixed costs are stated as a positive number for Break-even.
Margin of Safety (Total Customer Meals - Break-even Meals)

Assume Bene Petit is in its third year of operations and is on track to report a small profit during Year 3 after barely breaking even in Year 2. For this assignment, you will help Taylor budget for Bene Petit's operations in Year 4 so that she can plan for operating expenditures and determine how much (if any) profit she can withdraw from the business or reinvest to fuel future growth. Based on the upward trend in Bene Petit's sales over the first three years, industry estimates of growth in the mealpreparation service sector, and Taylor's plans to invest more heavily in online advertising to expand Bene Petit's customer base, Taylor has estimated Bene Petit's production and sales for each quarter of Year 4 as follows: Other operating plans including the following: Pricing - The retail price for customer meals is $6 per serving. However, after promotional discounts and other advertising promotions are factored in, Taylor estimates that her average sales price will be $5 per serving in Year 4 . Manufacturing Costs (Customer Meals only) - Customer meals are produced "just in time" for delivery to the customer, so there is no Finished Goods Inventory. - Purchases of raw materials (ingredients) are based on the number of servings in each meal (single-serving = one serving, dual-serving = two servings, family-sized = four servings). Each serving should require about one-half pound of raw ingredients at an average standard cost of $2 per pound. Bene Petit maintains a small inventory of staple ingredients equal to 2% of the next quarter's production needs. Assume Bene Petit will have 200 pounds of raw material on hand at the beginning and end of Year 4. - Direct labor wages vary with the number of customer meals, regardless of serving size. Each worker can make and package about 25 customer meals per hour and the average labor rate (including taxes and benefits) is $20 per direct hour. - Variable production overhead costs (for power, packaging materials, etc.) are applied at a rate equal to 50% of direct labor. - Annual fixed manufacturing costs of $37,500 are spread evenly across the total number of customer meals sold. Selling and Administrative Costs - The standard cost of donated meals is treated as a variable selling expense. The standard cost of a donated meal is $1.25 per meal. - Delivery expenses for customer meals should average $2 per customer order with an average order size of 4 customer meals per delivery. - Donated meals are delivered to community partners in batch sizes of 500 at a standard cost of $125 per delivery. - Fixed selling expenses for website hosting and advertising are budgeted at $7,250 per quarter. - Fixed administrative expenses are budgeted at $26,000 per year