You are the VP of overseas operations for a multinational corporation with manufacturing and sales operations in North America and Europe. It's often necessary to move money around to satisfy cash requirements. On this morning, your firm is short on cash for British pounds and Canadian dollars and long on cash for Euros, Swiss francs, and U.S. dollars.
Currency Symbol Required (in M) Excess (in M)
British Pound GBP 4.5 -
Canadian Dollar CAD 10 -
Euro EUR - 8
Swiss Franc SWF - 1.2
You have a few banks with which you frequently make large currency transactions. You ask you assistance to call around and compile the best rates available from the various banks. The analyst gives you the following cross rates table:
GBP CAD EUR SWF USD
GBP 1 2.236 1.6395 2.4714 1.4685
CAD 0.4468 1 0.733 1.1048 0.6563
EUR 0.6089 1.3625 1 1.5065 0.8949
SWF 0.4041 0.904 0.6635 1 0.5937
USD 0.6807 1.5226 1.1169 1.6833 1
This table shows the selling prices for the currency in each row (which we'll refer to as Rij). For example, you can exchange (i.e. sell) 1 GBP and receive 1.4685 USD.
1 GBP → R15 USD → 1.4685 USD
If you have 1 USD, you can exchange it for 0.6807 GBP,
1 USD → R51 GBP → 0.6807 GBP
Notice that if you start with 1 GBP, exchange it for USD and then exchange the USD back to GBP, you have lost a fraction of the value.
1 GBP → R15 USD → R15 R51 GBP = (1.4865)(.6807) GBP = .99960795 GBP
The slight loss can be interpreted as a fee charged by the banks involved in the transaction.
You notice that you can cover the shortfall in GBP by exchanging 8 EUR for 4.8712 GBP. You can exchange 10 SWF and the 1.2 USD for 10.86712 CAD. However, you recognize there are other transfer schemes the meet these requirements. For example, you could exchange all the EUR for CAD and all the SWF and USD for GBP. You wonder if one transfer scheme is better than another.
In the first transfer scheme above,
Initial Purchased Sold Final
GBP 4.8712 4.8712
CAD 10.86712 10.86712
EUR 8 8
SWF 10 10
USD 1.2 1.2
The final position is 4.8712 GBP and 10.86712 CAD.
Your company tracks the value of its currency portfolio in USD. That is if you exchanged final holdings for you company you would have:
(R15 USD/ GBP) (4.8712 GBP) + (R25 USD/ CAD) (10.86712 CAD) = (1.4685 USD/GBP) (4.8712 GBP) + (.6563 USD/CAD) (10.86712 CAD) = 14.28545 USD
Build a model to help find the best scheme for transferring money if the objective is to maximize the dollar value of the final position, (i.e. how much the cash holding are worth if they are converted to USD)
a.) Identify the elements of the decision problem by answering the following questions in words:
? What is the objective?
? What are the decisions? What constraints are there on your alternatives?
? What information do you have?
b.) Write a mathematical model using the following notation.
Index the currencies from 1 to 5 (1 for GBP, 2 for CAD, 3 for EUR, 4 for SWF, 5 for USD)
Define your decision variables as xij = quantity of currency i to exchange for currency j, in other words, the quantity of the currency i you will sell to buy currency j. For instance x31 is the number of Euros to exchange into British pounds. Hence, you have a total of n2 possible exchanges you can make (or n2-n if you exclude the "diagonal" xii)
Write an expression for the amount of currency i sold, as a function of the xii. Label it si. For instance, s4 is the number of Swiss Francs sold over all transactions you make.
Write and expression for the total amount of currency i bought as a function of the xii. Label it bi. For instance, b4 is the number of Swiss Francs bought over all transactions you make.
Write an expression for the final holdings of currency i. Label it fi. For instance, f1 is the number of British pounds in the final currency portfolio.
What is the constraint associated with the final holdings currency i?
Write an expression for the objective function.
c.) Build a spreadsheet model to solve this problem. What action should you take?
Please see attached files
a. What is the objective?
The objective is to maximize the dollar value of the final position, (i.e. how much the cash holding are worth if they are converted to USD).
What are the decisions? What constraints are there on your alternatives?
The decisions include how much of the existing currencies, i.e. EUR, SWF and USD, should be converted into the required currencies, i.e. GBP and CAD. The constraints include that the amount of money converted (EUR, SWF and USD) has to be less than the existing money available. Also ...
The solution provides detailed explanations and step-by-step instructions on how to solve the optimization model using Excel.