Question 2: Excel Solution (50 Points) The Nifty Company specializes in the production of a single product, which it produces in three plants. The product is doing very well, so the company currently is receiving more purchase requests than it can fill. Plans have been made to open an additional plant, but it will not be ready until next year. For the coming month, four potential customers (wholesalers) in different parts of the country would like to make major purchases. Customer I is the company's best customer, so his full order will be met. Customers 2 and 3 also are valued customers, so the marketing manager has decided that, at a Page 1 of 4 minimum, at least a third of their order quantities should be met. However, she does not feel that Customer 4 warrants special consideration, and so is unwilling to guarantee any minimum amount for this customer. There will be enough units produced to go somewhat above these minimum amounts. Due largely to substantial variations in shipping costs, the net profit that would be camed on cach unit sold varies greatly, depending on which plant is supplying which customer. Therefore, the final decision on how much to send to cach customer (above the minimum amounts established by the marketing manager) will be based on maximizing profit. The unit profit for each combination of a plant supplying a customer is shown in Table L. The rightmost column gives the number of units that cach plant will produce for the coming month (a total of 20,000). The bottom row shows the order quantities that have been requested by the customers (a total of 30,000). The next-to-last row gives the minimum amounts that will be provided a total of 12,000), based on the marketing manager's decisions described above The marketing manager needs to determine how many units to sell to cach customer (observing these minimum amounts) and how many units to ship from cach plant to each customer to maximize profit. Figure I shows the spreadsheet model for this variant of a transportation problem. Instead of a demand row below the changing cells, we instead have both a minimum row and a maximum row. Table 1 Product 1 Customer Plant 1 545 RO 5.000 561 SIS 532S 551 3000 2.000 0 9,000 6.000 Minimum purchase Requested purchase TO 7000 Plant SIT 542 SIS 3.000 SO 5 551 2) 6.00 3 of 4 0 Min purchase Requested purchase 7 7.000 Page 2 of 4 Nifty Co. Product-Distribution Problem Unit Profit Customer Customer Customer Customer 537 518 551 538 Shipment Plan Production 8.000 3.000 0 0 5.000 7.000 7.000 3.000 2.000 0 You Shoped 7000 27000 4 points cachexcept for the last two items, each of them is 3 points a. What's the range designated for the decision variables in the spreadsheet above? b. What's the formula in cell G11? 6. What's the formula in cell C17? d. What's the formula in cell 1177 c. What's the LHS of Production constraints that you would enter in Solver? What's the RHS of Production constraints that you would enter in Solver? 8. What's the operator for the production constraints and why? h. What's the LHS of minimum purchase constraints that you would enter in Solver? i. What's the RHS of minimum purchase constraints that you would enter in Solver? j. What's the LHS of maximum purchase constraints that you would enter in Solver? k. What's the RHS of maximum purchase constraints that you would enter in Solver? 1. Which cell would you enter in Solver for the "Set Objective" option? m. How much inventory would you need to satisfy all demand