I need help with the following problems in document the Midterm Applied Business Modeling posted below. I have also attached my current progress (Midterm Exam BUSA 542) excel document so you can possibly see where I am going wrong.

Midterm Spring 2020 The due date for the Midterm is 3/22/2020 11:59 pm. It must be submitted through the \"Midterm\" in Assignment Submission Folder provided in D2L. It is opened-book, opened-note, opened-Internet, but not opened-friends or neighbor. It means that you have to do this exam by your own. You can use Internet to check concepts, but cannot ask anyone to do the exam for you. If the midterm is not done by you, you will get 0 for the midterm. Partial credit will be given even if your answers are incorrect. It is important that you specify your name in the assignment document/attachment. The attachment name should follow the following convention: BUSA542_MIDTERM_FIRSTNAME_LASTNAME.XLSX General Instruction: Whenever you are asked to create optimization model, you must save the model in a separate Excel tab. For those who use Solver-cloud platform, you should include your Solver Parameter screenshots just in case your model settings are not saved. You will automatically lose 50% points of the question, if the required optimization model can't be found. You will get a partial credit even you get incorrect answer, so try not to leave any question blank. 1. TAMUC manufacturers cars and SUVs wants to ship cars and SUVs globally. Presently, TAMUC has an inventory of 180 cars and 140 SUVs in Newark, NJ and 300 cars and 180 SUVs in Jacksonville, FL. These vehicles need to be transported in the cities summarized in the following table: City Boston Columbus Richmond Atlanta Mobile Cars 100 60 80 170 70 Vehicles Needed SUVs 75 40 55 95 50 TAMUC rents rail cars to move its inventory of vehicles between these cities. Each rail car can hold up to 13 vehicles. The cost of renting a rail car is summarized in Figure below: A. Formulate an integer linear programming model (10 points) B. Implement the model in Excel and solve in a separate tab. What is the optimal solution? (10 points) 2. TAMUC wants to send 930,089 mailing pieces to customers in 7 states: State AZ CA CT GA IL MA ME Total Mailing Pieces 82,380 212,954 63,796 136,562 296,479 99,070 38,848 930,089 To coordinate with other marketing efforts, all the mailings for a given state must go out in the same week (i.e., if TAMUC decides to schedule mailings for AZ in week 2, then all of the 82,380 pieces of mail for AZ must be sent that week). TAMUC would like to balance the work load in each week as much as possible and, in particular, would like to minimize the maximum amount of mail to be processed in any given week during the 6-week period. A. Formulate mathematical linear programming problem (10 points) B. Implement the model in Excel and solve in a separate tab. What is the optimal solution? (20 points) 3. TAMUC is in charge of loading cargo ships to Boston. One company wants to transport the following products aboard this ship. Commodity 1 2 3 4 Amount Available (tons) 4,000 2,000 1,200 1,700 Volume per Ton (cubic feet) 40 25 60 55 Profit per Ton ($) 70 50 60 80 The ship has three cargo holds with the following capacity restrictions: Cargo Hold Forward Center Rear Weight Capacity (tons) 3,000 6,000 4,000 Volume Capacity (cubic feet) 145,000 180,000 155,000 More than one type of commodity can be placed in the same cargo hold. However, because of balance considerations, the weight in the forward cargo hold must be within 10% of the weight in the rear cargo hold and the center cargo hold must be between 40% to 60% of the total weight on board. A. Formulate an linear programming model (5 points) B. Implement the model in Excel and solve in a separate tab. What is the optimal solution? (10 points) C. Is the solution degenerate? Is the solution unique? If not, find the alternative optimal solution (5 points). D. Create a Spider Plot showing the change in the total profit obtained by changing the profit per ton on each commodity from 95% to 105% in 1% increments. If the shipping company wanted to increase the price of transporting one of the commodities, which on would have the greatest influence on total profits? (10 points). 4. U.S. Express is an overnight package delivery company based in Atlanta, Georgia. Jet fuel is one of the largest operating costs incurred by the company, and the company wants your assistance in managing this cost. The price of jet fuel varies considerably at different airports around the country. As a result, it seems that it might be wise to "fill up" on jet fuel at airports where it is least expensive. However, the amount of fuel an airline burns depends, in part, on the weight of the plane, and excess fuel makes an airplane heavier and, therefore, less fuel-efficient. Similarly, more fuel is burned on flights from the East Cost to the West Coast (going against the jet stream) than from the West coast to the east coast (going with the jet stream). The following table summarizes the flight schedule or rotation flown nightly by one of the company's plans. For each flight segment, the flight summaries the minimum required and maximum allowable amount of fuel on board at takeoff and the cost of fuel at each point of departure. The final column provides a linear function relating fuel consumption to the amount of fuel on board at takeoff. For instance, if the plane leaves Atlanta for San Fran with 25,000 gallons on board, it should arrive in San Fran with approx. 25 - (3.2 + .45 x 25) = 10.55 thousand gallons of fuel. The company has many other planes that fly different schedules each night, so the potential cost savings from efficient fuel purchasing is quite significant. But before turning you loose on all of its flight schedules, the company wants you to create a spread sheet model to determine the most economical fuel purchasing plan for the previous schedule. (Hint: keep in mind that the most fuel you would purchase at any departure point is the maximum allowable fuel level for takeoff at the point. Also assume that whatever fuel is on board when the plane returns to Atlanta at the end of the rotation will still be on board when the plane leaves Atlanta for the next evening) Implement integer network modeling in Excel and solve in a separate tab (20 points) The following criteria will be used to grade the midterm: Optimization models are detailed, accurate, and written answers are correct. ***************************NOTE ************************************* Please feel free to consult your instructor by email or phone, if you have questions or need assistance!! LP model: Let RCi represent the number of rails car rentals needed to supply the demand to each location. Minimize: Total Cost = (1080*RC1) + (1020*RC2) + (990*R3) + (1110*RC4) + (1220*RC5) + (980*RC6) + (860*RC7) + (900*RC8) + (930*RC9) + (910*RC10) + (850*RC11) Supply Constraints: NJc1 180 NJs1 140 FLc1 300 FLc1 180 Demand Constraints: MAc1 = 100 MAs1 = 75 OHc1 = 60 OHs1 = 40 VAc1 = 80 VAs1 = 55 GAc1 = 170 GAs1 = 95 ALc1 = 70 ALs1 = 50 Rail Compacity Constraint: RC = 13 X12 X14 X23 X35 X53 X