• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Factor 11 Forecasting Tricks

You are here: Home / Excel Formulas / Excel Factor 11 Forecasting Tricks
Excel Factor 11 Forecasting Tricks
August 22, 2012 by Mynda Treacy
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.

Excel Forecasting Tip Example

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:

Excel Forecasting Tip Example

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’.

Excel Forecasting Tip Example

Here’s how it works:

Excel Forecasting Tip Example

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 🙂

Excel Factor 11 Forecasting Tricks

More Excel_Factor Posts

Excel Factor Voting Roundup

Excel Factor Voting Roundup

Excel Factor 21 Hyperlink Triptych

Excel Factor 21 Hyperlink Triptych

Tips and tricks with hyperlinks to make them dynamically update as the selection in the sheet changes. Sample workbook available.
Excel Factor 20 Custom Number Format Disguise

Excel Factor 20 Custom Number Format Disguise

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 18 Dynamic Hyperlinks and the Magic Hash

Excel Factor 18 Dynamic Hyperlinks and the Magic Hash

The Excel HYPERLINK function creates links to places inside or outside of your Excel file. I'll show you a shortcut for creating them that few people know.
Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 16 Dynamic Lookup

Excel Factor 16 Dynamic Lookup

Excel Factor 15 The Lazy Lookup

Excel Factor 15 The Lazy Lookup

Excel Factor 14 Interleave Data from Two Columns into One

Excel Factor 14 Interleave Data from Two Columns into One

Excel Factor 13 Handy Tips & Tricks

Excel Factor 13 Handy Tips & Tricks

More Excel Formulas Posts

excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.


Category: Excel FormulasTag: Excel_Factor
Previous Post:Excel Factor 10 Conditional Formatting Painting by NumbersExcel Factor 10 Conditional Formatting Painting by Numbers
Next Post:Excel Factor 12 Secret EVALUATE FunctionExcel Factor 12 Secret EVALUATE Function

Reader Interactions

Comments

  1. asim saleh

    September 10, 2012 at 1:36 am

    thksThank you for this interesting commentary .. I am very happy and I wish you well

    Reply
    • Mynda Treacy

      September 10, 2012 at 1:08 pm

      Cheers, Asim 🙂

      Reply
  2. Mynda Treacy

    August 24, 2012 at 9:20 am

    From David Kramer:

    Thanks, Mynda, for your excellent, clear write-up of my tip. The Control Cell idea can be simplified and expanded. To simplify it, we combine the Control Cell and bonus multiplier into one. Cell C8 in the write-up has
    =C6*.01*$B$3

    The idea is to toggle B3 between 1 and 0 whether bonuses are included or excluded. By putting the bonus multiplier into B3, you can reduce the formula in C8 to
    =C6*$B$3

    If you do not want to apply bonuses, enter a 0 or blank; otherwise enter the bonus multiplier. This is a standard use of Excel for projections. To expand the idea, enter the bonus multiplier in another cell, say B2, and change the formula in C8 to
    =C6*$B$2*$B$3

    This lets you to easily alter the bonus multiplier and independently indicate whether you want bonuses included.

    Cheers!

    David Kramer
    Denver, CO
    USA

    Reply
  3. John Johnson

    August 24, 2012 at 5:37 am

    Hi

    I particularly like the check sum method used. It was very good.

    Reply
    • Mynda Treacy

      August 24, 2012 at 9:20 am

      Cheers, John. I’ll pass on your comments to David.

      Kind regards,

      Mynda.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.