excel

Tesla just demonstrated their Tesla Bot at Al Day 2022. Always the risk takers, they debuted the robot without a tether for the first time on stage - they did an easy demo so the bot wouldn't "fall on its face". Tesla wants you to help assess the Tesla Bot project. Tesla spent $1 million developing the Optimus prototype that walked on stage, $1 million each on three other prototypes, and an additional $300 million in research and development (their R\&D expense for the twelve months ending June 30 , 2022 was $2.883 billion). The project will officially begin (t=0) in January 2023 . At the end of 2027(t=5), Tesla expects this Bot 1.0 project to end and make way for Bot 2.0. The tooling and equipment required for producing the Bots will cost $2.5 billion. The tooling and equipment is classified as a seven-year asset and will be depreciated using Modified ACRS depreciation (see Table 10.7); no bonus depreciation will be used. The estimated market value of the tooling and equipment at the end of the project is $250 million. Tesla plans to make millions of Bots (true story). Their goal, is to get 700,000 of them produced and sold in the first 5 years. In year one, Tesla thinks they can sell 60,000 Bots. The following four years they expect quantity sold to grow by 40% per year. Tesla believes that the Tesla Bot will sell for $20,000. Tesla doesn't have much experience producing robots and there will be a host of challenges involved in giving the consumer what they might want in a robot. But Tesla believes they can take advantage of hardware, software, manufacturing and supply chain advantages developed for its car business. Tesla thinks the variable costs will be $13,000. Fixed costs for the Tesla Bot production are estimated to be $9.5 million per year. Net working capital for the Bot project will be 25% of sales and will occur with the timing of the cash flows for the year. There is no initial outlay for NWC; changes in NWC will first occur in year one with the first year's sales. Tesla's tax rate is 11% and they have a required return of 22%. These Bots need charging! Tesla thinks that Bots will often be bought with Powerwalls, maybe around 50% of the Bots will be also include a Powerwall sale. While a one-battery Powerwall usually sells for $11,500, Tesla will offer a discount of 20% if you buy a Powerwall with your Bot. Variable costs for the Powerwalls are $7,500. There are no fixed costs, investments in NWC, or capital expenditures associated with the additional Powerwall sales. a) What is the payback period of the Tesla Bot? b) What is the profitability index of the project? c) What is the IRR of the project? d) What is the NPV of the project? e) Based on your answers for a-d, what would you suggest Tesla do knowing that Elon is VERY determined to get the Tesla Bot to the market? PART 2: Tesla is pretty unsure about their sales projections. They aren't big spenders in terms of marketing research, and the Tesla Bot is a big question mark in terms of demand and the going rate for a Bot like this is tough to assess. They need you to analyze how changes in the price of the Bot as well as changes in the quantity sold will affect the NPV of the project. a) How sensitive is the NPV to changes in price? b) How sensitive is the NPV to changes in quantity sold? (same \% change in sales each year) c) Based on a and b, what additional information would you give Tesla that might help them make a decision regarding the Tesla Bot. ADDITIONAL INSTRUCTIONS Part of what you will be graded on is your Excel proficiency. An excellent assignment would have: - A title at the top of the page - Part 1 in a single Excel tab and Part 2 in a single tab - Changes in inputs that flow through and update the entire spreadsheet - Attractive - Everything understandable and easy to navigate - No values typed into Excel more than once - No results calculated more than once (in other words, previously calculated results can be referenced, but not recalculated) Tesla just demonstrated their Tesla Bot at Al Day 2022. Always the risk takers, they debuted the robot without a tether for the first time on stage - they did an easy demo so the bot wouldn't "fall on its face". Tesla wants you to help assess the Tesla Bot project. Tesla spent $1 million developing the Optimus prototype that walked on stage, $1 million each on three other prototypes, and an additional $300 million in research and development (their R\&D expense for the twelve months ending June 30 , 2022 was $2.883 billion). The project will officially begin (t=0) in January 2023 . At the end of 2027(t=5), Tesla expects this Bot 1.0 project to end and make way for Bot 2.0. The tooling and equipment required for producing the Bots will cost $2.5 billion. The tooling and equipment is classified as a seven-year asset and will be depreciated using Modified ACRS depreciation (see Table 10.7); no bonus depreciation will be used. The estimated market value of the tooling and equipment at the end of the project is $250 million. Tesla plans to make millions of Bots (true story). Their goal, is to get 700,000 of them produced and sold in the first 5 years. In year one, Tesla thinks they can sell 60,000 Bots. The following four years they expect quantity sold to grow by 40% per year. Tesla believes that the Tesla Bot will sell for $20,000. Tesla doesn't have much experience producing robots and there will be a host of challenges involved in giving the consumer what they might want in a robot. But Tesla believes they can take advantage of hardware, software, manufacturing and supply chain advantages developed for its car business. Tesla thinks the variable costs will be $13,000. Fixed costs for the Tesla Bot production are estimated to be $9.5 million per year. Net working capital for the Bot project will be 25% of sales and will occur with the timing of the cash flows for the year. There is no initial outlay for NWC; changes in NWC will first occur in year one with the first year's sales. Tesla's tax rate is 11% and they have a required return of 22%. These Bots need charging! Tesla thinks that Bots will often be bought with Powerwalls, maybe around 50% of the Bots will be also include a Powerwall sale. While a one-battery Powerwall usually sells for $11,500, Tesla will offer a discount of 20% if you buy a Powerwall with your Bot. Variable costs for the Powerwalls are $7,500. There are no fixed costs, investments in NWC, or capital expenditures associated with the additional Powerwall sales. a) What is the payback period of the Tesla Bot? b) What is the profitability index of the project? c) What is the IRR of the project? d) What is the NPV of the project? e) Based on your answers for a-d, what would you suggest Tesla do knowing that Elon is VERY determined to get the Tesla Bot to the market? PART 2: Tesla is pretty unsure about their sales projections. They aren't big spenders in terms of marketing research, and the Tesla Bot is a big question mark in terms of demand and the going rate for a Bot like this is tough to assess. They need you to analyze how changes in the price of the Bot as well as changes in the quantity sold will affect the NPV of the project. a) How sensitive is the NPV to changes in price? b) How sensitive is the NPV to changes in quantity sold? (same \% change in sales each year) c) Based on a and b, what additional information would you give Tesla that might help them make a decision regarding the Tesla Bot. ADDITIONAL INSTRUCTIONS Part of what you will be graded on is your Excel proficiency. An excellent assignment would have: - A title at the top of the page - Part 1 in a single Excel tab and Part 2 in a single tab - Changes in inputs that flow through and update the entire spreadsheet - Attractive - Everything understandable and easy to navigate - No values typed into Excel more than once - No results calculated more than once (in other words, previously calculated results can be referenced, but not recalculated)