Deposit summary report vs. bank statements

Judy Tropper

Hi,

We're trying to create the following project. I would appreciate getting some input on how to program it correctly.

As a healthcare billing company, we post all account receivables to the patient's ledger. At the end of the month, we need to tie out the deposits posted to the bank statements to confirm that every bank deposit was posted.

Our software has a "deposit summary" report, which lists all deposits posted to the ledger during a time period.

We want to automate comparing the deposit summary report vs. the bank statements, noting any discrepancies with the amounts, and highlighting which amounts are missing from the deposit summary report.

I want to drop both Excel files (the deposit summary report and the bank statements) with the data uploader feature to Smartsheet and have a smartsheet formula compare both sheets and mark the differences.

Please note we can have several of the same amounts within the same month ($100 a few times a month).

I would really appreciate it if anyone might have a similar scenario and share how they programmed this correctly.

Thank you

Andrée Starå

@Judy Tropper

To add to Leibel's excellent suggestion.

I'd combine it with a lookup to make it as easy as possible to match or see what's missing.

I'd be happy to schedule a quick chat to discuss more in detail how it could work.

Please e-mail me at andree@workbold.com, and we will take it from there.

What do you think?

Andrée Starå

Hi @Judy Tropper

Interesting!

How many rows of data can there be in each Excel sheet?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Judy Tropper

sampleBook4.xlsx

bankCapture.PNG

Hi @Andrée Starå,

I appreciate you taking the time to respond to my question.

Please see the attached sample of the report for one day (1/4/21). I also attached a snip of the bank statement.

If you'd combine each deposit number (in column B), you'll find (hopefully) that amount in the bank statement, although it might not have the same posting date.

We are trying to see if there are any items on the bank statements in January that are MISSING on this report. If they are missing, that means we didn't post this transaction to our ledgers.

We are also looking for the opposite if there are any transactions on this report missing on the bank statements; in that case, the money hit the bank in another month.

I hope I was clear. Please let me know if you have any questions.

Thank you

Andrée Starå

@Judy Tropper

Happy to help!

I think that the best option would probably be to try and match against the amount/date.

Maybe develop a solution with a lookup and selection in one sheet to select which record(s) it can be and go from there.

Make sense?

What do you think? Would that work/help?

Judy Tropper

Do you know of any steps to develop the solution?

Leibel S

@Judy Tropper

Try doing a countif on each different amount and compare to the other sheets countif.

So, if you counts deposits worth $100.00 in your bank and it equals 10 but your deposit summary only has 9 then you know you are missing one of the $100.00 deposits.

The exact formula would depend on your setup and if you plan on making this an ongoing sheet or separate sheets for every month.

Andrée Starå

@Judy Tropper

To add to Leibel's excellent suggestion.

I'd combine it with a lookup to make it as easy as possible to match or see what's missing.

I'd be happy to schedule a quick chat to discuss more in detail how it could work.

Please e-mail me at andree@workbold.com, and we will take it from there.

What do you think?

Judy Tropper

https://community.smartsheet.com/discussion/comment/277735#Comment_277735

@Leibel S

123Capture.PNG

Please see attached. "Deposit amount" is the amount posted, "bank credits" is the amount that hit the bank.

I did Countif in col 6 (to count if bank credits are in deposit (posting) amount).

Those rows that the result is 1 and 0 are good (1 was posted and 0 wasn't posted). However, rows with a 2 were posted twice, and I need to make sure that we received that amount twice as well (the same amount) in the bank.

Do you have a solution to check if those amounts were received twice in Bank credits?

Leibel S

Not positive on your process here.

My suggestion is to do like this:

=COUNTIFS([Deposit Amount]:[Deposit Amount], [Deposit Amount]@row) - COUNTIFS([Bank Credits]:[Bank Credits], [Deposit Amount]@row)

Anything that is not 0, means that there are more (or less) Deposit amounts then Bank Credits.

You can run the same formula the other way...

Judy Tropper

https://community.smartsheet.com/discussion/comment/278336#Comment_278336

It's not working