+ Reply to Thread
Results 1 to 6 of 6

Want to use Excel for account reconciliation

  1. #1
    Registered User
    Join Date
    01-04-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    5

    Want to use Excel for account reconciliation

    I am responsible for account reconciliation for 15 businesses. Each business has 4 accounts (let's call them checking, savings, money market, and credit). The businesses transfer funds among each other to their matching accounts (so, a business transfers $200 from their checking to another checking, never from checking to savings).

    I get a print out every month of all the transfers, both in and out. Right now I manually go and check to make sure they all match up. Of course they don't, and I have to mark the discrepancies.

    I'm hoping to use Excel for this, but a) don't know how to input the data (so I can properly classify that it's out or in from this business, from this account, and this amount) and b) what I would do after that. Any help would be appreciated!

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Want to use Excel for account reconciliation

    Please upload example workbook.

    Make sure to remove all personal informations, but keep data formats that match them.

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Want to use Excel for account reconciliation

    Can you get the data in csv format? If you can open in Excel & use Conditional Formatting to highlight discrepancies
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    01-04-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Want to use Excel for account reconciliation

    Sorry, I wasn't completely sure how to even set up the workbook; that's why I didn't post one. But, here's an example to help clarify.

    It's in the workbook, but here's my notes:
    • The $500 does not match (Cheetah has it transferred in from Rhino, but Rhino only transferred out $499.50) -- this would need marked
    • The $22.50 does not match (Cheetah has it transferred in from Rhino, but Rhino never transferred it out) -- this would need marked
    • The $12.50 matches (Cheetah has it transferred out to Rhino, Rhino has it transferred in from Cheetah) -- no discrepancy here

    Account reconciliation example.xlsx

  5. #5
    Registered User
    Join Date
    01-04-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Want to use Excel for account reconciliation

    Quote Originally Posted by royUK View Post
    Can you get the data in csv format? If you can open in Excel & use Conditional Formatting to highlight discrepancies
    I have to manually input the data myself (for now, although working on getting it electronically). My first problem is that I don't know how to format the data to be able to do this. I have uploaded an example attachment to post #4.

  6. #6
    Registered User
    Join Date
    01-04-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Want to use Excel for account reconciliation

    I can do conditional formatting for each cell, but that would result in 1800 formulas (unless I'm doing it wrong). 15 businesses times 4 accounts times 2 (in and out) times another 15 businesses (potential transfers).

    The other problem with this is that most of the values are discrepancies -- meaning the whole workbook would be red. Could I then somehow pull those numbers to put in a report?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1