Spread Sheet Modelling

1. El Genio produces expensive gift items for the affluent. The latest product proposal from the market research team is a limited edition grandfather clock. The management team would like to study the feasibility of the project and then decide the number of such clocks to produce. If El Genio decides to go ahead with this product, it would have to setup a production facility which will cost around Rs. 50,000. It would cost Rs. 1000 to produce each clock and the market research team has suggested that the price of each clock be Rs. 5000. The demand (no. of units of the product) for the product would follow the equation given by. [pic 1]

The management team is aware that it is not always possible to produce exactly the same number of products as determined by the demand equation. However it is not possible to sell more than what is predicted by the demand equation.  

1. Draw a graph which shows the variation of profit against the number of units produced. From the graph identify the break-even point (the number of units to be produced) for a price point of Rs. 6000.             (2)

Please note that no mark will be awarded if the graph is not present in your excel file. Write down the break-even point only in the box provided below.

1. What would be the break-even price if the company has to produce 80 units of the product? Write down the price and the corresponding demand in the box provided below.                                        (1)

1. Vary the price from 5000 to 10000 with an increment of 500. Also vary the number of units to be produced from 10 to 110 with an increment of 5. Write down the number of combinations where the profit is negative. 

                                                                                                                                                                          (2)

Please write down all the formulas that you have used in the excel file to solve this problem. This does not have any mark, however, failure to write the formulas in the box provided below will render your attempt null and void.

1. Three pharmaceutical companies Provartis, Praxo and Moon Pharma are ready with a new drug (compound) which they want to introduce in the market as soon as possible. Moon Pharma was able to develop the drug with an investment of Rs 5 crores. Provartis and Praxo invested Rs. 9 crores and 7.75 crores respectively.  These three companies have different estimates of the market size for the drug. Provartis estimates the current market size (2017) at 1.5 million. Praxo’s estimate is 0.95 million, whereas Moon Pharma believes that the market size would be around 2 million. However, all three companies have the same estimate for the growth rate of the market; it is assumed to be 8%.

Moon Pharma has plans to launch the drug in January, 2017.  Praxo would wait till the market size is at least 1 million and then launch the drug. Provartis has decided to wait till the market size is at least 2 million. It has been seen in the past that the price of a drug falls as more companies enter the market. The assumption here is that the price follows the equation, p = 450 – 5n, where n is the number of companies selling the drug and p is the price of each unit of the drug. There are reasons to believe that it will be same for this case as well.