Alan Industries is expanding its product line to include three new products: A, B
and C. These are to be produced on the same production equipment, and the
objective is to meet the demands for the three products using overtime where
necessary. The demand forecast for the next four months, in hours required to make
each product, is shown below. Costs and other additional data also are provided.
Your assignment is to create a lowest cost production plan for the next four months.
You must determine how many regular hours and overtime hours to assign to each
product in each month.
Total units available of each product in each month must be sufficient to meet
demand in that month.
Total units available of each product in each month is the sum of that months
production plus any inventory carried over from the previous month. No inventory has
been carried over into April. Beginning inventory in April is zero.
Because the products deteriorate rapidly, there is a high loss in quality and,
consequently, a high carrying cost when extra product is made in any month and
carried over to meet future demand. Production carried as inventory into the
following month has a carrying cost per hour as shown below. This cost is charged to
the month in which the production occurred.
Excel Solver Solution
1. State the decision to be made and identify the Decision Cells.
	In this case: How many hours of regular and overtime production
	should be assigned to each product, in each month, in order to
	meet forecast demand?
2. Set up needed calculated values with cell references to Decision Cells.
3. Show the formula to be maximized or minimized.
4. Subject to the constraints.
	Write out each constraint in words. Then set up a table of the
	constraints using cell references to Decision Cells and the
	calculated values.
5. Complete the Solver dialog box and solve for lowest cost production plan.

DATA Product June A B Demand Forecast April May 600 600 600 800 500 600 700 1,000 700 July 900 1,100 700 Production Hours Available April May June 1,300 1,500 1,600 700 800 Regular Time -Overtime July 1,700 700 700 Production Costs Regular Time Product June July A $4.00 April $4.00 $5.00 $6.00 May $4.00 $5.00 $6.00 B $4.00 $5.00 $6.00 $5.00 $6.00 Overtime Product June July A $6.00 April $6.00 $7.50 $9.00 May $6.00 $7.50 $9.00 B $6.00 $7.50 $9.00 $7.50 $9.00 Product June July Carrying Costs April May $3.00 $3.00 $4.00 $4.00 $5.00 $5.00 $3.00 $3.00 $4.00 B $4.00 $5.00 $5.00 DATA Product June A B Demand Forecast April May 600 600 600 800 500 600 700 1,000 700 July 900 1,100 700 Production Hours Available April May June 1,300 1,500 1,600 700 800 Regular Time -Overtime July 1,700 700 700 Production Costs Regular Time Product June July A $4.00 April $4.00 $5.00 $6.00 May $4.00 $5.00 $6.00 B $4.00 $5.00 $6.00 $5.00 $6.00 Overtime Product June July A $6.00 April $6.00 $7.50 $9.00 May $6.00 $7.50 $9.00 B $6.00 $7.50 $9.00 $7.50 $9.00 Product June July Carrying Costs April May $3.00 $3.00 $4.00 $4.00 $5.00 $5.00 $3.00 $3.00 $4.00 B $4.00 $5.00 $5.00