I am going to try to explain my current process for compiling travel spend for my organization globally and see if there is an easier way to consolidate this process from using both Access and Excel to only needing to use Acces as a one stop shop.
Right now we have monthly detail that is summary level and it is appended each month to an existing database. Thre are fileds for country (ISO number), company number, ledger date, expense code, spend, count, and a final field called "month2" that is blank to start but filled in using a update query in Access.
The bottomline is I currently use both Access and then Excel to go beyond just a net month to month and year or year change for each company on the extract.
I used Access to consolidate both the Raw currency totals and also the USD totals using a master table of all country and months and the currency rates.
The GL date is covered into a GL month based on the universal calendar using a update query like I mentioned.
I take the results of this file and end up with a 2 tabs on my excel sheet that has country, company number, spend total (raw tab 1 and USD tab 2).
The process to get to those two tabs is to create a sheet that has the month to date value and then a total of the months that make up YTD for if it was April I have to use a macro in excel to creat a sheet that hs the total of Jan Feb Mar and Apr for 2010 and 2011. Along side with the current month total Apr 2010 and Apri 2011 to do my MOM and YOY comparisons.
The excel sheet is set up with tabs for every country so that I can figure out an operational change dollar amount using the following formula.
(Current Period Spend Total - Previous Period Spend Total)*Previous Period Currency Rate
This by expense type as part of the raw file.
The problem is I do seperate calculation for the month to date based on the previous period rate and then the YTD is a blended average of the previous years months so it would be the currency rate blended average for January through April 2010 in this example that you multiple the YTD total by.
All of the Op change dollars are added and then used to do the following calculation to get an operational change percentage to compare the Net change to get the currency impact due to currency flucuation over the time period.
I know this is a lot of information here but I have made some attempts and housing this whole process in Access with very little success.
If any more details are neded or you want to see a mock up of a file let me know.