Excel Analytics Case Study - Need urgently in 6 hours
Budget: $10 – $20 AUD
There is a sample finance data. The dataset contains all the loan contracts that they settled from 2017 to 2020. Using this sample, the business wants to understand:
- How many accounts have gone to loss?
- How long does it typically take for an account to go loss?
- What meaningful ways are there to segment the data?
- What is the worst performing segment?
- Has COVID had an impact on the losses? If so, what impact has it had?
a) Summarise and report on your findings for the 5 questions above
b) Was there anything unexpected or a cause for concern? If so, what did you do about it?
c) Based on the above analysis, can you come up with a mathematical / statistical relationship so that the business can better estimate how many losses to expect in the near future?
d) Sales projections for the next 12 months are shown below. The time series has been indexed to show the relative growth compared to the month of Nov'21.
- How many accounts have gone to loss?
- How long does it typically take for an account to go loss?
- What meaningful ways are there to segment the data?
- What is the worst performing segment?
- Has COVID had an impact on the losses? If so, what impact has it had?
a) Summarise and report on your findings for the 5 questions above
b) Was there anything unexpected or a cause for concern? If so, what did you do about it?
c) Based on the above analysis, can you come up with a mathematical / statistical relationship so that the business can better estimate how many losses to expect in the near future?
d) Sales projections for the next 12 months are shown below. The time series has been indexed to show the relative growth compared to the month of Nov'21.