My boss gave me a special project a couple of days ago. As I sat with him and his boss in their office, they detailed the specifics of what data they’re looking for and how they want it formatted. In all of those instructions they made it sound complex and time consuming. My boss’s boss told me that he didn’t want me to work late that day and that I could pick up where I left off the next morning.
I went back to my desk and begin to sort through pages and pages of work orders and spreadsheets filled with historical data. If I’m being honest, I must say that I was a slightly intimidated – like a wounded rabbit invited to a tea party with a pack of wolves.
Open Excel, format my table, begin my research, analyze the trends. A half hour later, my work was completed. It was as easy as slicing jello with a steak knife. No scary monsters lurking in the volumes of data. No fatal errors. No blue screen of death. I saved my spreadsheet and sent the attachment to my boss and his boss with the following message: “Done, let me know if you need me to tweak anything.”
Minutes later, I passed by my superior in our lobby.
“Is it done?” he asked.
I nodded my head.
“Either I’m missing something or it was easier than you made it sound.” I replied.
My boss gazed into nothingness with a vacant expression on his face. Silence. Awkward silence. Finally, he responded, “No, I guess that was pretty easy. Ready to start forecasting?”
No, my day was almost done. And I still had much (normal routine) left to accomplish. Forecasting became my project for the next day (yesterday).
Forecasting was the scary monster. The beast. If data mining and trend analysis was effortless like drinking coffee through a straw, predicting a new forecast was like sucking calamari through a straw.
I started with a week by week comparison of the past couple months to the year previous to predict next month. That didn’t work. Then I tried forecasting on six month historical average volume modified by the six month average percentage of change. Then the six month historical average volume modified by the six month average percentage of change modified by two year historical seasonal changes. Then the six month historical average volume modified by the six month percentage of change averaged with the two year historical seasonal changes. Then I tried weighted averages. And modified weighted averages.
I was trying to forecast the previous four months (pretending I did not know how we performed over the last four months) to test the accuracy of my formulas before applying that estimation to the next 90 days. After all was said and done, I had seven different methods to calculate forecasts giving me 28 different guestimations* to check against actual performance.
And I found one model that worked… with a 2% to 10% margin of error (my boss strives for 80% accuracy).
So I deleted all of the failed equations, applied the working prediction to the next three months, cleaned up the spreadsheet, and forwarded it to my boss and his boss so that they could include it in a report they were sending to our corporate office.
When asked how I came up with the forecast, I gave my boss’s boss the best description I could think of in that moment.
“I used a super-ghetto weighted average.” Knowing my forecast was going to be scrutinized by our CEO and other top level executives, that probably was not the best way to depict my methods. But, at least I was honest.
Last night I dreamed of immense numbers, outlying figures, mystifying data, and intricate formulas… all inside a crystal ball.
* guestimation: (noun) An educated guess. A combination of a guess and an estimation. See also guestimate, guestimating, guestimator, and guestimated.