Question

e Layout Formulas DataReviewView Tell me what you want to do. S Cut a Copy y Format Painter Cipboard B 1--| O. A- 器| Conditional Format as Formatting Table- 트트闇Merge & Center , s-% , Font Alignnent E38 1Complete the spreadsheet to calulate break-even point and revenue and costs at a given volume for the fol 2 Plot revenue and cost showing the break-even point. 3 Plot revenue and cost showing the break-even point with a $4 per bowl price. s Matthew has an ice cream business, selling homemade ice cream at events. The relevant data is given below $3 per bowl 9 Fixed Costs 10 Entry fee 11 Travel 12 Overhead Variable costs $400 Ingredients: $0 600 Labor $1.100 $20 Supplies 0.150 14 Total 15 Waste Fuel Total $0.300 50.500 17 Break Even Point-Fixed costs/ (Price Variable costs) 18 Profit-(Price- Variable Costs)"a- Flxed Costs Revenue Price x quantity Cost - Fixed costs+ (Variable Cost) a Break even pt sybowls) 22 5 23 Quantity 3 per bowl analysis Cost 25 27 Break even pt SAlbowls) 35 $4 per bowl analysis 6 Quantity Break Even

Answer 1

Total fixed costs =SUM(B10:B12)=670

Total variable costs =SUM(F10:F14)=2.65

Break even point(@ $4 per bowl) =B14/(3-F15)=1914.29=1915 (rounding up)

Similarly for 4 per bowl

Calculations with plot are as shown below :

$3 per bowl:

F17 B14/(3-F15) $3 per bowl 8090 8400 9 Fixed Costs 10 Entry fee 11 Travel 12 Overhead Variable costs Ingredients Labor Supplies Waste Fuel Total 703 0.6 1.1 0.15 0.3 0.5 200 57230 4910 14 Total 4800 3850 Revenue 3600 --Cost 2790 16 17 18 $3 per bowl analysis 19 Quantity 3000 2000 1000 Break even pt@ $3 1914.29 1730 2400 Revenue Cost 1200 21 1730 2790 3850 4910 2400 500 1000 500 2000 2500 3000 1600 5745 5970 72007030 26

$ 4 per bowl

B$14 (A20 SF$15) $4 per bowl 12000 11200 9 Fixed Costs 10 Entry fee 11 Travel 12 Overhead 13 14 Total Variable costs Ingredients Labor Supplies Waste Fuel Total 0.6 1.1 0.15 0.3 0.5 10000 7030 5970 Revenue 4800 1 4910 Cost Break even pt @ $4 Г I 4000 3200 3850 18 $4 per bowl analysis 19 Quantity 1984 16 2790 Revenue Cost 21 1600 1730 496 1984 800 3200 2790 23 24 0 400 800 1200 1600 2000 2400 2800 1200 4910 5970 7030 1600 27 2400 2800 11200