The following table summarizes the operating results for Bene Petit’s first year of operations:

Bene Petit First year operating data:
	Single (1 serving)	Dual (2 servings)	Family (4 servings)	Total
Customer Meals Sold	3,000	5,000	12,000	20,000
Total Customer Servings	3,000	10,000	48,000	61,000
Customer Orders (Average = 4 meals per order)	750	1,250	3,000	5,000
Number of Donated Meals (1 per customer meal)	3,000	5,000	12,000	20,000
Number of Donated Deliveries (500 meals per delivery)	6	10	24	40

Additional information about selling prices, variable costs and fixed costs is summarized below:

The average sales price for customer meals is $5 per serving.

The average direct materials (ingredients) cost of customer meals is $1 per serving.

Direct labor costs average $0.75 per customer meal.

Variable manufacturing overhead costs are applied at a rate equal to 60% of direct labor.

Delivery expense for customer meals is $2 per customer order.

The incremental cost of producing the donated meals is $1.25 per meal.

Delivery expense for donated meals is $125 per delivery to community partners.

The following fixed costs are allocated to customer meals based on total sales revenue:

Fixed manufacturing overhead costs are $75,000 per year.

Fixed selling expenses are $29,000 per year.

Fixed administrative expenses are $40,000 per year.

Based on this information, you will prepare a segmented (contribution margin) income statement for Bene Petit. The statement should be created in Excel and should include formulas linking to the starting data so that you can change only one cell in the statement and everything else will recompute. This will allow you to perform “what if” and “goal seek” analysis, including the following independent decision scenarios.

Problem 1: COST-VOLUME-PROFIT ANALYSIS

Treat the following questions as independent scenarios, going back to the original spreadsheet and assumption to answer each question.

Use the starting spreadsheet to answer the following questions.

What was Bene Petit’s net operating income during the first year of operations? $36,000

What was the average contribution margin per customer meal sold? $9

What was the overall contribution margin ratio (as a percentage of total sales revenue)? 59.02%

What is Bene Petit’s degree of operating leverage for the first year of operations? 5

Break-even analysis

How many single-, dual- and family-sized meals must be sold to breakeven?

How much is total sales revenue at the breakeven point? $244,000

What was Bene Petit’s margin of safety (in total meals sold) for the first year of operations? 4,000

How many meals will Bene Petit donate to the homeless at the break-even point?

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.

How many total meals are needed to earn $126,000 in net operating income?

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

Perform a “what if” analysis to see how operating results will change if sales increase by 12% during the second year of operating.

What is the new net operating income?

What is the new degree of operating leverage?

If sales increased by 10% in the third year, what percentage growth in profit can the company expect?

What is the predicted operating profit in year 3?

Assume that the sales mix shifts to be 10% single serving, 20% dual serving and 70% family-size, with all other variables remaining the same?

What is the new average contribution margin per meal sold?

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

Assume that Taylor is considering raising the price per serving by 20%, but expects a corresponding drop in demand. How much would profit increase or decrease compared to the starting profit of $36,000?

If Bene Petit wants to increase net operating income to $121,400 by changing only the selling price per serving, what should the new price be?