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.

You were given SkyBlue's 2018 income statement (IS) and balance sheet (BS), along with forecasts of the revenue growth and tax rates You must forecast the IS and BS for the next 3 years.

Please show all Excel fromulas.

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 E G 1 8 B D F Actual Projected 2 Income Statement 2018 2019 2020 2021 Notes 3 Key Assumptions: 4 Average growth rate: 0% 6% 6% 5 Tax Rate 21% 21% 21% 21% Use current statutory rate of 21% 6 Net Sales 5,500 Increase at average growth rate 7 - Cost of Goods Sold (COGS) 3,200 Use same percent of Net Sales as in 2018 Gross Profit 2,300 Calculation 9 - SG & A Expenses 1,600 Use same percent of Net Sales as in 2018 10 = EBIT 700 Calculation (Use for Free Cash Flow valuation!) 11 - Interest 110 Hold at 2018 level 12 = EBT 590 Calculation 13 - Taxes 124 Use implicit rate of 2018 14 = Net Income 466 Calculation 15 Balance Sheet 16 Cash 300 17 Notes and Acc. Rec. 1,300 18 Inventory Increase at average growth rate 1,100 19 Prepaid 80 20 Current Assets 2,780 21 Other 1,300 Hold at 2018 level 22 Total Assets 4,080 23 24 Bank Loan 830 See below 25 Payables 100 Increase at average growth rate 26 CPLTD 100 Hold at 2018 level 27 Other 125 Hold at 2018 level 28 Current Liabilities 1,155 29 LTD 675 LTD of previous year minus CPLTD of current year 30 Equity 2,250 Equity of previous year plus net income of current year 31 Total Liabilities & Equity 4,080 32 33 Working Capital 2,455 Exclude Bank Loan 34 35 Note: the Bank Loan is a plug figure. Its formula starts with total assets and subtracts the liability and equity accounts. 36 If the Bank Loan number is positive, it is debt. If negative, it is basically cash