3 4 5 6 A Mild Winter B Probability 250 300 350 No. of shovels 7 8 9 10 11 Cost 12 Price $ 13 Discount price $ 14 15 16 Set up your newsvendor model below 17 Cost 18 Reg Price 19 Discount Price 20 21 Demand 22 Order size 23 24 Qty sold at reg price 25 Qty sold at discount 26 27 Revenue at reg price 28 Revenue at discount 29 Total costs 30 31 Profit 32 33 34 Set up your decision table and everything else below 35 Probability ? 36 37 38 Supply 200 ? 39 40 41 42 43 44 GA GA GA $ ? ? ? ? ? ? 0.5 0.4 0.1 C D E F G H J K L O Harsh Winter No. of shovels Probability Midwestern Hardware must decide how many snow shovels to order for the coming snow season. Each shovel costs $15.00 and is sold for $29.95. No inventory is carried from one snow season to the next. Shovels unsold after February are sold at a discount price of $10.00. Past data indicate that sales are highly dependent on the severity of the winter season. Past seasons have been classified as mild or harsh, and the following distribution of regular price demand has been tabulated: 1500 2500 3000 0.2 0.3 0.5 Shovels must be ordered from the manufacturer in lots of 200; thus, possible order sizes are 200, 400, 1,400, 1,600, 2,400, 2,600, and 3,000 units. Construct a payoff table to illustrate the components of the decision model, and find the optimal quantity for Midwestern to order if the forecast calls for a 40% chance of a harsh winter.Show your work on worksheet Hardware. Hint: 0. You must clearly mark every row, column, and cell in your work. www. 1. Contruct a payoff table. Make sure rows represent alternatives (order quantity, 200, 400, 1400, 3000) and columns outcome of random event (demand 250, 300, ..., 3000). It would be easier to calculate the payoff using a Newsvendor model and a two-way data table. Calculate the probability of each demand (B35:G35) as a joint probability (mild winther and demand is 250) as product of marginal prob. (mild winter 60%) and conditional prob. (demand 250 given weather is mild 50%). 2. Set up the payoff table. Calculate the expected pay off for each order quantity using SUMPRODUCT(), highlight the highest expected pay off. ? ? Expected payoff 15.00 29.95 10.00 Demand 250 ? ? ? ? ? ? ? ? M N