Excel Furniture Model

The Brown Furniture Company makes three kinds of office furniture: chairs, desks, and tables. Each product requires skilled labor in the parts fabrication department, unskilled labor in the assembly department, machining on some key pieces of equipment, and some wood as raw material. At current prices, the unit profit contribution for each product is known, and the company can sell everything it manufactures. The size of the workforce has been established, so the number of skilled and unskilled labor hours are known. The amount of wood, fabrication, assembly, and machining required for one unit of each product is known, and a known amount of wood can be obtained from the supplier each month. The managers would like to maximize their monthly profit. The data for this problem is given in the table below.

Chairs Desks Tables Resource
Fabrication (hr) 4 6 2 2000hr
Assembly (hr) 3 8 6 2000hr
Machining (hr) 9 6 4 1440hr
Wood (sqft) 30 40 25 9600 sqft
profit/unit ($) 16 20 14
Furniture model Excel File

Model taken from Optimization Modeling with Spreadsheets, by Kenneth R. Baker, Thomson Brooks/Cole, Duxbury Applied Series, Toronto (2006).