USA: +1(669)289-8683 
Sign in
Excel 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
1
5
% 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 $
11
billion. 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
7
5% 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 unk
no
wn but is believed to be between 0 and 600 million units. Unit sales of
10
0 million and 500 million are equally likely. Unit sales of 200 million and
4
00 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 |
||||||||||
| 3 |
17 |
||||||||||
| 4 | 24 | ||||||||||
| 5 |
21 |
||||||||||
| 6 |
1 8 |
||||||||||
| 7 |
22 |
||||||||||
| 8 |
29 |
||||||||||
| 9 | |||||||||||
| 10 | |||||||||||
| 11 | |||||||||||
|
12 |
|||||||||||
|
13 |
14 |
Note that if we ran out of the magazine we give number of days left in the 7 day sale period.
OSCO pays us $1 for each issue sold and the variable cost is $0.25 per copy. All unsold issues are returned to us (and are a total loss) after seven days. Assuming demand for People follows a normal random variable how many copies should we deliver to OSCO?
>
Excel Problem
Kelley is trying to schedule 1
6
MBA courses in
8
time slots.. The file Spring13exam3 contains relevant information for the 8 professors teaching these classes.
For example, Professor 1 teaches Courses 1 and 2 and does not want to teach anything in time slot 2 or time slot
4
.
` You are also given data on the 4 courses each student wants to take.
For example, student 1 wants to take courses
10
,
16
,
12
and
11
.
You want to schedule the courses to meet two goals (each of equal importance)
· Minimize the number of courses taught at times the professor does not want.
· Minimize the number of students who have a conflict (due to two or more courses at the same time). For example, if Courses 10, 16, and 12 are given in time slot 4 then student 1 has a conflict.
Use Evolutionary Solver to determine an optimal schedule.
4
5
6
7
8
9
10
11
12
PQRST
ProfessorCourse 1Course 2Bad time 1
Bad Time
2
11
22
4
23
413
35662
47845
591032
6111
25
6
71314
24
8
15
1616
15
16
17
18
19
20
21
22
23
24
25
26
27
NOPQR
StudentCourse 1Course 2Course 3Course 4
110161211
2212511
364915
4101234
511849
61311112
778310
8911016
996712
101141314
111612615
1227410
