BSB123 Data Analysis

BSB123 Data Analysis

Assessment 2: Research Report (30% of total grade)

Marking Criteria

Criteria Grade 7 6 5 4 <4 Weight

KS (1.1): Demonstrate and apply integrated discipline (including technical) knowledge across the broad field of business with depth in one or more core business disciplines

Application of statistical knowledge

Selects and correctly uses relevant graphs and statistical concepts throughout the report Selects mostly relevant graphs and statistical concepts, and uses them appropriately throughout the report Selects mostly relevant graphs and statistical concepts, but with occasional inappropriate use Selects only a limited range of relevant graphs and statistical concepts, with some incorrect use Unable to select and use relevant graphs and statistical concepts; Graphs / Statistical Output not included in final submission

20%

KS (1.2): Apply technical and technological skills appropriate and effective for real world business purposes and contexts

Analysis of data

Analysis methods appropriate for comprehensively and critically investigating the research question were selected; all analyses and calculations were correctly performed Appropriate analysis methods were selected comprehensively investigating most aspects of the research question ; most analyses and calculations were correctly performed Analysis methods relevant to some aspects of the research question were selected, though narrowly addressing the research question ; most analyses and calculations were correctly performed though minor errors are present Some analysis methods relevant to the research question were selected, though minimally addressing the research question ; substantive errors are evident in the analysis and/or calculations Analysis methods were not appropriately selected or were not relevant to the research question; multiple major errors present in analysis and/or calculations; Analysis methods were not included in final submission 50%

HO (2.1): Exercise independent judgment and initiative in adapting and applying knowledge and skills for effective planning, problem solving and decision making in diverse contexts

Interpretation and explanation of research findings

Results are presented clearly and interpreted correctly and comprehensively; research findings are critically discussed in depth and are coherently related to all aspects of the analysis and research problem Results are presented clearly and interpreted correctly in some detail; research findings are well discussed in detail in relation to most parts of the analysis and research problem Results are mostly presented clearly, though minor errors of interpretation are evident; research findings are well discussed in relation to some aspects of the analysis and research problem, though explanation is lacking in detail in parts Some results have been presented and interpreted correctly though substantive errors in explanation and/or interpretation are present; research findings do not sufficiently address the research question and/or analysis, and contain minimal explanation Little or no attempt to present or interpret results, or attempt contains major interpretation errors; research findings lack relevance to the analysis and/or research question and are provided with little or no explanation; Graphs / Statistical Output not included in final submission 20%

PC (3.1): Use information literacy skills, and communicate effectively and professionally in written forms and using media appropriate for diverse purposes and contexts

Written expression and integration of relevant statistical findings

Writes fluently and clearly using language, format, and structure that always adheres to the report genre; meaning is clearly articulated and effectively expressed, and relevant to task Language is generally fluent and clear; format and structure mostly appropriate for the report genre; meaning is effectively expressed and relevant to task Writes generally fluently with occasional grammatical errors; format and structure not always appropriate for report genre; meaning generally clear and relevant to task but lacks clarity in parts Meaning is apparent but not clearly and fluently expressed, with limited relevance to the task; format and structure not always appropriate for the report genre Meaning is confusing or mostly irrelevant to task with frequent grammatical errors; format/structure inappropriate for the report genre (or not meeting proper academic standard, e.g. plagiarism); Graphs / Statistical Output not included in final submission 10%

BSB123 Research Report Semester 2, 2022

Instructions

Data for this Case Study can be found in the Excel Spreadsheet BSB123 Research Report Credit Analysis.xlsx.

Please include all excel output necessary to answering the questions including data tables / graphs / data outputs / formulas.

Do not submit you excel spreadsheet.

It is highly recommended in doing your analysis that you copy data from the Original Data worksheet in the Excel file to a new worksheet when doing your analysis. In this way the original data will remain unaltered if you make any errors. You can then copy from this Original Data worksheet as you do each question so that errors are not carried across.

Include any assumptions and / or checks you need to make to ensure any analysis can be conducted.

Complete your solutions in a Word document but please save your final report as a PDF file with the title <your name>BSB123 Research Report.pdf.

The report is due 1st October (Friday of Week 12) at 11:59pm. All extensions must be applied for via the normal processes through HiQ.

Case Study Credit Card Spending and Debt

Background

Recent statistics indicate that there are over 13 million credit cards in Australia as of April 2021. Consumers need the cards for a variety of reasons and banks and other institutions are keen to provide these services because of the revenues they generate. However the Royal Commission on Banking was scathing of the practices surrounding credit cards and led ultimately to a number of changes around credit card rules particularly in terms of assessing eligibility and credit limits.

You have recently been hired as part of the data science team for a regional bank and have been tasked with investigating your credit card system. The bank has provided you with information on 246 randomly selected customers for which they have data on a range of relevant variables from the previous year.

The Royal Commission was particularly interested in the extent to which personal indebtedness had increased through credit cards and other loans, and the social and economic disruption that this caused. From the banks perspective, the ability to repay credit card debt is crucial for their own bottom line, but more importantly, they recognise the social, health and welfare issues associated with credit card abuses. As a consequence you have been asked to investigate those factors which may lead to excessive debt or inability to manage card repayments.

In the first instance you have been given information on 9 variables for each of the customers. In addition to their customer number you have been provided:

Balance the average monthly unpaid credit card balance ($)

Income the average monthly household income from all sources ($)

Spend average amount spent using the credit card per month ($)

Loans the average amount paid servicing other loans per month ($)

Card level of card issued (Blue for lowest level, Gold for mid tier and Platinum highest)

Gender gender of the card holder restricted to Male or Female

Status household income arrangements defined as couple or single income earner

Children Number of children in home

Education highest level of education of card owner (High School, Diploma, Bachelor or Postgraduate)

Your Task

Your role is to analyse the data as thoroughly as possible and provide any recommendations to the bank on issues including what factors seem to have the biggest impact on credit card debt; to what extent is overall debt an issue among your customers and are the correct type of cards being issued to customers. In considering these issues please answer the following questions:

Question One

Your first task is to consider Average Monthly Credit Card Debt to get an understanding of the problems associated with card debt. To do so:

Calculate the full range of appropriate descriptive statistics and create appropriate graphs to describe this data.

Identify the monthly unpaid debt associated with the top 10% of customers

Construct a 95% confidence interval for the average amount of unpaid credit card debt.

Using the information created in parts (i) to (iii) describe what you have learned about Average Monthly Credit Card debt in no more than 120 words.

(10 Marks)

Question Two

In addition to credit card indebtedness, the coronavirus pandemic and recent inflationary pressures have meant that overall indebtedness is becoming an issue and the bank is concerned that it may be a problem for them.

Prior to this last year, 15% of all customers had been fully paying off their credit cards each month (i.e. average credit card debt was zero(0)). The bank is concerned this has now fallen. Conduct a test to see if the proportion of people fully paying off their cards has decreased.

The bank defines a customer in Debt Stress as a household whose total monthly debt defined as average unpaid credit card debt + other monthly loan payments is more than one third of their average monthly household income. Normally we would expect that those households with a lower average monthly income would be more likely to be in stress. Conduct a test at the 1% level of significance to see if the average of the income for customers in distress is less than that of customers not in distress. (Hint you will need to define which customers are in Stress and which are not and then work on the averages of those two groups).

There is a view that Females are better at managing budgets and money than males. Conduct a test at a 5% level of significance to determine if the proportion of females under debt stress is less than that of males.

Finally, the bank is also concerned that while the overall level of indebtedness has increased along with inflation, household incomes have not been increasing to keep pace. Twelve months ago the average monthly household income was $9350. Conduct a test at the 5% level of significance to see if average monthly household income has increased.

(20 Marks)

Question Three

The bank is hoping to identify those factors which impact Credit Card non payment.

Carry out a stepwise regression analysis to determine which factors impact the overall average monthly unpaid credit card balance. In conducting the analysis, and as you build the model, make sure to explain why you are taking each step until you have identified the best model.

Once you have identified the final model please interpret all necessary statistics, and show fully in full detail all of the tests you would normally conduct as part of a regression analysis including testing any assumptions.

Forecast the average monthly debt balance for a male customer with a postgraduate degree who has a Gold Card, lives in a household with a single income of $10,000, spends on average $8000 per month on their card, and services other loans of $1500 per month and has one child.

Comment on any concerns you have about your final model and / or next steps you would take in building a better model.

(25 Marks)

Question Four

In no more than 200 words write a report in non technical language outlining what you have learned about indebtedness among customers at this bank. Highlight any issues you believe the bank should consider in particular and those that do not seem to be an issue for them.

(5 Marks)