Excel Budget Report with Value Selector

Instead of showing a budget’s forecast, actual and variance data all at once, click a button to view the values one at a time. That makes the report easier to read, and takes less space on the worksheet. See how this technique works in my Excel Budget Report with value selector workbook.

Excel Budget Report

There’s a budget report tutorial on my website, which shows how to set up a workbook with forecast and actual budget amounts, and then calculate the variance.

  • It has a traditional layout, with months across the columns, and budget categories down the side.
  • There are separate sheets for the Forecast amounts and the Actual amounts
  • The sheet layouts have to be exactly the same, or things could go wrong

Here’s the Forecast sheet, where you enter the amounts in the green cells.

Time for an Update

My original budget report workbook was created way back in September 2002! It was time for a new version of the budget workbook, using today’s Excel tools.

The new Excel Budget Report workbook takes a different approach for entering and reporting the budget amounts. Here’s what the new budget report looks like, and there are setup details below.

budgetreporter01[3]

Named Table for Data Entry

Instead of entering the Forecast and Actual amounts horizontally, the new workbook has a named table for data entry – tblInput.

Enter the Forecast amounts there, and later, add the Actual amounts, when they’re available.

budgetreporter02

Calculated Budget Variance

The data entry table also has columns with formulas to calculate the Year to Date (YTD) amounts, and the Variance.

For YTD, the actual amount is used, if it has been entered. Otherwise, the forecast amount is used.

  • =IF([@Actual]=””,[@Forecast],[@Actual])

For Variance, the Forecast is subtracted from the Actual, or zero is shown, if there is no Actual amount.

  • =IF([@Actual]=””,0,[@Actual]-[@Forecast])

For Variance %, Actual is divided by Forecast, and 1 is subtracted, or zero is shown, if there is no Actual amount.

  • =IF([@Actual]=””,0,[@Actual]/[@Forecast]-1)

budgetreporter03

Excel Budget Report Types

On another sheet (Lists), there is a named table (tblRpt), with the five budget report types that will be available.

In the adjacent column, the number format for each report type is entered.

NOTE: The budget report types are an exact match for the columns in the data entry table. In another formula, the MATCH function will find the column heading that matches our selected report type.

budgetreporter04

There is also a pivot table based on the Report types table, with the Reports field in the Rows area.

  • The value in cell I2 will be used in a “Report” formula, to determine which values to show in our Budget report.
  • The pivot table name was changed to ptRptSel

budgetreporter05

Report Format

In a cell named FormatSel, an INDEX/MATCH formula returns the format for the report type in cell I2

  • =INDEX(tblRpt[Format],MATCH(I2,tblRpt[Reports],0))

That value will be used in a macro that formats the final report.

budgetreporter06

Report Type Slicer

The next step is to insert a Slicer, based on the Report Type pivot table. This will be used to select which values to show in the final report.

Change the Slicer settings to hide the headings, and set it to show 5 columns

budgetreporter07

Adjust the Slicer’s size, so the five Report Types are visible. The Slicer will be moved to a different sheet later.

budgetreporter08

Report Value Calculation

Back on the Data Entry sheet, the final column in the input table is “Report” This column has an INDEX/MATCH formula that gets the value based on the Report Type in cell I2 on the Lists sheet:

=INDEX(tblInput[@[Code]:[Var%]], MATCH(Lists!$I$2, tblInput[#Headers],0))

Currently, cell A2 show “Actual”, so that value is returned in the Report column.

budgetreporter09

Pivot Table Budget Report

To create the budget report, insert a pivot table, based on the input table.

  • Put the Category, Code and Item fields in the Row area
  • Add the Month field to the Column area
  • Put the Report field in the Values area.
  • Change the pivot table name to ptRpt
  • Cut the Slicer from the Lists sheet, and paste it above the budget report pivot table.

Without macros,

  • click a Report Type in the Slicer
  • then refresh the pivot table to see the values change.

The Budget Report sample file has a macro, to automate that.

budgetreporter10

Macro to Update the Pivot Table

The Budget Report workbook has a macro to update the pivot table’s Report values automatically, when you click a Report Type on the Slicer. The macro also formats the numbers, based on our lookup table.

To see the code:

  • Right-click the Lists sheet tab, and click View Code
  • The Worksheet_PivotTableUpdate event code that updates the pivot table is shown below

The Slicer updates the Report Type pivot table (ptRptSel), and that fires this update event.

Private Sub Worksheet_PivotTableUpdate _
  (ByVal Target As PivotTable)
Dim pt As PivotTable

Select Case Target.Name
  Case "ptRptSel"
    Set pt = Sheets("Report") _
      .PivotTables("ptRpt")
    With pt
      .RefreshTable
      .PivotFields("Sum of Report") _
        .NumberFormat = Sheets("Lists") _
          .Range("FormatSel").Value
    End With
End Select
End Sub

Test the Report Type Slicer

After the macro has been added, go back to the Report sheet, and test the Slicer.

  • Click a report type in the Slicer, and see those values in the pivot table.
  • The Report column in the data entry table calculates which value to show
  • The macro refreshes the pivot table values, and applies the number format.

In the animated screen shot below, you can see that the blue text changes, when the Slicer is clicked. That cell (B2) is linked to cell I2 on the Lists sheet, to show the selected Report Type.

Excel Budget Report

Another Budget Report Slicer

In the sample file, there’s another Slicer too — use it to show or hide the zeros on the Budget Report sheet.

budgetreporter11

That slicer is based on another named table and pivot table (ptZeros) on the Lists sheet.

budgetreporter12

There’s an extra section in the code on the Lists sheet, to show or hide the zeros on the Budget Report sheet.

  Case "ptZeros"
    With ActiveWindow
      Select Case Range("ZeroSel").Value
        Case "Hide 0s"
          .DisplayZeros = False
        Case Else
          .DisplayZeros = True
      End Select
    End With

Get the Excel Budget Report Workbook

To get the Excel Budget Report with Value Selector workbook, go to the Budget Variance page on my website.

This example is #2 in the download section. The zipped file is in xlsm format, and contains macros.

__________________________

The post Excel Budget Report with Value Selector appeared first on Contextures Blog.

Original source: http://blog.contextures.com/archives/2018/02/22/excel-budget-report-with-value-selector/

Leave a Reply

Close Menu