Consider a product-max example where a company is concerned with the mix of 4 products it should produce during the upcoming week. Production of each products requires a given amount of production time on each of three machines, and each machine has a certain available production time per week. Each product provides a certain profit. The company needs to determine an optimal product mix so as to maximize profit while considering constraints related to the limited production capacity of machines. The table below shows the profitability of each item, the time required in each operation for each product, and the production time available in each department each week.

1) Formulate a Linear Program and Input into Excel

2) Solve the linear program and answer the following questions:

-- How many of each product should be produced in order to maximize weekly profit?

-- What is the total profit using your optimal solution?

-- If you were to add capacity to rolling, cutting or welding - to which department would you add capacity? WHY?

FORMULATE AND SOLVE A LINEAR PROGRAM IN EXCEL

Production Time per Unit (in hours) PRODUCT B Production Time Available per Week (in hours) A D 1.7 2.1 1.4 2.4 28 Machine for Rolling Cutting Welding Profit per Unit 2.5 1.7 2.6 1.1 1.6 $26 34 21 1.3 $35 1.6 $25 0.8 $37