Assignment Task
Background
Consumer debt has been a major topic of discussion in Australia over the last few years. From the Banking Royal Commission in 2017 through to the current Cost of Living crisis questions are being posed regarding issues surrounding consumer indebtedness and their ability to pay.
You have been given information on 9 variables for a series of credit card holders. 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
1. Consumers are considered to be under Debt Stress when they have to use a significant proportion of their available income to pay off existing debt. Calculate a new column (column K) called DSI for Debt Stress Index. Debt stress is measured as Average Monthly Unpaid Credit Card Balance + other monthly loan payments expressed as a proportion of Average Monthly Income
DSI = (Balance + Loans)/ Income
2. If the DSI is > 0.333 the consumer is said to be in stress. Create a new column (column L) title “In Stress” and use an IF function to identifying which customers are in stress as Yes or No.
3. Create a Pivot Table which includes all of the data including your new variables.
4. Use the Pivot Table to construct a Frequency Distribution for the variable Balance using an appropriate number of classes. Copy the Frequency Distribution to your solution document.
5. Using the Frequency Distribution in d. create an appropriate Graph and label it. Save the graph to your solution document. Note: Make sure all classes are shown, including those with no observations.
6. From the graph in part e. are their any outliers? Do you think they should be excluded from the data set?
7. In one sentence what can you say about credit card balances?
8. Use your Pivot Table to construct a table to show the relationship between “In Stress” and Card Type (Blue, platinum, gold). Copy that table to your answer document.
9. Create a graph to the relative rates of stress by credit card type. Copy the graph to your answer document.
10. In one sentence what can you say about the relationship between stress and credit card type
11. Ability to pay off debt would logically be related to income. Create a graph to show the relationship between Balance and Income. Copy it to your answer document.
12. In one sentence what can you say about the relationship between Balance and Income.
