Apache Airlines, a privately held firm, is looking to buy additional gates at its home airport for $800,000 Apache has $240,000 in the bank but that money may not be spent as it is used to pay salaries, suppliers, and equipment Apache asked its bank for a loan but the bank refused saying that Apache's interest-bearing debt to equity was too high at 3.1 The bank said that Apache needed to lower that ratio below 2.3 in order to get the loan Separately, SkyBlue Airlines has approached Apache to see if Apache will buy it.

The price discussed by the two CEOs is 25x SkyBlue's 2018 net earnings You must calculate this price and compare it with the free cash flow value of SkyBlue, which you must also calculate The CFO wants to know if Apache is overpaying or underpaying for SkyBlue.

Please show all Excel formulas.

B D E F G H L M N 1 A I J K This case has the following road map: 2 Tab 3 WACC Shows the financials for both airlines and calculated the weighed average cost of capital (WACC) for the combined airline 4 Machine NPV Please calculate the NPV of the new gates 5 SkyBlue financials Calculate SkyBlue's income statement (IS) and balance sheet (BS) in order to run free cash flows in the valuation tab 6 SkyBlue valuation Calculate the present value of SkyBlue using free cash flows 7 Apache BS (balance sheet) Shows Apache's balance sheet for 2018 and forecast 2019-2021 8 Consolidated BS Consolidates Apache's and SkyBlue's balance sheets and recalculates the bank ratios 9 10 Please note: All figures are in thousand $ unless noted otherwise A B D E 1 SkyBlue Acquisition 2 - Earnings Multiple 3 - 2018 actual net earnings 4 Price 5 Value based on free cash flows: Price Paid 6 2019 2020 9 7 EBIT 8 EBIAT Change in Working Capital 10 Change in PP&E 11 Free Cash Flow 12 Terminal Value 13 Present Value 14 PV of Free Cash Flows 15 - Existing Debt 16 = Present Value of SkyBlue 17 18 (Underpay)/Overpay for SkyBlue 2021 Notes From Pro Formas - EBIT projection EBIT minus tax From Pro Formas (exclude Bank note plug!) From Pro Formas Calculation Assumes growth rate given Calculation Calculation Bank Loan + CPLT + LTD Calculated value If negative, Apache Airlines paid less than SkyBlue is worth B D E F G H L M N 1 A I J K This case has the following road map: 2 Tab 3 WACC Shows the financials for both airlines and calculated the weighed average cost of capital (WACC) for the combined airline 4 Machine NPV Please calculate the NPV of the new gates 5 SkyBlue financials Calculate SkyBlue's income statement (IS) and balance sheet (BS) in order to run free cash flows in the valuation tab 6 SkyBlue valuation Calculate the present value of SkyBlue using free cash flows 7 Apache BS (balance sheet) Shows Apache's balance sheet for 2018 and forecast 2019-2021 8 Consolidated BS Consolidates Apache's and SkyBlue's balance sheets and recalculates the bank ratios 9 10 Please note: All figures are in thousand $ unless noted otherwise A B D E 1 SkyBlue Acquisition 2 - Earnings Multiple 3 - 2018 actual net earnings 4 Price 5 Value based on free cash flows: Price Paid 6 2019 2020 9 7 EBIT 8 EBIAT Change in Working Capital 10 Change in PP&E 11 Free Cash Flow 12 Terminal Value 13 Present Value 14 PV of Free Cash Flows 15 - Existing Debt 16 = Present Value of SkyBlue 17 18 (Underpay)/Overpay for SkyBlue 2021 Notes From Pro Formas - EBIT projection EBIT minus tax From Pro Formas (exclude Bank note plug!) From Pro Formas Calculation Assumes growth rate given Calculation Calculation Bank Loan + CPLT + LTD Calculated value If negative, Apache Airlines paid less than SkyBlue is worth