Project - Operations / Aligning ERP with Data Processing Efforts

Not long after I was hired my position was changed from working in Engineering on prototypes to helping the company with their ERP system.  As I was investigating why this help was needed, I overheard that the ERP system could not be used for demand planning. Also that the data in the ERP lacked overall integrity, could not be trusted, and if used in analysis would lead to faulty conclusions / results. The general consensus was that the ERP system was faulty and could not process data and information correctly and that the ERP needed to be replaced.

I initiated a review of the data in the ERP, and found that there were a significant number of open or incomplete jobs, purchase orders, and sales orders. I used VB scripts to ensure only valid data is written to the database, and used reports to help identify issues with stock codes, structures, and operations.  In reviewing the data, I uncovered data processing concerns and worked to review the company's data processing efforts.  

I worked with various departments to help ensure best ERP usage by reviewing procedures, introducing automation, and reviewing third party integrations. A short list of some of my work follows:

• I ensured customer facing documents met marketing vision and reviewed terms and conditions on documents:
• Sales Orders terms and conditions were reviewed, and return and warranty information was updated.
• Company logo's and displayed trading names were standardized and updated.
• The location that information was presented on documents was standardized across the sales orders, invoices, proformas, and etc..
• I worked to streamline sales, manufacturing, purchasing, quality reporting, and production scheduling procedures, and simplified supporting systems:
• I reviewed the status of all jobs, purchase orders, and sales orders in the ERP, working with appropriate staff to retire old documents, closing them as appropriate, and reviewed the data processing systems to ensure that all documents were opened, processed, and closed appropriately and within acceptable time frames. This process was repeated per each of the departments in the company. I remained appropriately in touch with Accounting to help ensure that these efforts did not inappropriately touch the GL.
• Some of the benefits per this review include:
• I eliminated the need for Accounting to close work orders by setting the ERP up to automatically close jobs when calculated and actual costs were to within 10% of each other.
• I used automation to reduce time spends entering payroll data entry into the ERP.
• I introduced a customer Setup form, which helped to reduce overhead communications, ensured data consistency, and helped to raise awareness to other departments.
• This review also lead to my reviewing the GL Integration and associated entries (double-entries and control code configuration), Product Classes, Bills of Materials, Structures, Work Centers, item costs, and customer price lists.
• I created custom fields and data validation scripts in the ERP:
• While working to simplify and correct data processing efforts it became apparent that some of the third-party applications were being used to house the data that the ERP did not provide an ability to store (out of the box).  I created and used custom fields to store this information directly in the ERP, and worked to include them in reports.
• I created data validation scripts to help eliminate data entry errors. Reports were also made to help identify if data was missing (ie. in structures), incomplete, or erroneous.
• These efforts helped to improve overall reporting, reduce data entry errors, and improve bill of materials and stock code accuracy. Example: the Sales Order team could now record reasons as to why a sales order could not be processed within a customer's expected time line, and could generate an exception report based on reason codes.
• I developed training manuals to help standardize procedures and reduce time spends when on-boarding or cross training:
• As I reviewed each module in the ERP I created data processing flowcharts using MS Visio and reviewed all of the data processing procedures, later building a master flowchart.
• As we worked to document the procedures, I included the team in the discovery of how the information was being used by other departments to help ensure clear visibility into the impacts felt by other departments when proper data processing efforts were not realized. Data processing procedures were agreed upon by both myself and the team - resulting in little to no extra training being required, and empowering team members to make further adjustments to their procedures on their own as they found necessary (I asked to be advised so that I could update my flowcharts and procedure flows).
• I created a Quality Hold warehouse in the ERP, with intention to remove on-hold inventory items from the view of the Sales Team:
• There was no way for the Sales Team to know whether items in inventory were on any type of a hold. The sales team had to either guess about item availability or contact someone in Quality or Production to find out if the items were actually available.
• Unfortunately this system was not fully active prior to my leave as the company was contemplating moving to a new ERP, and significant changes were required.
• I reviewed the Purchasing department:
• We reduced lead times and stock outs, re-established lead times, reviewed min/max/safety stock levels, improved inventory count accuracy, corrected the outsourced manufacturing procedure, eliminated obsolete and hidden inventories, exposed slow moving inventory, and corrected the inter-warehouse transfer procedure. I assisted in the re-creation of the stock take procedure. I also suggested Purchasing implement Supplier Scorecards and use the requisition module in the ERP to eliminate the external paper-driven system.
• This helped to improve supplier performance and reduce costs, lead times, ensure product accuracy, and reduce on the receipt of damaged or defective product.
• This also helped ensure inventory transfers did not inappropriately impact the GL.
• The Production Scheduler had created a system of Excel spreadsheets to enable the capturing of manufacturing data, production scheduling data, and quality recording / reporting. Through time these systems became  interconnected.  Data entry procedures, data types, and terminologies had not been standardized, causing issues in the translation of data throughout the system.
• I worked with the Production Scheduler to combine the production scheduling, manufacturing reporting, and quality recording systems into a single solution sharing a single database.  This proved difficult, as the system was live and could not be taken down for extended time periods.
• We worked to ensure data uniformity across these parallel spreadsheet systems, worked to improve the quality of the recorded data, eliminate duplicate data entry, optimize efficiencies, maximize employee potential, and reduce production scheduling conflicts.
• While we noticed significant improvements (ie. improved demand visibility, production leveling, forecasting, etc.) and a significant reduction in over/under production, due to the magnitude of the overall system this process was not completed prior to our leaves.
• We also worked to document the manufacturing data collection recording system. Prior to documentation, this critical system was only known to two people in the company. This helped us optimize and describe how the manufacturing data was being recorded. We also brought awareness to the need for new manufacturing equipment / devices to be integrated into the data collection system as quickly as possible so that the systems would record the proper parts as having been made (the scheduler was spending significant time correcting this information).
• I created reports supporting basic and advanced business processes, functions and analytics that support the business model and vision:
  
• Some of the reports I made: total sales and profitability by item or item family (Y-T-D and trending), holding inventory, commodity spend reports, cost of inventory / inventory turnover reports, prototype costing spreadsheet for use by R&D, and a Requirements Planning / Item Replenishment report to expedite ordering.  I also redeveloped the standard financial reports.
• I assisted the Finance Department:
• I investigated large variances, and odd or unknown entries on GRN price variance, WIP variance, and Inventory Valuation reports.
• I mentored staff on various procedures, such as; how to retire inventory, moving inventory to an outside location, etc., and to work with Accounting on the timing of movements / corrections.
• I updated related procedures to tighten the closing of work orders, purchase orders, and sales orders.  
• I identified GL coding issues (both their use in reports and the actual coding of GL entries).
• I brought visibility into discrepancies between the costing system developed by Finance and its implementation into ERP, working to remedy (adjusted labor rates, fixed and variable costs, and sub-contracted rates as required). I also advised of future changes necessary to further improve the system.
• I created a new procedure to update the Standard Cost in the ERP to ensure the accurate generation of item costs, and worked to set the standard cost for every stock code.
• I reviewed the indirect and direct payroll entries manually entered into the ERP and suggested changes to GL coding that would improve the separation between direct and indirect labor payables on financial reports.
• I reviewed the differences between year-to-date calculated and actual labor payables for both direct and indirect labor using my rebuilt costing spreadsheets to calculate payables based on 2018 YTD sales, per item. Calculated differences and provided to Finance to review and adjust for use in the 2019 standards.
• I assisted in the review of company assets, reviewing their status, and all of the prior recorded transactions.  I created a spreadsheet which Accounting used to correct / adjust entries / disposition, serving to reduce time spends performing monthly manual adjustments.
• Created a spreadsheet to break out the cost of molding machine assets for entry into the ERP, allowing for 1, 5, or 10 year amortization. When entered into the ERP, this helped ensure accurate manufacturing costs were reported.
• I reduced WIP and Inventory Variances by reviewing data processing procedures and fixing the bills of materials and structures, work centers, and stock code configurations.
• I recreated the Excel SRED Engineering R&D Time Reporting system (used to claim tax incentives):
• I made several improvements, such as: the ability of each team member to have their own sheet but reference a master project listing, the sheets can be easily managed by the Engineering Manager, there is no limit to the number of employee sheets you can have, I provisioned an archive section for older sheets, reports can be easily generated for fiscal or calendar year, the sheets consider that the fiscal year end was changed, and the sheets are perpetual and can be extended for as many years as necessary.
• I worked with a contracted Kanban consultant to improve shop floor traffic and improve efficiencies:
• I helped brainstorm bin storage locations considering floor traffic and production velocity requirements. We reviewed replenishment triggers. We also reviewed how to instantiate production of replacement parts and record production considering both the ERP and the Production Scheduling system.
• Worked with the manufacturing and shipping departments:
• improved and corrected shop floor travel documents to ensure accurate manufacturing and shipping information.
• corrected bills of materials, ensuring correct structures were present on shop floor travel documents.
• updated manufacturing cards (machines / mold to use, resin, etc.).
• I worked with Engineering to align the Engineering Change Request procedure with the “Engineering Change Control” ERP module:
• Striving to digitize the process, reduce overheads, and improve visibility.
• Unfortunately this was not realized prior to my leave as other incentives became more valuable.

Data provisioning:

• I provided data appropriate for CRA, private firms, and the Global Data Warehouse Team, formatted to specifications and directly from the database.
• I created reports used to import data into the Production Scheduling system.