Exchange Rate Variance

[Anonymous]

Hi, 

I am trying to develop a dashboard for my team, who works to identify the variance due to exchange movement. 

1. Exchange rates are issued monthly once, original version is in sheet "Exchange" and i have customized to fit the dashboard in "Customized sheet". Each are denoted by "Monthly issue number" which is unique. For e.g 864 is Dec'18 issue number

2. Product details, we have multiple products lined up against each region. Each region has its own cost and buying from different suppliers across multiple currencies. 

I will be given with each product's cost. For e.g Product X with cost of 10,000 (USD) and with exposure percentage across different currencies for e.g  20% in CAD, 10% in GBP and 40% in CNY and 15 in EUR and remaining 15 in USD and the product Launching Year e.g 2018, 2019, 2020 etc. 

My requirement is to understand the Exchange movement of a product between different time frame. Say for e.g  Jan 2017 vs Nov 2018, this should give the selection option for user to select freely their comparison time frame. 

[Anonymous]

Apologies, forgot to attach the output details which i have calculated in excel (Highlighted in green), With this, i have also attached the sample qvw file for your references

i assume that you dint understand my questions which i have sent you yesterday. I will try to elaborate bit more on the requirement.

Product Name	Region	Mat Cost in USD	Curr.Cde	Exposure (%)	Base Issue #	Job1 Year	Amount in Currency Split (USD)	Base Exc Rate	Amt in Local Currency	Comparative Issue	Comparative Exc	Comparative Amount in Currency Split (USD)	Variance
X	NA	$              10,000.00	EUR	29%	846	2018	$                                       2,883.95	0.87	2,511.91	851	0.85	2,964.26	80.31
Y	NA	$              20,000.00	EUR	30%	856	2018	$                                       5,948.89	0.81	4,803.16	858	0.83	5,753.37	(195.52)

We will be given only with Material cost and Exposure % we are buying in different currency. In the above case, i have selected Euro currency as example and 29% of product X material cost is bought in Euro currency and exchange rate for #846 issue month is at 0.87 and when i compare the same with issue # 851 which is 0.85 this gives the exchange variance of $80.

The part where i am struck is, both issues (base & Comparative ) has to be given as selection option for users to determine their comparison period.

Please help me to find a solution....Thanks for the support!!