I need help doing this in excel
Scenario 1: COST-VOLUME-PROFIT ANALYSIS Compute the break-even point based on Bene Petits breakeven point based on the current mix of customer meals. o How many single-, dual- and family-sized meals must be sold to breakeven? o How much is total sales revenue at the breakeven point? What was Bene Petits margin of safety for the first year of operations? What is Bene Petits degree of operating leverage for the first year of operations? If sales increase by 10% during the second year of operations, how much operating profit will Bene Petit earn, assuming all other variables remain the same? If the sales mix shifts to 10% single serving, 20% dual serving and 70% family-size, how many total meals must be sold to earn $106,000 in net operating income. If Bene Petit wants to increase net operating income to $121,000 by changing only the selling price per serving, what should the new price be? Hint: Use the goal-seek function in excel (select data/what-if analysis/goal seek) to target net operating income by allowing the sales price per serving to change.
Note: Fill in formulas for all of the highlighted cells. Single Serving Dual Serving Family Size 15,000 Total $ Total 20,000 100% 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 Single Serving Dual Serving 3,000 5,000 15% 25% 1 2 3,000 10,000 750 1,250 3,000 5,000 6 6 10 Family Size 12,000 60% 4 48,000 3,000 12,000 24 61,000 5,000 20,000 40 $ 5.00 per customer serving 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) Customer Delivery Expenses ($2 per customer order) Total Variable Cost Per Customer Meal Donated Meals: Variable Manufacturing Cost ($1.25 per donated meal) Donation Delivery Expense ($125 per delivery) Total Variable Cost per Donated Meal 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 $ $ 1.00 per customer serving 0.75 per customer meal 60% of direct labor cost 2.00 per customer order $ #NAME? $ $ $ 75,000 29,000 40,000 144,000 (144,000) Price Per Serving Variable Costs: Customer Meals Direct Materials ($1 per serving) Direct Labor ($0.75 per customer meal) Variable Manufacturing Overhead 60% of DL Variable delivery expenses (customer meals) Donated Meals: Variable Manufacturing cost of donated meals Variable delivery expenses (donated meals) Fixed costs: Fixed manufacturing costs Fixed selling expenses Fixed administrative expenses Total Fixed costs $ $ 1.25 per donated meal 125.00 per delivery $ $ $ $ $ 75,000 29,000 40,000 144,000 $ Weighted Average Contribution Margin Average Contribution Margin Ratio Degree of Operating Leverage #DIV/0! Single Serving Dual Serving Family Size Total Break-even point (customer meals sold) Margin of Safety Note: Fill in formulas for all of the highlighted cells. Single Serving Dual Serving Family Size 15,000 Total $ Total 20,000 100% 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 Single Serving Dual Serving 3,000 5,000 15% 25% 1 2 3,000 10,000 750 1,250 3,000 5,000 6 6 10 Family Size 12,000 60% 4 48,000 3,000 12,000 24 61,000 5,000 20,000 40 $ 5.00 per customer serving 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) Customer Delivery Expenses ($2 per customer order) Total Variable Cost Per Customer Meal Donated Meals: Variable Manufacturing Cost ($1.25 per donated meal) Donation Delivery Expense ($125 per delivery) Total Variable Cost per Donated Meal 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 $ $ 1.00 per customer serving 0.75 per customer meal 60% of direct labor cost 2.00 per customer order $ #NAME? $ $ $ 75,000 29,000 40,000 144,000 (144,000) Price Per Serving Variable Costs: Customer Meals Direct Materials ($1 per serving) Direct Labor ($0.75 per customer meal) Variable Manufacturing Overhead 60% of DL Variable delivery expenses (customer meals) Donated Meals: Variable Manufacturing cost of donated meals Variable delivery expenses (donated meals) Fixed costs: Fixed manufacturing costs Fixed selling expenses Fixed administrative expenses Total Fixed costs $ $ 1.25 per donated meal 125.00 per delivery $ $ $ $ $ 75,000 29,000 40,000 144,000 $ Weighted Average Contribution Margin Average Contribution Margin Ratio Degree of Operating Leverage #DIV/0! Single Serving Dual Serving Family Size Total Break-even point (customer meals sold) Margin of Safety
