Brutus has been given the following assignments

a. Determine the minimum number of aircraft that Buckeye Airlines must lease to provide service on all of the 24 flights listed above.

b. Buckeye Airlines leases the minimum number of aircraft determined in part (a) but three of the aircraft are grounded for unscheduled maintenance. In what cities should be remaining aircraft be kept overnight and which flights should be cancelled to allow Wings Airlines to fly the maximum number of passengers?

c. Buckeye Airlines leases 12 aircraft. One of the maintenance managers quits his job after winning the lottery. Since Buckyey Airlines can only keep aircraft overnight at cities with maintenance capability, Buckeye will only be able to keep aircraft overnight in 3 of the 4 cities. Determine which 3 cities should have aircraft overnight and which flights should be cancelled to allow Buckeye Airlines to fly the maximum number of passengers?

(1) Download “EXCEL Model for Case Study #2a” from Carmen.

(2) Given the flight schedule above, determine the timeline of departures and arrivals at each city. Complete the “Airplane Timeline” worksheet.

(3) Determine the balance equations for the problem. Assume that there are A₀, aircraft on the ground overnight in city “A”. Use A₁, A₂, A₃, etc. for aircraft on the ground at subsequent times during the day (refer to the “Airplane Timetable” worksheet). Formulate a balance equation for each point on the timeline that a departure or an arrival occurs. Use the flight identification as the variable names.

Aircraft on ground + aircraft arriving − aircraft departing = aircraft on ground after “transaction”.

Aircraft on ground − aircraft on ground after “transaction” + aircraft arriving − aircraft departing = 0.

For example, at 7:00am in City A, the balance equation would be:

A₀ − A₁ = AB₁  A₀ - A₁ - AB₁ = 0

It is the student’s responsibility to determine the other 42 balance equations (there are a total of 43 balance equations.)

(4) The “Airplane LP “A”” worksheet contains a template with decision variables across the top and constraint labels down the left-hand side. The cells shaded black are for readability of the problem; no values should be entered in those cells. Input the decision variable coefficients, the objective function coefficients, and the RHS values into the yellow shaded cells. For ease of reading and troubleshooting, leave the cell blank if a coefficient is zero.

• 
  Balance constraints for each time that an aircraft either arrives or departs a city. The coefficients for the decision variables in these constraints are either “-1”, “0”, or “1”. The RHS values for these constraints should be entered as zeroes.
  
• 
  Constraints setting all flights equal to 1 (indicating that that flight will be scheduled).
  

(5) The “Airplane LP “B”” worksheet contains a template with decision variables across the top and constraint labels down the left-hand side. The cells shaded black are for readability of the problem; no values should be entered in those cells. Input the decision variable coefficients, the objective function coefficients, and the RHS values into the yellow shaded cells. For ease of reading and troubleshooting, leave the cell blank if a coefficient is zero.

• 
  Balance constraints for each time that an aircraft either arrives or departs a city. The coefficients for the decision variables in these constraints are either “-1”, “0”, or “1”. The RHS values for these constraints should be entered as zeroes.
  
• 
  A constraint setting the total number of aircraft equal to the value obtained in part (a) minus three. The total number of aircraft can be obtained by picking any time of the day and adding the aircraft on the ground in the three cities and the aircraft in the air at that time. It is easiest is to look at the total aircraft parked overnight at city “A”, city “B”, city “C”, and city “D” as no aircraft are in the air overnight.
  
• 
  A binary constraint for the decision variables for flights must be added in SOLVER to this formulation.
  

The Barvarian Motor Company (BMC) manufactures expensive luxury cars in Hamburg, Germany, and exports cars to sell in the United States. The exported cars are shipped from Hamburg to ports in Newark, NJ and Jacksonville, FL. From these ports the cars are transported by rail or truck to distributors located in Boston, MA, Columbus, OH, Atlanta, GA, Richmond, VA, and Mobile, AL. The network shown below shows the possible routes available to the company along with the transportation cost for shipping each car along the indicated path.

The Northwest Petroleum Company operates an oil field and refinery in Alaska. The crude obtained from the oil field is pumped through the network of pumping substations shown in the figure below to the company’s refinery located 500 miles from the oil field. The amount of oil that can flow through each of the pipelines, represented by the arcs in the network, varies due to differing pipe diameters. The numbers next to the arcs indicate the maximum amount of oil that can flow through the various pipelines (measured in thousands of barrels per hour). Northwest wants to determine the maximum flow from the oil field to the refinery. Formulate a solution for Northwest.

A small financial company is considering 12 projects (A through L) and it trying to determine which of the projects it should implement in an effort to attain 7 management goals. The ratings for each of the projects are given in the table below:

Goal 1 should be greater than or equal to 25

Goal 2 should be equal to 5000

Goal 3 should be less than or equal to 10

Goal 4 should be greater than or equal to 150

Goal 5 should be equal to 12

Goal 6 should be greater than or equal to 2500

Goal 7 should be less than or equal to 35

The number of projects should be equal to 4

1. Formulate the problem as a binary integer program with 12 decision variables and 8 constraints.

• 
  Download the EXCEL Spreadsheet “Case Study #4 from Carmen and open the worksheet “Hard Constraints”.
  

• 
  Enter the decision variable names (cells B3 through M3) in to the spreadsheet along with the problem constraint labels (cells A6 through A13).
  
• 
  Input the coefficients for the constraints, the sign [“≤”, “=”, “≥”] and the RHS value.
  

• 
  Add a constraint in Solver to limit the decision variables values for the projects (red cells) to “Binary”
  
• 
  Set all objective function values to zero.
  
• 
  Run SOLVER. The problem will be INFEASIBLE! Keep the Solver solution.
  

• 
  Download the EXCEL Spreadsheet “Case Study #4 from Carmen and open the worksheet “Soft Constraints”.
  
• 
  Enter the decision variable and deviation variable names (cells B3 through AC3) into the spreadsheet as well as the problem constraint labels (cells A6 through A13).
  

• 
  Modify the constraints from (1) by adding underachieving variables, subtract overachieving variables, and change the sign to “=” for each constraint.
  

• 
  Input the coefficients for the constraints, the sign [“=”] and the RHS value.
  
• 
  Add a constraint in Solver to limit the decision variable values for the projects (red cells) to “Binary”. Do not limit the deviation variable values to either “Integer” or “Binary”.
  
• 
  Use percent deviation in the objective function for the undesirable deviation variables. Input an equation into these cells; the format for these values should be 4 decimal places. Set the objective function values for the desirable deviation variables to zero. Set the objective function values for the projects to zero.
  

• 
  Run SOLVER. Keep the Solver solution. Complete the worksheet “Report” for the problem.
  

Cover sheet with all team member names.

“Airplane Timeline” worksheet detailine the airline schedule

“Airplane LP A” worksheet with LP completed and solved

“Airplane LP B” worksheet with LP completed and solved

“Transshipment” worksheet with LP completed and solved

“Maximum Flow” worksheet with LP competed and solved

“Hard Constraints” worksheet. The output should be printed on one page, landscape, with margins set to 0.25”.

“Soft Constraints” worksheet. The output should be printed on one page, landscape, with margins set to 0.25”.

All output must be centered horizontally and vertically on the pages.

• 
  This cover sheet must be filled in completely and signed by all members to be accepted
  
• 
  All team members must be listed on the cover sheet in alphabetical order.
  
• 
  All material must be stapled to the cover page.
  
• 
  Up to 25 points will be deducted for “unprofessional” work.