Intro It is the end of 2020. You're a financial analyst working for Tesla Motors. The company is considering producing the new Model Y car and is predicting the following sales (in $ million). Assume all cash flows occur at the end of the year. | A B C D E 2022 2023 2024 2025 120 156187.2 205.92 2 | Sales COGS and SG&A are expected to add up to 70% of sales. After 2025, production will end and the factory will shut down. To start production at the beginning of 2022, Tesla has to invest $200 million now and $100 million in 2021 to build a new factory. After completion at the end of 2021, the factory can be depreciated straight to zero over 10 years. The factory is expected to be worth $100 million at the end of 2025. 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, until the last year of the project when it will all be recouped. Tesla has a weighted average cost of capital of 11% and a marginal tax rate is 27%. IB Attempt 2/10 for 8 pts. Part 2 Extend the spreadsheet to find net capital spending for each year. What is net capital spending for 2025 (in $ million)? Include the after-tax salvage value of the factory and enter it with the correct sign. Correct v After-tax salvage value in 2025: ATSV2025 = SV2025 - t (SV2025 - BV2025). The book value in 2025 equals the initial purchase price minus the accumulated depreciation after 5 years. A B C D E F G H I Value 2020 2021 2022 2023 2024 2025 Formula 2 Sales 120 156 187.2 205.92 3 Costs 0.7 84 109.2 | 131.04 144.14 | =$B3*H$2 | 4 Depr. 30 30 30 30 30 | =$B4 5 EBIT 6 16.8 26.16 31.78 | =H2-H3-H4 6 Taxes 0.27 1.62 4.536 7.06 8.58 | =$B6*H5 7 OCF 34.38 42.26 49.1 53.2 | =H5-H6+H4 9 BV 210 200 300 To 200 100 270 240 all 180 =G9-H4 oua =-(B10-B6* -121.6 |(B10-H9)) 10 NCS 100 NCS for 2025 is -$121.6 (million). It's a negative number even though it denotes a positive cash flow since we're subtracting NCS to find CFA. Part 3 IBM 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 2025 (in $ million)? 1+ decimals Submit Part 4 18 | Attempt 1/10 for 10 pts. Extend the spreadsheet to find the cash flow from assets for each year. What is the CFA for 2025 (in $ million)? 0+ decimals Submit Part 5 18 | Attempt 1/10 for 10 pts. What is the NPV of the project in $ million)? 0+ decimals Submit Intro It is the end of 2020. You're a financial analyst working for Tesla Motors. The company is considering producing the new Model Y car and is predicting the following sales (in $ million). Assume all cash flows occur at the end of the year. | A B C D E 2022 2023 2024 2025 120 156187.2 205.92 2 | Sales COGS and SG&A are expected to add up to 70% of sales. After 2025, production will end and the factory will shut down. To start production at the beginning of 2022, Tesla has to invest $200 million now and $100 million in 2021 to build a new factory. After completion at the end of 2021, the factory can be depreciated straight to zero over 10 years. The factory is expected to be worth $100 million at the end of 2025. 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, until the last year of the project when it will all be recouped. Tesla has a weighted average cost of capital of 11% and a marginal tax rate is 27%. IB Attempt 2/10 for 8 pts. Part 2 Extend the spreadsheet to find net capital spending for each year. What is net capital spending for 2025 (in $ million)? Include the after-tax salvage value of the factory and enter it with the correct sign. Correct v After-tax salvage value in 2025: ATSV2025 = SV2025 - t (SV2025 - BV2025). The book value in 2025 equals the initial purchase price minus the accumulated depreciation after 5 years. A B C D E F G H I Value 2020 2021 2022 2023 2024 2025 Formula 2 Sales 120 156 187.2 205.92 3 Costs 0.7 84 109.2 | 131.04 144.14 | =$B3*H$2 | 4 Depr. 30 30 30 30 30 | =$B4 5 EBIT 6 16.8 26.16 31.78 | =H2-H3-H4 6 Taxes 0.27 1.62 4.536 7.06 8.58 | =$B6*H5 7 OCF 34.38 42.26 49.1 53.2 | =H5-H6+H4 9 BV 210 200 300 To 200 100 270 240 all 180 =G9-H4 oua =-(B10-B6* -121.6 |(B10-H9)) 10 NCS 100 NCS for 2025 is -$121.6 (million). It's a negative number even though it denotes a positive cash flow since we're subtracting NCS to find CFA. Part 3 IBM 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 2025 (in $ million)? 1+ decimals Submit Part 4 18 | Attempt 1/10 for 10 pts. Extend the spreadsheet to find the cash flow from assets for each year. What is the CFA for 2025 (in $ million)? 0+ decimals Submit Part 5 18 | Attempt 1/10 for 10 pts. What is the NPV of the project in $ million)? 0+ decimals Submit
