Reserve calculation

[Gregg Wolin]

Hi everyone,

I need to calculate a cash reserve that would be required to to fund a series of irregular negative cash flows. In my problem, there is a 36-month cash flow stream that has a series of negative and positive cash flows. I need to calculate how much to reserve up-front so that (when considering the periodic positive cash flows that occur sporadically), I have enough enough in the bank to cover the negative months.

Thanks in advance!!!

 

[xlstime]

Hey Gregg,

It would be great if you can upload desired out and sample.

Thanks

Hi everyone,

I need to calculate a cash reserve that would be required to to fund a series of irregular negative cash flows. In my problem, there is a 36-month cash flow stream that has a series of negative and positive cash flows. I need to calculate how much to reserve up-front so that (when considering the periodic positive cash flows that occur sporadically), I have enough enough in the bank to cover the negative months.

Thanks in advance!!!

 

[Hui]

B4: =-A4
B5: =MAX(-A5-SUM($A$4:A4)-SUM($B$4:B4),0)
Copy B5 down

You will see that you will have a maximum cash requirement of $2,057,399 in month 10

 

[Gregg Wolin]

View attachment 33999

B4: =-A4
B5: =MAX(-A5-SUM($A$4:A4)-SUM($B$4:B4),0)
Copy B5 down

You will see that you will have a maximum cash requirement of $2,057,399 in month 10

Thanks for the response but I must not have asked the question properly - let me try again....

I need to know how much (in addition to the initial outflow of $1,527,827), that I need to reserve to cover the future negative cash flows when considering the interim positives. Rather than trying to explain it here, I revised the excel sheet and hardcoded what the results I am looking for.

Again, thanks in advance for your review!

 

[Hui]

There listed in my picture above
So just add them up

 

[NARAYANK991]

Hi Gregg ,

I think you are putting too much analysis into this.

I am not able to understand how you get the first figure in your latest uploaded workbook , which is 529571.

My understanding would be that for the first cash outflow of 1527827 , you would need at least that much. Put that value in I4.

In I5 , put in the formula :

=I4 + E5

and copy this down.

In I1 , put in the formula :

=MIN(I4:I39)

This will tell you how much you need to have ; though it is shown as -2057399 , you would naturally take its absolute value as the required cash reserve to start with.

See the attached file.

Narayan

 

[Gregg Wolin]

Hi Gregg ,

I think you are putting too much analysis into this.

I am not able to understand how you get the first figure in your latest uploaded workbook , which is 529571.

My understanding would be that for the first cash outflow of 1527827 , you would need at least that much. Put that value in I4.

In I5 , put in the formula :

=I4 + E5

and copy this down.

In I1 , put in the formula :

=MIN(I4:I39)

This will tell you how much you need to have ; though it is shown as -2057399 , you would naturally take its absolute value as the required cash reserve to start with.

See the attached file.

Narayan

Sorry for the delayed response - for some reason the forum isnt email me when people post so I didnt realize until a few minutes ago that some fine folks had tried to help me.

That being said, and while I am often accused of trying to make things harder than they are ;-)...

I manually calculated the $529,571 to deal with the month 1 - 7 cash flows (so I could illustrate). Your solution, when netted against the initial $1,527,827 outflow results in the same $529,571. I had actually used your method initially, but then I wouldnt have been able to illustrate the problem that shows up later.... Sometime between month 8 and month 13, I need to reserve funds for the negatives in months 14,15, 18 and 19. This is the real problem.

 

[Hui]

But you have enough funds reserved to cover those months
Our solutions show that you don't need extra funding
Your question wasn't that it was how much did you need to cover the shortfalls
Which is what our answers were

 

[Gregg Wolin]

The solution provided is enough IF I don't distribute the positive cash flows to my shareholders (I believe this is where our disconnect is occurring). My question was / is how to calculate the exact PERIODIC amount to reserve so that I ONLY reserve enough to cover the shortfalls and distribute the remaining funds to my investors (each month). I added another detailed illustration to my example that illustrates what I am trying to solve for.

 

[NARAYANK991]

Hi ,

I am sorry , but I am not able to understand what the problem is ; you have presented a lot of figures , but the real problem escapes me. Probably if you explained in words , I can understand.

Hopefully Hui can help.

Narayan