Background

IF has a mixed fleet of aircrafts that it uses on its routes: $(1)$ turboprop propeller planes, $(2)$ narrow$-$body jet

engine planes, and $(3)$ wide$-$body jet engine planes.

Each type of plane has different resource requirements, as well seats to hold passengers. For example, the

turboprop planes are smaller and slower, resulting in less fuel being consumed each flight, but it can only carry

$74$ passengers. In contrast, the largest of planes, the wide$-$body jets, travel almost $70\%$ faster than turboprops

while in the air and carry $174$ passengers, but consume four times as much fuel per flight!

IF tracks the following items when analyzing the performance of a route: $(1)$ time for each flight $($which includes

time to take off, fly, and land$)$; $(2)$ the amount of fuel used during the flight; $(3)$ the number of personnel involved

in the flight $($including pilots, flight attendants $($FA$),$ security guards, and baggage handlers$)$; $(4)$ the maximum

number of passengers per flight; and $(5)$ the ticket price for the flight.

Given the resource requirements and revenue potential for each type of plane, along with constraints on specific

resources, your task is to determine the optimal mix of planes to cover the demand for a new route that IF's

management team is considering.

Tasks

In the $"$R$03\_$PLANE$\_$MIX" worksheet, enter formulas to calculate the following:

$1\mathrm{.}1.$ Total Revenue Per Flight, which is Passenger Per Flight multiplied by Average Ticket Price, for each

plane type.

$1\mathrm{.}2.$ Individual and Total Costs for each type of resource $($Pilots$,$ FAs, Security, Baggage, Fuel$)$ for a month.

Each of these will be a combination of $(1)$ the number of flights, multiplied by $(2)$ the amount of each

resource used per flight, then multiplied by $(3)$ the dollar cost rate per unit of each resource. Note that

the cost of a resource depends on its cost behaviour; for example, the cost of a pilot is based on how

many hours they have flown, while the cost of baggage handlers is based on how many of them were

needed for that flight.

$1\mathrm{.}3.$ Number of Passengers flown, the Total Revenue earned, and Gross Margin $($i$.$e$.$ Total Revenue less

Total Costs$),$ for each plane type and in total.

$1\mathrm{.}4.$ Number of Turboprop Pilot, Jet Pilot, and FA hours used in the month. Each of these are combination of

$(1)$ the number of each personnel type used on a given flight, multiplied by $(2)$ the number of hours

flown, with consideration to plane type.

Using the Solver Add$-$in provided by Microsoft, configure Solver to maximize Total Gross Margin by adjusting

the number of flights per plane type in a month, respecting the following constraints: $(1)$ there cannot be any

partial flights, and $(2)$ flight hours for pilots and FAs must not exceed the stated capacities.