Oak Products Project - Page 5   

  Forward  Back (Page 4)   Excel Review Home

 

The Objective

We can again use the SUMPRODUCT function to compute the objective function, which in this model is the Total Profit. We set aside a cell near the top of the model to hold this calculation and give the cell an appropriate label.

The SUMPRODUCT function for total profit it:

=SUMPRODUCT(C4:H4,C6:H6)

That is, the sum of:

  • The number of Captain's chairs produced X the profit for each Captain's chair, plus
  • The number of Mate's chairs produced X the profit for each Mate's chair, plus
  • The number of American High chairs produced X the profit for each American High chair,
  • And so on for all the chair models.

The Ending Inventory

With Starting Inventory figures already in the worksheet and Total Usage calculated, it's easy to add an additional column to calculate Ending Inventory. Having ending inventory figures is helpful as we perform manual manipulations of the model. Again, we want to make sure that we don't attempt to use more components than are on hand. E.g., that no cell in the Ending Inventory column holds a negative value. Enter the ending inventory formula for long dowel as =J9-K9. Copy this formula down the column for the other components.

At this point, the worksheet (with formulas displayed) looks like this:

Note: The SUMPRODUCT formula in Cell J6 in the illustration above contains =SUMPRODUCT(C4:H4,C6:H6).

We can also conditionally format this column so formatting changes quickly flag any negative values. To apply conditional formatting, highlight the range in this column and choose from Excel's menus Format, Conditional Formatting. Complete the Conditional Formatting dialog.

 

Return to Top   Next Page