Excel is a firm favourite with accountants. It’s a powerful tool for tracking financial data, creating reports, and making predictions. It’s easy to use and its affordability means its firmly embedded in many small and large businesses.
But are you making the most of it when it comes to financial reporting?
Tables are the perfect choice when you want to share precise values. They allow all your data to be visible to whatever accuracy you choose. They can also be useful when communicating with diverse groups who may want to interrogate your data in different ways. People can use slicers as interactive filter controls to focus on what is important to them.
Graphs and charts should be used when you want to highlight a pattern or trend. Effective data visualisations will display and tell the story of your data, helping you to convey the key points of a data set as quickly as possible.
Column charts allow quick comparisons, line charts highlight trends, and pie charts display relative proportions.
There are some less well-known charts that can confuse users, so let’s take a quick look at some of those now:
- A
box and whisker chart displays the distribution of a set of data, highlighting five key
statistics: the minimum value (the top whisker), the first quartile, the
median, the third quartile (all shown in the box), and the maximum value
(the bottom whisker). They are good for comparing multiple data sets from
independent sources that are related to one another. For example, management
accountants may want to compare the variation in sales figures across
different products or regions.
- Waterfall charts show how the starting value of something (say, a bank account balance at the
beginning of the month) reaches the end value (the balance at the month
end), as values are added or subtracted (payments come in or leave the
account). These can be used to track cash flow over time, for example.
- A
heat map is a chart that uses colour-coding (e.g. different colours or depth and tone
of colour) to display different values, highlighting any disparity between
those values. So, that makes them great for quickly comparing revenue or
profits over months or years.
- Combination charts show different chart types (each displaying a different set of information)
on the same chart, e.g. a line and a bar chart. These are useful to visually
highlight the relationship between independent but related sets of data in
one place, for example, to show revenue compared with your targets.
- A
bubble chart is a development of the scatter chart (which visualises the correlation
between values on the X axis and values on the Y axis). Bubble charts allow
you to add a third, and even fourth category of values into the mix! They
can tell the story of different variables that are related to one another -
but they are tricky to get right.
- Finally we have
Excel dashboardswhich present charts and graphs summarising critical performance
information, all in one place. The visualisations should help users to
quickly access and understand raw data on key performance areas, to help
them make effective business decisions. An Excel dashboard can be a very
effective tool when it comes to tracking KPIs, comparing data points, and
getting views over different time periods, to help management make
decisions.
You can check out all of our verifiable CPD on Excel here!
You need to sign in or register before you can add a contribution.