BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mirisage
Obsidian | Level 7

Hi Collegues,

I have the attached data set.

Table 1 Column D calculates the roll rate of outstanding balance from delinquency cycle_1 to cycle_1.

Table 1 Cell c28 shows the moving sum equation I have used to forecast the value of that cell based on previous 24 months actual data.

Column N of Table 2 applies the same forecasting equation for a known data range and column P calculates how well our prediction equation does the forecasting.

Sad to say that the % absolute deviation is way too unacceptable.

Could any one help me to improve this equation to increase the forecasting precision.

Thanks

Mirisage

1 ACCEPTED SOLUTION

Accepted Solutions
udo_sas
SAS Employee

Hi Mirisage -

Without context it is very difficult to recommend any forecasting approach - nevertheless here are some thoughts about your forecasts which might be useful:

I imported your data (actual values of Cycle_2 and your forecasts) to SAS and created a forecast plot (using the last fully observed data point - e.g. Jan2012 - as out-of-sample data):

ForecastsPlot.png

I think you are facing 2 issues with your forecasts - too high and not unbiased (which means you are constantly overforecasting).

Now depending on your goal this can be a good or bad thing - like I said it is difficult to provide more advise in this kind of forum.

In fact, I also run PROC ESM on your data - and it turns out that a damp-trend ESM model does a fairly good job predicting Jan 2012, given the type of data at hand.

ForecastsPlot1.png

My suggestion would be to get your hands on decent stastistical forecasting software, which will support you figuring out a forecasting model for you.

SAS would be my obvious choice - but I'm biased.

Thanks,

Udo

View solution in original post

6 REPLIES 6
udo_sas
SAS Employee

Hello -

Which SAS procedure did you use to create your forecasts or are you looking for feedback on your MS Excel approach?

Thanks,
Udo

Mirisage
Obsidian | Level 7

Hi Udo,

Thanks.

I had 1 million records data set in CSV. With the support of the SAS discussion forum, I have done a number of data manipulations using SAS to get the aggregate dollar values to the stage that is shown in the attached Excel sheet (attahced in my original query).

The forcasting part I am going to using the MS Excel.

I am looking for feedback on my MS Excel approach. How to get forecasted figures with a narrow deviation.

Thank you

Mirisage

udo_sas
SAS Employee

Hi Mirisage -

Without context it is very difficult to recommend any forecasting approach - nevertheless here are some thoughts about your forecasts which might be useful:

I imported your data (actual values of Cycle_2 and your forecasts) to SAS and created a forecast plot (using the last fully observed data point - e.g. Jan2012 - as out-of-sample data):

ForecastsPlot.png

I think you are facing 2 issues with your forecasts - too high and not unbiased (which means you are constantly overforecasting).

Now depending on your goal this can be a good or bad thing - like I said it is difficult to provide more advise in this kind of forum.

In fact, I also run PROC ESM on your data - and it turns out that a damp-trend ESM model does a fairly good job predicting Jan 2012, given the type of data at hand.

ForecastsPlot1.png

My suggestion would be to get your hands on decent stastistical forecasting software, which will support you figuring out a forecasting model for you.

SAS would be my obvious choice - but I'm biased.

Thanks,

Udo

Mirisage
Obsidian | Level 7

Hi Udo,

Thank you very much for your conccern and help.

I am working on SAS 9.2 version in EG. Does this SAS version help me to run foreacsting plot, proc ESM etc. that you have run.

I googled using "proc ESM" as key word. As I understand we need SAS/ETS which is another SAS module that we have to buy seprately, isn't it?

Please let me know thanks.

Mirisage


udo_sas
SAS Employee

Hi Mirisage -

Yes, PROC ESM is part of SAS/ETS software. For details about this procedure please check out:

http://support.sas.com/documentation/cdl/en/etsug/63348/HTML/default/viewer.htm#esm_toc.htm

You can test if you have access to SAS/ETS already by trying to use one of the Time Series Tasks of EG:

Capture.PNG

As of today PROC ESM is not part of these EG tasks, but you can run it using SAS Code in EG (provided you have access of course).

For details about the SAS forecasting portfolio you might want to check out:

http://www.sas.com/technologies/analytics/forecasting/index.html

Hope this is useful.

Thanks,

Udo

Mirisage
Obsidian | Level 7

Hi Udo,

This is very much useful. I'll check this.

Thank you very much.

Mirisage

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 4609 views
  • 3 likes
  • 2 in conversation