What if I told you that you could save 140 hours a month…with only 50 days of consultancy. That’s quite a financial saving, but on top of that you could also reap the benefit making it easier for people to interact with data they use on a daily basis to help drive more focused, insightful decisions! Read on to see how…

Introducing Qlik Business Intelligence Tools to a Credit Control team

undefined

The Challenge

Mindset is surely the biggest challenge. Ask anyone to think about how they could get more value from their data and most won’t know where to start. Some may have a clear vision of what they want and just need the technical resource to get them there and for others it can be a matter of building the foundations first and figuring out the next best route to achieve their goals. Some even resist and cling like mad to their static Excel or systems-based reporting!

Those that have a decent idea of what they need, along with an enthusiastic attitude to give it a go and take the required steps to reach their vision, can create massive value from their myriad of data sources.

Thankfully the Credit Control team, led by Jason Spyer at MKM Building Supplies were open to innovation! Coming from a background of big business / big data he had a good idea of the direction he wanted to set off in. From an initial point of automating his current semi-automatic reporting burden, we enhanced the Credit Control function using Qlik business intelligence tools, with a level of automation that eradicated the boring leg work and additional development work to enable greater focus and streamlined management. Benefits not only included quicker and enhanced interrogation capabilities to identify hotspots and drive targeted actions, but also such benefits as the softer but no lesser, team morale boost and better staff enhancement capabilities.

Background

I would like to take you through the journey taken at MKM Building Supplies Ltd (67, and growing, branch network of Builders’ Merchants – mkmbs.co.uk, with 1,700 employees) with their Credit Control team (centralised team, managing a very large volume of customer accounts) over a period of seven months (non continuous due to other project commitments), to ultimately save time, increase efficiency and effectiveness in the management of its credit exposure.

Gathering Scope, Due Diligence and Development Framework

Gathering the user requirements at the start of this project was easy. It was simply a case of understanding the current Excel ‘model’, how it was constructed (including any data sources) and how it was used operationally, along with its current limitations. Our first phase scope was then ‘go for launch’…. Copy the Excel ‘beast’ verbatim and automate it (making some creative developer tweaks along the way to enhance it)

Once this was put through the Qlik engine and Jason saw the results, the touch paper was lit and we were off. With results in front of him I explained in greater depth the capabilities of Qlik and how it could pretty much do anything with data that the user could throw at it, he then started to brainstorm what we could do and where he wanted to be as an end game.

At our next meeting the draft phased plan was then born and we talked through the strategy that would be employed to manage the project

Objectives Overview

Phase 1

o Automate current labour-intensive reporting

o Focus team on priorities / actions required

o Adhere to the ultimate objective of focus on minimising debt

Phase 2

o Enhance capabilities of the team to pinpoint target issues through use of Business Intelligence

o Enhance management insight of both major customer accounts and the credit control team

Phase 3

o Advanced insights through the tracking and trending of status changes and behaviours to identify time based indicators

o Auto reporting through the use of Qlik nPrinting (qlik.com)

o Investigate use of Ping Alerting bolt-on (roxai.com)

Development Framework

It was explained to Jason and agreed that to get the best results from this type of development an Agile project management strategy is the most optimum framework to employ.

Qlik lends itself to a ‘suck it and see’ approach. Develop something, see if it works, if it does use it, if not make it work as it should then start using it. As a developer you always keep mindful of the law of diminishing returns and be pragmatic when considering development time against the perceived value that it may achieve.

Follow this link for sample documentation qlickxl.com project management template


undefined

Summary Project Timeline

[Italic text is approx. development time spent]

March ’19 [10 days]

o Understanding of construction, data sources and use of the current spreadsheet models

o Creation / coding of the Qlik load script to connect to the data sources, map the relevant relationships, transform the data and schedule refreshes

o Build of the basic Qlik visuals using the standard Qlik charts

o Set up users, their access levels and provide training on how to use the dashboard

o Deploy for testing and feedback – once live, small changes made to meet users specific needs

June ’19 (April & May spent on other projects) [10 days]

o Vizlib (which is an add on to Qlik – vizlib.com) charts added to allow the user to click through relevant Key Performance Indicators to filtered content on other sheets and enhanced visuals

o Change the format and ordering of the visuals and navigation to allow the user to follow a story through their data and focus on driving the user to actionable insights

o Further formatting tweaks to meet the users needs

August ‘19 [15 days]

o Construction of a management dashboard

o Enhanced load script coding work to enable advanced filtering / flags

o Additional data requirement for new operational processes (introduced as a result of this development work)

o Segmented analysis (to allow management of pockets of debt and allow easy validation to financial reporting)

o Freeze first of month and previous day data to allow monitoring of status changes over time (new information Qlik then allowed that was not previously available)

o Provide separate legal team dashboard

o Gather feedback to date from team and management and make any suggested changes

September ’19 [15 days]

o Historise data to facilitate daily trending of customer and credit control metrics

o Additional enhanced filtering and data flags

o nPrinting of major reports (automated reporting & distribution). Ping mobile alerts enhancement didn’t get further than a proof of concept due to company IT security restrictions.

o Further usability dashboard tweaks

undefined

Benefits / Savings

· 30+ hours per month saved of credit manager reporting time

· Replacement of excel model that was no longer fit for purpose and prone to errors with a supportable and enterprise grade Qlik solution

· Approx. 110 hours per month saved within the team due to quicker identification and ability to action debt

· On these measures alone the saving is at least the equivalent to one full time person, and as the business grows the benefits become even bigger!

Direct quotes from the Jason (credit control) manager;

“Positive effect on team morale”

“No more waiting for the manual dashboard to be prepared each day and I don’t waste time completing and fixing it”

“Team now solely running off the Qlik dashboard, which is dynamic and quick to give the team an overview of the collection position of accounts and creates inter team competition”

“Dynamic approach allows individuals to better route to plan and prepare their day/week in managing their accounts”

“It quickly identifies hotspots to target, may that be Unallocated Cash, High Value Accounts or Aged Items”

“The management dashboard allows quick access to both positive and negative information to assist conversations with branches and the wider management team. Facilitates improvement of slipping performance and measures the team better to improve cash collection”

“Allows us to highlight high risk accounts to monitor over a period of time and plan additional resource and training needs”

“The trending and status facilities allow us earlier monitoring and identification of any issues concerning the team, the branch network, customer groups and debt balances”

Please Contact QlickXL here if you would like to find out how we can help you & GOOD LUCK OUT THERE WITH YOUR QLIK PROJECTS

undefined

Words of encouragement for fellow Qlik developers;

Henry Ford allegedly said “If I had asked people what they wanted, they would have said faster horses.”

He had a clear vision and stuck to it. It can be hard sometimes when people are used to horses and you are trying to show them that they can have rocket ship technology!!

undefined

As a Qlik developer you have to keep your enthusiasm and remember that;

“Success consists of going from failure to failure without loss of enthusiasm.” Winston Churchill

and that…

“Any change, even a change for the better, is always accompanied by drawbacks and discomforts.” Arnold Bennett

And most importantly, keep going – If you’re passionate about what you do, find the right people to work with and persist you can make a real difference…

after all….

“If you build it, they will come.” Kevin Costner, Field of Dreams

undefined