MS Excel: Using the Break-Even Decision Model to Determine the Optimal Production Method
DescriptionThe video explains how to design a break-even decision model in Microsoft Excel to determine whether it is more cost-effective to manufacture a product or outsource it. The model is created by comparing the total costs associated with both manufacturing and outsourcing the product. The model takes into account the number of units, fixed costs, and variable costs. The fixed cost is the same for both manufacturing and outsourcing, and is set at 5000. The variable cost for manufacturing is 1.5 per unit, and for outsourcing it is 2.5 per unit. The total cost for each scenario is calculated by adding the fixed cost and variable cost.
A line chart is created to represent the total costs for each scenario. The break-even point is the point at which the lines of the two scenarios intersect, indicating that the total cost for both manufacturing and outsourcing is equal. The model assumes that the fixed cost is constant, but this may not always be the case. If the fixed cost increases after a certain number of units are produced, the break-even point may shift. In this example, the fixed cost increases to 7000 after 4000 units are produced, and the revised break-even point is 17,500.
The model concludes that if the number of units produced is less than 7000, it is more cost-effective to outsource the product. But if more than 7000 units are to be sold, it is better to manufacture the product.
#break-evenanalysis #manufacturing #outsourcing #excel #productioncost #decisionmaking #businesstools #financialanalysis #costeffectiveness #productivitytips #youtubevideo #tutorial