Problem Overview

Imagine that you’re a new employee at a small, family-owned North Carolina

furniture manufacturing company named Oak Products, Inc. (OP). OP produces

a line of high-quality solid oak chairs. The chairs have been designed to use a

variety of components: Long and short dowels, heavy and light seats, heavy and

light rungs, and a rail that caps the back.

OP manufactures six types of chairs: Captain, Mate, American High, American

Low, Spanish King, and Spanish Queen. Each type of chair requires a certain

number of different parts. At OP, finishing activity for a chair requires one

month. For this reason only chair components that are on hand when a month’s

production starts can be used in chairs that will be produced during that month.

As a new employee you’re rotated through the different areas of the company’s

operations and you now have the chance to review the company’s production

plans. In August, OP has always had as its target the production of forty chairs of

each type. However, as you look over the factory data it appears that a different

product mix might be more profitable. To help OP maximize its total August

profits you do the following:

Obtain the Raw Data

You request a list of resource requirements for each chair type, current

chair profit levels, and the parts inventory expected to be on hand for

August. This information is made available to you in MS Word table

format. By importing some of the Word data into Excel, you can save data

entry time in Excel.

Analyze the Data

You build a model in Excel to analyze the current data. Your objective is

to adjust production levels to achieve the most profitable product mix for

the month of August, subject to manufacturing production constraints.

Obtain the Word Data

The Word document you receive holds:

__resource requirements for each chair type (for each chair, a column shows

how many dowels, rungs, rails, etc. are required)

__current chair profit levels (in a row with the label "Unit Profit")

__the parts inventory expected to be on hand for August (in a column with the

label "On Hand")

The contents of the Word file appears below. The data is arranged in tabular

format.

Oak Products

Manufacturing Data for August 2001

Analyze the Data

Build a spreadsheet model in Excel to help you determine August’s most

profitable product mix. Although you could start your worksheet from scratch,

you can save data entry time by importing into Excel some of the data from the

Word document.

1. Import Word Data Into Excel
   Setup: Open both Excel and Word as full-screen sessions. (You can shift between
   them by holding down the Alt key and pressing the Tab key.)
   Select the Data to Copy:
   __In Word, open the document that holds the data.
   __Position the insertion point in the table of data.
   __Choose the Table command in Word’s menu.
   __Choose Select Table . Word highlights the entire table (but not the separate
   document header).
   Copy the Data to the Windows Clipboard:
   From Word’s menus choose Edit and then Copy . (Alternatively, click the Copy
   button on Word’s Standard toolbar or hit CTRL+C.)
   Copy the Data to Excel:
   __Switch to Excel by hitting Ctrl+Tab until your Excel session is active.
   __Click Cell A3 to make it the current cell.
   __From Excel’s menus, select Edit, Paste Special.
   __In the Paste Special dialog box, select Paste and As Text and click OK .
   
   ready to build the model around this core set of data.
2. Conceptualize the Problem

Objective

Your objective for OP’s August production is to find the mix of chair types to

manufacture that results in the highest possible profit.

Constraints

__OP has only the current numbers of parts-on-hand (as

described in the Word table) to work with.

__Each of the six chair types requires a different mix of parts.

Assumptions

__OP can sell every chair they make.

__The number of parts on hand cannot be increased.

__It’s not possible to make substitutions for chair parts.

Data

You’ve already identified and obtained the raw data you need to determine the

best product mix. To review, the raw data includes:

__A description of the manufacturing line with the parts required for each chair

__The parts on hand

__The profit OP earns from each chair type

Questions:

1. How many of each type of chair should OP make to maximize profits?
2. What will their maximum profit be?
3. Which parts are constraining in this model?
4. Does OP appear to be way overstocked on any parts? If so, which parts?
5. Which variables are basic?
6. Which variables are nonbasic ?
7. The AmHi chair makes the most profit per chair. Why cannot OP just dispense with all of this math stuff and make all AmHi chairs?

Price: $17.03

Solution: The downloadable solution consists of 8 pages, 903 words and 1 charts.
Deliverable: Word Document