Results
IT Audits
Project Summaries
Project 1: Production Process Waste Reduction
Situation: Seafood production process was consistently wasting large amounts of the largest volume & revenue product – Atlantic salmon – costing the company over $100K per month. Data & metrics related to the waste were not visible until weeks after transactions had occurred and data was often missing or erroneous. Each department had their own theories on the cause of the waste often causing finger pointing between departments.
Action: I recommended, designed, and implemented a multi-stage project to evaluate and address salmon waste issue. First, we built a tracking platform (using web tools and SQL backend) to enable Production teams to track actual waste more accurately. Once the waste baseline was established (over 10% waste), we began redesigning processes with Production, Procurement, and QA teams to reduce waste and to enable identification of causes of waste (handling issues, vendor issues, temperature/storage issues, age issues, etc.).
After 45 days, enough data had been gathered to implement changes and to dispel “old wives tales” and stop finger pointing. We confirmed with data that issues which were previously used as “blame” for waste had no actual correlation to waste.
We implemented new processes for tracking and reporting and training Production. We provided KPI dashboards for Production management, Procurement, QA, and the entire leadership team to monitor waste levels with real-time data no more than a few hours old.
The final phase of project involved working with Sales to find target customers for new products to utilize material previously considered “waste”. We developed 6 new products and worked with Sales to educate sales team and customers on new product offerings. My team worked with Sales & Accounting to develop pricing/costing methodology for the new products. We worked with Sales & Demand Planning team to properly forecast new items. And we worked with Production and Warehouse to develop proper inventory levels and cycle count procedures.
Results: After six months…
– Finger-pointing between departments stopped (related to this issue)
– Waste levels dropped from over 10% to 4.2%.
– New products were successfully utilizing the majority of the remaining “waste”.
– Customers were happy because the quality of product had improved.
– Over $85K of pure profit EVERY MONTH was dropping to the bottom line through waste reduction and sales of new products made from re-purposed “waste”.
Project 2: Purchase Order Adjustment Process Re-engineering
Situation: For over 15 years the company had been struggling with Purchase Order Adjustments (POAs) growing in volume and dollar amount with no visibility or cost controls. The company was losing over $50K every month on products that could have been refunded by vendors if POA were processed timely and accurately with enough information. The company had come to expect major inventory losses from bad product every month that could not be recovered from vendors due to lack of controls over POA process. Communication between Production, Procurement, and Accounting was broken. Employees were afraid of making mistakes and of management blaming them for inventory write-offs.
Action: My team and I mapped the entire process, identified steps causing problems, worked with all departments to redesign the entire workflow, built automated data pulls from the ERP system to a newly-created POA database, designed a new web-based application to capture manual data, integrated scanning of physical forms and pictures into centralized mailbox & database, built extensive set of operational reports for Procurement to use for tracking POAs to claim credits from vendors, built extensive set of interactive accounting reports to enable reconciling general ledger inventory accounts each month, built simplified set of reports for senior management to be able to track POA status on a daily basis instead of monthly surprises, trained Production, Procurement, and Accounting staff on new processes and tools as well as senior management on reporting analytics.
The technology used in this project was fairly simple:
Custom-built technology (SQL, IIS, Javascript, Kendo UI, C#), and BI reporting/analytics (PowerBI)
Result: After first full month of operation, POA losses had already dropped by over $50K per month. Senior management had complete visibility on POA status to be able to identify which products, vendors, and buyers were having the biggest impact on POA costs which will enable further POA cost reductions and quality improvements over time.
Project 3: Comprehensive Procurement solution
Situation: Procurement department had been operating the same for the past two decades with little change or improvement to their processes or systems. All raw material purchasing was highly dependent on specific buyers with little cross-training and no standardized purchasing procedures. Each buyer used a different set of tools and reports to purchase their category of products so buyer rotations were not possible. Raw materials were often over- or under-purchased because each buyer interpreted upcoming demand in their own way. Over-purchasing caused excessive waste and under-purchasing caused excessive product substitutions which led to customer returns and lower satisfaction. The core ERP system did not have the capabilities to perform a complete MRP function for suggested purchase amounts or time frames.
Action: My team and I built a comprehensive Procurement solution outside of – but integrated with – the core ERP system to supplement lagging functionality. We created a standardized set of interactive views for both Fresh and Frozen products. We tied the warehouse data with historical sales data and forecast demand data to produce the most accurate purchase quantity information possible. We held multiple classroom-style training sessions to teach Procurement staff members how to utilize the new system.
Results: Within 45 days after training staff, we began to see improvements in both reducing customer substitutions and waste resulting in significantly higher customer satisfaction, fewer customer returns, and over $12K monthly cost reductions. The new system also enabled buyer rotations so that buyers were no longer required to stay focused on a single product category for years.
Project 4: Returns & Credit Memos analysis platform
Situation: Customer product returns were causing issues for the Sales team who reported hearing multiple complaints from customers about incorrect product substitutions or poor product quality or wrong product shipped. Operations and Sales were pointing fingers at each other. The existing data from a returns tracking system built almost two decades earlier did not show any significant increase in returns. Accounting was also feeling that there was an increase in returns requiring credit memos but again the existing data tools did not reflect that.
Action: My team and I researched the data coming from the old tracking tool and determined that the old tool was flawed and not capturing all the return transactions and that Logistics staff were skipping over many returns due to time constraints. We built an entirely new tool and redesigned the returns process to reduce the amount of labor required by Logistics staff – as well as built in a fool-proof mechanism to catch transactions regardless of staff manual entries. Once the new data was being captured, we developed a broad set of visual reporting tools and dashboards to track all types of returns and credit memos, organizing them into their three primary departmental classifications: Operations, Sales, & Accounting. We were also able to reconstruct 99% of the historical data without requiring manual entries so that we could have a full history of previous returns and credit memos. The new interactive reports were presented in real-time showing accurate returns information from minutes earlier.
Results: We found that returns and credit memos were indeed increasing in recent months but the root cause was not only Operations – Sales and Accounting were also contributing to the increases. Using the new real-time data, all three departments were able to address some of the root causes to reduce returns and credit memos within 45 days resulting in a 25% reduction in Operations-related credits, 10% reduction in Sales-related credits, and 3% reduction in Accounting-related credits saving the company $10K+ monthly, reducing workload in Operations, Logistics, and Accounting, plus improving customer satisfaction levels and reducing waste caused by returned product.
Project 5: Lot-tracking and Traceability of products
Situation: 30-year old green screen ERP system had significant flaws in its Warehouse Management System (WMS) and inventory scanning modules that caused lot-tracking and traceability issues. Industry requirements were massively increasing with pressure from both government and major customers to have full and accurate product traceability down to the lot level but existing system could not do it reliably. The company’s largest customer – and tens of millions of dollars of revenue – was at risk of leaving if traceability compliance could not be achieved.
Action: My team and I designed and built a new traceability workflow within the ERP system using a new set of tables and functions combined with new processes we designed for the inventory control department to utilize.
Results: Product lot tracking and traceability was able to be successfully implemented and reporting provided on monthly basis to our largest revenue customer – eliminating the risk of losing customer and protecting tens of millions of dollars of revenue. In addition, the new lot level accuracy improved our inventory management making it easier to identify products that were not turning over and eliminating waste due to aged product going unnoticed.
Project 6: SOX IT Risk/Controls/Testing/Audit Framework
Situation: After Sarbanes-Oxley was enacted, there were no established frameworks for building IT controls or testing them. Auditors were using various approaches causing confusion, high audit costs, and potentially failing controls that could cause a material findings on an external SOX audit report. Employees were frustrated at controls that were cumbersome and did not provide any business value or substantial risk reduction. Costs were spiraling out of control while the external auditors and consultants were getting rich.
Action: Using the existing standards for IT auditing (COBIT) combined with extensive research into the new SOX regulations, built an extensive framework for mid-sized companies to document IT Risks, Controls, Tests, Evidence, and Audit processes.
Results: Successfully assisted 15 publicly-traded companies prepare for and pass SOX audits using newly established framework. Successfully pushed back against external auditors from E&Y, KPMG, and other firms to avoid their requests for out-of-scope compliance items or unnecessary evidence that could have produced significant or material SOX deficiencies. Kept external SOX IT audit costs down by using framework to prove to external auditors that SOX regulations were met without additional unnecessary controls.
Project 7: Paperless A/P invoice approval process
Situation: Accounts Payable department tracked G&A vendor invoices through a highly labor-intensive, manual, paper-based process that wasted time for both AP and department managers, caused confusion and mis-classification for department managers, increased time to pay vendors unnecessarily, and increased risk of lost vendor invoices. Plus, complex vendor invoices that require multiple department approvals and/or GL code assignments were often mis-classified or not approved by proper managers.
Action: Interviewed AP staff and department managers. Documented existing workflow and process. Developed requirements for new solution. Designed new workflow using cloud-based process automation/workflow tool to enable scanning of vendor invoices, tracking meta-data on each invoice, easy task and document management, collaborative workflow enabling managers to ask questions and provide more useful details on vendor invoices, and enabled easy cross-department approvals and multi-GL code assignments.
Results: AP staff reduced labor time by 26 hours per week. Invoice approval accuracy increased. Manager approvals timeliness increased. GL code assignment accuracy improved. New system ensured that no invoice approval could be lost or forgotten. System enabled easy roll-out to remote sites without requiring paperwork transport among sites.
Project 8: Point-of-Sale system for multi-site market/cafe retail
Situation: Company’s multi-site retail market/cafe division was struggling with an aging Point of Sale (POS) system from a tiny software vendor. Support was poor. The system was difficult to use and performance was poor. However, the sites had a unique set of requirements needing to operate both as a full restaurant with recipes, tips and table management as well as a retail market/store with inventory, catch-weight products, bar codes, and more.
Action: My team and I documented all system and business requirements needed to address both the “market” and “restaurant” aspects of the business. We researched 12 potential POS solutions. After careful evaluation, narrowed potential solutions to 2 POS vendors – Aloha (NCR) POS and Micros (Oracle) POS. Organized full demo and presentations from both vendors to retail management team. Final selection of Micros (Oracle) solution was made.
Results: New POS system was successfully implemented within estimated time frame and within budget. By using the multi-site design that we architected during the planning phase, the Retail management team was able to easily produce extensive reporting that was never available from the previous system. This reporting allowed much more granular information about labor efficiency, menu success, coupon redemption, and much more – allowing the stores to increase operating income by over 35% across all locations.