AVIA2902 Airline Financial Analysis and Decision Support Individual Assignment 1 | UNSW

AVIA2902 Individual Assignment 1 (25 Marks and 25% of your assessment)

An Excel Spreadsheet in the Assignment section of  Moodle entitled   “AVIA   2902  AssignmentData.xlsx” includes information about Norwegian Air Shuttle fuel costs, inclusive of hedge gains and losses, denominated in  Norwegian Krone  (NOK), Norwegian Air Shuttle ASKs and Norwegian Air Shuttle fuel consumption in metric tonnes over the quarterly time period Mar-13 to Dec-23. You are to use the data in this spreadsheet to answer the following questions.

Question 1

Construct a calendar annual line graph in Microsoft Excel of Norwegian fuel costs per barrel in US dollars between 2013 and 2023. Note, to do this, you will need to research how to convert metric tonnes of jet fuel into barrels of jet fuel and clearly state this conversion in your answer, including a reference to how you obtained your answer. You will also need to find data on the Norwegian Kroner exchange rate versus the US dollar from an appropriate source. You will need to clearly state this source in your answer.

Ensure that your axes are properly labelled and that the graph you construct is of high quality. Build the graph in Microsoft Excel and copy the graph into a Word document. Marks will be allocated to thequality of the graph you construct. (3 Marks)

Question 2

Construct a calendar annual line graph in Microsoft Excel of Norwegian Air Shuttle fuel cost per ASK in US cents between 2013 and 2023. 

Ensure that your axes are properly labelled and that the graph you construct is of high quality. Build the graph in Microsoft Excel and copy the graph into a Word document. Marks will be allocated to the quality of the graph you construct. (1 Mark)

Question 3

Use the data you constructed in Question 1 on Norwegian Air Shuttle fuel cost per barrel in US dollars between 2013 and 2023 to find the compound annual growth rate of Norwegian Air Shuttle fuel costper barrel in US dollars between 2013 and 2023 using the regression-slope method. Compare this to the estimated compound annual growth rate using the end-point method. Discuss the reasons why the estimates are different, and which is preferred. (4 Marks)

Question 4

Use your estimates of Question 1 to draw a line graph of Norwegian fuel cost per barrel in US dollars against the U.S. Gulf Coast spot price of jet fuel per barrel over the calendar annual period 2013 to 2023. Both line graphs are to be drawn in the same graph with years on the horizontal axis.

Build the graph in Microsoft Excel and copy the graph into a Word document. Marks will be allocated to the quality of the graph you construct. To complete this task, you will need to find data on the U.S.Gulf Coast jet fuel price over the relevant time period. Clearly state the source of the data you collect in your answer. Discuss why the Norwegian Air Shuttle fuel cost per barrel in US dollars may be greater than or less than the Gulf Coast jet fuel price. (3 Marks)

Question 5

Find the correlation between the Norwegian Air Shuttle fuel cost per barrel and the Gulf Coast jet fuel price, both in the current time period for the Gulf Coast price, and the one-period lagged value of the Gulf Coast price. The correlation will need to be estimated over the time period 2013 to 2023. To find the estimates of the correlations, you will need to study and use the function in  Microsoft  Excel“=correl(range 1, range 2)”. Discuss and explain the differences in the answers you obtain. (5 Marks)

Question 6

In the case of your question 2, estimate of fuel cost per ASK in US c/ASK, what type of factors wouldyou expect to be causing this metric to change over time? (5 Marks)

Question 7

Discuss in a single paragraph of no more than 100 words why COVID-19 raised the likelihood that airlines would make significant hedge losses if they used financial instruments to hedge against the movement in jet fuel prices in 2020 and 2021. (4 Marks)