How much to complete by tonight
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 correspondingly 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% 0.1 0.4 0.5 UK2 Mean + 7% Mean - 7% Mean + 20% 0.15 05 0.35 EUL Mean + 5% Mean - 10% Mean + 18% 0.05 06 0.35 EU2 Mean + 5% Mean - 10% Mean + 25% 0.2 045 0.35 EU3 Mean + 8% Mean - 15% Mean + 15% 0.1 0.4 0.5 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 similarityCriteria 2: Strength of competitorsCriteria 3: Client application usageCriteria 4: Management risk UK1UK2EU1EU2EU3UK1UK2EU1EU2EU3UK1UK2EU1EU2EU3UK1UK2EU1EU2EU3 52413533222231132223 41423542235411533421 11323412124222434213 24332443132413223222 52421542214423243322 14413243223233513311 21331444125232244313 13431542125432252222 21422324212213522323 23321444233132113411 14413212234413322411 43331142111222524311 12221223133213114312 34421212221432122323 31223133131433252321 22321343211412222222 24423534222313553212 12331133112133121312 22421423222312554322 24421413215432252312 53222333224221153413 32323433114333344212 22223523131111423211 54213324215111211412 31221442134122231312 51432343122411524423 13223243132312253421 24211213112232414422 42212422222131423423 51431542211123553421 23431534132411353413 31412313123413541323 41333444112412324311 34322422223113544221 21232433131113254212 41231524214132341213 13223224223123412411 53323544221132531411 51213532112111231321 54331513134433454321 51323424235313411312 32413314113213142311 32322323133122142222 34222544221412423413 12333444231223244422 34221513232313453411 51332413133233524313 23311432111222524312 24331112222412411223 13232142232233342311 34223433124321144313 22232243212312242312 52213324215111321313 44421544223222343212 33221542124323151421 13321524231233444211 11411213225311233322 43223224233423522421 31413113211221151411 33222214132323532311 44332514113411131322 22233324121122321313 12422334133121542211 54412433135132331213 32232113211411353211 13221232135122451221 13222213222221314423 24323414122133441411 12331122221431522422 24213544213122134321 21221123122121532211 44411512121212432411 34422223123212354213 24233114231223131421 33423424232432324222 33413312125122111412 12332114132331233211 44213112132432124213 21232223123433313212 51221133112331334323 13421312221223531311 42431312215122211213 41233332124313144322 13411332222323312422 12232343121323341313 14211532135411543412 41331432235221532421 54312242133311113212 33421334213422252412 23332432121221134211 22313532135331452212 44232213215413412212 12311543133321324213 54232522235213234222 42233323212121531212 32323523133421241422 22321334123413213313 32322313224422211322 53321424233332234221 51433123114332331313 Revenue_by_Location 2010201120122013201420152016201720182019Mean 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 2010201120122013201420152016201720182019 UK1684056104931811214915528 UK21838136172770304814 EU14556289450516091112104 EU21103811111020414812421722970 EU333691956214716537176180 478510600725740775805815837875 License icense categories needs by region (%) Scheduling4416616064 Crew Planning0000000101 Route Optimization1011111011 Maintenance Plan0011000001 Supply Chain1021223201 Fuel Optimization0010111000 Support_Cost_Region 2010201120122013201420152016201720182019 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 requestsMinutes per requests 3531 7999 3970 7325 1761 9770 171 9576 4215 4256 8454 7095 4435 9640 5355 2029 8684 514 6937 9130 7156 1686 3677 9322 7082 9778 4924 968 1133 9059 7890 9867 9763 2253 5463 166 421 5936 636 661 497 4696 727 8871 1382 742 3128 5134 7331 2574 3954 1883 5868 9434 7897 8091 8890 7750 7591 4723 982 6473 992 7850 1198 7630 9248 2172 6047 8531 2781 2874 3871 355 8113 3379 5577 4520 4010 3890 3926 8949 361 7669 2926 889 276 3759 4226 2910 9642 4441 9311 4090 8284 10051 887 9946 7892 7353 9047 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 ForecastMean Sales ($M USD)ProbExpected (Sales) UK1High $0.00 Average$920,797.50$0.00 Low$0.00 UK2High $0.00 Average$1,355,238.80$0.00 Low$0.00 EU1High $0.00 Average$634,559.70$0.00 Low$0.00 EU2High $0.00 Average$1,015,636.50$0.00 Low$0.00 EU3High $0.00 Average$548,701.40$0.00 Low$0.00