Attached
Deepak Baid
excel_problem3.docxExcel Problem
1.
Funky
You are trying to estimate NPV of profit for new computer product. You are confident the product will sell for ten years and are given the following information
Hurdle Rate
15% and assume end of year for profits.
Fixed Cost
Total cost of developing the product will be depreciated equally over the product’s life. Total development cost will be between $2 billion and $11billion. There is a 25% chance that total fixed cost is $3 billion or less, 50% chance total fixed cost is $6 billion or less, and a 75% chance that total fixed cost is $9 billion or less. Development cost is incurred at end of Year 0.
Market Size
The total Year 1 market size (in terms of annual unit sales) is unknown but is believed to be between 0 and 600 million units. Unit sales of 100 million and 500 million are equally likely. Unit sales of 200 million and 400 million are equally likely, and are 4 times as likely as sales of 100 million. Sales of 300 million are 5 times as likely as sales of 100 million. Each year market growth is expected to average 5%, and during each Year we are 95% sure that market growth will be between 3% and 7%.
Market Share
Our most likely Year 1 market share is 30%. There is a 5% chance that our market share will be less than or equal to 10% and a 5% chance that our market share will be more than 40%. A triangular distribution appears to be reasonable for market share. In later years we expect market share, on average, to equal the previous year’s share, but there is a 95% chance that market share could change by up to 20% of its current value.
Unit Price
The Year 1 price charged for each unit follows a triangular random variable with most likely value $50, worst-case $45, and best-case $60. Each year unit price will increase 5%.
Unit variable Cost
The Year 1 unit variable cost of production follows a triangular random variable with worst-case $30, best-case $20, and most likely case $24. Each year variable costs will increase 5%.
a. You are 95% sure mean NPV of project is between ____and ____. Run 1600 iterations.
b. What is the chance that this project will meet its hurdle rate?
c. What are key drivers of the project’s profitability?
2.
People Magazine Problem
We are trying to determine how many copies of People Magazine to deliver to OSCO in Bloomington. We have the following data on past demand.
|
|
observed |
days left if |
|
week |
demand |
ran out? |
|
1 |
23 |
no |
|
2 |
27 |
no |
|
3 |
17 |
no |
|
4 |
24 |
no |
|
5 |
21 |
no |
|
6 |
18 |
no |
|
7 |
22 |
no |
|
8 |
29 |
no |
|
9 |
24 |
no |
|
10 |
17 |
2 |
|
11 |
10 |
3 |
|
12 |
10 |
4 |
|
13 |
14 |
1 |
Note that if we ran out of the magazine we give number of days left in the 7 day sale period.
