Assignment:
Q: The MJK Manufacturing Company must produce two products in sufficient quantity to meet contracted sales in each of the next three months. The two products share the same production facilities, and each unit of both products requires the same amount of production capacity. The available production and storage facilities are changing month by month, so the production capacities, unit production costs, and unit storage costs vary by month. Therefore, it may be worthwhile to overproduce one or both products in some months and store them until needed. For each of the three months, the initialed columns of the following table give the maximum number of units of the two products combined that can be produced on regular time (RT) and on overtime (OT). For each of the two products, the subsequent columns give
(1) the number of units needed for the contracted sales,
(2) the cost (in thousands of dollars) per unit produced on regular time,
(3) the cost (in thousands of dollars) per unit produced on overtime, and
(4) the cost (in thousands of dollars) of storing each extra unit that is held over into the next month. In each case, the numbers for the two products are separated by a slash with the number for product 1 on the left and the number for product 2 on the right.
The production manager wants a schedule developed for the number of units of each of the two products to be produced on regular time and, if regular time production capacity is used up, on overtime in each of the three months. The objective is to minimize the total of the production and storage costs while meeting the contracted sales for each month. There is no initial inventory, and no final inventory is desired after the three months.
Formulate and solve a spreadsheet model for this problem