A company has decided to install a new production facility for one of its products. The company is considering three options,

Option A:

The facility will have an annual capacity of 200,000 units. It is estimated to have an annual fixed cost of Rs. 1,50,00,000 and a variable cost of Rs. 275 per unit.

Option B:

The facility will have an annual capacity of 100,000 units. It is estimated to have an annual fixed cost of Rs. 90,00,000 and a variable cost of Rs. 337 per unit.

Option C:

The facility will have an annual capacity of 65,000 units. It is estimated to have an annual fixed cost of Rs. 37,50,000 and a variable cost of Rs. 412 per unit.

If the product price is reduced, sales can be expected to be more. The expected sales as a function of product price are shown below.                                                                                                   

The company has an advantage that it will be the first to enter the market with the product. However, the company has competitors and if they enter the market at any stage, the sales may go down by 20% from that year onwards. However it is not certain in which year the competitors will enter the market.

The company has to make two decisions: which manufacturing system to have and what should be the product price?

Instructions:

Use payoff or regret tables for decision making.

Prepare a report where in you should first explain the method you will use, solve using excel and finally discuss the results and your decision.

An additional deliverable will be an excel sheet where one should be able to do "what if " analysis by changing the values of sales forecast, price, costs etc.

It is necessary for the excel sheet to be user friendly.