solution

Individual Excel Solver Computer Project

Natural Furniture Company manufactures three outdoor products, chairs, benches, and tables. Each product must pass through the following departments before it is shipped: sawing, sanding, assembly, and painting. The production time available in each department each week and the minimum weekly production requirement to fulfill contracts are as shown in the table below

Weekly production time and quantities

Department	Capacity (In hours)	Product	Minimum production level
Sawing	450	Chairs	100
Sanding	400	Benches	50
Assembly	625	Tables	50
Painting	550

The production time requirements (in hours) and profit per unit of each product are summarised in the table below.

Production time and profit per single product

Hours required
Product	Sawing	Sanding	Assembly	Painting	Unit profit
Chairs	1.5	1.0	2.0	1.5	$15
Benches	1.5	1.5	2.0	2.0	$10
Table	2.0	2.0	2.5	2.0	$20

The production manager has the responsibility of specifying production levels for each product for the coming week.

(a) Formulate a linear programming model for this problem.

(b) Set up a spreadsheet model for this problem and use the Excel Solver to find the optimal solution.

(c) State the optimal solution and the value of the objective function.