Minimizing Cost while Meeting Nutritional Needs -- An example of linear programming

Yu-Ju Kuo, Indiana University of Pennsylvania, Indiana, PA15705
Author Profile
This material was originally developed by Spreadsheets Across the Curriculum as part of its collaboration with the SERC Pedagogic Service.
Initial Publication Date: September 21, 2007

Summary

The "Dietary Problem" is important in our daily lives. The key issue is whether one's choice of foods meets nutritional requirements while at the same time meeting the objective of paying minimum cost or consuming minimum calories. In this Spreadsheets across the Curriculum module, students set up the Dietary Problem as a linear programming (LP) model, find the feasible set from inequality constraints, consider how the solution appears on a graph, and then use Excel's Solver feature to solve the problem. The module illustrates the basic idea of searching for solutions to minimization/maximization problems via linear programming and provides step-by-step directions for using Excel Solver to solve such a problem.

Share your modifications and improvements to this activity through the Community Contribution Tool »

Learning Goals

Students will

  • set up a simplified dietary model using two foods and two nutrition requirements in the form of a linear programming (LP) model.
  • use Excel Solver to solve the problem.
  • explore the model of the simplified dietary problem through what-if questions.

In the process, the students will

  • learn how to develop a LP model.
  • learn how to solve and analyze a LP model.

Context for Use

This module was designed originally for the first two class periods in an Introduction to Operations Research course. It could also be used after students have studied linear inequalities and linear functions. It is advisable that students have basic Excel skills, such as modifying cells, or writing formula.

The first portion of module (background information and simplified dietary model) could be done in lecture form, but it is important to conduct the Excel Solver in a lab. The activity can easily be expanded to a larger model using the data set provided.

If students are comfortable with linear inequalities and basic Excel, the module can also be used as the basis of a project.

Description and Teaching Materials

SSAC2007.QP141.YJK1.1 (PowerPoint 499kB Sep20 07)
This module is a PowerPoint presentation with embedded spreadsheets. Students work though the presentation, answering questions and developing the spreadsheet. If the embedded spreadsheets are not visible, save the PowerPoint file to a disk and open it from there.

This PowerPoint file is the student version of the module. An instructor version is available by request. The instructor version includes the completed spreadsheet. Send your request to Len Vacher (vacher@usf.edu) by filling out and submitting the Instructor Module Request Form.

Teaching Notes and Tips

Review linear equations and linear inequalities prior this module if students are not comfortable with the topics.

Assessment

Students will be asked to build a slightly larger dietary model, solve it with Excel, and interpret the results.
Students will be asked to answer several what-if questions.

References and Resources