I did the last excel sheet already. Having trouble with the others. Need guidance
C Apache Airlines, A Privately Held X This case has the following road X M Inbox (936) - chelsea.collier.cc@ x + 5.2ProblemSet - Google Sheets X + X -> C A docs.google.com/spreadsheets/d/1Sw4qdaCOP5rAxemVMzDYr9RHvu2verbMS92hJn4Qy-w/edit#gid=780227429 EE 5.2ProblemSet 6 Share File Edit View Insert Format Data Tools Add-ons Help Last edit was 2 minutes ago C 100% - $ % .0 .00 123- Times New... 12 . B IS A Q B E- I - H . V C + Y - E. A fx Tab A B C D E F G H J K L M N P Q R S 1 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 B 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 11 12 13 14 15 16 17 18 19 20 21 + Read 1st! * WACC - Machine NPV - SkyBlue financials SkyBlue valuation Apache BS . Consolidated 1 + O 7:36C Apache Airlines, A Privately Held X This case has the following road x M Inbox (936) - chelsea.collier.cc@ x + 5.2ProblemSet - Google Sheets X + X -> C A docs.google.com/spreadsheets/d/1Sw4qdaCOP5rAxemVMzDYr9RHvu2verbMS92hJn4Qy-w/edit#gid=1301615892 EE 5.2ProblemSet 6 Share File Edit View Insert Format Data Tools Add-ons Help Last edit was 2 minutes ago C 7 100% * $ % .0 .00 123- Times New... 12 . B IS A Q B - I - H . V. C + Y - E. A fx A B C D E F G H 1 J K L M 1 Component Caledonian Laker Source 2 1 Net Income (M$) 33.40 24.10 Given 3 2 Earnings per share 1.04 1.53 Given 4 3 # of shares (M) 32.12 15.75 Net Income / Earnings per share 5 4 Price per share 14.00 12.63 Given 6 5 Market Value - Equity (M) 450 199 # of shares x Price per share 7 6 Market Value - Debt (M) 341 75 Given 8 7 Market Value - Total (M) 791 274 Market Value - Debt + Equity 9 8 -% Debt 43% 27% Market Value - Debt / Market Value - Total 10 9 -% Equity 57% 73% 1 - % Debt 11 10 Beta (levered) 1.07 0.93 Given 12 11 Beta (unlevered) 0.61 0.68 Beta (levered) x % Equity 13 12 Average Beta (unlevered) 0.64 Average 14 15 Values for combined Apache/SkyBlue airline: 16 % Debt 45.0% Given 17 % Equity 55.0% 1 - % Debt 18 Beta (relevered) 1.16 Average Beta (unlevered) / % Equity 19 Risk free rate 4.0% Assumption 20 Market risk premium 5.5% Historical figure 21 Expected equity return 10.4% CAPM calculation + Read 1st! * WACC - Machine NPV - SkyBlue financials SkyBlue valuation Apache BS - Consolidated 1 + Explore O 1 7 17:36C Apache Airlines, A Privately Held X This case has the following road X M Inbox (936) - chelsea.collier.cc@ x + 5.2ProblemSet - Google Sheets X + X -> C A docs.google.com/spreadsheets/d/1Sw4qdaCOP5rAxemVMzDYr9RHvu2verbMS92hJn4Qy-w/edit#gid=204394851 EE 5.2ProblemSet File Edit View Insert Format Data Tools Add-ons Help Last edit was 2 minutes ago 6 Share C 7 100% * $ % .0 .00 123- Times New... 12 . B IS A Q B 3 - - I . H . V . C + Y - E. A fx A B C D E F G H K L M N 0 P Q R S T 1 Start Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 2 Investment 3 Working Capital 4 Operating Cash Flow 5 Total Cash Flow 6 7 NPV 8 IRR 9 10 11 12 13 14 15 16 17 18 19 20 21 + Read 1st! * WACC Machine NPV - SkyBlue financials * SkyBlue valuation Apache BS - Consolidated 1 + O 7:36C Apache Airlines, A Privately Held X This case has the following road x M Inbox (936) - chelsea.collier.cc@ x + 5.2ProblemSet - Google Sheets X + X > C A docs.google.com/spreadsheets/d/1Sw4qdaCOP5rAxemVMzDYr9RHvu2verbMS92hJn4Qy-w/edit#gid=96429444 Search the menus (Alt+/) 100% * $ % .0 .00 123- Times New... 12 - B ISA fx Income Statement A B C E F G H 1 J K L MY N 1 Actual Projected 2 Income Statement 2018 2019 2020 2021 Notes 5 Tax Rate 21% 21% 21% 21% Use current statutory rate of 21% 5 Net Sales 5,000 Increase using the average growth rate 7 - Cost of Goods Sold (COGS) 3,075 Use same percent of Net Sales as in 2018 8 = Gross Profit 1,925 Calculation g - S,G & A Expenses 1,550 Use same percent of Net Sales as in 2018 10 = EBIT 375 Calculation (Use for Free Cash Flow valuation!) 11 - Interest 100 Hold at 2017 level 12 = EBT 275 Calculation 13 - Taxes 58 Use implicit rate of 2018 14 = Net Income 217 Calculation 15 Balance Sheet 16 Cash 88 17 Notes and Acc. Rec. 1,000 18 Inventory 800 Increase at average growth rate 19 Prepaid 62 20 Current Assets 1,950 21 Other 1,250 Leave flat 22 Total Assets 3,200 23 24 Bank Loan 250 See below 25 Payables 50 Increase at average growth rate Read 1st! WACC - Machine NPV - SkyBlue financials - SkyBlue valuation Apache BS Consolidated 1 + Explore O 7:37C Apache Airlines, A Privately Held X This case has the following road x M Inbox (936) - chelsea.collier.cc@ x + 5.2ProblemSet - Google Sheets X + X > C A docs.google.com/spreadsheets/d/1Sw4qdaCOP5rAxemVMzDYr9RHvu2verbMS92hJn4Qy-w/edit#gid=96429444 Search the menus (Alt+/) 100% * $ % .0 .00 123- Times New... * 12 - B I S A fx Income Statement A B C E F G H 1 J K L M N 1 Actual Projected 2 Income Statement 2018 2019 2020 2021 Notes 19 Prepaid 62 20 Current Assets 1,950 21 Other 1,250 Leave flat 22 Total Assets 3,200 23 24 Bank Loan 250 See below 25 Payables 50 Increase at average growth rate 26 CPLTD 50 Leave flat 27 Other 25 Leave flat 28 Current Liabilities 375 29 LTD 500 LTD of previous year minus CPLTD of current year 30 Equity 2,325 31 Total Liabilities & Equity Equity of previous year plus net income of current year 3,200 32 33 Working Capital 1,825 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. 37 38 30 Read 1st! * WACC - Machine NPV - SkyBlue financials * SkyBlue valuation Apache BS - Consolidated 1 + Explore O 7:37C Apache Airlines, A Privately Held X This case has the following road x M Inbox (936) - chelsea.collier.cc@ x + 5.2ProblemSet - Google Sheets X + X > C A docs.google.com/spreadsheets/d/1Sw4qdaCOP5rAxemVMzDYr9RHvu2verbMS92hJn4Qy-w/edit#gid=856870903 Search the menus (Alt+/) 100% * $ % .0 .00 123- Times New... * 12 - B I S A fx A B C E F G H I J K L 1 SkyBlue Acquisition 2 - Earnings Multiple 3 - 2018 actual net earnings 4 Price Price Paid 5 Value based on free cash flows: 6 2019 2020 2021 Notes EBIT From Pro Formas - EBIT projection 8 EBIAT EBIT minus tax g - Change in Working Capital From Pro Formas (exclude Bank note plug!) 10 - Change in PP&E From Pro Formas 11 Free Cash Flow Calculation 12 Terminal Value Assumes growth rate given 13 Present Value Calculation 14 PV of Free Cash Flows Calculation 15 - Existing Debt Bank Loan + CPLT + LTD 16 = Present Value of SkyBlue Calculated value 17 18 (Underpay)/Overpay for SkyBlue If negative, Apache Airlines paid less than SkyBlue is worth 19 20 21 22 23 + Read 1st! * WACC - Machine NPV - SkyBlue financials SkyBlue valuation Apache BS - Consolidated 1 + Explore O 1 7 17:37C Apache Airlines, A Privately Held X This case has the following road x M Inbox (936) - chelsea.collier.cc@ x + 5.2ProblemSet - Google Sheets X + X > C A docs.google.com/spreadsheets/d/1Sw4qdaCOP5rAxemVMzDYr9RHvu2verbMS92hJn4Qy-w/edit#gid=696573939 Search the menus (Alt+/) 100% * $ % .0 .00 123- Times New... * 12 - B IS A fx A B C D E F G H 1 J K L M N 1 Actual Projected 2 Balance Sheet 2018 2019 2020 2021 Notes 3 Cash 240 250 250 250 4 Notes and Acc. Rec. 7,013 8,385 9,998 12,040 5 Inventory 5,588 6,630 7,905 9,520 6 Prepaid 192 195 232 280 7 Current Assets 13,033 15,460 18,385 22,090 8 Other 3,325 3,830 4,393 5,105 g Total Assets 16,358 19,290 22,778 27,195 10 11 Bank Loan 6,070 7,586 9,321 11,404 12 Payables 3,057 3,705 4,417 5,320 13 CPLTD 433 450 450 450 14 Other 1,477 1,712 1.994 2.350 15 Current Liabilities 11,037 13,453 16,182 19,524 16 LTD 2,458 2,008 1,558 1,108 17 18 Equity 2,863 3.829 5,038 6,563 19 Total Liabilities & Equity 16,358 19,290 22,778 27,195 20 21 Ratios: 22 Bank Loan to Receivables 0.9 0.9 0.9 0.9 23 Liabilities / Equity 4.7 4.0 3.5 3.1 Read 1st! WACC - Machine NPV - SkyBlue financials * SkyBlue valuation Apache BS - Consolidated 1 + O 1 7 17:38C Apache Airlines, A Privately Held X This case has the following road x M Inbox (936) - chelsea.collier.cc@ x + 5.2ProblemSet - Google Sheets X + X > C A docs.google.com/spreadsheets/d/1Sw4qdaCOP5rAxemVMzDYr9RHvu2verbMS92hJn4Qy-w/edit#gid=696573939 Search the menus (Alt+/) 100% * $ % .0 .00 123- Times New... * 12 - B IS A fx A B C D E F G H 1 J K L 1 M N Actual Projected 2 Balance Sheet 2018 2019 2020 2021 Notes 5 Inventory 5,588 6,630 7,905 9,520 6 Prepaid 192 195 232 280 7 Current Assets 13,033 15,460 18,385 22,090 8 Other 3,325 3.830 4,393 5,105 g Total Assets 16,358 19,290 22,778 27,195 10 11 Bank Loan 6,070 7,586 9,321 11,404 12 Payables 3,057 3,705 4,417 5,320 13 CPLTD 433 450 450 450 14 Other 1.477 1,712 1,994 2.350 15 Current Liabilities 11,037 13,453 16,182 19,524 16 LTD 2,458 2,008 1,558 1,108 17 18 Equity 2,863 3,829 5,038 6,563 19 Total Liabilities & Equity 16,358 19,290 22,778 27,195 20 21 Ratios: 22 Bank Loan to Receivables 0.9 0.9 0.9 0.9 23 Liabilities / Equity 4.7 4.0 3.5 3.1 24 Debt / Equity 3.1 2.6 2.2 2.0 25 + Read 1st! WACC - Machine NPV - SkyBlue financials * SkyBlue valuation Apache BS - Consolidated 1 + O 1 7 17:38C Apache Airlines, A Privat X This case has the follow x M Inbox (936) - chelsea.col x + 5.2ProblemSet - Google x E Home X E 5.2 - Problem Set X + X C o erau.instructure.com/courses/105115/assignments/1892935?module_item_id=5873564 Modules Apache Airlines, a privately held firm, is looking to buy additional gates at its home airport for $500,000. Discussions Apache has $240,000 in the bank but that money may not be spent as it is used to pay salaries, suppliers, and Grades 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.5 in order to get Account People the loan. Separately, SkyBlue Airlines has approached Apache to see if Apache will buy it. EagleVision Apache's CFO hired you to help with financial analyses. To help, the CFO tells you the following: Dashboard Tutoring -Apache's cost of capital is ~7.7%. This is based on two airlines trading in the capital markets- Caledonian Tutoring and Laker. Since Apache does not trade, it has no beta, so the CFO used Caledonian and Laker as proxies Courses Tutoring - Aside from the purchase price, the gates will require a working capital infusion at purchase of $200,000. Tutoring Apache estimates the gates will generate cash flows of $280,000/year for the next 8 years. After that, the Calendar gates will revert back to the airport operator. You must calculate the NPV and IRR of the gates Hunt Library you were given SkyBlue's 2018 income statement (IS) and balance sheet (BS), along with forecasts of the Inbox Bookstore revenue growth and tax rates. You must forecast the IS and BS for the next 3 years you have been given the Excel file below to work with: Studio Nov 2019 template-3.xIs ? - The price discussed by the two CEOs is 44x SkyBlue's 2018 net earnings. You must calculate this price and Help 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 O 1 7 17:42C Apache Airlines, A Privat X This case has the follow x M Inbox (936) - chelsea.col x + 5.2ProblemSet - Google x E Home X E 5.2 - Problem Set X + X ( > C A erau.instructure.com/courses/105115/assignments/1892935?module_item_id=5873564 Apache estimates the gates will generate cash flows of $280,000/year for the next 8 years. After that, the Tutoring gates will revert back to the airport operator. You must calculate the NPV and IRR of the gates Hunt Library you were given SkyBlue's 2018 income statement (IS) and balance sheet (BS), along with forecasts of the Bookstore revenue growth and tax rates. You must forecast the IS and BS for the next 3 years Account you have been given the Excel file below to work with: Nov 2019 template-3.xIs Dashboard - The price discussed by the two CEOs is 44x 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 Courses 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, Calendar 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 Inbox - 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? Studio ? Problem Set 1 Rubric (2) Help Criteria Ratings Pts Description of criterion 100.0 pts 90.0 pts 80.0 pts 70.0 pts Excellent Above average Average Below average 100.0 pts Total Points: 100.0 O 1 7 17:42C | Chegg.com X *Homework Help - Q&Afr x M Inbox (937) - chelsea.col x + 5.2ProblemSet - Google X E Home X E 5.2 - Problem Set X + X C A docs.google.com/spreadsheets/d/1Sw4qdaCOP5rAxemVMzDYr9RHvu2verbMS92hJn4Qy-w/edit#gid=1953517155 Search the menus (Alt+/) 100% * $ % .0 .00 123- Times New... 12 - BISA fx A B C D E F G H I J K L M N 0 P Q R 1 Actual Projected 2 Balance Sheet 2018 2019 2020 2021 3 Cash 240 250 250 250 4 Notes and Acc. Rec. 7,013 8,385 9,998 12,040 5 Inventory 5,588 6,630 7,905 9,520 6 Prepaid 192 195 232 280 7 Current Assets 13,033 15,460 18,385 22,090 8 Other 3,325 3,830 4,393 5,105 9 Total Assets 16,358 19,290 22,778 27,195 10 11 Bank Loan 6,070 7,586 9,321 11,404 12 Payables 3,057 3,705 4,417 5,320 13 CPLTD 433 450 450 450 14 Other 1,477 1,712 1,994 2.350 15 Current Liabilities 11,037 13,453 16,182 19,524 16 LTD 2,458 2,008 1,558 1,108 17 Equity 2,863 3,829 1,558 6,563 18 Total Liabilities & Equity 16,358 19,290 19,298 27,195 19 20 Ratios: 21 Bank Loan to Receivables 0.9 0.9 0.9 0.9 22 Liabilities / Equity 4.7 4.0 3.5 3.1 23 Debt / Equity 3.1 2.6 2.2 2.0 Ist! * WACC - Machine NPV - SkyBlue financials * SkyBlue valuation Apache BS Consolidated BS - 1 + O 1 7 8:15
