A Company decided to start a new drug . The total R&D are $750000000 with clinical trials mounting to $120000,000. The current market size is 2000,000 and expected to grow 3% every year . the company hope the first year is 8% and grow by 20% each year for next 4 years . a monthly prescription is to make revenue of $350 while incurring variable costs of $150. Discount rate of 9% is assumed . Please solve this in excel .

What is net present value over the first four years ?

By which year the company can expect to realize a positive cumulative profit ? what is the cumulative profit of this year ?

Conduct appropriate what -if analyses to investigate how the cumulative profit for the next four years and the net present value change if unit price varices between $250 and $450 with increment of $20 ( the table below is to show the format of the solution , using EXCEL )

Unit Price	Year 1	Year 2	Year 3	Year 4	NPV
250
270
290
310
330
350
370
390
410
430
450

Conduct appropriate what -if analyses to investigate the joint effect of unit cost and market share growth rate on net present value .( for consistency , let unit cost vary between $100 and $200 with increment of $10, and let market share growth rate vary between 10% and 30%, and increment of 2% . ( answers in excel )

Market share growth rate
Unit cost		0.1	0.12	0.14	0.16	0.18	0.2	0.22	0.24	0.26	0.28	0.3
	100
	110
	120
	130
	140
	150
	160
	170
	180
	190
	200

5 . use a tornado chart to identify the most important 3 variables that affect the NPV .