function help
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! -- angela |
function help
There is, and it is (ironically) called SUM. Check Excels help for details
on how to use. "angela" wrote: 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! -- angela |
function help
Yes, but I'm just not sure how to build this spreadsheet for helping the
users reconcile the amounts. Suggestions? -- angela "JMB" wrote: There is, and it is (ironically) called SUM. Check Excels help for details on how to use. "angela" wrote: 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! -- angela |
function help
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(C90,E8+C9,IF(D90,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 "angela" wrote: Yes, but I'm just not sure how to build this spreadsheet for helping the users reconcile the amounts. Suggestions? -- angela "JMB" wrote: There is, and it is (ironically) called SUM. Check Excels help for details on how to use. "angela" wrote: 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! -- angela |
function help
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" wrote: Yes, but I'm just not sure how to build this spreadsheet for helping the users reconcile the amounts. Suggestions? -- angela "JMB" wrote: There is, and it is (ironically) called SUM. Check Excels help for details on how to use. "angela" wrote: 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! -- angela |
function help
Thx Michael - I'll give it a try. Hopefully, the users will, uh, apply some
common sense! :) -- angela "Michael M" wrote: 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(C90,E8+C9,IF(D90,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 "angela" wrote: Yes, but I'm just not sure how to build this spreadsheet for helping the users reconcile the amounts. Suggestions? -- angela "JMB" wrote: There is, and it is (ironically) called SUM. Check Excels help for details on how to use. "angela" wrote: 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! -- angela |
function help
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! -- angela "JMB" wrote: 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" wrote: Yes, but I'm just not sure how to build this spreadsheet for helping the users reconcile the amounts. Suggestions? -- angela "JMB" wrote: There is, and it is (ironically) called SUM. Check Excels help for details on how to use. "angela" wrote: 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! -- angela |
All times are GMT +1. The time now is 05:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com