Predict Value in Third Column Based on Data in First Two Columns

[barbaragitt]

Hello everyone,

I need to estimate or predict the number of scheduled appointments based on outpatient visits and unique patients. I have some historical data and have used trend analysis to estimate the number of future outpatient visits and unique patients. Based on that estimate, is there a way to predict the scheduled appointments?

I have attached the worksheet I'm using.

Thanks!

[rylo]

Hi

Your source data only has 4 regions, yet your forecast has 23 regions. Where do the extra regions come from? Also, the forecast for FY13/Q3/Region 1 seems to be a little "optimistic" given the region 1 history. The last 5 results have been < 7000, yet you have a forecast for Q1 of 30,525. Is there a disconnect somewhere?

rylo

[barbaragitt]

The first four regions were part of a pilot program, so data are known for them. Outpatient visits were low in the historical data due to the pilot being run in only a portion of the region. I am trying to use the known data from the pilot to estimate for the rest of the regions. Hope that makes sense!

[rylo]

Hi

I'd rearrange things so that you have all the data for the one region together, then do your forecast on that data. Then I'd put in the assumption that increases it from the pilot to the full range (it would be an assumption, so it should be identified, documented and agreed, and easily modifiable. Then I'd turn the appointment and clinical volumes into percentages, and forecast those. Again, if you want to modify them, separately identify your assumption, and move on. This should give you some basis for a forecast for each region separately with all parts open to evaluation and discussion.

Only when you have all your individual region forecasts completed would I try to bring them into the grouped Quarterly forecast.

rylo

[barbaragitt]

Thank you, I will give it a try!