Question

A furniture firm makes three types of tables: coffee table, side table and dining room table, and two types of chairs : arm chair and side chair. A coffee table requires 35 unit s of wood, a side table needs 45 units of wood, and a dining room table requires 95 units of wood. In addition, an arm chair requires 40 units of wood and a side chair needs 30 units of wood. There are at most 9800 units of wood. Moreover, each coffee table needs 25 minutes of production time , a side table requires 20 minutes of production time and each dining room table requires 80 minutes of production time. The following table presents the same information (in minutes) for the two types of chairs.

Chair Arm chair 30 Side Chair 20 Production time

There are also 110 hours of production time. Assume the net profit for producing a coffee table is $ 30, a side table is $28 and for a dining room table is $50. In addition, each arm chair and side chair would generate a profit of $15 and $25, respectively. How many from each types of tables and chairs should be produced in order to maximize the total profit of the furniture firm?

Solve the problem using Excel Solver and find the optimal solution.

Answer 1

here, we will maximize the Profit,

Maximize objective function = 30*X1 +28*X2+50*X3+15*X4+25*X5

Constraints

35*X1+45*X2+95*X3+40*X4+30*X5=<9800

25*X1+20*X2+80*X3+30*X4+20*X4=<110

X1, X2, X3, X4 and X5 >= 0

Decision Variables:

X1 = Number of coffee table

X2 = Number of side table

X3 = Dining room table

X4 = Arm Chair

X5 = Side chair

1	B	C	D	E	F	G	H	I	J
2	Profit per Coffee table	30
3	Profit per side table	28
4	Profit per dining room table	50
5	profit per arm chair	15
6	Profit per side chair	25
7
8		Coffee table	side table	dining room table	arm chair	side chair
9	Unit Sold	0	5	0	0	0
10	Profit Generated	30	28	50	15	25
11		Coffee table	side table	dining room table	arm chair	side chair
12	woods	35	45	95	40	30	247	=<	9800
13	time	25	20	80	30	20	110	=<	110
14		1	0	0	0	0	0	>=	0
15		0	1	0	0	0	5	>=	0
16		0	0	1	0	0	0	>=	0
17		0	0	0	1	0	0	>=	0
18		0	0	0	0	1	0	>=	0
19	Total Profit generated- Maximum	154

Excel Formula:

1	B	C	D	E	F	G	H	I	J
2	Profit per Coffee table	30
3	Profit per side table	28
4	Profit per dining room table	50
5	profit per arm chair	15
6	Profit per side chair	25
7
8		Coffee table	side table	dining room table	arm chair	side chair
9	Unit Sold	0	5.49999999995946	8.63380915319896E-12	4.00520012538094E-12	0
10	Profit Generated	30	28	50	15	25
11		Coffee table	side table	dining room table	arm chair	side chair
12	woods	35	45	95	40	30	=SUMPRODUCT($C$9:$G$9,C12:G12)	=<	9800
13	time	25	20	80	30	20	=SUMPRODUCT($C$9:$G$9,C13:G13)	=<	110
14		1	0	0	0	0	=SUMPRODUCT($C$9:$G$9,C14:G14)	>=	0
15		0	1	0	0	0	=SUMPRODUCT($C$9:$G$9,C15:G15)	>=	0
16		0	0	1	0	0	=SUMPRODUCT($C$9:$G$9,C16:G16)	>=	0
17		0	0	0	1	0	=SUMPRODUCT($C$9:$G$9,C17:G17)	>=	0
18		0	0	0	0	1	=SUMPRODUCT($C$9:$G$9,C18:G18)	>=	0
19	Total Profit generated- Maximum	=SUMPRODUCT(C9:G9,C10:G10)

Solver Parameter :

Solver Parameters 6 Solve value of: 0 Close R Guess Set Target Cell: $C$19 Equal To: Max Min By Changing Cells: $C$9:$G$9 Subject to the Constraints: SH$12 <= $J$12 SH$13 <= $J$13 $H$14 >= $J$14 $H$15 >= $J$15 $H$16 >= $J$16 SH$17 >= $J$17 Options Add Change Reset All Delete Help