In this exercise, we will model a common supply chain contracting problem, with both limited and unlimited capacities, using outdoor grills as an example. Specifically, we will consider the wholesale price contact. While discussing the solutions for this homework, the instructor may discuss other supply chain contracts. The supply chain under consideration involves one manufacturer and one retailer as pictured below. The manufacturer produces the outdoor grills at a fixed cost (K) of $100,000 plus a variable cost (c) of $35/grill, inclusive of transportation cost to retailer. The manufacturer sells the outdoor grill to the retailer at a wholesale price w (we will have different values of w for different scenarios.) The retailer, in turn, sells the outdoor grills to the consumers at a retail price (p) of $125 apiece.

At the end of the selling season (typically after the Labor Day in the US) any leftover grill is salvaged for $20.00/grill. The salvage value is same irrespective of who (the manufacturer or the retailer) salvages the leftovers.

For this question assume that the manufacturer first decides how much plant capacity to have for the season before the retailer places its order of for the season (at this time the demand is unknown). Assume also that manufacturers cost structure described in Question 1 (i.e., the fixed production cost of $100,000 and variable production cost of $35/grill) no longer holds. Manufacturer now incurs cost of capacity as well as a variable cost of production. If the manufacturer decides to install a capacity of x grills, then the cost of capacity is 110,000 + 30 dollars. In addition, the manufacturer now incurs a variable cost of production of $20/grill. There is no fixed production cost now. If the manufacturer decides to install a capacity of x grills and, if the retailers order exceeds x units, the manufacturer can supply only x units. However, if the retailers order is below x units then the manufacturer fulfills it entirely. The retailer pays the manufacturer based on the quantity received (not quantity ordered). There is no alternative supplier for the retailer. Assume values of all other parameters remain unchanged from Question 1. (a) (15 points). Develop a spreadsheet model to calculate the expected profit of the retailer for different values of retailer order quantities when manufacturers installed capacity is 11,000 grills for the season. Consider order quantities within the range [6000, 18000] in steps of 1000. What is the optimal order quantity of the retailer?

(b) (15 points). As described in this question, the manufacturer makes capacity decision based before knowing the retailers order or the demand. Given this, the manufacturer assumes that retailer orders can take one of the eight values with known probabilities as shown below.

Retailer's order	9000	10000	11000	12000	13000	14000	15000	16000
Probability	8%	10%	12%	19%	17%	16%	11%	7%

Develop a spreadsheet model to calculate the expected profit of the manufacturer for different values of chosen capacity. Consider capacities within the range [6000, 18000] in steps of 1000. What is the optimal capacity choice of the manufacturer?