Please use the excel file attached to answer question b of the pdf.

1980 Inflation Rates (%) U.S. U.K. Germany 1981 1982 1983 13.5% 17.9% 5.5% 10.3% 11.9% 6.4% 6.2% 8.6% 5.2% 3.2% 4.5% 3.3% U.S. Market Volume (units) Units growth (%) 2,518 5,154 104.7% 9,971 93.5% 15,260 53.0% Rest of the World Volume (units) Units growth (%) 12,951 8,779 -32.2% 11,036 25.7% 13,207 19.7% Total Volume Units Units growth (%) 15,469 13,933 -9.9% 21,007 50.8% 28,467 35.5% Jaguar, plc (Scenario A) (million GBP except where indicated) 1983 1984 1985 1986 1987 1988 1989 1.4880 1.3500 -9.3% 1.3243 -1.9% 1.2991 -1.9% 1.2743 -1.9% 1.2500 -1.9% 1.2262 -1.9% 3.2% 53.0% 15.260 3.0% 12.4% 17.154 53.6% 3.0% 6.0% 18.183 54.1% 3.0% 4.0% 18.911 54.6% 3.0% 2.0% 19.289 54.6% 3.0% 2.0% 19.675 54.6% 3.0% 2.0% 20.068 54.6% 24.884 25.631 439.674 325.685 26.400 480.036 362.487 27.192 514.215 395.836 28.008 540.234 423.940 28.848 567.570 454.040 29.713 596.289 486.277 4.5% 19.7% 13.207 5.0% 12.4% 14.846 46.4% 5.0% 4.0% 15.440 45.9% 5.0% 2.0% 15.749 45.4% 5.0% 2.0% 16.064 45.4% 5.0% 2.0% 16.385 45.4% 5.0% 2.0% 16.713 45.4% Price/unit (GBP) Sales (in GBP) 16.461 17.284 256.598 18.148 280.205 19.056 300.100 20.008 321.407 21.009 344.227 22.059 368.667 Total Units Units growth (%) 28.467 35.5% 32.000 12.4% 33.623 5.1% 34.659 3.1% 35.352 2.0% 36.059 2.0% 36.781 2.0% 13.521 30.000 14.197 33.112 14.907 35.856 15.652 38.401 16.435 41.128 17.257 44.048 Spot [USD/GBP] Change in spot rate U.S. Market Inflation (%) Units growth (%) Volume (units) Share (%) Price/unit (USD) Sales (in USD) Sales (in GBP) Rest of the World Inflation in GBP (%) Units growth (%) Volume (units) Share (%) Variable mfg. Cost/unit (GBP) Net Working Capital (GBP) Total Revenues Variable Cost of Sales Depreciation R&D Distribution Administration Total Costs 472.600 361.100 8.600 16.500 13.300 22.000 421.500 582.283 432.672 10.000 18.000 13.965 23.100 497.737 642.692 477.349 12.000 18.900 14.663 24.255 547.167 695.936 516.661 14.000 19.845 15.396 25.468 591.371 745.347 553.344 16.000 20.837 16.166 26.741 633.089 798.267 592.632 18.000 21.879 16.975 28.078 677.564 854.944 634.709 20.000 22.973 17.823 29.482 724.987 84.546 95.525 104.565 112.258 120.703 129.956 35.0% 29.591 54.955 10.000 64.955 35.0% 33.434 62.092 12.000 74.092 35.0% 36.598 67.967 14.000 81.967 35.0% 39.290 72.968 16.000 88.968 35.0% 42.246 78.457 18.000 96.457 35.0% 45.485 84.472 20.000 104.472 Increase in Net Working Capital 27.000 3.112 2.743 2.546 2.726 2.920 Incremental CAPEX Capital Expenditures 11.500 2.300 13.800 2.300 16.100 2.300 18.400 2.300 20.700 2.300 23.000 Free Cash Flow (FCF) 26.455 57.179 63.124 68.022 73.030 78.552 1.0000 26.455 0.8475 48.457 0.7182 45.335 0.6086 41.400 0.5158 37.668 0.4371 34.336 EBIT Tax Rate Taxes on EBIT NOPAT (EBIAT) Depreciation Operating Cash Flow Discount Rate: Dicount Factor Present Value of FCF Cummulative PV of FCF 35.0% 18.0% 233.651 Growth rate (g) Terminal Value (growing perpetuity) PV of Terminal Value 5.0% 277.326 Firm Value (PV of FCF + PV of TV) 510.977 634.456 Jaguar, plc (Scenario A) (million GBP except where indicated) 1983 1984 1985 1986 1987 1988 1989 1.4880 1.3500 -9.3% 1.3230 -2.0% 1.2965 -2.0% 1.2706 -2.0% 1.2452 -2.0% 1.2203 -2.0% 3.2% 53.0% 15.260 3.0% 12.4% 17.154 53.6% 3.0% 2.0% 17.497 53.1% 3.0% 2.0% 17.847 53.1% 3.0% 2.0% 18.204 53.1% 3.0% 2.0% 18.568 53.1% 3.0% 2.0% 18.939 53.1% 24.884 25.631 439.674 325.685 26.400 461.922 349.147 27.192 485.295 374.300 28.008 509.851 401.265 28.848 535.649 430.172 29.713 562.753 461.162 4.5% 19.7% 13.207 5.0% 12.4% 14.846 46.4% 5.0% 4.0% 15.440 46.9% 5.0% 2.0% 15.749 46.9% 5.0% 2.0% 16.064 46.9% 5.0% 2.0% 16.385 46.9% 5.0% 2.0% 16.713 46.9% Price/unit (GBP) Sales (in GBP) 16.461 17.284 256.598 18.148 280.205 19.056 300.100 20.008 321.407 21.009 344.227 22.059 368.667 Total Units Units growth (%) 28.467 35.5% 32.000 12.4% 32.937 2.9% 33.596 2.0% 34.268 2.0% 34.953 2.0% 35.652 2.0% 13.521 30.000 14.197 32.425 14.907 34.746 15.652 37.233 16.435 39.898 17.257 42.754 Spot [USD/GBP] Change in spot rate U.S. Market Inflation (%) Units growth (%) Volume (units) Share (%) Price/unit (USD) Sales (in USD) Sales (in GBP) Rest of the World Inflation in GBP (%) Units growth (%) Volume (units) Share (%) Variable mfg. Cost/unit (GBP) Net Working Capital (GBP) Total Revenues Variable Cost of Sales Depreciation R&D Distribution Administration Total Costs 472.600 361.100 8.600 16.500 13.300 22.000 421.500 582.283 432.672 10.000 18.000 13.965 23.100 497.737 629.352 467.607 12.000 18.900 14.663 24.255 537.425 674.400 500.807 14.000 19.845 15.396 25.468 575.516 722.672 536.365 16.000 20.837 16.166 26.741 616.109 774.399 574.446 18.000 21.879 16.975 28.078 659.378 829.829 615.232 20.000 22.973 17.823 29.482 705.511 84.546 91.927 98.884 106.563 115.021 124.319 35.0% 29.591 54.955 10.000 64.955 35.0% 32.174 59.753 12.000 71.753 35.0% 34.609 64.274 14.000 78.274 35.0% 37.297 69.266 16.000 85.266 35.0% 40.257 74.764 18.000 92.764 35.0% 43.512 80.807 20.000 100.807 Increase in Net Working Capital 27.000 2.425 2.321 2.487 2.665 2.856 Incremental CAPEX Capital Expenditures 11.500 2.300 13.800 2.300 16.100 2.300 18.400 2.300 20.700 2.300 23.000 Free Cash Flow (FCF) 26.455 55.528 59.853 64.379 69.399 74.951 1.0000 26.455 0.8475 47.057 0.7182 42.986 0.6086 39.183 0.5158 35.795 0.4371 32.762 EBIT Tax Rate Taxes on EBIT NOPAT (EBIAT) Depreciation Operating Cash Flow Discount Rate: Dicount Factor Present Value of FCF Cummulative PV of FCF 35.0% 18.0% 224.238 Growth rate (g) Terminal Value (growing perpetuity) PV of Terminal Value 5.0% 264.616 Firm Value (PV of FCF + PV of TV) 488.853 605.376 William E. Simon Graduate School of Business Administration FTN 442 International Economics and Finance Prof. Gregory H. Bauer Jaguar plc, 1984 Instructions for Case Preparation There are three main questions that must be addressed in this case: (1) (2) (3) How much is Jaguar worth in 1984? What is the company's exposure to exchange rates? What do you recommend to hedge this exposure? In order to answer these questions you will have to construct scenarios which depend on your forecast of exchange rates. In each scenario, construct a spreadsheet that will give you free cash flow as a function of your inputs and assumptions. Use two scenarios: (A) No change in the real exchange rate between the dollar and the pound. That is, the dollar/pound rate follows PPP from its initial level of USD1.35/GBP in 1984. (B) There is a 25% drop in the real value of the dollar against the pound in 1985, followed thereafter by movements governed by PPP. Assume that Jaguar reacts to this by raising its USD prices by 10% in 1985 (beyond the inflation increase of 3%). US unit growth is 2% from 1985 on. The rest-of-world prices are the same as in scenario A. To estimate Jaguar's value, you must estimate its "free cash flow\" net of increases in working capital and capital expenditures. Construct a spreadsheet that estimates these free cash flows from 1984 to 1989. To value the firm, you must make an assumption about cash flows beyond 1989. Assume that they rise at 5% in 1990 and thereafter. In one cell in the spreadsheet, have a NPV of all future free cash flow. To estimate the exposure to exchange rates do the following for scenario (b) only: (1) Change the value of the nominal exchange rate in 1984 by +/- 1% from the values given in scenario B. This will affect the values of the exchange rates in all future years. What is the change in the NPV of future free cash flows? (2) Construct an elasticity as: % change in NPV / % change in S. (3) The size of the exposure will be this elasticity x NPV(free cash flow). Assumptions for the baseline case of PPP Exchange rates and inflation Inflation in the U.S. is assumed to be 3% per year from 1984 to 1989. Inflation in Britain is 5% per year. Market growth: As stated in the case, the total market for jaguars in 1984 is projected to be 32,000 cars. Assume that the market in 1984 is shared between the U.S. and the rest of the world (ROW) as in 1983, with the U.S. share being: 15,260/28,467 = 53.61% U.S. market volume is assumed to grow 6% in 1985, 4% in 1986, and 2% thereafter. ROW market volume is assumed to grow 4% in 1985 and 21/o thereafter. Price Per unit: In the U.S., the price per unit in 1984 is assumed to be 3% above 1983's figure. 1983's price/unit = ('83 revenue x U.S. share x USD1.488/GBP)/'83 U.S. volume = $24,884, USD 1.488/GBP being the average exchange rate in 1983. In the ROW, make similar assumptions. Fixed costs: Capital expenditure is assumed to be GBP 11.5 million in 1984 and rises by GBP 2.3 million each year. Depreciation is GBP 10.0 million in 1984 and rises by GBP 2.0 million each year. R&D is GBP 18.0 million and rises at the 5% inflation rate. Distribution and administrative expenses (both assumed to be fixed costs) rise at the inflation rate from their 1983 figures of GBP 13.3 million and GBP 22.0 million, respectively. Variable costs and net working capital: All of the "cost of sales" in the income statement, net of depreciation, is (arbitrarily) assumed to be variable costs. Variable costs/unit rise at the inflation rate. Note that the 1983, volume should be based on production volume of 28,041, not sales volume. Net working capital in 1983 is unrealistically low for a stand-alone company. Assume GBP 30.0 million in 1984, growing thereafter with revenues. Other assumptions: Taxes: assume a 35% tax rate. Discount rate: assume an 18% discount rate. Start of Jaguar 1984 analysis 1. Dollar/Sterling United States 2. U.S. growth, units (%) 3. U.S. volume, units 4. U.S. Inflation (%) 5. US$ price/unit 6. U.S. sales, dollars 7. U.S. sales, sterling Rest of World 8. Growth, units (%) 9. Volume, units 10. Inflation, sterling (%) 11. Price/unit, sterling 12. R.o.W. sales, sterling 13. Var. mfg. Cost/unit 14. Net working capital 15. Discount rate, sterling 16. Total revenues 17. Var. cost of sales 18. Depreciation 19. R & D 20. Distribution 21. Administration 22. Total Costs 23. EBIT 24. Tax 25. EBIAT 26. Depreciation 27. Operating cash flow 28. Increase in NWC 29. Capital expenditures 30. Free cash flow 31. Terminal value 32. PV, free cash flow 1.35 17.154 25.631 439,674 325,685 14.846 17.284 256,598 13.521 30,000 0.18 582,283 432,672 10,000 18,000 13,965 23,100 497,737 84,546 29,591 54,955 10,000 64,955 27,000 11,500 26,455 26,455