Quality Solutions

Published by admin on

As a management consultancy our customers expect results for their money and we obviously aim to serve and what could be a better example than a major quality issue identified one day and solution developed the next.  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 Certification Bodies. Identifying a risk is interesting, but solving it and seeing the customer benefit from it is much more satisfying.


During an internal audit it became apparent that sales staff were not confident providing prices to their customers. The reason for this was multiple sources of price information and it took time to determine which price to quote.  The organisation was aware that Sales staff were quoting incorrectly and sometimes this meant the erosion of profits. Occasionally the quote was so inaccurate the sale was at a loss. The organisation didn’t understand why this happened and questioned the competence of the sales staff.  The internal audit identified the extent of the risk:

  1. sales staff can find the list price and customer’s ‘special price’ in the financial system, but this is not easy to navigate and not 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 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,


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 a reasonable price for an existing customer for a product they don’t typically buy requires referring to all four sources. This is not an easy task via any of the available sources,
  • Sales staff are not able to update the prices in the finance system (currently the 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. The accuracy of these documents is dependent on the person updating them.
  • the array of pricing sources and potential for them not being current, means Sales staff are likely to provide the wrong price. It is also difficult to hold them accountable for the error.

The Solution

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 quick to develop and roll out, cheap, and with minimum disruption.

We took the Sales Manager’s price list, and developed an automated process for generating a formatted customer price list.  The list integrates the list price and any special pricing agreed between the customer. It can also create an output file suitable for upload directly to the financial system. This reduces the lead time of system updates. All the updates can take place in the spreadsheet and be uploaded to the financial system ensuring ongoing alignment. 

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.  With a single click they can produce a Margin Analysis Report for any customer and the report uses colour to indicate the 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 and easy graphical display of who is paying above or below what is a normal special price. It 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

The outcome

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.  Updating the Finance System is now quicker and requires no manual data entry.  As the process now creates the Customer Price List, Output to the Finance System and Margin Analysis Report dynamically, the spreadsheet has less clutter and is 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, it’s more about efficiency gains and reducing risks.


A word from the Managing Director: “We aim to establish long term, mutually beneficial working relationships, helping organisations grow and avoid the pitfalls that many fall into. Too many organisations feel their certification is a burden. We want to help organisations realise the benefits of effective management systems and certification”