Excel Exercise

公司金融代写 Excel Instructions: For this question I want all work to be clearly explained in the pdf solutions. Important components to include…

Excel Sheet Formatting Instructions: Please format the cells you will be using in Excel to include three decimal points. And to use commas as 1000 separator (drag and highlight cells you will use, right click, choose Format CellsNumber, Fill in Decimal places: 3, and click the box “Use 1000 separator”). 公司金融代写

Webticks is a young private fifirm that provides a streaming service platform to which it charges an annual subscription fee of $15 per user. Data reveals an expected user base of 10m in 2019. Due to the lockdowns from the pandemic. And general uncertainty, Webticks’ user base is expected to drop by 15% for 2020. Yet, given the high demand for streaming (due to stay at home orders) Webticks is expected to be able to boost its market share during the pandemic resulting in an expected increase in users by 45% in 2021 (from the 2020 level). Analysts’ best forecast predicts that, due to the ease of stay at home orders at the end of 2021, Webticks’ users would decline in 2022 by 10% of the 2021 level. And then continue growing at the pre-pandemic rate of 3% starting in 2023 (i.e. this growth rate does not apply to 2022).

The annual operating cost per user of Webticks’ operations depends on the number of users. And the year and is summarized by the following table:


As can be seen, Webticks faces a higher cost per user in the fifinal ”start-up” years of 2021,2022, and 2023. But only for the fifirst 8m users.3 This represents the fifixed cost of buying the content that Webticks streams. Starting in 2024 Webticks will own this content outright. And therefore faces cost per user of $8 independent of the number of users. The net capital expenditure for this purchase of content has already been accounted for prior to 2019 (i.e. we can ignore it) but the purchase is still being accounted for via D&A according to the straight line method at 2m per year with the last year being 2019.

Additional Capex from 2019 onward is equal to additional D&A (i.e. not including the 2m D&A in 2019). Additionally, change in NWC is equal to zero as the business requires very little working capital. Webticks is all equity, the required return on Webticks’ assets is 20%, and the corporate tax rate is 30%.  公司金融代写

5.1 Scenario 1: Give the most accurate possible valuation of Webtixs at the start of 2019 given the above forecasts using the APV method.

5.2 Scenario 2: Suppose instead that at the start of 2019 you did not know about the coronavirus outbreak and its potential to disrupt markets. In that case, you simply assumed the company will have 10m users in 2019. And that they are growing at the prepandemic rate of 3% after 2019 (all other data is the same). Compare the valuation under this scenario to that of Scenario 1.

5.3 Scenario 3 :Suppose that the user base for Webtixs is the same as in Scenario 2. But that Webtixs has 25m outstanding debt at the start of 2019 with an interest rate of 5%, linearly amortized over the next 5 years. In order to understand the required return on Webtixs equity you use data from Netflflix from 2016-2019. When it was in a com-parable growth phase (see the attached excel fifile). Assume Netflflix had the same leverage over this sample period as Webtixs starts with in 2019. The annual risk free rate in 2019 is expected to be 2.8% and the annual market return is expected to be 15.3%. Finally, we as-sume that the cost of debt for Webtixs remains constant at 4% over the lifetime of the loan. 公司金融代写

Give the most accurate possible valuation of Webtixs using the FTE method. You cannot use any estimates for the value of equity when using this approach. But you can use appropriate estimates for leverage (hint: you will need this to correctly proceed). Was your estimate of leverage accurate? (note that this exercise is labeled hard for a reason and that  you MUST use the FTE approach. Without using any of the prior calculations of value.  You can of course use the same initial calculations as in Scenario 2 so you don’t have to  repeat the same work. Also note that not all of the exercise is hard so there is potential  for partial credit.).