Pilgrim Bank Case Graded Assignment

For this assignment, you will analyze Pilgrim Bank (B): Customer Retention Case (by Frei and Campbell) and report your findings and conclusions in an executive memo from Alan Green to Ravi Raman. Save your executive memo in Word with the title "Pilgrim_YourLastName.doc" and your analysis in Excel with the title "Pilgrim_YourLastName.xls."

Please address the following in your analysis. All of your work on this section should be done in the Excel file "PilgrimCaseAssignment.xls" using the worksheets indicated. To make the data more manageable, a subset of the original random sample has been included in this Excel file for your analyses.

1. What can Green conclude about the average profitability for all customers in 1999 versus 2000? (In other words, is there a statistically significant difference in the 1999 average vs. the 2000 average?)
   1. Use the descriptive statistics tool in Excel to compute summary statistics for 1999 Annual Profit and for 2000 Annual Profit. (Place your output in the worksheet Quest1) (5 points)
   2. In Excel, use the "t-Test: Two Sample Assuming Unequal Variances" tool in Excel and a 95% level of confidence. (Place your output in the worksheet Quest1) (5 points)
2. In 2000 there was a substantial increase in the percentage of customers who use online banking (from 13.05% in 1999 to 19.61% in 2000). In the 2000 data, is there a statistically significant difference in the average profitability of online banking customers and non-online banking customers? Use the data and save your output in the worksheet "Quest 2."
   1. In Excel, use the "t-Test: Two Sample Assuming Unequal Variances" tool in Excel and a 95% level of confidence. (Place your output in the worksheet Quest2) (5 points)
3. Using a regression model and the sample data in the "Regression Data" worksheet, can Annual Profit per customer be accurately predicted using online banking usage and tenure with the bank as independent variables? Use the data in the Regression Data worksheet. 1999 Tenure data can be used as one of your independent variables.
   1. Use the "Regression" tool in Excel to derive your regression model. (Place your output in the worksheet Regression Data) (5 points)
   2. Interpret the adjusted r ² value for the estimated model. (Textbox in Regression Data worksheet) (5 points)
   3. Interpret the "Significance F" value for the estimated regression model. (Textbox in Regression Data worksheet) (5 points)
   4. Explain whether or not each of the independent variables is statistically significant to 2000 Annual Profit. (Textbox in Regression Data worksheet) (5 points)
4. According to the sample data, 17.55% of 1999 customers left the bank in 2000 which seems quite large. For this question, you need to help Alan determine if there is a statistical relationship between customer retention and the independent variables, tenure and online billpay. Preliminary analysis shows that 2.09% of customers who remained with the bank used the billpay option while 1.85% of customers who left the bank used the billpay option in 1999. This may seem like a minor difference but it can be proven statistically that this is a significant difference.
   1. The question now is, can we create a regression model to predict customer retention rates with the data we have? Use the data in the worksheet "Retention Data" for this analysis. Run a regression model in Excel with Retention as the dependent variable and 1999 BillPay and 1999 Tenure as the independent variables. (Place your output in the Regression Data worksheet.) (5 points)
   2. Comment on the usefulness of the estimated regression model for predicting customer retention in the textbox provided in the worksheet Regression Data. (5 points)

In your executive memo to Ravi Raman, include the following items.

1. Set the scene (Introduction). (10 points)
2. State the management objective for Pilgrim Bank? (5 points)
3. Now think outside the case facts. What other factors would you like to include in your analysis of profitability and customer retention? (10 points)
4. Summarize your findings and recommendations for Ravi Raman. (15 points)
5. Attach any tables and figures needed to address the questions posed at the end of the memo. Be sure to label all tables and figures and refer to each somewhere in the memo. (5 points)
6. Write in easily understood and interesting English. (10 points)
   

Note: I will deduct points for spelling and grammar errors, or hard to understand writing or spreadsheets. So label everything clearly.

This is to be an individual assignment and your work must be your own.

When you are ready to submit your assignment, go to the Assignments Area in WebCT and select Pilgrim Bank – Graded Assignment. Attach your Word and Excel files and submit your assignment.

If you want to ask questions about this assignment, use the "Introduction to Quantitative Analysis II" link in the Discussion area of WebCT.

Price: $16.22

Solution: The downloadable solution consists of 4 pages, 1265 words.
Deliverable: Word Document