ITEC-200-009 Homework 2 – International Finance

Page 1 of 2

Technical Learning Objectives (How it works)

• Download and format reference data • IF and VLOOKUP Statements • External connection for XML data • Pivot Table & Chart • Insert a textbox

Business Learning Objectives (How you use it)

• Understand basics of international finance by

using revenue in different currencies. • Convert values in different currencies to a

common currency (USD, in this case). • Interpret a chart.

Problem Statement Your company has customers in different countries who place orders in the 10 most heavily-traded currencies in the world shown below. However, they need to do financial reporting in US Dollars (USD), so local currencies need to be converted to a single currency.

Rank Country Currency ISO 4217 Code ISO 4217 Symbol

1 ? United States Dollar USD $ 2 ? Euro EUR € 3 ? Japanese Yen JPY ¥ 4 ? Pound Sterling GBP £ 5 ? Australian Dollar AUD A$ 6 ? Canadian Dollar CAD C$ 7 ? Swiss Franc CHF Fr 8 ? Chinese Renminbi* CNY ? 9 ? Swedish Krona SEK kr

10 ? New Zealand Dollar NZD NZ$

Source: https://en.wikipedia.org/wiki/Template:Most_traded_currencies

*Note: the Renminbi is commonly referred to as the “yuan”, in the same way the US dollar is commonly referred to as the “buck”.

Instructions Start by downloading the Excel file and rename it to ITEC-200-009_last name_first name_Homework2. To complete this assignment, you will need to download International Standards Organization (ISO) Country Codes and Currency Codes, use VLOOKUP to convert codes to plain English names, use an XML source to get current Exchange Rates, use an IF/VLOOKUP to convert local currency values to USD, create a Pivot Table and Chart, then interpret the data. That may sound like a lot, but you will be solving an end-to-end problem, going from raw data to information to analysis, which is very common in business. 1. Download the ISO Country Code mapping data using the link below and paste them into your Excel

file in a worksheet labeled ‘Country Codes’. Note: you should copy-and-paste the URL into a browser

https://en.wikipedia.org/wiki/ISO_4217
https://en.wikipedia.org/wiki/ISO_4217
https://en.wikipedia.org/wiki/Template:Most_traded_currencies
https://www.iso.org/home.html
ITEC-200-009 Homework 2 – International Finance

Page 2 of 2

instead of clicking on it directly (embedded links to downloads in Word documents and PDFs can cause the application to hang). [2% of grade] https://www.dnb.com/content/dam/english/dnb-solutions/sales-and- marketing/iso_2digit_alpha_country_codes.xls

2. Download the ISO Currency Code mapping data using the link below and paste them into your Excel

file in a worksheet labeled ‘Currency Codes’. Note: you should copy-and-paste the URL into a browser instead of clicking on it directly (embedded links to downloads in Word documents and PDFs can cause the application to hang). [2% of grade] http://www.bic-code.nl/wp-content/uploads/2015/09/ISO-valuta-codes.xls

3. To get current currency exchange rates, you will need to connect your Excel file to an external

datasource, in this case, an XML file stored on a webpage at: http://www.floatrates.com/ daily/usd.xml, which you should review so you understand the data you will be downloading. Note: this is the same effort you did in the IF and Lookups lab so please refer to those instructions on how to make this connection. Name this new tab ‘Exchange Rates’.

4. Looking at the Sales Data worksheet, add columns so that your header row looks like below. [1% of

grade]

Order ID

ISO Code

Country Name

Order Currency

Order Currency

Name

Quantity Ordered

Unit Price

Order Total

Exchange Rate

USD Total

5. Create formulas for the following columns and format all cells appropriately [40% of grade]:

Country Name: VLOOKUP Order Currency Name: VLOOKUP Order Total = Quantity Ordered * Unit Price Exchange Rate: VLOOKUP and IF (If it is already USD, the exchange rate should be 1, otherwise, pull

the actual exchange rate) USD Total = Order Total/Exchange Rate

6. Add a worksheet labeled ‘Pivot Chart’ and make it the first one on the left. Then create a Pivot Chart

(Column Chart) showing Country Name and Sum of USD Total. Format all cells appropriately and sort it from highest total to lowest. The order of worksheets should be: Pivot Chart, Sales Data, Exchange Rates, Country Codes, and Currency Codes [15% of grade]

7. Add a textbox next to the Pivot Chart and answer the following questions: do not, do not, repeat the

questions in your answers. [15% of grade]

a. Assume your boss asked you to summarize the results in a paragraph. What would you tell them? An assessment such as “It tells us how much revenue came from each country.” would be true of any chart like this and is not useful. Instead, you are being asked to say what this chart tells you.

b. What questions would you ask so you could better understand what the data tell you? Think about what data or detail beyond what you were given might be useful.