The MutiSales Company has an inventory of different items each of whose weight is given in column B of the data file project.xlsx posted in Canvas (this list can change over time). Every morning, the Distribution Manager gets a list indicating how many of each item must be loaded onto trucks for distribution that day. The company has 6 trucks but not all of those trucks are necessarily available that day. The managers list contains the weight capacities of the available trucks for that day (see, for example, column F).

The following is the data. Please help create a generalized Integer Programming model for the problem of shipping items 1, 3, 5, and 8. Minimizing the truck capacity used of the 4 available trucks. Subject to the weight of each item.

The following is the full question for reference.

1. Create, on paper a general optimization model, using appropriate symbols for both the data and the variables, to determine how to load the items to ship that day onto the least number of trucks possible. (Use only as many variables as are absolutely necessary and do not use numerical values for the data.) Turn in a Word document containing your complete model (see item (4) below) that includes an answer to the following two questions:

2. Using the given number of items in column C of the worksheet in the file project.xlsx that must be shipped out today, together with the weight capacities of the available trucks given in column F, manually create your model in (1) above in the EXCEL worksheet project.xlsx. (Note: Design your worksheet so that the Distribution Manager will eventually be able to solve your model on a daily basis.) (25 points)

3. Use Solver to obtain the optimal solution to your spreadsheet model in (2).

A B D E F 1 Item 140 2 3 4 B Weight Number to Ship Today 70 2 140 80 1 10 1 2 3 4 5 2 Truck Capacities 200 150 250 100 80 5 6 5 12 5 60 6 7 8 7 8 300 15 9 20 300 8 9 10 18 11 10 20 12 11 63 13 12 137 14 13 13 14 117 15 16 15 28 A B D E F 1 Item 140 2 3 4 B Weight Number to Ship Today 70 2 140 80 1 10 1 2 3 4 5 2 Truck Capacities 200 150 250 100 80 5 6 5 12 5 60 6 7 8 7 8 300 15 9 20 300 8 9 10 18 11 10 20 12 11 63 13 12 137 14 13 13 14 117 15 16 15 28