GPI 008 – To find problems, define and run exception reports to resolve problems more quickly.

Many company employees run numerous reports and when necessary, dump that data into a spreadsheet to spend hours manipulating it multiple times.  They do this week after week deriving various sorts and lists of exceptions and data problems.  Some of this work is necessary but most is not.  There is definitely a part of this that begs to be automated.  Examples of this data manipulation your employees do every day, include running entire reports looking for inventory details, all-inclusive sales transaction lists for the month, every transaction comprising accounts receivable or a complete listing of all of the company’s 14,000 employees.

These employees diligently dump this data into spreadsheets and try to find bugs, errors, answers to management’s questions or maybe they are just trying to clean up the database, which is never finished in a growing business.  Ask yourself how much of this is wasted effort, and can it be cut down with exception reports?  Try a different approach to get to the answers more quickly, with less cost.

Decide the specific problems, exceptions and abnormalities you wish to track and define them in the form of designed reports.  For some people this is already being done.  Many small companies still waste a lot of time running reports that do not help to efficiently support the growth of the firm.  For this reason, stop and review the most critical pieces of information that you need to run your business, ask how it is being derived and how often.  You may be surprised at the manual manipulation that is being done that should be automated.

Run lists of only the exceptions you need to find.  This will speed up review and cut wasted time culling through thousands of good data entries.  Design reports to list only those items that fall outside the normal parameters of what you are seeking.  Shorter reports allow you to get to the problems much faster and speed up the correction process.  Here are some examples of these types of reports you might consider having written by your programmer (internal or IT vendor).

Examples of various useful data exception reports:

1. Old invoice cutoff: Run a listing of only outstanding company billed invoices now over 60 days old (you pick the cutoff time period).  Include a page break between each customer, include all invoice and purchase order detail, the customer’s email or fax number, email address and the name of the AP person who pays your invoices.  With different pages, it may be easier and much faster to assign and pass out past due invoice reports to a number of staff so you may double up on collection efforts thereby bringing in cash far more quickly.  By including a page break, each report can be scanned and emailed or faxed directly to the customers’ AP in charge of paying your firm.  Stress to the group involved with this collection effort that they are essentially collecting their paychecks.
2. Excess inventory cutoff: Run a listing of any inventory over 90 days old, sorted by location (i.e.  Warehouse A, warehouse B, store 101, store 102, department 29, department 30, etc.) or cost level (over $100.00, under $100.00, $.01 to $1,000.00, $1,000.01 to $5,000.00 — you pick the categories).  Once these reports are run on a regular basis, mark down this inventory to move and notify purchasing to reduce future purchases at this point.  Mark the products ‘overstock’ and distribute the lists to the sales department to begin moving.  Announce improved or increased incentives to your sales personnel to find buyers for this ‘stock with whiskers’.  Make it worthwhile to turn this inventory back into cash.  Do not regret the overstock; just do not make the same mistake twice.  Make sure to lower the buy level trigger that resides in your system for the next purchase.
3. Product sales cutoff: Run a list of only those items you sold that generated less than $20.00 net income, 20% gross profit, 10% net profit or 8.5% net income (you pick the net income cutoff percentage or dollar amount).  You decide between percentage return and dollar return.  It will depend upon your business and internal requirements to cover fixed costs in your industry.  $10.00 items may generate 75% net profit every day of the week for your firm, but it will do no good if you cannot ever cover fixed costs.  This is the reason net profit percentage cutoffs may not be the only solution.  You may have to develop a combination of net profit percentages with dollar bottom levels.  It is your decision, specific for your company.  Design the report and run it for review on a regular basis.  Your goal is to isolate low profit items for targeting.
4. Sales order minimum cutoff: Run a listing of all the sales personnel who generated under $100,000 of net sales this month, $50,000 of gross receipts this week $250.00 of receipts today (you choose the dollar and time period cutoff).  You may wish to cut the report by net income produced by sales person if that is more applicable for your business.  Make sure to have this report available for the sales person to run himself at any time.  The question to answer is “Who is failing.”
5. Customer data exceptions: Run a listing of all customers without a buyer’s name, telephone number, fax number, email address, zip code, city, state or whatever basic customer information you need for your analysis.  Instead of dumping these databases into excel and sorting this data over and over again, have the report run that automatically pulls out these exceptions much more quickly and more methodically.  You want the best customer information can you get.
6. Vendor data exceptions: Run a listing of all vendors without the company’s valid and current W-9 on file, seller or contact’s name, telephone number, fax number, email address, zip code, city, state or whatever basic customer information that you need for your analysis.  Instead of dumping these databases into excel and sorting this data over and over again, have the report run that automatically pulls out these exceptions much more quickly and more methodically.
7. Customer Sales and Net Profit ‘Laggard” Exceptions: Run a listing of all customers that generated under 20% of net profit.  Within that group determine which had the largest sales dollars and have that report generated automatically in order to develop a strategy to improve these margins on these larger accounts.  It is this group of accounts that is dragging down your overall weighted average net profit-return percentage.  Begin analyzing what is causing this account to be lagging behind the rest of the pack.  Does this customer need some products raised in price?  Does he need a cost analysis and review?  What is costing an excess amount of money that is dragging down net profit return?  In this group of low net profit percentage high sales dollar companies, run this report often and analyze the details on these higher dollar accounts before attempting to go in and raise prices without considering the consequences.  Consider all of the other alternatives; obtaining cheaper material costs, streamlining the production process, eliminating extra costly steps that add no value or speak to the customer and suggest cost cuts that can help avoid a price increase.
8. Last purchase dates exception reports: If customers are known to buy from your company every three months, you may want to develop a report for your sales people that will list all those customers that have not placed an order for more than 90 days.  If that time frame is 6 weeks, have the report cutoffs for one month and one half (i.e.  2 months, five days, two weeks — you decide the reminder cutoff time).  Have these reports available to be run personally by the sales personnel at any time.  You may want to also develop follow-up reports to mail out 10% discount coupons to these missing customers to be used on their next in store purchase or other promotional ideas for those missing customers.  The most important thing is running these reports because customers are missing for a reason.  You need to find out why they have not returned.  Your product or service is deemed not worthy.
9. Absent customers from the monthly sales list (Lost Sales): Run the list of customers that did not show up for the month.  If you will enter a dollar amount that should be the normal dollar amount expected from a customer and have an exception report pick up that customer name and the shortfall for the month, it will be easier for you to see who is dropping off or which customers are displaying problems with reduced orders.  Your sales team needs to investigate what is happening; where your customers are going and why?  Try coupons, a phone call or survey to find what went wrong.
10. Unscheduled production machines list (Lost Sales): Run a listing of production machines that are not running because of lack of sales.  Forward this to sales and put a bounty on incoming purchase orders to keep these machines operating.  This generates a level of overhead absorption even if those sales revenues are bid at breakeven.  Your goal is to keep machines operating at all scheduled times to recover capital investment.
11. Out of Stock Items / Products Listing: If you religiously keep track of all the times you are out of stock for various products you normally carry (inside sales and customer service feedback), you will know the lost sales you are experiencing because of poor purchasing knowledge or a misunderstanding of your potential market.  You will want to provide this list of missed sales to purchasing or production scheduling to adjust to larger market demand.  What sells and what does not?