Integer optimization with Excel solver
Budget: $10 – $30 USD
I have a question needs to be solve with Excel Solver.
Buying Equipment. 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
A450 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?
b. Suppose the maintenance expansion were to augment operating
cost to $24 million instead of $18 million. Then, what
would be the optimal purchasing plan?
c. Suppose that, in addition to the augmented operating cost of
$24 million, the uniformity policy required a minimum of only 8
airplanes of the same type rather than 10 as at present. What
would be the optimal purchasing plan under these assumptions?
Buying Equipment. 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
A450 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?
b. Suppose the maintenance expansion were to augment operating
cost to $24 million instead of $18 million. Then, what
would be the optimal purchasing plan?
c. Suppose that, in addition to the augmented operating cost of
$24 million, the uniformity policy required a minimum of only 8
airplanes of the same type rather than 10 as at present. What
would be the optimal purchasing plan under these assumptions?