Refer once again to the International Expansion spreadsheet (xixs) provided in the previous module. Take note of the financial information provided in
the “Revenue by Location” and “Support Cost by Region.” Use the time value of money in your analysis to assess the pros and cons of each region. Also,
experiment with discount rates—one high, middle, and low to understand the effect of cost of capital and risk.
First:
Calculate the mean revenue per location using the Revenue_By_Location worksheet. Construct a decision tree that analyzes the possibility of achieving
the mean revenue on an ongoing basis along with a co
espondingly high and low estimate. Use the following data to construct your decision tree:
Region Average Rev. Est Low Est High Est Prob. High Prob. Average Prob. Low
UK1 Mean + 5% Mean - 5% Mean + 15% XXXXXXXXXX
UK2 Mean + 7% Mean - 7% Mean + 20% XXXXXXXXXX
EUL Mean + 5% Mean - 10% Mean + 18% XXXXXXXXXX
EU2 Mean + 5% Mean - 10% Mean + 25% XXXXXXXXXX
EU3 Mean + 8% Mean - 15% Mean + 15% XXXXXXXXXX
Next:
Construct your decision tree in Excel and calculate the expected value (refer to the readings and resources in this module to identify how to perform these
tasks in Excel).
Market_Location Rating
Criteria 1: Market similarity Criteria 2: Strength of competitors Criteria 3: Client application usage Criteria 4: Management risk
UK1 UK2 EU1 EU2 EU3 UK1 UK2 EU1 EU2 EU3 UK1 UK2 EU1 EU2 EU3 UK1 UK2 EU1 EU2 EU3
5 2 4 1 3 5 3 3 2 2 2 2 3 1 1 3 2 2 2 3
4 1 4 2 3 5 4 2 2 3 5 4 1 1 5 3 3 4 2 1
1 1 3 2 3 4 1 2 1 2 4 2 2 2 4 3 4 2 1 3
2 4 3 3 2 4 4 3 1 3 2 4 1 3 2 2 3 2 2 2
5 2 4 2 1 5 4 2 2 1 4 4 2 3 2 4 3 3 2 2
1 4 4 1 3 2 4 3 2 2 3 2 3 3 5 1 3 3 1 1
2 1 3 3 1 4 4 4 1 2 5 2 3 2 2 4 4 3 1 3
1 3 4 3 1 5 4 2 1 2 5 4 3 2 2 5 2 2 2 2
2 1 4 2 2 3 2 4 2 1 2 2 1 3 5 2 2 3 2 3
2 3 3 2 1 4 4 4 2 3 3 1 3 2 1 1 3 4 1 1
1 4 4 1 3 2 1 2 2 3 4 4 1 3 3 2 2 4 1 1
4 3 3 3 1 1 4 2 1 1 1 2 2 2 5 2 4 3 1 1
1 2 2 2 1 2 2 3 1 3 3 2 1 3 1 1 4 3 1 2
3 4 4 2 1 2 1 2 2 2 1 4 3 2 1 2 2 3 2 3
3 1 2 2 3 1 3 3 1 3 1 4 3 3 2 5 2 3 2 1
2 2 3 2 1 3 4 3 2 1 1 4 1 2 2 2 2 2 2 2
2 4 4 2 3 5 3 4 2 2 2 3 1 3 5 5 3 2 1 2
1 2 3 3 1 1 3 3 1 1 2 1 3 3 1 2 1 3 1 2
2 2 4 2 1 4 2 3 2 2 2 3 1 2 5 5 4 3 2 2
2 4 4 2 1 4 1 3 2 1 5 4 3 2 2 5 2 3 1 2
5 3 2 2 2 3 3 3 2 2 4 2 2 1 1 5 3 4 1 3
3 2 3 2 3 4 3 3 1 1 4 3 3 3 3 4 4 2 1 2
2 2 2 2 3 5 2 3 1 3 1 1 1 1 4 2 3 2 1 1
5 4 2 1 3 3 2 4 2 1 5 1 1 1 2 1 1 4 1 2
3 1 2 2 1 4 4 2 1 3 4 1 2 2 2 3 1 3 1 2
5 1 4 3 2 3 4 3 1 2 2 4 1 1 5 2 4 4 2 3
1 3 2 2 3 2 4 3 1 3 2 3 1 2 2 5 3 4 2 1
2 4 2 1 1 2 1 3 1 1 2 2 3 2 4 1 4 4 2 2
4 2 2 1 2 4 2 2 2 2 2 1 3 1 4 2 3 4 2 3
5 1 4 3 1 5 4 2 2 1 1 1 2 3 5 5 3 4 2 1
2 3 4 3 1 5 3 4 1 3 2 4 1 1 3 5 3 4 1 3
3 1 4 1 2 3 1 3 1 2 3 4 1 3 5 4 1 3 2 3
4 1 3 3 3 4 4 4 1 1 2 4 1 2 3 2 4 3 1 1
3 4 3 2 2 4 2 2 2 2 3 1 1 3 5 4 4 2 2 1
2 1 2 3 2 4 3 3 1 3 1 1 1 3 2 5 4 2 1 2
4 1 2 3 1 5 2 4 2 1 4 1 3 2 3 4 1 2 1 3
1 3 2 2 3 2 2 4 2 2 3 1 2 3 4 1 2 4 1 1
5 3 3 2 3 5 4 4 2 2 1 1 3 2 5 3 1 4 1 1
5 1 2 1 3 5 3 2 1 1 2 1 1 1 2 3 1 3 2 1
5 4 3 3 1 5 1 3 1 3 4 4 3 3 4 5 4 3 2 1
5 1 3 2 3 4 2 4 2 3 5 3 1 3 4 1 1 3 1 2
3 2 4 1 3 3 1 4 1 1 3 2 1 3 1 4 2 3 1 1
3 2 3 2 2 3 2 3 1 3 3 1 2 2 1 4 2 2 2 2
3 4 2 2 2 5 4 4 2 2 1 4 1 2 4 2 3 4 1 3
1 2 3 3 3 4 4 4 2 3 1 2 2 3 2 4 4 4 2 2
3 4 2 2 1 5 1 3 2 3 2 3 1 3 4 5 3 4 1 1
5 1 3 3 2 4 1 3 1 3 3 2 3 3 5 2 4 3 1 3
2 3 3 1 1 4 3 2 1 1 1 2 2 2 5 2 4 3 1 2
2 4 3 3 1 1 1 2 2 2 2 4 1 2 4 1 1 2 2 3
1 3 2 3 2 1 4 2 2 3 2 2 3 3 3 4 2 3 1 1
3 4 2 2 3 4 3 3 1 2 4 3 2 1 1 4 4 3 1 3
2 2 2 3 2 2 4 3 2 1 2 3 1 2 2 4 2 3 1 2
5 2 2 1 3 3 2 4 2 1 5 1 1 1 3 2 1 3 1 3
4 4 4 2 1 5 4 4 2 2 3 2 2 2 3 4 3 2 1 2
3 3 2 2 1 5 4 2 1 2 4 3 2 3 1 5 1 4 2 1
1 3 3 2 1 5 2 4 2 3 1 2 3 3 4 4 4 2 1 1
1 1 4 1 1 2 1 3 2 2 5 3 1 1 2 3 3 3 2 2
4 3 2 2 3 2 2 4 2 3 3 4 2 3 5 2 2 4 2 1
3 1 4 1 3 1 1 3 2 1 1 2 2 1 1 5 1 4 1 1
3 3 2 2 2 2 1 4 1 3 2 3 2 3 5 3 2 3 1 1
4 4 3 3 2 5 1 4 1 1 3 4 1 1 1 3 1 3 2 2
2 2 2 3 3 3 2 4 1 2 1 1 2 2 3 2 1 3 1 3
1 2 4 2 2 3 3 4 1 3 3 1 2 1 5 4 2 2 1 1
5 4 4 1 2 4 3 3 1 3 5 1 3 2 3 3 1 2 1 3
3 2 2 3 2 1 1 3 2 1 1 4 1 1 3 5 3 2 1 1
1 3 2 2 1 2 3 2 1 3 5 1 2 2 4 5 1 2 2 1
1 3 2 2 2 2 1 3 2 2 2 2 2 1 3 1 4 4 2 3
2 4 3 2 3 4 1 4 1 2 2 1 3 3 4 4 1 4 1 1
1 2 3 3 1 1 2 2 2 2 1 4 3 1 5 2 2 4 2 2
2 4 2 1 3 5 4 4 2 1 3 1 2 2 1 3 4 3 2 1
2 1 2 2 1 1 2 3 1 2 2 1 2 1 5 3 2 2 1 1
4 4 4 1 1 5 1 2 1 2 1 2 1 2 4 3 2 4 1 1
3 4 4 2 2 2 2 3 1 2 3 2 1 2 3 5 4 2 1 3
2 4 2 3 3 1 1 4 2 3 1 2 2 3 1 3 1 4 2 1
3 3 4 2 3 4 2 4 2 3 2 4 3 2 3 2 4 2 2 2
3 3 4 1 3 3 1 2 1 2 5 1 2 2 1 1 1 4 1 2
1 2 3 3 2 1 1 4 1 3 2 3 3 1 2 3 3 2 1 1
4 4 2 1 3 1 1 2 1 3 2 4 3 2 1 2 4 2 1 3
2 1 2 3 2 2 2 3 1 2 3 4 3 3 3 1 3 2 1 2
5 1 2 2 1 1 3 3 1 1 2 3 3 1 3 3 4 3 2 3
1 3 4 2 1 3 1 2 2 2 1 2 2 3 5 3 1 3 1 1
4 2 4 3 1 3 1 2 2 1 5 1 2 2 2 1 1 2 1 3
4 1 2 3 3 3 3 2 1 2 4 3 1 3 1 4 4 3 2 2
1 3 4 1 1 3 3 2 2 2 2 3 2 3 3 1 2 4 2 2
1 2 2 3 2 3 4 3 1 2 1 3 2 3 3 4 1 3 1 3
1 4 2 1 1 5 3 2 1 3 5 4 1 1 5 4 3 4 1 2
4 1 3 3 1 4 3 2 2 3 5 2 2 1 5 3 2 4 2 1
5 4 3 1 2 2 4 2 1 3 3 3 1 1 1 1 3 2 1 2
3 3 4 2 1 3 3 4 2 1 3 4 2 2 2 5 2 4 1 2
2 3 3 3 2 4 3 2 1 2 1 2 2 1 1 3 4 2 1 1
2 2 3 1 3 5 3 2 1 3 5 3 3 1 4 5 2 2 1 2
4 4 2 3 2 2 1 3 2 1 5 4 1 3 4 1 2 2 1 2
1 2 3 1 1 5 4 3 1 3 3 3 2 1 3 2 4 2 1 3
5 4 2 3 2 5 2 2 2 3 5 2 1 3 2 3 4 2 2 2
4 2 2 3 3 3 2 3 2 1 2 1 2 1 5 3 1 2 1 2
3 2 3 2 3 5 2 3 1 3 3 4 2 1 2 4 1 4 2 2
2 2 3 2 1 3 3 4 1 2 3 4 1 3 2 1 3 3 1 3
3 2 3 2 2 3 1 3 2 2 4 4 2 2 2 1 1 3 2 2
5 3 3 2 1 4 2 4 2 3 3 3 3 2 2 3 4 2 2 1
5 1 4 3 3 1 2 3 1 1 4 3 3 2 3 3 1 3 1 3
Revenue_by_Location
2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 Mean
UK1 $514,013.00 $1,052,061.00 $1,128,715.00 $236,995.00 $676,730.00 $1,320,879.00 $1,367,333.00 $1,372,658.00 $1,441,747.00 $96,844.00 $920,797.50
UK2 $328,444.00 $1,197,551.00 $1,684,890.00 $233,468.00 $1,715,163.00 $699,270.00 $2,164,345.00 $891,890.00 $1,855,838.00 $2,781,529.00 $1,355,238.80
EU1 $66,645.00 $525,805.00 $874,360.00 $488,497.00 $296,199.00 $663,171.00 $674,719.00 $1,622,731.00 $948,834.00 $184,636.00 $634,559.70
EU2 $905,415.00 $914,369.00 $1,540,608.00 $1,924,680.00 $457,786.00 $614,634.00 $937,165.00 $26,658.00 $1,981,360.00 $853,690.00 $1,015,636.50
EU3 $99,385.00 $562,896.00 $637,761.00 $334,826.00 $1,429,632.00 $543,802.00 $408,524.00 $1,419,409.00 $31,808.00 $18,971.00 $548,701.40
$15,000,000.00 $16,100,000.00 $16,500,000.00 $16,850,000.00 $17,100,000.00 $17,250,000.00 $17,750,000.00 $18,201,000.00 $18,500,000.00 $18,750,000.00 $17,200,100.00
Licenses by region (est)
License needs by region
2010 2011 2012 2013 2014 2015 2016 2017 2018 2019
UK1 68 40 56 104 93 18 112 149 155 28
UK2 18 38 13 6 17 27 70 30 48 14
EU1 45 56 28 94 50 51 60 91 112 104
EU2 110 38 111 110 204 148 124 217 229 70
EU3 3 36 91 95 62 147 165 37 176 180
478 510 600 725 740 775 805 815 837 875
License icense categories needs by region (%)
Scheduling 4 4 1 6 6 1 6 0 6 4
Crew Planning 0 0 0 0 0 0 0 1 0 1
Route Optimization 1 0 1 1 1 1 1 0 1 1
Maintenance Plan 0 0 1 1 0 0 0 0 0 1
Supply Chain 1 0 2 1 2 2 3 2 0 1
Fuel Optimization 0 0 1 0 1 1 1 0 0 0
Support_Cost_Region
2010 2011 2012 2013 2014 2015 2016 2017 2018 2019
UK1 $362,786.00 $57,015.00 $41,987.00 $125,917.00 $126,226.00 $183,869.00 $282,536.00 $353,102.00 $295,716.00 $30,342.00
UK2 $282,508.00 $182,999.00 $26,477.00 $25,197.00 $274,670.00 $260,794.00 $1,163.00 $252,034.00 $78,197.00 $122,336.00
EU1 $303,621.00 $7,385.00 $288,312.00 $383,136.00 $379,861.00 $440,710.00 $84,412.00 $296,195.00 $172,475.00 $489,769.00
EU2 $282,210.00 $243,968.00 $301,860.00 $107,711.00 $390,813.00 $208,932.00 $22,212.00 $167,983.00 $89,170.00 $367,709.00
EU3 $130,099.00 $254,733.00 $371,601.00 $174,406.00 $128,204.00 $114,964.00 $47,433.00 $393,276.00 $373,631.00 $350,584.00
$3,750,000.00 $3,900,000.00 $4,056,000.00 $4,218,240.00 $4,386,969.60 $4,562,448.38 $4,744,946.32 $4,934,744.17 $5,132,133.94 $5,337,419.30
Regression
Support requests Minutes per requests
35 31
79 99
39 70
73 25
17 61
97 70
1 71
95 76
42 15
42 56
84 54
70 95
44 35
96 40
53 55
20 29
86 84
5 14
69 37
91 30
71 56
16 86
36 77
93 22
70 82
97 78
49 24
96 8
11 33
90 59
78 90
98 67
97 63
22 53
54 63
16 6
42 1
59 36
63 6
66 1
4 97
46 96
72 7
88 71
13 82
74 2
31 28
51 34
73 31
25 74
39 54
18 83
58 68
94 34
78 97
80 91
88 90
77 50
75 91
47 23
9 82
64 73
99 2
78 50
11 98
76 30
92 48
21 72
60 47
85 31
27 81
28 74
38 71
3 55
81 13
33 79
55 77
45 20
40 10
38 90
39 26
89 49
36 1
76 69
29 26
88 9
2 76
37 59
42 26
29 10
96 42
44 41
93 11
40 90
82 84
100 51
8 87
99 46
78 92
73 53
90 47
Is there any relationship between the number of requests and the time spent in response? What if anything does does this suggest about planning for support of international offices?
Decision Tree
Demand Forecast Mean Sales
($M USD) Prob Expected (Sales)
UK1 High $0.00
Average $920,797.50 $0.00
Low $0.00
UK2 High $0.00
Average $1,355,238.80 $0.00
Low $0.00
EU1 High $0.00
Average $634,559.70 $0.00
Low $0.00
EU2 High $0.00
Average $1,015,636.50 $0.00
Low $0.00
EU3 High $0.00
Average $548,701.40 $0.00
Low $0.00