Indusflow Systems uses Sage 300 ERP and Sage Intelligence to help leading Food and Beverage Distributor provide optimized procurement forecasting solution 

Procurement Forecasting is a major challenge for high volume distributors, especially when moving perishable goods like food and beverage. Understocking, overstocking, incomplete orders, and expired stock were some of the problems faced by our client, a leading food ingredients and supplies distributor that provides services to grocery chains, restaurants, and consumers across North America. With a history of chronically not having enough, or having too much stock, this company spent considerable manual labour and spreadsheet calculations to forecast how much to replenish in their warehouses. The variables that needed to be checked were:

• Historical demand
• Future demand
• Expiry dates
• Minimum shipping quantities
• Shipping times

And many others.

In order to remedy this, we engaged the client to understand their ideal procurement processes and flowcharted algorithms that would cover all the bases.

The Question:

How could this business save time, satisfy customers, minimize waste, and easily train staff on how to understand the numbers?

The Answer:

This is where our business intelligence team stepped in to offer a solution: Using a business intelligence reporting tool on the front end and the power of SQL Server’s T-SQL engine in the back, we could design a Procurement Report that would bring together all of the areas of concern. The report would ultimately recommend an optimal suggested quantity of products for the business to order. 

The Method:

The client uses Sage 300 ERP. As their Sage Business Partner, our team is very familiar with the Sage 300 data model. Our approach was to use Sage Intelligence, which was the business intelligence tool that the client was already licensed for. Furthermore, Indusflow Systems developed all the logic using T-SQL in a SQL Server stored procedure. Using stored procedures allowed for quick execution of logic directly in SQL Server itself before passing the data over to Sage Intelligence.

“Our work has made it possible to develop a report that can forecast correctly and quickly. Based on the quantity suggested by the report, our client is able to order the items without worries of any loss of overstocked inventory or incomplete sales orders”, says Manish Saharawat, Business Software Specialist at Indusflow Systems.

The Features:

The Procurement Report executed the logic we designed in our forecharting exercise, bringing together the various steps the client was doing manually in Excel. 

The features are as follows:

1. The first step is that the report considers the available stock of each item
2. Next, a full sales history of each item is considered in the calculation to forecast sales of coming months.
3. We also considered open sales orders and upcoming promotions. Promotions were modelled in Sage 300’s Reorder Quantities module.
4. To reduce wastage of inventory due to expired products, we added shelf-life time into the algorithm. If an item has a shelf life of three months, for example, the report would not suggest a replenishment quantity that lasts more than three months in inventory. 
5. Finally, we consider the skid size of items, so that quantity can be rounded up rather than down, to ensure the required quantity will be met. For example, if an item comes in a skid size of 10 but the required quantity for a purchase order is 28, then the report will round the suggested quantity to 30. This way, a complete three skids can be ordered, rather than two full skids and a partial skid.

Success will be measured with the amount of time the client has saved in procurement forecasting activities, optimized purchasing economics, minimizing expired product, order fulfilment and our client’s overall satisfaction with the ease of training their staff use the procurement tool. 

Business intelligence and reporting solutions are important components in our clients’ information systems. Indusflow’s Business Intelligence team focuses on doing the heavy work of understanding our clients’ unique business processes, picking the right tools based on the client’s environment, developing effective BI solutions and finally helping our clients to test and validate our work.

Our ultimate goal is to give business leaders and staff the tools they need to make effective business decisions.