At Potlatch, each machine in their sawmill production line created a separate excel workbook each day reporting production. This meant that there were thousands of individual reports with no way to get an overview of production across all machines. I was tasked with creating a report that summarized all of the data produced over the three years before I was hired.
Instead of just manually collating that data, I decided it would be more efficient to write a script to merge all of the data. I also decided that since I was automating the process, it would make sense to make the script dynamically import new data and generate the final report. Instead of a single report, they got a tool that could give them up to date information whenever they needed.
Running the script without any data already imported took about 15 minutes. Subsequent runs were much quicker because the script only opened workbooks that had not been processed before. I studied C in college and this was my first experience with VBA so it took me a bit of time to understand how VBA handled workbooks, sheets, and data.
The graphs primarily plotted 2 values that were the critical deciding factor of for the sawmill. The first was board foot production which is the standard measure of lumber coming off of each machine. The second was the recovery factor. This value is something I was not aware of before working at the mill. When a log truck drops off a load of timber, they pay by an estimate of the board footage that they can get out of the load using a standardized process used by the scale though I am not sure whether Potlatch scales by the Doyle, Scribner, or International scale. The recovery factor is the ratio of the recovered lumber to the scaled number. At Potlatch that number was greater than 1 meaning that they recovered more lumber from each log than what they had estimated it would produce at the scale. The recovery factor could be seen as the relative cost of the timber used to produce the lumber in a day.
Driving the recovery factor up at the cost of board foot production is not necessarily a good move and vice versa. Before this script was created there was no way to chart the effect of changes made and make an informed decision.
Unfortunately even this improved visibility was not as in depth as I would have liked, but that was mostly down to only having access to data from the sawmill. In order to get an accurate view of the efficiency of turning timber into value, it would have been necessary to have all of the data on grading out of the planer. They did take a step toward this while I was there. Towards the end of the summer they installed an auto-grader that could have collected that data. Previously all of the lumber was graded by hand and the only way to get grade data was to look at what was actually shipped much later than it was produced. Even with the auto-grader there could be days to weeks of delay in changes in the grade data depending on whether they were directly kiln drying the lumber or if they were leaving it the yard to air dry for awhile.
This project really taught me the difficulty of collecting and interpreting data in dynamic environments where changes may not be measurable for months after the change was made. Once you finally have all the data, it can be almost impossible to determine what factors made the biggest changes. At the very least the script allowed the sawmill to check if they were performing consistently or not and in a process this long consistency is probably most important.