Excel Exercises: Oak Products Project*   

  Forward  Back (Exercises Home)  Excel Review Home

 

Introduction

Download

The  Oak Products document is available for download from this page. The file is in Adobe Acrobat v.4 format and is about 498K in size. To follow along with the project in Excel, you'll also want to download a copy of the Word 97  data file you need as a starting point.

The Text

The "Oak Products" information is also reproduced on this site, starting below. Follow the links to review the entire document.

 

The Excel Skills this Project Exercises

In terms of Excel model-building and data-analysis skills, the Oak Products project gives you practice with:

  • analyzing a problem from scratch
  • 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.

Return to Top

 

Problem Overview

Imagine that you're a new employee at a small, family-owned North Carolina furniture manufacturing company called Oak Products, Inc. (OP). OP produces a line of high-quality solid oak chairs. The chairs have been designed to use a number of interchangeable components: long and short dowels, heavy and light seats, heavy and light rungs, and a rail that caps the back. The interchangeable parts help protect OP against sudden shifts in demand.

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 the chairs requires one month. For this reason only chair components that are on hand when the 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 get a 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:

  1. 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 as document in Word format.

  2. Analyze the Data - You build a model in Excel to analyze the current data. The objective is to adjust production levels to achieve the most profitable product mix for the month of August, subject to manufacturing production constraints. (By importing some of the raw Word  data into Excel, you can save data entry time as you build your Excel model.)

Return to Top    Next Page

 

* The Oak Products problem is drawn from Prentice-Hall's "Introductory Management Science" by Eppen, Gould, Schmidt, Moore, and Weatherford.