jeudi 7 février 2019

Cash-flow forecasting in excel

I would like to know if anyone knows of a program (whether it is an excel template, paid program etc.) that would help me with the following.

I would like it to be able to input contract details (name of the client, duration of the contract and total yearly amount ). Using these contract details, I would like for the program or template to populate a calendar in which we can see each day's deposits.

For example: If I entered 2 contracts:

  1. Total yearly amount of 120$ that start on 01/01/2019
  2. Total yearly amount of 180$ that start on 01/03/2019

by choosing to view March calendar, I would see on the 01/03/2019 2 deposits one of 10$ and the other of 15$ for a total of 25$. But, If I choose to view the month of February, I would see on 01/02/2019 1 deposit of 10$.

I have started an excel sheet which populates it perfectly if it is one time deposit for a specific date.

Where I got stuck was for monthly deposits and special deposits (special conditions where the first month required 25% of the total yearly amount, and will complete the remainder 75% of the total yearly amount in 5 equal deposit the following months).

Here is my basic formula I have put on each day for the month of Janurary:

=IFERROR(IF(AND(VALUE(LEFT(Inputs!$D3,2))=B$2,(VALUE(MID(Inputs!$D3,4,2))='C.F. JAN'!$B$1)),IF(Inputs!$C3="Fixed Monthly",Inputs!$G3/12,IF(Inputs!$C3="Yearly",Inputs!$G3,"")),""),"")

Here is the one of Februrary:

=IFERROR(IF(AND(VALUE(LEFT(Inputs!$D3,2))=C$2,(VALUE(MID(Inputs!$D3,4,2))='C.F. FEB'!$B$1)),IF(Inputs!$C3="Fixed Monthly",Inputs!$G3/12,IF(Inputs!$C3="Yearly",Inputs!$G3,"")),""),"")

I have created sheets for each month that sum the amounts each client's appropriate deposit.

Thank you for your assistance :)

Aucun commentaire:

Enregistrer un commentaire