2001OakProducts-Simple

# 2001OakProducts-Simple - An Excel Modeling Practice...

This preview shows pages 1–5. Sign up to view the full content.

An Excel Modeling Practice Problem: Oak Products E XCEL R EVIEW 2001-2002

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
Introduction Excel Skills You’ll Practice In terms of Excel model-building and data-analysis skills, the Oak Products problem gives you practice with: = analyzing a problem from scratch = retrieving data from a non-Excel format = devising a suitable spreadsheet model layout = building formulas to express data relationships = making use of time-saving Excel functions such as SUMPRODUCT = using Excel’s Solver utility to quickly zero in on the best solution = illustrating model results with a graph = printing Fuqua students preparing to take Fuqua’s core Decision Models class may find practice with these skills particularly useful. For additional Excel materials and practice problems see this URL: http://faculty.fuqua.duke.edu/~pecklund/excelreview/ExcelReview.htm Paula Ecklund Spring 2001
1 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.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
2 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
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 12/20/2011 for the course BUS 101 taught by Professor Ecklund during the Spring '01 term at Duke.

### Page1 / 18

2001OakProducts-Simple - An Excel Modeling Practice...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online