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:
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.
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:
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