Qantas Financial Analysis Report

This assignment presents a detailed analysis of Qantas' financial performance based on their provided financial statements. The report focuses on key aspects such as current assets, liquidity position (indicated by cash and cash equivalents), and profitability. The analysis utilizes charts and figures to visualize trends in these areas and draws conclusions about the company's financial health and potential for investment.

Running head: FINANCIAL ANALYSIS
Subject Code: ACC 566
Subject: Financial Analysis
Name of the Student:
Assignment Task Number 2
Number of Questions Attempted: 13
Number of Question Not Attempted: 0

FINANCIAL ANALYSIS
Table of Contents
Question 1..................................................................................................................................2
Question 2..................................................................................................................................2
Question 3..................................................................................................................................3
Question 4..................................................................................................................................3
Question 5..................................................................................................................................4
Question 6..................................................................................................................................5
Question 7..................................................................................................................................6
Question 8..................................................................................................................................9
Question 9................................................................................................................................13
Question 10..............................................................................................................................14
Question 11..............................................................................................................................14
Question 12..............................................................................................................................15
Question 13..............................................................................................................................15
Reference..................................................................................................................................19
NAME
STUDENT NO Page 1

FINANCIAL ANALYSIS
Question 1
One of the main reasons for replacing cell references with names is to describe what that
particular cell wants to display. Naming a particular cell allows the users to identify the cell
with what calculations it is associated with and the technique is used for the basic
convenience of the user. One instance of replacing cell references with names is briefly
illustrated as follows:
Particulars Amount (in $)
EBIT 100,000
Interest 10000
Tax 30000
EAT/PAT 60000
Figure 1: (Chart showing Calculation of EAT/PAT)
Source: (Author Made)
Question 2
In most cases any negative figures which arises in any calculations are shown in
brackets or is highlighted. The main reason for this is to ensure that the user of the excel sheet
is aware of the fact that the figure is negative whenever the user see the figures in brackets.
The main purpose of putting brackets or highlighting the figure is basic identification and
differentiation of the negative figures from the positive ones.
NAME
STUDENT NO Page 2

FINANCIAL ANALYSIS
Figure: (Excel Sheet showing negative figure)
Source: (Ahmed, 2015)
Question 3
The accountant main responsibility is effective calculation and presentation of financial data.
In an excel sheet, accountant normally inputs data in another area and for reporting purposes
another sheet or area is used. This is mainly done in order to keep the calculations and the
reporting part from getting mixed up and moreover for the sake of simplicity of the whole
reporting framework. If any of the calculations and reporting area are mixed up than mistakes
or miscalculations are bound to happen. A spreadsheet generated instance has been depicted
as follows:
Figure 3: (Separate areas of data entry and report in spreadsheet)
Source: (Bogale, 2013)
Question 4
IF function is one of the most popular functions which is used in excel. IF function
provide the users with an opportunity to make comparison among values in an excel
worksheet (Liengme, 2015). IF function a technique which helps to check whether a
condition is accomplished or not. The function returns a single value if it is true and another
value if it is false. An instance of spreadsheet generated IF function is depicted as follows:
NAME
STUDENT NO Page 3

FINANCIAL ANALYSIS
Figure 4: (Excel Sheet showing IF function)
Source: (Okeniyi & Okeniyi, 2012)
Question 5
Perpetual Inventory System Periodic Inventory System
This system traces every single movement of
inventory as and when they arise. The system
analyses the book records for keeping track of
inventories (Berlemann & Wesselhöft, 2014).
As per this system of inventory the recoding
method of inventory involves updating the
inventory records at periodic intervals. The
system analyses by using physical verification
of inventories (Qin et al. 2014).
In this system the inventory and cost of good
sold are continuously updated
Under the periodic inventory system, there is no
need for continuous updating however both
inventory and cost of good sold are updated at
the end of the year.
In the perpetual inventory system there is no
need for closing entries to be recorded.
In the periodic inventory system as the system
updates inventories and cost of good sold
therefore the system requires closing entries to
be recorded.
NAME
STUDENT NO Page 4

FINANCIAL ANALYSIS
Perpetual Inventory System
Date Particulars Debit Credit
Amount (in $) Amount (in $)
01-01-2017 Purchase Account……Dr 10,000
To Bank Account 10,000
05-01-2017 Bank Account……….Dr 15,000
To Sales Account 15,000
Periodic Inventory System
Date Particulars Debit Credit
Amount (in $) Amount (in $)
01-01-2017 Inventory Account…….Dr 4,000
To Bank Account 4,000
05-01-2017 Bank Account……Dr 3,900
To Sales Account 3,900
07-02-2017 Cost of Goods Sold Account……Dr 3,000
To Inventory Account 3,000
Question 6
Any individual who is starting off with accounting background will be definitely
using excel worksheets for the various calculations. Spreadsheet helps in keeping tracks of
various data such as accounts of small as well as large businesses. The owners of small
business are new to accounting and may not be able to maintain proper records, spreadsheet
helps offering easy solution to such a problem. Spreadsheet helps in commonly using the
analysis of money which is spent and income that has been received. Spreadsheet enables an
individual to split the amount of cash that business has received and received (Zhu, 2014).
Another advantage of using the spreadsheet is that business can link the worksheets together
and it is very helpful in generating cash flow of a company. However a disadvantage which
arises while using spreadsheets is that such spreadsheets can be manipulated easily and thus
there is an increased chance of occurrence of frauds.
NAME
STUDENT NO Page 5

FINANCIAL ANALYSIS
Question 7
Average Method
Date
Purchase Cost of Sales Balance Inventory
Unit
Cos
t
per
Uni
t (in
$)
Total
Amount
(in $) Unit
Cost per
Unit (in
$)
Total
Amoun
t (in $)
Uni
t
Cos
t
per
Uni
t (in
$)
Total
Amoun
t (in $)
01-
01-
2017 80 57 4,560
03-
01-
2017 10 65 650 80 57 4,560
10 65 650
90 58 5,210
05-
01-
2017 30 70 2,100 90 58 5,210
30 70 2,100
120 61 7,310
08-
01-
2017 70 72 5,040 120 61 7,310
70 72 5,040
190 65 12,350
31-
01-
2017 75 65
4,8
75 115 65 7,475
FIFO Method
Date
Purchase Cost of Goods Sold Balance Inventory
Unit
Cos
t
per
Uni
t (in
$)
Total
Amount
(in $) Unit
Cost per
Unit (in
$)
Total
Amoun
t (in $)
Uni
t
Cos
t
per
Uni
t (in
$)
Total
Amoun
t (in $)
01-
NAME
STUDENT NO Page 6

FINANCIAL ANALYSIS
01-
2017 80 57 4,560
03-
01-
2017 10 65 650 80 57 4,560
10 65 650
05-
01-
2017 30 70 2,100 80 57 4,560
10 65 650
30 70 2,100
08-
01-
2017 70 72 5,040 80 57 4,560
10 65 650
30 70 2,100
70 72 5,040
31-
10-
2016 75 57
4,2
75 5 57 285
10 65 650
30 70 2,100
70 72 5,040
31-
10-
2016 75
4,2
75 115 8,075
LIFO Method
Date
Purchase Cost of Goods Sold Balance Inventory
Unit
Cos
t
per
Uni
t (in
$)
Total
Amount
(in $) Unit
Cost per
Unit (in
$)
Total
Amoun
t (in $)
Uni
t
Cos
t
per
Uni
t (in
$)
Total
Amoun
t (in $)
01-
01- 80 57 4,560
NAME
STUDENT NO Page 7

FINANCIAL ANALYSIS
2017
03-
01-
2017 10 65 650 80 57 4,560
10 65 650
05-
01-
2017 30 70 2,100 80 57 4,560
10 65 650
30 70 2,100
08-
01-
2017 70 72 5,040 80 57 4,560
10 65 650
30 70 2,100
70 72 5,040
31-
10-
2016 120 70
8,4
00 80 57 4,560
70 72
5,0
40 10 65 650
(90) 70 (6,300)
- 72 -
Question 8
Normal View
Bank Reconciliation
31-07-2017
Account Description General Cash Prepared
By
GL Account Number Reviewed
By
NAME
STUDENT NO Page 8

FINANCIAL ANALYSIS
Bank Account Number
Balance Per Bank Statement 24,009.00
Add: EFT Rent Received 700
EFT Insurnace payment 400
Deposits in Transit 4567.00
NFS Cheque from customers 2300.00
Bank Service charges 78.00
Note Receivables 2345.00
9,690.00
Subtract:
Checks Issued Not Cleared 1789.00
Checks Issued Not Cleared 650.00
Checks Issued Not Cleared 320.00
(2,759.00)
Other:
A
Book error cheque 78.00
78.00
Balance Per Bank Ledger 31,018.00
Bank Reconciliation
31-07-2017
Account Description General Cash Prepare
d By
GL Account
Number
Reviewe
d By
Bank Account
Number
Balance Per Bank
Statement 25,000.00
Add: EFT Rent Received 750
EFT Insurnace 500
NAME
STUDENT NO Page 9

FINANCIAL ANALYSIS
payment
Deposits in Transit 6587.00
NFS Cheque from
customers
2500.00
Bank Service
charges
90.00
Note Receivables 3545.00
13,222.00
Subtract:
Checks Issued Not
Cleared
2100.00
Checks Issued Not
Cleared
700.00
Checks Issued Not
Cleared
300.00
(3,100.00)
Other:
B
Book error cheque 95.00
95.00
Balance Per Bank
Ledger 35,217.00
NAME
STUDENT NO Page 10

FINANCIAL ANALYSIS
Bank Reconciliation Statement Formula View:
Bank Reconciliation
42947
Account
Description
General Cash Prepa
red
By
GL Account
Number
Revie
wed
By
Bank Account
Number
Balance Per
Bank
Statement
24009
Add: EFT Rent
Received
700
EFT Insurnace
payment
400
Deposits in
Transit
4567
NFS Cheque
from
customers
2300
Bank Service
charges
78
Note
Receivables
2345
=SUM(OFFSET(J19,-1,-
1,MATCH("Add:",E$1:E$104,0)-ROW()+1))
Subtract:
Checks Issued
Not Cleared
1789
Checks Issued
Not Cleared
650
Checks Issued
Not Cleared
320
=-SUM(OFFSET(J24,-1,-
1,MATCH("Subtract:",E$1:E$104,0)-ROW()
+1))
Other:
A
Book error
cheque
78
=SUM(OFFSET(J28,-1,-
1,MATCH("Other:",E$1:E$104,0)-ROW()+1))
NAME
STUDENT NO Page 11

FINANCIAL ANALYSIS
Balance Per
Bank Ledger
=SUM(OFFSET(J30,-1,,MATCH("Balance
Per Bank Statement",E$1:E$104,0)-ROW()))
Bank Reconciliation
31-7-2017
Account
Description
General Cash Prepa
red
By
GL Account
Number
Revie
wed
By
Bank Account
Number
Balance Per
Bank
Statement
25000
Add: EFT Rent
Received
750
EFT Insurnace
payment
500
Deposits in
Transit
6587
NFS Cheque
from
customers
2500
Bank Service
charges
90
Note
Receivables
3545
=SUM(OFFSET(R19,-1,-
1,MATCH("Add:",M$1:M$104,0)-ROW()+1))
Subtract:
Checks Issued
Not Cleared
2100
Checks Issued
Not Cleared
700
Checks Issued
Not Cleared
300
=-SUM(OFFSET(R24,-1,-
NAME
STUDENT NO Page 12

FINANCIAL ANALYSIS
1,MATCH("Subtract:",M$1:M$104,0)-ROW()
+1))
Other:
A
Book error
cheque
95
=SUM(OFFSET(R28,-1,-
1,MATCH("Other:",M$1:M$104,0)-ROW()+1))
Balance Per
Bank Ledger
=SUM(OFFSET(R30,-1,,MATCH("Balance
Per Bank Statement",M$1:M$104,0)-ROW()))
Question 9
Journal Entries
Date Particulars Amount
($)
Amount
($)
01-01-
2018
Accounts Receivables A/c …………….Dr 50000
To Sales A/c 50000
(Being goods sold in credit)
05-01-
2018
Cash A/c……………..Dr 50000
To Accounts Receivables 50000
(Being the amount owing from credit sales received)
10-01-
2018
Allowance for Doubtful Accounts A/c…………Dr 10000
To Accounts Receivable A/c 10000
(Being written off accounts receivables)
31-01-
2018
Accounts Receivable A/c……………………Dr 10000
To Allowance for Doubtful Accounts A/c 10000
(Being reinstatement of amount written off)
31-01-
2018
Cash A/c…………………………….Dr 10000
To Accounts Receivables A/c 10000
(Being the received of full amount owing)
NAME
STUDENT NO Page 13

FINANCIAL ANALYSIS
Question 10
There are two methods of estimating bad debts which are given below:
1. Direct write off Method: In this method the amount is directly written off from the
sales revenue when the amount of credit sales is deemed to be uncollectable.
DATE PARTICULARS L/
F
AMOUNT ($) AMOUNT ($)
31/1/201
8
Bad Debt Expense Account…….Dr 4000
To Accounts Receivable Account 4000
(Being writing-off of the debtor’s
account)
2. Allowance Method: In this method the amount which cannot be collected is recorded
as an estimated bad debt is recorded in the same year of sales. . The bad debts that are
written off is debited to the allowance account rather than debiting bad debt
expenditure expense account.
Question 11
In recent times the importance of online retailing in business has become crucial for
business. most of the business nowadays have online websites in order to access digital
markets. For example E-bay is a proper example of online retail shopping. The online
shopping websites has a variety of good on offer and this is widely used by business as well
as consumers for selling and purchasing respectively. The use of computers for accessing E-
bay websites has become essential because it helps in keeping records products that have
greater growth for potential.
Question 12
DATE DETAILS AMOUNT
(IN $)
01-01-
2017
Credit Sale to Alex 12,000
05-01-
2017
Receipt of Partial Payment 4,000
Promise received outstanding to 5/1/2017
08-01-
2017
Dishonour of past promise due to no payment
23-01-
2017
Receipt of Partial Payment 4,000
T-Accounts
NAME
STUDENT NO Page 14

FINANCIAL ANALYSIS
DEBIT ACCOUNTS RECEIVABLE ACCOUNT CREDIT
DATE Particulars Amount Date Particulars Amount
01-01-2017 Sales 12,000 01-01-2017 Cash 4,000
05-01-2017 Notes Receivable 8,000 05-01-2017 Notes Receivables 8,000
23-01-2017 Cash 4,000
Total 20,000 Total 16,000
DEBIT Notes Receivable
Account
Credit
DATE Particulars Amount Date Particulars
Amount
05-01-2017 Accounts
Receivable
4,000 05-01-
2017
Accounts Receivable -
Total 9,500 Total
9,500
Question 13
Balance scorecard can be defined as the metric of assessing the performance that is
used in strategic administration procedure for locating and improving the different forms of
integral functions along with the results obtained from the process. The management of
Qantas needs to introduce an effective balanced scorecard so that the investors of the
company are benefitted by such practices. Balanced scorecard introduction in the business
will greatly improve the financial performance of the company and help the company to
achieve the long term goals of the company.
NAME
STUDENT NO Page 15

FINANCIAL ANALYSIS
Figure 5: (Figure showing Remuneration of the director of Qantas)
Source: (Investor.qantas.com, 2018)
The figure shows that the directors of the company earn a basic pay and also a bonus. The
basic pay is more or less unchanged but the bonus has been reduced by the company. The
figure shows that the company has an incentive plan.
Current Assets: 2016 2017
Assets Classified as held for Sales 12 17
Inventory 351 336
Trade Receivables 784 795
Cash and Cash Equivalents 1,775 1,980
Other financial Assets 100 229
Other Assets 97 101
NAME
STUDENT NO Page 16

FINANCIAL ANALYSIS
Current Assets: Assets
Classified as
held for Sales
Inventory Trade
Receivables Cash and Cash
Equivalents Other financial
Assets
0
500
1000
1500
2000
2500
2016
12
351
784
1775
100
2017
17
336
795
1980
229
Current Assets
2016 2017
Figure 6: (Chart showing Non currents Assets of Qantas)
Source: (Author Made)
NAME
STUDENT NO Page 17

FINANCIAL ANALYSIS
0
500
1000
1500
2000
2500
3000
3500
4000
Line Diagram Showing Current Assets
2016 2017
Figure 7: ((Chart showing Pie Chart of Qantas)
Source: (Author Made)
As per the analysis performed above, the share of the majority of the current assets
has increased and therefore it is probable that the investors should invest in the company. The
company has significant amount of liquid cash in the current year which shows the liquidity
of the business, The organization’s financial statement shows better liquidity position of the
business (Investor.qantas.com, 2018). The result states that the company has maintain the
advantage over the local and the global competitors that has been underpinned by the
completion of the three year of transformation program. The international operations of the
firm have experienced higher degree of capacity growth and also witnessed an improvement
in the EBIT of $327 million.
NAME
STUDENT NO Page 18

FINANCIAL ANALYSIS
Reference
Berlemann, M., & Wesselhöft, J. E. (2014). Estimating aggregate capital stocks using the perpetual
inventory method. Review of economics, 65(1), 1-34.
Kaplan, R. S. (2012). The balanced scorecard: comments on balanced scorecard
commentaries. Journal of Accounting & Organizational Change, 8(4), 539-545.
Liengme, B. (2015). A Guide to Microsoft Excel 2013 for Scientists and Engineers. Academic Press.
Okeniyi, J. O., & Okeniyi, E. T. (2012). Implementation of Kolmogorov–Smirnov P-value
computation in Visual Basic®: implication for Microsoft Excel® library function. Journal of
Statistical Computation and Simulation, 82(12), 1727-1741.
Qantas | 2017 Annual Report. (2018). Investor.qantas.com. Retrieved 15 January 2018, from
http://investor.qantas.com/annual-report-2017/
Qin, L., Miao, L., Ruan, Q., & Zhang, Y. (2014). A local search method for periodic inventory
routing problem. Expert Systems with Applications, 41(2), 765-778.
Zhu, J. (2014). Quantitative models for performance evaluation and benchmarking: data
envelopment analysis with spreadsheets (Vol. 213). Springer.
NAME
STUDENT NO Page 19

FINANCIAL ANALYSIS
NAME
STUDENT NO Page 20