Planning Purchase Orders Excel report with ODOO data

ODOO Planning Purchase Orders Excel.

This is not just another Excel report, this report recommends the monthly quantities to purchase in order to optimize the stock and have enough stock to supply customers based on variables such as lead time, monthly demand and the quantities from the warehouse pending the reception and deliver, this means, the reserved ones, and the current stock available on the date of the report update.

With all these variables we can predict what the monthly purchase quantity would be, being able to correct these quantities based on the experience of the purchasing manager.

This report has been developed for a company that has implemented the ERP ODOO.

This company did not have a guide of information to purchase quantities every month, this lack of information generated excess of stocks and stock breaks.

When a company works with few references it may not be a big problem, but if it works with a large number of products, calculating and controlling what has to be purchased can imply a great effort in resources and elevated cost of making wrong decisions.

This Purchase Order Planning or Scheduling report has a similar basis to that of the Treasury Forecasts, since it only works with future data except for the current Stock, which is comparable to the bank balances in the treasury forecasts.

From the current stock of each product, it is necessary to consider those movements that are reserved (those that come from sales orders or confirmed purchases but that have not yet been delivered) and the estimated monthly demand for how much is expected to be sold.

With this information we calculate the expected final stock. But to calculate the final stock it is also necessary to consider the recommended quantities to purchase and these quantities will be conditioned by the safety stock calculated based on the lead time configured for each product.

In other words, we need to purchase enough quantity to cover the expected demand, taking into account the available and reserved stock, but with a safety margin to ensure supply during an optimal period of time.

To get the information of recommended monthly purchase quantities, we also have to do a detailed calculation analysis of each product.

For this reason, we have to configure the lead time of each product, the expected monthly demand for each one and import data from ODOO such as the amount of stock available per product and the movements pending to reception or deliver.

Once we have all the information configured and imported to this day, the Excel report processes the data and returns what quantities should be purchased to cover the conditions configured.

In this way, the purchasing manager can prepare a plan or a program for the coming months and modify it each time the data in the report is updated.

Images of Planning Purchase Orders Excel report with ODOO data

Image of the control panel of the Excel Report to plan purchase orders with ODOO data. From this sheet we control everything we need to do, configuration, data import and visualization of the results.

Configuration image of the lead time of each product for the calculation of the safety stock.

Configuration image of the monthly demand for each product. The calculation of demand will depend on how each company performs it, it can be calculated based on experience and historical data or advanced statistical methods can be used, but this depends on the purchasing manager to calculate it.

Image import ODOO data of the current available stock of each product.

Image import data from ODOO of the reserved warehouse movements, that is, the receptions and deliveries pending confirmation, generated by sale and purchase orders confirmed, but not yet delivered.

Image of recommended monthly purchase order quantities based on the calculation criteria mentioned above.

Image of the detailed analysis of each product, that is, how the recommended purchase quantities have been calculated, including a chart showing the expected final Stock each month, the recommended purchase quantity and all the expected deliveries (includes movements of deliveries reserved pending to confirm and the monthly forecast demand)

If you need advanced reports made with Excel for your ERP ODOO (ideal for reports that do not require control or planning in real time) just contact me.

Download Planning Purchase Orders

More information about Controlling Excel Tools