IT Assignment

Data Analysis(at least 200 words)

To show our data in a simpler and clearer way, the data from the data source are selected and

pre-processed. The total number of data in the air traffic statistics covering the monthly

movement of aircraft, passenger, and freight from 1998 to 2020 will not all be under

consideration. In this project, only figures starting from January 2017 till March 2020 will be

considered. Moreover, for the annual report, all unnecessary data which was not related to our

research and analysis are simplified or excluded in order to make the report more

understandable and relevant. For instance, the shares and dividends that the shareholders could

gain or lose. In addition to the use of formulas, tables, and graphic representations on

spreadsheets such as charts are used to transform our data into a summary.

Sheet 1:

On excel sheet 1, the table named MovementofAPF includes the air traffic statistics for aircraft,

passenger, and freight movements of the Hong Kong International Airport from January 2017

onward until March 2020. Meanwhile, the statistics from April 2020 onwards are not yet

announced and cannot be illustrated in the table. Therefore, the countblank function with the

formula (=COUNTBLANK(MovementofAPF[[Landing]:[Year-on-year ]] is applied at F56 so

as to count the empty cells only and represent the number of missing figures after March 2020.

The incomplete record after March will not be taken into consideration.

Sorting is also applied to the MovementofAPF table to arrange data in one or more columns

by ascending or descending order. It is useful to see the time sequence of years in ascending

order in order to compare the movement each year. Apart from sorting, filtering is also applied

to find desirable values quickly. In the table, filtering is useful to filter off the unwanted month

or year of data. However, filter function may not be as useful on the other items such as year-

on-year % change as there are too many distinct values.

Meanwhile, the abundance of landing and take-off of aircraft, arrival, and departure of

passengers, as well as the unloaded and loaded freight in each month, are all included in the

table. Besides, the sum of total aircraft, passenger, and freight movement monthly in Hong

Kong are all calculated respectively in column E from E3:E50, in column I from I3:I50 and in

column M from M3:M50. The formula (=SUM(MovementofAPF [@[Landing]:[Take-off]]))

is used in row E for aircraft. (=SUM(MovementofAPF [@[Arrival]:[Departure]])) is the

formula applied in row I for passenger and (=SUM(MovementofAPF

[@[Unloaded]:[Loaded]])) in column M representing the freight movement in the unit of

tonnes.

Several counting formulas are also used apart from countblank. This includes counta, count

and countif. First of all, counta is applied with the formula ( =COUNTA(B3:B41)) at F57 to

count the number of months taken into account as counta adds up the non-empty cell with both

alphabets & number characters. At F58, (=IFERROR(COUNT(MovementofAPF[Year]),0)) is

used to calculate the number of years considered. Besides, counting functions are also used in

the table under MovementofAPF table. Countif with the formula (=COUNTIF(F3:F14,">=0"))

is used in the table below to count the number of months with a positive year-on-year% change.

Apart from counting, functions such as average, median, maximum, and minimum are also

applied in sheet 1. Average formula (=AVERAGE(MovementofAPF [Landing])) is used at

C52 to calculate the average of all the movement and year-on-year % change from 2017 to

2020. ( =MEDIAN(MovementofAPF[Landing])), (=MAX(MovementofAPF[Landing])) as

well as (=MIN(MovementofAPF[Landing])) are implied to account for the median, maximum,

and minimum amount. The average, lowest, and the highest amount of the movements are also

calculated in the table below. However, they represent the amount within a year instead of the

period from 2017 to 2020. This is to compare the movement difference between the years in a

more effective way. If function is used in the last row of the table. (=IF(M65>J65,

"Increase","Decrease")) is used at M66 to indicate whether the total movement of aircraft,

passenger, and freight increase or decrease compared to the year before.

From I54, we can tell that the maximum monthly total of passenger movement is of 6817626.

At R31, vlookup function is used with the formula (=VLOOKUP(R30,B15:J26,8,FALSE)) in

order to find out the maximum total number for the passenger movement is in August 2018.

From the tables and calculations in sheet one, we can conclude that the surge of overall

movement is the greatest in 2017. As the year-on-year % change decreases to a smaller extent

or even towards a negative extent from 2017 to 2020, this implies the movement of aircraft,

passenger, and freight keep on shrinking in recent years. Although the total moving

performance of 2018 increases compared with that in 2017, the total movement of the three

continues to decrease in 2019 and early 2020. However, the total movement figure in 2020

should not be used to compare with those in other years as it only accounts for three-month

statistics. Instead, the average movement could be used to compare and found that movement

in 2018 is of the greatest among these years.

Sheet 2:

In sheet 2, pivot tables and charts are used to illustrate the sum of each movement, including

aircraft’s landing and take-off, passenger’s arrival and departure; and freight’s unload and load.

From the chart, it is obvious to see the movement of the passenger is the greatest followed by

0

2000000

4000000

6000000

8000000

10000000

12000000

14000000

Ja n

Fe b

M ar

A p

r

M ay Ju n

Ju l

A u

g

Se p

O ct

N o

v

D ec

Sum of Landing

Sum of Take-off

Sum of Arrival

Sum of Departure

Sum of Unloaded

Sum of Loaded

aircraft and freight. Besides, people usually go to travel abroad in January, followed by July.

People are probably having their Chinese holiday and summer holiday. Therefore, they do have

to go to school or work and choose to take the time to depart from Hong Kong and travel around

the world.

Sheet 3:

Scenario Summary:

Scenario manager is used in excel sheet 3 to show the sales forecasts from 2017 to 2020 and a

scenario summary is shown in the sheet named Scenario Summary by opening the scenario

manager and then clicked summary. Different assumption rate is implied in different scenarios.

There is a total of three scenarios created, including the worst case, the best case, and the most

likely case. By going back to Scenario manager and clicking at different scenarios, you can

find changes in assumption attribute in assumption percentage according to the scenario. From

different scenarios, the higher the operating expenses, the lower the profit. Particularly in the

worst case, the expense assumption is of the highest among all cases, and loss has resulted.

Meanwhile, higher sales will bring in more profit for the company. It is assumed that the

expenses will most likely increase due to inflation, especially for staff salaries and fuel costs.

The maintenance fee is also expected to increase due to the deterioration of assets such as

aircraft.

Sheet 4:

From sheet 4, we could predict that Cathay Pacific is not a major airline in Hong Kong, or that

there are many different airlines carrying passengers to or away from Hong Kong. The figures

for total passenger carried by Cathay pacific in 2018 and 2019 are retrieved from the annual

report 2019. To meet the goal of a total 10000000 passengers counting from 2018 to 2020, the

goal-seeking function is applied at B10 to find out the number of passengers carried by Cathay

pacific in 2020 in order to achieve the goal.

In conclusion, from the 4 sheets of data analysis, it is found that Cathay Pacific experiences a

fall in revenue, especially in recent years. The falling of revenue gained may be due to the

social unrest in Hong Kong recently. The anti-extradition law amendment bill movement

started in February 2019 and has lasted for more than a year. Rather than traveling around to

enjoy their holidays, especially in peak season that is summer holiday, people tend to go on

strikes and demonstrations to voice out their opinion and arouse people's political awareness.

Apart from the political movement, the outbreak of coronavirus has also rocked Hong Kong

for months. Even though Hongkongers are still able to go to other countries in the beginning,

citizens’ travel demand has dropped substantially. People tend to stay home to prevent infection

of Covid-19. Under such a challenging operational environment, it is predicted that the

movement of aircraft, passenger, and freight, as well as the revenue of Cathay Pacific, will

continue to fall in the coming months.

Image Editing

Original image

Edited image

For this photo, I have blurred the edges of the photo with black to bring more focus on the

ground crew and the aeroplane in the middle. The temperature is adjusted to be cooler with a

blue tone. Meanwhile, the saturation of colour also increases so as to stand out the crew’s

fluorescent vest and the yellow ground lines. On top of that, I have added a light at the top left

corner such that the ground crew may also seems to be using his hands to hide from the

sunshine while he was directing the way for landing.

Original image

Edited image

This is a photo capturing few people wearing a whole set of protective clothing and holding

disinfection sprays. I would imply sadness to the photo and therefore I have toned down the

photo to monochrome base. On top of that, I have added a grunge layer in order to make the

photo more hierarchical with a concrete-wall-like texture. Moreover, the contrast is heightened

to protrude the difference between the shade (left) and the light area (right).

Reflection

In this project, I have gained valuable experience during my attempt in utilizing all the

techniques learnt in the lab sessions of the semester. In the data analysis part, I have learnt how

to make use of different functions in Excel to draw wanted conclusion from a large amount of

data. By applying different formulas, the same set of data may indeed result differently. I have

found Excel useful and significant in data analysis to count and conclude from data easier.

Apart from formulas, I have also learnt to use tables and charts to express and correlate different

data. However, I found it challenging when all the formulas are putting together in the project.

I got to decide and use the right formulas in excel. In particular to functions taught in later

lessons such as scenario manager and pivot table, it is difficult for me to decide the different

cases and rows in the table. It is rather hard for me to handle such large amount of data. It is

not as easy as if I was doing my class exercises because guidelines are given and the question

are comparatively easier. Yet, I could only depend on myself in working this project.

Fortunately, I was able to find some useful websites and videos from the internet. Besides, I

read through all the lab manuals thoroughly in order to understand how to put down the correct

formula with the following correct steps. I tried my very best to include as much formulas as I

could.

Meanwhile, in the image editing section, I have learnt how to use different applications to

beautify the photos. I discovered many new functions and different tools to help me edit the

photos and make them more attractive. Besides, I have learnt to adjust the photos in different

areas such as exposure and brightness that will bring different effects to the photos. Apart from

lighting, it is also interesting to play around with the colour. I always try to adjust the photo

into the two extreme ends of functions like saturation and temperature to create and compare

different impressions implied. Although it may seems difficult for me to undo the steps made,

I really enjoy the process of photo editing as the photo can be transformed to a totally distinct

one after editing. This surprises me every time and I undoubtedly enjoy the moment when I

compare the two images. I have Since we can add many special effect by editing the photo. I

do not have much photo-editing experience before and therefore I browsed in the internet to

explore my inspiration and get to know more about the effects that I can add on a photo.