PLEASE, Answer it using EXCEL

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:

1. What is your total cost?
2. How many units will be produced of each product in each month?
3. Which constraints are non-binding?
4. What are the shadow prices for overtime hours in month 1 and 2, then explain what do they mean?

A H J B C E F G Month1 Month 2 Demand Regular hours Overtime hours Regular hours Overtime hours Month 1 Month 2 B Regular product Special product Holding cost per month 7 B Hours Needed per unit for R Hours Needed per unit for S Availabe Regular hours per month Availabe Overtime hours per month 1 2 3 24 5 Month1 Month 2 Regular hours Overtime hours Regular hours Overtime hours Regular product Special product Inventory for R Inventory for s Month1 Month 2 Regular hours Overtime hours Regular hours Overtime hours 7 3 3 0 1 2 3 4 5 6 7 8 9 Hours Used for R Hours Used for S Total hours used Month1 Month 2 Net production for R Net production for S 0 0 0 Total 1 2 3 4 5 Production Cost for R Production Cost for S Inventory Holding Cost for R Inventory Holding Cost for S Total Cost