Please answer this problemusing excel formulaswith both the answers and themethodof retrieving the answers.Make sure to do this in EXCEL. Thank you!

A B D E F G H I 1 2 Problem 14.16 Grupo Modelo S.B.A de C.V. 3 4 5 6 Grupo Modelo, a brewery out of Mexico that exports such well-known varieties as Corona, Modelo and Pacifico, is Mexican by incorporation. However, the company evaluates all business results, including financing costs, in U.S. dollars. The company needs to borrow $10,000,000 or the foreign currency equivalent for four years. For all issues, interest is payable once per year, at the end of the year. Available alternatives are: 7 8 9 10 11 12 a. Sell Japanese yen bonds at par yielding 3% per annum. The current exchange rate is 106/$, and the yen is expected to strengthen against the dollar by 2% per annum. b. Sell euro-denominated bonds at par yielding 7% per annum. The current exchange rate is $1.1960/, and the euro is expected to weaken against the dollar by 2% per annum. c. Sell U.S. dollar bonds at par yielding 5% per annum. 13 14 Which course of action do you recommend Grupo Modelo take and why? 15 16 Japanese 17 Alternatives yen bonds euro bonds US dollar 18 Coupon rate 3.000% 7.000% bonds 5.000% 19 Current spot rate, yen/$ 20 Expected change in the value of the foreign currency 106.00 $ 2.000% 1.1960 -2.000% 0.000% 21 Principal needed by Grupo Modelo $ 100,000,000 22 23 Calculation of the dollar cost debt alternatives Year 0 Year 1 Year 2 Year 3 Year 4 24 25 Japanese yen bonds: 26 27 28 29 30 31 Proceeds and principal and interest payments Expected exchange rate (yen/$) US dollar equivalent in expected cash flows IRR of US$ cash flow stream (cost of funds) euro-denominated bonds: 10,600,000,000 $ 106.00 100,000,000 $ 5.060% (318,000,000) 103.92 (3,060,000) $ (318,000,000) 101.88 (3,121,200) $ (318,000,000) 99.89 97.93 (3,183,624) $ (111,490,512) (10,918,000,000) 32 33 Proceeds and principal and interest payments Expected exchange rate (yen/$) 34 35 36 37 38 39 US dollar equivalent in expected cash flows IRR of US$ cash flow stream (cost of funds) US dollar bonds: Proceeds and principal and interest payments IRR of US$ cash flow stream (cost of funds) 40 41 Given the expected exchange rate changes, the 42 -denominated bonds have the lowest all-in-cost of funds for the Mexico-based company, Grupo Modelo. (Note that it is the expected changes in exchange rates which determine this outcome. In the event that all currencies were expected to remain fixed, an expected 43 change of 0%, then the Japanese yen bonds are clearly the cheapest source of capital.) 44