Question

**Please Show Steps in Excel**

Each coffee table produced by Home Woods nets the firm a profit of $15. Each bookcase yields a $20 profit. Home Wood’s firm is small and its resources limited. During any given production period (of 1 week), 20 gallons of varnish and 15 lengths of high quality redwood are available. Each coffee table requires approximately 2 gallon of varnish and 1 length of redwood. Each bookcase takes 1 gallon of varnish and 2 lengths of wood. Assume minimum 4 coffee table and 5 bookcases should be produced per week.

Formulate this manufacturing problem and then find the optimal solution.

Answer 1

let X represents number of coffee table to be produced while Y represents number of bookcase to be produced. since each coffee table yields $15 while each bookcase yields $20 therefore our objective to maximize profit is given by

Objective: Zmax = 15X + 20Y

During any given production period 20 gallons of varnish is available. Each coffee table requires approximately 2 gallon of varnish and Each bookcase takes 1 gallon of varnish. therefore X units of coffee table will require 2X gallons of varnish while Y units of bookcase will require 1Y units of varnish.

2X + 1Y <= 20

During any given production period 15 length of redwood is available. Each coffee table requires approximately 1 length of redwood and Each bookcase takes 2 length of redwood . therefore X units of coffee table will require 1X length of redwood while Y units of bookcase will require 2Y length of redwood.

1X + 2Y <= 15

minimum 4 coffee table and 5 bookcases should be produced per week. therefore

X >= 4

Y >= 5

since X, Y are quantities to be produced therefore they can't be negative and they must be integer , X, Y >= 0

hence problem can be formulated as

Objective: Zmax = 15X + 20Y

subject to constraints:

2X + 1Y <= 20

1X + 2Y <= 15

X >= 4

Y >= 5

solving using MS - Excel we get optimal solution is to produce 5 units of coffee table and 5 units of bookcase while earning profit = $175

XDS5 solver - Excel (Product Activation Failed) ? X FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign in If I Calibri 11 - A Wrap Text General * Cut e Copy Format Painter RA LO Insert Delete Format Paste BIU- 三 Merge & Center - D% 4.0 .00 .00 0 Conditional Format as Cell Formatting Table Styles Styles AutoSum - Fill Sort & Find & Clear Filter Select- Editing Clipboard Font Г Alignment Number Cells с D E F G H — J к L M N 1 2. 3 4 5 6 15 20 formula in respective cells Y Х 2 7 1 <= Parameter profit per unit Variables varnish redwood coffeetable bookcase Solution 8 1 1 2 0 constraint 20 C1 15 15 C2 15 4 cз 5 5 C4 5 objective function Zmax = 9 10 J7=SUMPRODUCT($E$11:$F$11,E7:F7) J8=SUMPRODUCT($E$11:$F$11, E8:F8) J9=SUMPRODUCT($E$11:$F$11, E9:F9) J10=SUMPRODUCT($E$11:$F$11, E10:F10) K11=SUMPRODUCT(E11:F11, E5:F5) 0 1 5 5 175 11 12 13 14 15 16 17 18 19 Sheet1 + READY + 120%