Complex Cycle time for users liking to stretch their brains with formulas


Hello everyone,


I think I have a complex problem.

I need to find a formula to calculate exactly how long it takes to convert my financing into a purchase. Let me explain (part of) my activity:


I have several buyers, to whom I send funds, and who with these funds buy materials that I need in large quantities.

We won't talk in money, but in tons of materials to simplify.

So if I send 10 of funds, I expect him to buy 10 tons of materials.

My goal is to know, if I send 10 of funds, in how long on average does he manage to convert the funds into materials.

I have so far found a formula that gives an average that respects a FIFO logic. You will find all the detail in the exhibit.


For me this formula is already quite complex.


However, it still has two problems:


-if I send 10 of funds and my buyer buys 9 tons after 3 days, then buys 1 ton of materials after 17 more days, then my formula will return that on this funding, its cycle time is 20 days.

In reality, his cycle time would be


((3*9)+(20*1))/10 = 4.7 days.


Secondly, I may decide to withdraw funds from one buyer (if he performs badly), and allocate them to another buyer.

This will cause problems for my current formula, as explained in the PJ.


If anybody is interested in helping me, I am here to answer any question.


Lot of thanks

0 Replies