The Shoemaker Family makes shoes. They want to produce a unique leather shoe for women; however, they want to evaluate whether they can make a profit. They want to find out how many pairs of shoes they must make to break even. The company has determined that it would cost $2,000 to set up production to manufacture the shoes. Accounting has provided you the raw data which has determined each pair would cost approximately $60 marginal cost to make from similar models. Marketing Research has determined that each pair would sell for $80 based upon similar models. From the raw data create a pivot table to determine exact average marginal cost and average selling price. A) Place the data in the appropriate data cells. B) Place the appropriate formulas given in column H to the appropriate place in column F. C) Using Excel, how large must the order where a break-even point is obtained? Use the data in the word problem to solve for the correct Production Quantity. D) Make a Chart on this worksheet. Using the Production Qty, the Revenue and Cost... create a scatter chart with lines to show the breakeven crossover point. Label your chart "Breakeven". Highlight the Production Qty cell in column H where the crossover occurs.

114 X V fx A B C D E F G H J K L M N Break-Even Analysis N Data Results Unit Revenue Total Revenue Total Revenue=UnitRevenue*Production Quantity Fixed Cost Total Fixed Cost Total Fixed Cost = IF(ProductionQuantity>0, FixedCost,0) Marginal Cost Total Variable Cost Total Variable Cost = MarginalCost*Production Quantity Profit (Loss) Profit (loss) = TotalRevenue-(TotalFixedCost+TotalVariable Cost) Production Quantity Break-Even Point Break-Even Point = FixedCost/(UnitRevenue-MarginalCost) 10 11 12 Range Name Cell Step D. Graph Points 13 BreakEvenPoint F9 Production Qty Revenue Cost 14 FixedCost C5 25 15 MarginalCost C6 50 16 Production Quantity C9 75 17 Profit F7 100 18 SalesForecast C7 125 19 TotalFixedCost F5 150 20 TotalRevenue F4 175 21 TotalVariable Cost F6 200 22 UnitRevenue C4 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38