Do you have Analytic solver or R in exel?

BUA 6315: Business Analytics for Decision Making

1

Overview:

Module 4 Assignment Handout:

Data Mining: Similarity Measures, Performance Evaluation, and PCA

In this assignment, first you will calculate Euclidean and Manhattan distance for an online retailer. Next, you will evaluate the performance of a prediction model that classifies gamers who are likely to make in-app purchases by constructing a confusion matrix.

Prompt:

For this assignment, you will analyze the three case studies below and address the questions associated with each:

Case 1: For this case, first download the data: Online_Retailers (available in blackboard).

Next review the following case study:

An online retailer collects data on its annual revenues (Revenue in $), the number of products available on its website (SKUs), and the number of visits to the website per day (Visits).

Then complete the actions below and record your answers in a Microsoft Word document.

Note: For step-by-step instructions on how to calculate Euclidean, Manhattan distances, and min-max normalization, refer to the following videos from this module’s lesson:

● Similarity Measures for Numerical Variables - Part 1 (10:07) ● Similarity Measures for Numerical Variables - Part 2 (6:36)

1. Without transforming the values, compute the Euclidean distance for all pairwise observations of websites 001, 003, and 005 based on annual revenues, SKUs, and visits per day.

2. Compute the min-max normalized values for annual revenues, SKUs, and visits per day, and then

compute Euclidean distance for all pairwise observations of websites 001, 003, and 005. Discuss the difference in question 1 and 2. Make sure to exclude the website IDs from your calculations.

3. Using the min-max normalization values, compute the Manhattan distance for all pairwise

observations of websites 001, 003, and 005. Discuss the difference between the Euclidean and Manhattan distance. Make sure to exclude the website IDs from your calculations.

Case 2:

For this case, first download the data: Gamers (available in blackboard).

Next, review the following case study:

Monstermash, an online game app development company, has built a predictive model to identify gamers who are likely to make in-app purchases. The model classifies gamers who are likely to make in-app purchases in class 1. Applying the model on the validation

BUA 6315: Business Analytics for Decision Making

2

dataset generated a table that lists the actual class and Class 1 probability of the gamers in the validation set.

Then complete the actions below and record your answers in a Microsoft Word document.

Note: For step-by-step instructions on how to construct a confusion matrix, and create a cumulative lift chart, decile-wise lift chart, and ROC curve, refer to the following videos from this module’s lesson:

● Performance Evaluation for Classification Models

● Using Excel to Obtain Confusion Matrix and Performance Measures ● Performance Charts for Classification - Introduction ● Performance Charts for Classification with Excel ● Performance Evaluation for Prediction with Excel

1. Specify the predicted class membership for the validation dataset using the cut off value of 0.5. Create a table that illustrates a confusion matrix based on classification results from this cutoff value. Be sure to insert your 2x2 matrix table within your Microsoft Word document.

2. Compute the misclassification rate, accuracy rate, sensitivity, precision, and specificity of the classification model from the cutoff value specified in Question 1.

3. (Extra Credit – 10 points) Create the cumulative lift chart, and ROC curve for the classification model. Be sure to insert these two graphs within your Microsoft Word document.

Case 3: For this case, first download the data: Stocks (available in blackboard).

Next, review the following case study:

Investors usually consider a variety of information to make investment decisions. The accompanying table displays a sample of large publicly traded corporations and their financial information. Relevant information includes stock price (Price), dividend as a percentage of share price (PE), earnings per share (EPS), book value, lowest and highest share prices within the past 52 weeks (52 wk low and 52 wk high), market value of the company’s shares (Market cap), earnings before interest, taxes, depreciation, and amortization (EBITDA in $billions).

Then complete the actions below and record your answers in a Microsoft Word document.

Note: For step-by-step instructions on how to conduct principal component analysis, refer to the following videos from this module’s lesson:

● Principal Component Analysis - Introduction (6:35) ● Principal Component Analysis with Analytic Solver (6:00)

1. Conduct principal component analysis on all variables except the Name variable. Should you standardize the data? Explain.

2. What percentage of total variability is accounted for by the first principal component? How many

principal components must be retained in order to account for at least 80% of the total variance in

BUA 6315: Business Analytics for Decision Making

3

the data?

3. Which original variables are given the highest weight to compute the first principal component? Which original variable is given the highest weight to compute the second principal component?

4. What is the principal component 1 score for the first record (3M)?

Submission Guidelines: Your completed assignment must be submitted as a Microsoft Word document, 1-2 pages in length, double spacing, 12-point Times New Roman font, and 1-inch margins. The submission must be accompanied by a Microsoft Excel spreadsheet showing your work. Only the Word document will be assessed for grading purposes, however the spreadsheet is required and must be submitted to show your work. Relevant graphs and/or tables of the data should be inserted within the Word document.

Note About Grading: This assignment will be assessed based on the accuracy of your responses to each question in the worksheet.