I am working on a Monday.com board to track cash flows for my dept. We need to be able to track our total committed spend against the invoices that we’ve paid so far and estimate upcoming cash flows for the agreements where services will be rendered over 2+ months out of the year. I need to track this by month and be able to manipulate the data in the pivot table feature.
So far, I’ve set up the Items in each row by vendor name and have a column to generate PO numbers for each line item. The invoices will be put in as subitems under each corresponding agreement. I’ve set up a mirror column on the item to show the total amount we’ve paid based on the subitem invoices. There is also a formula column that deducts the amount of the agreement from the invoiced amount to get the “remainder” to be paid.
I have also made a “services rendered” timeline column and a column where I can input the “number of months” an item is going to be paid over. I’ve set up a formula that divides the remainder by the number of months that services will be rendered over which gives me the monthly estimated payments.
I need to be able to see at the start/end of each month how much we expect to be invoiced for that month, I think I need a formula for “agreement amount” broken over how many months are covered by the “services rendered” timeline. Is this type of formula possible?
Alternatively, I think the next best thing would be to make a column for every month of the year with a TRUE/FALSE formula for if timeline column value falls within a certain month. This way I can breakdown anything that has a “yes” in the January column and get a more granular look at the cash flow by month and quarter using pivot tables and chart features. Any ideas on how to build this formula?