Life is uncertain. Here’s a spreadsheet for that.

Uncertainty is everywhere in life. You can view it as an exciting challenge or a scary threat but it won’t go away. As I’m typing this on the runway at Heathrow it’s not a comforting of thoughts.

The same is especially true of Agency’s finances. Clients can change their mind, pitches can be won or lost. Projects can be delayed until they are needed yesterday.

Trying to get a handle of what net effect of these movements will have on your Agency is a daunting challenge. Added to the uncertainty there is the practical problem of having different bits of information in different places.

Hopefully you’ll have a forecast. Probably you’ll have some form of CRM system to track new business and each account manager will be aware of what their clients might do next month/quarter.

If you want to have any sense of control this is an impossible situation. For many, many years I struggled with this. Wrestling data out of various sources to forecast what will probably happen can be a thankless task. Especially when it turns out to be wrong.

Occasionally, and I do mean occasionally, I get a moment of clarity. A coming together of ideas, a conversation which clears some previously obscured problem up.

I’m both happy to have had such a moment and a little embarrassed that it’s taken me over, ahem, 20 years to have such a moment about the best way to manage the uncertainty about Agency revenue forecasts.

I’ve written before about revenue forecasts. My advice was to take uncertainty out of the equation by only forecasting what was definitely going to happen.

That idea still has some merit but it’s not the full answer. It avoids the issue by shutting it’s eyes and covering it’s ears and hoping the nasty uncertainty will go away and leave you alone.

We all know this is not going to happen. To forecast accurately we have to embrace uncertainty but we have to do it systematically and we have to do it in a well constructed spreadsheet with each uncertainty broken down and a judged.

Now I’m aware that writing about spreadsheets has a limited appeal. But to make the point I will have to explain how this new, and to me vastly improved, forecast tool works.

I start by breaking by separating confirmed projects from potential activity with current clients with a further analysis for new business into 3 different worksheets. Never cross the streams.

The confirmed activity should represent the worst case scenario. Only projects which have signed off authorisation go into this list. Anything from your clients which hasn’t been signed off goes behind door number 2.

By adding some clever formula to the client development and new business sections we can decide whether each potential opportunity is probable/possible or hot/warm/cold (take your pick about how you classify them – the only rule is to be consistent). Only the probable or hot prospects will add through to the forecast.

The summarised forecast has a column for definite projects and one for projects you think will most likely happen even if you don’t have full sign off. This moves the forecast from being a pessimistic view of the minimum that will happen to a dynamic view of what will probably happen.

I call this view the Most Likely Outcome (“MLO”). It gives you the ability to breakdown uncertainty line by line. It moves our focus from what is happening to what should happen.

More importantly it gives us the ability to think what could happen if the possibles/warms were signed off. This means the forecast can move in from the wings as a necessary but rather dull spreadsheet to centre stage where it can become a planning tool, a “what if?” Dashboard. Exciting isn’t it?

Whether there is a gap to fill to hit your target or whether there is a resource shortage if a project is signed off it is vital to know the effect of the revenue increase and (any) costs you need to service the extra work.

This is a recent step forward. I’ve shared it with my clients who have, to a person, reacted positively. “Thanks for the new spreadsheets – I love them!” was the unprompted reaction.

So positive has the reaction been I’m going to spend some time and money to move it from a spreadsheet to a simple web based solution. Watch this space as I report back from time to time about the trials and successes of turning a simple forecast spreadsheet into a sophisticated planning tool for any size of agency.

Wish me luck.