use the excel format when answering please VP Analysis at Mo

use the excel format when answering please!

VP Analysis at Molly\'s Cupcakes1 (B) Recall Molly\'s information $34,000 37,100 Revenue - Expense Profit (loss) S3,1002 Management responded with the following information Costs have been matched to revenue to determine what costs should appear as expenses. The costs of goods remaining in inventory are not included in expenses. . There are no fixed manufacturing costs. Cost of sales is 40% of revenue $8,500 of the selling and administrative expenses are variable Costs and selling prices are expected to remain stable for at least the next two years. Based on this information you created the following contribution margin format income statement and used it to analyze several alternatives for management. 0 Revenue Revenue Variable COGS Variable S&A; CM Fixed Cost Profit 34,000 13,600 40.00% 8,500 25.00% 11,900 35.00% 15,000 44.12% 3100-91296 Management is now wondering how sales mix will affect their financial picture. It provided the following information. The current sales mix, computed as percent of total units, is 80% regular cupcakes and 20% deluxe cupcakes. The total number of cupcakes currently being sold is 10,000 . Regular cupcakes sell for $3.00 and have a contribution margin of $0.95 per unit. . Deluxe cupcakes sell for $5.00 and have a contribution margin of $2.15 per unit. Management believes it can increase sales of deluxe cupcakes by rearranging the display case. Its best estimate is that deluxe cupcakes would increase to 48% of cupcakes sold t believes it will continue to have the same number of customers and sell the same number of cupcakes.

Solution

Regular Deluxe Total Regular Deluxe Total Variables Unit Sales 7800 7200 15000 7800 7200 15000 CM /unit 0.95 2.15 1.55 2.95 Total fixed costs 15000 25000 Wt. av. Contn. 1.526 2.222 Scenario 1 Scenario 2 Revised sales mix Revised sales mix Regular Deluxe Total 8 & 9 Regular Deluxe Total Unit sales 7800 7200 15000 7800 7200 15000 Sales $ 23400 36000 59400 23400 36000 59400 Variable costs 15990 20520 36510 11310 14760 26070 Contribution Margin 7410 15480 22890 12090 21240 33330 Fixed costs 15000 25000 Net Operating Income 7890 8330 (Fixed costs/Wt.av.CM/unit) BEP (units) (15000/1.526) 25000/2.222= 9830 11251 Break-up 5111 4718 15000 5851 5401 11251 SP/unit 3 5 3 5 10..Total B/E sales $ 15334 23591 38925 17552 27003 44554 10..MOS% (59400-38925)/59400= (59400-44554)/59400= 34.47% 24.99% 10.. Operating Leverage Total $ contn/Net income 22890/7890= 33330/8330= 2.90 4.0 10 & 12..Profit Sensitivity : Up by 30%-Scenario 1--(30%*2.9) 87% Up by 30%-Scenario 2--(30%*4) 120% Down by 30%-Scenario 1(-30%*2.9) -87% Down by 30%-Scenario 2---(-30%*4) -120% 11. Scenario 2 has more inherent risk as when sales goes down by 30%, profits will go down by 120%, where as Scenario 1 \'s profits sway by a lesser % when sales go down.