..The Shoemaker Family makes shoes. They want to produce a unique leather shoe for women; however,...

 

Transcribed Image Text:

..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 from the data above. 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? (cell (9) Use the data in the word problem to solve for the correct Production Quantity. D) Create 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 "Break Even". Highlight the Production Qty cell in column H where the crossover occurs. Break-Even Analysis Unit Revenue Fixed Cost Marginal Cost Production Quantity Data $80.00 $2,000 $60.00 Total Revenue Total Fixed Cost Total Variable Cost Profit (Loss) Break-Even Point Range Name BreakEvenPoint FixedCost MarginalCost ProductionQuantity Profit SalesForecast TotalFixedCost Total Revenue TotalVariableCost Unit Revenue Cell F9 C5 238845223 C6 C9 F7 C7 F5 F4 F6 Results C4 100 Total Revenue-UnitRevenue Production Quantity Total Fixed Cost IF(ProductionQuantity>0, FixedCost,0) Total Variable Cost MarginalCost ProductionQuantity Profit (loss) = Total Revenue-(TotalFixedCost+TotalVariableCost) Break-Even Point = FixedCost/(UnitRevenue-MarginalCost) Step D. Graph Points Production Qty 25 50 75 100 125 150 175 200 Revenue Cost ..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 from the data above. 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? (cell (9) Use the data in the word problem to solve for the correct Production Quantity. D) Create 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 "Break Even". Highlight the Production Qty cell in column H where the crossover occurs. Break-Even Analysis Unit Revenue Fixed Cost Marginal Cost Production Quantity Data $80.00 $2,000 $60.00 Total Revenue Total Fixed Cost Total Variable Cost Profit (Loss) Break-Even Point Range Name BreakEvenPoint FixedCost MarginalCost ProductionQuantity Profit SalesForecast TotalFixedCost Total Revenue TotalVariableCost Unit Revenue Cell F9 C5 238845223 C6 C9 F7 C7 F5 F4 F6 Results C4 100 Total Revenue-UnitRevenue Production Quantity Total Fixed Cost IF(ProductionQuantity>0, FixedCost,0) Total Variable Cost MarginalCost ProductionQuantity Profit (loss) = Total Revenue-(TotalFixedCost+TotalVariableCost) Break-Even Point = FixedCost/(UnitRevenue-MarginalCost) Step D. Graph Points Production Qty 25 50 75 100 125 150 175 200 Revenue Cost