Know-How Exchange : Research/Metrics

Thanks everyone for your early responses – they are greatly appreciated.

Declaration of interest
I am commercially involved with the sales forecasting software SymVolli www.symvolli.com which I believe to be a solution to many sales forecasting problems at about one hundredth the price of using SAP or Oracle, which are both fine packages in their own right. See www.oracle.com and www.sap.com for further information.

Basically the sales forecasting method most appropriate to the vast majority of companies is to use an intelligent way of adding together the individual sales forecasts of everyone, everyone in a company who has ownership or visibility of a possible sale.

It has to be done to the rules agreed by management as to what constitutes a potential sale which has always been the case. Are we in the right price range? Is he talking to a decision maker? Is there a budget, Does it solve his problem? And so on.

Then add some clever bits to see whether, over the duration of the customer’s buying cycle (Forget sales cycles these days!) that the forecast remains credible and real. Then add some way of removing figments of imagination and outright lies and you’ve got it.

In addition, if done correctly it can save hundreds of hours of time wasted in drawing up sales reports and concocting forecasts on a spreadsheet.

In my next post I will cover the points raised and the definitions requested – I just wanted to ensure everyone that I wasn’t doing some form of “Below the Line” or “Subliminal” sales job on the KHE.

Steve Alker
Xspirt

.

Wow - this is Like Christmas come early so I'll answer individual postings tomorrow - it's late over here.

From most of the postings it looks as though we need some definitions and a bit of a tutorial first! Here goes

Iterative processes are very common and they are used when the results of one week (or period) of forecasting or prediction determines the figure, variables and constraints you will start off with the for the next weeks forecast. One week forecasts are of limited use (!) so the tendency is to plug next weeks answer into the week after that’s algorithm and repeat 52 times to get a view for the year might look like, which as real data come in (Booked sales versus forecast) can only get more accurate, right?

Unfortunately iterative processes also involve feedback. Clearly in week two, unless you have run a new advertising campaign the sales team will not have as many leads to book appointments from. And they will hopefully be carrying out follow up calls which usually take longer and therefore impact on their call rate. Then they won’t be able to prospect adequately whilst out and about and that will affect their self developed business in a couple of week’s time. All of thee factors multiply themselves together in some way defined by the actual process and exist in a feedback loop – next weeks leads are less that last weeks leads because the feedback loop from this week to next week is negative. It’s the same thing as negative feedback in electronics or process control.

Unfortunately because you’ve also got variables and constraints changing by sort of multiplying themselves together you don’t have a linear variable in the feedback loop (Say the sales figure) but one which is to the power of 2 or 3 or 2.467 or something.

That combination of factors means that even the simplest models can behave in different ways. They can settle down to an answer which is what you are looking for or settle down to an answer which wrongly shows you’ve gone out of business. Or they can oscillate.

This was discovered in a Harvard Business School course in the 1950’s. Students had to run a brewery, a distribution centre and a pub group in three different rooms. They were only allowed to communicate with certain commercial constraints, written on slips of paper which mimicked the real world, and acknowledgements which mimicked the real world by manually passing slips of paper from room to room by hand and in both directions.

Without exception this simple system would go out of control with the brewery being closed through lack of orders or not being able to make enough beer to meet demand, the distribution centre was either out of stock or storing it in the rain in the yard and the pubs either had too much beer to sell or they ran out. What’s more it wasn’t predictable even a month in advance. Next weeks mess was just about visible, but 4 weeks down the line, anything could happen.

The rules were simple, they never changed, the orders were simple and the feedback was by hand and prompt. The problem was that this is negative feedback (Don’t deliver next week our cellar is full) on a non linear variable (sales multiplied by itself between squared and cubed) - the brewers could only make what their raw materials would allow them based on their own forecasts. Distribution could only order from the brewers what the publicans said they would want next week and the pubs themselves had to order based on the fact that one week they were having to give the stuff away whilst next week they were loosing customers through running out of beer.

That’s non linear negative feedback and it is a situation where the maths of Chaos theory can come into play, rather like in weather forecasting.

The equations for prediction may be simple. The feedback may be well defined but unless you know your starting conditions to an infinite accuracy, small differences in the numbers you use in the start conditions quickly cause two forecasts to both diverge and look like random noise. They are not random, they are utterly deterministically predictable but our failure to know the absolute value of what numbers we should start from make predictions 20 weeks down the line impossible.

Let’s say that the first figure team A used was to order 1,000,000.0 gallons of beer from the brewery whilst team B tried to be more accurate and ordered 1,000,000.1 gallons of beer. Not much of a difference is there? If the system was linear the forecasts would diverge over 20 weeks by whatever the rate of consumption gradient is (Say 10 for round figures) on a straight line graph by 1 gallon. That is a 0.00001% error. Excellent

If the relationships were to a square power (non linear) through interdependence, then the difference would be more pronounced. It would be about 10,000 gallons on our basic model based on something to the power 20. Still that’s only a 1 % error. So not bad

If the relationships were to be non linear and involve a negative feedback loop we are lost because we enter the maths of chaos, our 0.1 gallon starting difference would give you two forecasts. Initially they would track each other quite closely, next they might oscillate like the Harvard model but then things go haywire.

The oscillations start to get out of phase with each other and the frequencies of oscillations change from the team which started with an initial ordered 1,000,000.0 gallons and the team who ordered 1,000,000.1 gallons. The forecasts diverge both in value and periodicity over your 20 weeks so a tiny error of .1 gallon in a million gallons could produce one forecast which says they need 2,000,000 gallons whilst the other one says they’ve got 3,000,000 gallons too much already and will have for weeks!

It looks random on a graph. It is not it is chaotic. It looks like noise. It is not, start with the same figures and you will always get the same chaotic graph and figures to go with it – just don’t thy this on two different computers at the same time or you will discover the significance of the rounding up errors of a Toshiba over those of an Dell when you come to compare the two graphs – even those tiny rounding errors will give you two different sets of graphs after twenty weeks.

If anyone wants some simple examples of this which you can run on a spreadsheet or in Visual Basic, please email me from my profile. The population model of a fruit-fly is a two term iterative process with nothing more scary that a square and a constant, yet it is chaotic. It closely resembles forecasting models used in several situations you find in FMCG where the ERP guys get too big for their boots. Chaos from Excel – surely not? Well not only can I show you it happening, you can play around with the figures and see how the graphs change shape and how they diverge from each other depending on what you do to the starting conditions.

Best wishes and here endeth the lesson

Steve Alker

Michael (mgoodman)

I was hoping that you would chip in with your experience of high volume consumer goods. Sum total forecasting of all sales staff involved in a in a sale in such a company is still appropriate but you need the measures of the metrics of seasonality, advertising spend, promotion response and price elasticity to overlay the sales staffs predictions or you will get silly figures based on enthusiasm.

But have you seen what “Big ERP” has been trying to foist on the high volume industry sector. Yep its programmes which feed next weeks forecast into the following week so that they can look down the lens of their ERP system-telescope to see what they might be doing in 6 months time. If only someone had told them that the telescope was looking into Alice’s looking glass!

Strangely the retailers seem to be relatively immune to this nonsense – probably because they have used JIT to reduce the stock room to the size of a cupboard, used the maths of logistics big-time and through their loyalty cards at POS (All praise to Dunn Humby who invented the idea) know who is buying what, when, where and at what price and profit almost immediately.

A sensible dose of linear programming and operational research tells them in real time what to buy on a very short forecasting cycle. On the longer term basis they can use this ongoing provision of information to combine it with the metrics of seasonality, advertising spend, promotion response and price elasticity but surprisingly, the greatest benefit they get from the longer term forecasts is to the supplier / retailer relationship along with the bullshit they release to the Stock Market!

The manufacturers and suppliers get their sales forecasts from the big retailers, not from their sales staff who largely promote in store, do the internal marketing effort and are the face of the supplier at management and checkout level. I’m not sure because it is not one of my speciality areas but I don’t think that food manufacturers send a salesman to see a store manager in order to end up asking, “So Mr Green we have agreed that our beans are the best and that our new promotional campaign is having an positive impact so how many million extra cans do you want next moth and over the next 6 months?” Followed by the “Closer’s” silence!

Please feel free to disabuse me if I am wrong.

If you are a smaller supplier of volume goods with many customers (Say shopkeepers) that approach is prohibitively expenses and trying to get weekly feedback from 1000 corner shops is a trial but not impossible. They really do need your help! I’ve explained above why iterative foresting goes haywire and as SAP on demand gets rolled out to smaller companies I only hope that SAP’s VAR sales staff understand the meaning of the words “non-linear” and “feedback” and “chaotic” and "iteration". They would be better off consulting the entrails of a chicken to gauge demand than to go down that road.

Again thanks for your comment and if these explanations and comments clarify anything (rather than confuse or drive mad) please do come back – you are probably one of our most experienced people in FMCG and Retail.

Best long winded wishes

Steve Alker
Xspirt

Sorry but I must let mgoodman jump the queue

Ahh! Michael, music to my ears – Involve people and don’t expect the answers to be right just because they come out of a computer. Get people to give their informed opinions about the forecast. Lets them comment on the feasibility of any intermediate result and don’t, don’t use whatever technique you want to forecast 52 weeks in advance and keep it there. That’s a target.

Did you know that any individual quote where the buying cycle (Kind of like the sales cycle but viewed from the customer’s perspective) is around 3 months, a quote in a forecast really only has 3 characteristics?

There is what it is for (Handy for buying, manufacturing and cash flow). There is the likelihood of actually getting the order based on the company’s own criteria expressed as a percentage (Vital to every function in an enterprise). Then there’s the date on which the “Closed” prospect has stated that he wants either delivery or will place the order. (Vital for all departments and your bonus!)

That’s it, but the funny thing is that in the real world (George hates me saying real people, real world, but what the hell, you are fellow marketers not customers. And he’s a sales genius, a CEO and an originator of some great software in SymVolli, not a marketer!!) Yes, in the real world a forecast cannot stay the same over the buying cycle without a few things having to change. Unless of course the sales person responsible is not doing a proper job and taking things for granted. So if the sale started out at £10,000, 60% chance of closing and 8th of July when the order will be placed, in the next month’s review of forecast sales, it can’t remain the same.

If the buyer has started his internal processes then you will be either more likely of less likely to win the sale– after all, he is unlikely just to wait until the 6th Of July and send you an order and a cheque. Buyers and their companies have requirements to be met.

Likewise, the value of the sale rarely remains static. A good buyer will try to secure a better price before he or she signs an order. Requirements change. And the due date – the 6th July was the feeling at the time – maybe they have realised that August would be a better date as the plant is down or maybe they will realise that the 20th og June is better because that’s when some other related factor kicks in.

A real forecast cannot be frozen throught its life, it must change and only by doing their job with respect to communicating with the client, will the sales team know what is going on. You don’t need to piss the prospect off either. A mutually agreed progress review phone call every couple of weeks or every month will give you all the info he needs to establish if the forecast is improving in value to the company or for some reason slipping. If it is slipping then he can try to do something about it with incentives or features or an upgrade or a service contract or whatever.

That’s it Michael; an iterative process or even a linear projection does not tell you that the buyers keep changing their minds and poor salesmen or saleswomen who do not have the skills or techniques needed to stay in contact don’t know what is going on so they cant change the figures without making things up. That’s why we, Oracle and SAP have built in warning flags to spot frozen quotes in a forecast or an individual forecast which has ossified! And it show up deceptions and offers managers the opportunity to ask a few questions and to manage rather than filling in meaningless reports!

Steve Alker
Xspirt

Jay, thanks for your comments. “The Quants” sold well and explained some aspects of the disaster very well, but unfortunately like Stephen Hawkins’s “A brief history of time” was read and understood by about 5% of those who bought it. Looks good on the bookshelf though.

In our village we have a derivatives designer. He has a PhD in Maths and he is absolutely brilliant – plays the cello, talks about anything, livens up a pub, great guest to dinner and car nut. He’s difficult to spot on the road because you will never know what he’s driving – he’s not excessive and he keeps the same modest number plate – ending in “PHD” but his car varies from a Porsche 911 Turbo, to Elton’s Aston, to a new DB9 to a Merc SL 550 AMG to a Range Rover which belongs to his equally talented wife.

I’m about the only person that he can talk to who understands what he does and even he doesn’t look for consequences – he’s paid to construct a financial instrument which under a given set of conditions dictated to him by the bank will bring in profits. He and his team are expected to test them under those constraints. Supervision and Compliance are meant to test the risk, but as there are an infinite number of ways it can behave under an infinite number of different situations, they don’t.

Hence the Consolidated Debt Obligation farrago which was almost pure fraud compounded some quant errors when the bounds of design were breached. Uncertainties traded as certainties is telling someone fibs!

It is therefore scary to see some fairly similar techniques evolving in CRM, ERP and in the City again on the sales forecasting front. The method you choose to do a forecast must be appropriate to the properties of the business and the market. SymVolli will only do a consolidation of everyone’s expectations, flag things which are going wrong and allow management to burrow into the forecast daily if they want to.

NeuralWare, for whom Gary Rosensteel (NuCoPro) did some great work, designed a brilliant fuzzy software product. It’s a neural network, so if you really don’t know what drives your sales and your sales forecast, you can just chuck in the last two years of sales data and any other connected variables, like sunshine and temperature if you make ice cream and the value of the Dollar and the publics surveyed level of confidence in the economy and your marketing spend and how many sales people you employed over this period and how many sales calls they made.

It will then chew through the data testing different relationships and throwing out those that don’t work by comparing the results from early data to the actual later figures you have provided for it. It then evolves the ones which work. Once it’s gone through 18 months of data, if you have thought out the inputs well, it will come up with some algorithm or other (It doesn’t print it!) which is a best fit of the sales figures to variables you put in. You can now ask it to predict the next 6 months which you have not processed yet. If its forecast matches the last 6 months data, something is working.

You then have some degree of confidence that if you let it process the last 6 months of data as well, it will give you a reasonably accurate picture of where your sales are going over the next 3, 6 and 9 months. The further away you forecast the more likely things are to change, but here’s the beauty of the system. As new sales figures arrive they can be compared and contrasted to the forecast, the errors ascertained, assumptions checked and then they can be fed into NeuralWare to let it further refine its process.

By always having people revue the viability and likelihood of its predictions and results if you want to, on a weekly basis you can check that your unknown neural model is still churning out reality. If it goes askew, you can start from scratch and perhaps add further data suggested by the analysis.

This is not forecasting for the faint of heart as neither NeuralWare, the sales people or the CEO actually ever get to know what algorithm it is using. Being neural the form and detail of the equation is irrelevant and constantly evolving. I understand that they have clocked up some spectacular success stories!

Basically I’d like to be able to give it a whirl for those enquiries where SymVolli or a big spreadsheet is inappropriate!

Steve Alker
Xspirt

Rwhite: There is no info in your profile, so what’s your name – I can’t call you rwhite all year!

You are just about right. The inputs can be assessments from sales people, the current sales figures possibly backed by the last few months of figures, which are measured as accurately as possible (That’s easy for booked orders – an order is for $1002.50 or it is not! The problems arise in the constraints and variables because you cannot measure them to an infinite accuracy. And that sales figure might not be £1002.50 it might be 1002.496 rounded up or 1002.504 rounded down.

I know it’s hard to believe but if you would like to see it, I’ll send you an Excel spreadsheet in which you will find this in action. Using iteration to predict sales where the feedback exists and is none linear will cause tiny errors to make the sales forecast go haywire after only a few months. I am not able name “names” of those who are starting to do this because I might be plain wrong and because we might get sued, but believe me, the method is creeping in.

I wonder why marketers can’t learn from meteorologists who stopped trying to perform long range forecasts by running ever more complex programmes on ever more powerful computers from more and more accurate weather stations. Once they’d been alerted by Prof Ian Stewart in the 1980’s and a few other fine mathematicians that whilst they were trying to forecast a chaotic system and whilst they couldn’t measure the starting conditions to an infinite accuracy their long range forecasts were always going to be bunk.

If the met office had continued down the forecasting road which some economists, banks, some companies and now chunks of the marketing industry (It sells, like putting the word “Quantum” in a book title or product name) are going, the UK would by now be covered in a weather station every 10 meters, measuring to 6 decimals of accuracy feeding data into a computer the size of Manhattan or Manchester and the long range forecast would still be bunk!

Steve Alker
Xspirt

Rwhite again - sorry but I missed an important point that you had guessed at. Next weeks forecast gets plugged into two weeks hence with not a correction factor, but a compounding error, the nature and size of which you can't know.

If people who use iteration would do a reality check - run a few months forecast and then compare it to booked sales they'd quickly identify the problem which is as you look further to the future your forecast will become increasingly useless or not-fit-for purpose.

Human intervention to introduce a correction factor is good in principal but if your manufacturing plant needs 6 months to gear up for a big contract having a forecast which is OK for weeks, sort of OK for 2 months and anywhere in the world at 6 months is hopeless for manufacturing buying, staffing and cashflow.

Maybe it is a basic human weakness in sales. Forecasts are endlessly discussed in sales meetings, used a weapons of confidence destruction and occasionally used to mange hot looking potential orders. After 90 years of working in CRM where there is the potential to recod a snapshot of something called the forecast to whatever rules you use every day about 90% of customers don’t compare it to booked orders. My guess is that the reason is that it is embarrassingly fictional.

What is covered in public and at sales meetings is the graph of orders against target, which all to often is a straight line heading towards a made-up number with no seasonal or predictable variations overlain on it and another more wobbly line drooping somewhere below it! That is used to threaten and humiliate sales people and their managers which is great for incentivising and motivating them. How anyone can use a sales chart which implies that the company takes sales through the Christmas and Easter holidays or through the sales conference or whatever loses their credibility. It is an easy representation but it is wrong!

Koen earlier posting:

"If I get your proposed solution right, you are suggesting bringing in employee judgment about the future (by aggregating individual sales forecasts) into forecasts based on historical data. In my experience, this combination is indeed much better than either one by itself. How exactly are you executing this?

A) Yes people should be involved if you iterate
B) Aggregation and smart thinking is applicable to most Small and many larger sales operations. You can overlay other forecast data derived from known cyclical behaviour, econometrics, the weather, or iterative calculations which do not involve negative feedback. If you sell ice cream, you know that May-August will see the greatest sales in the Northern Hemisphere because it is warmer. Using the long range weather forecast to predict the sales in the week of August-7th-14th so you can stock up the ingredients now is barking mad. The LRF is chaotic so it likely will be wrong and so therefore will your sales forecast which uses it.

C) Exactly How we Execute is a state secret!!

Dawson

Thanks for your points – your observations are valuable and I largely agree. You can’t just mix and match forecasting techniques but I think that I get the point.

Short term forecast in a volatile market place is tricky (The unexpected happens) but if you hire someone to construct a viral or WOM campaign which is a success, then shouldn’t someone be looking at what might happen if it succeeds. To ignore it and for it to be a success, is a customer relations disaster as you get a backlog of disgruntled customers who can’t get their hands on what they want and want it now!

Medium term – mainly aggregating. Some iteration acceptable

Long term – use aggregation, overlay trends, overlay the econometrics, overlay cycles which sales have ignored, Don’t include the weather forecast, and if you want to get scary go neural with historic data. Iterative forecasting only if linear and no negative feedback. If the maths is chaotic, use another method or do it in acceptably short chunks this way and then use linear projections - involve people

Good Lord, one of my clients uses last years and this years Service Department sales trends for repairing things to forecast this years sales of new product as he doesn’t believe in a word the sales people report back to him through the sales director.

Its chicken entrails stuff again but until I can persuade the FD to believe the sales forecasts which come out of my software regardless of who they come from (We spot cheats and dreamers and pessimists) his only other option is pig’s entrails!!

Thanks for the comments


Steve Alker
Xspirt

Right - closing this on Friday so any last minute comments, get them in now

Thanks for everything so far - so valuable - such insights

Steve

No last minute comment...but just a thank-you for starting a lively discussion and answering each of our answers!

Cheers

Koen

Koen - thanks for your contributions and to everyone else who made this lively and useful

I now wish that I’d either set the points at 25 which is what the question was about and see if I got away with it, or for 25,000 points so I could reward all contributors in an adequate fashion.
However as we all know, the best present is not necessarily a gift of £25,000 from your wealthy Uncle but one which is non the less wholeheartedly appreciated. That said, Uncle, if you are reading forget what I’ve written and bear in mind that even £250,000 is spare change to you.

Koen - thanks for your contributions and to everyone else who made this lively and useful

I now wish that I’d either set the points at 25 which is what the question was about and see if I got away with it, or for 25,000 points so I could reward all contributors in an adequate fashion.

However as we all know, the best present is not necessarily a gift of £25,000 from your wealthy Uncle but one which is non the less wholeheartedly appreciated.

That said, Uncle, if you are reading forget, what I’ve written and bear in mind that even £250,000 is spare change to you.

Your loving nephew

Steve (Thats the one who lost it and went into marketing)