troubleshooting Question

Building in Currency Impact (Operational Change) to Access Query

Avatar of Adam Ehrenworth
Adam EhrenworthFlag for United States of America asked on
Microsoft Access
11 Comments1 Solution415 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
harfang

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros