# Business Management 2321 Case Study #3 – Version f due Thursday, April 17th, at the beginning of Recitation

 Page 1/2 Date conversion 17.11.2017 Size 163.64 Kb.
1   2

Case Study #3 – Version F

Due Thursday, April 17th, at the beginning of Recitation

Linear Programming Formulations, Running Solver, and Interpreting Results

Attached are five linear program formulations similar to those done in class. Input the formulations into EXCEL and use Solver to determine the optimal answer.

2. There is a worksheet for each of the problems.

a. The first worksheet contains a template for the Portfolio Analysis problem with the decision variables defined as column labels and the constraints defined as row labels.

• Input the coefficients for the decision variables in the objective function and constraints.

• Input the equations for the cells in the LHS column and the constants in the cells in the RHS column.

• Run SOLVER by identifying the target cell, the changing cells, and the constraints and save the optimal solution.

b. The worksheets for the Advertising problem, the Staffing problem, the Diet problem, and the Production Mix problem are blank.

• Descriptively name the decision variables and constraints with labels with meaning to the problem (for example, don’t use X1, X2, etc. for the decision variables).

• Input the coefficients for the decision variables in the objective function and constraints.

• Input the equations for the cells in the LHS column and the constants in the cells in the RHS column.

• Run SOLVER by identifying the target cell, the changing cells, and the constraints and save the optimal solution.

Case Study Requirements:

1. Cover sheet from this document with all of the team member names (TYPED!) and signatures. (1 page portrait)

2. Summary sheet with the answers to the 6 linear programs. (1 page portrait)

c. EXCEL worksheet for each problem (6 pages landscape)

• 0.5” page margins on the top, bottom, and sides

• Output centered vertically and horizontally. Do not increase the size of the output over 100% in an effort to fill the entire page!

• Solution clearly identified in the spreadsheet

• 0.5” space between the title box and the solution.

d. Up to 25 percentage points will be deducted for “non-professional” reports.

e. Points will be deducted for excess output (there should be 9 pages total, DO NOT PRINT 2-SIDED).

1. Scheduling Aircraft

Brutus Etu is employed by Buckeye Airlines and has been given the following schedule of flights among the four cities it serves (“A”, “B”, “C”, and “D”). The numbers of passengers on each flight are in the columns with the heading “#”.

 City “A” to City “B” City “A” to City “C” City “A” to City “D” Flight Departs Arrives # Flight Departs Arrives # Flight Departs Arrives # AB1 6:00am 12:30pm 100 AC1 7:00am 11:30am 94 AD1 8:00am 1:30pm 125 AB2 1:00pm 7:30pm 82 AC2 12:00pm 4:30pm 103 AD2 12:00pm 5:30pm 112 City “B” to City “A” City “B” to City “C” City “B” to City “D” Flight Departs Arrives # Flight Departs Arrives # Flight Departs Arrives # BA1 8:00am 2:30pm 80 BC1 9:00am 1:30pm 93 BD1 7:00am 1:30pm 91 BA2 3:00pm 9:30pm 75 BC2 2:00pm 6:30pm 74 BD2 12:00pm 6:30pm 56 City “C” to City “A” City “C” to City “B” City “C” to City “D” Flight Departs Arrives # Flight Departs Arrives # Flight Departs Arrives # CA1 9:00am 1:30pm 149 CB1 6:00am 10:30am 83 CD1 7:00am 12:30pm 113 CA2 2:00pm 6:30pm 106 CB2 3:00pm 7:30pm 96 CD2 1:00pm 6:30pm 125 City “D” to City “A” City “D” to City “B” City “D” to City “C” Flight Departs Arrives # Flight Departs Arrives # Flight Departs Arrives # DA1 7:00am 12:30pm 120 DB1 8:00am 2:30pm 135 DC1 6:00am 11:30am 107 DA2 2:00pm 7:30pm 79 DB2 12:00pm 6:30pm 98 DC2 3:00pm 8:30pm 51
1   2