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
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.
Apache'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 Apache one given to you and the SkyBlue one that you calculated Once these two are consolidated, you are asked to calculate three debt ratios, as listed in the file.
Please show all Excel formulas.
Finally, the CFO wants to know if the consolidated balance sheet's Debt/Equity ratio is below 2.5 if so, it will allow Apache to buy the gates Is Apache's ratio low enough that they can borrow to buy the gates?
4. 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. 5. Apache'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 Apache one given to you and the SkyBlue one that you calculated Once these two are consolidated, you are asked to calculate three debt ratios, as listed in the file. 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 A B D E F 1 2. Balance Sheet 3 Cash 4 Notes and Acc. Rec. 5 Inventory 6 Prepaid 7 Current Assets 8 Other 9 Total Assets Actual 2018 240 7,013 5,588 192 13,033 3,325 16,358 Projected 2019 2020 250 250 8,385 9,998 6,630 7,905 195 232 15,460 18,385 3,830 4,393 19,290 22,778 2021 Notes 250 12,040 9,520 280 22,090 5,105 27,195 9,321 6,070 3,057 433 1,477 11,037 2,458 7,586 3,705 450 1,712 13,453 2,008 4,417 450 1,994 16,182 1,558 11,404 5,320 450 2,350 19,524 1,108 10 11 Bank Loan 12 Payables 13 CPLTD 14 Other 15 Current Liabilities 16 LTD 17 18 Equity 19 Total Liabilities & Equity 20 21 Ratios: 22 Bank Loan to Receivables 23 Liabilities / Equity 24 Debt / Equity 25 2,863 16,358 3,829 19,290 5,038 22,778 6,563 27,195 0.9 4.7 3.1 0.9 4.0 2.6 0.9 3.5 2.2 0.9 3.1 2.0 A B D E 1 Actual Projected 2019 2020 2018 2021 2 Balance Sheet 3 Cash 4 Notes and Acc. Rec. 5 Inventory 6 Prepaid 7 Current Assets 8 Other 9 Total Assets 10 11 Bank Loan 12 Payables 13 CPLTD 14 Other 15 Current Liabilities 16 LTD 17 Equity 18 Total Liabilities & Equity 19 20 Ratios: 21 Bank Loan to Receivables 22 Liabilities / Equity 23 Debt / Equity 4. 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. 5. Apache'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 Apache one given to you and the SkyBlue one that you calculated Once these two are consolidated, you are asked to calculate three debt ratios, as listed in the file. 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 A B D E F 1 2. Balance Sheet 3 Cash 4 Notes and Acc. Rec. 5 Inventory 6 Prepaid 7 Current Assets 8 Other 9 Total Assets Actual 2018 240 7,013 5,588 192 13,033 3,325 16,358 Projected 2019 2020 250 250 8,385 9,998 6,630 7,905 195 232 15,460 18,385 3,830 4,393 19,290 22,778 2021 Notes 250 12,040 9,520 280 22,090 5,105 27,195 9,321 6,070 3,057 433 1,477 11,037 2,458 7,586 3,705 450 1,712 13,453 2,008 4,417 450 1,994 16,182 1,558 11,404 5,320 450 2,350 19,524 1,108 10 11 Bank Loan 12 Payables 13 CPLTD 14 Other 15 Current Liabilities 16 LTD 17 18 Equity 19 Total Liabilities & Equity 20 21 Ratios: 22 Bank Loan to Receivables 23 Liabilities / Equity 24 Debt / Equity 25 2,863 16,358 3,829 19,290 5,038 22,778 6,563 27,195 0.9 4.7 3.1 0.9 4.0 2.6 0.9 3.5 2.2 0.9 3.1 2.0 A B D E 1 Actual Projected 2019 2020 2018 2021 2 Balance Sheet 3 Cash 4 Notes and Acc. Rec. 5 Inventory 6 Prepaid 7 Current Assets 8 Other 9 Total Assets 10 11 Bank Loan 12 Payables 13 CPLTD 14 Other 15 Current Liabilities 16 LTD 17 Equity 18 Total Liabilities & Equity 19 20 Ratios: 21 Bank Loan to Receivables 22 Liabilities / Equity 23 Debt / Equity
