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).