Megacorp Acquisition Analysis using Visual Analytics

Problem Set

Purpose

This problem set gives students the opportunity to practice steps in the analytical lifecycle to help determine if a company should be acquired. Students will practice theses skills using SAS Visual Analytics.

SAS Software

This problem set uses	SAS Visual Analytics in SAS Viya for Learners 3.5
Complete E-Learning course – Prerequisites.

.

Industry Alignment

This activity aligns with the retail industry.

SAS Course Alignment

This problem set compliments SAS Visual Analytics 1 for SAS Viya: Basics and can be used to practice skills learned in the course.

Table of Contents

Megacorp 1

Purpose 1

SAS Software 1

Industry Alignment 1

SAS Course Alignment 1

Activity Notes and Requirements 3

Learning Objectives 3

Estimated Completion Time 3

Experience Level 3

Prerequisite Knowledge 3

Software 3

Content Knowledge 3

Additional Notes 3

Required Setup 4

Data Source 4

Introduction 4

Description of Variables 4

Megacorp Toy Factory 6

Part 1: Investigate 6

Part 2: Prepare 6

Part 3: Explore 7

Part 4: Report 7

Appendix 10

Appendix A: Access Software 10

Appendix B: Recommended Learning 10

Activity Notes and Requirements

Learning Objectives

Students will practice essential SAS Visual Analytics skills, including:

· Exploring a new data set

· Creating new variables

· Modifying data to meet specifications for analysis

· Creating visualizations within interactive reports

· Building and enhancing visual reports

Interpreting visualizations to make business decisions Estimated Completion Time

This problem set should take students approximately 2 hours to complete. Estimation times will vary based upon student level.

Experience Level

This problem set is targeted for beginner-intermediate users of SAS Visual Analytics.

Prerequisite Knowledge

Software

Students should be comfortable with the basics of visualization and dashboarding in SAS Visual Analytics, utilizing SAS Viya for Learners.

Content Knowledge

Students should have completed SAS Visual Analytics 1 for SAS Viya: Basics before completing the assignment. Additional Notes

(OPTIONAL) Two escape rooms have been developed using this problem set for an engaging, gamification experience. Faculty can utilize either of the escape rooms below:

· Option 1: https://view.genial.ly/628e533fe8f77500117a3a84/interactive – content simple – corporate – escape – room

· Option 2: https://view.genial.ly/628e51f7dada9e0018a6f008/interactive – content factory – escape – game

(NOT REQUIRED) Faculty can ask students to provide evidence about the sequence of steps followed during the development of the escape room through a variety of tools methods.

Examples of free tools are:

· Padlet : Faculty decide the dashboard format. It can be a timeline per team or just a board to share evidence.

3

· BookCreator : Faculty can create their own library where students create their own book with the evidence of their data. This is also a great resource to have students be creative and tell as story around the evidence with the data

Required Setup

Access SAS Viya for Learners 3.5. For more info, please see Appendix A: Access Software

Data Source

Introduction

MEGACORP2020 has daily data for each item produced by a unit between 05JAN2008 and04JAN2020. If a unit did not produce a product on a day, one row is written to the table with a non-active unit status (closed, failure, upgrade, or upkeep). Because no products were produced on these units, product ID values are missing. Data quality stewards have already cleaned and grouped the data, created several new columns, and modified some data properties. You still need to ensure that the data used in SAS Visual Analytics is ready for analysis and reporting. The table contains information about the manufacturing facilities (location, age), production units (status, age, capacity, yield, reliability), and products (brand, line, description, ID). In addition, the table also has information about expenses, revenues, and profits.

Description of Variables

The MEGACORP2020 table has 32 columns and 2,229,087 rows. The variables used for this exercise are:

Variable	Description
City Latitude	Latitude for each manufacturing city in the US
City Longitude	Longitude for each manufacturing city in the US
Date	Manufacturing date (between 05JAN2008 and 04JAN2020)
Date by Month	Manufacturing month (between JAN2008 and JAN2020)
Date by Year	Manufacturing year (between 2008 and 2020)
Day of Week	Weekday identifier: 1=Sunday, 2=Monday, …,7=Saturday
Expenses	Total expenses
Facility	Unique identifier for each facility
Facility Age	Age of facility (in years)
Facility City	City in the US where manufacturing facility is located
Facility Region	Region in the US where manufacturing facility is located
Facility State	State in the US where manufacturing facility is located
Product	Type of product within each product line
Product Brand	Brand of the product (Toy or Novelty)
Product Description	Description of each product
Product ID	Unique numeric identifier for each product
Product Line	Product line within each product brand (Toy has Action Figure, Game, and Stuffed Animal; Novelty has Promotional)
Profit	Total profit (Revenue – Expenses)
Region Latitude	Latitude for each manufacturing region in the US
Region Longitude	Longitude for each manufacturing region in the US
Revenue	Total revenue
State Latitude	Latitude for each manufacturing state in the US
State Longitude	Longitude for each manufacturing state in the US
Unit	Unique identifier for each production unit
Unit Age	Age of unit (in years)
Unit Capacity	Maximum number of products that can be produced on a specific unit
Unit Downtime	Indicator for the unit being non-operational: 1 (true) or 0 (false)
Unit Reliability	Percentage of time a unit is operational
Unit Status	Operational status for a unit (active, closed, failure, upgrade, or upkeep)
Unit Yield (actual)	Actual number of products produced on a specific unit
Unit Yield (rate)	Percentage of actual production divided by targeted production
Unit Yield (target)	Targeted number of products produced on a specific unit

Megacorp Toy Factory

The executive board at Megacorp has decided to sell the private toy company. A competing toy company SASBro is considering purchasing Megacorp to expand their presence in the market. The executives at SASBro have enabled a team to assess and determine if they should acquire Megacorp. Part of the acquisition requires a review of the company’s production, profit, and distribution. You have been hired on as a consultant to help the team develop an interactive report to review each of these metrics. Part 1: Investigate – progress learning

First, access the data and investigate the variables and their attributes. The goal is to see what data is available and if any data cleaning or preparation is needed.

1. Open SAS Visual Analytics .

2. Navigate to the MEGACORP2020 data. Explore the following:

a) How many rows and columns are in the MEGACORP2020 table?

b) What is the aggregation for Unit Reliability and Unit Yield (rate)? Are these aggregations appropriate?

c) How many date data items are in the table?

d) How many unique values does Product Line have?

Part 2: Prepare – progress learning

Now that you are familiar with the data and necessary changes, clean the dataset within your report.

1. Change Product ID and Day of Week to category – how many unique products does

Megacorp have?

2. Rename Unit Reliability to Unit Reliability (avg) and change the aggregation to Average.

3. Convert to geography data items:

a) Use Region Latitude and Region Longitude to change Facility Region to a geography data item.

b) Use State Latitude and State Longitude to change Facility State to a geography data item.

c) Use City Latitude and City Longitude to change Facility City to a geography data item.

d) Hide all the Latitude and Longitude data items since their information is encompassed in new data items.

4. Create the Product Hierarchy data item containing Product Brand, Product Line, and Product, respectively.

After completing the above, create a page with the following:

· Automatic chart using Unit Reliability (avg)

· Automatic chart using Facility City

· Automatic chart using Product Hierarchy

Part 3: Explore – progress learning

Our dataset looks how we need it to as we begin our analysis! Throughout this step, you will use SAS Visual Analytics to explore the MEGACORP2020 table to uncover trends and relevant patterns that can be hidden in massive amounts of data.

1. Create a table to answer the following:

a) What are the regions in Megacorp?

b) Which region covers the fewest number of states?

c) Which state or states have only one facility? (Hint: Use the Facility data item)

2. Examine the relationship between Facility Age, Unit Age, Unit Capacity, Unit Downtime, and Unit Yield (actual).

a) What is the degree of correlation between Unit Capacity and Facility Age?

b) What is the degree of correlation between Unit Yield (actual) and Unit Downtime?

c) Of all the correlations, which is the weakest?

3. Examine profits by Product Brand and Product Line.

a) What is the profit for the Toy brand for all years?

b) What is the profit for the Toy brand for 2015 through 2019?

c) Which product lines are experiencing losses?

Part 4: Report – only submission

1. As you create your final report, remember to create organized pages with specific information on each page. Titles and additional text about the page contents and controls available will help your report’s navigability. Follow these requirements when building:

i. For all pages in the report, a viewer should be able to choose a range of years displayed.

ii. Leadership wants to see both the total profit value, as well as the profit by year.

iii. Include a geographical representation of the regions, states, and cities using drilldown functionality that shows the profits and other details for each level.

iv. They want an analysis of profits by products produced, showing the analysis for Product Brand and Product Line.

v. The directors occasionally want to see details about a specific location, a specific product, or both. The answers to these questions should be available at any level

Megacorp Acquisition Analysis using Visual Analytics

Problem Set

3

3

3

for locations or products. Typical information requested includes profits, capacity, targets, and actual quantities produced. Create a hidden page that provides this content and can be viewed only upon request. Be sure to link relevant charts and graphs to this page.

2. Report Format, Analysis, and Content: The report covers years 2008 to 2020 and must have fno more than five (5) tabs with the following:

i. Overview Tab

· Display Total Profit

· Display Profit by year

· Controls: able to use a slider to select range of year

· Answers: which years are most profitable and least profitable?

ii. Profit by Location Tab

· Create Hierarchy by region/state/city

· Controls: able to use a slider to select range of years

· Drill down by hierarchy

· Answers: most profitable region/state/city or least profitable region/state/city?

iii. Profit by Product Brand (Novelty and Toy) Tab

· Controls: able to use a slider to select timeframe

· Double click graph for details (filter by year) – may be a separate tab

· Display Facility Region, Facility State

· Display Products (Novelty and Toy)

· Profits, unit capacity, unit yield (target), unit yield (actual) • Answers: which products are most profitable and least profitable?

iv. Correlation Analysis

· Facility Age

· Unit age

· Unit Capacity

· Unit down time

· Unit Yield (actual)

· Answers: which relationship has the strongest and weakest correlation

3. Recommendation

i. Based on your analysis using the information above make a recommendation to the Executive Board

ii. The Title Page of the interactive report must have your name, course number, and date. The Title Page and Recommendation are not counted towards the Tab limit.

iii. The submission must be the following in two files:

An interactive rep ort ( narrative text and graphs) in PDF

· format ; and

· An exported shareable file of your report (in txt file formate). Instructions on how to create a shareable report file is here – https://

communities.sas.com/t5/SAS <a rel='nofollow' target='_blank' href='https://urldefense.proofpoint.com/v2/url?u=https-3A__communities.sas.com_t5_SAS-2DCommunities-2DLibrary_Sharing-2DReports-2Din-2DSAS-2DViya-2Dfor-2DLearners_ta-2Dp_892377&d=DwMFAg&c=0CCt47_3RbNABITTvFzZbA&r=hO8xTUXhdgTaxXa3PZw