6LLLD

Lab Overview

Scenario/Summary

Adventure Works Cycles, a fictional bicycle manufacturing and sales company, wants to be able to predict sales from new customers during their first year. Specifically, Adventure Works would like to classify new customers using the following categories.

Category	Expected Sales in First Year
D	Less than $2,000
C	$2,000–$2,999
B	$3,000–$3,999
A	$4,000 or more

Adventure Works currently collects a set of demographic data from all customers through a customer survey. You have been asked to perform data mining to determine the feasibility of classifying new customers based on their survey responses. You have been provided with two data sets extracted from the company's data warehouse.

1. A list of long-term customers with the survey responses and total first-year sales for each (OldCustomers)

2. A list of new customers to be classified with their survey responses (NewCustomers)

Deliverables

You will submit three files for this lab.

1. A NeuralTools-based Excel workbook titled Lab6_yourlastname_Data.xlsx containing the customer data classified by the neural net.

2. A NeuralTools-generated Excel workbook titled Lab6_yourlastname_Summary.xlsx containing the summary report from the NeuralTools network training.

3. A Word document titled Lab6_yourlastname_Paper.docx containing a one-page summary of your findings and recommendations for Adventure Works regarding use of NeuralTools for data mining.

When submitting the assignment, provide a comment explaining what you learned from completing this lab activity.

Category	Points	%
Step 1: Load and Prepare Data Workbook contains worksheets for the OldCustomer and NewCustomer data sets. Age column was added to both sheets with formula to calculate customer age. CustomerClassification column was added to both sheets, with formula to determine customer classification on the OldCustomers sheet only.	15	21.4%
Step 2: Define the Training/Testing and Prediction Data Sets A training and testing data set is defined based on the OldCustomer data, and a prediction data set is defined based on the NewCustomer data. Variable types are set appropriately for both data sets.	15	21.4%
Step 3: Train and the Test Neural Network Neural network is trained based on the training and testing data set, and tested using a randomly selected sample of 20% of the cases. Individual case test results are in the OldCustomers sheet, and summary results are in the Lab6_yourlastname_Summary workbook.	15	21.4%
Step 4: Predict Classifications for New Customers Predicted classifications generated by the neural network are recorded in the NewCustomers sheet.	15	21.4%
Step 5: Write Opinion Paper and Submit Paper is in APA format, free of typographical, spelling, and grammar errors, and clearly states appropriate findings and recommendations from the analysis. Findings and recommendations are relevant to the strategic objective and are supported by the dashboard.	10	14.4%
Total	70	100%

Lab Resources

Palisade NeuralTools

You must use this software from the Virtual Lab-Citrix environment. The link is accessible from the Course Resources page in the Introduction and Resources module. View the Lab Resources section. If you would like to experiment with NeuralTools and other decision-support tools on your local PC for your personal use, a free limited-time trial version or a discounted student version may be available from Palisade Corporation. A link to the company's website is on the Course Resources Page under Web Links. However, these products may not be compatible with some versions of Excel and some operating systems. Installation and use of NeuralTools or other Palisade products on your local PC is not covered in the lab instructions and is not supported by the DeVry University Help Desk.

Lab Steps

Lab Videos

Please watch the videos below for guidance on completing the lab steps.

Step 1: Load and Prepare Data

In this section, you will load the data extracted from the Adventure Works data warehouse into Neural Tools and prepare it for data mining.

a. Download the following Excel workbook containing customer data:  AWCustomerClassification.xlsx (Links to an external site.) . This file contains both the OldCustomers and NewCustomers data sets described in the scenario/summary section, each on its own worksheet.

b. Upload the data file to a folder in your virtual home drive in the Virtual Lab-Citrix environment.

c. Launch Palisade NeuralTools in the Virtual Lab-Citrix environment.

d. Open the AWCustomerCustomerClassification.xlsx file that you previously uploaded to your virtual home drive.

e. To both the OldCustomers and NewCustomers worksheets, add a column headed age with a formula that calculates the customer's age at the time of first purchase, using the DateFirstPurchase and BirthDate columns.

f. To both the OldCustomers and NewCustomers worksheets, add a column headed CustomerClassification. On the OldCustomers worksheet, use a VLOOKUP formula in this column to determine the customer's category based on the Year1Sales column. On the NewCustomers worksheet, leave the CustomerClassification column blank.

g. Save the workbook using the file name Lab6_yourlastname_Data.xlsx.

Step 2: Define the Training/Testing and Prediction Data Sets

In this section, you will define the data sets to use with your neural network for training, testing, and prediction.

a. On the OldCustomers sheet, select any cell in the customer data. Then on the NeuralTools tab, select data set manager.

b. Using the data set manager on the NeuralTools tab of the ribbon, create a new data set named training testing set from the data on OldCustomers worksheet. Apply the following variable types to the data set.

Variable	Variable Type
CustomerKey	Unused
FirstName	Unused
LastName	Unused
BirthDate	Unused
MaritalStatus	Independent Category
Gender	Independent Category
YearlyIncome	Independent Numeric
TotalChildren	Independent Numeric
NumberChildrenAtHome	Independent Numeric
Education	Independent Category
Occupation	Independent Category
HouseOwnerFlag	Independent Category
NumberCarsOwned	Independent Numeric
DateFirstPurchase	Unused
Age	Independent Numeric
CommuteDistance	Independent Category
Year1Sales	Unused
CustomerClassification	Dependent Category

c. Create a second data set named prediction set from the data on the NewCustomers worksheet. Import the variable settings from the training test set you previously created.

d. Save the workbook.

Step 3: Train and Test the Neural Network

In this section, you will train the neural network to perform customer classification and test its performance using known cases.

a. Using the train command on the NeuralTools tab of the ribbon, train the neural neural network. Select the training testing set as the data set to use for training. Check the option to automatically test on randomly selected cases, and enter 20% as the percentage of selected cases to use for testing. Uncheck the options to automatically predict missing dependent variables and to calculate variable impacts. Select PN/GRN net as the type of net.

b. When training is completed, review the summary results automatically created as a second workbook. Save the workbook containing the summary results as Lab6_yourlastname_Summary.xlsx.

c. Also, review the results for individual test cases inserted in the OldCustomers sheet in the data workbook, and save this workbook.

Step 4: Predict Classifications for New Customers

In this step, you will use the trained neural network to generate predicted classifications for new customers.

a. Using the predict command on the NeuralTools tab of the ribbon, generate predicted classifications for customers in the prediction data set. Choose the options to predict for cases with missing dependent values and to place predicted values directly in the data set.

b. Review the prediction results in the NewCustomers sheet, and save the workbook.

Step 5: Write Opinion Paper and Submit

Review your analysis performed for this lab activity, and write a one-page opinion paper summarizing the key findings and recommended actions for management. Include an evaluation of the performance of the neural network on this data mining problem; one or more recommendations as to how the performance might be improved; and one or more recommendations as to how Adventure Works could use predicted customer classifications to improve business results. Your paper should be in APA format, professionally written, and should be free of grammatical errors, typos, and misspellings. Save your paper in a Microsoft Word file named Lab6_yourlastname_Paper.docx. Submit your two Excel workbooks and the Word document containing your Opinion Paper.