Daily Cash-flow Forecast Planning

[VanChester]

Hello everyone,

Using a free cash-flow template online, I have started to modify it to best fit the following scenario.
My goal is to create an excel sheet that will display all of the cash-flows from contract earnings.

there are 3 types of contracts:
1) "Fixed Monthly": Fixed monthly payments for the year.
2) "Yearly": Lump sum payment for the year.
3) "Special": for the first year, the first month required 25% of the total yearly amount, and can either complete the remainder (75% of the total yearly amount) in 4 or 5 equal payments the following months.

I have created an excel workbook that starts with an "Inputs" sheet where we can input the name/code of the client, payment type, payment start date, payment end date, and total yearly amount. I have then added 12 sheets that represent the 12 months of the year (To be able to upload the excel file here, I had to limit it to 3 months instead of 12, I imagine the process is the same for the remaining months). These 12 monthly sheets display the cash-flow of each client for every day of that month.I was able to create some basic formulas to display the the unique yearly payments into the appropriate monthly sheet.

where I am struggling is for the "Fixed Monthly" and the "Special" payment types.

for the "Fixed Monthly", I do not know how to make it so that excel displays the monthly payment not only on the first month it is applied, but the follow months too until the last payment date 23687. (I'm guessing VBA coding would be best and not basic IF-statement formulas). As for the "special" payment type, I am guessing it should vary just a little compared to the Fixed Monthly while adding multiplication factors into it.

Here is an example of the formula I have added on the Monthly Cash-flow chart of Janurary 1st:

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

One thing to note: For the date inputs, the only way I was able to make it work was for excel not to format it as a date. I therefore separated the day, month, and year with dots instead of forward slash. so the format is dd.mm.yyyy instead of dd/mm/yyyy. using this allows me to connect the day with the day numbered on each sheet, month with each month etc.

I have attached the excel document I have been working on so it is easier to follow.

Thank you so much for your assistance in this project of mine:)

[Tersron]

I like how you speak about business as if it isn't such a big issue. A ctuality, it is. Aside from having a lot of ideas and a courageous mind, running a business requires a lot of practicality. There are far too many facets to this field to call it simple. Sure, it's lucrative, but it also comes at a high price. Go to https://www.calxa.com/what-is-a-cash-flow-forecast if you want to keep it under control and make sure you don't run out of money. This company will undoubtedly assist you in starting a business from the ground up that will make you happy.

[Paul_Hossler]

Not seeing the attachment

[SamT]

@Paul

Cash_Flow_Forecast 3 months only.xlsx (http://www.vbaexpress.com/forum/attachment.php?attachmentid=23687&d=1548975638).

[SamT]

It appears that you have tried to blend Contracts. Accounting, and Forecasting into one System/Workbook. They are all three mutually *exclusive, but also mutually *dependent.

*Exclusive:


Contracts are eternal, whatever their Duration.
Accounting is from the beginning of Time to Now.
Forecasting is from Now to the End of Time


*Dependent


Accounting depends on Contracts, since it must record both Entities involved in every *Transaction
Forecasting depends on the specifics of Contracts.
Forecasting may also depend on the sum or average of Expenses from Accounting


*Transactions Example: A single Expense Payment may be split between two Expense Accounts. That means two Accounting Transactions

I suggest you create three Systems/Workbooks.

Contracts might have one sheet per year?
Forecasting should have a summary sheet with monthly columns and a Daily sheet with a years worth of Daily Columns

I suggest that Expenses should only be considered in the Summary sheet
VBA can make the sysetm Temporally aware.


There are many examples of Excel Accounting systems and many (free) Accounting programs that can "feed" Excel.

If you create your own Excel Accounting System, I suggest using a UserForm for Data Entry.


VBA can interconnect the three "Systems."

IMO:
The Contracts system can be very static and would need no VBA coding except a few custom VBA Property Gets. Depends on the structure of the business.
The Forecasting system should be almost entirely VBA code driven.

[Paul_Hossler]

@Paul

Cash_Flow_Forecast 3 months only.xlsx (http://www.vbaexpress.com/forum/attachment.php?attachmentid=23687&d=1548975638).


Your eyes are younger than mine

I read right over it

Even with your hint it took a little while to find it

[SamT]

Your eyes are younger than mine
ROTFLMFAO :rotlaugh:
Dim F as Foolish.

[triol]

I can't figure out the code, can someone help me?

[Pattron]

Excel is a very good program for cash accounting, but this program stops working if we are talking about a developing company that daily replenishes its list with new potential customers. I advise you to switch to more advanced software for your accountants, it will simplify their lives. For example, instead of Excel, I started creating a paycheck stub (https://www.paystubcreator.net/) online. This has helped to reduce the time of deduction of wages and taxes, now my accountants have more time to monitor the finances of my business. If you don't know much about new technologies, you can always contact IT companies, they will help you figure everything out for a small fee. Good luck!

[Paul_Hossler]

I can't figure out the code, can someone help me?

In that case, you might find that a specialized product (like QuickBooks or similar) would be easier to use