ABP2 Complete - ABP 2 deals with graphs and how to interpret them. The use of excel is necessary. PDF

Preview

Summary

ABP 2 deals with graphs and how to interpret them. The use of excel is necessary....

Description

MIS 111 – F19 – Applied Business Project 2 – Financial Analysis Name:

Lopez Ortega, Yolotzi (ygl)

Project:

ABP-2 – Financial Analysis

Task 1: ETL of Transactional Data 1.1. Extract (Downloaded CSV Datafile) Date, Open, High, Low, Close, Adj Close, Volume 2018-09-01,37.000000,40.799999,34.900002,35.349998,31.031322,16290500 2018-10-01,35.650002,35.950001,31.080000,32.189999,28.329401,27625200 2018-11-01,32.259998,34.240002,31.719999,33.860001,29.799116,14588700 2018-12-01,34.320000,34.650002,28.670000,29.540001,25.997221,14650600 2019-01-01,29.160000,33.759998,29.020000,32.919998,29.058878,11803200 2019-02-01,33.000000,35.450001,32.430000,34.529999,30.480049,13881100 2019-03-01,34.820000,35.459999,32.509998,34.150002,30.144621,12845300 2019-04-01,34.299999,36.529999,33.490002,33.500000,29.570854,12205900 2019-05-01,33.410000,34.799999,27.270000,27.879999,24.673061,19997700 2019-06-01,27.920000,31.219999,27.920000,31.080000,30.906813,12522300 2019-07-01,31.250000,33.939999,28.709999,33.150002,33.061733,15810000 2019-08-01,33.130001,33.480000,30.260000,32.680000,32.592983,11902300

Task 1.1 – Werner Trading Data 09/01/2018 – 08/01/2019

MIS 111 Fall 2019

Page 1 of 10

MIS 111 – F19 – Applied Business Project 2 – Financial Analysis (Source: https://finance.yahoo.com/quote/WERN/history?period1=1535785200&period2=156464 2800&interval=1mo&filter=history&frequency=1mo ) 1.2. Data Transformation (Intraday Stock Prices and Shares Traded)

TASKS 1.2 A/B Intraday Stock Prices Date Open High Low Close 9/1/2018 37 40.8 34.9 35.35 10/1/2018 35.65 35.95 31.08 32.19 11/1/2018 32.26 34.24 31.72 33.86 12/1/2018 34.32 34.65 28.67 29.54 1/1/2019 29.16 33.76 29.02 32.92 2/1/2019 33 35.45 32.43 34.53 3/1/2019 34.82 35.46 32.51 34.15 4/1/2019 34.3 36.53 33.49 33.5 5/1/2019 33.41 34.8 27.27 27.88 6/1/2019 27.92 31.22 27.92 31.08 7/1/2019 31.25 33.94 28.71 33.15 8/1/2019 33.13 33.48 30.26 32.68 Max High Min Low Range

MIS 111 Fall 2019

37 27.92 9.08

40.8 31.22 9.58

34.9 27.27 7.63

35.35 27.88 7.47

Page 2 of 10

MIS 111 – F19 – Applied Business Project 2 – Financial Analysis Shares Traded Date 9/1/2018 10/1/2018 11/1/2018 12/1/2018 1/1/2019 2/1/2019 3/1/2019 4/1/2019 5/1/2019 6/1/2019 7/1/2019 8/1/2019

Volume 16290500 27625200 14588700 14650600 11803200 13881100 12845300 12205900 19997700 12522300 15810000 11902300

Max Min Range

27625200 11803200 15822000

Task 1.2 a/b – Werner Trading Data 09/01/2018 – 08/01/2019 (Source: https://finance.yahoo.com/quote/WERN/history?period1=1535785200&period2=156464 2800&interval=1mo&filter=history&frequency=1mo Referenced: 10/09/2019)

TASKS 1.2 C Formulas =MAX(B2:B13) =MAX(C2:C13)

=MAX(D2:D13)

=MAX(E2:E13) =MAX(F2:F13)

=MIN(B2:B13)

=MIN(D2:D13)

=MIN(E2:E13)

=MIN(F2:F13)

=(D15-D16)

=(E15-E16)

=(F15-F16)

=(B15-B16)

=MIN(C2:C13) =(C15-C16)

1.3. Data Visualizations (Intraday Stock Prices and Shares Traded)

MIS 111 Fall 2019

Page 3 of 10

MIS 111 – F19 – Applied Business Project 2 – Financial Analysis

Task 1.3 a – Werner Intraday Stock Prices 09/01/2018 – 08/01/2019 (Source: https://finance.yahoo.com/quote/WERN/history?p=WERN Referenced: 10/10/2019)

MIS 111 Fall 2019

Page 4 of 10

MIS 111 – F19 – Applied Business Project 2 – Financial Analysis

Task 1.3 b – Werner Traded Share Volume 09/01/2018 – 08/01/2019 (Source: https://finance.yahoo.com/quote/WERN/history?p=WERN Referenced: 10/10/2019)

1.4. Investment Assessment (150 words) Based on the information presented in the Number of Shares Traded chart, we can see the variance in trading volume. This indicates to investors that stock exchanges in the months do not have a specific trend (Nickolas 2019). It is evident that stock exchanges have decreased since 2018, with current monthly trading volume decreasing. These charts can help an investment analyst determine the trends stock exchanges are at in given moments. This once again helps investors know when a good time is to enter or exit a stock. Trading range and open/close visualizations can also help determine price ranges and when stocks are being gained or left. Overall, visualization can help someone determine whether a company holds a steady stock exchange. Task 1.4 – Werner Investment Assessment 09/01/2018 – 08/01/2019

MIS 111 Fall 2019

Page 5 of 10

MIS 111 – F19 – Applied Business Project 2 – Financial Analysis (Source: https://finance.yahoo.com/quote/WERN/history?p=WERN & https://www.investopedia.com/ask/answers/041015/why-trading-volume-importantinvestors.asp Referenced: 10/12/2019)

Task 2: Corporate Ownership Profile 2.1. Corporate Ownership Visualization

MIS 111 Fall 2019

Page 6 of 10

MIS 111 – F19 – Applied Business Project 2 – Financial Analysis

Class WERN USAK KNX

Insider 35.87 18.51 9.67

Insitutional Individual 69.92 78.89 90.53

5.79 2.6 0.2

Task 2.1 Shareholder Analysis (Source: https://finance.yahoo.com/quote/KNX/holders?p=KNX , https://finance.yahoo.com/quote/USAK/holders?p=USAK&.tsrc=fin-srch , https://finance.yahoo.com/quote/WERN/holders?p=WERN Referenced: 10/12/2019)

2.2. Corporate Ownership Assessment (150 words) In business, knowing where to invest is key. Individual investors often must look to Insider and Institutional investors and the patterns in which their stock holdings stand. Inside investors provide individual investors with key information because they are one of the first to know what is going on within the company. Paying attention to Individual investors will give one of the greatest insights to knowing how well the company is doing. Institutional investors are on the fence for either being helpful or not doing much for individual investors. It is such a grand number that many institutional investors may not pay enough attention to what is going on within the company. Although, they do hold power depending on the amount of variance they show in a company’s shareholding. Task 2.2 Corporate Ownership Assessment MIS 111 Fall 2019

Page 7 of 10

MIS 111 – F19 – Applied Business Project 2 – Financial Analysis (Source: https://www.investopedia.com/articles/stocks/05/042605.asp Referenced: 10/12/2019)

Task 3: Corporate Sustainability Profile 3.1. Sustainability Visualization

\ Rating Envir Social Govern

JBHT

CHRW DAL 36 39 52 42 42 63 56 68 63

Task 3.1 ESG Ratings Analysis (Source: https://finance.yahoo.com/quote/JBHT/sustainability?p=JBHT&.tsrc=fin-srch , https://finance.yahoo.com/quote/CHRW/sustainability?p=CHRW&.tsrc=fin-srch , https://finance.yahoo.com/quote/DAL/sustainability?p=DAL&.tsrc=fin-srch Referenced:

10/13/2019)

3.2. Sustainability Assessment (150 words)

MIS 111 Fall 2019

Page 8 of 10

MIS 111 – F19 – Applied Business Project 2 – Financial Analysis With current studies and issues with environmental change, sustainalytics has become a recently emerged importance for companies and their investors. The greater the company shows to care for ESG factors, leads to more support from investors. Individual investors will look at ESG ratings in order to see if their investments are going to good use. If a company is doing well but has poor ratings, investors may overlook the well because of the risk that a company with a lack of sustainability knowledge may cause. The importance of ESG ratings may grant companies greater investments due to locals seeing their commitment to a better tomorrow. A well-rounded business will have a greater turnout if their investors see them managing their resources in a positive manner. Task 3.2 Sustainability Assessment (Source: https://www.investopedia.com/investing/sustainalytics-investing-long-term/ Referenced: 10/13/2019) Task 4: Assessing your stock the Warren Buffet Way 4.1. Buffet Visualization Criterion 4d: Is the company’s return on equity above average?

MIS 111 Fall 2019

Page 9 of 10

MIS 111 – F19 – Applied Business Project 2 – Financial Analysis Fiscal Year Return on Equity Average 12/31/2018 13.3 12.4333 12/31/2017 16 12.4333 12/31/2016 8 12.4333 12/31/2015 13.2 12.4333

Task 4.1 Buffet Visualization (Source: https://finance.yahoo.com/quote/WERN/financials?p=WERN Referenced: 10/13/2019) 4.2. Buffet Analysis Task 4.2 Buffet Visualization Werner Enterprises (WERN) successfully meets the Buffet criteria for quality investment. The question was whether a company’s return on equity (ROE) has an average of 12% or over in the most recent years of the company. The visualization presents the change of ROE over time as well ass a line indicating the company’s average through those years. Currently WERN has an average of 12.43%, meaning that it is above the average that Buffet recommends when investing. Although markets can often fall and rise, seeing over time can help determine the overall gain one might earn rather than only temporary. Based on the information granted through visualization, we can determine that Werner is a company that is worth investing into based on ROE. More investigation will help but currently exceeds Buffet’s recommendation. (Source: https://gbr.pepperdine.edu/2010/08/the-buffett-approach-to-valuing-stocks/ and https://finance.yahoo.com/quote/WERN/financials?p=WERN Referenced: 10/13/2019)

MIS 111 Fall 2019

Page 10 of 10...