Mini-Case II - Discounted Cash Flow Valuation

i need help with Excel sheet and a word document explaining the answers of question A,B,C,D,E. The formula sheet required to answer these questions are attached below.

Keep 4 decimals to round numbers in the process of your calculations, and state your final results with the correct units.

The following mini-case requires you to use the FCFF model, and the following numbers are in millions of Canadian Dollars (currency of Canada) unless told otherwise. Hint: Draw a timeline and label the cash flows and discount rates on the time line, which will make this problem much easier to understand.

You are asked to do fundamental valuation on Firebird Inc, a Montreal based Canadian video game developer. You and your group members sorted out the following information that is most relevant to your DCF valuations.

Firebird's current EBIT is 1,963, and its current interest expense is 650. It has a current capital spending of 146 and a current depreciation and amortization of 28. The effective tax rate and marginal tax rate for Firebird happens to be the same, which is 26.50%. For the current year, Firebird has inventories of 11, accounts receivable of 179, accounts payable of 4, a book value of debt of 2,672, a book value of equity of 11,596, a cash and marketable securities value of 4,696, and a non-cash operating assets value of 10,443. For the previous year-end, Firebird has inventories of 24, accounts receivable of 58, accounts payable of 1, a book value of debt of 2,667, a book value of equity of 11,392, a cash and marketable securities value of 4,225, and a non-operating assets value of 10,497.

Firebird is currently being traded on NYSE and its most recent stock price is $49.73 per share. One U.S. dollar is now worth 1.33 Canadian dollars. It currently has 767 million common shares outstanding. The firm has some preferred shares outstanding, and its aggregated market value is calculated to be 2,500. The firm has no convertible debts outstanding at the moment

The current nominal 10 year Treasury bond quarterly yield to maturity is 0.5975%, and your team decides to use the annual percentage rate instead of the annual effective rate. Canada currently has an inflation rate of 1.42%. Your team members believe in precise nominal risk free rate rather than the Fisher Effect. Based on the firm's interest rate coverage ratio and your analysis on the rating of the company's notes, you found the company default spread to be 1.87%. The Canadian and the U.S. equity risk premium are the same and equals to 5.04%. China currently has a country default spread of 0.76%. The standard deviation in the Shanghai Composite Stock Market Index is 33.75% and the standard deviation in Chinese government bond is 30.00%. Firebird gets 47.86% of its revenues in the U.S., 23.43% of its revenue in Canada and the rest of its revenue from China. However, you fail to acquire the percentage revenue the average Canadian firm gets in Canada. Your team went through votes and 4 out of 6 are supporting the operation based beta exposure approach for the cost of equity. One of your teammate did an excellent job on estimating the bottom-up beta, and the estimated value of the unlevered beta is 0.75 for the current year. Your teammates estimated the current ROC to increase about 15.00% next year based on the trend analysis from the last 5 years.

Based on the qualitative analysis, you decided to use a three-stage DCF model and a high growth period of 5 years for Firebird. The growth rate will remain its value during the first year of the high growth period and gradually transit to the growth rate in the stable growth period during the following years in the high growth period. In the stable growth period, you estimated the ROC to be 2% higher than the wacc in the stable growth period. The growth rate in the stable growth period is set to be 1% higher than the current long term risk free rate, and you need to compute the wacc in the stable growth period. You consider three aspects when calculating the wacc: firstly, have the beta set to 1; secondly, take the average of the debt to equity ratio of your firm and the industry average debt to equity ratio, which is 0.35 for the video game industry here, use the average of these two ratios as your debt ratios in the stable growth period. Thirdly, consider the emerging market country risk premium shrink to half of its current size in the stable growth period. Your team is assuming the growth rate and the cost of capital to gradually change to the stable growth rate and stable cost of capital, respectively, in a straight line fashion. While calculating the market value of debt, the pretax cost of debt is assumed to be fixed across the years. Your team decided to ignore the short-term debt, and you estimated that the firm has a 5-year average maturity of debt. In addition, you decided to do no adjustment on the market value of equity

Last but not the least, your team decided to capitalize the R&D expenses and convert the operating lease expenses to debt. The current R&D expense is 1,148. You estimated a 3-year amortizable life for the R&D, and the R&D expenses one year ago to 3 years ago are 1,103, 1,008, and 676, respectively. You assumed that R&D expenses are spent at the end of each year. The current operating lease expense is 39. You estimated a 6-year life for the operating lease assets, and the future commitments from year one to year six are 56, 67, 51, 46, 38, and 60, respectively. You decided to use a straight line depreciation method for both R&D and operating lease expenses

After a sip of water (sorry, no coffee) and with a bitter smile on your face, you begin the many hours journey of calculating the intrinsic value for Firebird:

a) What is the current year FCFF0) for Firebird?

b) What is the current year weighted average cost of capital for Firebird?

c) What is the terminal value at the end of the high growth period?

d) What is the firm value now according to your fundamental analysis?

e) Is Firebird overvalued or undervalued? By how much in percentage?