EFIM20005: Management Science – Case Coursework 2022/23 Administration This is the third of three assessments for this unit. It accounts for 60% of total unit marks. Please submit your work presented in a single Word document via Turnitin. The assessment is to be completed individually and independently. Case: Foreign Exchange Trading at Baldwin Enterprises Baldwin Enterprises is a large manufacturing company with operations and sales divisions located in the United States and several other countries. The CFO of the organization, Wes Hamrick, is concerned about the amount of money Baldwin has been paying in transaction costs in the foreign exchange markets and has asked you to help optimize Baldwin’s foreign exchange treasury functions. With operations in several countries, Baldwin maintains cash assets in several different currencies: U.S. dollars (USD), the European Union’s euro (EUR), Great Britain’s pound (GBP), Hong Kong dollars (HKD), and Japanese yen (JPY). To meet the different cash flow requirements associated with the company’s operations around the world, Baldwin must often move funds from one location (and currency) to another. For instance, to pay an unexpected maintenance expense at their facility in Japan, Baldwin may need to convert some of its holdings in U.S. dollars to Japanese yen. The foreign exchange (FX) market is a network of financial institutions and brokers in which individuals, businesses, banks, and governments buy and sell the currencies of different countries. They do so in order to finance international trade, invest or do business abroad, or speculate on currency price changes. The FX market operates 24 hours a day and represents the largest and most liquid marketplace in the global economy. On average, the equivalent of about $1.5 trillion in different currencies is traded daily in the FX market around the world. The liquidity of the market provides businesses with access to international markets for goods and services by providing foreign currency necessary for transactions worldwide (see http://www.ny.frb.org/fxc). The FX market operates in much the same way as a stock or commodity market where there is a bid price and ask price for each commodity (or, in this case, currency). Abid price is 1 the price at with 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 0.9724 1.5593 0.12812 0.00843 EUR 1.01864 1 1.5881 0.1304 0.00856 GBP 0.6409 0.6295 1 0.0821 0.0054 HKD 7.7985 7.6552 12.154 1 0.0658 JPY 118.55 116.41 184.97 15.1005 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 U.S. 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 U.S. dollars, and then converted those 1.5593 dollars back to British pounds, you would end up with only 0.999355 British pounds (that is, 1 * 1.5593 * 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 million 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. As Wes’ assistant, he has asked you to figure out the optimal trading plan? Task 1. Formulate and solve this problem as a Linear Programme in Excel. 2. Produce the sensitivity report for the LP. 3. Using the above output, analyse the above case and write a report for Wes advising him the optimal trading plan. The report should include: • Formulation of the Linear Programming problem. • Screenshots of the Excel spreadsheet model. • Statement of the recommended solution. • Discussion of the interpretation of the sensitivity report. • Discussion of assumptions made in applying Linear Programming to Wes’ problem. • Extended analysis that would be useful to Wes followed by more general suggestions, including a statement of requirements to carry out the extended analysis. 2 Submission requirements • Submission is required by Thursday 11 May by 1pm. • The report should be uploaded to Turnitin in Microsoft Word. You should use a 12- point font size and line-spacing of 1.5 with blank line between paragraphs. Margins should be set to “Normal”. 1500 words is suggested as a guideline for length. It may be longer or shorter than this, but if shorter, it will be assessed as to whether it has covered the salient issues in sufficient depth. If longer, it will be assessed from the perspective of whether unnecessary material has been included. So, word length is an element of academic judgement in marking, not a penalty for failing to meet a word count. • Use Harvard System for referencing. Report structure you should follow • Statement of Findings – presentation of the results of the analysis for Wes including recommended solution, and commentary on the interpretation of the sensitivity report. • Assumptions – model assumptions refer to the underlying assumptions or conditions that must hold true for a particular model to be valid and accurate. To ensure that the model is appropriate for the specific problem at hand, it is important to consider and evaluate the model assumptions. This section requires a discussion of the case and model assumptions, along with an assessment of their realism. • Formulation – statement of the mathematical formulation of the problem and interpretation of variables/constraints. • Extensions – you should present any extended work that you have completed, providing a clear rationale for its usefulness. Additionally, you should discuss potential ways in which this project could be extended in a sensible manner, while outlining the requirements for such an extension. Note: • Include a screenshot of your Excel spreadsheet in Number View, and provide explanatory comments to help readers understand the data and calculations presented. • Include a screenshot of your Excel spreadsheet in Formula View, and provide explanatory comments to help readers understand the formulas and functions used. • Include a screenshot of the Solver Form, and provide explanatory comments to help readers understand how you used Solver to optimize your model.