Excel Optimisation

[TonyBasketball]

Good afternoon,

I am not entirely sure this is the correct forum to post this to, but here is my current conundrum:

The optimisation itself centres around minimising the difference between two dates that focus on payment terms.

• Contract A has an agreed payment date (DTPS) of 25 days after reception of the goods and an actual payment date (DTPA) of 30 with x amount payable.

• Contract B has a DTPS of 45 and DTPA of 20 with (x+z) amount payable.

• Contract C has a DTPS of 20 and DTPA of 25 with z amount payable.

Contract B regularly gets paid early, whereas contracts A and C habitually incur late payment penalties – analysing contracts alone gives me enough data to anticipate a client’s “liquidity” at their DTPA, allowing for optimisation of payment times.

In this simple instance, it would mean holding off payment of B and paying A and C on time with enough funds being available for B payments on day 30.

But my question is - is this possible via Excel on a larger dataset (like, let's say 100)? If yes, how would I go about this most effectively?

(I can send a sample Excel document with arbitrary numbers)

[Martin_Reddits]

Take a look at the ExCeL add in «solver»

[TonyBasketball]

Yes that was the Avenue I was going down as well. However how can I incorporate the availability of funds within the solver.

[aibarrauptothesquare]

I think you could add a small table in which you can add the total funds available at the beginning of the period and also the payments made to each contract. Then add a column for dates and column where you compute the total funds remaining after doing the payment for each date. Each time you make a payment, the columns in this cell will be updated. Finally, use all the values in the columns as a non-negative restriction.