# 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 “operating income or loss” by its “total revenue”.

BAFI3258 – Managerial Finance

Assessment Task 3: CASE STUDY (100 marks – 40%)

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 “cash and cash equivalents” for the fiscal

year ended 31 December 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 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 “operating income or

loss” 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

2

RMIT 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 (40 marks)

1.Determine the cost of capital using data over the five-year period ending 31/12/2020 (i.e.

4/1/2016 – 31/12/2020).

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 be

estimated by regressing the XRO daily excess return on the market (All

Ordinaries) daily excess return. Present the graph with the best fitting line and

a regression output table in your Excel worksheet. Using other methods of

computing will be penalized. 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 2020. 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 2020 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. How sensitive is the interest tax shield to changes in the level of debt?

4. Provide a sensitivity table for interest tax shield with the level of debt incrementing by

10%. Your starting point should be the actual level of debt that XRO adopts and the

maximum amount of debt allowed is 75%. You may assume that XRO is making nil

profits on all other products/projects.

Your sensitivity table should look like this:

Interest tax shield Year 0

30% (assumed

starting level of

debt)

40%

50%

60%

70%

80%

Section 3: Answer the following questions in a Word document. You do not need any

references in answering these questions. (30 marks)

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

(cost of

3

RMIT Classification: Trusted

equity) and (cost of debt), separately.

2. Discuss the result in the interest tax shield sensitivity table.

3. 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.

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.

4

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/2020

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,836

5

RMIT 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/2020

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

## Leave a Reply

Want to join the discussion?Feel free to contribute!