Operations Management: An Integrated Approach, 5th Edition

SPREADSHEET MODEL DEVELOPMENT

Now that an algebraic formulation has been developed, we will implement the formulation in the spreadsheet. There is no single way to set up an LP in a spreadsheet, but there are some guidelines that can be established:

• Develop a correct, flexible, and documented model, as described in Supplement A. The user should be able to use the model in a what-if manner, changing any decision variable or coefficient in the model without having to change any formulas.
• Divide the worksheet into three sections: one for the decision variables, one for the objective function, and one for the constraints.
• Use the algebraic formulation and the natural structure of the problem to guide the structure of the spreadsheet.
• Use one cell for each decision variable.
• Store the coefficients of the objective function in separate cells, and use another cell to store a formula that calculates the value of the objective function (by referring to the decision variable cells and the coefficient cells).
• Likewise, store the coefficients of the constraints in cells, and write formulas to compute the “left-hand-side” (LHS) value of each constraint. The LHS value is the value of the constraint expression to the left of the ≤, ≥, or = sign. Then store the “right-hand-side” value (RHS value) of each constraint next to this formula for easy comparison.

LHS value

The value of the constraint ...