Intro It is the end of 2022. You're a financial analyst working for Tesla Motors. The company is considering producing the new Model Z car and is predicting the following sales (in $ million). Assume all cash flows occur at the end of the year. B D E 1 2024 2025 2026 2027 2 Sales 210 273327.6360.36 COGS and SG&A are expected to add up to 70% of sales. After 2027, production will end and the factory will shut down. To start production at the beginning of 2024, Tesla has to invest $200 million now and $100 million in 2023 to build a new factory. After completion at the end of 2023, the factory can be depreciated straight to zero over 10 years. The factory is expected to be worth $100 million at the end of 2027. Net working capital consists mainly of inventory of car components, minus some current liabilities. Net working capital is expected to be always 15% of sales. Tesla has a beta of 1.5. Its target capital structure weight for equity is 90% and the expected return on the S&P 500 is 4.3%. The company also has $1.2 bn in outstanding bonds and an average yield on its bonds of 0.085. The yield on Treasury bills is 2%. Tesla's marginal tax rate is 32%. Part 1 IB Attempt 1/10 for 10 pts Open a spreadsheet with a blank tab and name it CF', for cash flows. Duplicate the table above and extend it to find the operating cash flow for each year. What is the operating cash flow for 2027 (in $ million)? 0+ decimals Submit Part 2 - Attempt 1/10 for 10 pts. Extend the spreadsheet to find net capital spending for each year. What is net capital spending for 2027 (in $ million)? Include the after-tax salvage value of the factory. 0+ decimals Submit Part 3 IB Attempt 1/10 for 10 pts. Extend the spreadsheet to find the change in net working capital for each year. What is the change in net working capital for 2027 (in $ million)? 1+ dedimals Submit Part 4 IB Attempt 1/10 for 10 pts. Extend the spreadsheet to find the cash flow from assets for each year. What is the CFA for 2027 (in $ million)? 0+ decimals Submit Part 5 IB Attempt 1/10 for 10 pts. Create a new tab in Excel for cost of capital calculations. Name it 'WACC'. What is Tesla's cost of equity? 3+ decimals Submit IB Attempt 1/10 for 10 pts. Part 6 What is Tesla's cost of debt? 3+ decimals Submit Part 7 IB Attempt 1/10 for 10 pts. What is the weighted average cost of capital (WACC)? ? 3+ decimals Submit IB Attempt 1/10 for 10 pts. Part 8 What is the NPV of the project in $ million)? 1+ decimals Submit Intro It is the end of 2022. You're a financial analyst working for Tesla Motors. The company is considering producing the new Model Z car and is predicting the following sales (in $ million). Assume all cash flows occur at the end of the year. B D E 1 2024 2025 2026 2027 2 Sales 210 273327.6360.36 COGS and SG&A are expected to add up to 70% of sales. After 2027, production will end and the factory will shut down. To start production at the beginning of 2024, Tesla has to invest $200 million now and $100 million in 2023 to build a new factory. After completion at the end of 2023, the factory can be depreciated straight to zero over 10 years. The factory is expected to be worth $100 million at the end of 2027. Net working capital consists mainly of inventory of car components, minus some current liabilities. Net working capital is expected to be always 15% of sales. Tesla has a beta of 1.5. Its target capital structure weight for equity is 90% and the expected return on the S&P 500 is 4.3%. The company also has $1.2 bn in outstanding bonds and an average yield on its bonds of 0.085. The yield on Treasury bills is 2%. Tesla's marginal tax rate is 32%. Part 1 IB Attempt 1/10 for 10 pts Open a spreadsheet with a blank tab and name it CF', for cash flows. Duplicate the table above and extend it to find the operating cash flow for each year. What is the operating cash flow for 2027 (in $ million)? 0+ decimals Submit Part 2 - Attempt 1/10 for 10 pts. Extend the spreadsheet to find net capital spending for each year. What is net capital spending for 2027 (in $ million)? Include the after-tax salvage value of the factory. 0+ decimals Submit Part 3 IB Attempt 1/10 for 10 pts. Extend the spreadsheet to find the change in net working capital for each year. What is the change in net working capital for 2027 (in $ million)? 1+ dedimals Submit Part 4 IB Attempt 1/10 for 10 pts. Extend the spreadsheet to find the cash flow from assets for each year. What is the CFA for 2027 (in $ million)? 0+ decimals Submit Part 5 IB Attempt 1/10 for 10 pts. Create a new tab in Excel for cost of capital calculations. Name it 'WACC'. What is Tesla's cost of equity? 3+ decimals Submit IB Attempt 1/10 for 10 pts. Part 6 What is Tesla's cost of debt? 3+ decimals Submit Part 7 IB Attempt 1/10 for 10 pts. What is the weighted average cost of capital (WACC)? ? 3+ decimals Submit IB Attempt 1/10 for 10 pts. Part 8 What is the NPV of the project in $ million)? 1+ decimals Submit
