A certain production line makes two products. Relevant data has been collected as indicated below. total time available (for production & setup) each week is 80 hours. The firm has no inventory of either product at the start of week 1 and no inventory is allowed at the end of week 4. The cost of carrying a unit of inventory from one week to the next is $4 for each product. One unit of unsatisfied demand costs $10 for product A and $15 for product B. Demand data are also given below. The line is shutdown & cleaned each weekend. as a result, if a product is produced in a week the appropriate setup cost is incurred. Only 1 product can be produced in a week. No production can take place during the time that the line is being setup.
Formulate and solve this 4 week planning model as a mixed integer lp. The objective is to maximize the profit over a 4-week period. Provide your recommendations for production & inventory of the two products in a form of a memo to the CEO.
Product Data
product
A B
setup time 5hrs 10hrs
perunit production time 0.5hrs 0.75hrs
setup cost $200 $400
perunit production cost $10 $15
Selling price $20 $30
Demand Data
week
Product 1 2 3 4
A 80 100 75 80
B 15 20 50 30
Note: I really need the Variable & coefficients as well as the constraints and the decision variables. If it can be done in excel that would be good, need help thanks.