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 31December 2020 (i.e., =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 XROs fiscal year ended31December 2020.The data centers 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 XROs existing projects(i.e., gross profit divided by total revenue).

Section 1: Complete this section using Excel.For calculation purposes, assume we are at1 January2021. (30marks)

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 centers life in Excel. Be sure to make outflows negative and inflows positive.

a. You may assume that the data centers profitability will be similar to XROs existing projects in 2020 and estimate its profit margin by dividing XROs 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 XROs 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 tax expense for each year.2.Determine the internal rate of return of the project using the Excel function.

instructions:1.You should name both your Excel and Word documents

.2.Your Excel file should contain 3worksheets. 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 3is named Calculation and it should contain all your calculations for Sections1 and 2.

Table 1: Income Statement for Xero Limited(i.e., this table is also provided in the Excel File)

Income Statement
All numbers in thousands
Total revenue	7,00,235
Cost of revenue	1,03,911
Gross profit	5,96,323
Operating expenses Research development	1,73,792
Selling general and administrative	3,91,764
Total operating expenses	5,65,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	1,40,922
Diluted average shares	1,43,278
EBITDA	38,836

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/2020
Assets
Current assets
Cash
Cash and cash equivalents	1,05,320
Other short-term investments	4,17,327
Total cash	5,22,647
Net receivables	7,423
Other current assets	1,809
Total current assets	7,00,507
Non-current assets
Property, plant and equipment
Gross property, plant and equipment	1,27,098
Accumulated depreciation	-42,630
Net property, plant and equipment	84,467
Goodwill	76,799
Intangible assets	2,56,871
Other long-term assets	2,479
Total non-current assets	4,24,274
Total assets	11,24,781
Liabilities and stockholders' equity
Liabilities
Current liabilities
Accounts payable	10,349
Deferred revenues	9,366
Other current liabilities	5,934
Total current liabilities	1,13,222
Non-current liabilities
Long-term debt	4,13,949
Deferred tax liabilities	1,086
Other long-term liabilities	6,479
Total non-current liabilities	5,99,776
Total liabilities	7,12,998
Stockholders' equity
Common stock	6,60,564
Retained earnings	-3,32,004
Accumulated other comprehensive income	83,223
Total stockholders' equity	4,11,783
Total liabilities and stockholders' equity	11,24,781