Please answer question #2 on the spreadsheet. Show any formulas used

Start

Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

Year 7

Year 8

Year 9

Year 10

Investment

Working Capital

Operating Cash Flow

Total Cash Flow

NPV

IRR

Note: Show all numbers in thousand dollars.

Problem Set 5 - Valuing an airline for acquisition 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 Eastem 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 Sky Blue, which you must also calculate. The CFO wants to know if Argo is overpaying or underpaying for SkyBlue. 5. Argo's forecast balance sheet has been included in the Excel file, so you need do nothing to it. However, the CFO has asked you to consolidate the two balance sheets - the Argo one given to you and the Sky Blue one that you calculated. Once these two are consolidated, you are asked to calculate three debt ratios, as listed in the file. 6. Finally, the CFO wants to know if the consolidated balance sheet's Debt/Equity ratio is below 0.5. If so, it will allow Argo to buy the gates. Is Argo's ratio low enough that it can borrow to buy the gates? Problem Set 5 - Valuing an airline for acquisition 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 Eastem 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 Sky Blue, which you must also calculate. The CFO wants to know if Argo is overpaying or underpaying for SkyBlue. 5. Argo's forecast balance sheet has been included in the Excel file, so you need do nothing to it. However, the CFO has asked you to consolidate the two balance sheets - the Argo one given to you and the Sky Blue one that you calculated. Once these two are consolidated, you are asked to calculate three debt ratios, as listed in the file. 6. Finally, the CFO wants to know if the consolidated balance sheet's Debt/Equity ratio is below 0.5. If so, it will allow Argo to buy the gates. Is Argo's ratio low enough that it can borrow to buy the gates