PLEASE SHOW WORK IN EXCEL FORMULAS!! PLEASE SHOW WORK IN EXCEL FORMULAS!! PLEASE SHOW WORK IN EXCEL FORMULAS!!
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:
-
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.
-
-
PLEASE SHOW WORK IN EXCEL FORMULAS
A B D E F G H 1 PAN Airways Cash Flow Valuation 2 2021 2022 2023 2024 2025 TV 3 (in 000 S) 4 Income before taxes 5 + Interest 6 - Income before interest and taxes (EBIT) 7 8 EBIAT - 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 9 Place answer in the box below 16 17 18 19 20 21 22 B C D E F G H 1 J 1 Actual 2020 Notes 2021 4.4% 8,581 2022 4.2% 8,564 Projected 2023 4.0% 8,548 2024 3.9% 8,540 2025 4.0% 8,548 8,219 2,953 2,346 3,174 2,511 695 731 2,211 1,790 565 437 374 1,300 6,677 2,377 1,915 595 458 395 1,365 7,105 2,555 2,049 626 480 416 1,433 7,560 2,747 2,193 660 503 439 1,505 8,046 Increase at a yearly rate of Increase at a yearly rate of Increase at a yearly rate of Increase at a yearly rate of Increase at a yearly rate of Increase at a yearly rate of 7.5% 7.0% 5.3% 4.8% 5.5% 5.0% 527 463 1,580 8,564 SS2 489 1,659 9,117 2 Income Statement (in '000 $) 3 Operating Revenue growth rate 4 Total Operating Revenues 5 6 Wages, benefits, payroll support, incentives 7 Aircraft fuel, incl. hedging gains/losses & Aircraft maintenance/rent 9 Landing fees and other rentals 10 Depreciation/amortization 11 All other expenses 12 Total Operating Expenses 13 14 Operating Income 15 Total non-operating income (expense) 16 Income before taxes 17 Income tax expense/(benefit) 18 Net Income 19 20 Cash and investments 21 Accounts receivable 22 Inventory and supplies, net 23 Other S01 (566) Increase at a yearly rate of 6.0% 1,542 (475) 1,067 224 1,476 (504) 973 204 1,004 (534) 470 99 (64) (13) (51) (25) (600) (624) (131) (493) (569) (636) (1,205) (253) (952) 371 843 769 Balance Sheet 934 975 1,249 1,304 645 673 192 200 973 1,301 672 200 971 1,299 671 200 970 1,289 670 199 971 1,299 671 200 Increase at Operating Revenue growth rate 19 3,020 1,811 4,831 3,153 1,891 5,044 3,147 1,887 5,034 3,141 1,883 5,024 3,138 1,882 5,020 3,141 1,883 5,024 Increase at Operating Revenue growth rate F 847 Plug to make balance sheet balance 24 Current Assets 25 Other 26 Total Assets 27 28 Bank Loan 29 Payables 30 CPLTD 31 Other 32 Current Liabilities 33 LTD 34 Equity 35 Total Liabilities & Equity 36 37 Working Capital 38 1,649 248 44 93 1,122 259 46 97 772 270 48 101 281 SO 105 1,371 292 52 109 2,362 303 54 114 Increase at Operating Revenue growth rate 2,034 1.282 1,733 1,877 920 4,831 1,524 1,831 1,689 5,044 1,191 1,783 2,060 5,034 1,823 1,682 1,515 5,020 2,833 1,628 LTD of previous year minus CPLTD of current year 563 Equity of previous year plus net income of current year 5,024 2,008 5,024 986 1,629 1,956 1,859 1,315 308 Exclude Bank Loan 39 40 41
