Question

Select a single product line that you could make yourself. Examples of possible products could be cookies, handmade craft items or custom printing. Assume that you have decided to start a small business producing and selling this product. Apply the concepts of cost-volume-profit analysis to this potential venture to calculate on a per unit basis the following:

5 year Projected Sales (In units)

Contribution margin per unit  

Break even point in units

Margin of safety in units

Target profit in units

I picked Cakes, but I am struggling on how to format it in an excel worksheet.

Answer 1

X 2019 10 01.xlsx - Microsoft Excel Sign in INSERT PAGE LAYOUT VIEW FILE НОМE FORMULAS DATA REVIEW DEVELOPER fox К1 CA K A C D E G н L M O P 1 Information 2 Delight cake shop sold Choco cake. $ 3 Selling price per cake 15.00 $ 4 Ingredients cost per cake 2.40 5 Labor cost per cake $ 1.25 6 Variable overhead per cake 0.35 7 Annual Fixed costs $ 66,000 8 9 The expected sales for 5 years are as follows. 10 Year Year 1 Year 3 Year 2 Year 4 Year 5 11 Projected Sales (In units) 6,800 7,500 7,000 7,800 8,500 12 13 How many cakes sold by compant each year to earn annual profit of $22,000? 14 15 Answer: Year Year 2 Year 4 16 Year 1 Year 3 Year 5 $ 2.40 $ 2.40 $ 2.40 $ 2,40$ 2.40 17 Ingredients cost per cake 1.25 S $ 1.25 $ 1.25 $ 18 Labor cost per cake 1.25 $ 1.25 0.35 $ 0.35 $ 0.35 $ 0.35 $ 19 Variable overhead per cake 0.35 20 Variable Cost per cake $ 4.00 $ 4.00 $ 4.00 $ 4.00 S 4.00 21 22 Selling price per cake $ 15.00 $15.00 $15.00 $15.00 $ 15.00 4.00 $ 4.00 $ 4.00 $ 23 Less: Variable Cost per cake 4.00 $ 4.00 11.00 $ 11.00 $ 11.00 $ 11.00 $ 11.00 24 Contribution margin per unit 25 66,000 $ 66,000 $ 66,000 $ 66,000 $66,000 26 Annual Fixed costs 27 Divided by: Contribution margin per unit $ 11.00 11.00 11.00 $ 11.00 $11.00 Activate Windows- Go to Settings te activate Windews 28 Break even point in units 6.000 6.000 6.000 6.000 6.000 . Sheet233 Sheet234 Sheet235 Sheet236 Sheet237 Sheet238 Sheet239 Sheet240 100% READY EEE 08:22 ENG Search the web and Windows 26-11-19 US

X 2019 10 01.xlsx - Microsoft Excel Sign in PAGE LAYOUT VIEW FILE НОМE INSERT FORMULAS DATA REVIEW DEVELOPER К40 Са K A В C D E G н L M O P 14 15 Answer: Year 2 16 Year Year 1 Year 3 Year 4 Year 5 2.40 $ $ 2,40 $ 2,40$ 2.40 $ 17 Ingredients cost per cake 18 Labor cost per cake 19 Variable overhead per cake 2.40 1.25 $ $ 1.25 $ 1.25 $ 1.25 $ 1.25 0.35 $ $ 0.35 $ 0.35$ 0.35 $ 0.35 20 Variable Cost per cake 4.00 $ 4.00 $ 4.00 $ 4.00$ 4.00 21 15.00 15.00 15.00 $ 15.00 $ 15.00 $ 22 Selling price per cake 23 Less: Variable Cost per cake 4.00 $ 4.00 $ $ 4.00 $ 4.00 $ 4.00 11.00$11.00 11.00 $11.00 $ 11.00 24 Contribution margin per unit 25 $ 66,000 $66,000 $66,000 $66,000 $66,000 26 Annual Fixed costs 27 Divided by: Contribution margin per unit 11.00 $ 11.00 11.00 $ 11.00 $ 11.00 $ 28 Break even point in units 6,000 6,000 6,000 6,000 6,000 29 30 Projected Sales (In units) 6,800 7,800 7,000 7,500 8,500 31 Less: Break even point in units 6,000 6,000 6,000 6,000 6,000 32 Margin of safety in units 1,500 800 1,000 1,800 2,500 33 22,000 $ 22,000 $ 22,000 $ 22,000 $ 22,000 34 Taget Profit 35 Add: Annual Fixed costs 36 Contribution margin required 37 Divided by: Contribution margin per unit $ 66,000 66,000 $66,000 $ 66,000 $ 66,000 $88,000 88,000 $ 88,000 88,000 $ 88,000 $ 11.00 $11.00 $11.00 $11.00 $ 11.00 38 Units sold to earn profit of $22,000. 8,000 8,000 8,000 8,000 8,000 39 40 Activate Windows- Go to Settings e activate Winelews 41 Sheet237 Sheet233 Sheet234 Sheet235 Sheet236 Sheet238 Sheet239 Sheet240 100% READY 08:22 ENG Search the web and Windows 26-11-19 US