A manufacturing firm makes photographic paper and needs to plan its production and distribution of this product over the next three months.The production of raw or unfinished paper is fixed and is given in the following table for the next three months.

	Supply of photographic paper
Month 1	1200
Month 2	1500
Month 3	1000

The units are wide rolls of paper; thus, we are given that there will be a supply of 1200 units of paper in month 1, 1500 in month 2 and 1000 units in month 3.

In each month the firm needs to decide how to use this supply to produce finished paper, where there are three finished products: glossy, semi-glossy and matte.There are three dedicated processes to produce these products, each with a capacity limit and cost given in the next table:

	Finishing process
	Cost	Capacity/month
Glossy	1	500
Semi-gloss	0.8	500
Matte	0.8	500

For instance, the cost per unit to produce glossy paper is $1/unit, and there is capacity limit of 500 units/month for this finishing process.

In each month the firm also needs to decide where to ship the finished products.There are three distribution centers: West, East and South.The cost and capacity for each distribution option is in the table:

	Distribution process
	West	East	South
Cost	2	1	1.5
Capacity/month	500	500	500

For instance, it costs $2/unit to ship to the West distribution center, and there is limit of 500 units/month that can be shipped to the West DC.

Demand is served from the DCs.For each product and each DC, the firm has a forecast of the maximum demand by month, as given below.There is no penalty for not meeting the maximum demand.

Maximum demand/month
	West	East	South
Glossy	250	300	200
Semi-gloss	300	250	200
Matte	200	400	300

Inventory can be held at the DCs at a cost of $1.10 per unit per month.The firm does not hold inventory at its manufacturing facility. For planning purposes, we can assume that the initial inventory at each DC is zero for all products, and that there are no requirements on the ending inventory at the end of month 3.

The firm sells its products at the following market prices ($/unit).Note that the firm is forecasting that the prices will increase over the next three months for both glossy and matte paper.

Sales price
	Month 1	Month 2	Month 3
Glossy	5	6	8
Semi-gloss	4	4	4
Matte	3	4	5

For purposes of this planning exercise, we will not model any time delays due to production or shipping within a month. In effect we are assuming that product that is produced and finished in a month, can then be shipped to a DC and sold within the same month.

The objective of the firm is to maximize its profits, which equal its sales revenues minus its costs.For this planning exercise we need to only consider the costs for finishing, for shipping and for inventory holding.

Lets use the following sets of indices, parameters and variables:

i : product

j: distribution Center

t: month

pit: sales price for product i in time t

h: holding cost per unit per month

ci1: is the finishing cost for product i

cj2: is the shipping cost to DC j

kt: is the supply of paper in month t

dij: is the maximum demand per month for product i at DC j

Develop a linear programming problem by defining equations and solve it using excel solver.

Hint: Its a two stage problem