# How do I calculate projected figures for the next year based on past performance?

I'm a SQL/C++ developer who recently has been asked to generate a report from our database to predict some future performance based on historical data; the problem is that I don't have much experience of this sort of data modelling.

I initially thought I could take an average of each month's results and use that but after reading articles on the web about statistical models, estimation, forecasting etc. I felt that might not be sufficient. Plus, most of the formulas are over my head as I've never learnt statistics.

What process would you recommend as the best way (for me) to calculate these predictions? Preferable something I can translate in to SQL or use in a spreadsheet (Excel)?

The link jthetzel provides to the document "Statistical flaws in Excel" by Hans Pottel (www.coventry.ac.uk/ec/~nhunt/pottel.pdf) no longer exists. I managed to find the document here.

• You are kind of asking, "I'm a dentist, and one of my patients has a brain tumor that I need to remove. What is the best way for me to operate?" In order to generate and interpret a predictive model, you will either need to learn some statistics yourself or consult with a statistician. There are many reasons to not use Excel (e.g. www.coventry.ac.uk/ec/~nhunt/pottel.pdf), and SQL's strength is managing data, not analyzing it. I'd recommend looking into R and the various introductions to statistics available here: cran.r-project.org/other-docs.html. Knowing C++, R shouldn't be hard. – jthetzel Nov 24 '11 at 15:57
• @jthetzel - Thanks for your comment, I realised it was a big ask to post this rather vague question and what you say is really helpful. I will take a look at R but it may be better for me to go back to the boss and tell them this is out of my knowledge area and for them to find someone else, and I'll just provide the data :) – Tony Nov 24 '11 at 16:27
• Telling the boss that this is out of your current knowledge area seems responsible. Regardless, I strongly recommend reading through those introductions. Attaining a foundation in statistics is not difficult for the curious, and you will of course be an even more valuable colleague for future projects. – jthetzel Nov 24 '11 at 16:45
• @jthetzel - I will have a read of the introductory material as I am interested in learning more, especially when I can also learn another programming language at the same time. Thanks again. – Tony Nov 24 '11 at 17:09
• Also you could tell your boss the story of the inductive turkey. – xmjx Dec 17 '11 at 20:39

The idea of predicting future performance based solely upon the past values is called univariate time series modelling (as compared to also using predictor series which is called multivariate time series modelling). Time series modelling requires formulating a customized model that may have auto-projective components (e.g., last year at this time) and/or deterministic components (e.g., level shifts / multiple time trends /seasonal dummies). In the absence of good software or a strong analytic background you may find yourself using ineffective methodology that is based upon an assumed model or a process that tries a few models and picks the "best" from a pre-defined list. Neither of these work, as the model needs to be customized to the data in order to provide reasonable results.

Rather than telling your boss of your deficiencies, provide him/her with choices to select from. Some of those choices might be 1: find a local expert in time series / predictive models to help; 2: find a software company that specializes in this area and also provides consultancy/training in these methods. In either case you will need to implement a computer-based solution and the question will be "should you make or buy?" . People who try to make without knowledge of the subject are "planning to fail."

In summary I would take a small set of your time series and engage the software companies that offer "expert systems in time series modeling" and contrast/compare their methodologies and modeling strategies. You might find that the bigger the company, the poorer the solution, as big companies provide generalized solutions but often fail to provide specialized solutions. While you may think that your problem is "everybody's problem" and generic solutions using buzz words like "data mining" should be the answer, they simply don't provide good specific solutions, especially in the area that concerns you. Learn from the experts who design/market and support "time series modelling/forecasting" and learn from them what they are doing and more importantly how they are doing it! Black-box solutions are never acceptable: demand transparency, as transparency leads to understanding which could lead you to roll-your-own.

I am commercially involved with one of those companies, so my advice may be classified as either "good advice" or "self-serving." I firmly believe the former. Select the best in terms of price/performance. Evaluate which would be cheaper, to make or to buy. Faced with a similar decision, I recently decided to buy a new 2011 Lexus as compared to building my own car which I would have named "the SHAMROCK". Guess what decision I made!

One final point, if I had a simple problem like how to calculate an ANOVA/OLS model, a statistical generalist/computer program would be able to help me. If I wanted to find out where the statistically significant change points in a time series were or how to distinguish between level shifts and true trends or how to detect where either the parameters of the model changed or where the variance of the errors changed, I would not be using generalized data mining tools but would seek qualified help.

On the one hand, jthetzel is correct.

But on the other hand, asking your question here is like going to the annual conference of neurosurgeons to say "My patient has a headache, what do I do?" Of course the answer from a bunch of neurosurgeons will be "You need a neurosurgeon!" ;-)

Modern-day SQL implementations are full suites of applications that go well beyond mere database management. So I take issue with the suggestion that analytics is not one of SQL's strengths. Microsoft SQL Server, for example, includes a full range of Analysis Services. This includes a variety of data mining solutions that can be used for predictive forecasting.

Any major enterprise SQL suite is going to have something similar.

Is "one size fits all" canned-algorithm data mining of this sort a substitute for an expert statistician who will analyze the unique situation of your business? Of course not. Not remotely.

But can it get you a first pass of some useful predictive modeling and leverage the skills you already have to accomplish a decent beginning on the task? Yes, it can.

• re the second paragraph: I think many of us who contribute regularly to this site would bristle at the suggestion we're trolling for clients or that every statistical problem requires the intervention of a statistician. Some 6,000 answered questions attest to the opposite. It is rare on this site for anyone to recommend, either in a comment or reply, that consultation with a statistician or extensive additional study are necessary. When such a recommendation appears, it should be taken seriously. – whuber Dec 17 '11 at 18:02
• That suggestion was a "winking suggestion" for a reason. By no means did I intend to imply that the site is "trolling for clients". My point was simply that it is easiest for anyone to recommend solutions that are within their normal realm of expertise. Hardcore statisticians will naturally be working with the tools of trained adepts, and thus may have little empirical experience on the basis of which to recommend the sort of solution given above by an admittedly "softcore" acolyte of the field. I see neither malice nor avarice in that...it's a natural phenomenon. :) – Jonathan Van Matre Dec 18 '11 at 19:09

You've heard now a lot about why it is not possible but on the other hand time series modelling depends a lot on how the data looks like and reacts. Probably in your case the solution may be easier - probably it isn't. What about posting a graph of the past year or so and we can tell you whether you need all the fancy stuff...

• :Seb The actual data would be more useful which would allow a comparative review of the simple but possibly wrong approach versus the complex but possibly needed model. Complex methods implemented correctly should be able to reduce/step-down to the trivial/simple method. Models should be complex enough but not too complex ! – IrishStat Dec 17 '11 at 17:16