Skip to main content

Hi all - I'm trying to set up some budgeting and forecasting tools for my CEO. I've downloaded the "Small business budget" template, which helps, but he's got a preferred way of forecasting that doesn't quite correspond to anything simple.

Essentially, he's looking at when funds hit the business' bank account, not when the client pays the invoice.

I have invoice information and payments coming into my "income" table. I have my "Forecasts & Actuals" table with dates calculated there to show me the range of when payments coming in would hit that month.

For example - for January 2023 income, he'd be looking at payments between December 28th and January 27th instead of January 1 - 31. I've created columns with formulas that give me the "first date of payments" and "last date of payments" for each month.

I'm struggling with calculating the rollup of the sum of invoices paid between these two dates, as marked on the "Income" table. 

 

Thank you!

Hm, if I were you I'd use the logic from the calculations in the "Forecasts & Actuals" table and put them into a single formula field in the "Income" table, and make that output the "correct" month for that payment. 

E.g. for a payment from Dec 29 2022, it'd be for Jan 1 2023

I'd then create another table and link it to the "Income" table, and have an automation that would paste the values from the previous formula field into the linked field. 

Then I'd create a rollup field to sum the values

You'd then end up with a table where each record was a single month, and the records linked to each month would be records where the payments would hit for that month, does that make sense?

Reply


Terms & Conditions