ACCTN203 Assignment Brief

Individual Excel Exercise #1 

The assignment is worth 7.5% of the total paper mark. It is due on 10 August 2025, 9:00 PM. Late submissions will be penalised according to the guidelines specified in the paper outline.

Scenario

You work as a management accountant for an airline company. Your role includes performance evaluation and reporting on key performance indicators, and conducting ad-hoc analysis to support management decision-making. Your line manager (Levi Ackerman) has asked you to assess airline on-time performance and build a model to identify factors that influence arrival delays.

Data.xlsx includes information on the following variables, and each row represents a flight:

• ArrDelay – Difference between scheduled and actual arrival time in minutes. Early arrivals show negative numbers.
• DepDelay – Difference between scheduled and actual departure time in minutes. Early departures show negative numbers.
• TaxiOut – Taxi-out time in minutes (i.e., time from when the aircraft leaves the departure gate till it takes off).
• TaxiIn – Taxi-in time in minutes (i.e., time from when the aircraft lands until it arrives at the arrival gate).
• AirTime – Flight time in minutes.
• Distance – Distance between airports in miles.
• WeatherDelay – Extreme or hazardous weather conditions causing delays on arrival. Coded 1 for ‘Yes’ and 0 for ‘No’.
• LateAircraftDelay – Late arrival of the same aircraft at a previous airport, causing delays on arrival. Coded 1 for ‘Yes’ and 0 for ‘No’.

Complete Part 1 and Part 2 of the requirements directly on Data.xlsx. Complete Part 3 of the requirements on Email.docx. Submit both the Excel workbook and Word document to Moodle.

Requirement

Part 1 – Complete in the Part 1 sheet of the Excel workbook.

(a) Column C (Dep_Indicator) should show a departure delay indicator that returns 1 if the aircraft departure time is delayed by 15 minutes or more, and 0 otherwise. Column D (Arr_Indicator) should show an arrival delay indicator that returns 1 if the aircraft arrival time is delayed by 15 minutes or more, and 0 otherwise. Write Excel formulas in Cell C2 and Cell D2 to achieve this and auto-fill down the columns. (8 marks)

(b) Delays that are 15 minutes or more are considered major delays. Write Excel formulas in Cell G3 and Cell G4 to return the total number of departure delays and the total number of major arrival delays, respectively. (6 marks)

(c) Write Excel formulas in Cell H3 and Cell H4 to calculate the proportion of flights with major delays in departure and major delays in arrival, respectively. (6 marks)

Note: No hard-coding of values. Try using the COUNT() function as the denominator. Can try prompting GenAI tools for more guidance if needed.

(d) Write Excel formulas in Cell I3 and Cell I4 to calculate the average delay time for major departure delays and major arrival delays, respectively. (8 marks)
Note: Try the AVERAGEIF() function. Can try prompting GenAI tools for more guidance if needed.

Part 2 – Complete in the Part 2 sheet of the Excel workbook. Include all the output on this page and present your answers in an easy-to-follow manner.

(a) Create a scatter plot that shows the relation between ArrDelay and DepDelay. Format your graph as appropriate and describe what pattern you see in the graph created. (12 marks)

(b) Run correlation analysis on all variables. Use conditional formatting to highlight any moderate and strong relations. (15 marks)

(c) Using the data provided, form the most appropriate regression model that can be used to identify factors that influence arrival delay. (20 marks)

Part 3 – Complete in the Word document template

Write an email to Levi that provides an overview of your analysis findings, including insights on airline on-time performance and factors that influence arrival delay time. Briefly describe the goodness-of-fit and direction effect of variable(s) that are statistically significant in your final regression model. (25 marks)

Note: Maximum 250 words for the email with no minimum word count. Format and structure as a professional business email. Proofread thoroughly.