A manufacturer is considering a couple of options for producing a new product: It can make the product with a semiautomatic machine at $100 per unit; or it can make the product on an automatic machine at $40 per unit. The fixed cost for setting up the semiauto machine is $100,000 and the fixed cost for procuring and setting up the automatic machine is $250,000. Both machines can be ready in time for the production of the new product. The product can sell at $400 per unit. Help the manufacturer analyze the decision problem.
Do the following on a spreadsheet file on a different sheet for each question.
1) Develop an algebraic model and a graphical model, and make a recommendation to the manufacturer (by comparing total revenue and total cost of each production option) as to whether to produce the product and which production option to choose under different sales scenarios. (The manufacturer has not had a forecast for the sales yet.)
2) Develop a spreadsheet model showing the total revenue, total cost of each production option, and total profit of each function, and difference in total profits between two options given the information available. Also, include a maximum sales number, estimated by the marketing department at 1,200 units. Show the results for producing 500 units.
3) Develop a spreadsheet to compute the break-even point of each production option and the point where two production options are equal in profit. Show (write out) all these breakeven points.
4) With forecast sales of 1,200 units, how large can the fixed cost for the (selected) production option be before the product stops to be profitable? Hint: Answer the question by revising the spreadsheet model in (2) or revising the algebraic models in (1). It would help first to identify parameters (constants) and variables for this question.
5) With forecast sales of 1,200 units, how small can the price for the product be before the product stops to be profitable? Same hint as the previous question.