Scenario

Tesla is an American brand of electric vehicles. The brand was launched in $2003$ and it was only in $2008$ when it first started producing electric vehicles. The Tesla brand was envisioned by Elon Musk as an independent automaker which will focus on developing and producing electric vehicles at affordable prices. The brand's first car was the Tesla Roadster and was based on the same platform as Lotus Elise.

In $2012,$ Tesla began production of their first$-$mass market electric vehicle sedan, the Model S$.$ The launch of the Model S is what put Tesla on the map and gained a reputation as a disruptor of the automobile industry. They soon followed the Model S with another an SUV, the Model X SUV, which was launched in $2015.$ The Model $3$ sedan was introduced in $2017$ and quickly became the most successful electric car so far.

The CEO of Tesla has hired your team as business analytics consultants. The CEO wants you to confirm their analyses related to Model $3$ costs and sales. The client has asked that your team present your analyses in a PowerPoint presentation to the company's executive committee. Your company performs such analyses in Excel. As team leader, you know that although other powerful tools like SQL and Python exist, spreadsheets remain quick and accessible for data analysis.

Part A: Calculating the Companvs Breakeven

A company's break$-$even point is the point where its revenues equal or exceed its costs. The calculation for the break$-$even point can be done one of two ways: either breakeven in units to be sold, or breakeven in dollars $($the total amount for sales to cover or exceed costs$).$ A company's breakeven point is an important financial metric. The break$-$even point allows a company to know when it$,$ or one of its products, will become profitable. If a business's sales are below the break$-$even point, then the company is operating at a loss. If it$$s above, then it's operating at a profit.

Using the following data,

$1)$ Calculate the company's break$-$even in Units and Dollars for the Years $2018$ through $2022.$

we are equating "Total Automotive Costs" with Variable Costs, and the calculated Gross Prof Margin as Contribution Margin.

As a refresher, see Business Breakeven $($Investopedia$).$

$2)$ What was the average sales price of all models sold for each year?

$3)$ Visualize your findings in your presentation. Use visualizations that you think fit the story is telling. Stretch a little!

Another way is to create a break$-$even chart! Create a Breakeven Chart $2022$ data above.

Part B: Forecasting Sales

$ in millions $($multiply by $1,000,000)20182019202020212022$

Total Automotive Revenues $$18,515$ $$20,281$ $$27,236$ $$47,232$ $$71,462$

Total Automotive Costs $ $14,174$ $$15,858$ $$20,259$ $$33,393$ $$51,108$

Fixed $($R&D$,$ SG&A$)$ $$4,430$ $$4,138$ $$4,636$ $$7,083$ $$7,197$

Units Sold $($Delivered$)245,506367,656499,647936,2221,313,8511$

In this part of the case, use the data file below to analyze Telsa sales.

DESCRIPTIVE ANALYSIS

$1.$ Create a linear graph of the Monthly Units Sold data. Briefly describe the trend and cy units sold.

$2.$ Estimate the Annual Monthly growth for all months and create a new table with your calculations:

Compute the percentage change of the same month for the following year.

For example, calculate the percentage change between January $2016$ and January $2017$

January $\%$ change $2017=$ units sold in January $2017$ units sold in January $2016/$ units sold in January $2016*100$

$3.$ Create a linear graph for the annual monthly growth of sales. Briefly describe the monthly growth rates of units sold.

$4.$ Calculate the total units sold annually, present them in a new table, and create a linear graph. Briefly describe the annual trend of units sold.

$5.$ Estimate the annual growth of sales. Compute the percentage change annually and create a new table with your calculations.

For example, calculate the percentage change between $2016$ and $2017$:

$\%$ change $2017=$Total units sold in $2017$ total units sold in $2016/$ total units sold in $2016*100$

$6.$ Create a linear graph for the annual growth of sales. Briefly describe the annual growth rates of units sold.

B$.$ FORECASTING ANALYSIS

$1.$ Compute the Monthly Forecast for the rest of the year $2023$ using the Moving Average Model.

$2.$ You must present in one table your estimation for different number of lags $(-$k $+1)$ for the Moving Average Method to determine the optimal k$.$

$3.$ Compute the Monthly Forecast for the rest of the year using the Exponential Model.

$4.$ You must present in one table your estimation for different values of alpha to determine the optimal level of alpha.

$5.$ Annual Forecast for $2023$ and $2024$ using the Exponential Method.

Note: You must present in one table your estimation for different values of alpha to determine the optimal level of alpha.