Solving with Excel Solver

Oriental Airlines is considering a capital expansion in which it will purchase new aircraft for its Pacific runs. Oriental is looking at the purchase of Boeing B797s, Airbus A450s and Lockheed L120s. The budget for new purchases is $750 million. Boeing B797s cost $42 million, Airbus A450s cost $30 million, and Lockheed L120s cost $27.5 million. On the average these planes are expected to generate annual profits of $3.5 million, $2.8 million, and $3.0 million, respectively. In an effort to achieve some uniformity with respect to spare parts and maintenance procedures, airline executives have specified that they will not buy fewer than 10 airplanes of any type. Their objective is to maximize total annual profit.

Oriental has allocated up to $10 million for additional personnel to support the operation of the new aircraft. Each B797 requires $200,000 in new hires; each A 450 requires $180,000; and each L120 requires $190,000.

Currently the available maintenance facilities allow 800 days of maintenance per year for new purchases. Each B797 requires 45 days of annual maintenance; each A450 requires 38 days; and each L120 requires 42 days. It is possible, however to increase the available annual maintenance to 1,250 days. To accomplish this, the maintenance facilities would have to be expanded at a capital cost of $16 million, which would come out of the budget for new purchases. In addition, the expansion would augment operating costs by $18 million annually, an expense that would reduce annual profits.

a. What is the optimal purchasing plan, and what is the corresponding annual profit for Oriental?

HINTS:

(1) For each type of airplane, you need a binary variable (y) for purchase/no-purchase decision and a variable (x) for the actual purchase quantity. They must be linked by two constraints: x >= m*y (the lower threshold) and x <= M*y (the upper threshold). The lower limit m is given as 10. The upper limit M can be any number that is large enough or can be easily estimated from the problem -- for example, as each Boeing B797 cost $42 and the total purchase budget is $750, the airline company cannot purchase more than 750/42=18 B797s, so the number M can be replaced by 18 for B797. Although the problem only mentions the lower threshold, to link x and y correctly, we must add the upper threshold constraint as well. Why?

(2) To handle the expansion, you can add another binary variable. If it is 1, extra resources should be added to the existing resources and extra costs should be deducted from the existing costs; if it is 0, no extra should be added or deducted. Another way to handle the expansion is to build two separate models, one with the expansion and the other without, and compare their solutions directly. The first method is preferred, although you can still earn full credit if you choose the second.