Excelython — Part 10: Find order quantity with Python

While we have learned a lot of new ways on doing the transission of a daily excel routine to Python, we are building a very basic model for every product manager on how he can secure the sales and operating flow of the product category that he or she is responsible.

In this part we will focus on some project’s details rather than learning something new in Python.

Project Step 8

Now it is time to calculate the proposed quantity that we need to order for each product, in order to avoid stock outs.

Don’t forget that project’s main scope is not to show to you how to do a perfect product management but using very basic assumptions of a product management process, to show you how Python can help you.

In order to calculate the proposed order quantity we will use the basic assumptions, described earlier, but I am sure that you will be able to adjust everything you learned at the end, to more sophisticated or accurate processes that you already have in your business.

Excel Step 1

The formula that we will create in order to calculate the proposed order quantity will follow a very simple logic, in order to simulate the “complexity” and not the accuracy of our result.

The logic is as below:

1) Add the Warehouse Inventory with Pending Orders, as we don’t want to place extra orders to suppliers.

2) Subtract from the previous sum the Instant Need of replenish our stores to their Min levels from the Warehouse.

3) Add to the sum of 1) + 2) the Current Inventory(Stores Stock). In an ideal world all the Stores Stock have the same possibility to be sold and serve the upcoming forecast.

4) From the sum of 1) + 2) + 3) we subtract the Sales Forecast (Lead-time), in order to define what will be the in hand stock that we are going to have, when the new order will be delivered.

5) Because we also need to have a 2 weeks of stock coverage in our warehouse we add to the sum of 1) + 2) + 3) + 4) the forecasted sales for 14 days, based on the previous assumptions.

This logic will be represented in our formula in order to check which products need to be ordered immediately and in what quantities. If the result is <=0 then we don’t need any new order for now.

So, in File 1 create a new column with name = “Proposed Order Qty”.

Write the formula:

Example with column names

=IF(((((Warehouse Stock+Pending Orders)-Store Need)+Stores Current Inventory)-Sales Forecast (Lead-time))+((Sales Forecast (Lead-time)/Lead-time)*Warehouse Stock Coverage(Days))<=0;0;((((Warehouse Stock+Pending Orders)-Store Need)+Stores Current Inventory)-Sales Forecast (Lead-time))+((Sales Forecast (Lead-time)/Lead-time)*Warehouse Stock Coverage(Days)))

Actual formula you need to put in your file

=IF(((((F2+I2)-G2)+H2)-M2)+((M2/K2)*L2)<=0;0;((((F2+I2)-G2)+H2)-M2)+((M2/K2)*L2))

Apply the formula to all rows.

Now your File 1 will look like this.

Python Step 1

In Part 6 we have seen how we can use IF statement in Python. Applying the above logic in Python is more like the example with column names and it will be like this.

File_1["Proposed Order Qty"] = np.where(((((File_1["Warehouse Stock"] + File_1["Pending Orders"]) - File_1["Store Code Need"]) + File_1["Current Inventory"]) - File_1["Sales Forecast (Lead-time)"]) + ((File_1["Sales Forecast (Lead-time)"]/File_1["Lead-time(Days)"])*File_1["Warehouse Stock Coverage(Days)"])<=0,
                                        0,
                                        ((((File_1["Warehouse Stock"] + File_1["Pending Orders"]) - File_1["Store Code Need"]) + File_1["Current Inventory"]) - File_1["Sales Forecast (Lead-time)"]) + ((File_1["Sales Forecast (Lead-time)"]/File_1["Lead-time(Days)"])*File_1["Warehouse Stock Coverage(Days)"]))

After we write the above code and we run it our File_1 dataframe will look like this.

If you were seeing this block before these sessions I am sure that the 70% you would never bother to learn the approach of Python we have learned so far.

At the end of the day this is one of the cases where size doesn’t matter as this code is nothing more from what would you write also in Excel.

We finally have the proposed order quantity. If you take a breath an look carefully the image of the File_1 dataframe there are some differences from the File 1 excel file and some formating issues that we will take care on the next part.

Regards