Assessment Task 3: CASE
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., = 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 ended 31 December 2021. 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 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 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 2021 and estimate its profit margin by dividing XROs gross profit by its
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
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.
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
1. Determine the cost of capital using data over the (i.e. 4/1/2017 31/12/2021).
a. Using XRO daily stock price, All Ordinaries daily price and that are provided in the
spreadsheet to compute CAPM . The can be estimated 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 companys Interest Expense by the Long-term Debt for
2021. You may assume this cost of debt applies to this XROs 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
centers capital expenditure is in line with the XROs 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
Your sensitivity table should look like this:
debt level Year 0
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 (cost of
equity) and (cost of debt), separately.
2. Based on your answers in Q1 and Q2 of Section B, please analyze XROs current capital structure (i.e., please provide advantages versus disadvantages associated with XROs current capital structure choice based on ModiglianiMiller theorem). In addition, please illustrate what can you do to further optimize XROs capital structure.
3. Discuss how current rise in inflation and interest rate affects XRO’s operation.
1. You should name both your Excel and Word documents (i.e., your full name and
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.
Table 1: Income Statement for Xero Limited (i.e., this table is also provided in the Excel
All numbers in thousands 12/31/2021
Total revenue 700,235
Cost of revenue 103,911
Gross profit 596,323
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
Table 2 Balance Sheet for Xero Limited (i.e., this table is also provided in the Excel File)
All numbers in thousands 12/31/2021
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
Property, plant and equipment
Gross property, plant and equipment 127,098
Accumulated depreciation -42,630
Net property, plant and equipment 84,467
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
Accounts payable 10,349
Deferred revenues 9,366
Other current liabilities 5,934
Total current liabilities 113,222
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
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
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more