Homework Assignment #4
Part 1: Timeshare classwork
Follow the video “Timeshare solution 2” and submit your spreadsheet solution to the timeshare problem with color code consideration when:
1. Only same color allowed
2. Downward exchange is allowed
Part 2: NLP classwork
Follow the videos and submit your spreadsheet solution to
1. Snow removal probelm
2. City tax problem
3. Snoey software problem
Part 3: Regression
Assume that a car sale price is a linear function of the car actual mileage. Given the data in the Excel spreadsheet “Robust Regression” (under Nonlinear Problems), estimate the sale price using the following model:
yaxb
Where x is the car mileage and y is the estimated car sale price. Find the regression line using
– OLS regression (minimum of sum of squared deviations)
– Robust regression (minimum of sum of absolute deviations)
Compare the results. Which method yields a better estimation? (To answer, use the graph provided to you on the last slide of “Soft constraints”).
Note:
Ordinary Least-Square (OLS) Linear regression is a non-linear optimization problem:
a0,a1
finding the optimal solution
Robust regression is also a non-linear optimization problem:
min abs(y1 (a0 a1x1)) abs(yn (a0 a1xn)) a0,a1
min(y(aax))2 (y(aax))2
1011 n01n
This formulation is quadratic in the decision variables, and solver does not have a problem
This formulation is more difficult for Solver to handle.
Please submit your spreadsheet solution.
Part 4: The optimal diet plan*
Based on a nutrition table, find the minimum-cost diet plan that contains total calories, fat, saturated fat, trans fat, cholesterol, sodium, carbohydrates, fibre, sugar, protein, vitamin A, vitamin C, calcium and iron that are within a given range. All the data is provided in the excel file Diet_file.xlsx. Specifically, the data includes a nutrition table, nutrition bounds, and price per serving. For example, a 60g serving size of donut costs $0.85, and contains 239Kcal calories, 11g fat, 3g saturate fat, 1g trans fat, 18mg cholesterol, 232mg sodium, 30g carbs, 1g fiber, 12g sugar, 4g protein, 13.8IU vitamin A, 0.7mg vitamin C, 27.6mg calcium, and 2.2mg iron. An optimal diet plan should consist of at least 1900Kcal calories and not more than 2200Kcal, maximum 300mg of cholesterol (there is no minimum requirement), minimum 300mg vitamin C (there is no maximum requirement), and so on.
1. What is the objective? Do you minimize or maximize the objective?
2. What are the decision variables?
3. What are the constraints?
4. Using the model described in questions 1-3, find the optimal diet plan based on the data
provided in the excel file Data_file.xlsx.
5. What is the solution to the problem? Give the values of your solution and explain in words what
is their meaning.
6. According to your solution, give two examples of binding constraints.
7. What is the reduced cost for Chili? Why did we receive this value?
8. What is the shadow price of the minimum total calories constraint? What does it mean? Give an
explanation in words.
9. What is the shadow price of the maximum total calories constraint? What does it mean? Give an
explanation in words.
10. Use the function IF to create a list of all the food items that are excluded from your diet.
11. Use the functions INDEX and MATCH to find the food product that contains 22mg of sodium. As
an answer, point to a cell that contains the calculation (a written answer that do not contain the
explicit formula will not be accepted).
12. Use the functions Max, Index and Match to find the food with the highest nutrition value in each
nutrition category (calories, fat, saturated fat, trans fat, cholesterol, sodium, carbohydrates, fibre, sugar, protein, vitamin A, vitamin C, calcium and iron).
Please submit your spreadsheet solution and written answers to the questions above.
*This question was given as an exam question in 2019.