SOLVE WITH EXCEL SOLVER

1. Lavare, located in the Chicago suburbs, is a major manufac- units in demand from April and 2,500= 0.1 X 25,000) units turer of stainless steel sinks. Lavare is in the middle of the from May forward to March S&OP exercise for the coming year. Anticipated monthly demand from distributors over the 12 months is shown in TABLE 9-4 Anticipated Monthly Demand at Lavare Table 9-4. Capacity at Lavare is governed by the number of Month Demand machine operators it hires. The firm works 20 days a month, January 10,000 with a regular operating shift of eight hours per day. Any time beyond that is considered overtime. Regular-time pay is $15 February 11,000 per hour and overtime is $22 per hour. Overtime is limited to March 15,000 20 hours per month per employee. The plant currently has 250 employees. Each sink requires two hours of labor input. April 18,000 It costs $3 to carry a sink in inventory for a month. Materials May 25,000 cost per sink is $40. Sinks are sold to distributors at a price of June 26,000 $125 each. We assume that no stock outs are allowed and the starting inventory entering January is 5.000 units and the July 30,000 desired ending inventory in December is also 5,000 units. August 29,000 Market research has indicated that a promotion drop September 21,000 ping prices by 1 percent in a given month will increase sales in that month by 20 percent and bring forward 10 percent October 18,000 demand from each of the following two months. Thus, al November 14,000 percent drop in price in March increases sales in March By 3,000(= 0.2 x 15,000) and shifts 1,800(0,1 X 18,000) December 11,000 a. What is the optimal production plan for the year if we assume no promotions? What is the annual profit from this plan? What is the cost of this plan? b. Is it better to promote in April or July? How much increase in profit can be achieved as a result? c. If sinks are sold for $250 instead of $125, does the deci- sion about the timing of the promotion change? Why