Solve the question using Excel.

Chapter 11 15. 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. The following distribution of demand for snow shovels has been tabulated: No of Shovels Probability 250 0.15 300 0.12 350 0.03 1500 0.14 2500 0.28 0.28 3000 Shovels must be ordered from the manufacturer in lots of 200, which consist of 200, 400, 1400, 1600, 2400, 2600, and 3000 a. Assume order size is 400 and demand is 300. Compute total cost, total revenue, and profit. Total cost = Order size x unit cost; Total revenue = Revenue from regular price plus revenue from sale price; Profit = Total revenue- Total cost. The general definition of| revenue is number of units sold times corresponding selling price. Note that the quantity that can be sold at the regular price is the minimum between order size and demand. The quantity exceeding demand, if any, will be sold at the sale price. So, your model needs to be dynamic to accommodate all three plausible scenarios 1) order size is greater than demand, 2) order size is equal to demand, 3) order size is less than demand. b. Use Data Table from What-If Analysis, to compute profit at different combinations of demand and order size. Use 200, 400, 1400, 1600, 2400, 2600, and 3000 for order size and 250, 300, 350, 1500, 2500, and 3000 for demand. c. With results from calculation in part (b), compute expected profit at each level of order size and determine the optimal order size. Optimal order size is the order size that maximizes the profit