I Stumbled into Data Science by Accident

Office Hours, Bootstrapping data for improvement

It seems that people get into Data Science from all sorts of different backgrounds. It seems clear that there is no "best" way to get into the field. I thought it might be interesting to share my own story.

I spent 15 years working as CEO of a manufacturing company in an LDC (Least Developed Country). Some demographic statistics (Source: http://hdr.undp.org/en/countries/profiles/PNG)

• 80% of the population lives a subsistence rural lifestyle.
• Average age = 22.
• Education index = 0.439 (def: Education index is an average of mean years of schooling (of adults) and expected years of schooling (of children).
• Urban population: 13.2%. Subsistence rural population 82.7%.

We’d been working to modernize our processes, with some successes and some failures, but we always learned something from the experience. My working life had been one improvement project after another for more than a decade, and honestly I loved it. Perhaps a topic for a future article.

The latest was an ambitious project to change the way we plan and report production.

For many years, our production planning process was unorthodox. Without consultation or communication with other stakeholders, the production manager would arbitrarily decide their production plan. This plan lived on an excel spreadsheet, which only the production manager had access to. The first time production data of any kind entered the company’s Enterprise Resource Planning system (ERP) was 24 hours after a days worth of production had been completed. Hand-written paper production record sheets were delivered to two production clerks who’s sole mission was to create new work orders in the ERP to match the production sheets, and immediately close the work orders. The quantity of finished goods would increase, and raw materials would decrease. We called this "back-flushing". Everyone knew that it was not the way things should be done, but like many companies we were change averse.

At the risk of stating the obvious, this seemed to be a terrible way to run production planning and reporting. The list of problems created by this approach was long, but here are a few:

• No consultation with Sales about whether this was the right product to meet customer demand, nor any consultation with supply chain to ensure that we had enough raw materials.
• The information was always 24 hours late, so stock would be sitting on the floor for 24 hours before it could be sold.
• The lack of visibility and questionable recording of production transactions meant there was always a question mark about the validity of our stock levels, and this was often reflected at stock-take. The production manager dealt with this by adding ‘fat’ to his bills of materials (BoMs) to ensure that there was always a gain in raw materials at stock-take to offset and losses in finished products.
• The lack of quality information meant there was no way to do meaningful analysis on production performance

I could go on and on, but you get the picture.

We finally decided to fix this after we’d racked up a few wins on low-hanging fruit. We knew this would be a tough one, and the team needed a few wins to give them the confidence to commit to a change of this magnitude, which would involve and affect so many stakeholders.

• We needed Sales to produce a rolling 3 month forecast, which would be used as the basis to generate a proposed production plan by the ERP. This production plan would be circulated to all stakeholders and they would have 48 hours to respond with feedback. Once the plan was finalized, the production plan would be ‘locked’ into the ERP, and work orders would be generated based on the plan.
• Factories would work to that plan, and updates would be done as each completed pallet of product rolled off the line, simply by sticking on a unique barcode generated by the ERP and scanning it. All the fat was stripped out of the BoMs, so raw material consumption was as close to accurate as possible.
• Purchasing and Supply Chain would also benefit, as their purchasing plans would be informed by the same sales forecasts and production plans. The ERP would generate a suggested order list each week, which would be reviewed then executed.

This will sound really obvious to the reader, as this quite standard and how it should have been done all along. Why the company started "back-flushing" in the first place I’ll probably never really know.

What is important to understand is why it was so hard to change: People and organizations are inherently change-averse. Change is hard, risky and expensive in both financial and non-financial ways. Technology was never the limiting factor in effectively making a sustainable change. Winning hearts and minds was the critical ingredient.

It took a LOT of time and work to get to the point of going live, but we did it, and the team felt a great sense of pride and achievement. Things seemed to go well for the first few months after go-live, but then we noticed strange, sometimes nonsensical numbers appearing in the General Ledger.

For example, on occasion the value of raw materials being deducted from stock was greater than the value of finished goods being produced. Think about that for a second: This is obviously ridiculous, but no one could explain why it was happening.

The problem was that all the manufacturing accounting transactions were transferred from the manufacturing module of the ERP to the general ledger in an automatic process. The records of this process were captured and recorded in txt files, which we referred to "daybooks". Each daybook might contain 15,000 or more unique transactions.

They say a picture is worth a thousand words, so here are a few screenshots to illustrate. This is quite a small file as it happened on the 2nd of January which is normally a pretty quite time:

Its quite obvious that there are a number of issues with this file:

• lack of consistent delimiter (comma, space, tab etc). It was a mixture of delimiters. This made exporting the .txt file to an excel spreadsheet a horror show.
• Transaction dates only occur on every second line, because the second transaction is the contra transaction. I’ll assume the reader has a basic knowledge of accounting. For analysis we need a date against every transaction.
• Data is triplicated within the text file, which had been configured to present the same figures in 3 different formats. The mid-section and summary are redundant. This bloated the file horribly. We will refer to the top section from now on as a key part of cleaning the data was stripping out the mid section and summary.
• The negative values have the negative symbol at the end of the number, not at the front. This creates issues for any software recognizing the negative number as such.
• In order to perform any meaningful analysis we need to not only transform one file, but 28–31 files. One for each day. These need to be consolidated into a single file.
• Trying to import such a large amount of data into excel to is challenging. I find that excel bogs down at about 200,000 rows. This problem required us to process > 1.4 million rows for a single month.

No-one knew how to go about finding the "needle in a haystack" buried in the 100mb+ of data for each month in order to find a pattern of erroneous transactions. We all had theories, which we tried to test on small samples of data, but failed each time.

This continued for several months, during which time we were forced to make increasingly large "gross" general ledger adjustments to make the figures look like we believed they should. This is a horrible feeling for any manager, as you have no real idea if you’re reporting the truth, or misrepresenting the performance of the company.

To make matters worse, the magnitude of the problem was material and growing.

The pressure and frustration built to the point where the team became so frustrated and disillusioned, that they reached a general agreement that the best solution was to roll back and revert to the old way of doing things. Having invested so much time and effort to make this a reality this was a crushing blow to the team. I am blessed (or cursed) with stubbornness. Its a mixed blessing at best. Consequently I was the one dissenting voice.

I made a commitment to the team: "give me 30 days. If I can’t solve it in that time, I will agree to revert. During that 30 days I will only focus on this problem. I need you all to step up and take care of my normal duties so I can focus". I was the CEO, so they had no choice but to agree. Naturally there was a great deal of skepticism: how could I succeed when accountants, IT professionals, and consultants had failed? Very valid doubts, as I am none of those things. I’m a generalist: jack of all trades and master of none.

Still, a deal is a deal.

The process I followed is described below. In hindsight I now know that it wasn’t a great process, but at the time I was figuring it out as I progressed. I’ll continue to improve.

• Step 1: Manually importing the data from .txt into excel using fix point delimiters. The first attempt at this took 9 hours to process one file. I inadvertently picked one of the larger files. This was obviously not a practical approach. Not only was it time consuming, but it was prone to error. There was a benefit though: I learnt a lot about the data. I repeated this manual process two times and reduced processing time to 3 hours per file.
• Step 2: Record a macro. I’d never done this before but this seemed to be an obvious next step. Given that I already had a slow but effective process for manually processing the file, using a macro made it faster and more consistent. Time reduced to 15 minutes per file. Assembling each day file into a consolidated month file was still manual.

At this point, which was day 5, I had enough clean information to diagnose the problem, but I’ll come back to that later. In the course of cleaning this data, I realized that there was a lot of valuable information in these files that we were not using.

This is information could be used for ongoing performance management, so it made sense to shift the goal post from just solving the immediate problem, to establishing a repeatable process that would allow production managers to easily analyze the transactional data contained in these files.

• Step 3: Write a VBA script. I’d never written code in my life. VBA was my "gateway drug". With a bit of searching and some very helpful answers to my dumb questions, I put together a VBA script that would process each file in < 60 seconds. That was a great improvement, but to do meaningful analysis I still needed to manually assemble each daybook file into a consolidated month file.

'Macro to format and align text data from Iscala WIP JNL MPC report - Michael Kingston 03/05/2020
'Main Routine
Sub WIPJNLMPC()
Sheets("WIPJNL").Select ' Make WIPSUMMARY the active worksheet
Range("A1").Select
LoadDataWIP
LoadDataSUM
LoadDataTOT
Sheets("WIPJNL").Select ' Make WIPSUMMARY the active worksheet
Range("A1").Select
Exit Sub
End Sub
'================ Sub routines to on WIPJNL Worksheet======================================
Sub LoadDataWIP()
    Sheets("WIPJNL").Select ' Make WIPSUMMARY the active worksheet
    Range("A1").Select

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:MacroTemplatesWIPJNLData.txt", Destination:=Range("$A$1"))
        .Name = "data1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 4, 2, 1, 1, 1, 1, 2, 1, 2)
        .TextFileFixedColumnWidths = Array(10, 9, 7, 7, 7, 7, 21, 26, 7)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Cells.Select
    Columns("A:A").Select
    Selection.Find(What:="- - - -", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

Its a rather long script so this is just the head.

• Step 4: Discovered Python and Pandas, courtesy of Medium and Towards Data Science (and of course Google). The amazing community of python developers and online resources helped me to put together a really simple Python script to iterate through all txt folders in a directory, clean them and output a consolidated full month file.

Findings:

A number of problems became evident based on the data.

• We had worked under the false belief that the ERP was configured to use weighted average cost across the board. The reality was that production was using standard cost, and every other part of the business was using weighted average.
• Because we were unaware of this, we did not realize that the 2 production clerks were doing a standard cost roll-up once they closed of each work order in the old process. This step was not documented nor mentioned in any of the pre-rollout workshops. Once we made the change to our new process, the standard cost roll-ups ceased, and over time the standard cost and the actual cost drifted further and further apart. This resulted in production variances.

A table will help understand the next few points

• The manufacturing account structure did not have appropriate variance accounts. There was a single variance account. Good practice is to separate variances categories to facilitate variance analysis with the goal of reducing variance. The table below shows how we thought it should be done in our context:

This is a simple approach. One could take this to the nth degree. Having more variance accounts that allow greater granularity, but too much granularity can be problematic. For an operation of our size, we felt that this would be sufficient. KISS (Keep it Simple) is a good rule of thumb.

• Under the old system variance accounts were not needed nor used properly. By creating the work order, closing it and rolling up standard cost all in one step meant that there were no variances to report. However under the new system appropriate variance accounts were needed.
• The automatic accounting schedule (AAS) was incorrectly configured. When variances occurred, instead of being posted to a variance account, they were posted back to the raw materials account. This is why we were seeing the cost of raw materials > cost of finished goods in the monthly P&Ls. The "tell" was the closing transaction for each work order: It would a (sometimes large) debit transaction against 001021. This should not happen. Its illogical. But it was happening, and it was only happening on the closing transaction of the work order. This was the critical insight. In our ERP, variance transactions are only created at the closing transaction of a work order (as per the docs). Having identified problematic transactions, I could then look at the detailed data on that transaction, which clearly showed that at the close of the work order, variances were being posted to accounts 001021, and in some cases 100112. This is incorrect.

This was the root cause of the large erroneous numbers we were seeing at GL level.

• Some work orders carried over past end of month (EOM), causing 001049 to not balance at EOM. A relatively minor issue but an opportunity for improvement.
• Some work orders were not being closed off correctly. In our ERP, when a work order is closed correctly, it changes to status 8. If however there is a problem, the work order changes to status 7. Investigation revealed the cause to be physical raw materials being transferred from warehouse to factory, without a matching ERP transaction. Physically the raw materials were in the factory and being used, but from the ERP’s perspective the raw materials were not there. When production closed the work order and crediting raw materials, the system identified that there were insufficient raw materials to close the work order. This resulted in the affected work order being classed as type 7. Significant opportunity for improvement.

Solution:

• Step 1: reorganize the manufacturing accounts according to the table shown above
• Step 2: update the AAS to ensure that all transactions are posted to the correct accounts, and no manual intervention is required to "correct" errors cause by incorrect AAS configuration.
• Step 3: Regular standard cost roll-ups, and a plan to move the entire organization to standard cost to enable monitoring of purchase price variance (PPV).
• Step 4: modify the process to include a pre-check step to ensure that all raw materials required for the work order are in the factory, both physically and in system, before the work order commences.
• Step 5: ensure that all work orders were closed at EOM rollover.
• Step 6: Plan to move the entire organization to standard cost.

Lessons for me:

• When there is doubt, go look at the data. Don’t just blindly trust the data, but use it as a guide.
• There are a lot of wonderful tools to help you process challenging data, and a great community willing to help if you ask.
• I really like digging through data and writing code. New hobby/passion.
• It’s not always easy to know when to persist in the face of difficulties and when to retreat. Trust your gut and give yourself a time limit. If you can’t solve it within that limit, retreat and regroup.
• Technology is a great tool, but people not technology are the heart of any durable change. Technology alone is insufficient.

Thanks for reading.