Data analysis Flashcards
Data analysis Approach for data analysis
Being able to analyse and interpret data is a key tool for professional accountants.
This technique is tested in several ways, from analysing testing data for defects in
production to analysing ESG data to compare to targets (Chapter 9).
In the SBM exam you will need to demonstrate these skills, which translates to the
following approach for data analysis questions:
Choosing analytical tools that are appropriate in the context of the question, e.g.
financial ratios, or breakeven calculations
Carrying out the relevant calculations, usually up front in a data table
Interpreting the information to demonstrate an understanding of the story behind
the numbers and communicating that analysis succinctly
Analysing the wider consequences and implications of the data including
predicting future movements
Linking different pieces of data to discuss trends and cause and effect
relationships
Suggesting additional information that may be useful to improve the analysis
Using judgement to draw conclusions and/or produce sensible
recommendations.
Data analysis Financial statements analysis 2.1 Financial statements and operating data
Financial statements are often provided in the exam, alongside operating data (e.g.
number of shops, number of customers). Interpretation of the financial statements is
a common exam requirement and usually asks to explain:
Performance (revenue, profit margins, operating data); and/or
Position (cash, assets and liabilities).
Data analysis Financial statements analysis Calculations
Start with a data table – which is a collection of all the computed ratios. This enables
the marker to see the calculations produced, as there will be a set number of marks
available for the calculations, usually up to half of the available marks.
To score high marks, aim for a variety of calculations that include:
Percentage changes are necessary, but typically score 1 mark in total
(regardless of how many are calculated!)
Financial v Financial ratios (combining numbers from the financial statements,
such as gross profit margin)
Financial v Operating ratios (combining numbers from the financial statements
with the additional operating data, such as revenue per employee)
Operating v Operating ratios (combining numbers from the operating data, such
as average customer per store. These are harder to generate but it’s important
to calculate these in order to score high marks).
Data analysis Financial statements analysis Discussion
Interpret YOUR analysis (marks are awarded for discussing incorrect numbers).
Avoid re-stating the numbers without any meaningful discussion. Ideally each point
made should include:
What – briefly state what has happened to the numbers
Why – how has this happened and what has caused it
When – timing may be relevant
So-what – this is the added value if you can take your point further, think about
the impact on stakeholders / future of the company / finance / impact on
reputation / operational issues / what you expect the results to do in the future.
It is worth attempting the worked example Chrono from Chapter 20 in your ICAEW
Workbook to see how this can be tested at this level.
Data analysis Financial statements analysis 2.2 Data visualisation
Data visualisation is the use of charts and diagrams to present information. You may
be provided with visualisations in the exam and asked to interpret or discuss them.
Do not ignore, but always ask the questions ‘what is this telling me?’ and ‘how does it
link to other data provided?’
Some of the common visualisations include:
Bar charts
Pie charts
Line charts
Data analysis Data analysis tools 3.1 Probability
Probability can assess the likelihood of an event happening in the long run, often using historical data.
Probability of achieving desired results = (number of ways of achieving desired result) X (total number of possible outcomes)
Independent not mutually exclusive events
Probability of (A or B) = probability of A + probability of B – probability of (A and B)
Dependent events
Probability of (A and B) = probability of A × probability of (B[line]A)
Where probability of (B[line]A) means the probability of B occurring given that A has occurred
Data analysis Data analysis tools 3.2 Statistics
Statistical methods will often be associated with analysing data with respect to risk
management. In the exam, you may be provided with a data set that needs to be
interrogated using statistical tools and the corresponding results will need to be
interpreted. It is important that you have an understanding of the spreadsheet
functions to perform calculations.
The mean (or average) of a set of data is calculated by taking the sum of all the
values and dividing by the number of values in the distribution.
=AVERAGE(range of cells)
Standard deviation is the measure of how much variation there is in a data set.
A wide number of possible outcomes would have a large standard deviation and
if the outcomes are for a new strategy, it would indicate a high level of risk. This
results in a new strategy being rejected if the standard deviation is higher than
the risk appetite of the organisation.
=STDEV(range of cells)
A more meaningful measure is the coefficient of variation, which is calculated
as the standard deviation divided by the mean. The lower the result, the better
the risk-reward trade-off.
Regression analysis aims to specify the relationship between two variables. If
a relationship exists then it may be visualised using a line of best fit. This can
allow predictions of future variables and aims to understand the causes behind
movements.
A correlation coefficient looks at the strength of a relationship between two
variables. Perfect correlation has a co-efficient value of 1 or -1. However, it is
important to understand whether a causal relationship exists between the two
variables. Sales of ice-cream and sun lotion may have a high correlation
coefficient. But, they are both impacted by a third variable – the weather.
=CORREL(cell range 1, cell range 2)
A covariance is a measure of how two distributions vary with each other.
When a covariance is positive, the value of one distribution moves in line with
the other. A negative covariance has the opposite effect and they move
inversely. Diversification is most beneficial when the covariance is negative –
which simply means they react differently to external factors.
Data analysis Data analysis tools 3.3 Trend analysis
Trend analysis aims to uncover patterns in a company’s data in order to predict
future trends and support strategic decision making. Trend analysis involves
collecting information from multiple time periods and plotting the information on a
graph to assess whether there is an upward or downward trend.
The TREND function returns values along a linear trend and only looks for linear
relationships. It uses regression analysis to calculate the predictive values of Y for a
given range of X values). For example – forecasting the future revenue (Y) per
month (X) from existing data (known revenue per month)
=TREND(known Y values, known X values, new X values)
Note: Highlight ALL cells where you wish trend values to be returned. It is important
to do this before you input the TREND formula. For a worked example – see
Chapter 20 of the ICAEW Workbook.
Data analysis Data analysis tools 3.4 Distribution
Probability distributions demonstrate possible values and associated probabilities
that a variable may take.
Normal distribution
The normal distribution is a bell shaped curve and the probabilities of variables taking
particular values is represented by the area under the curve between two values.
In general 68.27% of values are within one standard deviation (between –1 and 1),
95.47% of values are within two standard deviations (between –2 and 2) and 99.73%
of values are within three standard deviations (between –3 and 3).
**Z-score **
A Z-score shows how many standard deviations above the mean a particular point is
Once a Z-score has been calculated, it is possible to look up the value in normal distribution tables to ascertain the probability of a variable taking a value between the
mean and that point.
The Z-score for a particular value X is calculated as:
(X-mean)/(standard deviation)
Data analysis Confidence intervals
When collecting data, a sample is often collected and inferences made on the
sample to represent the whole population. Taking a different sample however may
yield different results.
Confidence intervals can be calculated using the properties of the normal distribution.
For example, a sample size of 40 was carried out on heights of men. It found an
average height of 178cm with a standard deviation of 28.46.
To calculate the 95% confidence level we can use the spreadsheet function:
=CONFIDENCE(1 – confidence level, standard deviation, sample size)
=CONFIDENCE(0.05,28.46,40) = 8.82
This means there is a 95% chance that the actual population mean lies within 8.82cm
of the sample mean.
How far could the actual mean be from the one arrivd at, for a stated confidence level
Data analysis Spreadsheet functions
In most exams, you will be provided with a set of data to interrogate in a prepopulated spreadsheet. Spreadsheet functions can assist you with analysing this
data quickly. Chapter 20 of the ICAEW Workbook covers each of these functions in
turn with an example and it is vital that you work through this material before your
exam.
In Chapter 20 of the ICAEW Workbook, you can find a worked question called Home
and Garden Furniture (HGF) and it is recommended to attempt this question.
A full list of the spreadsheet functions that the examiner expects you to be aware of
can be found in the summary notes. In addition to the ones already covered within
this material, you will need an awareness of:
SUMIF
SUMIFS
COUNTIF
COUNTIFS
IF statement
IFS
Nested IF statements
Absolute cell referencing
Rank
Sort
Data analysis Spreadsheet functions Rank
=RANK(cell to investigate, cell range, order)
– An order of ‘0’ or blank will return the rank from highest to lowest
– An order of a number other than 0 will return the rank from lowest to highest
Example
=RANK(B6,B2:B11,1)
This will look at where the number in B6 ranks when sorting the numbers in column B
from lowest to highest.
What is it’s rank (lowest to highest or vice versa)
Data analysis Spreadsheet functions Sort
This allows you to sort data in a range of cells from highest to lowest, or lowest to
highest.
=SORT(“Array”,”Sort_index”,”Sort_order”,”By_col”)
– Array is the range of cells to sort
– Sort_index indicates the row or column to sort by
– Sort_order indicates the order (1 for ascending, -1 for descending)
– By_col indicates the desired sort (False to sort by row, True to sort by column).
Example
=SORT(A2:B7,2,-1,FALSE)
This will sort the data in descending order from A2 to B7 by the data in column B.
Alternatively, you could use the sort tool under the data heading in the exam
software
