Case 33 137 1 A bid price is the price at which the market is willing to buy a particular currency and the ask price is the price at which the market is willing to sell a currency. The ask prices are typically slightly higher than the bid prices for the same currency - representing the transaction cost or the profit earned by the organizations that keep the market liquid. The following table summarizes the current FX rates for the currencies Baldwin currently holds. The entries in this table represent the conversion rates from the row currencies to the column currencies. Convert To USD EUR GBP HKD JPY USD 1 1.01864 0.6409 7.7985 118.55 EUR 0.9724 0.6295 7.6552 116.41 GBP 1.5593 1.5881 1 12.154 184.97 HKD 0.12812 0.1304 0.0821 1 15.1005 JPY 0.00843 0.00856 0.0054 0.0658 1 For example, the table indicates that 1 British pound (GBP) can be exchanged (or sold) for 1.5593 U.S. dollars (USD). Thus, $1.5593 is the bid price, in U.S. dollars, for 1 British pound. Alternatively, the table indicates 1 US dollar (USD) can be exchanged (sold) for 0.6409 British pounds (GBP). So, it takes about 1.5603 U.S. dollars (or 1/0.6409) to buy 1 British pound (or the ask price, in U.S. dollars, for 1 British pound is roughly $1.5603). Notice that if you took 1 British pound, converted it to 1.5593 US dollars, and then converted those 1.5593 dollars back to British pounds, you would end up with only 0.999355 British pounds (i.e., 1 x 1.5593 x 0.6409 = 0.999355). The money you lose in this exchange is the transaction cost. Baldwin's current portfolio of cash holdings includes 2 million USD, 5 million EUR, 1 million GBP, 3 million HKD, and 30 million JPY. This portfolio is equivalent to $9,058,560 USD under the current exchange rates (given earlier). Wes has asked you to design a currency trading plan that would increase Baldwin's euro and yen holdings to 8 million EUR and 54 JPY, respectively, while maintaining the equivalent of at least $250,000 USD in each currency. Baldwin measures transaction costs as the change in the USD equivalent value of the portfolio 1. Create a spreadsheet model for this problem and solve it. 2. What is the optimal trading plan? 3. What is the optimal transaction cost (in equivalent USD)? 4. Suppose another executive thinks that holding $250,000 USD in each currency is excessive and wants to lower the amount to $50,000 USD in each currency. Does this help to lower the transaction cost? Why or why not? 5. Suppose the exchange rate for converting USD to GBP increased from 0.6409 to 0.6414. What happens to the optimal solution in this case? CASE 3.3 The Wolverine Retirement Fund Kelly Jones is a financial analyst for Wolverine Manufacturing, a company that produces engine bearings for the automotive industry. Wolverine is in the process of hammering out a new labor agreement with its unionized workforce. One of the major concerns of the labor union is the funding of Wolverine's retirement plan for its hourly employees. The union believes the company has not been contributing enough money to this fund to cover the benefits it will need to pay to retiring employees. Because of Copyright 2018 Cangage Learning. All Rights Reserved. May not be copied, scanned, er duplicated, 138 Chapter 20 years Modelling and Solving LP Problems in a Spreadsheet this, the union wants the company to contribute approximately $1.5 million dollars in additional money to this fund over the next 20 years. These extra contributions would begin with an extra payment of $20,000 at the end of 1 year with annual payments increasing by 12.35% per year for the next 19 years. The union has asked the company to set up a sinking fund to cover the extra annual payments to the retirement fund. The Wolverines' CFO and the union's chief negotiator have agreed that AAA rated bonds recently issued by three different companies may be used to establish this fund. The following table summarizes the provisions of these bonds. Company Maturity Coupon Payment Price Par Value AC&C 15 years $80 $847.88 $1,000 IBN 10 years $90 $938.55 $1,000 MicroHard $85 $872.30 $1,000 According to this table, Wolverine may buy bonds issued by AC&C for $847.88 per bond. Each AC&C bond will pay the bondholder $80 per year for the next 15 years, plus an extra payment of $1,000 (the par value) in the fifteenth year. Similar interpretations apply to the information for the IBN and MicroHard bonds. A money market fund yielding 5% may be used to hold any coupon payments that are not needed to meet the company's required retirement fund payment in any given year. Wolverine's CFO has asked Kelly to determine how much money the company would have to invest and which bonds the company should buy in order to meet the labor union's demands 1. If you were Kelly, what would you tell the CFO? 2. Suppose the union insists on including one of the following stipulations in the agreement: a. No more than half of the total number of bonds purchased may be purchased from a single company. b. At least 10% of the total number of bonds must be purchased from each of the companies. Which stipulation should Wolverine agree to? CASE 3.4 Saving the Manatees "So how am I going to spend this money," thought Tom Wieboldt as he sat staring at the pictures and posters of manatees around his office. An avid environmentalist, Tom is the president of "Friends of the Manatees" a non-profit organization trying to help pass legislation to protect manatees. Manatees are large, gray-brown aquatic mammals with bodies that taper to a flat, paddle-shaped tail. These gentle and slow-moving creatures grow to an average adult length of 10 feet and weigh an average of 1,000 pounds. Manatees are found in shallow, slow-moving rivers, estuaries, saltwater bays, canals, and coastal areas. In the United States, manatees are concentrated in Florida in the winter, but can be found in summer months as far west as Alabama and as far north as Virginia and the Carolinas. They have no natural enemies, but loss of habitat is the most serious threat facing manatees today. Most human-related manatee deaths occur from collisions with motor boats. Tom's organization has been supporting a bill before the Florida legislature to restrict the use of motor boats in areas known to be inhabited by manatees. This bill is scheduled to come up for a vote in the legislature. Tom recently received a phone call Copyright 2018 Cangage Learning. All Rights Reserved. May not be copied, scanned, er duplicated, Case 33 137 1 A bid price is the price at which the market is willing to buy a particular currency and the ask price is the price at which the market is willing to sell a currency. The ask prices are typically slightly higher than the bid prices for the same currency - representing the transaction cost or the profit earned by the organizations that keep the market liquid. The following table summarizes the current FX rates for the currencies Baldwin currently holds. The entries in this table represent the conversion rates from the row currencies to the column currencies. Convert To USD EUR GBP HKD JPY USD 1 1.01864 0.6409 7.7985 118.55 EUR 0.9724 0.6295 7.6552 116.41 GBP 1.5593 1.5881 1 12.154 184.97 HKD 0.12812 0.1304 0.0821 1 15.1005 JPY 0.00843 0.00856 0.0054 0.0658 1 For example, the table indicates that 1 British pound (GBP) can be exchanged (or sold) for 1.5593 U.S. dollars (USD). Thus, $1.5593 is the bid price, in U.S. dollars, for 1 British pound. Alternatively, the table indicates 1 US dollar (USD) can be exchanged (sold) for 0.6409 British pounds (GBP). So, it takes about 1.5603 U.S. dollars (or 1/0.6409) to buy 1 British pound (or the ask price, in U.S. dollars, for 1 British pound is roughly $1.5603). Notice that if you took 1 British pound, converted it to 1.5593 US dollars, and then converted those 1.5593 dollars back to British pounds, you would end up with only 0.999355 British pounds (i.e., 1 x 1.5593 x 0.6409 = 0.999355). The money you lose in this exchange is the transaction cost. Baldwin's current portfolio of cash holdings includes 2 million USD, 5 million EUR, 1 million GBP, 3 million HKD, and 30 million JPY. This portfolio is equivalent to $9,058,560 USD under the current exchange rates (given earlier). Wes has asked you to design a currency trading plan that would increase Baldwin's euro and yen holdings to 8 million EUR and 54 JPY, respectively, while maintaining the equivalent of at least $250,000 USD in each currency. Baldwin measures transaction costs as the change in the USD equivalent value of the portfolio 1. Create a spreadsheet model for this problem and solve it. 2. What is the optimal trading plan? 3. What is the optimal transaction cost (in equivalent USD)? 4. Suppose another executive thinks that holding $250,000 USD in each currency is excessive and wants to lower the amount to $50,000 USD in each currency. Does this help to lower the transaction cost? Why or why not? 5. Suppose the exchange rate for converting USD to GBP increased from 0.6409 to 0.6414. What happens to the optimal solution in this case? CASE 3.3 The Wolverine Retirement Fund Kelly Jones is a financial analyst for Wolverine Manufacturing, a company that produces engine bearings for the automotive industry. Wolverine is in the process of hammering out a new labor agreement with its unionized workforce. One of the major concerns of the labor union is the funding of Wolverine's retirement plan for its hourly employees. The union believes the company has not been contributing enough money to this fund to cover the benefits it will need to pay to retiring employees. Because of Copyright 2018 Cangage Learning. All Rights Reserved. May not be copied, scanned, er duplicated, 138 Chapter 20 years Modelling and Solving LP Problems in a Spreadsheet this, the union wants the company to contribute approximately $1.5 million dollars in additional money to this fund over the next 20 years. These extra contributions would begin with an extra payment of $20,000 at the end of 1 year with annual payments increasing by 12.35% per year for the next 19 years. The union has asked the company to set up a sinking fund to cover the extra annual payments to the retirement fund. The Wolverines' CFO and the union's chief negotiator have agreed that AAA rated bonds recently issued by three different companies may be used to establish this fund. The following table summarizes the provisions of these bonds. Company Maturity Coupon Payment Price Par Value AC&C 15 years $80 $847.88 $1,000 IBN 10 years $90 $938.55 $1,000 MicroHard $85 $872.30 $1,000 According to this table, Wolverine may buy bonds issued by AC&C for $847.88 per bond. Each AC&C bond will pay the bondholder $80 per year for the next 15 years, plus an extra payment of $1,000 (the par value) in the fifteenth year. Similar interpretations apply to the information for the IBN and MicroHard bonds. A money market fund yielding 5% may be used to hold any coupon payments that are not needed to meet the company's required retirement fund payment in any given year. Wolverine's CFO has asked Kelly to determine how much money the company would have to invest and which bonds the company should buy in order to meet the labor union's demands 1. If you were Kelly, what would you tell the CFO? 2. Suppose the union insists on including one of the following stipulations in the agreement: a. No more than half of the total number of bonds purchased may be purchased from a single company. b. At least 10% of the total number of bonds must be purchased from each of the companies. Which stipulation should Wolverine agree to? CASE 3.4 Saving the Manatees "So how am I going to spend this money," thought Tom Wieboldt as he sat staring at the pictures and posters of manatees around his office. An avid environmentalist, Tom is the president of "Friends of the Manatees" a non-profit organization trying to help pass legislation to protect manatees. Manatees are large, gray-brown aquatic mammals with bodies that taper to a flat, paddle-shaped tail. These gentle and slow-moving creatures grow to an average adult length of 10 feet and weigh an average of 1,000 pounds. Manatees are found in shallow, slow-moving rivers, estuaries, saltwater bays, canals, and coastal areas. In the United States, manatees are concentrated in Florida in the winter, but can be found in summer months as far west as Alabama and as far north as Virginia and the Carolinas. They have no natural enemies, but loss of habitat is the most serious threat facing manatees today. Most human-related manatee deaths occur from collisions with motor boats. Tom's organization has been supporting a bill before the Florida legislature to restrict the use of motor boats in areas known to be inhabited by manatees. This bill is scheduled to come up for a vote in the legislature. Tom recently received a phone call Copyright 2018 Cangage Learning. All Rights Reserved. May not be copied, scanned, er duplicated