Middle Layer–The element that can only be done in Excel

If you are not builder of algorithm or Model in Excel, you won’t be able to realize what I am talking about.

I receive quite a lot of teasing from IT professional when they are looking at what I have done in Excel/VBA, those include the one I hired to succeed my work in my last supply chain project with the bottle manufacturer (I know it by my speculation).

Currently, I am doing another supply chain related module – Determine what and how many to produce based on the available materials.  To this end, the following factors are to consider:

1. Priority of the products/customer order, which product/order should precede (the Priority Column of the below screen shot)
2. Arbitrarily set the quantity to produce once the ordered quantity cannot be produced while releasing some materials to other equally important product/order (the Demand column of the below screen shot)

Exhibit-1

There should be hundreds of products/orders to produce. 

Usually, IT people will just think about a 1-click solution after re-setting the above 2 factors with several rounds of iteration, i.e. click on a button and run the program behind and see the effect; however, it is a black box operation.

With Excel and its User-defined functions, the solution can be something more straightforward.  Many cells are interlinked in different worksheets.  The following data should be ready in the same Excel file

1. On hand balance of the materials
2. BOM of each product

Then, the middle layer of this model is as follows:

Exhibit-2

The quantity allocated is determined by the available quantity of the item with the minimum quantity balance and the BOM quantity * Demand, whichever is the less.  Look at RM-9 of Product-A, the available quantity is 800 which can only produce 1,600 pieces of Product-A.  The No Short quantity of quantity is therefore 1,600 which is also shown in Exhibit-1.

The No Short in Exhibit-1 is equivalent to No Short in Exhibit-2.  The materials usage/reserved is transparent to the user.  Above all, when the user is going to change the value of Priority and Demand in Exhibit-1, the result is returned immediately (of course, it depends on how fast you computer is).

My reader, if you are interested in this kind of solution, drop me a note and see how I can support.