Chapter 15: Reports for Management Decisions

Accounting Analytics: An Integrated Approach

In this chapter

Part IV of V: Data Visualization and Power BI

Chapter 3 of 4 in this Part

• 15.1 Designing Reports for Their Readers
• 15.2 Drill-Through and Report-Page Tooltips
• Guided Tutorial 15.1

• 15.4 Budget Versus Actual
• 15.5 Visual Calculations
• Guided Tutorial 15.2
• 15.7 Assembling the Reporting Package
• Guided Tutorial 15.3
• 15.9 What-If Parameters and Forecasts
• 15.10 Looking Ahead

This chapter turns the model of Chapter 14 into the company’s monthly reporting package, with a page for each of three readers. Each module follows a section of the chapter, and the three guided tutorials build the Promotions, Budget and Plant pages of Charles River Reports.pbix.

Learning objectives

1. Design a report page for a named reader and decision, with key performance indicators shown against their comparisons and a layout that passes a five-second test.
2. Build drill-through pages and report-page tooltips that let a reader move from a summary to its detail.
3. Model a budget at the grain of month, account, and cost center beside the ledger, and explain a budget variance as volume, classification, timing, rate, or spending.

The first three objectives cover designing a page for its reader, drill-through and tooltips, and the budget against the ledger.

Learning objectives (continued)

4. Write visual calculations for running sums and moving averages, and decide when a measure in the model is the better choice.
5. Assemble a reporting package with navigation, an About page, and validation.
6. Use what-if parameters and the Analytics pane’s forecast, and judge their results against the models of Chapter 7 and the Part II case.

The last three cover visual calculations, assembling the package, and what-if parameters and forecasts.

Three readers will open the package first

• The sales manager: what the promotions cost, and which cost most
• The department managers: their spending against the approved budget
• The production manager: whether the plant’s hours are under control
• One department spent far less than budget, another far more

It is April, and the controller wants Charles River Reports to become the monthly package. Neither of the two budget results means what it seems. The task is to design a page for each reader, connect the pages so a reader can move from a summary to the detail behind it, and explain each number before it reaches someone who will act on it.

15.1 Designing Reports for Their Readers

Start with the reader’s decision

• A page serves a reader with a question and a decision to make
• Its readers did not build the model, and give a page a few seconds
• A KPI is a measure chosen to track progress toward an objective
• It is shown with the comparison that gives it meaning

Operating expense of several million dollars is neither good nor bad until the reader knows the budget, the same period last year, or a target. Few argues that a display meant for monitoring should fit on one screen and show each measure in the context that lets the reader judge it at a glance. Few calls such displays dashboards; Power BI reserves that word for the service, so the chapter says report page.

How a page is shown shapes the judgment

• Readers weigh shared measures and pass over unique ones
• Organization and markers shift the weights, so state each threshold
• Put the KPI and its comparison top left, the breakdown next
• The five-second test: can someone say what the page shows?

Lipe and Salterio found that participants acting as superiors, evaluating two units, relied on the measures common to both and ignored those unique to each. That is a warning, not a reason to give every department the same KPIs: define the shared measures the same way, keep each department’s own measures, and make clear that they count. Cardinaels and van Veen-Dirks found that organizing the measures changed how evaluators weighted them, and in a second experiment, with markers for above, below and on target on both scorecards, the organized scorecard drew more weight to any category in which the units differed, so a marker should appear only where it signals something to act on. Detail is available on demand, through drill-through pages and tooltips, rather than crowding the page.

Each reader gets a page and a KPI

Table 15.1 · The readers of the reporting package

Reader	Question	KPI and its comparison	Page
Sales manager	What did the promotions cost, and where?	Revenue from list price to net, by step; discounts by promotion	Promotions
Department manager	Is my cost center within its budget?	Operating expense against the budget, flexed for volume	Budget
Production manager	Are the plant’s hours under control?	Hours per standard hour over twelve months	Plant
Controller	Do the pages agree with the ledger?	Control totals of earlier chapters	Validation

The controller’s Validation page carries the control totals of the earlier chapters, so every other page can be trusted.

Four pages for four readers, and an About page

Figure 15.1 · The Reporting Package and Its Readers (detail)

The About page opens the package and states its purpose, readers, source and period, and where each KPI is defined and tested. It holds the page navigator, as every visible page does, with one button for each visible page.

Three hidden pages hold the detail

Figure 15.1 · The Reporting Package and Its Readers (detail)

Promotion Detail and Cost Center Detail are reached by drill-through from the Promotions and Budget pages, and Promotion Tooltip by pointing at a promotion. They are hidden from the page tabs and the navigator, and left by the back button or by moving the pointer away.

15.2 Drill-Through and Report-Page Tooltips

Drill-through and tooltips reach the detail

• Drill-down stays inside one visual; drill-through opens another page
• Drill-through filters that page to the item the reader right-clicked
• A report-page tooltip is a small page shown while the reader points
• Tooltips are not interactive, and screen readers cannot read them

The drill-through field sits in a well of its own on the detail page, and Power BI adds a back button to it. The field must also appear in the visual the reader right-clicks, or the option does not appear. A tooltip page is set to the tooltip canvas size and switched on as a tooltip in its page settings. Nothing the reader must know should appear only in a tooltip.

Three ways from a summary to its detail

Table 15.2 · Three ways to move from a summary to detail

	Drill-down	Drill-through	Report-page tooltip
What changes	The level shown in the same visual	The page, filtered to the selected item	A small page shown while pointing
How the reader starts it	Expand a level or use the drill buttons	Right-click, then Drillthrough	Point at a data point
Best for	Moving down a hierarchy	Studying one item in detail	A quick second view
Accessible to screen readers	Yes	Yes	No

Drill-through is how a report lets managers explore causes on their own, as Chapter 1 promised, without placing every detail on the summary page.

Drill-down stays put; drill-through opens a page

Figure 15.2 · Drill-Down Within a Visual and Drill-Through to Another Page (detail)

On the left, expanding Furniture shows its product types in the same matrix, as in Chapter 13. On the right, right-clicking a promotion’s bar and choosing Drillthrough opens a separate page filtered to that promotion, with a back button.

Check your understanding

Drill-down, drill-through or tooltip?

1. Move from an item group to its product types
2. Study one promotion’s terms and months in detail
3. Glance at one promotion’s months while pointing at it
4. A fact that a screen-reader user must be able to find

Ask students which of the four should never rely on a tooltip.

Each need has its tool

1. Drill-down, in the same visual
2. Drill-through, to a detail page
3. A report-page tooltip
4. Never only a tooltip: put it on the page

Drill-down and drill-through are accessible to screen readers; a tooltip is not.

Guided Tutorial 15.1: The Promotions Page

Build the promotions page in nine steps

1. Import the promotions
2. Relate the promotions to the invoice lines
3. Build a table of the steps from list price to net
4. Write the gross-to-net measure
5. Chart the steps as a waterfall
6. Show the discounts by promotion
7. Build the drill-through page
8. Build the tooltip page
9. Use the detail pages

Students import the promotions and relate them to the invoice lines, where lines sold without a promotion appear under (Blank), a meaningful blank. They build a disconnected table of steps, chart the steps from list price to net as a waterfall, show the discounts by promotion, and add a drill-through page and a tooltip page.

A disconnected table and SWITCH make the steps

Gross to Net =
SWITCH (
    SELECTEDVALUE ( Steps[Step] ),
    "At list price", [List Amount],
    "Price lists and overrides",
        [Price Before Discount] - [List Amount],
    "Promotions",
        [Revenue] - [Price Before Discount],
    [Revenue]
)

• Steps relates to nothing: its rows only give the waterfall its categories

SELECTEDVALUE returns the step of the current bar, or a blank when the context holds more than one step, as it does for a total, and the last argument returns revenue itself. The promotions step is revenue less the price before discount, a negative amount, rather than the Discounts measure with its sign reversed, so that the steps add up to the cent.

Everyday prices take more than the promotions

Figure 15.3 · The Promotions Page (detail)

The waterfall starts at the list amount, steps down twice, and ends with the total that Power BI adds, equal to the latest year’s revenue of Chapter 6. The price lists and overrides take far more off the list amount than the promotions do, as the Part II case found.

One promotion gave away several times any other

Figure 15.3 · The Promotions Page (detail)

The Furniture Seasonal Promotion leads by far. Two promotions of the year before still appear, because their last orders were invoiced early in the latest year.

Drill-through: one promotion, month by month

Figure 15.4 · The Promotion Detail Drill-Through Page (detail)

The Furniture Seasonal Promotion ran in September and October, but its discounts continue into November and December: Chapter 5 found those lines were ordered within the promotion’s dates and invoiced later. Drilling through on the Harbor Collection Promotion shows an end date before its start date, which cannot be right. The Part II case found failing recorded dates in every collection promotion, and their orders point to the end date as the likely error.

The tooltip shows the months while pointing

Figure 15.5 · A Report-Page Tooltip (detail)

The pointer rests on the promotion’s bar, and the Promotion Tooltip page appears beside it, filtered to that promotion. A summary page could not have shown either finding of the detail pages; they appear in two clicks.

Checkpoint

Before you move on, confirm the following:

• The waterfall’s total equals fiscal 2026 revenue, and its steps are the list amount, the price lists and overrides, and the promotions.
• PromotionProgram is related to the invoice lines, and Steps is related to nothing.
• Pointing at a promotion shows its tooltip page, and right-clicking it opens Promotion Detail filtered to that promotion.

The sales manager’s question now has a one-page answer and two levels of detail. The page also shows something the question did not ask: the promotions are the smaller of the two reductions from list price.

15.4 Budget Versus Actual

The budget is a second fact table beside the ledger

• Each line is one account, month and cost center, approved in advance
• It shares Account, CostCenter and Date with the ledger
• A line has a month but no day: relate the month’s first day to Date
• Variance is actual less budget, and the report must say so

Microsoft’s guidance for facts at a higher grain than the date table is to store the first day of each period. The budget then adds up correctly by month, quarter and year, so budget measures are read by month or above. For an expense, a positive variance means spending above the budget, which is unfavorable; for revenue the signs mean the opposite. Variance percent divides the variance by the budget.

A variance measures a difference; causes explain it

Table 15.3 · Causes of a budget variance

Cause	What happened	Charles River, fiscal 2026
Volume	Activity differed from the budget’s assumption	Commission budgeted on the budget’s sales volume
Classification	The budget and the ledger record the cost in different places	Depreciation budgeted in Administration, recorded partly in the Warehouse
Timing	The cost fell in a different month from the budget	Salaries paid in months with three pay dates
Rate	The price per unit differed from the budget’s	Payroll burden below the budgeted rate
Spending	More or less was used than planned	To be investigated in what remains after the other causes

Charles River’s operating expense shows four of the causes at once. Sales spent far less than its budget, mostly from volume. The Warehouse spent more and Administration less, offsetting each other, a classification difference that Chapter 2 warned about. What remains is a few percent under budget almost everywhere, consistent with the payroll burden rate, and the months with three pay dates are unfavorable against a budget that spreads salaries evenly.

Watch out: a static sales budget misleads

• The sales budget plans several times the volume actually sold
• Revenue and cost of sales fall far short of it every month
• The huge variances measure the plan’s volume, not operating performance
• Flex the budget to actual volume first, or leave revenue off

Against the static budget, the page would show enormous unfavorable revenue variances and favorable cost variances. Flex the budget as Chapter 7 did, or leave revenue off the page and say why.

Mark the variances, never with color alone

• Conditional formatting places an icon beside a value, by rules
• An arrow up for spending five percent or more above budget
• A circle in between, an arrow down five percent or more below
• Different shapes, beside the signed percentage, work in grayscale

This is the rule Chapters 4 and 6 set and Chapter 13 applied to every visual: meaning never rests on color alone. A marker appears only where it signals something the reader should act on, and its threshold is stated. The five percent is this package’s choice, not a standard.

Check your understanding

Which cause explains each variance?

1. Commission budgeted on the budget’s sales volume
2. Depreciation budgeted in one cost center, recorded in another
3. Salaries paid in a month with three pay dates
4. Payroll burden below the budgeted rate

Ask students which of the four nets to zero across the cost centers.

Each variance has its cause

1. Volume
2. Classification
3. Timing
4. Rate

The classification difference nets to zero: it moves cost from one cost center to another without changing the total. Spending is investigated in what remains after the other causes.

15.5 Visual Calculations

A visual calculation works on its visual’s rows

• A DAX calculation defined on one visual and stored with it
• Home > New visual calculation opens an editor with the visual matrix
• Functions: RUNNINGSUM, MOVINGAVERAGE, PREVIOUS and FIRST
• Each takes one short formula, and templates write the common ones

Optional arguments name the direction the calculation moves through the visual, down the rows or across the columns, and restart it at each change of a higher level, such as each year in a visual of months.

Visual calculations and their SQL counterparts

Table 15.4 · Visual calculation functions and their SQL counterparts

Visual calculation	What it returns	SQL counterpart (Chapter 11)
RUNNINGSUM	The running total down the rows	SUM with an OVER clause ending at the current row
MOVINGAVERAGE	The average of the last n rows	AVG over a frame of n rows
PREVIOUS	The value of the previous row	LAG
FIRST	The value of the first row	FIRST_VALUE
COLLAPSE	The value at the parent level	An aggregate at the parent level, such as SUM over a window partitioned by the parent

These are conceptual counterparts, not translations. The order of the rows in the visual plays the part of ORDER BY, restarting at a higher level plays the part of PARTITION BY, and the number of rows in a moving average plays the part of the frame, as in Chapter 11.

It sees only what its visual shows

• On a chart starting mid-way, a moving average has fewer months
• It cannot be reused in another visual or pinned, and its exports need a test
• One visual’s shape, such as a year-to-date line: a visual calculation
• A number readers rely on, compare or export: a measure with a test

A measure written with the time intelligence of Chapter 14 reaches into the model and finds the months before the chart starts; a visual calculation sees only the visual’s rows. Tutorial 15.3 shows the difference on the plant page.

Where a calculation can live

Table 15.5 · Where a calculation can live in an Import model

	Measure	Visual calculation	Calculated column
Defined in	The model	One visual	A table of the model
Sees	The whole model, through the filter context	Only the rows and columns of its visual	The current row and related rows
Calculated	When a visual asks for it	When its visual is drawn	At refresh, and stored
Reused elsewhere	Yes	No	Yes
Exported with the data	Yes	Test the export mode and version	Yes

The calculated column of Chapter 14 completes the comparison: in an Import model like this one it sees its own row and related rows, is stored at refresh, and can be used to filter, group or sort. Whether an export includes a visual calculation depends on the export mode and the Power BI version, and an export of the underlying data leaves it out, so test the export readers will use.

Check your understanding

Measure, visual calculation or calculated column?

1. A year-to-date line on one chart
2. The trailing ratio the production manager relies on
3. A figure readers export and compare across pages
4. A value that belongs to each row, to slice by

Ask students what would go wrong if the second were a visual calculation on a filtered chart.

Each calculation has its place

1. A visual calculation
2. A measure, whose window reaches into the model
3. A measure, with a description and a test
4. A calculated column

The second is the lesson of Tutorial 15.3: on a chart that starts mid-way, the visual calculation’s window holds fewer months than it should.

Guided Tutorial 15.2: The Cost-Center Budget Page

Steps 1 to 6: import, relate, measure and flex

1. Import the budget lines
2. Relate the budget to the ledger’s dimensions
3. Write the budget measures
4. Compare each cost center with its budget
5. Mark the variances that need attention
6. Flex the commission budget

Budget sums the budget lines, Actual is Chapter 14’s GL Amount, and Variance is actual less budget. The Flexed Budget measure replaces the commission budget with the budget’s commission rate times actual product revenue, without design services, and leaves every other budget line as it was; it reproduces the flexed commission of Chapter 7.

Steps 7 to 11: compare, explain and test

7. Compare the spending with the flexed budget
8. Drill through to the accounts
9. Reconcile the variances
10. Show how the variance built up during the year
11. Add the budget test

Drilling through to the accounts finds a depreciation account with expense and no budget in the Warehouse, and a depreciation budget well above the actual in Administration. Test 4 checks the budget, flexed budget and actual against Chapter 7 and Tutorial 14.3.

BudgetLine shares three dimensions with the ledger

Figure 15.7 · The Budget Beside the Ledger in Model View (detail)

BudgetLine and GLEntry are two fact tables sharing Account, CostCenter and Date, so a visual grouped by cost center or month can place the budget beside the ledger. BudgetDate, the first day of each budget month, is added in Step 1. Budget lines and postings without a cost center meet in the (Blank) row.

Three cost centers stand out against the budget

Figure 15.8 · The Budget Matrix With Variance Markers (detail)

Sales spent far less than its budget, the Warehouse far more, and Administration less; every other cost center is a few percent under. The (Blank) row holds the expenses without a cost center, such as rent, utilities and insurance, which Chapter 7’s Budget Table left out; here the budget and actual agree closely.

Volume and classification explain the big variances

Figure 15.9 · The Variance Reconciliation by Cost Center (detail)

Each cost center’s variance is the volume in the commission budget, plus the classification of depreciation, plus what remains. The classification column totals zero, the signature of a classification difference. What remains is a few percent below budget nearly everywhere, consistent with the payroll burden rate, and it still has to be explained before anyone calls it efficient or wasteful spending. Sales remains further below, because the actual commission rate on merchandise was also below the budgeted rate.

RUNNINGSUM shows how the variance built up

Figure 15.6 · The Visual Calculation Editor (detail)

The year-to-date variance climbs in January, falls through June, climbs again in July, and falls for the rest of the year. January and July are the months with three pay dates: against a budget that spreads salaries evenly, they look unfavorable and every other month favorable. A reader in July would have seen operating expense close to the flexed budget for the year to date, and the months between show the payroll timing behind it.

Checkpoint

Before you move on, confirm the following:

• BudgetLine relates to Account, CostCenter, and Date, and the Budget page compares each cost center’s operating expense with its budget.
• The flexed budget restates only the commission budget, and Test 4 returns the budget, flexed budget, and actual amounts of Chapter 7 and Tutorial 14.3.
• The classification effect totals zero, and the remaining variance is a few percent below budget for nearly every cost center.

Without the flex and the reconciliation, the page would have rewarded Sales for a budget inflated by volume and blamed the Warehouse for a cost it was never budgeted.

15.7 Assembling the Reporting Package

Navigation that keeps itself up to date

• A page navigator’s buttons follow the pages as they change
• Detail and tooltip pages are hidden from the tabs and the navigator
• A bookmark saves a page’s state: for views, not page navigation
• A field parameter lets the reader choose the measure a chart shows

In Desktop, and while a report is edited in the service, a button responds to a click only while Ctrl is held, so you can still select it to edit it; a reader viewing the report simply clicks it. A bookmark keeps the state it captured, its filters, slicers, sort order and visible visuals, until someone updates it, while the navigator follows the pages by itself. Hiding a page tidies the navigation; it does not decide who may see the data. A field parameter is a small table listing fields with NAMEOF, and it cannot serve as the field of a drill-through or tooltip page. Microsoft’s workaround links the fields the parameter references, which a parameter of measures does not offer.

The About page documents the package

Figure 15.12 · The About Page With Its Page Navigator (detail)

The About page states the report’s purpose, its readers, its source and period, and where each KPI is defined and tested, the counterpart of Chapter 4’s Documentation worksheet. Below the text sits the page navigator, one button for each visible page. Every measure carries a description, the Validation page keeps reproducing the sales totals, and the Tests tab grows with each new page. Every Part of the book has ended its tool’s work this way, because an error in a monthly report is repeated every month until someone finds it.

Check your understanding

Which tool does each need?

1. Buttons that follow pages as they are added or renamed
2. A saved view of a page prepared for a meeting
3. One chart that switches between two measures
4. The report’s purpose, source and KPI definitions

Ask students which of the four would go stale when the page changes.

Each need has its tool

1. A page navigator
2. A bookmark
3. A field parameter
4. The About page, with a description on every measure

The bookmark is the one that can go stale: it keeps the state it captured until someone updates it.

Guided Tutorial 15.3: The Plant Page and the Package

Steps 1 to 7: model the plant and its hours

1. Choose the plant’s Tables
2. Keep the columns the model needs
3. Merge the headers into the facts
4. Relate the plant tables
5. Write the hours measures
6. Measure the output and the ratio
7. Measure the trailing twelve months

Labor time and completions join the model as two more fact tables, with Employee, WorkCenter, Item and Date as dimensions. A labor line takes the work center of its operation, so indirect time has none and appears under (Blank). Standard hours are cut off at the last day with time records, December 11, 2026, as in Chapters 10 and 11. Indirect time ends a week earlier, on December 4, 2026, so the ratio is an observed-records diagnostic, not a labor efficiency variance, and its last month is provisional. Item filters the sales and the output, WorkCenter and Employee only the labor time, so the ratio of hours to output is meaningful for the whole plant over a period.

Steps 8 to 13: compare, chart and finish the package

8. Add the trailing ratio as a visual calculation
9. Filter the table’s axis
10. Add the KPI card
11. Chart the work centers and the overtime share
12. Add the About page and the navigation
13. Add the plant test

The KPI card sets the latest trailing figure beside the first year’s level, the KPI with its comparison, under a title that says the latest month is provisional; without its year filter it is blank, because its window would end in the empty last year of the Date table. Step 11 stops the work-center and job-title charts from filtering the KPI, and the report is saved on the About page, the page it then opens on when no landing page is set. Test 5 returns the yearly ratios of Chapter 11’s view, which shows that the model reproduces them, not that the ratio is a complete measure of labor performance.

KEEPFILTERS lets the two filters combine

Manufacturing Hours =
CALCULATE (
    SUM ( LaborTimeEntry[RegularHours] )
        + SUM ( LaborTimeEntry[OvertimeHours] ),
    KEEPFILTERS (
        LaborTimeEntry[LaborType] <> "NonManufacturing" )
)

Direct Hours =
CALCULATE ( [Manufacturing Hours],
    LaborTimeEntry[LaborType] = "Direct Manufacturing" )

Without KEEPFILTERS, Direct Hours would return every manufacturing hour. A filter argument replaces any filter on the same column, as Chapter 14 explained, so the filter inside Manufacturing Hours would override Direct Hours’ filter on LaborType. With KEEPFILTERS, the two filters combine and Direct Hours returns direct time only.

On a filtered axis, the visual calculation drifts

Figure 15.10 · The Trailing Ratio as a Measure and as a Visual Calculation (detail)

On a table that starts at the beginning of the data, the visual calculation of two twelve-month moving averages agrees with the measure. On one that starts a year later, it averages one month in the first month, two in the second, and agrees with the measure again only in the twelfth. The measure, whose DATESINPERIOD window reaches into the model, is the figure the production manager relies on.

The trailing ratio rose in every year

Figure 15.11 · The Plant Page (detail)

The plant recorded more hours for each standard hour of output in every successive year, the trend Part III explained. The card’s title says that December 2026 is provisional, because direct time and output run to December 11, 2026, and indirect time to December 4, 2026. Most direct hours of the latest year were recorded at Packing, the work center Chapter 11 found far above its planned load, and the (Blank) work center holds the few direct lines without an operation. The bar chart does not filter the card or the line: one work center’s hours over the whole plant’s output would describe neither.

Overtime share rose across all three job titles

Figure 15.11 · The Plant Page (detail)

The overtime share rose by about two-thirds or more from the first year to the latest for each of the three manufacturing job titles, the plant-wide pattern of the surge days that Chapter 12 found.

Checkpoint

Before you hand the package to its readers, confirm the following:

• LaborTimeEntry and ProductionCompletionLine relate to their dimensions, and Test 5 returns Chapter 11’s yearly ratios.
• The trailing measure and the visual calculation agree on the full axis and differ on the filtered one, for the reason Step 9 gave.
• The report opens on the About page, the navigator reaches every visible page and lists none of the hidden ones, and the detail pages are hidden.
• Clicking a work center in the bar chart leaves the card and the line chart unchanged.

The package now answers three readers’ questions on three pages, each with its comparison and its detail, and documents itself on the first. Its control totals are tested against the earlier Parts, which is what makes it fit to be sent every month.

15.9 What-If Parameters and Forecasts

A what-if parameter turns an assumption into a slider

• Modeling > New parameter > Numeric range creates a slider and a value
• Measures that use the value recalculate with every move
• The Part II case’s break-even lift becomes a page to try discounts on
• The case found no unusual increase in volume to weigh it against

The parameter is a one-column table of the allowed values and a measure that returns the value the reader selected. Chapter 7 used Goal Seek to find the break-even lift, the increase in volume a promotion needs after its discount, and the Part II case found it for each promotion of its proposal. Exercise 15.4 builds the same calculation as a what-if page for the Design Trade promotion.

The forecast and exponential smoothing

• The Analytics pane adds it to a line chart, with a length and an interval
• Exponential smoothing weights recent observations more heavily
• It can detect a seasonal pattern, and be run as a hindcast
• Judge it, as Chapter 7 did, against the same period last year

Microsoft described the method as exponential smoothing when it introduced the forecast in Power View; the Analytics pane’s documentation still points to that article as dated but still relevant. A hindcast runs the forecast on older data to see how it would have predicted the recent past. Chapter 7’s advice applies unchanged: a forecast is judged by how well it would have predicted periods you already know, against simple benchmarks.

Watch out: a forecast learns from any history it gets

• The revenue history begins with a start-up month of half the usual invoices
• Expense months with three pay dates may or may not read as seasonal
• Fit the forecast on data that ends before a quarter you know
• Compare it with that quarter and the benchmarks, and say so on the page

Exercise 15.5 tests the forecast in this way before anyone trusts it.

15.10 Looking Ahead

The package is ready for its readers

• Pages designed around each reader’s question, with KPIs and comparisons
• Drill-through pages and tooltips that reach the detail behind a number
• Budget variances explained by volume, classification, rate and timing
• Navigation, an About page, and tests of each page’s totals

Most of this work was descriptive and diagnostic analytics; the what-if parameter and the forecast are its first predictive and prescriptive tools, each to be judged before it is trusted. Chapter 16 turns to the internal auditor of Chapters 8 and 12, who will monitor the same data for exceptions and test whether the package itself can be relied on. How the package reaches its readers, and how each manager sees only their own cost center, is the subject of the appendix on publishing and security.

Summary

• A report page serves a named reader, with each KPI against its comparison
• Drill-through and tooltip pages put the detail on demand
• A budget beside the ledger needs its variances explained, not just shown
• Visual calculations shape one visual; numbers readers rely on are measures
• A package needs navigation, documentation and tests

What-if parameters and forecasts bring prescriptive and predictive questions into the report; each should be tested against what is already known before a manager relies on it.

Key terms

Bookmark · Budget variance · Conditional formatting · Disconnected table · Drill-through · Exponential smoothing · Field parameter · Key performance indicator (KPI) · Page navigator · Report-page tooltip · Visual calculation · What-if parameter

Every term is defined at the end of the chapter. KPI, drill-through and visual calculation are the ideas the package rests on.

Practice and next steps

• Financial Accounting
  • Exercise 15.1: From the Income Statement to the Entry
  • Exercise 15.2: Month and Year to Date With Visual Calculations
• Managerial Accounting
  • Exercise 15.3: One Chart, Three Measures
  • Exercise 15.4: What Would the Design Trade Promotion Need?
• Auditing
  • Exercise 15.5: Testing the Forecast Before Trusting It
  • Exercise 15.6: Which Work Center Did the Time Belong To?

Next: Chapter 16: Audit Monitoring and Report Assurance

Assign one exercise from each perspective; Exercise 15.1 drills through from the income statement to the entry, Exercise 15.4 builds a what-if page for a promotion, and Exercise 15.5 tests the forecast before trusting it. Chapter 16 turns to audit monitoring and report assurance.

About these slides

These slides accompany Accounting Analytics: An Integrated Approach, licensed under CC BY-SA 4.0. Every figure and table comes from the chapter:

https://aa.accountinganalyticshub.com/chapters/15-management-reports/chapter.html

Students can find each figure and table, with the full explanation, in the online chapter.