Please answer question #4 with formulas on the excel sheet.
let me know if this helps. thank you!
JTM Airlines, a privately held firm, is looking to buy additional gates at its home airport for $1,500,000. It has money in the bank, but that money may not be spent as it is used to pay salaries, suppliers, and equipment. It asked its bank for a loan, but the bank refused unless the project had a return higher than JTM's weighed average cost of capital. Separately, PAN Airways's CEO approached JTM's CEO to sell the airline. As a result of all this, JTM has contracted you to: 1. Calculate JTM's weighed average cost of capital (WACC) based on two airlines trading in the capital markets - PDM and GAL. Since JTM does not trade, it has no beta, so you need to use these two firms as proxies. JTM's CFO kindly gave you the necessary information on PDM and GAL for you to do this. 2. Aside from the purchase price, the gates will require a working capital infusion of $1,500,000 at purchase. JTM estimates the gates will generate cash flows of $295,500 in year 1, inflating at 4%/year over the next 12 years. After that, the gates will revert back to the airport operator. Half the working capital is recovered at the end. Calculate the NPV and IRR of the gates. 3. You were given PAN'S 2020 income statement (IS) and balance sheet (BS), along with forecasts of the revenue growth. Forecast the IS and BS for the next 5 years. 4. The price discussed by the two CEOs is $6.5M. You must value PAN Airways using free cash flows to see if this price is fair or not. G E F B D PAN Airways Cash Flow Valuation H 2021 2022 1 2 3 (In '000 S) 4 Income before taxes 5 + Interest 6 - Income before interest and taxes (EBIT) 2023 2024 2025 TV OO NAWN - 8 EBIAT 9 Change in Working Capital 10 Change in Other assets 11 Free Cash Flow (FCF) 12 PV of FCF 13 - Existing Debt 14 -Value of equity 15 16 17 18 19 Place answer in the box below 20 21 22 23 24 25 20 PDM 22.00 2.20 10.00 16.00 160 200 360 56% 44% 1.00 0.44 1 Component 2 Net Income (MS) 3 Earnings per share 4 of shares (M) 5 Price per share 6 Market Value - Equity (M) 7 Market Value - Debt (M) 8 Market Value - Total (M) 9 - % Debt 10 -% Equity 11 Beta (levered) 12 Beta (unlevered) 13 Average Beta (unlevered) 14 15 Values for combined JTM/PAN Airways: 16 % Debt 17 % Equity 18 Beta (relevered) 19 Risk free rate 20 Market risk premium 21 Expected equity return (CAPM) 22 Expected cost of debt 23 Tax rate 24 Weighed average cost of capital (WACC) 25 26 27 GAL 25.00 1.90 13.16 17.00 224 100 324 31% 69% 1.05 0.73 0.59 50.0% 30.0% 1.18 2.3% 5.5% 8.7% 5.7% 21.0% 6.5% 28 29 30 31 + wwwwww OUW
