The mathematical programming
model is placed in the first 20 rows of the worksheet and the
range for the inventory data is placed below, starting in row
23 for the example. The cells in rows 23 through 33, excluding
rows 24 and 25, can be changed to reflect the data for the problem.
The specific data included depends on the type of inventory
defined by the dialog. In the example, we have chosen to have
an infinite replenishment rate and allow no shortages. The data
for the items has been selected for illustration.
Row 34 holds the lot sizes for the items. These cells have
formulas that link their contents to the math-programming solution
in row 8. The contents in these cells, as well as other yellow
cells, should not be changed. They all hold Excel formulas.
Rows 35 through 39 hold functions provided by the add-in that
compute quantities necessary for the constraints or objective
function of the math-programming model. They all depend on the
lot size in row 34.
The nonlinear programming model is repeated below for convenience.
The variables for the model are the lot sizes
of the three items. We use 10 as the lower bound and the default
of 10000 for the upper bound. The lower bound is chosen to keep
the lot size from going to zero, where some of the functions
are undefined. The objective is to maximize net profit for the
system. The linear coefficients are zero for this application.
The nonlinear objective function terms all point to row 35 of
the inventory definition where the profit for each item is computed.
We have selected four constraints for the example.
The nonlinear terms for the inventory items appear in the
yellow cells of columns H through I. The constraint
values depend on the lot sizes and are computed in the *Value* column.
The restrictions on the quantities are provided by the user
and are listed in the RHS column. The model requires for each
constraint that
*Value *<= *RHS*.
- Investment: This constraint restricts the average inventory
investment for the system. The yellow areas for this constraint
hold formulas that multiply the item costs (row 29) by the
mean values of the inventories (row 36). The sum appears
as the quantity under the word
*Value*. It is limited
by the number in the RHS cell to 20,000. The constraint
is loose for the optimum solution.
- Size: This constraint restricts the maximum space or size
of the inventory. The yellow areas for this constraint hold
formulas that multiply the item sizes (row 32) by the maximum
values of the inventories (row 37). The total size appears
as the quantity under the word
*Value*. It is limited
by the number in the RHS cell to 9,000. This constraint
is tight.
- Residence Time: This constraint restricts the weighted residence
times for the items. The yellow areas for this constraint hold
formulas that multiply the weights (row 33) by the mean residence
times (row 39). The sum appears as the quantity under the
word
*Value*. It is limited
by the number in the RHS cell to 20 weeks. This constraint
is loose.
- Order Frequency: This constraint limits the number of replenishment
orders per time interval (per week for the example). The yellow
areas for this constraint hold the inverses of the cycle times
(row 38). The sum appears as quantity under the word
*Value*.It
is limited by the number in the RHS cell to 1 per week. This
constraint is also loose.
Clicking on the Solve button automatically calls the Excel
Solver, loads the model and initiates the solution procedure.
Be sure to open and close the *Solver* dialog before
using this button. If the model was created on a different computer,
use the *New Functions and Buttons* command on the menu
to create a new button.
Since this is a nonlinear programming model, the solution may
not converge to a globally optimum solution. Try several different
starting points for the variable values in row 8, and choose
the solution with the greatest profit. There is no guarantee
that the global optimum will be obtained by this process, but
it improves the chance that a good solution will be found. |