Excel homework

ITEC-200 Excel International Finance Homework Instructions

1

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 client has customers in different countries who place orders in multiple currencies. However, they need to do financial reporting in US Dollars for a certain period of time but revenue comes in from the 10 most heavily-traded currencies in the world (see below; source: https://en.wikipedia.org/wiki/Templ ate:Most_traded_currencies).

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$

Instructions

Start by downloading the Excel file and rename it to 'ITEC200-00X_last name_first name_Homework2' where 00X = your section number. In order 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. 1. Since sales data contain raw codes (i.e., not mapped to plain English values), you will need to use

VLOOKUP to find them. However, you must first import the mapping data from the text files included with this assignment. Copy-and-paste the codes into worksheet names of 'Country Codes' and 'Currency Codes' respectively and format all cells appropriately. [4% of grade]

2. In order 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: XML or Extensible Markup Language, is a computer language that allows transmission of textual

ITEC-200 Excel International Finance Homework Instructions

2

data that is both human- and machine-readable and across different platforms. For a 2-minute explanation of XML, please watch https://www.youtube.com/watch?v=tZE-O5KTwCY). [25% of grade] This assignment is based on the Windows version of Excel 2016 of which there are two, different versions – Excel 2016 and Excel 2016 Professional Plus. This section file contains instructions for both subversions. If you are a Mac user, you should use the Virtual Computing Lab (VCL) at: https://vcl.american.edu/ to complete this assignment. a. Excel 2016 Instructions for Windows To get these external data, you will need to use Excel's Get & Transform Data feature found under the Data Tab. Select Get Data → From Web (below left), which will bring up a dialog box (below, right).

Enter the URL and select OK then select Edit as shown below.

This brings up another window for the Query Editor, which allows you to edit which columns of data are imported into Excel when you click on the far-right column of Item.

ITEC-200 Excel International Finance Homework Instructions

3

This brings up the following dialog box. Select the following

columns: pubDate, baseCurrency, baseName, target

Currency, targetName, and exchangeRate, then select Close

and Load. Your worksheet should now show all the needed

exchange rate data. Label this worksheet 'Exchange Rates'.

b. Excel 2016 Professional Plus Instructions (version found on the VCL) To get the XML download, click on Data → Get External Data → From Web, which brings up the

dialog box below. Enter the URL http://www.floatrates.com/daily/usd.xml and click Go.

If you get a JavaScript error like shown below, click Yes until it goes away.

ITEC-200 Excel International Finance Homework Instructions

4

Scroll down within the New Web Query window until you can the Exchange Rates for U.S. Dollar

(USD) table and click the icon, which will highlight in blue the data you need.

ITEC-200 Excel International Finance Homework Instructions

5

You will then get a prompt as to where to put the data. Click on New Worksheet (see below).

This will retrieve the following three columns: Currency Name, Code, and Rate per 1 USD (see sample

below), which is all the data you need to complete the IF/VLOOKUP statements. Label this worksheet

'Exchange Rates'.

*** End of XML download instructions. ***

3. 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

ITEC-200 Excel International Finance Homework Instructions

6

4. 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 USD Total = Order Total/Exchange Rate

5. Add a worksheet labeled 'Pivot Chart' and move it so that it's 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. The order of worksheets should be: Pivot Chart, Sales Data, Exchange Rates, Country Codes, and Currency Codes [15% of grade]

6. Add a textbox next to the Pivot Chart and answer the following questions; do not repeat the

questions in your answers. [15% of grade]

1. 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; you are being asked to say what this chart tells you.

2. 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.