Data Science Project: Excel-Based Diet Problem Optimization

This project focuses on solving the Diet Problem, a classic optimization challenge, using data science and Excel. The goal is to determine the most cost-effective combination of foods that meets specific daily nutritional requirements. The assignment requires building Excel spreadsheets to present food and nutrition data, as well as historical consumption information. A user interface is to be developed, including forms for food selection, data updates, and solution presentation. The solution form will display the optimal diet, allowing for user updates and sensitivity analysis. The project also includes designing a logo, creating reports detailing the solution and sensitivity analysis, and plotting daily calorie and cost data. The reference provided is Winston's 'Operations Research: Applications and Algorithms'. The project demonstrates practical application of linear programming and data analysis techniques to solve real-world problems.

Case Study 5 The Diet Problem
The Diet Problem
Problem Description
The goal of the diet problem is to find the cheapest combination of foods that will satisfy all
the daily nutritional requirements of a person. This problem has often been used in
Operations Research textbooks to illustrate the use of linear programming to solve real-life
types of problems. The corresponding linear programming minimizes the cost of food,
subject to nutritional requirements. Below, we give the linear programming formulation of
the problem. In this formulation we include constraints that regulate the number of calories
and the amount of vitamins, minerals, fats, sodium, and cholesterol in the diet.
Optimization Model
Let Food and Nutrition be sets of foods and nutrition we consider in this problem. Assume
that there are f different types of food in Food and n different types of Nutrition. The
following is a linear programming formulation of the problem:
where: is the cost of food j; is the amount of food j consumed; is the amount of
nutrition i in food j; is the minimum amount of nutrition i required per day; is the
maximum amount of nutrition i allowed to be consumed per day; is the minimum
amount of food j desired per day; is the maximum amount of food j desired per day.
Excel Spreadsheets
1. Build a spreadsheet that presents the following data about different types of food:
price, weight, calories, amount of cholesterol, and vitamin content per serving.
2. Build a spreadsheet that presents data about the minimum and maximum amounts of
nutrition i required per day for a healthy diet.
3. Build a spreadsheet that presents historical data about the food consumed by the
user on daily bases: total amount of calories consumed and total amount of nutrition
taken by nutrition type.
User Interface
1. Build a welcome form.
2. Build a form that allows the user to do the following:
5

Case Study 5 The Diet Problem
a. Select the type of food and the corresponding amount consumed. Here we
describe one way to complete this task: Insert two list boxes in this form. The first
list box presents a list of food items. The second list box, which is initially empty,
presents the following data: the names of food items selected by the user and the
corresponding minimum and maximum amounts desired per day. The user
selects the name of a particular food item from the first list box and then clicks on
a command button that adds the selected food in the second list box. The user
enters manually the minimum and maximum amounts desired for each food item.
b. Update the data presented in Spreadsheets 1 and 2.
c. Add data about the food consumed in a day to Spreadsheet 3.
d. Solve the problem and open Form 3, described below, which presents the
solution of this problem.
Use text boxes, list boxes, command buttons, subforms, etc. as needed.
3. Build a form that does the following:
a. Presents details about the final solution of this problem, such as the total price of
the diet, the amount of food j consumed (j =1,…,f), the total amount of calories
gained, and the total amount of nutrition taken per nutrition type. The user is
allowed to update this information (if the user actually consumes different food
types and quantities from what was proposed by the solution). Insert a command
button that, when clicked on, does the following: re-calculates the total price of
the diet, the amount of food consumed, the total amount of calories gained, etc;
and stores the information in Spreadsheet 3.
b. Enables the user to perform a sensitivity analysis with respect to changes in the
price of food.
Design a logo for this project. Insert this logo in the forms created above. Pick a
background color and a font color for the forms created. Include the following in the forms
created: record navigation command buttons, record operations command buttons, and
form operations command buttons as needed.
Reports
1. Build a report that presents details about the final solution to the diet problem.
2. Build a report that presents details about the sensitivity analysis.
3. Plot the following:
c. The total amount of calories consumed daily.
d. The total amount of money spent daily.
e. The total amount of a particular nutrition type (e.g., vitamin C) taken daily.
Reference
Winston, L.W., “Operations Research: Applications and Algorithms.” Duxbury Press, 3rd
Ed., 1994.