please solve the questions 1. a), 2. a), and 3 that are within the border using excel. do not use solver.

Exercises 1. Lavare, located in the Chicago suburbs, is a major manufac- has 250 employees. Each sink requires two hours of labor input. It costs $3 to carry a sink in inventory for a month. Materials cost per sink is $40. Sinks are sold to distributors at a price of $125 each. We assume that no stockouts are allowed and the starting inventory entering January is 5,000 units and the desired ending inventory in December is also 5,000 units. Market research has indicated that a promotion drop- ping prices by I percent in a given month will increase sales in that month by 20 percent and bring forward 10 percent demand from each of the following two months. Thus, a 1 percent drop in price in March increases sales in March turer of stainless steel sinks. Lavare is in the middle of the demand and supply planning exercise for the coming year. Anticipated monthly demand from distributors over the 12 months is shown in Table 9-4. Capacity at Lavare is governed by the number of machine operators it hires. The firm works 20 days a month, with a regular operating shift of eight hours per day. Any time beyond that is considered overtime. Regular-time pay is $15 per hour and overtime is $22 per hour. Overtime is lim- ited to 20 hours per month per employee. The plant currently Chapter 9 Sales and Operations Planning lanning Supply and Demand in a Supply Chain 243 Capacity at Jumbo is limited by the number of employ- ees it hires. Employees are paid $10 per hour for regular time and $15 per hour for overtime. Each bicycle requires 2 hours of work from one employee. The plant works 20 days a month and 8 hours a day of regular time. Overtime is restricted to a maximum of 20 hours per employee per month. Jumbo currently has 250 employees and prefers not to change TABLE 9-4 Anticipated Monthly Demand at Lavare Month Demand Month Demand 30,000 July 10,000 January 11,000 29,000 February August March September 15,000 21,000 that number. April October 18,000 18,000 Each bicycle uses $35 of material. Carrying a bicycle in inventory from one month to the next costs $4. Jumbo starts with 4,000 bicycles in inventory and wants to end the year with 4,000 bicycles in inventory. Bicycles are currently 14,000 25,000 November May 26,000 December June 11,000 sold to retailers for $80 each. The market is shared between Jumbo and its competitor, Shrimpy. Jumbo is in the process of making its production plan- ning and promotion decisions. Jumbo wants to consider only plans without any stockouts. One option is to drop the sale price by $3 (from $80 to $77) for one month in the year. The outcome of this action by Jumbo is influenced by the action taken by Shrimpy. If neither firm promotes, the forecast demand for Jumbo is as shown in Table 9-5. If Jumbo pro- motes in a given month but Shrimpy does not, Jumbo sees consumption (this does not include forward buying) in that month increase by 40 percent and forward buying of 10 per- cent from each of the two following months. If Shrimpy pro- motes in a given month but Jumbo does not, Jumbo sees consumption in the month drop by 40 percent, with no change in other months. If both promote in a given month, neither sees an increase in consumption but both see forward buying of 15 percent from each of the two following months. The debate within Jumbo is whether to promote, and if so, whether to do it in April or June. For the following questions, assume that Shrimpy and Jumbo have similar demand. a. What is the optimal production plan for Jumbo, assuming no promotion by either Jumbo or Shrimpy? What are the annual profits for Jumbo?. b. What are the profits for Jumbo if Shrimpy promotes in April but Jumbo does not promote throughout the year (because it follows everyday low pricing)? What are the profits for Jumbo if it promotes in April but Shrimpy does not promote throughout the year? Comment on the bene- fit from promoting versus the loss from not promoting if the competitor does. c. What are the optimal production plan and profits if both by 3,000 (= 0.2 15,000) and shifts 1,800 (= 0.1 18,000) units in demand from April and 2,500 (= 0.1 25,000) units from May forward to March. 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? 2. Consider the data for Lavare in Exercise 1. We now assume that Lavare can change the size of the workforce by laying off or hiring employees. Hiring a new employee incurs a cost of $1,000; laying off an employee incurs a layoff cost of $2,000. a. What is the optimal production plan for the year if we assume no promotions? What is the annual profit with 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 the holding cost for sinks increases from $3 per month to $5 per month, does the decision of the timing of pro- motion change? Why or why not? 3. Return to the data for Lavare in Exercise 1. Now, assume that a third party has offered to produce sinks at $74 per unit. How does this change affect the optimal production plan without a promotion? How does this change affect the opti- mal timing of a promotion? Explain the changes. 4 Iumbo manufaaturas hieualas far all nn The damnd foue cast for the coming year is as shown in Table 9-5. promote in April? Both promote in June? Jumbo pro- Exercises 1. Lavare, located in the Chicago suburbs, is a major manufac- turer of stainless steel sinks. Lavare is in the middle of the demand and supply planning exercise for the coming year. Anticipated monthly demand from distributors over the 12 months is shown in Table 9-4. has 250 employees. Each sink requires two hours of labor input. It costs $3 to carry a sink in inventory for a month. Materials cost per sink is $40. Sinks are sold to distributors at a price of $125 each. We assume that no stockouts are allowed and the starting inventory entering January is 5,000 units and the desired ending inventory in December is also 5,000 units. Market research has indicated that a promotion drop- ping prices by I percent in a given month will increase sales in that month by 20 percent and bring forward 10 percent demand from each of the following two months. Thus, a I percent drop in price in March increases sales in March Capacity at Lavare is governed by the number of machine operators it hires. The firm works 20 days a month. with a regular operating shift of eight hours per day. Any time beyond that is considered overtime. Regular-time pay is $15 per hour and overtime is $22 per hour. Overtime is lim- ited to 20 hours per month per employee. The plant currently TABLE 9-4 Anticipated Monthly Demand at Lavare Month Demand Month Demand 10,000 January July August 30,000 February 11,000 29,000 March 15,000 September 21,000 April 18,000 October 18,000 May 25,000 November 14,000 June 26,000 December 11,000 by 3,000(-0.2 x 15,000) and shifts 1,800 (=0.1 x 18,000) units in demand from April and 2,500 ( = 0.1 25,000) units from May forward to March. 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? 2. Consider the data for Lavare in Exercise 1. We now assume that Lavare can change the size of the workforce by laying off or hiring employees. Hiring a new employee incurs a cost of $1,000; laying off an employee incurs a layoff cost of $2,000. a. What is the optimal production plan for the year if we assume no promotions? What is the annual profit with 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 the holding cost for sinks increases from $3 per month to $5 per month, does the decision of the timing of pro- motion change? Why or why not? 3. Return to the data for Lavare in Exercise 1. Now, assume that a third party has offered to produce sinks at $74 per unit. How does this change affect the optimal production plan without a promotion? How does this change affect the opti- mal timing of a promotion? Explain the changes. Exercises 1. Lavare, located in the Chicago suburbs, is a major manufac- has 250 employees. Each sink requires two hours of labor input. It costs $3 to carry a sink in inventory for a month. Materials cost per sink is $40. Sinks are sold to distributors at a price of $125 each. We assume that no stockouts are allowed and the starting inventory entering January is 5,000 units and the desired ending inventory in December is also 5,000 units. Market research has indicated that a promotion drop- ping prices by I percent in a given month will increase sales in that month by 20 percent and bring forward 10 percent demand from each of the following two months. Thus, a 1 percent drop in price in March increases sales in March turer of stainless steel sinks. Lavare is in the middle of the demand and supply planning exercise for the coming year. Anticipated monthly demand from distributors over the 12 months is shown in Table 9-4. Capacity at Lavare is governed by the number of machine operators it hires. The firm works 20 days a month, with a regular operating shift of eight hours per day. Any time beyond that is considered overtime. Regular-time pay is $15 per hour and overtime is $22 per hour. Overtime is lim- ited to 20 hours per month per employee. The plant currently Chapter 9 Sales and Operations Planning lanning Supply and Demand in a Supply Chain 243 Capacity at Jumbo is limited by the number of employ- ees it hires. Employees are paid $10 per hour for regular time and $15 per hour for overtime. Each bicycle requires 2 hours of work from one employee. The plant works 20 days a month and 8 hours a day of regular time. Overtime is restricted to a maximum of 20 hours per employee per month. Jumbo currently has 250 employees and prefers not to change TABLE 9-4 Anticipated Monthly Demand at Lavare Month Demand Month Demand 30,000 July 10,000 January 11,000 29,000 February August March September 15,000 21,000 that number. April October 18,000 18,000 Each bicycle uses $35 of material. Carrying a bicycle in inventory from one month to the next costs $4. Jumbo starts with 4,000 bicycles in inventory and wants to end the year with 4,000 bicycles in inventory. Bicycles are currently 14,000 25,000 November May 26,000 December June 11,000 sold to retailers for $80 each. The market is shared between Jumbo and its competitor, Shrimpy. Jumbo is in the process of making its production plan- ning and promotion decisions. Jumbo wants to consider only plans without any stockouts. One option is to drop the sale price by $3 (from $80 to $77) for one month in the year. The outcome of this action by Jumbo is influenced by the action taken by Shrimpy. If neither firm promotes, the forecast demand for Jumbo is as shown in Table 9-5. If Jumbo pro- motes in a given month but Shrimpy does not, Jumbo sees consumption (this does not include forward buying) in that month increase by 40 percent and forward buying of 10 per- cent from each of the two following months. If Shrimpy pro- motes in a given month but Jumbo does not, Jumbo sees consumption in the month drop by 40 percent, with no change in other months. If both promote in a given month, neither sees an increase in consumption but both see forward buying of 15 percent from each of the two following months. The debate within Jumbo is whether to promote, and if so, whether to do it in April or June. For the following questions, assume that Shrimpy and Jumbo have similar demand. a. What is the optimal production plan for Jumbo, assuming no promotion by either Jumbo or Shrimpy? What are the annual profits for Jumbo?. b. What are the profits for Jumbo if Shrimpy promotes in April but Jumbo does not promote throughout the year (because it follows everyday low pricing)? What are the profits for Jumbo if it promotes in April but Shrimpy does not promote throughout the year? Comment on the bene- fit from promoting versus the loss from not promoting if the competitor does. c. What are the optimal production plan and profits if both by 3,000 (= 0.2 15,000) and shifts 1,800 (= 0.1 18,000) units in demand from April and 2,500 (= 0.1 25,000) units from May forward to March. 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? 2. Consider the data for Lavare in Exercise 1. We now assume that Lavare can change the size of the workforce by laying off or hiring employees. Hiring a new employee incurs a cost of $1,000; laying off an employee incurs a layoff cost of $2,000. a. What is the optimal production plan for the year if we assume no promotions? What is the annual profit with 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 the holding cost for sinks increases from $3 per month to $5 per month, does the decision of the timing of pro- motion change? Why or why not? 3. Return to the data for Lavare in Exercise 1. Now, assume that a third party has offered to produce sinks at $74 per unit. How does this change affect the optimal production plan without a promotion? How does this change affect the opti- mal timing of a promotion? Explain the changes. 4 Iumbo manufaaturas hieualas far all nn The damnd foue cast for the coming year is as shown in Table 9-5. promote in April? Both promote in June? Jumbo pro- Exercises 1. Lavare, located in the Chicago suburbs, is a major manufac- turer of stainless steel sinks. Lavare is in the middle of the demand and supply planning exercise for the coming year. Anticipated monthly demand from distributors over the 12 months is shown in Table 9-4. has 250 employees. Each sink requires two hours of labor input. It costs $3 to carry a sink in inventory for a month. Materials cost per sink is $40. Sinks are sold to distributors at a price of $125 each. We assume that no stockouts are allowed and the starting inventory entering January is 5,000 units and the desired ending inventory in December is also 5,000 units. Market research has indicated that a promotion drop- ping prices by I percent in a given month will increase sales in that month by 20 percent and bring forward 10 percent demand from each of the following two months. Thus, a I percent drop in price in March increases sales in March Capacity at Lavare is governed by the number of machine operators it hires. The firm works 20 days a month. with a regular operating shift of eight hours per day. Any time beyond that is considered overtime. Regular-time pay is $15 per hour and overtime is $22 per hour. Overtime is lim- ited to 20 hours per month per employee. The plant currently TABLE 9-4 Anticipated Monthly Demand at Lavare Month Demand Month Demand 10,000 January July August 30,000 February 11,000 29,000 March 15,000 September 21,000 April 18,000 October 18,000 May 25,000 November 14,000 June 26,000 December 11,000 by 3,000(-0.2 x 15,000) and shifts 1,800 (=0.1 x 18,000) units in demand from April and 2,500 ( = 0.1 25,000) units from May forward to March. 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? 2. Consider the data for Lavare in Exercise 1. We now assume that Lavare can change the size of the workforce by laying off or hiring employees. Hiring a new employee incurs a cost of $1,000; laying off an employee incurs a layoff cost of $2,000. a. What is the optimal production plan for the year if we assume no promotions? What is the annual profit with 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 the holding cost for sinks increases from $3 per month to $5 per month, does the decision of the timing of pro- motion change? Why or why not? 3. Return to the data for Lavare in Exercise 1. Now, assume that a third party has offered to produce sinks at $74 per unit. How does this change affect the optimal production plan without a promotion? How does this change affect the opti- mal timing of a promotion? Explain the changes