excelisfun - MSPTDA 25: Budget vs Actual, Excel Formula Solution with SUMIFS & EOMONTH

Short Summary

Main recommendation: To compare budget against actual sales when the two tables are at different grains (budget at month-product level, transactions at day-product level), use an Excel worksheet formula built with SUMIFS and EOMONTH to aggregate the transactional sales up to the month-product grain, then compute the variance and percent variance against the budget. The video demonstrates this step by step and notes that the same comparison can later be done with DAX and the data model in the next video.

Key Points

• The budget table is at the product-month grain, while the transactional sales table is at the product-transaction (day) grain, so the sales must be summarized before comparison.
• SUMIFS is used with three conditions: matching product, dates greater than or equal to the first of the month, and dates less than or equal to the end of the month.
• EOMONTH supplies the upper limit (end of the current month) and, with a -1 offset, the lower limit (end of the prior month, used with a "greater than" comparison to exclude that day).
• Variance is calculated as actual minus budget (positive when sales exceed budget), and percentage variance as the variance divided by budget.
• The video's example shows some products above target and some below, for example a case where 260,000 is less than a 266,000 budget.

Details Worth Noting

• Concrete instructions:
  1. In the top cell of the actual sales column, enter =SUMIFS(, then press Control+Shift+Down Arrow and F4 to lock the sum range, then add a comma.
  2. Criteria range 1: select the product column (locked with Control+Shift+Down Arrow and F4), add a comma, and use the row's product cell as Criteria 1 (relative reference).
  3. Criteria range 2: select the date column, lock it, then add a comma. Criteria 2 is the lower limit: type ">" in double quotes, then "&" and EOMONTH(date cell, -1), then close the parenthesis. Use ">" (not ">=") so the last day of the prior month is excluded.
  4. Criteria range 3: copy the date column range from step 3 and paste it as the third range, then add a comma. Criteria 3 is the upper limit: type "<=" in double quotes, then "&" and the EOMONTH(date cell, 0) reference (the end of the month).
  5. Close the parenthesis and press Control+Enter, then double-click the fill handle to copy the formula down the column.
  6. Press F2 on the last cell to verify that all references point to the correct rows and ranges.
  7. Variance: enter =Actual − Budget in the next column, press Control+Enter, and fill down.
  8. Percent variance: enter =Variance ÷ Budget, press Control+Enter, fill down, and format the cells as a percentage with two decimals.
• The video uses named ranges only as absolute references (locking with F4) and relative references for each row's product and end-of-month values; the same pattern applies to any product-month summary.
• The method relies on the transactional dates falling within the month; transactions outside the defined bounds are excluded by design.

Bottom Line

Aggregating transactional sales to the budget's month-product grain with SUMIFS and EOMONTH makes a direct, row-by-row budget-versus-actual comparison possible in Excel, and the data model approach is previewed as the next step.