INSTRUCTION Use a word (.docx or .doc) or a hand-written document to submit your
INSTRUCTION Use a word (.docx or .doc) or a hand-written document to submit your work on Canvas. You may use Excel (.xlsx) instead, but your explanation must be included in the Excel file as well. You must show your work (e.g. equations, inference, explanations) thoroughly. Your explanation does not need to be over a paragraph for each part of a question. You are encouraged to work together (with social distancing). However, you must submit your own homework. This assignment does not require Xrealstats. Thus, you can complete this assignment even if you have not installed the add-in program. Assignments QUESTION Download the Excel file "L02S_HW1.xlsx" and then perform the following activities. Part A. Using the "Scatter" tab, analyze whether or not it appears that customers with a longer association with the bank have more assets. Below are the steps for the analysis: Step 1: Aggregate checking and savings account balance for each record in the "Checking+Savings" column. Step 2: Sort the whole table by the number of months as a customer of the bank. Step 3: Using the datasets in the two columns "Months Customers" and "Checking+Savings", create a scatter plot. Replicate the two columns into the light red area. Chart title: Relationship between "Months Customer" and "Checking+Savings". Input horizontal and vertical axis titles. Step 4: From examining the data, can we argue that customers with a longer association with the bank have more assets? Explain. Part B. Using the "Pareto" Tab, conduct Pareto analysis to draw a conclusion about the combined amount of money in checking and savings accounts. Below are the steps for the Pareto analysis: Step 1: Calculate the % values for "Checking+Savings". You need to find the sum of "Checking+Savings" first. You must interpret the second % value in your words for full credit. Step 2: Sort the whole table by using "Checking+Saving" from largest to smallest. Step 3: Find the cumulative % information. You must interpret the third cumulative % value in your words for full credit. Step 4: Derive the conclusion (one sentence) of the Pareto analysis in your words. The sentence should be clear for full credit. Step 5: Create the Pareto Chart. Pareto Chart is to plot "% information" as a column chart and "cumulative % information" as a line chart in a single graph. Use "Combo Chart" feature in Excel to aggregate two chart types in a single plot. You must add the secondary plot for "% information" in your combo chart. Part C. Go to the "Histogram" tab. Using the "Data Analysis" tool in MS Excel, create a frequency distribution (table) and a histogram for the number of months as a customer of a bank (Months Customer). Use your judgement for determining the number of groups (bins) to use. You must interpret the results in your words. PreviousNext

Leave a Reply

Your email address will not be published. Required fields are marked *