Excel Factor 11 Forecasting Tricks

These Excel Factor tips were sent in by David Kramer of Denver, Colorado, USA.

Tip 1: Include or exclude parts of a projection

Often when forecasting you ask a lot of ‘what if’ questions. A simple tool you can use to include or exclude specific items from a forecast is a control cell.

In the example below we have a 1 in cell B3. This is the control cell and it enables you to include Bonuses by simply entering a 1, or exclude them by deleting it.

In the above image you can see in the formula bar that the bonus is 10% of the salary (=C6*0.1), and then you implement the control by multiplying the formula by the control cell:

=C6*.01*$B$3

Which evaluates like this:

=504*.01*1

Of course when the control cell is blank or zero, the formula resolves to zero like this:

Bonus tip: Suppress Zeros

I've set my worksheet to not display zeros as I think it looks a bit tidier. Here's how:

Excel 2010 Supress zeros:  File tab > Options > Advanced > Display options for this worksheet > uncheck the ‘Show a zero in cells that have zero value’.

Excel 2007 Supress zeros: Windows button > Excel Options > Advanced > Display options for this worksheet > uncheck the ‘Show a zero in cells that have zero value’.

Alternate Control

Instead of a 1 or zero, you could use a Y or an N as your control, but the formula would be slightly more complex. For example:

=IF($B$3="Y",B6*0.1,0)

Translated into English:

If B3 contains a 'Y' then multiply B6*0.1, otherwise enter a zero.

Go here for more on the IF Function.

Alternate Excel Forecasting Tool

Another tool you can use for forecasting and What If analysis is Excel  Scenarios.

Tip 2: Display an Indicator When a Total Isn’t Calculating Correctly

David uses a clever formula to let him know when his totals aren’t adding up correctly.

All he does is calculate his totals two different ways, and then compare them to check they are the same…or at least within reason.

If you don’t already do something like this then you should, and I’m about to show you how so you’ll have no excuse.

The formula in cell O9 is checking to see if the total in cell N9 is greater than 0.2 different to the sum of all values in cells B6:M8. If it's more it will return ???, and if it's less than 0.2 different it will return ‘ok’.

Here’s how it works:

Note: The total in cell N9 is the sum of N6:N8.

Checking that the difference is greater than .02 is optional. If you expect your totals to be exactly the same you could use this formula:

=IF(N9=SUM(B6:M8),"OK","???")

Or even better, cross check all totals like this:

=IF(AND(N9=SUM(B6:M8),N9=SUM(B9:M9),N9=SUM(N6:N8)),"OK","???")

If your totals are calculated by multiplying values, then it’s possible you will have some rounding differences between your different calculations, which is where the .02 test is handy.

Note: You can alter the degree of accuracy to suit your needs by changing the .02 to a bigger or smaller number.

Thanks for sharing these tips, David.

David is from Denver, Colorado, USA and has been using and teaching Excel for over 20 years.

“I like tricks that make it easy for a user to play “What if”, which is how these tricks were born. I’ve used Excel in a wide variety of applications but enjoy business modeling the most, especially modeling start-up companies.”

Vote for David

If you’d like to vote for David’s tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment….or all of the above 🙂