Balance Sheet For Xero Limited All numbers in thousands 12/31/2020 Assets 12/31/2020 700,235 103,911 596.323 173,792 391,764 565,555 Income Statement for Xero Limited All numbers in thousands Total revenue Cost of revenue Gross profit Operating expenses Research development Selling general and administrative Total operating expenses Operating income or loss Interest expense Total other income/expenses net tax expense Income from continuing operations Net income available to common Basic EPS Diluted EPS Basic average shares Diluted average shares 105,320 417,327 522,647 7,423 1,809 700,507 Other Income before tax Income tax Net income 30,768 29.245 23,249 1.095 1,099 9,591 6.338 3.252 3,252 3.252 9,252 0.02 0.02 140,922 143,278 38,836 127,098 -42,630 84,467 76,799 256,871 2,479 424,274 1,124,781 EBITDA Current assets Cash Cash and cash equivalents Other short-term investments Total cash Net receivables content assets Total I current assets Non-current assets Property, plant and equipment Gross property, plant and equipment Accumulated depreciation Net property, plant and equipment Goodwill Intangible assets Other long-term assets Total non-current assets Total assets Liabilities and stockholders' equity Liabilities Current liabilities Accounts payable Deferred revenues Other current liabilities Total current liabilities Non-current liabilities Long-term debt Deferred tax liabilities Other long-term liabilities Total non-current liabilities Total liabilities Stockholders' equity Common stock Retained earnings Accumulated other comprehensive income Total stockholders' equity Total liabilities and stockholders' equity 10,349 9,366 5,934 113,222 413,949 1,086 6,479 599,776 712,998 660,564 -332,004 83,223 411,783 1,124,781 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 "cash and cash equivalents for the fiscal year ended 31 December 2020 (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 (i.e., 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 2020. 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 operating margin is similar to the rest of the XRO's existing projects. Section 1: Complete this section using Excel. For calculation purposes, assume we are at 1 January 2021. (30 marks) 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 2020. 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 2020 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 calculate RMIT Classification: Trusted tax expense for each year. 2. Determine the internal rate of return of the project using the Excel function. Balance Sheet For Xero Limited All numbers in thousands 12/31/2020 Assets 12/31/2020 700,235 103,911 596.323 173,792 391,764 565,555 Income Statement for Xero Limited All numbers in thousands Total revenue Cost of revenue Gross profit Operating expenses Research development Selling general and administrative Total operating expenses Operating income or loss Interest expense Total other income/expenses net tax expense Income from continuing operations Net income available to common Basic EPS Diluted EPS Basic average shares Diluted average shares 105,320 417,327 522,647 7,423 1,809 700,507 Other Income before tax Income tax Net income 30,768 29.245 23,249 1.095 1,099 9,591 6.338 3.252 3,252 3.252 9,252 0.02 0.02 140,922 143,278 38,836 127,098 -42,630 84,467 76,799 256,871 2,479 424,274 1,124,781 EBITDA Current assets Cash Cash and cash equivalents Other short-term investments Total cash Net receivables content assets Total I current assets Non-current assets Property, plant and equipment Gross property, plant and equipment Accumulated depreciation Net property, plant and equipment Goodwill Intangible assets Other long-term assets Total non-current assets Total assets Liabilities and stockholders' equity Liabilities Current liabilities Accounts payable Deferred revenues Other current liabilities Total current liabilities Non-current liabilities Long-term debt Deferred tax liabilities Other long-term liabilities Total non-current liabilities Total liabilities Stockholders' equity Common stock Retained earnings Accumulated other comprehensive income Total stockholders' equity Total liabilities and stockholders' equity 10,349 9,366 5,934 113,222 413,949 1,086 6,479 599,776 712,998 660,564 -332,004 83,223 411,783 1,124,781 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 "cash and cash equivalents for the fiscal year ended 31 December 2020 (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 (i.e., 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 2020. 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 operating margin is similar to the rest of the XRO's existing projects. Section 1: Complete this section using Excel. For calculation purposes, assume we are at 1 January 2021. (30 marks) 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 2020. 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 2020 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 calculate RMIT Classification: Trusted tax expense for each year. 2. Determine the internal rate of return of the project using the Excel function