Could you help me finishing solving problem 1 parts b-e. Excel attached

Group Project Kimberly Love Airron Wiggins Finance 620 McKENZIE CORPORATION'S CAPITAL BUDGETING Sam McKenzie is the founder and CEO of McKenzie Restaurants, Inc., a regional company. Sam is considering opening several new restaurants. Sally Thornton, the company's CFO, has been put in charge of the capital budgeting analysis. She has examined the potential for the company's expansion and determined that the success of the new restaurants will depend critically on the state of the economy over the next few years. McKenzie currently has a bond issue outstanding with a face value of $29 million that is due in one year. Covenants associated with this bond issue prohibit the issuance of any additional debt. This restriction means that the expansion will be entirely financed with equity at a cost of $5.7 million. Sally has summarized her analysis in the following table, which shows the value of the company in each state of the economy next year, both with and without expansion: 1. What is the expected value of the company in one year, with and without expansion? Would the company's stockholders be better off with or without expansion? Why? Value of the firm without expansion = $32,100,000 Value of the firm with expansion = $38,000,000 Stockholders will be better with expansion rather than without expansion because they will have a positive increase in the expected value of the firm. 2. What is the expected value of the company's debt in one year, with and without the expansion? Expected value of the firm's debt without expansion= $27,800,000 Expected value of the firm's debt with expansion = $28,400,000 3.One year from now, how much value creation is expected from the expansion? How much value is expected for stockholders? Bondholders? Value of the equity without expansion = $4,300,000 Value of the equity with expansion = $9,600,000 Stockholder's expected value = $-400,000 Bond holder's expected value = $5,900,000 4. If the company announces that it is not expanding, what do you think will happen to the price of its bonds? What will happen to the price of the bonds if the company does expand? If the firm decides not to expand the price of the bond will increase. But if the firm does expand the interest rate and stockholder value will increase and bond value will remain the same. 5. If the company announces that it is not expanding, what do you think will happen to the price of its bonds? What will happen to the price of the bonds if the company does expand? If the firm decides to expand interest rates will raise in the future attracting new shareholders. Likewise if the firm doesn't expand bond value will remain the same and interest will fall in the future. 6. If the company announces that it is not expanding, what do you think will happen to the price of its bonds? What will happen to the price of the bonds if the company does expand? If the expansion were financed by cash on hand shareholders would bear the whole of the expected loss in stock value. Positive's from using cash on hand is that the company won't have to bear the cost of changing equity into cash. This exam is open book and is to be your individual work ONLY. You are not allowed to consult with anyone in answering the questions (please remember the pledge signed at the beginning of semester). If you have questions about the exam, please post them in the Midterm Test Forum. That way, I can deal with all students as fairly as possible, especially those who might have the same question. I also reserve the right to delete posts or comments that provide solutions or answers to problems. You must show all your work for full credit; showing your work will also ease getting partial credit. I have shown the weights of each problem. Remember that quite often providing just a number is not enough, you have to give your recommendation or explanation. Each problem is located in a separate spreadsheet tab, which includes the text of the problem in the textbox and a suggested template. These templates are for your convenience only; you don't have to use them. If you decide to use them, then it is your responsibility to include all required information from the problems in the templates. If there is a discrepancy between numbers in the text box and numbers in the template, please use the numbers from the textbox. Feel free to add additional cells, rows and even separate tabs, if you need them. Please put your name on your exam and in the title of your files as well (last name and first initial, e.g., Doe_J_Fin630_Midterm). I will download all files first and I don't want tens of files named \"FIN630.\" Even if you decide not to use the templates, please consolidate all your Excel work in one workbook with multiple spreadsheets. It will help me if you name the spreadsheets \"Problem 1,\" \"Problem 2\" etc. to identify the questions/problems. If you need more than one spreadsheet per question, please name them accordingly. The exam is due by 11:59 pm, March 13. Late Policy specified in the Syllabus will be followed for any exam submitted after the due date. Please submit the completed exam under Midterm Exam in the Assignment Folder. Problem Problem 1 Problem 2 Problem 3 Total Grading Sheet Your Points Maximum 0 25 0 15 0 10 0.0% 0.0% 0.0% 0 0.0% 50 Solution Legend Value given in problem Formula/Calculation/Analysis required Qualitative analysis or Short answer required Goal Seek or Solver cell Score (filled by professor) PROBLEM 1 Solution Legend Value given in problem Formula/Calculation/Analysis required Qualitative analysis or Short answer required Goal Seek or Solver cell Score (filled by professor) This exercise asks you to forecast Square (SQ) free cash flow and discuss risks the company is facing. You may use and modify if necessary the template below or you may create your own template. a) download 2012-2015 historical financial data for the company, using one of the sources listed in Course Content (you may enter numbers in the template below or create your own). I recommend using 424B4 (prospectus) filing with the SEC b) Based on historical financial data calculate ratios that to be used later in pro-forma financial statements (revenue growth, gross margin etc., see chapter 6 for details); c) Using historical data from a) and ratios from b) create pro-forma statements (see chapter 6 for details); d) Estimate free cash flows FCF for 2016-2019 (see chapters 2 and 6 for details); e) What are risks the company is facing? Discuss, how its tornado diagram would look like (see chapter 3 for details). You don't have to build the diagram itself, unless you want to earn bonus points (5 points maximum); Solution a. Download Financial Data In Thousands, unless otherwise specified Assets, Current Cash and cash equivalents Restricted cash Settlements receivable Merchant cash advance receivable, net Other current assets Total current assets Property and equipment, net Goodwill Acquired intangible assets, net Restricted cash Other assets Total assets Current liabilities: Accounts payable Customers payable Accrued transaction losses Accrued expenses Other current liabilities Total current liabilities Debt Other liabilities Total liabilities Commitments and contingencies (Note 16) Stockholders' equity: Convertible preferred stock Common stock Additional paid-in capital Accumulated other comprehensive loss Accumulated deficit Total stockholders' equity Total liabilities and stockholders' equity Income Statement In Thousands, except Per Share data Revenues Transaction revenue Starbucks transaction revenue Software and data product revenue Hardware revenue Total net revenue Cost of revenue: Transaction costs Starbucks transaction costs Software and data product costs Hardware costs Amortization of acquired technology Total cost of revenue Gross profit Operating expenses: Product development Sales and marketing General and administrative Transaction and advance losses Amortization of acquired customer assets Impairment of intangible assets Total operating expenses Operating loss Interest (income) and expense Other (income) and expense Loss before income tax Provision (benefit) for income taxes Net loss Basic and diluted net loss per share (in dollars per sha Weighted-average shares used to compute net loss per Pro forma net loss per share attributable to common s Score Sep 30, 2015 Dec 31, 2014 Max Dec 31, 2013 174,083 12,017 156,188 43,638 32,561 418,487 85,012 58,899 20,726 14,579 2,243 597,946 225,300 11,950 115,481 29,302 27,834 409,867 63,733 40,267 10,279 14,394 3,348 541,888 7,528 238,085 16,004 35,669 13,954 311,240 4,541 95,794 7,488 9,272 12,646 129,741 50,244 361,484 5,436 148,648 8,452 17,368 11,202 191,106 30,000 47,110 268,216 514,945 514,945 366,197 249,954 -1,227 -527,160 236,462 597,946 155,166 -807 -395,632 273,672 $541,888 5 166,176 10,000 64,968 38,229 -693 -241,539 162,294 $318,341 2014 12,658 253,802 51,656 602 612 9,270 2,399 318,341 26,306 156,047 Year ended December 31 2013 2012 $707,799 $123,024 $12,046 $7,323 $850,192 $433,737 $114,456 $193,978 $9,471 $4,240 $552,433 $203,449 $450,858 $150,955 $2,973 $18,330 $1,002 $624,118 $226,074 $277,833 $139,803 $126,351 $12,547 $423,648 $128,785 $138,898 $64,551 $144,637 $112,577 $94,220 $24,081 $1,050 $82,864 $64,162 $68,942 $15,329 Nine months ended September 30 2015 2014 $376,565 ($150,491) $1,058 $1,104 ($152,653) $1,440 ($154,093) ($1.08) 142,042 ($0.56) $751,929 $95,199 $35,628 $10,002 $892,758 $501,468 $86,508 $6,022 $5,300 $599,298 $479,937 $118,542 $13,820 $16,636 $2,886 $631,821 $260,937 $318,501 $107,889 $1,032 $13,527 $602 $441,551 $157,747 $46,568 $56,648 $36,184 $10,512 $140,452 $107,170 $97,743 $40,840 $1,373 $104,967 $81,704 $68,585 $17,826 $591 $149,912 ($85,361) $5 ($167) ($85,199) $387,578 ($126,641) $995 $1,390 ($129,026) $2,502 ($131,528) ($0.88) 148,058 ($0.46) $273,673 ($115,926) $615 $737 ($117,278) ($257) ($117,021) ($0.84) 140,024 $6,012 $2,430 $233,727 ($104,942) ($12) ($950) ($103,980) $513 ($104,493) ($0.82) 127,845 ($85,199) ($0.71) 119,220 b. Calculate historical ratios, which you will need to create proforma statements Score Max 5 Score c. Using historical data from a) and ratios from b) create pro-forma statements Assets, Current 12/31/2015 12/31/2016 Cash and cash equivalents Investments, current portion Accounts receivable, net Prepaid expenses and other current assets Total current assets Investments, non-current Property and equipment, net Intangible assets, net Goodwill Other assets Total assets Current liabilities: Accounts payable Accrued payroll and compensation Accrued expenses and other liabilities Deferred revenue, current portion Total current liabilities Deferred revenue, non-current Other liabilities, non-current Total non-current liabilities Total liabilities Commitments and contingencies (Note 3) Stockholder's equity Preferred stock Common stock2014 Accumulated other comprehensive income (loss) Additional paid-in capital Accumulated deficit Total stockholders' equity Total liabilities and stockholders' equity Income Statement In Thousands, except Per Share data Revenues License Maintenance and services Total revenues Cost of revenues License Maintenance and services Total cost of revenues Gross profit Operating expenses Research and development Sales and marketing General and administrative Total operating expenses Operating loss Interest and other income (expense), net Interest income, net Other income (expense), net Change in fair value of preferred stock warrants Total interest and other income (expense), net Loss before income taxes Provision for income taxes Net loss 2015 5 12/31/2017 Year ended December 31 2016 2017 12/31/2018 2018 d. Using Pro-Forma Statements, estimate FCF 12/31/2019 2019 Score 2016 Max Year 2017 2018 Maximum 5 2019 EBIT EBIT(1-T) = NOPAT Plus: Depreciation Expense Less: CAPEX Less: Working Capital Investment Firm Free Cash Flow e) What risks is the company facing? Discuss, how its tornado diagram would look like. Bonus: The Tornado diagram itself Your Score Maximum 5 Your Score Maximum 5 PROBLEM 2 ABM considers selling a n artificial intelligence system Blue Watson . The management anticipates that new system will have the first year revenues of $30,000 K with subsequent annual revenue growth of 3%. Operating costs are 70% of revenues. The project requires $35 Mil investment in equipment, which will have a five year anticipated life and will be depreciated using five year MACRS depreciation method toward a zero book value (five year MACRS official depreciation rates are given below - it does require 6 years, this is not a typo!). However, the company will be able to sell the equipment on the after-market at the end of year 5 for 25% of its original cost. The company requires an 8% rate of return from its investment and faces a 35% tax rate (overall the company is profitable). In addition to capital investment, the project requires an outlay of net working capital equal to 20% of revenues in the coming year. I.e., at time 0 (beginning of year 1) net working capital requirement is $5,150 K and will grow in subsequent years. All NWC will be recovered after the project's end. a) Calculate the NPV and IRR for the project. Should the company undertake the project? (see chapter 2 for details) b) The marketing and operations department disagree with current projections for operating costs, first year revenues and revenue growth . Considering one factor at a time, at what level of operating costs, initial revenues, and revenues growth (decline) the project will break-even (NPV=0)? (see chapter 3 for details) c) Looking at percentage difference between the predicted level and critical (break-even) level of each of the three factors, which of them is the most critical? (see chapter 3 for details) Investment cost (today) Project Life Net Working Capital Year 1 revenues Operating costs After-market (salvage) value Revenue annual growth Required rate of return Tax rate MACRS 5 Year Depreciation Asset Book Value Year 1 20.00% Year 2 32.00% Year 3 19.20% Year 4 11.52% Year 5 11.52% Year 6 5.76% Given $ (35,000,000) 5 years 20.00% of revenues $ 30,000,000 70.00% of revenues 20% of initial investment 3.00% 8.00% 35% Solution a. Year Cash flow estimation Investment Revenues Operating costs EBITDA Less: Depreciation Incremental EBIT Less: Taxes NOPAT Plus: Depreciation Change in NWC After-Tax Cash from Asset Sale FFCF 0 $ (35,000,000) NPV IRR Analysis 1 $ $ $ $ (6,000,000) $ (41,000,000) 30,000,000 21,000,000 9,000,000 $ (7,000,000) 2,000,000 $ (700,000) 1,300,000 $ 7,000,000 (180,000) $ 8,120,000 2 30,900,000 21,630,000 9,270,000 $ (11,200,000) (1,930,000) $ 675,500 (1,254,500) $ 11,200,000 (185,400) $ 9,760,100 3 31,827,000 22,278,900 9,548,100 $ (6,720,000) 2,828,100 $ (989,835) 1,838,265 $ 6,720,000 (190,962) ### 4 32,781,810 22,947,267 9,834,543 $ (4,032,000) 5,802,543 $ (2,030,890) 3,771,653 $ 4,032,000 (196,691) ### 5 33,765,264.30 23,635,685 10,129,579 (4,032,000) 6,097,579 (2,134,153) 3,963,427 4,032,000 6,753,053 4,550,000 ### 254,031 8.21% The NPV is positive and IRR is more than required rate, so I would undetake this project. b) The marketing and operations department disagree with current projections for operating costs, first year revenues and revenue growth . b. Considering one factor at a time, at what level of operating costs, initial revenues, and revenues growth the project will break-even (NPV=0)? (see chapter 3 for details) Base case Break-even Using Goal Seek Score Your Score Maximum 5 Maximum 7 Operating costs (% of revenues) Year 1 revenues Revenues growth (decline) Cash flow estimation Investment Revenues Operating costs EBITDA Less: Depreciation Incremental EBIT Less: Taxes NOPAT Plus: Depreciation Change in NWC Cash from Asset Sale FFCF $ 70.00% 30,000,000 3.00% 0 $ (35,000,000) 1 2 3 4 5 NPV IRR c. Looking at percentage difference between the predicted level and critical (break-even) level of each of the three factors, which of them is the most critical? Your Score Maximum 3 Operating costs (% of revenues) Year 1 revenues Revenues growth Your answer. Base case Break-even 70.00% $ 30,000,000 3.00% Difference (%) Solution Legend Value given in problem Formula/Calculation/Analysis required Qualitative analysis or Short answer required Goal Seek or Solver cell Score (filled by professor) PROBLEM 3 UMUC Inc. has the balance sheet as shown below. Recently the yield on bonds similar to the ones that company has had dropped to 4.25%, so that the market value of the bonds is now about $325 million The rate on company' short-term notes is equal the market's rate on these notes, which is 2.95%. a) What are the company's enterprise value and capital structure weights? b) What is the company's cost of equity according to CAPM, if the U.S. T-bond yield is 2.75 %, the long-term market risk premium is 5.00% and the company's levered equity beta is 1.6? c) What is the company's WACC? Given 29-Feb-16 Balance Sheet Invested Capital (Book Values) (Market Values) Liabilities and Owners' Capital Current Liabilities Accounts payable $ Notes payable Other current liabilities` Total current liabilities $ Long-term debt (6.5% interest paid semiannually, due in March 2020) $ Total liabilities $ Owners' Capital Common stock (at $1 par value per share) $ Paid-in-capital Accumulated earnings Total owners' capital $ Total liabilities and owners' cap$ US Treasury Bond Yield Estimated Market or Equity Risk Pr Current Share Price $ Market value of owners' equity Current yield on the firm's long-term Current yield on the firm's short-term Dollar value of short term notes outs $ Corporate tax rate 7,550,000 10,000,000 22,266,000 39,816,000 $ 10,000,000 10,000,000 300,000,000 $ 339,816,000 $ 324,590,842 334,590,842 20,000,000 200,025,000 255,000,000 475,025,000 $ 814,841,000 $ 1,400,000,000 1,734,590,842 2.70% 4.50% 70.00 1,400,000,000 4.25% 2.95% 10,000,000 35% Solution Your Score $ 1.92% 2.76% 1.60 9.900000000% c. What is company's WACC? Source of Capital Notes Payable Long-term Debt Equity Maximu m 1,734,590,842 0.58% 18.71% 80.71% b. What is the company's cost of equity according to CAPM? After-Tax Cost of Sources of Capital Notes Payable (after-taxes) Long-term Debt (after-taxes) Levered equity beta Cost of Equity (using the CAPM) Maximu m 4 Your Score Enterprise value = Market capitalization + Debt Notes payable / Enterprise Value Long-Term Debt / Enterprise Value Equity / Enterprise Value Maximu m 3 Your Score a) What are the company's enterprise value and capital structure weights? Capital Structure Weight (Proportion) 0.58% 18.71% 80.71% After-Tax Cost 1.92% 2.76% 9.90% WACC Weighted After-Tax Cost 0.0111% 0.5169% 7.9904% 8.5184% 3 Solution Legend Value given in problem Formula/Calculation/Analysis required Qualitative analysis or Short answer required Goal Seek or Solver cell Score (filled by professor)