TESLA'S (2016-2017) SALES ANALYSIS

First Excel Project. Tags: Pivot tables, Dashboards, Sparkline, Data Visualization with Excel

Source: https://evcompare.io/cars/tesla/tesla_model_x_p90d/

“Consumer Reports recently brought out its yearly Owner Satisfaction Survey, and Tesla finished number one, with a whopping 91% of owners saying they'd buy a Tesla vehicle if they had to do it all over again”- INSIDER

Introduction

This is my First Excel Project. In this project, my primary focus was on creating an interactive dashboard with excel. Although that was my primary focus, it was not limited to that. Analysis was also done and business insights were derived from the analysis process.

Tesla is a car loved by many (myself included). It is eco-friendly and operates with high technology. Alongside other amazing features. Model X and Model S are examples of Tesla cars. This analysis looks at the Sales trend of two Tesla models from 2016-2017.

Statement of Problem

This analysis aims to answer the following questions;

• What model and version had the most sales?

• What is the most profitable year?

• What is the most profitable quarter?

• What is the least profitable quarter?

• What purchase type was used the most?

• Which selected country generated the highest revenue?

Data Analysis Process

Preparation

• The dataset was gotten from Kaggle. The data contains the sales of two-tesla models and their versions; Model X (90D, P90D) and Model S (60D AWD, 75 RWD, 75D AWD, and 90D AWD) from three countries; Australia, Germany, and the USA for the period of two years (2016-2017). The dataset contained 85593 rows and 7 columns which includes Model, Period, Country, Purchase type, Version, Price, and Gross Profit.

• The data is not a big one which made MS Excel my choice tool for cleaning, analysis, and visualization.

Data Cleaning

The data was already clean to an extent, except for the date that was in text format. I had to convert it to date format using text to columns and changed it to d/m/y format.

Fig 0: a screenshot of the excel workbook showing the data cleaning stage.

Data analysis

The statistical analysis was an easy one. I calculated the total revenue and gross profit for the two years, countries, and different models.

Fig i: Screenshot of excel workbook showing statistical analysis worksheet.

A new sheet was then created at each stage to properly explain the data and create understandable visualizations. Pivot tables were created for further analysis and visualization.

New sheets and Pivot tables like the screenshot below;

Fig ii: Screenshot of excel workbook showing pivot table worksheet.

Results

Fig iii: dashboard

(To interact with the dashboard, send a request in the comment session and it will be sent via mail)

Fig IV: Pie chart showing the distribution of the revenue by models. Model X is seen to have generated more revenue.

Fig v: Line Chart showing the distribution of the total revenue by versions with Model X-P90D topping the chart as the highest and Model S-90D AWD as the lowest.

Fig vi: Line chart showing the trend in sales from 2016-2017. A rise and fall in sales can be seen in this chart with a significant fall in the fourth quarter of 2017. From the chart, the second quarter in both years experienced a rise in sales while a reduction in sales can also be seen in the first and fourth quarter of both years.

Fig vii: Bar chart showing the purchase type used more. From the chart, the deposit method of purchasing was used more in purchasing the cars.

Fig viii: Bar chart showing the distribution of sales by country. The USA tops the chart with the highest generated revenue.

Summary

The total revenue attained for the two years (2016-2017) was $6,441,067,500 and a total Gross Profit of $1,878,359,596.

Model X had the most sales of $355,543,000,000 which is 55% of the total revenue. Two versions (90D and P90D) made up Model X.

Version P90D generated the highest revenue of $2,490,470,000 which is 38.67% of the total generated revenue. Four versions (60D AWD, 75 RWD, 75D AWD,90D AWD) made up Model S of which, Version 90D AWD generated the least revenue of $393,916,700 which added just 6.12% to the total revenue.

The second quarter of 2016 generated the highest revenue, 12.97% of the total revenue while the fourth quarter of 2017 generated the least revenue which is 11.64% of the total revenue.

Only three countries were represented in the dataset; The United States, Germany, and Australia. The United States; generating 81.12% of the total generated revenue made it the country with the highest sales, and Germany generated the least revenue, adding just 4.28% to the total generated revenue.

Conclusion

The United States can be said to be the country with the most Tesla owners. Why is this so? Could it be because of the cost, accessibility, or other reasons?

Using gut instinct, Tesla’s headquarter is in Texas which is in the USA. This might be the reason US citizens are the highest consumers of the product.

Model X, Version P90D is also seen to generate the most revenue, Could it be that it is the most loved Tesla brand?

From the data analysis, For Model X; Version P90D which generated the highest revenue is seen to be cheaper ($74,000) than Version 90D ($80,000). For Model S, Version 75RWD which generated the second highest revenue and the highest revenue in the Model S versions is seen to cost $70,000 which is the cheapest among the four versions. The lowest generated revenue is seen in Model S version 90D AWD and it cost the highest; $88,700. From this, COST is seen to be the major factor influencing sales. The cheaper the car, the more revenue it is likely to generate.

At the beginning and end of each year, there is a noticeable decrease in sales. But a significant increase in the second and third quarter, most especially the second quarter. Possibly, due to some social factors (using gut instinct). Thus, Advertising new brands or marketing may be great towards the beginning of the second quarter.

It is also noticed that deposit was the most used purchase type.