Data Analytics and

Use Cases
What is Data?
• Broadly speaking data is information with descriptors
• $30,000 on its own does not tell much, but $30,000 of sales at 4 PM on
Sunday October 30th in the East Store tells more
• $30,000 is the data point
• Sales, time, which store are the descriptors or metadata
• It does not need to be financial
• Demographic information (age/background/alma mater/etc)
• Satisfaction rate
• Survey results

2
What is Analytics?
• Making sense of data
• Data on its own does not give any sort of actions or conclusion
• Analytics entails:
• Grouping
• Categorising
• Finding patterns
• Cleaning up the data
• Finding outliers
• Once those are complete, data becomes usable for business cases

3
Data Analytics
• Process of evaluating data to draw conclusions to address business questions
• Transform raw data into knowledge to create value
• Using data to tell a story
• Culminates in an action plan to generate profit or non-financial value

• For data to be useful, it should be:

• Complete
• Reliable
• Valid

4
Data Use Cases in Accounting
Audit
Allows for more tests, better quality tests
Improved efficiency and identification of key risk areas

Financial Reporting
Improve quality of valuations and professional judgement calls (i.e. A/R recovery rates)

Tax
Use of data to allow for better estate planning

Managerial Accounting
Use of trending/estimating to create budgets/forecasts
Use of analytics to understand underlying causes for variances
Compile data to drive performance evaluation of strategic decisions
5
Required Skills for Data Analytics
• Ability to articulate problems/requirements/solutions
• Understand the quality and limitations of the dataset
• Draw appropriate conclusions
• Know which questions to ask
• Ability to compile and summarise for non-accounting professionals
• Technical skills
• Data scrubbing
• Analysis
• MS Office/Excel
• Visualisation to tell your story

6
Software Used in Data Analytics
• Many types of software depending on size/industry of company
• Excel often used for ad-hoc and initial analysis
• Access used for database management
• Visualisation software – many on the market, most common:
• Tableau
• Power BI (Microsoft O365 included)
• Database servers – typically SQL based, but there are others

7
5Vs of Data
Volume
Size/amount of structured or unstructured data Volume

Velocity
Speed of processing
Speed of generation of data points
Value
5Vs of Velocity

Variety
Types of data consumed (i.e. inventory codes, time of sale, person who sold)
Data
Veracity Veracity Variety
Quality/accuracy of data

Value
What can the organization do with the data (what value it brings to the org)

8
Types of Data Analysis

9
Financial Data Analysis
• Computers allow GLs to be extensible -> use of subledger to house additional
data
• As such, management accountants have much more information to work with
• i.e. Can accurately track inventory through use of RFID and inventory
management software
• Identify seasonality/consumer trends

10
Trend Analysis
• Using ledger and subledger information allows for more granular trend
analysis
• Can use data mining to find trends that would normally be impossible to find
• Can use software to eliminate seasonality and estimate impacts of economic
downturns

11
Drill Down Analysis
• Allows you to find the real reason behind business questions

• Start from a high level issue like revenues are down versus budget
• Drill into which geographic locations are driving that variance
• Drill further into which products are causing that variance
• Drill further down to the specific stores that are not meeting budget

12
Financial Ratio Analysis
• Can use Excel to automate financial ratio analysis and track that graphically on
a KPI dashboard
• Most accounting softwares do this out of the box
• Ratio examples:
• Profit margin %
• Contribution margin %
• Return on Investment
• A/R turnover
• Etc.

13
IMPACT Cycle
Identify the
• Systematic approach to question

implementation of data analytics

Track Master the
Outcomes data
• Goal is to systematically approach
business inquiries/questions

• Can also be applied to any

implementation Communicate Perform the
Insights test plan

Address and
refine results

14
1. Identify the Question
• What do you want to show?
• What story do you want to tell?
• Examples:
• Are there suspicious travel expenses?
• Are we paying invoices on time to take advantage of early payment discounts
• Are transactions being received in the correct amount?

15
2. Master the Data
• Identify challenges with the data
• How reliable it is
• Will it be available on a timely basis
• How much data is available for use
• What does it take to create the dataset/bring it to a usable state

16
3. Perform the Test Plan
• Identify dependent (y) and independent (x) variables
• What analysis methodologies to use
• Trend analysis
• Pattern analysis
• Drill down
• Financial ratio analysis

17
4. Address and Refine Results
• Workshop different presentation methods and data approaches with colleagues
• Additional slicing and dicing of data
• May have missed certain points
• May find a new view that answers the question more effectively
• Ask for feedback from reviewers

18
5&6. Communicate Insights and Track Outcomes
• Decision makers will communicate the result
• Typically done via a presentation or a data visualisation
• Ongoing plan to track results and impacts

19
Working with Data
• Typically stored in a database
• Could be SQL type
• Could be specific financial software (i.e. SAP, MS Dynamics, Oracle ERP)
• Regardless of the type of database, these are all some form of relational
databases
• There are other types, but relational is most common
• These softwares will typically have an Excel plugin
• To analyse it -> can choose to dump into Excel if the data allows for it
• Can also analyse it within the database engine using SQL/Python/other
languages

20
Relational Database Qualities
• Most companies will use a relational database
• All data is stored in a single location
• There is no redundancy
• Internal controls are enforced
• Different business functions communicate with each other as they should be
using the same database as their “truth”

21
Data Dictionary
• Data dictionary is used to explain what each column/descriptor means and how
it was derived

22
Approaches to Working With Data

Supervised Unsupervised

• Using historical data to answer a • Not seeking answer to specific

specific question question
• Curiosity driven
• Seeking trends or specific
groupings

23
Qualitative vs Quantitative Data

Qualitative Quantitative

• Categorial • The difference between data

• Can do count/group/rank analysis points is meaningful
• Main use to show proportion (i.e. • For example, $100 revenue in
% of students in accounting vs all July vs $200 revenue in August
disciplines) • Can do sum/average/median/etc

24
Declarative vs Exploratory

Declarative Exploratory

• Explicitly answering a question • No set conclusion

• By looking at the data analysis • Used to demonstrate patterns or
result (i.e. the graph), the findings
audience should be able to • Presented in interactive style like
reasonably guess the question in a brainstorming session (i.e.
being answered based on past sales in winter
season, we should run campaigns
on x)

25
Excel Data Analysis Tools
• There are certain functions with Excel that are used widely during data clean-
up and analysis
• CONCAT() – combines multiple strings together, can be used to create unique
IDs
• RIGHT()/LEFT() – returns certain sections of the cell, used to remove
unnecessary information from the field (i.e. a prefix or suffix)
• INDEX MATCH/XLOOKUP – function used to look up against a table
• More complex form of VLOOKUP
• Pivot Tables/Charts – allow you to sum up certain categories in a quick and
easy way or to create charts quickly
• PowerQuery – Excel has a row limit, but with PowerQuery you can import more
rows into a back-end Excel database that you can then query through pivots

26
Excel Data Demo
Context:
• You are a financial analyst working in Barley&Co company
• Barley&Co was founded in 2000 and sells Barley and Canola in wholesale quantities for further processing
• Last year was not as profitable as it could have been due to many different world events
• Your manager has asked you to analyse historic trend data and create a model to help prepare for 2025
budget
• For Gas prices, the model should take the average of Jul-Dec for a filtered location and have an input for the number
of kilometres to travel
• For Barley and Canola prices, the model should average the last 4 years of prices, with a filter for the province

27
Excel Exercise Takeaways
• Pay attention to the data you are working with:
• Is it averages?
• Can it be summed up?
• Ask yourself if the output makes sense
• A gas price of $1.60/L makes sense, a gas price of $160/L does not
• Make sure your presentation is easy to follow, your story and inputs should be
easily traceable
• Keep track of your tabs and where each data source is
• Set up your model so you can easily change inputs, and the output will be
automatically calculated
• i.e. change amount of volume sold, or change the geography filter if you’d like to do the
analysis for another province

28
Visualisations
• The end result, more often than not, will be a visual representation of the data
analysis done
• Qualitative data can use pie charts, bar graphs, word clouds, tree maps
• Quantitative data can use line charts, scatter plots, maps, etc

29
Bad Graphing
• Scale does not begin at 0 or is
inverted
• Too much detail
• Cluttered
• Visual cues do not correspond
to actual scale

30
Bad Graphing
• Scale does not begin at 0 or is
inverted
• Too much detail
• Cluttered
• Visual cues do not correspond
to actual scale

31
Bad Graphing
• Scale does not begin at 0 or is
inverted
• Too much detail
• Cluttered
• Visual cues do not correspond
to actual scale

32
Bad Graphing
• Scale does not begin at 0 or is
inverted
• Too much detail
• Cluttered
• Visual cues do not correspond
to actual scale

33
Bad Graphing
• Scale does not begin at 0 or is
inverted
• Too much detail
• Cluttered
• Visual cues do not correspond
to actual scale

34
Good Graphing
• Ask yourself, does the axis and title make sense? Who are my audience?
• Some examples can be found in the following resources:

• Tableau Public -> hosted by visualisation software Tableau, have a “viz of the
day” which is dashboarding

• r/dataisbeautiful -> community members share their data visualisations and get
feedback

• On the flip side, there is r/dataisugly which has bad visualisations ☺

35
Graphing Considerations
• What scale to use for the axis
• Colours, probably want to avoid red/green/yellow due to traffic light connection
• Also be mindful of where the graph will be presented, like projectors might not show
yellow or pink very well
• If using colours for a scale, don’t use wildly different colours, but opt for the shades of the
same colour to go from most to least
• Audience and tone (co-worker vs management)
• Corporate branding considerations

36
Dashboards
• Dashboards are a collection of interactive visualisations
• Used often for high level executives due to ease of use
• Used to track KPIs specific to each executive’s function or overall KPIs for the
whole company

37