BAFI3258 - Managerial Finance Assessment Task 3: CASE STUDY The Case Study introduces an opportunity to review the learning materials and employ what have been learnt to the process of making investment, financing and dividend decisions. This assessment covers all the CLOs of the course, and it is worth 40% of the Final Result THE CASE STUDY CONSISTS OF THREE PARTS (PART 1, 2 &3). You are the Chief Financial Officer (CFO) of Xero Limited (XRO.AX). Recently, XRO is venturing into the end-to-end encryption-based accounting software and partnering with the blockchain giant ULTRA. Two companies are working together to develop a blockchain-based hyperscale data center for the cloud-based encryptions. Building such data center would require a large investment from XRO. As a result, the development of this new data center will initially require a capital expenditure equal to 30% of the "total cash" for the fiscal year ended 31 December 2021 (i.e., t = 0). This new data center is expected to have a life of five years. The depreciation is calculated using the straight-line method. Both the estimated and actual salvage values are assumed to be zero (ie., to protect the patient privacy and confidentiality, this data center will be destroyed at the end of the useful life. Hence, the actual salvage value is zero). First-year revenue from this data center is expected to be 8% of the "total revenue" for XRO's fiscal year ended 31 December 2021. The data center's revenue is expected to grow at 15% for the second year, then 10% for the third, and 5% annually for the final two years of the expected life of the project. Your role in this project is to determine the cash flows associated with this data center. The CEO of XRO has informed you that the profit margin is similar to the rest of the XRO's existing projects (i.e., gross profit divided by total revenue). Section 1: Complete this section using Excel. For calculation purposes, assume we are on 1 January 2022. 1. You are now ready to determine the free cash flow. Compute the free cash flow for each year using the financial reports provided for XRO for 2021. Set up the computation of the free cash flow in separate, contiguous columns for each year of the data center's life in Excel. Be sure to make outflows negative and inflows positive. a. You may assume that the data center's profitability will be similar to XRO's existing projects in 2021 and estimate its profit margin by dividing XRO's "gross profit" by its "total revenue". b. Determine the annual depreciation by assuming XRO depreciates the data center by the straight-line method over a 5-year life (both the estimated and the actual salvage values are zero). c. Assume that XRO's effective tax rate is 30%. For simplicity, assume that the tax credit cannot be carried forward and XRO does not have any existing tax liabilities. Then calculateRMIT Classification: Trusted tax expense for each year. 2. Determine the internal rate of return of the project using the Excel function. Section 2: Complete this section using Excel and report your answers in a Word document. Estimate the CAPM / and Calculate Net Present Value Calculations as well as interest tax shield 1. Determine the cost of capital using data over the five-year period ending 31/12/2021 (i.e. 4/1/2017 - 31/12/2021). a. Using XRO daily stock price, All Ordinaries daily price and daily risk-free yield that are provided in the spreadsheet to compute CAPM . The can beestimated by regressing the XRO daily excess return on the daily market excess return. Present the graph with the best fitting line and a regression output table in your Excel sheet. Calculate the cost of equity using CAPM. b. Calculate the cost of debt by dividing the company's "Interest Expense" by the "Long-term Debt" for 2021. You may assume this cost of debt applies to this XRO's new investment. c. Calculate the weighted average cost of capital (i.e., note that you should use the closing share price on 31 December 2021 to calculate the equity weight in WWCC). You may assume the financing of the data center's capital expenditure is in line with the XRO's current capital structure. The ongoing cash outflows on expenses can be covered by the ongoing cash inflows, and thus no additional financing is required. 2. Calculate the NPV for this project. 3. Provide a sensitivity table for interest tax shield with the level of debt incrementing by 15%. Your starting point should be the actual level of debt that XRO adopts. You may assume that XRO is making nil profits on all other products/projects. Your sensitivity table should look like this: debt level Year 0 7% ?+15% ?+30% 6+457 Section 3: Answer the following questions in a Word document. You do not need any references in answering these questions. 1. Compare the internal rate of return (IRR) and the weighted average cost of capital (WACC) of this project. Please explain what causes the difference between IRR and WACC in this particular project. Please provide reasons with respect to re (cost of 2equity) and ra (cost of debt), separately. 2. Based on your answers in Q1 and Q2 of Section B, please analyze XRO's current capital structure (i.e., please provide advantages versus disadvantages associated with XRO's current capital structure choice based on Modigliani-Miller theorem). In addition, please illustrate what can you do to further optimize XRO's capital structure. 3. Discuss how current rise in inflation and interest rate affects XRO's operation. Instructions: 1. You should name both your Excel and Word documents (i.e., your full name and student ID). 2. Your Excel file should contain 3 worksheets. Worksheet 1 is named 'XRO financial statements'. You should copy the financial statements provided into this worksheet. Worksheet 2 is named 'Data', which contains the daily prices and returns (and excess returns) of XRO stock and S&P500. Worksheet 3 is named 'Calculation* and it should contain all your calculations for Sections 1 and 2.RMIT Classification: Trusted Table 1: Income Statement for Xero Limited (i.e., this table is also provided in the Excel File) Income Statement All numbers in thousands 12/31/2021 Total revenue 700.235 Cost of revenue 103,911 Gross profit 596,323 Operating expenses Research development 173,792 Selling general and administrative 391.764 Total operating expenses 565.555 Operating income or loss 30.768 Interest expense 29,245 Total other income/expenses net 1.095 Income before tax 9,591 Income tax expense 6,338 Income from continuing operations 3.252 Net income 3,252 Net income available to common 3.252 Basic EPS 0.02 Diluted EPS 0.02 Basic average shares 140.922 Diluted average shares 143.278 EBITDA 38.836RMIT Classification: Trusted Table 2 Balance Sheet for Xero Limited (i.e., this table is also provided in the Excel File) Balance Sheet All numbers in thousands 12/31/2021 Assets Current assets Cash Cash and cash equivalents 105,320 Other short-term investments 417,327 Total cash 522,647 Net receivables 7,423 Other current assets 1,809 Total current assets 700,507 Non-current assets Property, plant and equipment Gross property, plant and equipment 127,098 Accumulated depreciation 42,630 Net property, plant and equipment 84,467 Goodwill 76,799 Intangible assets 256,871 Other long-term assets 2,479 Total non-current assets 424,274 Total assets 1,124,781 Liabilities and stockholders' equity Liabilities Current liabilities Accounts payable 10,349 Deferred revenues 9,366 Other current liabilities 5,934 Total current liabilities 113,222 Non-current liabilities Long-term debt 413,949 Deferred tax liabilities 1,086 Other long-term liabilities 6,479 Total non-current liabilities 599,776 Total liabilities 712,998 Stockholders' equity Common stock 660,564 Retained earnings 332,004 Accumulated other comprehensive income 83,223 Total stockholders' equity 411,783 Total liabilities and stockholders' equity 1,124,781