1. Fill in all empty boxes on the worksheet named “Xero historical ratios”. Link historical financial ratios and other numbers (as needed) to the related cells on the worksheet named “Xero DCF”. It is a good practice to have historical numbers and projected numbers side-by-side so we can notice any irregular line/items (maybe the assumptions are not reasonable or maybe we have made a mistake in the model). (20 marks)
2. Complete the projection and the valuation using the template provided on the “Xero DCF” worksheet. (Note: We make the assumptions for projections in the near future based on company’s plan and recent performance. We use average ratios for the Internet industry (sourced from Damodaran’s website) as the assumptions in 2025. Then, we extrapolate the assumptions for the middle years. ) (60 marks)
   1. Assume that, in 2016, revenues from New Zealand (NZ), Australia (AUS), United Kingdom (UK), North America and the Rest of the World (RoW) grow at 40%, 100%, 100%, 200% and 80% from 2015, respectively.
      2016. Assume that in 2017 revenues from AUS and UK continue to grow at the same rate as in 2016. After that, the growth rate decreases by half each year till it reaches the steady growth rate of 4% in 2025. That is, the growth rate in each year equals the average of the grow rate in the previous year and the long-term growth rate. Thus, the growth rate of revenue decreases more rapidly at the early stage of a company’s life.
   2. Assume that in 2017 and 2018 revenues from North America continue to grow at the same rate as in 2016. After that, the growth rate decrease by half each year till it reaches the steady growth rate of 4% in 2025 (similar to ii).
   3. Assume that from 2017 onward, the growth rate of New Zealand revenue decreases by half each year till it reaches the steady growth rate of 4% in 2025 (similar to ii).
   4. Assume that revenues from RoW grow at 80% in 2016 and steadily decrease to the long-term growth rate (similar to ii).
   5. Assume the EBITDA margins in 2016 for NZ, AUS, UK, North America and RoW are 45%, 15%, -25%, and 55%, respectively. Margins in new markets are lower than those in more mature markets because the company needs to spend a large sum in marketing and sales to gain a foothold in a new market. After 2016, these segment margins gravitate to industry mean of 30% in 2025, by changing by half each year (similar to ii).
   6. Assume corporate EBITDA as % of total revenue to be in 2016 and increasing by half each year to reach the industry average of   in 2025.
   7. Assume a marginal tax rate of 28% in 2015, and let it flow through to assumptions in years between 2016 and 2025.
   8. Assume OWC to be of operating revenue in 2016, and let it increase by half each year to the industry average of   in 2025.
   9. Assume capital expenditure on PP&E and intangible to be 45% of operating revenue in 2016, 2017 and 2018 and let it decrease by half each year and reach the industry average of 8% in 2025.
   10. Assume depreciation and amortisation (D&A) to be of beginning PP&E and intangible in 2016 and let it change by half each year to the industry average of  in 2025.
   11. Assume the long-term growth rate to be 4% after 2025.
3. Run sensitivity analysis on the main valuation assumptions. Highlight the three most important value drivers for the Xero stock in red? (16 marks)
4. Use the solver function to find out the revenue growth rate in 2016-2018 in the North American market that gives you a share price that equals to the closing price on 4^th March, 2016. What annual sales growth rate in North America (NA) between 2016 and 2018 does this share price imply? What is Xero’s market share in North America in 2025 implied by this share price? Fill your answer in cell F36 and F37 in “Xero DCF” worksheet. (4 marks)

 

Do you want your assignment written by the best essay experts? Then look no further. Our teams of experienced writers are on standby to deliver to you a quality written paper as per your specified instructions. Order now, and enjoy an amazing discount!!