I need help with the last spreadsheet, please.

Argo Airlines, a privately held firm, is looking to buy additional gates at its home airport for $350,000. Argo has money in the bank but that money may not be spent as it is used to pay salaries, suppliers, and equipment. Argo asked its bank for a loan but the bank refused saying that Argo's interest-bearing debt to equity was too high. The bank said that Argo needed to lower that ratio below 0.5 in order to get the loan. Separately, SkyBlue Airlines has approached Argo to see if Argo will buy it. Argo's CFO hired you to help with the following tasks: 1. Calculate Argo's cost of capital based on two airlines trading in the capital markets- Eastern and Western. Since Argo does not trade, it has no beta, so you need to use Eastern and Western as proxies. Hint - the textbook has the formula in two separate chapters. 2. Aside from the purchase price, the gates will require a working capital infusion of $95,000 at purchase. Argo estimates the gates will generate cash flows of $44,000/year for the next 10 years. After that, the gates will revert back to the airport operator. The working capital is recovered at the end. Calculate the NPV and IRR of the gates. 3. You were given SkyBlue's 2020 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. 4. The price discussed by the two CEOs is 24.5x SkyBlue's 2020 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 Argo is overpaying or underpaying for SkyBlue. Income Statement Net Sales - Cost of Goods Sold (COGS = Gross Profit - SG & A Expenses = EBIT - Interest = EBT - Taxes = Net Income Balance Sheet Cash Notes and Acc. Rec. Inventory Prepaid Current Assets Other Total Assets Actual 2020 4,233 2.700 1,533 1,199 334 77 257 54 203 Projected 2021 2022 2023 Notes 4,373 4,521 4,680 Increase using the average growth rate 2,789 2.884 2.985 Use same percent of Net Sales as in 2018 1,584 1.637 1,695 Calculation 1,239 1,281 1,326 Use same percent of Net Sales as in 2018 345 357 369 Calculation (Use for Free Cash Flow valuation!) 77 77 77 Hold level 268 280 292 Calculation 56 59 61 Use implicit rate of 2018 212 221 231 Calculation 230 238 246 254 664 686 709 734 Increase at average growth rate 715 739 764 790 65 67 69 72 1,674 1,729 1.788 1,851 102 105 109 Increase at average growth rate 1,776 1,831 1,894 1,960 102 Bank Loan Payables CPLTD Other Current Liabilities LTD Equity Total Liabilities & Equity 550 434 317 193 Plug to make balance sheet balance 33 34 35 36 Increase at average growth rate 42 42 42 42 Leave flat 34 34 34 34 Increase at average growth rate 659 545 428 305 125 83 41 (1) LTD of previous year minus CPLTD of current year 992 1,204 1.425 1,656 Equity of previous year plus net income of current year 1,776 1,831 1,894 1,960 Working Capital 1,565 1,619 1,677 1,738 Exclude Bank Loan B F 1 2 - Earnings Multiple 3 - 2020 actual net earnings Price 5 Value based on free cash flows: D E SkyBlue Acquisition 24.5 203 4,974 Price Paid 4 6 2021 345 77 2022 357 77 2023 369 77 9 7 EBIT 8 EBIAT - Change in Working Capital 10 - Change in Other assets 11 Free Cash Flow 12 PV of Free Cash Flows 13 - Existing Debt 14 = Present Value of SkyBlue equity 15 16 (Underpay)/Overpay for SkyBlue 17 18 WACC 19 Average growth rate 2023 TV Notes From Pro Formas - EBIT projection EBIT minus tax From Pro Formas (exclude Bank note plug!) From Pro Formas EBIAT plus changes in WC/Other assets Calculation Bank Loan + CPLT +LTD Calculated value If negative, the purchase prices is less than SkyBlue is worth 7% 3.5%