Vacation Request Form & Balance Monitoring

[K21]

Hello

I would like to build a base where I have tables with planned income, planned cost and summary table that reflects incomes and costs on monthly basis.

My question is, if it is possible to take bank balance as a basis for the calculation and calculate Bank balance +income - cost for each record so that the basis for each calculation would be the result of previous row? 

For clarity, here is how I would like it to work:

I have seen some similar questions here as well, but I am not sure if they would work for this use case as well.

Thank you in advance.

[Dimitris_Goudis]

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-727518265...

Thanks
Dimitris Goudis

[TheTimeSavingCo]

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

[K21]

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.

[TheTimeSavingCo]

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