Linear Programming with Microsoft Excel

• One objective function

• One or more constraints

• Alternative courses of action

• Objective function and constraints are linear

• Certainty

• Proportionality

• Additivity

• Divisibility

• Nonnegative variables

Omni Furniture's production process for tables and chairs requires a certain number of hours of carpentry work and a certain number of labor hours in the painting and varnishing department.  Determine the best combination of tables and chairs to manufacture for Omni to maximize profit.

The graphical method works only when there are two decision variables, but it provides valuable insight into how larger problems are structured.

The optimal solution to any problem will lie at a corner point of the feasible region.  It is only necessary to find the values of the variables at each corner of the feasible region; the optimal solution should lie at one (or more) of them.

Excel and other spreadsheets offer their users the ability to analyze problems using built-in problem solving tools.  Excel uses a tool named Solver to find solutions to linear programming related problems.

Poultry Feed Company is considering buying two different brands of turkey feed and blending them to provide a good, low-cost diet for turkeys for resale.  Each feed contains, in varying proportions, some, or all, of the three nutritional ingredients essential for fattening turkeys.  Poultry would like to determine the lowest cost diet that meets the minimum monthly intake requirement for each nutritional ingredient.

Lack of a feasible solution region can occur if constraints conflict with one another.

When the profit in a maximization problem can be infinitely large, the problem is unbounded and is missing one or two constraints.

A redundant constraint is one that does not affect the feasible solution region.

Multiple optimal solutions are possible.

Sound Company manufacturers high quality headphones and Bluetooth speakers.  Each of these products requires a certain amount of skilled artisanship, of which there is a limited weekly supply.  Determine the best combination of headphones and Bluetooth speakers to manufacturer in order to maximize profit.

We can increase or decrease the objective coefficient of any variable, and the corner point may remain optimal if the change is not too large.  However, if we increase or decrease this coefficient by too much, then the optimal solution would be at a different corner point. 

Changes in the technological coefficients often reflect changes in the state of technology.  These changes will have no effect on the objective function, but they can produce a significant change in the shape of the feasible region and the optimal solution.

The right-hand-side values of the constraints often represent resources available to the firm.  If the right-hand-side of a constraint is changes, the feasible region will change and often the optimal solution will change.

Some key takeaways about linear programming.

How to install Solver in Excel.

Key terms used in linear programming