M I S 1 1 1 – C o m p u t e r s a n d t h e I n t e r n e t w o r k e d S o c i e t y

Applied Business Project 2 Guidelines

Eller College of Management

This document contains instructions on completing ABP-2 for MIS 111 Version 1.0 (Initial Release): 26 September 2022

Fall 2022

On-time Deadline for Completing the Submission of ABP-2

Wednesday, October 12, 2022 by 11:59pm

The D2L submission process (i.e., the upload of your report must finish and a confirmation message displayed by D2L) must be

completed by 11:59pm local Tucson time (MST or UTC/GMT-7) by the assignment due date. This is a hard deadline and no extensions

are offered to the on-time assignment due date, so be sure to plan your work and submission attempts accordingly.

APPLIED BUSINESS PROJECT 2 GUIDELINES 2

MIS 111 – Fall 2022 Version 1.0 Page 2

Table of Contents Assignment Overview …………………………………………………………………………………………………………… 4

Introduction to Data Visualization ……………………………………………………………………………………… 4

Finding Your Required Assigned Company ………………………………………………………………………. 5

IMPORTANT NOTE on using your assigned company ……………………………………………. 5

No Cost Access to Data Sources for Analysis …………………………………………………………………….. 5

Task 1: ETL (Extract-Transform-Load) of Transactional Data …………………………………….. 6

Task 1 Self-Assessment Quality Review ……………………………………………………………………. 6

Extracting Data for Analysis from Yahoo! Finance ……………………………………………………… 7

Task 1.1: Document your downloaded data ……………………………………………………………….. 8

Task 1.1 …………………………………………………………………………………………………………………………… 8

IMPORTANT NOTE: Use of the entire Task 1 dataset ……………………………………………. 8

Task 1.2: Data Cleansing, Transformation and Analysis ……………………………………………. 8

ETL Load Process ………………………………………………………………………………………………………….. 8

ETL Transform Process ………………………………………………………………………………………………… 9

Creating a Stock Price Summary table for Task 1.2 ………………………………………………… 9

Task 1.3 Document the formulas used in the Stock Price Summary ……………………… 9

Task 1.4: Data Visualization – Stock Trading Prices ………………………………………………… 10

Calculating the Task 1.4 data range and upper and lower bounds ……………………. 10

Task 1.5: Data Visualization – Stock Trading Volume …………………………………………….. 11

Task 1.6: Data Visualization – Investment Analysis ………………………………………………… 12

Task 2: Comparative Market and Financial Strength ………………………………………………….. 12

Task 2 Self-Assessment Quality Review …………………………………………………………………. 12

Task 2.1: Tabular Data Representation ……………………………………………………………………… 13

Tasks 2.2, 2.3 and 2.4: Treemap Charts Visualizations …………………………………………… 13

Task 2.5: Value of a Treemap Chart in Investment Analysis ………………………………….. 14

Task 3: Corporate Sustainability Profile ………………………………………………………………………… 14

Task 3 Self-Assessment Quality Review …………………………………………………………………. 14

Task 3.1: Tabular Data for Sustainability Visualization ………………………………………….. 15

Task 3.2: Radar Chart Visualization for Sustainability ……………………………………………. 16

Task 3.3: Sustainability Assessment …………………………………………………………………………… 16

Task 4: Assessing your stock the Warren Buffet Way …………………………………………………. 17

Task 4 Self-Assessment Quality Review …………………………………………………………………. 17

APPLIED BUSINESS PROJECT 2 GUIDELINES 3

MIS 111 – Fall 2022 Version 1.0 Page 3

IMPORTANT NOTE: Outcome of Investment Analysis ………………………………………. 17

Task 4.1: Selection of Criteria and Visualization ……………………………………………………… 18

Task 4.2: Visualization Assessment ……………………………………………………………………………. 18

Task 4.3: Buffet Assessment Data Visualization ……………………………………………………… 18

Options Available for Task 4 Buffet Investment Analysis Criteria …………………………. 19

Task 4 Analysis Criterion 1: Is the company conservatively financed? …………… 19

Task 4 Analysis Criterion 2: Are earnings strong and show an upward trend? 19

Task 4 Analysis Criterion 3: Has the company been buying back its shares? … 19

Task 4 Analysis Criterion 4: Is the company’s return on equity above average? ……………………………………………………………………………………………………………………………………….. 19

General Applied Business Project Report Guidelines …………………………………………………… 20

Required Business Report Formatting Elements ……………………………………………………… 20

• Use the ABP-2 Report Template ……………………………………………………………………….. 20

• Include with the E-tegrity Statement ………………………………………………………………. 20

• Be submitted as a PDF-formatted document………………………………………………….. 20

Citing Sources Required for Discussion Questions, Tables and Figures ……………….. 20

Required Table and Figure Formatting Checklist …………………………………………………………. 21

Major Business Report Penalties ……………………………………………………………………………………… 22

Not using the ABP-2 report template ………………………………………………………………………….. 22

Not submitting your report in PDF file format ………………………………………………………….. 22

Not using your assigned company ……………………………………………………………………………….. 22

Not including the E-Tegrity statement ……………………………………………………………………….. 22

Submitting an ungradable document ………………………………………………………………………….. 22

On-time Deadline for Completing Submission of ABP-2 ……………………………………………… 23

Wednesday, October 12, 2022 by 11:59pm ……………………………………………………………….. 23

Subject to Change Statement …………………………………………………………………………………………….. 23

Changes and Errata List ……………………………………………………………………………………………………… 23

APPLIED BUSINESS PROJECT 2 GUIDELINES 4

MIS 111 – Fall 2022 Version 1.0 Page 4

MIS 111 Applied Business Project 2

Assignment Overview

Your task for ABP-2 is to create a structured financial analysis report for your assigned company (as shown in your D2L grade book item “ABP Ticker”) from a data visualization perspective. Your business project report will include:

• Documentation on the process you used to extract, transform and load (ETL) information from a data repository (Yahoo! Finance) on your assigned stock;

• Charts visualizing key aspects of your stock’s trading data; • A treemap chart visualizing and comparing financial metrics; • A radar chart assessing the effectiveness of corporate sustainability; and, • An analysis and visualization of your stock using one selected Buffet criteria.

Introduction to Data Visualization

Not long ago, the ability to create smart data visualizations, or dataviz, was a nice-to-have

skill. For the most part, it benefited design- and data-minded managers who made a

deliberate decision to invest in acquiring it. That has changed. Now visual communication

is a must-have skill for all managers, because more and more often, it’s the only way to

make sense of the work they do.

Data is the primary force behind this shift. Decision making increasingly relies on data,

which comes at us with such overwhelming velocity, and in such volume, that we can’t

comprehend it without some layer of abstraction, such as a visual one. A typical example:

At Boeing the managers of the Osprey program needed to improve the efficiency of the

aircraft’s takeoffs and landings. But each time the Osprey gets off the ground or touches

back down, its sensors create a terabyte of data. Ten takeoffs and landings produce as

much data as is held in the Library of Congress. Without visualization, detecting the

inefficiencies hidden in the patterns and anomalies of that data would be an impossible

slog.

But even information that is not statistical demands visual expression. Complex systems—

business process workflows, for example, or the way customers move through a store—are

hard to understand, much less fix, if you cannot first see them.

Thanks to the internet and a growing number of affordable tools, translating information

into visuals is now easy (and cheap) for everyone, regardless of data or design skills. This

is largely a positive development. One drawback, though, is that it reinforces the impulse

to “click and viz” without first thinking about your purpose and goals. Convenient is a

tempting replacement for good, but it will lead to charts that are merely adequate or, worse,

ineffective. Automatically converting spreadsheet cells into a chart only visualizes pieces

of a spreadsheet; it doesn’t capture an idea.

APPLIED BUSINESS PROJECT 2 GUIDELINES 5

MIS 111 – Fall 2022 Version 1.0 Page 5

As the presentation expert Nancy Duarte puts it, “Don’t project the idea that you’re

showing a chart. Project the idea that you’re showing a reflection of human activity, of

things people did to make a line go up and down. It’s not ‘Here are our Q3 financial results,’

it’s ‘Here’s where we missed our targets.’”

Managers who want to get better at making charts often start by learning rules. When

should I use a bar chart? How many colors are too many? Where should the key go? Do I

have to start my y-axis at zero? Visual grammar is important and useful—but knowing it

doesn’t guarantee that you’ll make good charts. To start with chart-making rules is to

forgo strategy for execution; it’s to pack for a trip without knowing where you’re going.

Your visual communication will prove far more successful if you begin by acknowledging

that it is not a lone action but, rather, several activities, each of which requires distinct

types of planning, resources, and skills.

Scott Berinato “Visualizations That Really Work” Harvard Business Review, June 2016

Finding Your Required Assigned Company

The ticker symbol and name of your assigned company can be found on D2L in your grade book in the “ABP Ticker” item.

No Cost Access to Data Sources for Analysis

All information required for this assignment is available at no cost on the standard access Yahoo! Finance website at: https://finance.yahoo.com/ You are not required to purchase Yahoo! Finance Premium for this assignment. If you encounter data that is not accessible or blocked by a paywall, please contact an instructor for assistance with accessing the required information.

IMPORTANT NOTE on using your assigned company: You must use your assigned company, that is shown in the “ABP Company Ticker” item in your MIS 111 grade book on D2L

APPLIED BUSINESS PROJECT 2 GUIDELINES 6

MIS 111 – Fall 2022 Version 1.0 Page 6

While you may use other sources to obtain information, please contact an instructor if you are unsure whether an item is the required financial measure.

Task 1: ETL (Extract-Transform-Load) of Transactional Data

Task 1 Self-Assessment Quality Review [30 points]

Point Goal 0-7 points 8-15 points 16-22 points 23-30 points

Assessment (Based on the

specific criteria

defined for each subtask)

Did not attempt or missing

content in most or all tables

and/or written assessment or

citations

Missing major content in two or

more tables and/or written assessment and

citations

Missing some content in one

table or written assessment and

citations

Fully included most or all content in

tables, written assessment and

citations

For this task, you will perform many of the standard functions required to prepare data for data analysis in a business setting. When dealing with large volumes of data it is not possible to type the data in (as you may have done for ABP-1). This task takes you through many of the steps normally followed by information systems professionals when preparing data for analysis.

For this task, you will extract (download) data from Yahoo! Finance as an Excel “CSV” data file, transform (refine and select) your data, and then load (import) the data into Excel for visualization. This Extract-Transform-Load process is commonly referred to by information systems professionals using the acronym “ETL”.

What is an Excel CSV (Comma-Separated Value) file?

A CSV file is a comma-separated values file commonly used by spreadsheet programs such as Microsoft Excel or OpenOffice Calc. It contains plain text data sets separated by commas, with each new line in the CSV file representing a new database row and each database row consisting of one or more fields separated by a comma.

The CSV data exchange format is supported by many personal, business, and scientific programs. The format is especially useful in transferring tabular data between programs and databases because of its wide support. Since CSV files are saved as plain text you can open them with a plain text editor, such as Microsoft Notepad (Windows) and Apple TextEdit (MacOS).

(From: https://fileinfo.com/extension/csv)

APPLIED BUSINESS PROJECT 2 GUIDELINES 7

MIS 111 – Fall 2022 Version 1.0 Page 7

Extracting Data for Analysis from Yahoo! Finance

For Task 1.1, you will use Yahoo! Finance to extract the daily trading data for the most recent prior 12 months (52 weeks prior) starting with the day prior to the day you are collecting your data for your analysis. For example, if you download your data from Yahoo! Finance on September 26, 2022, your date range must include all trading days from September 27, 2021 to September 26, 2022, which represents the approximately 250 trading days during most calendar years.

To download the data, you will select the “Historical Data” tab on Yahoo! Finance, and

specify a “time period” date range () and “Daily” frequency (), then click the

“Apply” button (), and finally the “Download” hyperlink () shown below the “Apply” button, as shown below:

  



When your download is ready, your file saved in your “Downloads” folder, or you may be asked to confirm saving your file, as shown below:

Once you have downloaded your “CSV” data file, locate the file on your computer and open the file using a text editor, such as Notepad (on Windows) or TextEdit (OSX).

Do NOT use Excel or double-click on the filename to open the CSV file for task 1.1 (or other spreadsheet application), as this will not display the file correctly, as shown in Figure 1.1 below. For this step, right click (or long click) and select the program you want to select “open with” and then select your text editor application.

APPLIED BUSINESS PROJECT 2 GUIDELINES 8

MIS 111 – Fall 2022 Version 1.0 Page 8

Task 1.1: Document your downloaded data

Task 1.1: Open the CSV file using a text editor (not a spreadsheet application such as Excel) and copy the metadata (field labels on the first line) and only the first 10 rows (so 11 rows total, 1 header row and 10 data rows) of your stock’s CVS-formatted text data file into your report template.

The information MUST appear EXACTLY as shown below, with the first line of metadata showing the field names of the columns (e.g., “Date, Open, High…”) and the next 10 lines being comma-separated values (CSV) for only the first 10 days of the data range you specified in Yahoo! Finance for Task 1.1. Do NOT include all (over 250) data records from the CSV file.

Figure 1.1 – Selected Data from IBM Trading Data (Source: https://finance.yahoo.com/quote/IBM/financials?p=IBM)

IMPORTANT NOTE: Use of the entire Task 1 dataset For all other Task 1 subtasks (1.2-1.5), you must import and use the entire 52-week range (one year’s worth) of data. In other words, your analysis should be based on 250+ values representing all of the trading days that occurred during the 52-week range, and not just the sample first 10 items of the CSV file shown in Task 1.1

Task 1.2: Data Cleansing, Transformation and Analysis

To begin Task 1.2, clean and transform your data by:

ETL Load Process: This is importing the data into Excel. To import the data using the “From Text” option under “Get External Data” (in the Data ribbon).

APPLIED BUSINESS PROJECT 2 GUIDELINES 9

MIS 111 – Fall 2022 Version 1.0 Page 9

ETL Transform Process: Date Cleansing and Refining removes extraneous data. In this case, we will keep the “Adjusted Close” values and delete the “Close” values to allow us to compare different stocks more accurately. Once you have the data imported into Excel, use Excel to delete the “Close” column from the data, leaving only the Adjusted Close “Adj Close” column for your analysis.

Creating a Stock Price Summary table for Task 1.2

After you have completed your data cleansing and transformation, create a summary table of the 52-week range of your trading data showing the minimum, maximum, and range (high-low) of all the data (250+ values) each of the columns in your dataset (e.g., open, high, low, adjusted close, and volume).

To calculate the low and high values, you must use Excel’s “MIN()” and “MAX()” functions and then calculate each column’s the range (the difference between high and low) using an appropriate Excel formula.

The table for Task 1.2 should follow the “Required Table and Figure Formatting Checklist” shown in the “General Applied Business Project Report Guidelines” section.

Stock Price Summary- WMT (Walmart, Inc.)

(February 17, 2019 – February 17, 2020)

52-Week Category

Daily Open

Daily High

Daily Low

Daily Adjusted Close

Daily Volume

Low 97.12 97.40 95.00 95.52 2,227,400

High 124.60 125.38 120.70 121.28 22,504,200

Range 27.48 27.98 25.70 25.76 20,276,800

Table 1.2– Stock Price Summary Data Table

(Source: https://finance.yahoo.com/quote/WMT/holders?p=WMT Referenced 2/16/2020)

Task 1.3 Document the formulas used in the Stock Price Summary

For Task 1.3, you will show the Excel formulas or functions you used to calculate the Low (Task 1.3.1), High (Task 1.3.2), and Range values (Task 1.3.3), respectively, only for the values in the “Daily Open” column shown in red below.

Task 1.3 Task 1.4 Axis Bounds

APPLIED BUSINESS PROJECT 2 GUIDELINES 10

MIS 111 – Fall 2022 Version 1.0 Page 10

Task 1.4: Data Visualization – Stock Trading Prices

As the first step in your analysis, you will visualize your stock’s trading data using a horizontal line graph to display the stock price at the market opening, high, low and market close for Task 1.4.

Figure 1.4 – Walmart Intraday Stock Prices

(Source: https://finance.yahoo.com/quote/WMT/holders?p=WMT Referenced 9/29/2019)

The figure for Task 1.4 should follow the “Required Table and Figure Formatting Checklist” shown in the “General Applied Business Project Report Guidelines” section.

For Task 1.4, you must format the bounds of the trading price chart (vertical) axis must be set to +/- 20% more than the range of the data. As calculated in Task 1.2, you will use difference between the “Low” for the Daily Low and the “High” for the Daily High’s value, as highlighted in blue in the Task 1.3 sample table.

Calculating the Task 1.4 data range and upper and lower bounds To calculate your formatting bounds, subtract the lowest minimum value from the highest maximum value. Using the example shown with Task 1.3, the “Low” for the “Daily Low” is 95.00 and the “High” for the “Daily High” your high is 125.38, the range would be 30.38 (125.38-95.00), so 20% of this value would be 6 (rounded to the closest whole value). Using the 20% range of 6, you would subtract 6 from the “Daily Low” low (95.00-6) and adding 6 to the “Daily High” high (125.38+6). This would result in the bounds for your graph of 89 to 131.38.

Note that if your stock has wide trading range, you should set the lower bounds to zero, as opposed to a negative number for the lower bound of your graph.

APPLIED BUSINESS PROJECT 2 GUIDELINES 11

MIS 111 – Fall 2022 Version 1.0 Page 11

As shown below, to format an axis with an alternate bounds (or limits for your vertical axis) after creating your line graph, click on the axis values and right-click to open “Format Axis”. In the Format Axis options window, update the “Minimum” and “Maximum” Axis Options bounds, as shown below.

Task 1.5: Data Visualization – Stock Trading Volume

For Task 1.5, you will plot the Daily Volume of traded shares for the range of trading day as a vertical bar graph to display. You are not required to scale the vertical range (bounds) axis of this visualization.

Figure 1.5 – Walmart Traded Share Volume

(Source: https://finance.yahoo.com/quote/WMT/holders?p=WMT Referenced 2/16/2020)

The figure for Task 1.5 should follow the “Required Table and Figure Formatting Checklist” shown in the “General Applied Business Project Report Guidelines” section. </