PLEASE, Solve it with Excel by using the SOLVER and show all steps. Otherwise I will dislike the answer.

PLEASE, Solve it with Excel by using the SOLVER and show all steps. Otherwise I will dislike the answer.

PLEASE, Solve it with Excel by using the SOLVER and show all steps. Otherwise I will dislike the answer.

PLEASE, Solve it with Excel by using the SOLVER and show all steps. Otherwise I will dislike the answer.

1. Rakan is the new operations manager of Ronnie Coleman Inc. and he has just received orders for 1,000 regular and 1,250 special products next month and for 800 regular and 1,500 special products the following month. All orders must be met.

The production cost of regular products is $10 and for special products is $16. Overtime rates are 50% higher. There are 1,000 hours of regular time and 500 hours of overtime available each month. It takes (0.5) hour to make a regular product and (0.6) hour to make a special product. The cost of storing one product (regular or special) from one month to the next is $2.

(Hint: we have two cost components to be considered production and inventory holding cost)

Rakan wants to develop a two-month production schedule for regular and special products. Formulate this production planning problem as a linear program then solve it using Excel Solver (Use Ronnie Coleman Inc. Problem worksheet)

Then answer these questions:

a. What is your total cost?

b. How many units will be produced of each product in each month?

c. Which constraints are non-binding?

d. What are the shadow prices for overtime hours in month 1 and 2, then explain what do they mean?

A B D E F G w N 1 Month1 Month 2 Demand 2 Regular hours Overtime hours Regular hours overtime hours Month 1 Month 2 3 Regular product 4 Special product 5 Holding cost per month 6 7 Hours Needed per unit for R 8 Hours Needed per unit for 9 Availabe Regular hours per month 10 Availabe Overtime hours per month 11 12 Month1 Month 2 13 Regular hours overtime hours Regular hoursOvertime hours 14 Regular product 15 Special product 16 Inventory for R 17 Inventory for s 18 19 Month1 Month 2 20 Regular hours overtime hours Regular hours overtime hours 21 Hours Used for R 22 Hours Used for 23 Total hours used 24 wN Month1 Month 2 25 26 27 28 Net production for R Net production for S 29 Total 30 31 32 33 34 Production Cost for R Production Cost for S Inventory Holding Cost for R Inventory Holding Cost for S Total Cost