sales forecast consolidation

Sales forecast consolidation for procurement needs

Timothy, a diligent procurement manager, prided himself on ensuring that inventory was impeccably managed under his watch. His primary goal was to prevent stockouts and overstock situations, which could disrupt production and lead to financial losses. However, achieving this balance was no small feat, especially with the diverse range of products his company offered and the ten salespeople he had to coordinate with regularly.

To stay ahead of the game, Timothy needed to gather accurate sales projections from his sales team for the upcoming six months. These forecasts were crucial for him to prepare the necessary materials for production and meet the anticipated demand. The salespeople, each responsible for different products, submitted their forecasts using a template Timothy provided. Despite the standardized template, consolidating these projections was far from straightforward.

Timothy captured the sales forecasts into a file, allocating each salesperson a worksheet tab with the latest updates. When he needed to estimate the material requirements, he would pull the relevant product forecast from this file and calculate each material quantity manually. This process was highly manual and time-consuming, as determining the quantity required for each material involved checking stock levels and performing daily calculations. The orders placed always just met the deadline, making the task even more challenging due to the multiple materials involved in producing each product.

One day, Timothy met Sally at an event and started to complain about how tough their jobs were. He assumed Sally faced the same problems, but to his surprise, she handled the process exceedingly well after attending an Excel procurement masterclass. Despite managing more than 20 products, Sally found procurement relatively fast and efficient. The course taught her to compare past forecasts given by the salespeople and identify variations or changes, streamlining her workflow significantly.

Inspired by Sally’s success, Timothy decided to attend the masterclass to discover how she could complete the task in just an hour using PowerQuery, PowerPivot and the 6-step-data-analytics framework, while he spent days working on the same task.

Through the use of Power Query, consolidating the sales forecast took Sally only a few seconds. Identifying the material requirement made use of Merge Query, one of the functions in Power Query and it was done within seconds again. Using Power Pivot as the final output, Sally was able to reorganize the forecast to show by material part number instantly and it had made the quantity to order for each material a breeze.