Vinai Prakash

We all are sitting on mountains of data, and new data arrives each day in the form of Reports, CSV files, Charts from Marketing, Logistics, Sales, Websites, Google Analytics… Before you can make any sense of it, even more data will arrive.

Today we have much greater processing power in each computer than 10 years ago, yet we are not making appropriate use of it to process the data and create information.

I am sharing some of the best techniques used by data warriors & power business analysts. These are not really secrets… but best practices, that aid in converting data into actionable information.

If you want to learn How Do I Analyze Data Quickly, Join my next cohort of Data Analysis With Excel Masterclass.

1.    Clarity of Objectives: Before you begin your gold mining, define some broad goals or identify some of the problems faced by you or your company.

Is it low sales, low margin, low traffic or high CPC?

Once you have clarity on what exactly you are trying to analyze, you can begin our data analysis.

2.    Clean the Data: Most raw data arrives in a pretty bad shape. You need to remove duplicates, fill in some missing blanks or values, and get dates in a uniform format. This will make the later steps easier… or else it will be garbage in & garbage out. To check if the data looks good, try to sort it on different criteria, and have a look around. If it looks clean and complete, then you can begin the next steps in data analysis.

3.    Spot the Trends: It is easier to identify some trends in the data, and then analyze them further. There are several ways to spot the trends quickly. Some common methods are to visualize your data with the 80-20 rule. Identify which 20% of the factors contribute 80% of the results. Create bar charts, sort in descending order, and create a cumulative frequency chart with both axis to generate a quick Pareto chart displaying the 80-20 rule.

Another excellent way is to generate measures of central tendency – using Mean, Median, Mode, Outliers, Range, Variability and Skewness of data. They tell quite a lot about your data pretty easily, and make it easier to spot trends within the data.

Some of the common KPIs you could set are ROI, EBITDA, Net Profit Margin, Customer Lifetime Value, Market Share, Brand Equity, Cost per Lead, Customer Turnover Rate, Earned Value, Quality Index, Carbon footprint, or Supply Chain Miles.

With KPIs, and their trend, you can then find the story told by the data. Identify the reasons and take appropriate actions. Tracking KPIs over a long time period makes it easier to spot trends in seasonality, sales patterns, demand surge and profitability across months and quarters.

5.    Common Repository for Data: Set Up a common data repository, from which everyone draws data. It is quite common in larger companies to have multiple islands of data. Everyone seem to have a ghost server under their desk, compiling data from different sources and reporting off it. Thus, different stories are told in the board room, and the management often wonders which version is really the truth?

A common source brings more sanity, and trust on the data and reporting. A common data warehouse from where all management reports are generated is a great idea.

6.    Visualize Using Charts, Graphs & Dashboards: A picture is worth a thousand words. Rather than creating voluminous reports full of numbers, display the summarized information in the form of line charts, bar charts, spark lines and various other chart types. What may not be visible in data may jump out at you visually, in a chart. It is much easier to find actionable insights in charts. Fortunately, most data analysis tools come with excellent charting capabilities.

Create Simplified Reports Using Dashboards. Multiple summarized reports and charts can be compiled into a management dashboard. With key KPIs, charts and data visible on a single piece of paper or screen, it becomes much easier for senior management to make quick decisions.

Dashboards are dynamic, making it easier to compare month on month, quarter or quarter, division to division performance and spot trends quickly.

These visual implementation must be idiot proof – so simple that a O level student should be able to interpret it pretty easily.

Use simple tools for the analysis. It is not necessary that the next shiny reporting tool or expensive BI tools will make it a breeze. It takes many months of painstaking work to get to a standardized dashboard. A visually appealing and simplified dashboard makes analysis and reporting fun, something to look forward to.

7.    Constant And Never Ending Improvement (CANI): Experienced analysts are always on the lookout of opportunities to further extend their analysis, improve their dashboards and identify new insights. Ask your clients and users how they use the reports and dashboards, and seek ways to improve it.  Be open minded, flexible, inquisitive and persistent in your pursuit of information excellence. Ogle at your data from different angles and different perspectives. It will enable you to discover new insights and add value to your business.

Implementing these best practices will enhance your data analysis experience, and will enable you to create value for your clients, bosses, and with the new insights found, you can improve your business performance, productivity, and profits!

Cheers,
Vinai Prakash

About The Author: This article has been written by Excel expert Vinai Prakash. Vinai has over 28 years of experience in business intelligence, data mining, and creating useful management dashboards and reports.

Vinai runs his own training company Intellisoft Training, and has coached over 5,000 executives and management on creating dynamic dashboards using Microsoft Excel. Vinai runs his blog on Excel Tips & Techniques at https://excelchamp.net

Join Vinai’s Data Analysis With Excel MasterClass & Be an Excel Pro in Data Analysis.

Vinai Prakash will be conducting a 2 day Excel Dashboard MasterClass in Singapore in this year. Contact https://www.excelchamp.net or call +65-6296-2995 for more information.