Juarez.Jonathan_DA
docx
keyboard_arrow_up
School
Texas State University *
*We aren’t endorsed by this school
Course
3313
Subject
Industrial Engineering
Date
Dec 6, 2023
Type
docx
Pages
5
Uploaded by CaptainPuppyMaster778
Instructions: Use the Excel spreadsheet provided and follow the instructions below to complete the data analytics assignment. Once you complete the assignment, you will upload Word document (with answers of course) and supporting excel file in Canvas. Please make sure to name the Word and Excel files “
Lastname.firstname_DA”. Data set: Open “DA_Dataset”. Verify that worksheet “full_data” includes 7,342 observations. Identify the automotive industry: pick a large auto manufacturer, identify its Standard Industry Classification (SIC) code. Filter the data by this SIC code, and copy the data into a 2
nd
worksheet
(and name it “Auto Industry”).
In this “Auto Industry” worksheet, calculate market capitalization (“market value” or “MV” for short) as “Common Shares Outstanding” x “Price Close - Annual – Fiscal.” Once the above step is complete, sort data by “Data Year- Fiscal” and identify 5 largest auto firms by market capitalization as of the end of fiscal year 2022. Copy data for these firms for fiscal years 2018 through 2022 (5 years), into a new worksheet (and name it “five-largest”). Calculate current ratio, gross profit margin, net profit margin, and debt-to-equity ratio. (HINT: Current ratio= Current assets-total/current liabilities-total; Gross profit margin = Gross profit/ total revenue, net profit margin= net income (loss)/ total revenue; debt-to-equity ratio= [debt in current liabilities + long-term debt]/Stockholder’s equity total). Create pivot tables to answer questions #1 through #7 below.
While you do not have to use pivot tables to answer these questions, it will make the assignment substantially easier. Create these tables in a 4
th
worksheet (labeled “Ratios & Charts”)
. Also, you will need to create line charts for questions #4 through #6 below. 1
As a final product, you will submit word document and your excel work. In the word document, answer the following questions: 1.
As of FY 2022, which are the five companies with largest market capitalization
in the SIC code 3711, listed from highest (1
st
) to lowest (5
th
)? How much is the market capitalization as of the end of FY 2022 for these companies?
Rank
Firm MV ($, in millions)
1
TESLA INC
$389,741.52
2
TOYOTA MOTOR CORP
$192,205.04
3
VOLKSWAGEN AG
$78,994.10
4
BYD COMPANY LTD
$71,700.83
5
General Motors
$47,096.00
2.
For each of the fiscal years during 2018-2022, which company had the highest current ratio, gross profit margin, net profit margin, and debt to equity ratio? Use the table provided below. Fiscal Year
Current Ratio
(Highest)
GPM Ratio (Highest)
NPM Ratio (Highest)
Debt-to-
Equity Ratio (Highest)
2018
VOLKSWAGEN
VOLKSWAGEN
TOYOTA MOTOR CORP
GENERAL MOTORS CO
2019
TESLA INC
VOLKSWAGEN
TOYOTA MOTOR CORP
GENERAL MOTORS CO
2020
TESLA INC
VOLKSWAGEN
TOYOTA MOTOR CORP
GENERAL MOTORS CO
2021
TESLA INC
TESLA INC
TESLA INC
GENERAL MOTORS CO
2022
TESLA INC
TESLA INC
TESLA INC
GENERAL MOTORS CO
3.
For each of the fiscal years during 2018-2022, which company had the lowest current ratio, gross profit margin, net profit margin, and debt to equity ratio? Use the table provided below.
Fiscal Year
Current Ratio
GPM Ratio (Lowest)
NPM Ratio (Lowest)
Debt-to-
Equity Ratio 2
(Lowest)
(Lowest)
2018
TESLA INC
BYD COMPANY LTD
TESLA INC
TOYOTA MOTOR CORP
2019
GENERAL MOTORS CO
BYD COMPANY LTD
TESLA INC
TOYOTA MOTOR CORP
2020
GENERAL MOTORS CO
BYD COMPANY LTD
TESLA INC
TESLA INC
2021
BYD COMPANY LTD
BYD COMPANY LTD
BYD COMPANY LTD
TESLA INC
2022
BYD COMPANY LTD
BYD COMPANY LTD
BYD COMPANY LTD
TESLA INC
4.
Copy and paste the line chart of annual revenue for fiscal years 2018-2022
for the largest five companies from question #1. Make sure that the chart is correctly labeled in both axis. 0
50000
100000
150000
200000
250000
300000
350000
2018
2019
2020
2021
2022
BYD COMPANY LTD
GENERAL MOTORS CO
TESLA INC
TOYOTA MOTOR CORP
VOLKSWAGEN AG
5.
Copy and paste the line chart of annual net income for fiscal years 2018-2022
for the largest five companies from question #1. 3
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
-5000
0
5000
10000
15000
20000
25000
2018
2019
2020
2021
2022
BYD COMPANY LTD
GENERAL MOTORS CO
TESLA INC
TOYOTA MOTOR CORP
VOLKSWAGEN AG
6.
Copy and paste the line chart of annual market value for fiscal years 2018-2022
for the
largest five companies from question #1. 0
200000
400000
600000
800000
1000000
1200000
2018
2019
2020
2021
2022
BYD COMPANY LTD
GENERAL MOTORS CO
TESLA INC
TOYOTA MOTOR CORP
VOLKSWAGEN AG
7.
Based on the above analysis, which company do you believe has the largest volatility in market capitalization during 2018-2022 period? Why do you believe there is such a volatility in market cap for this company? 4
There is a large volatility for the market capitalization of Tesla. This is due to tesla being the forefront of electric vehicles technology and sustainable energy solutions, and rapid growth in these industries. There is speculation for this company that investors believe it has a strong base in its industry and will continue to have potential growth in the future. Also with their reports for their earnings can have an impact if they continue to meet their projected goals then that is positive and if not then it could be negative. Much more factors such as the brand loyalty, productivity and revenues. 8.
Some of the auto companies included in the above analysis have a relatively high market value than their net income would indicate otherwise. Why would investors be willing to pay significant premium for stocks for these companies? Investors are willing to pay the premium for key factors. Such as they prioritize companies’ growth over their current net income, especially in high growth industries like electric vehicles. Companies leading in innovative technologies, in dominant market positions have strong brand recognition, and favorable to market for high valuations. Also, scarcity value, long-term investments, long interest rates also contribute for investors to pay premium for stocks as they anticipate future profitability and success in these companies. 5