Hi team,

I am looking for a solution in Operations Management by using Excel Data Solver, below is the case study:

ABC PAPER MANUFACTURING

The Production manager of a paper manufacturing company must determine the daily product mix for a new paper and board plant. This plant produces four products: foot-board, container-board, book paper and coarse paper. The sales department had established the following maximum daily demand and sales prices:

Foot board 60 tons/day Rs. 2,050/- per ton

Container board 120 tons/day Rs. 1,100/- per ton

Book paper 25 tons/day Rs. 1,700/- per ton

Coarse paper 30 tons/day Rs. 1,550/- per ton

The first step in the manufacture of these products is the purchase of the wood to be chipped and pulped. Hardwood can be purchased for Rs. 234/- per cord; Soft-wood costs Rs. 260/- per cord (There is approximately 1.3 tons in a cord of either type of wood). The available local supply is limited to 200 tons per day of hardwood and 350 tons a day of softwood.

Hardwood and softwood are chipped and pulped separately. The equipment has a total output capacity of 235 tons per day of pulp, regardless of the type of wood being processed. The yield on hardwood is 47% (that is, the total tonnage of hardwood pulp is 47% of the hardwood tonnage fed into the process). The yield for softwood is 45%. The chipping and pulping process incurs a variable cost of Rs. 600/- per ton of pulp produced.

A portion of the pulp is used directly in producing the final products and the remaining portion is bleached first. The stream of unbleached pulp going directly into the paper and board machines is composed (by weight) of 60% hardwood pulp and 40% softwood pulp. The stream of pulp going into the Bleacher consists of 50% hardwood and 50% softwood pulp. Bleaching adds a variable cost of Rs. 100/- per ton of bleached pulp produced. The bleacher yield is 98% and has a daily output capacity of 80 tons.

The bleached and unbleached pulps are mixed in fixed proportions for each of the final products. Information regarding quantities of bleached and unbleached pulp required to produce one ton of each of the final products is given below.

A board machine is used to produce container board and foot board. A variable cost of Rs. 50/- is incurred from production of a ton of either product; the board machine has an output capacity of 190 tons per day. A paper machine is used to produce book paper and coarse paper. A variable cost of Rs. 50/- is incurred from production of a ton of either product, and the machine has an output capacity of 60 tons per day.

Tons of pulp required to produce one ton of final product

Foot Board

Container board

Book paper

Coarse paper

Unbleached pulp

--

0.10

0.70

0.90

Bleached pulp

1.10

0.95

0.40

0.15

P.T.O.,

Hard wood

Soft wood

Chipping and pulping machine

HW pulp - Direct use

HW pulp to bleacher

SW pulp - Direct use

SW pulp to bleacher

Unbleached Pulp

Bleached Pulp

60%

40%

50%

50%

Bleaching Machine

Board Machine

Paper Machine

Foot Board

Container Board

Book Paper

Coarse Paper

Rs. 234/- per cord

Rs. 260/- per cord

47% Yield

45% Yield

Yield 98%

Variable Cost: Rs. 50/- per ton produced

Capacity: 190 tons/day

Variable Cost: Rs. 50/- per ton produced

Capacity: 60 tons/day

Variable Cost: Rs. 100/- per ton produced

Capacity: 80 tons/day

Variable Cost: Rs. 600/- per ton produced

Capacity: 235 tons/day