Categories: excel, modelling, SIA, spreadsheet

Optimizing Passenger-Cargo Fleet Allocation

By: Ang Su Yiin, Gabriella Pauline Djojosaputro, Gordy Adiprasetyo, Kendra Luisa Baylon Gadong, Nguyen Nhi Thai An, Ulysses Chong Min Zhen

This was written as part of the requirement for the Spreadsheet Modeling module for MITB.

For executive report, please view: Executive Report

1. Problem Statement

With the halt of international travel due to COVID-19, the global aviation industry is one of the industries that was hit the worst. Border controls and travel restrictions were put in place, and the demand for air travel continued to be severely impacted. In Singapore, the airline operations are at standstill given all flights are international. Since passenger aircrafts sit idly due to the pandemic, airlines started operating those aircrafts to cargo-carrying ones as an alternative revenue stream. Passenger airlines said in a survey that 43% of them will keep their passenger-cargo ratio, 37% said airlines will place a greater emphasis on cargo, and 19% said airlines will make a permanent shift to cargo.

With that, this study aims to optimize the aircraft allocation of airlines’ fleets according to the post COVID-19 aviation industry environment through data modelling. Given a certain size of aircraft fleet, the study aims to help airline companies allocate how many aircrafts should be used for passengers, cargo, or how many should be put to storage. Additionally, a price point where an aircraft should be scrapped rather than put in storage will also be estimated. In this project, the focus is on Singapore Airlines (SIA) operations.

2. Data sources

Data is obtained from a variety of sources covering financial and operational aspects of SIA:
• Financial Reports: from SIA website
• Fleet Information: from mainlymiles, centreforaviation, and planespotters
• Covid-19 daily new cases per capita: from https://ourworldindata.org/covid-cases
• Historical Singapore Visitor Arrivals by Country: from https://www.ceicdata.com/en

3. Influence diagram and Black Box model

3.1. Black Box Model

Figure 1

Figure 2

Figure 3

Figure 4

4. Computation and Analysis

The spreadsheet model is built for the purpose of making the following computations and analyses:
• Each year from 2020 until 2025, compute optimum allocation of aircrafts by respective operation unit to maximize profits
• Compute scrap price for stored aircrafts
Each sub-component as outlined in the influence diagrams is explained in further detail below.

4.1 Forecasting COVID recovery rate by country from 2021 to 2025

Based on the current number of COVID cases per capita, each country is assigned a risk profile based on a traffic light system adapted from the European Center for Disease Prevention and Control (ECDC). Each country is assigned a traffic light (Green, Yellow, Red) based on the rate of new infections per 100,000 inhabitants in the past 14 days:

  • Green is for countries reporting less than 1 new infection per 100,000 inhabitants.
  • Yellow is for countries reporting greater than 1 new infection per 100,000 inhabitants and trend of daily new cases is going down.
  • Red is for countries reporting greater than 25 new infections per 100,000 inhabitants or countries reporting greater than 1 new infection per 100,000 inhabitants and trend of daily new cases is going up.

There are two sets of opening dates: partial (business travel only) and full (365 days after vaccine is available). Each country in the green and yellow category will be assigned two opening dates based on the traffic light profile. Countries in the red category will only have one reopening date – fully reopening date.

Figure 5

For Yellow bucket countries, daily new cases are projected using a curve-fitting approach with a Log-Normal distribution to meet the asymmetric feature of the distribution of daily new cases of COVID-19. The formula for the Log-Normal Distribution (Fig. 6) and Yellow bucket country Maldives curve-fitting example is illustrated in Fig. 7 and 8.

Figure 6

Figure 7

Using Solver, the curve-fitting approach minimizes the error from the fitted value and the observed daily COVID case per million by changing the parameters peak height a, peak position b, and width c. (Fig. 7). Then, an estimated partial reopening date for a yellow bucket country is forecasted when the daily new case per million is down to one case. For Maldives, the partial reopening date is 1 January 2021. This approach is done for all yellow bucket countries.

Figure 8

After assigning each country with a traffic light profile and estimated reopening dates, recovery rates will be based on the country’s opening status. SIA recovery rate is currently at 1.5% based on current rate of passenger volume in Changi Airport. Partial reopening dates are matched with a 16.5% recovery rate based on Singapore Airlines optimistic forecast on returning to approximately 15% of its passenger capacity from pre-COVID levels.

This model assumes that SIA recovery is progressive for all countries, which means that full recovery rate at 100% is expected in December 2025. To summarize the recovery rate calculation, recovery rate per country over time (in months) is forecasted linearly from 1.5% from today to partial reopening date and to 16.5% from the fully open date based on vaccine and to 100% by end of 2025.

Figure 9

For each country, forecasted recovery rate by end of year is calculated by taking the average of recovery rates for that particular year. Historical data of visitor arrivals from SIA destination countries are extracted to then forecast number of visitors per year based on recovery rates (Fig. 10).

Figure 10

Overall forecasted recovery rate and recovery rates per traffic light profile are summarized in Table 2 with visual representation in Fig. 11.
Figure 11

Figure 12

4.2 Forecasting cargo demand

Cargo demand is assumed to remain constant for the period of 2021 to 2025 and will be the same as 2019 demand. This assumption is reasonable because as more passenger aircrafts are grounded during COVID-19 and its recovery period, cargo transportation can mostly be done only by cargo aircrafts, therefore the demand for cargo services of SIA will not drop. Conservative assumption of constant instead of increasing demand is also adopted to simplify analysis, since obtaining accurate approximation of increase in demand requires in-depth studies on the cargo transportation market for the next 5 years.

4.3 Forecasting costs for passenger service, cargo service and storage

For passenger and cargo services, the following steps are taken to calculate their per aircraft costs in 2019:

  1. Total expenses for passenger and cargo services are retrieved from SIA Financial Reports FY19. (FRFY19)
  2. Number of aircrafts for 2019 are retrieved.
  3. Costs per aircraft for passenger and cargo services are obtained by dividing total expenses by number of aircrafts in 2019.

For storage:

  1. SIA’s idle aircrafts are parked at Alice Spring, Australia. Parking charges are calculated per Maximum Takeoff Weight (MTOW) of the aircraft.
  2. An average tonnage for all aircraft is obtained.
  3. Storage cost per aircraft is obtained by multiplying (1) and (2)

Total expenses are broken down into fuel and non-fuel costs for sensitivity analysis purposes. This breakdown is available in FRFY19 for passenger service but not cargo service. As such, the breakdown for cargo services is assumed to be the same as passenger service. Costs are assumed to be the same for non-fuel costs for years 2021 to 2025.

4.4 Compute the optimum allocation of aircrafts to maximize profits by respective operation unit

From FRFY19, passenger revenue and total passenger served are obtained, and from these the average ticket price per passenger is calculated. Yield for cargo services are obtained directly from FRFY19. Yield are assumed to remain constant for the years 2021 to 2025.

Another pivotal statistic that was retrieved from FRFY19 is Load Factor (LF), which measures capacity utilization of aircrafts. Several factors affect LF such as passenger distribution which is dependent on network routing. When the model allocates passengers and cargo into aircrafts for years 2021 to 2025, average LF will not exceed LF of year 2019 as we assume similar passenger distribution and flight routing.

Using this and previous calculations, a revenue maximizing model is developed and is solved using Solver.

Figure 13

Figure 14

GRG Non-linear solver method was first used but resulted in local optimum solutions. Given the complexity of the model and to be able to arrive at global optimum solution, Evolutionary solving method was chosen and used with the below specifications:
Figure 15

4.5 For stored aircrafts: compute scrap price – if offer price is above scrap price, the aircrafts should be sold instead of stored.

Scrap price is computed from the net present value of projected yearly cashflow for each aircraft. The cashflow for each year is determined by the number of useful years left and the number of years it is allocated to storage. The projected cashflow assumes that when there is a need to put an aircraft into operation, the newest aircraft will be deployed first.

It has two possible values:

  1. Storage cost: Computed using Alice Spring’s storage fee based on the aircraft’s MTOW
  2. Expected yearly profit if not stored: Obtained from the base model

Figure 16

The last year of useful life’s cashflow is the sum of expected book value at retirement and storage cost or expected profit, depending on the storage allocation. The expected book value is calculated from the purchase cost minus the depreciation along the course of its useful life. The depreciation value is estimated from SIA’s financial report.

5. Results

Overall, SIA’s profit is highly dependent on passenger demand recovery rate.
Figure 17

Given that all countries are only forecasted to reopen in Dec-2022, a large proportion – 93% (125/133) of aircrafts are allocation to storage initially, and gradually allocation to passenger services as passenger demand recovers.
Figure 18

6. Trade-offs and sensitivity

6.1 Sensitivity of fuel cost on total profit

Figure 19

Total profit is more sensitive to changes in fuel cost in the later years due to more aircrafts being in service. Based on figure 16, a 2.5% increase in fuel cost would result in losses, hence proper risk mitigation strategies, for example hedging fuel cost and dynamic seat pricing, must be in place to preserve profits.

6.2 Sensitivity of passenger demand recovery rate on total profit

Figure 20

The sensitivity analysis of passenger demand recovery reveals that total profit highly sensitive to the vaccine availability date.
Given that the vaccine release date is uncontrollable and uncertain at the date of this report, SIA should take the necessary steps to manage cash flow.

6.3 Trade off analysis

Figure 21

Profits would turn negative beyond a certain level of over allocation to passenger services due to supply outstripping demand for seats. As demand is the limiting factor, every additional plane allocated reduces profit. That being said, SIA may decide to allocate more aircrafts to passenger services to increase route offering for other business or technical reasons.

7. Model limitations and assumptions

Operating Costs

  • Model does not account for any redundancies and pay cut as projected cost is based on 2019 cost.
  • Model does not account for any changes in flight operating cost (e.g. different number of flights per aircraft per year) as it is based on 2019 cost.

Aircrafts

  • SIA retires its plane at 25 years old.
  • All aircraft purchases are put on pause, and no plans of acquisition of smaller airlines or new subsidiaries.
  • Model does not account for any sale of planes.
  • Almost all aircraft types operated by SIA are similar enough to be counted homogeneously in modeling output in terms of routing and allocation. All aircraft models, less the A380 or Boeing 747, are twin-engine-wide-bodied aircraft with similar range and passenger carrying capacity. This is aligned with the industry shift to use smaller twin-engine-wide-bodied aircrafts to service routes instead of larger jets with twice the capacity (such as A380 or Boeing 747) due to increase demand in non-hub airports.

Passenger Recovery Rate

  • SIA recovery rate will be based on the 31 countries where airline has operating flights. Country’s future performance in COVID recovery projected is based on number of cases per capita as of 26 Oct 2020 and projected vaccine availability.
  • Vaccine distribution timeline to reach herd immunity is assumed to be 1 year.
  • COVID-19 recovery rate per country assumed to be progressive – full recovery by end of year 2025.

Cargo Demand

  • Model does not account for any changes in cargo as it is based on 2019 demand.

Background photo created by onlyyouqj at freepik