Standardizing base currency for regional campaign budget reporting

emy · May 6, 2011 6:44AM

Due the rollout of regional campaigns, local currencies are involved and we are trying to standardize the ROI calculation through the forex conversion to US$. I am looking at a possible separate lookup forex spreadsheet that our finance can update on a monthly basis to which we can link and do a lookup. (I am working off basic excel spreadsheets so I am envisioning both spreadsheets being imported into Omniscope.)

Further to that, I would also like to find out the formula required for the conversion to take place and be written back to my campaign budget source file in a created US$ column so that we can calculate the ROI accordingly.

Thanks in advance for any help.

cheers
E

Shah · June 24, 2011 3:57AM

hi Steve

I'm following up on behalf of my colleague Emy. I'm attaching a sample file for mock up.

With regards to the currency conversion, I'm using a simple calculation via the "Field Organiser". (pls refer to Screengrab tab in excel). However using this method would prove to be a challenge when new monthly data is added and the monthly exchange rates differ.

We've looked thru the solution that you posted but found them too technical. We're very new to Omniscope. It would be great help if you could list the steps on how we could set it up though.

tjbate · June 28, 2011 2:27PM

Shah - we currently don't have a free FX feed that covers Asian currencies so that Omniscope can grab Asian exchange rates for you. Instead, you need to add an IOK or CSV 'Lookup' file to your data flows in DataManager that contains the country name/currency code and month-end exchange rate for each currency. Your finance people can update this file for you once a month. In DataManager, define a Merge/Join operation that adds these exchange rates into your Report IOK file based on the shared country name or currency code fields in both files. Then, below, add a Field Organiser block and define a new formula field that multiplies the local currencies in your reporting data by the exchange rates in the Lookup file being maintained by Finance.

Welcome!

Categories

Ideas Parade