代做MGT 180 – Business Finance帮做Python语言程序

Tesla Case

MGT 180 – Business Finance

There is perhaps no firm whose valuation is debated more heavily than Tesla (ticker: TSLA). The purpose of this case is to apply your capital budgeting skills in a valuation of Tesla.

Think of valuing a company like a big capital budgeting exercise. However, instead of forecasting cash flows for a single project, you will be forecasting cash flows for an entire company.   Likewise, instead of computing the NPV of an individual project, you will compute the NPV of the cash flows of the entire company. There will be one key difference: with projects we typically assume they come to an end, but with companies we don’t. Thus, we need to make an assumption about how cash flows in the company grow in the long run. Typically we forecast individual cash flows for 5-10 years and then make an assumption about how cash flows grow after that (more details on this below). Here are the steps for the Tesla case:

1.           Start with the Tesla excel worksheet I’ve posted on Canvas (Tesla.xlsx)

2.           The  spreadsheet gives assumptions about the year-over-year (YOY) growth of revenues  and related expenses for 2024-2031. These assumptions come from analyst reports and Tesla management forecasts. Combining these sources gives a bumpy growth forecast for the next few years which is why  the YOY growth estimates do not follow a smooth pattern at first. Use the base case assumptions in the excel sheet to build a forecast of earnings and free cash flows for the years 2024 to 2031. Specifically, start by forecasting revenues, research and development (R&D) and selling general and administrative expense  (SG&A) each year. Then, using these forecasts, compute the associated “cost of revenue”, research and development (R&D), selling general and administrative (SG&A), depreciation and capital expenditures. Cost of revenue are all costs of revenue not including depreciation, R&D and SG&A, so that Revenue –  Cost of Revenue – R&D – SG&A = Earnings Before Interest Taxes Depreciation and Amortization (EBITDA).  Subtract off depreciation to get EBIT.   Continue as we did in our capital budgeting exercises until you eventually get to Free Cash Flow. You can assume a tax rate of 21% and negligible changes in net working  capital.  You can also assume an opportunity cost of capital of 12% for all the FCFs.

3.           Beyond 2031, you will need to make an assumption about Tesla’s long-term growth once it becomes a mature company.

•    Start with the free cash flows you forecast for 2031 and assume that they will grow by 4% to 2032 and continue growing at 4% thereafter. You can then use the growing perpetuity formula to value all the cash flows after 2031.   By doing this, you will have what is called a “terminal value” or “continuation value” for Tesla as of the end of 2031 (one year before the first cash flow in the growing perpetuity).

4.           To avoid timing complexities, we will assume that it is now the beginning of 2024 and that the first cash flow (the 2024 FCF) will be generated exactly one-year from now, at the end of 2024. Discount all cash flows  back to the beginning of 2024 using a  12% cost of capital. The sum of total of these discounted cash flows is the estimated total enterprise value of Tesla at the beginning of 2024.  Enterprise Value = Equity + Debt – (Cash and Marketable Securities).

5.           To arrive at the price per (equity) share, you must subtract Tesla’s debt from its enterprise value and then add Tesla’s cash and marketable securities.

•    Tesla has $3.099 billion in debt and $22.185 billion in cash and marketable securities.

•    Tesla has 3.518 billion shares outstanding. Use this information to calculate the price per share.

6.           Next, perform some sensitivity analysis.

•    Use the Upside and Downside scenarios to compute Upside and Downside stock prices.

•    Using the base case, check the sensitivity of the valuation to the assumptions about Tesla’s cost of revenue. The sheet assumes that Tesla will be able to stabilize gross margins at 24%. This would be fairly high for the automotive industry, where gross margins can be as low as 10%. Calculate Tesla’s stock price if Cost of Revenue was 78%, 79%, 80%, and 82% in years 2024, 2025, 2026  and 2027, and then 85% from 2028 onward.

•    Using the base case, check the sensitivity of the valuation to the assumptions about Tesla’s cost of R&D.  The sheet assumes that eventually Tesla will be able to hold R&D growth to only 8% per year. As the space becomes more competitive, Tesla may have to keep R&D growth at 16% in order to keep its lead. Calculate Tesla's stock price if R&D levels out at 16% growth from 2027 onward.

7.           As of the writing of this case, Tesla’s stock price was $216.

•    Consider what it would take for your forecast to produce a valuation that equates to a $216 stock price.

•    By changing your revenue growth assumptions, find a set of YoY growth assumptions that would produce a stock price of approximately $216.  In other words, manually change (or use the solver function) the YoY growth assumptions so that you your valuation produces a stock price of $216.

•    With these revenue assumptions in place, compute the compound annual growth rate (CAGR) in  FCF from 2024 to 2031.  For example,  if your estimate of 2024  FCF  is $1,000 and for 2031  is $10,000, then you would use the “ RATE” function in Excel: =RATE(7,0,-1000,10000).

8.           An alternative way to value a stock is by use of multiples. NOTE: There is no reason to expect the multiples-based valuation to agree with your discounted cash flow (DCF) valuation.  This may be true for  a couple of reasons: 1) It is difficult to find a reasonable comparison company, and 2) Tesla may command  a premium valuation (which may be justified or not).

•    Start by valuing Tesla based on your 2024 projected EBITDA and using the Automobile industry  average Enterprise Value/EBITDA ratio of 6.9. This will give you a total company value which would replace your NPV of FCF in your share value calculation (you still need to subtract debt and add cash and marketable securities before dividing by shares outstanding to get the price per share).

•    Do the same with the average  EV/EBITDA ratio for the  broader Consumer Discretionary sector: 13.4.

Overall, what is your estimate of what Tesla’s stock price should be and what range of valuations do you think are reasonable? Defend  your conclusion by discussing and referencing  the  outcomes of the valuations in steps 5 to 8. You will have arrived at a range of prices and you will need to take a stand on how to interpret this range and which prices and assumptions to weigh more heavily. Your answer should take  the form. of a memo (maximum 2 single-spaced  pages)  that  references  spreadsheet  exhibits (the exhibits do not count toward the 2-page limit).

You are supposed to explain the highlights of your approach, synthesize what you found, draw a conclusion, and defend it.

This is a group case with a maximum number of five people per group.  Submission is electronic via Canvas with one submission per group. Submissions MUST also include your Excel file ending in .xlsx.   Please make sure your Excel file is not corrupted before submitting, and please list the name of each group member in your submission.