Open a new Microsoft Excel document and rename its worksheet to “Profits”; This
ID: 2723026 • Letter: O
Question
Open a new Microsoft Excel document and rename its worksheet to “Profits”;
This worksheet must reflect the following fixed and variable costs for a hypothetical local muffin top store that has recently opened up and started operating:
Fixed Costs
USD per month
Building lease
2,182
Interest on loans
1,861
Utilities
924
Insurance
638
Property taxes
342
Maintenance
253
Variable Costs
USD per unit
Labor
0.84
Flour
0.33
Sugar
0.02
Milk
0.14
Eggs
0.12
Butter
0.21
Fillings
0.08
Baking powder
0.26
Calculate the store monthly total costs, revenues and profit given that the prevailing market price for an average muffin top is $3.5 and that the average monthly output is 9600 muffins.
Create two copies of “Profits” worksheet and name them “Breakeven1” and “Breakeven2”;
In “Breakeven1” worksheet use Excel’s Goal Seek feature to figure out how many muffin tops the store has to make during one month in order to break even provided that prevailing market price for an average muffin top is $2.5;
In “Breakeven2” worksheet use Excel’s Goal Seek feature to find out what prevailing market price for an average muffin top has to be in order for the store to break even provided that its maximum monthly output is only 5,000 units.
Fixed Costs
USD per month
Building lease
2,182
Interest on loans
1,861
Utilities
924
Insurance
638
Property taxes
342
Maintenance
253
Explanation / Answer
Total Variable cost = $2 / muffin top
Total Fixed cost = $6200 per month
“Breakeven1” = Total Fixed cost / Contribution = $6200 / $2.50 - $2 = 12400 muffin tops
“Breakeven2” = Total Fixed cost / Contribution = $6200 / Selling price - $2 = 5000 muffin tops (maximum)
=> 5000 (Selling Price) = 16200
=> Selling Price = 16200 / 5000 = $3.24 per muffin tops
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.