How to Create a Forecasting Tool in Excel That Actually Works

Theoretically, Excel contains built-in tools for creating forecasts. Unfortunately, the real world is a little different.

No, it’s a lot different.

I say this as a professional who has provided Excel services since 1996, and created dozens of forecasting tools–both complex and simple–for a multitude of clients, spanning a wide variety of industries.

I am the first to laud how vital Excel is for any business, but on the subject of business forecasting tools and financial planning tools, I am the last to recommend relying entirely on its built-in functions.

If you really want an Excel forecasting tool that puts your business on the speed highway to success, you have to piece one together yourself using a combination of Excel’s features.

1. Raw Data is required for forecasting in Excel

First and foremost, you need raw data–the numbers, the dates, how much income was made, how many sales were made, how many leads the company obtained in a set period of time, etc.

Forecasting is not only about money in, it’s also about how many deals are in the pipeline, how many quotes have gone out in the last week or month. Good Excel forecasting is about percentages of deals that were closed compared to the number of quotes that went out. It’s about how much promotion went out, and what kind. How much internet advertising was done? How much offline marketing was done?

Data, data, data!

“We are moving slowly into an era where big data is the starting point, not the end.” – Pearl Zhu, author of the “Digital Master” book series. (source)

2. Use predictions when forecasting in Excel

Technically speaking, this also falls under Raw Data, but it is not data from the real world as such. It is predicted data.

The problem with Excel’s built-in forecasting capabilities is that they are far too automated. Microsoft doesn’t know your business. You know your business.

Just as you had a sheet which accepted all the raw data for your business in the last months or years, now you need a corollary sheet that will accept predictive data. This will most likely be in the form of percentages, for example, “We estimate that sales in Department X will increase by 10 percent” or “Promotional costs will go up by X amount in the next quarter, cutting profits by Y.”

The predictions are as varied as there are different types of businesses in the world. They’re up to you and your team to establish.

It can get pretty intricate. But sometimes it looks more complex than it really is. The trick is to establish what forecasting data is important and what isn’t.

It’s too easy to get carried away in Excel, buried underneath endless reams of raw data. Always try err in the direction of simplification, not more complexity. Pick the key data first, and build on from there as your tool becomes more involved.

3. Excel forecasting in action: Formulas

It is impossible to list out every single formula that would be of use to you in connecting the raw data with the predictions, resulting in the actual forecasting data.

Excel has over 475 formulas in its functions library. (I think I’ve used every single one of them at least once, to be honest.) It’s impossible for anyone to know them all, unless they work with Excel full-time.

The VLOOKUP function is invaluable.

Try and avoid overly complex or “black box” functions. It is important that you, the business owner, know why and how a computed value exists on the sheet.

This part is really easier than it might initially look. It does take someone with a fair amount of experience in Excel to pull it off. Like art, you have to know how to do the complex things before simplifying them, or dropping them entirely. I could tell you that all you needed was a combination of the SUM, SUMIF, COUNT and MATCH formulas to get a good forecast.

But it’s a little more involved than that.

The key thing to know is: Formulas will take you from the Raw Data and Predicted Data to the Final Forecast in Excel.

Here are some tips when using formulas, based off what we see too often here at Eppert Consulting, when improving on existing Excel Workbooks:

• Separate the formulas! Don’t try do it all in one long, endless formula. It gets too confusing. Rather split the formula across several columns to make debugging the formula easier in future.
• Err in the direction of simplicity.
• If you have too much data and the workbook becomes too slow, consider using the INDIRECT formula. This is an advanced subject and should be used with caution.

4. The charts

Lastly, you will want to add some charts to the sheet. Like everything in Excel, there’s the quick and easy way to do this, and then the intricate, exquisite way.

One really does need in-depth knowledge to make the final presentation something to write home about.

But Excel has also been designed for people who are not experts at it, and one can go a long way with its built-in wizards and tools to create great-looking charts and graphs to present at a meeting or to a potential client.

5. Other tips and tricks for forecasting in Excel

When forecasting in Excel, it’s easiest if you group your articles and apply predictions to each group, rather than applying those predictions to everything in bulk.

Another way to forecast is what we call the “top down” approach. This is more of a “Production Target” approach. You enter the raw data. Then, instead of typing in predictions, you type in a target for each group, such as “An increase of 10 percent in sales next year.”

Then you work backwards, working out what is needed in the various sub-departments and zones to achieve that goal.

There are many ways to skin this cat called Excel. It really is a fascinating piece of software with endless potential.

Making sense of data is our business

We have extensive experience in creating Excel forecasting tools. The most complex tool we created covers every phase of forecasting from acquisition to sale for a multi-million-Euro-per-annum retail corporation in the clothing industry.

The raw data can get complicated very quickly, even for small companies. In a sense, it’s even worse for small companies, because there are less people to take the load off and spend hours analyzing that data. If you’re a one-man show, likely you would just give up on forecasting altogether and simply go with your gut. Unfortunately, this is perilous in the long-term.

Making sense of data is our business–we work day in and day out with data and know how to spot what is important and what isn’t. We can usually figure out the best way to arrange the underlying raw data in an Excel workbook so that the resultant Excel forecasting becomes easier to do.

If you need help with any phase of your forecasting tool, give us a call, or send us a mail using the form below. We would be happy to help you.

Contact Us