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: Mild Winter Harsh Winter No. of Shovels Probability No. of Shovels Probability 250 0.5 1,500 0.2 F 300 0.4 2,500 0.3 350 0.1 3,000 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. 1. To construct the payoff table, calculate the probability of each demand. See the hint on the worksheet. 2. The optimal ordering quantity with the highest expected pay off is how much? Set up your newsvendor model below Cost Reg Price Discount Price Demand Order size Qty sold at reg price Qty sold at discount Revenue at reg price Revenue at discount Total costs Profit Set up your decision table and everything else below Probability Supply Demand 200 ? 400 1400 1600 2400 2600 3000 0.3 250 ? 0.24 300 ? 0.06 350 ? 0.08 1500 ? 0.12 2500 ? 1. Contruct a payoff table. Make sure rows ..., 3000) and columns outcome of random calculate the payoff using a Newsvendor m of each demand (B35:G35) as a joint proba marginal prob. (mild winter 60%) and cond 2. Set up the payoff table. Calculate the exp SUMPRODUCT(), highlight the highest expe 0.2 3000 Expected payoff 1. To construct the payoff table, calcula worksheet. 2. The optimal ordering quantity with t Probability of demand being 250 Probability of demand being 300 Probability of demand being 350 Probability of demand being 1500 Probability of demand being 2500 Probability of demand being 3000 Optimal order quantity