Question: I am trying to forecast the Debt Amount by Customer Segment by Debt Bucket for the next 6 months, also taking into consideration Billed Amounts.
Sample monthly csv dataset here, here are the field definitions:
end of month time stamp
9 customer segments, e.g. residential active, business active, etc.
numerical value of amount billed for whole customer segment in that month
9 debt buckets, e.g. 0-30, 30-60, etc. We use this to capture the customers debt age.
our dependent numerical variable that we are trying to forecast for next 6 months
(for confidentiality reasons I have heavily modified the dollar amounts)
At the moment, I am trying Regression with ARIMA errors in R, where I have tried to regress Debt Amount against Segment, Debt Bucket and Billed amount.
Some issues I have:
- How do I handle explanatory variables (e.g. Debt Bucket)? I have tried to add them through ARIMA (xreg) parameter, but no success - maybe I'm not adding them properly.
- If you look at the data, for the same date we have a separate observation for each Segment and Bucket combination. Do I need to do any transformation, or can R handle this automatically when applying Regression with ARIMA errors.
- Instead of just one model, I did think of having 9 separate ones for each Segment. I don't want to break down the model further as we need to consider that the buckets are dependent, i.e. having Debt in the 30-60 day bucket in month m is predictive of debt in the 61-90 day bucket in month m+1.
These are my thoughts at the moment, would be great to hear some answers/comments on my questions or even give me a different perspective/methodology.