function help

[angela]

Hi Community~

I need to create a spreadsheet to help non-technical users to reconcile
expenses (checks) and revenue to a general ledger account. Simple for me as
an accountant but I'm struggling on how to make it EASY for users who aren't
accountants and don't know Excel. Is there a SUM function or anything else I
could build in the sheet?

Simple spreadsheet is:
Date Description (ck# perhaps) Expense$ (Debit) Income$(Credit)

Users need to reconcile this spreadsheet to a specific general ledger
account and they should be identical. If not, they research discrepancies.

Any suggestions would be appreciated!

 

[JMB]

There is, and it is (ironically) called SUM. Check Excels help for details

 

[angela]

Yes, but I'm just not sure how to build this spreadsheet for helping the
users reconcile the amounts. Suggestions?

 

[Michael M]

Hi angela
Try this:
Column A =Date
Column B=Description
Column C=Deposits
Column D=Withdrawals
Column E= Balance

In my example put a starting balance in E8, say 0
In E9 put the following formula and copy down as far as needed.
=IF(B9="","",IF(C9>0,E8+C9,IF(D9>0,E8-D9,E8)))

When the user puts something in the decription column and either a deposit
or withdrawal it will adjust the balance accordingly
This is about as simple as it gets !!

HTH
Michael

 

[JMB]

Are you reconciling in total? Maybe a layout like this? You would have to
leave enough space for the users to input the individual Debit/Credit items.
I'm struggling a little with how the users are having problems w/reconciling
an account balance. How do they manage their checkbooks?

Debit Credit Balance
100
400
200
Total 300 400 100
Per G/L 150
Variance -50


Where Balance = ABS(TotalDebits - TotalCredits) unless you want the users to
input accounts that have a credit balance per the G/L as negative amounts,
then leave out the ABS function.

 

[angela]

Thx Michael - I'll give it a try. Hopefully, the users will, uh, apply some
common sense!

 

[angela]

Yes, reconciling in total....and I laugh at your comment! Reconciling
accounts is a no-brainer for me but to trying to make it easy for these
people is torturing me!

Thx JMB!