Finding reporting tools to understand your business data is not beyond reach

By Chris Lord|21st August 2015

business data

Business intelligence is not just a buzzword and so much more than churning out business data and producing KPIs for the sake of it for your management meetings. By gaining access to data on quotes, orders, job costings, marketing information, accounting ‘profit and loss’ information, stock inventory, or to gain an overview of your Engineers’ visits, it enables a company to manage their operations. Taking a holistic view to spot trends and identify opportunities – this is purely priceless for any size of business.

According to Webopedia BI “represents the tools and systems that play a key role in the strategic planning process of the corporation. These systems allow a company to gather, store, access and analyze corporate data to aid in decision-making.”

Some companies are lucky enough to have a dedicated team or integrated business accounting software such as Pegasus(XRL Reporting tool) or Intact IQ which provides BI dashboards. These type of systems can be set-up to automate scheduled reports, based on key filters or KPIs, to manipulate centralised business data and produce meaningful visual displays of your business, with downloadable full data sets. You don’t need technical knowledge or an accounting or statistics qualification to have access to real-time, historical data trends and data to help with forward planning.

At DCS, the Managers and Sales Team use BI Tools to support the business:

  • Produce all management reports together with comparative stock analysis to measure sales this year against the same period last year. A report is generated to calculate commission on sales.
  • We are also able to produce a directors’ report giving a detailed analysis of the Top 20 customers, Top 20 suppliers, Top 20 stock items and Top 20 Stock groups, amongst others.

All of these give DCS instant information allowing the team to make informed decisions on the spot. Regardless of what your role is in your business, having information on-the-spot or at the point of need is crucial to the execution of your job. It also has a direct impact on the profitability of your company.

IntactBI screenshot

This BI Dashboard shows examples of information that can be gleaned from iQ from Intact’s BI tools enabling you to look at monthly sales in various different views, thus giving you a 360-degree view of your business.

We appreciate that for some companies there are still barriers to accessing systems or BI tools, due to funding, purely the time to leverage current systems to automate the reporting process or to integrate data across systems. The solutions here may be more manual methods by using Excel of Access. So I thought, that I would share with you some tips on how to use Excel for cleaning and drilling into your data and some visual tools if you are looking to be sophisticated with your colleagues if you fall within this category.

TIPS FOR MANIPULATING YOUR DATA

1. Excel Pivot tables

Pivot tables are great for manipulating your data and visually displaying it in different tables, with drag and drop like features.

Table of data:

data_chart

Pivot table example of sorting orders by company:

pivoT_AND_CHART

You can order your data into rows, columns and choose different values to aggregate your data (SUM, AVERAGE, COUNT ETC) as shown below:

pivotfields

There is a great step by step guide by Excel on how to create your Pivot table which may vary according to your version of software.

2. Data Sort and Filter

Excel allows you to sort your data by rows by different criteria – by value, colour and alphabetically A-Z or Z-A order, as well as custom lists

This table has been sorted in alphabetical order by company name and second level by order value.

data_chart(1)

Sorted values which were selected:

sort_name_and_value

How?

  • 1. Highlight your data table.
  • 2. Select SORT and Choose the following criteria as shown above.

For more advanced sorting techniques, refer to support.office.com Excel has so many functions including autofilters, look-up tables, conditional formatting, removing duplicates that it’s definitely worth spending the time to get to grips with it.

3. Infographic Tools

If you don’t have the budget for a Graphic Designer, there are a range of free tools which allow you to showcase your data visually with a type of data dashboard. Here are some examples below from Piktochart if you are unfamiliar with the term Infographics.

piktochartinfographic

Other free tools to consider include VIsual.ly, Infogram and Canva.

Additional Tips and advise on BI Tools and Dashboards

1. Independent Review of the UK BI Software tools and from g2crowd.com.

2. Intact IQ BI Reporting.

3. Pegasus Opera Reporting.

4. Planning Framework for an actionable business dashboard.

Image/Credit: 123rf.com