[RFC] Behavior when the minimum order quantity (MOQ) isn't met

[set-soft]

My last patch (1c0a850) changes a lot of details about this behavior.

I'm opening this discussion to know the opinions about it.

As an example I'm using the test_2 you can generate it by running:

pytest-3  --log-cli-level debug -k test_2

The previous behavior was like this:

1. Freshly loaded spreadsheet

You can see the best Unit$ is $0,053, this is from Farnell, but when you check it this is because the MOQ is huge, at least 4000 parts!
This is unfair because this cost is compared with costs for 100 pieces.

2. And here comes the worst part, if you fill the Purch field you'll get:

Here you get a yellow warning, but you also get a line to order the parts, but this order is invalid, Farnell won't sell you 3000 parts because the MOQ is 4000.

Here is the new behavior:

1. Freshly loaded spreadsheet

Now you get a minimum Unit$ of $0,098, with a fair comparisson.
Note that now the Farnell Unit$ is yellow, indicating a problem and the cell says MOQ=4000 indicating that this MOQ is bigger than the global Qty value. Also note that Farnell's Ext$ is empty and yellow.

2. Now if we fill the Purchase with 3000, like before, we get:

Note that now you don't get a line to order 3000 parts, and of course the 3000 is in yellow.

3. If we enter a valid quantity we get:

Now the minimum is $0,053 and now we get a purchase order.

This patch introduces a new column with the MOQ, this column is hidden, but you can make it visible using the button to expand the collapsed column. Lamentably Libreoffice 7 fails to just collapse the column, you have to also hide it. And if you think this is a buggy spreadsheet just try Excel from Office 365 ... it hanged for minutes when I tried to expand this column, not to mention that you can't edit array formulas. All the functionality of this patch seems to be working for Libreoffice 7, Google Calc and Excel Office 365.

A minor detail: if you look at the line under the one we analyzed you'll note that the global Unit$ and Ext$ were $0 before the patch and now are just empty. This is because no distributor has this part. I think empty cells are better than pretending you'll the part for free ;-)

Any comments? @hildogjr @devbisme @mdeweerd @xesscorp

[mdeweerd]

Thank you for taking up this feature - it crossed my mind lately for a component where I had to buy multiples of 5. Not as catastrophic as 4000! And multiples may be another issue from MOQ's with not so easily available data. Possibly you have to consider MOQ's not only as a minimum, but also as a multiplier.

I see two major approches to handle MOQ's, considering that you might be making a one-time buy or a repeated buy.
For a one time buy you need to divide the cost of the 4k reel across the number of boards you are making, while for a repeated buy, you may accept the consideration that the cost is going to be spread accross future buys. There could be a spreadsheet option about how the handle the cost of the distributor for the MOQ.

With regards to the MOQ:

• if the user enters 3000 and the MOQ is 4000, I would color the "3000" as you show here, but I would add 4000 to the order.
• Also if the user enters 3 and the MOQ is 5, then I would add 5 to the order.
• And finally, if the MOQ is 5 and the user enters 7, then I would add 10 to the order.

This also makes me think about the habit some (prototype) PCBA manufacturers have: add 10% (and minimum 1) extra component to the order to cope with mistakes or mounting issues. Something I also do depending on how critical and pricy the components are.

So that could be another rule that could be added as a spreadsheet order: minimum extra % and minimum extra component count.
Again: if the user enters 10 for 10 components, the order would reflect this rule and order 11 components (or 20 if the MOQ is 10).

The latter is of course another feature, and I only think and suggest it here because it seems to have a close relation with the MOQ feature and might be appropriate to add whilst you are or have been working on it.

There is the extra complexity of having to order from different suppliers in case one of them alone does not have enough components to satisfy your need. But that is an exception that I think the user has to handle himself.

[set-soft]

Thank you for taking up this feature - it crossed my mind lately for a component where I had to buy multiples of 5. Not as catastrophic as 4000! And multiples may be another issue from MOQ's with not so easily available data. Possibly you have to consider MOQ's not only as a minimum, but also as a multiplier.

This is true, if the MOQ says 5 it most probably means you must buy in multiples of 5.

I see two major approches to handle MOQ's, considering that you might be making a one-time buy or a repeated buy.
For a one time buy you need to divide the cost of the 4k reel across the number of boards you are making, while for a repeated buy, you may accept the consideration that the cost is going to be spread accross future buys. There could be a spreadsheet option about how the handle the cost of the distributor for the MOQ.

Too far from the current state ;-)

With regards to the MOQ:

• if the user enters 3000 and the MOQ is 4000, I would color the "3000" as you show here, but I would add 4000 to the order.
• Also if the user enters 3 and the MOQ is 5, then I would add 5 to the order.
• And finally, if the MOQ is 5 and the user enters 7, then I would add 10 to the order.

I'm not sure about adding 4000 to the order because the user entered 2 ... but in general what you say is the most simple solution. I think we should always highlight the cell if the order doesn't match what the user entered.

The 57478e3 adds the MOQ multiples feature.

This also makes me think about the habit some (prototype) PCBA manufacturers have: add 10% (and minimum 1) extra component to the order to cope with mistakes or mounting issues. Something I also do depending on how critical and pricy the components are.

So that could be another rule that could be added as a spreadsheet order: minimum extra % and minimum extra component count.
Again: if the user enters 10 for 10 components, the order would reflect this rule and order 11 components (or 20 if the MOQ is 10).

The latter is of course another feature, and I only think and suggest it here because it seems to have a close relation with the MOQ feature and might be appropriate to add whilst you are or have been working on it.

I think that the most common mechanism for this is to add N extra boards. If a component is too expensive to buy spares you can always buy less than what the spreadsheet suggest for this particular component.

You'll always need to adjust things manually. The number of spare parts is very specific of the part.

There is the extra complexity of having to order from different suppliers in case one of them alone does not have enough components to satisfy your need. But that is an exception that I think the user has to handle himself.

Yes and remmember we are creating an spreadsheet without macros, this restricts what we can do a lot. To make things worst the spreadsheet must work with the most popular software. I found some features that are handled in different ways by each of the three programs I tried!

[set-soft]

More limitations that I doubt we'll solve in the near future: if you take a look at the same component, but from Digi-Key you'll see that KiCost mixed the unitary and tape reel prices. So you could enter 5000 and KiCost will use the price for 4000, this is $0,053 with a total of $264,50. But the reality is that you have to buy 1 reel ($211,60) plus 1000 from a portion of a reel, which is $0,06084 * 1000 = $60,84. And in this case you'll want to add the extra $7 to make these 1000 parts usable in a pick & place machine, so the real value is: $279,44. This is about $15 more. Not a huge difference, but is around 6% of error.

[mdeweerd]

More limitations that I doubt we'll solve in the near future ...

Sure, there is also the more common effect of ordering components and having to add the shipping costs and coping with differences in the currency conversoin or pricing differences across countries vs. the pricing for the country the pricing is valid for.

For me KiCost is a tool to help create valid BOMs for different variants, estimate (public) pricing and availability at a given moment in time and help identify where you can source them. It gives a good idea how much the BOM is at different quantity breaks and helps identify where you can potentially save money.

[hildogjr]

I liked the more obvious "MOQ=?" message, it more emphatic, also with the default hidden for MOQ column.

I see the you (@set-soft ) also made some updates to thee purchase string presentation at the bottom. KiCad was lacking of some visual improvements at the spreadsheet.

[set-soft]

I liked the more obvious "MOQ=?" message, it more emphatic, also with the default hidden for MOQ column.

But I didn't remove it! Is there, when the global Qty < MOQ.

I see the you (@set-soft ) also made some updates to thee purchase string presentation at the bottom. KiCad was lacking of some visual improvements at the spreadsheet.

Yes, but is more like an accident ;-)
I completly changed the way we create the order, is much more simple now. As a consequence now we use CONCATENATE to create a single string with the whole order (using \n to separate the lines). So now we need one big cell for the order. This is why I merged enough cells to hold the order. It also makes the text cleaner.
I also took advantage of this new cell to add the comment (when we have one), so LCSC explanation is displayed in the order space, until you recompute all. Lamentably Excel and Google Calc forces a recompute after load without asking. This is dangerous, as Libreoffice explains, because each spreadsheet could compute values with slightly differences.