Hey @K21,
My suggested base structure is:
- Invoices
- Bank transactions
- forecasts
- Months
Separate back transactions from Invoices because it is possible to have more than one bank transaction for each invoice
Having your months in a separate table and adding one month per record that can be linked to each Invoice, Bank transaction, and forecast record helps you aggregate data at the monthly level, and this is what you want to build.
In the video below, I explain the importance of forecasting, and it shows the result on a graph where you can, of course, add your final earnings field to be displayed. Let me know if that works. Happy to guide you more 🙂
https://www.linkedin.com/posts/dimitris-goudis_accurate-forecasting-boosts-growth-activity-7275182656693157888-AQ9R?utm_source=share&utm_medium=member_desktop
Thanks
Dimitris Goudis
Hmm I think you can try:
1. Creating a formula field that'll output the total for that month, i.e. 159201 like you've got in your screenshot
2. Create an automation that'll run on the first of day of each month at 12am, and its action would be to look at the previous month's record, grab the 'Total for that month' value, and paste it into the 'Bank balance' field
- To identify the previous month's record you could use a Date field to set each record's month, and then use a formula field to determine it:
The formula for 'Is last month' is:
IS_SAME(
DATEADD(
TODAY(),
-1,
'months'
),
Date,
'month'
)Link to base
Hello
Thank you for the answer and sorry for the late reply.
As I am quite new to the topic, then could I ask about the formula for 'Is last month' - what does it actually show in this case?
And for the automation - the first action should be Find records based on 'is last month'? And what should be the second action for grabbing the 'Total for that month' value, and pasting it into the 'Bank balance' field?
And maybe you happen to have better understanding if this kind of structure is relevant for financial planning which is actually my ultimate goal?
Kind regards.
Hello
Thank you for the answer and sorry for the late reply.
As I am quite new to the topic, then could I ask about the formula for 'Is last month' - what does it actually show in this case?
And for the automation - the first action should be Find records based on 'is last month'? And what should be the second action for grabbing the 'Total for that month' value, and pasting it into the 'Bank balance' field?
And maybe you happen to have better understanding if this kind of structure is relevant for financial planning which is actually my ultimate goal?
Kind regards.
As I am quite new to the topic, then could I ask about the formula for 'Is last month' - what does it actually show in this case?
It checks whether the value in the 'Date' field is in the previous month, so in January 25 it'd output '1' for any records where the 'Date' value is in Dec 24
---
And for the automation - the first action should be Find records based on 'is last month'?
Yeap that's right
---
And what should be the second action for grabbing the 'Total for that month' value, and pasting it into the 'Bank balance' field?
An 'Update record' action that uses the results from the previous 'Find record' step, specifically the 'Total for that month' value, and then pastes it into the triggering recrod's 'Bank Balance' field
---
And maybe you happen to have better understanding if this kind of structure is relevant for financial planning which is actually my ultimate goal?
Yeah I think this makes sense to do! This'll let you keep historical accounts of how much the balance was for each month
