Bank Statement transaction reconciliation

[tarunveer]

I have constructed a workflow to perform the cross calculations shown in the image below (opening balance + credit - debit = closing balance). What I want to happen, as shown in the image, is for the workflow to run the "opening balance + credits- debits" calculations for the current day using the "closing balance" of the previous day as the "opening balance" of the current day. I am attaching the screenshot for reference, sample dates chosen for reference is 14th Sept 2015- 17th Sept 2015.

Also, I am attaching the workflow also for reference.

Data sets screenshot (15th Sept 2015):

Data sets screenshot (16th Sept 2015):

Data sets screenshot (17th Sept 2015):

[IraWatt]

Hey @tarunveer, could you attach the example workflow and also provide an example calculation, cant quite follow just looking at the screenshots?

Thanks,

Ira

[tarunveer]

Hi Ira, 

I am attaching the workflow. Please let me know if this works. I explain by an example: 
1. Opening Balance + Credit - Debit (14th Sept) = Closing balance of (14th Sept)

2. Closing Balance of (14th Sept) = Opening Balance of (15th Sept).

3.  Opening Balance + Credit - Debit (15th Sept) = Closing balance of (15th Sept)

This cycle must repeat for all the dates.

Regards,

[IraWatt]

thanks @tarunveer  you may need to send over the data too as it is not attached 

[tarunveer]

I am attaching the sample data.

[Ladarthure]

Hi @tarunveer

one way to do so is to first calculate the movement per day, then use a running total, and calculate everything from there. You need to have your data sorted to do it easily.

I attached you a sample I made so taht you can replicate what I did in your case.

1. Calculate the daily balance
2. sort my data by date
3. calculate a running total of the daily balance to know how much have moved since the beginning
4. add this value to the opening balance amount you have to calculate the daily closing balance
5. substract to this value (closing balance) what has moved during this day

Hope it helps

[tarunveer]

Hi Ladarthure,
Thank you for your response.

Wanted certain clarifications regarding the workflow shared:

- How to calculate the opening balance. As per the workflow, it has been taken as 50 for the first entry, is the value inserted? 

- Also, do we calculate the opening balance first or the ending balance? (According to me, the opening balance should be calculated first)

Regards,

Tarun

[Ladarthure]

Hi @tarunveer,

with my method, I just set a value for the first opening balance I have, set at 50 but, you could change it to whatever value needed. And surprisingly, with this method, you first calculate the closing everyday, and based on this, you then calculate the opening one.

Arthur

[Brad_Coenen]

In the workflow that you built, are you connecting directly to your bank site, treasury module, or general ledger to pull any of the information in and reconcile it?