E F G H L set up your decision table and everything else below Prob. 0.05 0.2 0.3 0.1 Demand 150 175 200 225 250 Expected Payoff Supply A50 #NAME? 180 200 220 240 Payoff under perfect info Expected payoff under perfect info Expected value of perfect info Expected demand units Set up the following two-way data table to calculate the expected payoff if ordering the expected demand qty 150 175 200 225 250 Order qty 0 Expected payoff if ordering expected demand qty Question 4 4 pts Hint: 0. You must clearly mark every row, column, and cell in your work. Mountain Ski Sports, a chain of ski-equipment shops in Colorado, purchases skis from a manufacturer each summer for the coming winter season. The most popular intermediate model costs $150 and sells for $275. Any skis left over at the end of the winter are sold at the store's spring sale (for $100). Sales over the years are quite stable. Gathering data from all its stores, Mountain Ski Sports developed the following probability distribution for demand: 1. Contruct a payoff table. Make sure rows represent alternatives (order quantity, 160, 180,..., 240) and columns outcome of random event (demand 150, 175, ..., 250). It would be easier to calculate the payoff using a Newsvendor model and a two-way data table (FS:K10). Calculate the expected payoff of each purchase quantity (better using SUMPRODUCT() and placing the result at the end of each row L6:L10) and highlight the best one. Demand Probability 150 0.05 175 0.20 2. Calculate the expected payoff under perfect information by: find the best payoff under each demand (better place them at the bottom of each column G12:K12), multiply with corresponding probability and add up (SUMPRODUCT() again in G13). The difference between the expected payoff under perfect information and the best expected payoff from step 1 is the expected value of perfect information. Highlight it in G14. 200 0.35 225 0.30 250 0.10 The manufacturer will take orders only for multiples of 20, so Mountain Ski is considering the following order sizes: 160, 180, 200, 220, and 240. 3. Calculate the expected demand (each demand times corresponding prob. and then add up in G16). What would be the payoff of ordering this quantity under each demand? use another two- way data table to calculate in F18:K19. Calculate the expected payoff in G20 Highlight both expected demand and payoff. Will you do better than ordering the quantity from step 1? A B 1 Mountain Ski Sports 2 Set up the newsvendor model below 3 Cost $ 150.00 4 Reg Price 5 Discount Price 6 7 Demand 8 Order size 9 10 Qty sold at reg price 11 Qty sold at discount 12 13 Revenue at reg price 14 Revenue at discount 15 Total costs 16 17 Profit 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 C D 0.35 J K M