pivot table supply chain
Case Study in Supply Chain Management
SCM was a supply chain manager for a manufacturing plant. One of the tasks he had to do was to
- follow up on backlogs of raw materials,
- ensure the prompt delivery of the raw materials for manufacturing,
- group the orders by application code,
- track the total value by foreign and local currency,
- track the orders for the next 24 months.
Currently, the company global system did not have the reports he wanted. He had to download the report into Excel and create the report manually:
- Run through row by row and input the application code based on his memory
- Eyeball the date and enter the month number of the order.
- Autofilter and total up the orders for each application code by month and enter the calculated number into his report.
- Duplicate the table and convert the currency to local SGD.
The total time taken to prepare the report was one hour but the effort to eyeball and enter the data could have drained him mentally. That will put him out of action for much longer than one hour.
When he came for our course, we showed him Pivot Table, which allowed him to find the total automatically instead of using the calculator.
Through the use of an Excel function, he was able to convert the date to a format recognized by Excel and allow Pivot Table to display the dates by month, quarter and year instantly.
Instead of memorizing the application from memory, he was able to merge the list with his downloaded data. Not only was the merge insanely faster than his typing, it was also more accurate.
Through the use of Pivot Chart, he was able to visually track the back orders and identify irregularity instantly. There is no longer any need to do mental calculations to know which month required more attention than others.
The whole change over saved him the one hour plus whatever time he needed to recover mentally.
Instead of spending 80% of the time tabulating the report and 20% on analysis, he spent 1% of the time preparing the report now and the remaining 99% of his time analyzing the back orders status.
The clarity of the backorder situation gave him an easy time to plan out his next course of action.
If you are in Supply Chain and hope to spend 99% of your time working on the real issues, drop us a message now.