Help by showing the excel cells step, please.

Greatly appreciate

Tesla Valuation Model assumptions based on anaylst reports Upside YOY Revenue Growth Downside YOY Revenue Growth Base Case YOY Revenue Growth Total Cost of Revenue (\% of Rev) R\&D YOY Growth SG\&A YOY Growth Depreciation (\% of Rev) CapEx (\% of Depr) 76.0%47.0%10.0%5.0%90.0%95.0%75.0%19.0%8.0%4.8%85.0%90.0%74.0%21.0%7.0%4.5%80.0%90.0%74.0%18.0%5.0%4.0%75.0%90.0%74.0%16.0%4.0%3.8%70.0%85.0%74.0%14.0%3.0%3.5%70.0%85.0%74.0%12.0%2.0%3.0%70.0%85.0%74.0%10.0%2.0%3.0%70.0%85.0%74.0%8.0%2.0%3.0%70.0%85.0% Total Revenue in 2022 (in millions) Total R\&D in 2022 (in millions) Total SG\&A in 2022 (in millions) Cost of Capital (Discount Rate) Long run growth rate 81462 3075 3946 12% 3% 2. The spreadsheet gives assumptions about the year-over-year (YOY) growth of revenues and related expenses for 2023-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 2023 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 3% to 2031 and continue growing at 3% 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 2023 and that the first cash flow (the 2023 FCF) will be generated exactly one-year from now, at the end of 2023 . Discount all cash flows back to the beginning of 2023 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 2023. 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 76%,78%,79%,80%, and 82% in years 2023,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 $180. - Consider what it would take for your forecast to produce a valuation that equates to a $180 stock price. - By changing your revenue growth assumptions, find a set of YoY growth assumptions that would produce a stock price of approximately $180. In other words, manually change the YoY growth assumptions so that you your valuation produces a stock price of $180. - With these revenue assumptions in place, compute the compound annual growth rate (CAGR) in FCF from 2023 to 2031 . For example, if your estimate of 2023 FCF is $1,000 and for 2031 is $10,000, then you would use the "RATE" function in Excel: =RATE (8,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 2023 projected EBITDA and using the Automobile industry average Enterprise Value/EBITDA ratio of 5.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 Tesla Valuation Model assumptions based on anaylst reports Upside YOY Revenue Growth Downside YOY Revenue Growth Base Case YOY Revenue Growth Total Cost of Revenue (\% of Rev) R\&D YOY Growth SG\&A YOY Growth Depreciation (\% of Rev) CapEx (\% of Depr) 76.0%47.0%10.0%5.0%90.0%95.0%75.0%19.0%8.0%4.8%85.0%90.0%74.0%21.0%7.0%4.5%80.0%90.0%74.0%18.0%5.0%4.0%75.0%90.0%74.0%16.0%4.0%3.8%70.0%85.0%74.0%14.0%3.0%3.5%70.0%85.0%74.0%12.0%2.0%3.0%70.0%85.0%74.0%10.0%2.0%3.0%70.0%85.0%74.0%8.0%2.0%3.0%70.0%85.0% Total Revenue in 2022 (in millions) Total R\&D in 2022 (in millions) Total SG\&A in 2022 (in millions) Cost of Capital (Discount Rate) Long run growth rate 81462 3075 3946 12% 3% 2. The spreadsheet gives assumptions about the year-over-year (YOY) growth of revenues and related expenses for 2023-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 2023 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 3% to 2031 and continue growing at 3% 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 2023 and that the first cash flow (the 2023 FCF) will be generated exactly one-year from now, at the end of 2023 . Discount all cash flows back to the beginning of 2023 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 2023. 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 76%,78%,79%,80%, and 82% in years 2023,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 $180. - Consider what it would take for your forecast to produce a valuation that equates to a $180 stock price. - By changing your revenue growth assumptions, find a set of YoY growth assumptions that would produce a stock price of approximately $180. In other words, manually change the YoY growth assumptions so that you your valuation produces a stock price of $180. - With these revenue assumptions in place, compute the compound annual growth rate (CAGR) in FCF from 2023 to 2031 . For example, if your estimate of 2023 FCF is $1,000 and for 2031 is $10,000, then you would use the "RATE" function in Excel: =RATE (8,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 2023 projected EBITDA and using the Automobile industry average Enterprise Value/EBITDA ratio of 5.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