Use this Download this Excel Template to work either individually or in your group on the two following problems. Submit ONE EXCEL file to be graded. If you work in a group, make sure to clearly include the name of all group members (you may leave a comment in your submission). (Q1) In August of the current year, a car dealer is trying to determine how many cars of the next model year to order. Each car ordered in August costs $20,000. The demand for the dealer's next year models has the probability distribution shown in the first tab of the Excel Template. Each car sells for $25,000. If demand for next year's cars exceeds the number of cars ordered in August, the dealer must reorder at a cost of $22,000 per car. Excess cars can be disposed of at $17,000 per car. Considering.potential delays in restocking and the effects on planning and customer relationships, the car dealer excludes the sales of cars that are reordered from the sales value calculation. (a) Use simulation to determine how many cars to order in August, if the goal is to maximize mean profit. Narrow your search from 20 to 40 at increments of 5 cars, i.e., choose from 20,25,30,35, and 40.(b) For your optimal order quantity, find a 95% confidence interval for the expected profit. Hint: In order to check whether you develop the right simulation model, the final distribution of the mean profit for different order quantities (20,25,30_,35_,40) should have a similar distribution/pattern as below (Note: you will not get the exact numbers shown in the following.plot due to the random number generation): ########################### (Q2) A hardware company sells a lot of low-cost, highvolume products. For one such product, it is equally likely that annual unit sales will be low or high. If sales are low (60,000), the company can sell the product for $10 per unit. If sales are high (100,000), a competitor will enter and the company will be able to sell the product for only $8 per unit. The variable cost per unit has a 25% chance of being $6, a 50% chance of being $7.50, and a 25% chance of being $9. Annual fixed costs are $30,000.(a) Use simulation to estimate the company's expected annual profit. (b) Find a 95% interval for the company's annual profit, that is, an interval such that about 95% of the actual profits are inside it.(c) Now suppose that annual unit sales, variable cost, and unit price are equal to their respective expected values-that is, there is no uncertainty. Determine the company's annual profit for this scenario. (d) Can you conclude from the results in parts a and c that the expected profit from a simulation is equal to the profit from the scenario where each input assumes its expected value? when random quantities are replaced by their expected values, an overly optimistic estimate of profit is obtained, what is this pitfall called?