Posted: November 14th, 2022

Module 12 Homework Prompt

Module 12 Homework Prompt

Introduction
This is an individual assignment; while you are permitted to ask the instructor or a classmate specific questions if you get stuck, you are to complete your own work.
This assignment has two parts. First, you must define an appropriate mathematical model, then you will develop a what-if model that will determine the optimal decision. To help you with your what-if model, this assignment comes with a template; no template is perfect, but this one follows my convention of input parameters in blue, decision variables in red, constraints are in simple black borders with relation symbols in between, and the objective function is double-bordered in black.
The information below will provide the context you need to set up the what-if model required for this assignment. The overall template has been provided for you, and all the input parameters have been entered into the template, so now you must complete the spreadsheet model by entering the formulas necessary to calculate what is missing and entering relation symbols (e.g. <=) between the constraints. Read the Problem Statement below to understand the problem setting, then follow the instructions to complete the assignment.
Remember that your spreadsheet should be built containing formulas to calculate all values using the input parameters – hard-coding is NOT permitted! Additionally, the template is NOT to be modified in any way; adding or subtracting cells will be penalized.
Once you are finished, upload your completed workbook (your submission should be one workbook with two worksheets) to the Module 12 Integer Linear Programming I Homework folder. If you wish to make changes to your submission, you may click on the title of the assignment to begin a new submission, but completing a new submission will overwrite the previous one. As you always should, make sure you save early and often!
Problem Statement
Suppose that you are throwing a pizza party for yourself and your closest friends, with a total of ten (10) people expected to attend. Further suppose that you are ordering large pizzas from your favorite deep-dish pizza chain, a large pizza can easily feed four adults, and that the only topping choices available are Cheese, Pepperoni, and Veggie. The goal here is to satisfy everyone’s preferences as best you can, without just buying everyone their own pizza of course. To this end, let’s assume you asked your friends to rate their opinion of each pizza topping on a scale of 1 to 10 where 10 is the best, and you have recorded this data on the Part 2 sheet of the Module 12 Template workbook.

Armed with this information, you now need to make two decisions: (i) how many of each kind of pizza should you order, and (ii) who should receive each topping? Given that each pizza serves up to 4 people, so you’ll need to order three pizzas to have enough for all ten (10) people, but which three? One of each? Three of one kind? Fortunately, we can wait for Solver to tell us!

Part 1

Before we start building the what-if model, let’s be sure we understand the problem itself; so, Part 1 tasks you with writing out the problem mathematically first. Follow the guidelines below to complete the has three text boxes on the Part 1 worksheet on the Module 12 Template, and for now, disregard any possible integer constraints you think we might need.

For the ease of typing the formulation into the text box, you can use Excel-speak e.g. SUMPRODUCT(x,d) in place of Summation/Product notation. You may also find that adding a brief verbal description is helpful; for example, rather than typing out the same constraint ten times (one for each guest), you can write it in general notation and describe which index (i or j) is being repeated. If that sounds more complicated than just spelling out each constraint, that is fine too.

Decision Variables

There are two main types of decision variables in this problem that coincide with the two decisions that you must make, so there’s a group of decision variables for the pizzas that you order and another group of decision variables for how you distribute the servings. Since there are only three ordering variables (one for each pizza type), you can get away with an abbreviation-style variable name, but the pizza distribution variable has a lot more variations to consider (as can be seen on the Part 2 worksheet), so I encourage you to use the general LP notation introduced in Chapter 11.

Objective Function

This is the simplest box in that it only requires one function. Given that the objective is to satisfy everyone’s preferences according to their ratings on a scale from 1-10, we can think of this as trying to maximize what I’m going to call the “preference score” for giving people the pizza they want. Don’t forget to include either MAX or MIN in the Objective Function!

Constraints:

There are three constraints you must be aware of: the number of pizzas ordered shouldn’t exceed three, everyone should receive exactly one serving of pizza (defined as ¼ of a pizza), and the number of servings distributed of each pizza cannot exceed the number of servings purchased (again assume 4 servings per pizza).

For each of these boxes, I’ve provided large text boxes for you to use, but you don’t need to use all the space, it’s just there if you need it. Once you have completed the three text boxes, proceed to Part 2.

Part 2

Part 2 is all about putting the theory of Part 1 into practice! Accordingly, it’s time to build the what-if model that corresponds to the mathematical model from Part 1; for a starting point, enter an initial plan of ordering one of each type of pizza, then distribute Cheese to the first three friends, Pepperoni to the next four, and Veggie to the last three. Your decision variables should look like this:

From there, fill out the rest of the what-if model. If you have set up the model correctly, this should be a feasible solution with a Preference Score of 70. From there, construct an optimization model in Solver to find the best combination of pizzas and serving distribution to make your guests as happy as possible! If you’ve done it correctly, the servings distributed to each guest will be integer values like in the initial solution above, but the Pizzas Ordered won’t be! Let’s fix that. But first, take a screenshot of your Solver dialog box and paste it onto the Part 2 worksheet. Then, right-click on the Part 2 worksheet tab and choose . Select (move to end) and check the box before clicking OK. Rename the new sheet Part 3.

Reminder: there is a bug in Excel for Mac that ignores the right-hand side of Solver constraints, so Mac users not get this solution even if their spreadsheet is correct. You will not be penalized for having the wrong solution if the spreadsheet and Solver have been completed correctly.
Part 3

Modify the new worksheet to include integer constraints where they are needed – for example, you can’t order a fractional pizza. Then, update your Solver model to reflect the new constraint(s) and rerun it. Take a second screenshot of Solver and replace the old screenshot with the new one.

Once all three Parts are completed, save and upload your completed Template to the Module 12 Integer Linear Programming I Homework folder.