Using SUMIFS for long-range planning cost assumptions

Excel's SUMIFS formula is a simple but powerful function that any model-builder should understand. The logic is simple. The first argument is the range that the user wants to be added. The next argument pair is the criteria range and the accepted criterion required to add the value in the sum range.

Budgets and long-range plans are a great opportunity to use the SUMIFS formula. A data model I have had success with is using a tab to list of all the model assumptions and a second tab with a SUMIFS adding each unique intersection of assumption criterion. The SUMIFS formula calculates the total cost for each unique intersection.

Of course, there are a few positives and negatives when using the SUMIFS formula in models:

• POSITIVE: The SUMIFS formula is simple to understand - If your model is going to be shared, the SUMIFS formula is easy to understand. I've been burned by over-engineering Excel models in the past. But the SUMIFS formula is simple enough that anyone can understand how it works.
• POSITIVE: SUMIFS give you flexibility with different assumption criterion - The SUMIFS formula accepts multiple criterion associations, giving flexibility on different criteria to evaluate. This requires some balance. Models need to be complex enough to paint an accurate picture but simple enough for someone else to follow along. Which leads me to my next point...
• NEGATIVE: The more unique intersections, the more cumbersome the model becomes - Lets assume that Company A has 40 GL accounts, 12 departments and 5 programs. In this instance, the total possible unique intersections would be 2,400 (40 x 12 x 5). If you wanted to add a fourth criterion with 5 assumptions, now your unique combinations becomes 12,000. This will slow down your model. The workaround is to keep only the selected combinations using the Remove Duplicates feature, but users must remember to update this each time assumptions are added.
• NEGATIVE: The assumption criterion need to be an exact match, otherwise the formula won't work - The SUMIFS formula criterion must be an exact match to the assumption criterion. Excel doesn't give you a warning that the formula is failing. The returned value from the SUMIFS will simply be a zero. The model will need to have several quality checks to ensure everything matches (this is a good principle even if you aren't using a SUMIFS).

The SUMIFS is a powerful formula for building models. However, it has its limitations which will make it challenging to maintain and collaborate. The maintenance required is why 88% of spreadsheets contain errors.

intheBlk's long-range planning model includes all the functionality detailed above, just without the maintenance. Our program cost criterion include programs, departments, development phases and cost treatment timing. If building a model is daunting or Excel's limitations have made it too challenging to build a useful model, contact us to get a demo of our long-range planning tool today.