PLEASE SOLVE USING EXCEL. THANK YOU!
You have been hired to estimate the value of the startup company Garcia Ltd. Garcia has one product, which is expected to sell in the first year for $100. The price will grow in subsequent years by an amount given by a normal distribution with a mean of 5% and a standard deviation of 3%. Initial sales will be 5,000 units, expected to grow at 5%. The unit cost initially will be $75, and this will grow at a rate given by the normal distribution with a mean of 10% and a standard deviation of 3%. The valuation will be based on a time horizon of ten years. The discount rate is 10%. Garcia wants to investigate an option to decide in year 6 whether to continue in business or go out of business. (This is what is known as a real option, as opposed to the financial options discussed in the text). Specifically, management intends to execute the option to go out of business at the end of year 6 if the year 6 contribution is less than some cutoff value. If they opt to go out of business, they will receive zero contribution in years 7-10. a) Estimate the expected NPV of the business without the option to go out of business. b) What is the optimal cutoff value? That is, what cutoff value should Garcia use in year 6 to maximize the expected NPV of the company from the start? c) The value of this option is the difference between the expected NPV with the option from part b) and the expected NPV without the option from part a). What is the value of this option? You have been hired to estimate the value of the startup company Garcia Ltd. Garcia has one product, which is expected to sell in the first year for $100. The price will grow in subsequent years by an amount given by a normal distribution with a mean of 5% and a standard deviation of 3%. Initial sales will be 5,000 units, expected to grow at 5%. The unit cost initially will be $75, and this will grow at a rate given by the normal distribution with a mean of 10% and a standard deviation of 3%. The valuation will be based on a time horizon of ten years. The discount rate is 10%. Garcia wants to investigate an option to decide in year 6 whether to continue in business or go out of business. (This is what is known as a real option, as opposed to the financial options discussed in the text). Specifically, management intends to execute the option to go out of business at the end of year 6 if the year 6 contribution is less than some cutoff value. If they opt to go out of business, they will receive zero contribution in years 7-10. a) Estimate the expected NPV of the business without the option to go out of business. b) What is the optimal cutoff value? That is, what cutoff value should Garcia use in year 6 to maximize the expected NPV of the company from the start? c) The value of this option is the difference between the expected NPV with the option from part b) and the expected NPV without the option from part a). What is the value of this option
