Susan Jetson is a transportation planner for Briton Paper Company. Ms. Jetson needs to assign truck carriers for shipments of finished paper to different destinations. Truckloads of paper need to be delivered from Briton's paper mill to each of 5 destinations labeled a through e. The data for the problem is given in Table 1. Two truckloads of paper must be delivered to location a, four truckloads to location b, etc. Briton uses four carriers and each carrier has a limited number of available trucks. The rate table in Table 2 indicates the amount that each carrier charges per truckload, in \$ per mile. If the resulting charge of the truckload is less than the carrier's minimum charge, the carrier must be paid at least the minimum charge for the delivery of that truckload. For example, if Ms. Jetson decides to use carrier 3 to ship one truckload to location a, the charge is $624. If she uses carrier 3 to ship one truckload to location e the charge is $500. (All trucks must leave this morning. Because of the travel time involved, a truck can ship to a single destination only.) The data for the minimum charges and the number of trucks available from each carrier appear in Table 3. Table 1. Destinations, Distances and Reauirements Table 2. Rate table (\$ per mile per truckload) Table 3. Carrier Information In the past Ms. Jetson has made carrier assignments by hand, relying on her experience as a transportation planner for the last several years. (a) Develop a spreadsheet model that Ms. Jetson could use to determine the cost-minimizing assignment of carriers to shipments. Define and give a brief explanation of the decision variables, objective function, and constraints. [10 pts] Hint: Let xi,j represent the number of truck from carrier i assigned for shipment to destination j. (b) Solve your model as a continuous linear program (that is, do not define any of the decision variables as integer) and generate the Solver Sensitivity Report. What is the optimal assignment of trucks to shipments? What is the corresponding cost? [10 pts] (c) Suppose that Ms. Jetson could negotiate with one carrier to provide an additional truck. An additional truck from which carrier (1,2,3, or 4) would provide the biggest reduction in cost? Use the Solver Sensitivity Report to answer the question - do not rerun the model. Explain your answer. [10 pts] (d) Ms. Jetson just received new information that 2 extra truckloads of paper are needed at destination a. Using the Solver Sensitivity Report, calculate the new optimal cost. Explain. [10 pts] (e) Carrier 2 has decided to change its rate agreement with Briton paper. Carrier 2 will now charge only $1.46 per mile for going to destination a. Using the Solver Sensitivity Report determine whether this new rate will change the optimal allocation? Explain why or why not. [10 pts] (f) Briton Paper has agreed to give carrier 2 at least 25% of its business each day (i.e., 25% of the total shipping cost). Modify the formulation from part (b) to accommodate this agreement. Specify the modification as a linear constraint and resolve the model including the changes from d) and e) above. What is the new allocation and new objective function? What additional requirement did you need to add to make the results realistic? [10 pts] Hint: Check if the new formulation results in an integer solution