Excel Online Structured Activity: Break-Even Analysis Suppose that a firm has the option to make or buy a part. Its annual requirement is 15,000 units. A supplier is able to supply the part at $7 per unit. The firm estimates that it costs $400 to prepare the contract with the supplier. To make the part, the firm must invest $22,000 in equipment, and the firm estimates that it costs $5 per unit to make the part. COSTS MAKE OPTION BUY OPTION Fixed Cost $22,000 $400 Variable Cost $5 $7 Annual Requirement - 15,000 units The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. X HH Open Seadsheet Questions 1. What is the break-even point? Round your answer to the nearest whole number. 2. What is the total cost at the break-even point? Round your answer to the nearest dollar $ 3. What is the total cost for the make option? Round your answer to the nearest dollar $ 4. What is the total cost for the buy option? Round your answer to the nearest dollar 4. What is the total cost for the buy option? Round your answer to the nearest dollar. 5. What is the cost difference (make - buy)? Round your answer to the nearest dollar. Use the minus sign to enter a negative difference, If any. 6. To obtain the lowest production cost, should the firm make or buy the part? The firm should B the part. G D H 1 3 B K Variable (per unit) $5.00 7 B $7.00 Formulas 9 Difference (Fixed Costs) Difference (Variable Costs) WNIA #N/A 11 Break-even Units Break-even Cost UNIA #N/A 13 14 15 16 Total Cost (Make) Total Cost (Buy) ANA #N/A Cost Difference (Make - Buy) Optimal Decision #N/A #N/A %&&&CNEBGUNE 18 19 20 21 22 23 24 25 26 27 28 29