Quality Solutions: Identify one day, fix the next.

As a management consultancy our customers expect results for their money and we obviously aim to serve.  During an internal audit we identified a big risk for a customer and the next day we fixed it.  We treasure these interactions with our customers – that’s why we’re not just auditors for a Certification Body – identifying a risk is interesting, but solving it and seeing the customer benefit from it is much more satisfying.

Identify

During an audit for a long standing customer it was apparent that sales staff were not confident providing prices to their customers over the phone as there were multiple sources of price information.  When questioned the customer was aware that Sales staff were not always quoting the correct price and that sometimes this meant the customer’s margin was eroded. Occasionally the price quoted was so wrong the sale was made at a loss, but the customer had no real idea why and tended to question the competence of the sales staff.  The internal audit identified the extent of the risk:

  1. the list price and customer’s ‘special price’ can be obtained from the financial system, but this isn’t easy to navigate and isn’t always up to date,
  2. the list price and any customer’s ‘special price’ can be overridden by an open quotation in the financial system
  3. a spreadsheet managed by the Sales Manager includes all account customers and uses cost prices and import costs to set retail prices, determines margins and records any new prices verbally agreed with customers.
  4. the agreed price list which is emailed to the customer isn’t connected to the financial system or the Sales Manager’s spreadsheet and was manually being produced and maintained,

Understand

There are several risks related to this arrangement:

  • sales staff need to consider all four sources to determine a products price during a short phone call,
  • determining what is a reasonable price to quote an existing customer for a product they don’t typically sell to requires referring to multiple customers in all four sources (a task which is not easy to do via any of the available sources,
  • sales staff are not able to update the prices in the finance system (this is designated as a Sales Manager’s role),
  • there is a lead time between the Sales Manager setting new prices and updating spreadsheets, customer price lists and the finance system and the accuracy of these documents is dependent on the person updating them.
  • left to their own devices, the confusing array of price sources and the lead times in the process Sales staff can easily provide customers with the wrong price and in a process this disjointed it is difficult to hold them accountable.

Fix

Excel Pricing Solution

Pricing Solution

The real solution is probably to get a better financial system, but this is not a cheap solution and will take a significant time investment to resolve.  However our solution was implemented quickly, cheaply and with minimum disruption.

We took the Sales Manager’s price list, and using code developed a button which when pressed generates a nicely formatted customer price list.  The list integrates the list price and any special pricing agreed between the customer. Another button creates an output file suitable for upload directly to the financial system reducing the lead time of system updates – all the updates can take place in the spreadsheet and a simple upload ensures that the latest prices are uploaded to the financial system. The Sales Manager’s spreadsheet used to show the margins for each product against every customer making the spreadsheet much bigger than it needed to be and requiring formulae to be maintained.  Not anymore.  Now they just select a customer, click a button and a Margin Analysis Report drops straight out which uses colour to highlight products which are making good or poor margin. On another day we added a product report which plots all the special prices and lays them out graphically between the lower limit set by the cost and the upper retail price limit. This provides a quick any easy graphical display of who is paying above or below what is a normal special price and also makes it easy and quick for sales staff to determine a fair ‘special price’ for new customers.

Excel VBA Solution - Pricing Problem

Quality Solution for Pricing Problem – Margin Analysis Report screenshot showing products colour coded by margin

After our work the Sales staff only need to check the Sales Manager’s spreadsheet and the finance system for any related customer quotes and the job’s done.  If anyone decides to look into the finance system for a price it’s now more likely to be accurate. The preparation of a Customer Price List now takes no time and is always accurate.  The Margin Analysis Report is neat and easy to identify product prices which pose a risk to profitability.  The Finance System can be updated quickly without manual data entry.  As the Customer Price List, Output to the Finance System and Margin Analysis Report are all created dynamically the Sales Manager’s spreadsheet is less cluttered and easier to read.  And all of this took less than two working days to create, test and demonstrate.

Quality Management isn’t just about raising non-conformances and writing procedures.

Share this post