Supply Chain Modeling using Excel

e. Use Solver to find an optimal solution. Problem 3. (10 points) Northeastern Airlines is considering the purchase of new long-, medium-, and short-range jet passenger airplanes. The purchase price would be $201 million for each long-range plane, $150 million for each medium-range plane, and $105 million for each short-range plane. The board of directors has authorized a maximum commitment of $4.5 billion for these purchases. Regardless of which airplanes are purchased, air travel of all distances is expected to be sufficiently large that these planes would be utilized at essentially maximum capacity. It is estimated that the net annual profit (after capital recovery costs are subtracted) would be $12.6 million per long-range plane, $9 million per medium-range plane, and $6.9 million per short-range plane. SCM 478 - Supply Chain Modeling & Analysis It is predicted that enough trained pilots will be available to the company to crew 30 new airplanes. If only short-range planes were purchased, the maintenance facilities would be able to handle 40 new planes. However, each medium-range plane is equivalent to 1 short-range planes, and each long-range plane is equivalent to 1 short-range planes in terms of their use of the maintenance facilities. The information given here was obtained by a preliminary analysis of the problem. A more detailed analysis will be conducted subsequently. However, using the preceding data as a first approximation, management wishes to know how many planes of each type should be purchased to maximize profit. a. Formulate and solve a spreadsheet model for this problem. b. Formulate this model in algebraic form