Assume your boss is aware that you are an MBA graduate from Southern State University. He confides in you that executive Management Team has decided to add a new equipment part to its item sales list. However, they are at a dilemma as to which way they should go to produce the part. She asked you to do an excel analysis to include graphs in a PowerPoint slide so that the EMT can have a clear picture of the situation so that they can select the best approach to follow from the following options.
1. The production manager has identified the following options for obtaining a finished machined part for sale:
a. It can buy the part at $200 per unit (including materials). There is negligible fixed cost if the item is purchased;
b. It can make the part on a numerically controlled semi-automatic lathe at $75 per unit (including materials, labor, and other variable costs). A semi-automatic lathe costs $80,000; or
c. It can make the part on a machining center at $25 per unit (including materials, labor, and other variable costs). A machining center costs $200,000.
Additionally, the production manager estimates the learning curve is maximized at 200 units.
2. The sales manager has estimated the total demand for the part to be between 500 and 2500 units and that it should sell for approximately $300. He expressed a 68% level of confidence that the company should sell at least 1500 units at $300 each.
The finance manager has developed a total cost formula for each option which is:
a. Purchase cost= $200 * Demand
b. Produce using Lathe= $80,000 + $75* Demand
c. Produce using Machining Center= $200,000 + $25* Demand
To be prepared for discussion about the options, she asked you to start by finding the break even point in demand for each option. Additionally, she asked you to use excel to plot the curves to help clarify the different options. Finally, she wants you to prepare a PowerPoint slide as to what recommendation you would advise senior management to follow to maximize total profit for the company in producing and selling this item.
*Basically the PowerPoint slides don't have to be performed but a graph plotting the cost curve and profit curve should be plotted. Also which choose in both scenarios would be the better solution for management?