Use the $“$goal seek$”$ function in Excel to determine how many customer meals $($in total and by product line$)$ must be sold to earn $$126,000$ in net operating income. Hint: Use the $“$goal seek$”$ function to change net operating income to $$126,000$ by changing only the cell that contains the total number of customer meals sold.

a$.$ How many total meals must be sold to earn $$126,000$ in net operating income?

b$.$ How much total sales is required to earn $$126,000$ in net operating income?

Total Sales Revenue ($5 per serving) Less: Variable costs: Customer Meals: Direct Materials ($1 per customer serving) Direct Labor ($0.75 per customer meal) Variable Manufacturing Overhead (60% of Direct labor) Variable Manufacturing Cost ($1.25 per donated meal) Customer Delivery Expenses ($2 per customer order) Donation Delivery Expense ($125 per delivery) Total Variable Costs Total Contribution Margin Less: Fixed Costs Allocated Based on Sales Revenue Fixed Manufacturing Expenses Fixed Selling Expenses Fixed Administrative Expenses Total Fixed Expenses Net Operating Profit Average Contribution Margin Per Meal Sold Average Contribution Margin Ratio (% of Revenue) Break-even point (customer meals sold) Break-even point (total sales revenue) Margin of Safety (Customer Meals) Margin of Safety (Sales $) Degree of Operating Leverage Single Serving Dual Serving Family Size 15,000 $ 50,000 $ 240,000 $ $ $ $ $ $ 3,000 $ 2,250 1,350 3,750 1,500 750 12,600 $ $ 2,400 $ 10,000 $ 3,750 2,250 6,250 2,500 1,250 26,000 $ 3,689 $ 1,426 1,967 7,082 (4,682) $ Single Serving Dual Serving 0.80 $ $ 12,295 $ 4,754 6,557 23,607 48,000 $ 9,000 5,400 15,000 6,000 3,000 86,400 $ 59,016 $ 22,820 31,475 113,311 393 $ 40,289 $ 24,000 $ 153,600 $ 180,000 Family Size 4.80 $ 12.80 Single Serving Dual Serving Family Size Total 305,000 $ 61,000 15,000 9,000 25,000 10,000 5,000 125,000 75,000 29,000 40,000 144,000 36,000 Overall 9.00 59.02% Total Input Values: Number of Customer Meals Sales mix (% of customer meals) Number of Servings per Meal Number of Servings Number of Customer Orders Number of Donated Meals Number of Donated Deliveries Price Per Serving Variable Costs: Direct Materials ($1 per serving) Direct Labor ($0.75 per customer meal) Variable Manufacturing Overhead 60% of DL Variable cost of donated meals Variable delivery expenses (customer meals) Variable delivery expenses (donated meals) Fixed costs: Fixed manufacturing costs Fixed selling expenses Fixed administrative expenses Total Fixed costs 60% Dual Single Serving Serving 3,000 5,000 15% 25% 1 2 4 3,000 10,000 48,000 61,000 1,250 3,000 5,000 5,000 12,000 20,000 10 24 40 750 3,000 6 $ $ $ $ $ $ $ $ $ $ Family Size 12,000 5.00 per customer serving 1.00 per customer serving 0.75 per customer meal 60% of direct labor cost 1.25 per donated meal 2.00 per customer order 125.00 per delivery 75,000 29,000 40,000 144,000 Total 20,000 100%