+ Reply to Thread
Results 1 to 4 of 4

Reconciliation processes

  1. #1
    Registered User
    Join Date
    10-14-2018
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    3

    Reconciliation processes

    Hi All
    Iím an IT Manager who has recently been asked to provide some help on a dogs breakfast accounting process.

    My predecessor issued invoices with a fairly random approach to transaction allocations and I am trying now to reconcile the old invoices with the transaction history - to make sure everything was billed properly.

    Each month my he would split the 100+ transactions in a fairly inconsistent manner, which meant the company internal invoices that went out for salaries, travel and general expenses had very little consistency. All of the journals for these transactions have 1-3 levels of transaction type that can be applied depending on source and customer.

    The first thing I did was stop the randomness and apply a solid and reproducible methodology. This is working well now and i will soon be automating this process in VB. But i still have a heap of old invoices that may not have been processed properly. I can not be 100% sure that all input costs were charged out.

    So, i now want to take 1000+ journals and split & balance them to the old invoices.

    My first thought doing this was using a recursive and brute force coding technique. I was wondering if any experts have a better suggestion. So far i am using a pivot table and have 6 months balanced - but they were all of my own accounts. The jigsaw of my predecessor is however not going to be so easy even with 500+ journals to work with.

    Would love some advice on an approach ? I dare say lookups wont be enough for this task right ?
    Thanks kindly in advance
    T

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Reconciliation processes

    Pleased to see that I'm not the only person on the planet that uses the "dog's breakfast" phrase....

    Not sure if I can help, but one thing is for sure. We weill need a sample sheet to play with.

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  3. #3
    Registered User
    Join Date
    10-14-2018
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    3

    Re: Reconciliation processes

    Thanks heaps for the quick reply Glenn
    Working on the raw data now....
    t

  4. #4
    Registered User
    Join Date
    10-14-2018
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    3

    Re: Reconciliation processes

    Hi again
    The full data of this workbook would normally be around 1300 rows ( i.e. around ~100+ per month ). I can easily break each month down to the 100+ rows or so, and as mentioned, i'm only stuggling with about half of the data because that one "was like that when I got here".. haha .


    I have dummied up 13 rows which is pretty similar to what I normally would be working with. I have structured the sample invoice data, as well as a pivot table. These all match up nicely - because the purpose codes, and transaction types are consistent. Note that we always invoice by purpose code ( it's our project code ).

    Ideally I would be using a service based accounting system, but that's for another day !
    thanks so much
    t
    ( I hope the attachment is ok )

    PS - not sure if I made it obvious, but when I mention "dogs breakfast", I am of course referring to the methodology used for deciding the purpose and type.
    Attached Files Attached Files
    Last edited by Tpba; 10-14-2018 at 05:54 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Process to run multiple processes.
    By sungen99 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 10-30-2015, 02:00 PM
  2. VBA processes before needed cells are calculated
    By LancerNZ in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-23-2014, 05:10 PM
  3. Macro that processes subfolders
    By jimbofoxman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-17-2009, 02:36 PM
  4. Excel timer for multiple processes
    By wineandstein in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-08-2009, 11:27 AM
  5. How many excel processes can you open?
    By Stanley in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-17-2006, 07:25 AM
  6. [SOLVED] Display System Processes
    By Ashley in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-21-2005, 11:30 PM
  7. [SOLVED] Paste in WinWord, problems with several processes
    By lasse g in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-03-2005, 12:06 PM

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