For example, in the illustration at left, the column for Captain's chair holds the name, the number to produce, and the number of components required.

You'll see as we proceed that this data layout simplifies construction of the formulas required by the model.

 

Oak Products Project - Page 4   

  Forward  Back (Page 3)   Excel Review Home

 

D. Add Decision Variables

The next step is to add the decision variables to the model. I added a row for the decision variables and gave it the label Qty Produced (or Quantity Produced). In this problem, the decision variables are the numbers of each chair to manufacture. I've arranged these variables so they're aligned in the same columns as the data on which they'll operate. (Click here to see the entire worksheet at this stage.)

Since in August, OP always makes forty chairs of each type, we fill the Qty Produced row with 40 for each chair. I've highlighted these critical decision variable cells with a border (explained below on formatting) to distinguish them from the data.

Return to Top

 

E. Add Formulas

At this point I'm ready to calculate the objectives and the constrained quantities. I can do this by means of formulas that reference the data and the decision variables. Writing formulas into the model, I calculate the following:

  • How many of each component are required for the quantities of chairs (now set at 40 for each type) we'll produce? This information goes into a new column with the label Total Usage.

  • How much inventory will be left over at the end of the manufacturing process. Naturally, values in this column can't be less than zero, but zero in each column would be ideal. I add a new Ending Inventory column to the model and a formula calculates ending inventory for each one of the components listed in the first column.

  • What profit will result from our product mix? The calculation that will give us this information goes into a cell with the label Total Profit. With this model, our objective is to maximize the Total Profit value.

    Again, because this is a critical value for the model (like the number of each chair to manufacture), I locate it in a visible position near the top of the model.

    At this point our worksheet will look like this:

Return to Top

 

The Constrained Quantities

In our model we need to track how many of each chair component we use. In our situation, components are a constrained quantity since we can't use more than what the factory has on hand for the month of August. We'll track that value for each chair component by means of a Total Usage calculation.

Because of the way the data is arranged, we can save time and effort and simplify the worksheet in our calculation of Total Usage by using a built-in Excel function named SUMPRODUCT. This function allows us to easily compute the Total Usage for the first chair component (long dowel) for all six chair types.

Capt
Mate
AmHi
AmLo
Spank
SpanQ
Total Usage
Long Dowel
8
0
12
0
8
4
0

Then we can copy the formula we've built for the first long dowel component so it calculates Total Usage for each of the rest of the components (short dowel, leg, heavy seat, etc.).

The SUMPRODUCT function is a shorthand way to do the following:

  1. Multiply the Qty Produced for the Captain's chair by the number of long dowels needed for that model. Then:
  2. Multiply the Qty Produced for the Mate's chair by the number of long dowels needed for that model. Then:
  3. Do the same operation for the long dowel component for each of the quantities produced for each of the remaining four chair models: American High, American Low, Spanish King, and Spanish Queen. Then:
  4. Add together the results for all the chair models for Qty Produced X number of long dowel components required. The result of this addition is the Total Usage, or the total number of long dowels required for all the chairs.

The SUMPRODUCT function for the long dowel component has this format:

=SUMPRODUCT($C$4:$H$4,C9:H9)

Or, an easier-to-understand version might look like this:

=SUMPRODUCT(All-Chairs-Qty-to-Produce-Range,All-Chairs-Long-Dowel-Range)

In our example, this formula goes into the first cell (Cell K9) in the Total Usage column. That is, in the row for long dowel.

To summarize:

  • $C$4:$H$4 is the range referring to the quantities of each chair produced.
  • C9:H9 is the range referring to the number of long dowels required for each chair type.

Copy the formula for the long dowel down the Total Usage column, to complete the formulas that are required for each of the rest of the components.

Click here to review an important note on the use of absolute and relative addressing in the SUMPRODUCT formula used above.

Return to Top   Next Page