6  Descriptive and Diagnostic Analytics in Excel

Learning Objectives

After completing this chapter, you will be able to:

  1. Describe a numeric column, or part of it, with statistical functions such as AVERAGE, MEDIAN, STDEV.S, MIN, MAX, and PERCENTILE.INC, and show the shape of its distribution with a histogram.
  2. Calculate conditional totals, counts, and averages with SUMIFS, COUNTIFS, AVERAGEIFS, MINIFS, and MAXIFS, and test with a control total whether a summary accounts for the whole population it describes.
  3. Build PivotTables and PivotCharts from an Excel Table that summarize data across two or more dimensions, and choose a chart that fits the question.
  4. Create calculated fields for margins and ratios, and explain why a calculated field works on totals rather than on individual rows.
  5. Explain a change between two periods by drilling down, comparing, and decomposing it into price, volume, mix, and cost effects, and communicate the finding.
  6. Build a pre-closing trial balance and a common-size income statement from the general ledger.
Chapter slides View slides Download PowerPoint

Opening Scenario

InvoiceLines is ready, and the controller has three requests for the quarterly management meeting. First, she wants a profile of what Charles River billed in fiscal 2026: how many invoice lines, how large they were, how widely they varied, and a total that ties to the figures she already trusts. Second, she wants revenue and gross margin by product line and quarter, so that the management team can see how the Furniture decline compares with the company’s other lines, together with the year’s income statement from the ledger. Third, and most important, she wants the answer to the question she asked in your first week: why did the Furniture margin fall from the third quarter to the fourth?

The earlier chapters gave you a clean, reconciled table and a clue, the Furniture Seasonal Promotion, whose orders were still being invoiced after it ended. This chapter gives you the tools to describe what happened and then to explain why: statistical functions, conditional aggregation, PivotTables, and a margin bridge that breaks a change into defined components.

6.1 Descriptive and Diagnostic Analytics in Accounting

Chapter 1 introduced four types of analytics. Descriptive analytics answers the question “what happened?” by summarizing historical data into totals, averages, counts, distributions, and comparisons. Diagnostic analytics answers “why did it happen?” by drilling into those summaries, comparing them with an expectation, and breaking a change into its components. In accounting, descriptive analytics is not a warm-up for the real analysis. Comparative financial statements, budget-to-actual reports, aging schedules, and key performance indicators are all descriptive analytics, and they are much of what accountants produce. Diagnostic analytics is what happens when one of those reports shows something that nobody expected and someone has to explain it. The competency framework of the Institute of Management Accountants (IMA) includes a domain dedicated to data analytics, reflecting how central these skills have become to the profession (IMA, 2025).

This chapter moves from one type to the other, using four families of tools. Statistical functions reduce a column to a single number, such as the average or the median billed amount of an invoice line. Conditional aggregation functions, such as SUMIFS, calculate totals, counts, and averages for the rows that meet criteria you define. PivotTables summarize data across two or more dimensions at once, such as revenue by product line and quarter. Finally, a margin bridge decomposes the change in a measure between two periods into effects that add up exactly to the change. The first three describe; the last one explains. Along the way, you will chart the results and build the year’s income statement from the general ledger. By the end of the chapter, you will have used all four families of tools to answer the controller’s question.

6.2 Summary Statistics

A statistical function reduces a column of values to one number that describes the column as a whole. No single measure tells the whole story, so analysts combine measures of the center of the data, of its spread, and of its extremes.

6.2.1 Central Tendency

AVERAGE(range) returns the arithmetic mean, the sum of the values divided by their number. It is the most familiar summary measure, but a few very large values pull it upward. MEDIAN(range) returns the middle value when the values are sorted, or the average of the two middle values when their number is even. Because it depends only on position, the median is not affected by how extreme the largest values are.

The gap between the two is informative. A mean well above the median often comes from a distribution skewed to the right, in which most values are moderate and a minority of large values pulls the average up, but it is a clue to look at the distribution, not proof of its shape. Accounting populations are usually shaped this way. For the Charles River invoice lines of fiscal 2026, the average line is about half again as large as the median line, and the histogram in Tutorial 6.1 shows a long tail of large lines. The typical line is smaller than the average suggests, and a relatively small share of large lines carries a large share of the revenue. When you report a “typical” transaction, the median is usually the better measure, and when you report a total or plan a test of dollar amounts, the mean and the large values matter more.

6.2.2 Dispersion and Extremes

The standard deviation measures how widely values are spread around their mean. STDEV.S(range) treats the values as a sample, a subset drawn from a larger population, and divides by the number of values minus one. STDEV.P(range) treats them as the complete population and divides by the number of values. The distinction matters for small samples, such as an audit sample of a few dozen items. A year’s invoice lines are the whole population of that year, which STDEV.P describes, but for thousands of lines the two results differ by a fraction of a percent. This chapter uses STDEV.S, the sample formula, which fits when a year’s lines are read as one sample of what the business might have billed, and the difference between the two is immaterial here. A standard deviation is easiest to interpret relative to the mean: two cost centers with the same average monthly spending are very different to manage if one varies by a few percent from month to month and the other by a third.

MIN(range) and MAX(range) return the smallest and largest values. They are summary measures, but they are also data checks. A negative or zero amount where none should exist, or a maximum far above the rest of the data, is worth a look before any other statistic is trusted. In the fiscal 2026 invoice lines, the smallest line is under two dollars and the largest is not a piece of furniture but a design services engagement, a reminder that the lines mix very different kinds of sales. PERCENTILE.INC(range, k), which you used in Chapter 4 to set the large-line threshold, returns the value below which a share k of the values falls, and the top tenth of the fiscal 2026 lines carries nearly a third of the year’s revenue.

WarningWatch out

Summary statistics can hide what the data looks like. Anscombe (1973) constructed four small data sets that share the same means, standard deviations, and fitted regression line (the kind of line Chapter 7 fits to data) but look completely different when they are plotted. A statistic of a mixed population can also describe none of its members: the average fiscal 2026 invoice line falls between the small Accessories lines and the large design-services lines, and it resembles neither. Before relying on a statistic, sort, filter, or chart the data it summarizes, and calculate the statistic separately for groups that behave differently.

6.2.3 Statistics for Part of a Table

Most questions concern part of a table, such as one fiscal year or one product line. AVERAGEIFS, MINIFS, and MAXIFS accept criteria, as SUMIFS does, but MEDIAN, STDEV.S, and PERCENTILE.INC do not, and Excel has no MEDIANIFS. The FILTER function fills the gap. FILTER(array, include) returns the rows of an array for which a condition is true, and any statistical function can then summarize the result:

=MEDIAN(FILTER(InvoiceLines[LineTotal],InvoiceLines[FiscalYear]=2026))

FILTER is available in Excel for Microsoft 365, Excel 2024, and Excel 2021, so every desktop version of Excel that Microsoft still supports has it. Without its optional third argument, which these formulas leave out, FILTER returns the error #CALC! when no row meets the condition, a signal to check the condition before trusting the result.

6.3 Conditional Aggregation and Control Totals

You have already used SUMIFS and COUNTIFS, in Chapter 4 to count the lines above a threshold and in Chapter 5 to total the Furniture revenue by quarter. The pattern is the same in every function of the family. The first argument of SUMIFS, AVERAGEIFS, MINIFS, and MAXIFS is the range to aggregate, and COUNTIFS, which only counts, has none. Pairs of arguments follow, each a criteria range and a criterion, and a row is included only when it meets every criterion. A comparison is written as text joined to a value, as in ">"&LargeLineThreshold.

Criteria are easier to maintain in cells than in formulas. The formula

=SUMIFS(InvoiceLines[LineTotal],InvoiceLines[ItemGroup],$A12,InvoiceLines[FiscalYear],ReportYear)

takes the item group from column A of its own row and the fiscal year from a named cell. Copied down a column of item groups, it returns each group’s revenue, and changing ReportYear updates every row. The mixed reference $A12, which you learned in Chapter 4, keeps the formula pointed at column A when it is copied.

Conditional aggregation has one dangerous property: it fails silently. A SUMIFS or COUNTIFS whose criterion is misspelled, such as “Furnture”, returns zero rather than an error, and a list of categories typed by hand can simply leave one out. AVERAGEIFS is the exception, returning #DIV/0! when no row meets its criteria, which at least shows that something is wrong. The defense is a control total, an independently calculated total that the parts of a summary must add up to. You have used control totals since Chapter 4, when you compared the sum of the invoice lines with the sum of the invoice subtotals. In a summary by category, the control total is the total of the whole population for the same criteria, and the difference between it and the sum of the categories must be zero. Auditors apply the same discipline when they foot a schedule, adding its columns, and cross-foot it, adding its rows, before relying on it.

6.4 Charting Descriptive Results

A table of statistics describes a distribution in numbers. A chart shows its shape, and a well-chosen chart lets a reader see a trend, a comparison, or an outlier at a glance. The Watch out box above makes the same point from the other side: statistics that look alike can describe data that look nothing alike. Excel’s charts are on the Insert tab, and the first decision is which kind of chart answers the question. Table 6.1 matches the common questions of descriptive analysis to the charts that answer them.

Table 6.1: Charts for common descriptive questions
Question Chart Accounting example
How do categories compare? Column or bar chart, sorted by value Revenue by product line
How does a measure change over time? Line chart Monthly revenue or margin
What does a total consist of? Stacked column chart Revenue by segment within each quarter
How are values distributed? Histogram The number of invoice lines in each range of amounts
How do two measures relate? Scatter chart Commission expense against revenue by month
How did one total become another? Waterfall chart The change in margin from one quarter to the next

6.4.1 Histograms

A histogram divides the range of a measure into bins of equal width, such as line totals from $0 to $1,000, from $1,000 to $2,000, and so on, and draws a bar for the number of values in each bin. It is the chart that shows the shape of a distribution, such as the right skew of the invoice lines. Excel draws one directly from a column of values with Insert > Insert Statistic Chart > Histogram, and the bin width can be set on the horizontal axis with Format Axis (Microsoft, 2026b). That chart counts every value in the range it is given, however. To count only the lines of one fiscal year, you can instead build a frequency table with COUNTIFS, one row for each bin, and chart it as a column chart with no gap between the bars, which is what Tutorial 6.1 does.

6.4.2 PivotCharts

A PivotChart is a chart built on a PivotTable. It shows the PivotTable’s current layout, so filtering the PivotTable, or clicking a slicer connected to it, filters the chart too, and a refresh updates both. Insert a PivotChart from PivotTable Analyze > PivotChart, or build a PivotTable and its chart in one step with Insert > PivotChart.

6.4.3 Designing a Chart That Tells the Truth

A few rules keep a chart honest and easy to read. The length of a bar represents its value, so the value axis of a column or bar chart should start at zero. When a line chart or a waterfall chart needs a truncated axis to make small differences visible, as the bridge in Tutorial 6.3 does, say so in the title or a note. Sort bars by value unless their categories have a natural order, such as months. Label the chart with what it shows and what it means, remove decoration such as 3-D effects and heavy gridlines, and use color to mean something, never as the only way a message is carried, for the reasons Chapter 4 gave for conditional formatting.

6.5 Guided Tutorial 6.1: Profiling Fiscal 2026 and Checking the Total

Context and objective. In this tutorial, you will build the first part of the controller’s request: a statistical profile of the invoice lines billed in fiscal 2026 and a summary of revenue by product line. You will store the fiscal year in a named cell, so that one change updates every number, calculate statistics with and without criteria, and compare the product lines with a control total. The comparison will find something the product-line list leaves out. Finally, you will chart the distribution of the line totals as a histogram.

Prerequisites. You need the analysis workbook from Chapter 5, with the InvoiceLines Table loaded by Power Query and the Parameters worksheet from Chapter 4. If you start here, download Charles River Furniture Analysis.xlsx as it stands at the end of Chapter 5 from Companion Files, and point its queries at your copy of CharlesRiver.xlsx as that page explains.

Step-by-step instructions.

Step 1. Add the report year as a parameter.

  1. On the Parameters worksheet, type Report year in A5, 2026 in B5, and Fiscal year shown on the Profile worksheet in C5.
  2. Select B5, type ReportYear in the Name Box, and press Enter.
  3. Format B5 in blue font, the convention for input cells you adopted in Chapter 4.

The report year is now a named input, like the threshold percentile above it, so every formula in this tutorial can refer to ReportYear instead of a typed year.

Step 2. Set up the Profile worksheet.

  1. Add a worksheet named Profile after the Analysis worksheet.
  2. Type Fiscal year in A1, and enter =ReportYear in B1.
  3. In A2 through A9, type the labels Invoice lines, Line total, Average line, Median line, Standard deviation, Smallest line, Largest line, and 90th percentile.

B1 shows 2026, the value of the parameter, and the labels in column A name the measures that the next two steps calculate.

Step 3. Calculate the measures that accept criteria.

  1. In B2, count the year’s invoice lines with =COUNTIFS(InvoiceLines[FiscalYear],ReportYear).

  2. In B3, enter the year’s total, which is the control total for the rest of the tutorial:

    =SUMIFS(InvoiceLines[LineTotal],InvoiceLines[FiscalYear],ReportYear)

  3. In B4, enter the same formula with AVERAGEIFS in place of SUMIFS.

  4. In B7 and B8, enter the same formula with MINIFS and MAXIFS.

B2 counts several thousand lines, and B3 holds the year’s revenue from invoice lines, the total that every later summary in this tutorial must agree with.

Step 4. Calculate the measures that need FILTER. MEDIAN, STDEV.S, and PERCENTILE.INC accept no criteria, so FILTER supplies the year’s line totals to them.

  1. In B5, enter:

    =MEDIAN(FILTER(InvoiceLines[LineTotal],InvoiceLines[FiscalYear]=ReportYear))

  2. In B6, enter the same formula with STDEV.S in place of MEDIAN.

  3. In B9, enter:

    =PERCENTILE.INC(FILTER(InvoiceLines[LineTotal],InvoiceLines[FiscalYear]=ReportYear),ThresholdPercentile)

  4. Format B3 through B9 as numbers with two decimals and a thousands separator.

Compare the average in B4 with the median in B5. The average is about half again as large as the median, and the standard deviation is almost as large as the average. The lines are widely spread, and the gap between the average and the median suggests a right skew, a long tail of large lines, which the histogram of Step 9 will show.

Step 5. Look at the extremes.

  1. On the InvoiceLines worksheet, filter the FiscalYear column to 2026, and sort the LineTotal column from largest to smallest.
  2. Read the ItemGroup, ItemCode, and Quantity of the first rows.
  3. InvoiceLines carries no item names, so look up the ItemCode of the first row in the ItemCode column of the Item worksheet to read its ItemName.
  4. Clear the sort and the filter.

The largest line of the year is not a piece of furniture. It is a design service, Specification Development, billed for well over a hundred hours, and the next largest lines are desks and console tables. The lines combine two very different kinds of sales, products and professional services, which is worth keeping in mind whenever a single statistic describes them all.

Step 6. Total the product lines. The controller’s report lists four product lines. Summarize them and compare their total with the control total.

  1. In A11 through D11, type the headings Item group, Lines, Revenue, and Average line.

  2. In A12 through A15, type Furniture, Lighting, Textiles, and Accessories.

  3. In B12 and C12, enter these two formulas, the group’s number of lines and its revenue:

    =COUNTIFS(InvoiceLines[ItemGroup],$A12,InvoiceLines[FiscalYear],ReportYear)
=SUMIFS(InvoiceLines[LineTotal],InvoiceLines[ItemGroup],$A12,InvoiceLines[FiscalYear],ReportYear)

  4. In D12, enter =C12/B12. Copy B12:D12 and paste it into B13:D15.

  5. Type Total of groups in A16, and enter =SUM(B12:B15) in B16 and =SUM(C12:C15) in C16.

  6. Type Difference from control total in A17, and enter =B16-B2 in B17 and =ROUND(C16-B3,2) in C17.

The ROUND in C17 compares the revenue to the cent. Adding thousands of amounts can leave a residue far smaller than a cent, which is not exactly zero in binary arithmetic, and the rounding makes such a difference read as zero.

The differences are not zero. The four product lines account for fewer lines and less revenue than the year’s total, so the summary is incomplete.

Step 7. Find and add the missing group.

  1. On the InvoiceLines worksheet, open the filter list of the ItemGroup column and read the values it offers. There are five item groups, not four.
  2. On the Profile worksheet, right-click the header of row 16 and choose Insert. Type Services in the new A16, then copy B15:D15 and paste it into B16:D16.
  3. The totals are now in row 17. Click B17 and C17: their SUM formulas still end at row 15, because Excel does not extend a range when you insert a row just below it. Change them to =SUM(B12:B16) and =SUM(C12:C16).
  4. Check that both differences, now in row 18, are zero.

Services is the design services line, billed by the hour. It is a revenue line with its own account, but not a product, which is why a list of product lines leaves it out. The control total found the gap in a single subtraction, and the edited SUM formulas show how easily a summary can miss a row even after the missing category is added. Figure 6.1 shows the completed Profile worksheet.

A mock of the Profile worksheet. Rows 1 through 9 show the fiscal year, 2026, and the count, total, average, median, standard deviation, smallest and largest line, and 90th percentile of the year's invoice line totals, with the median cell selected and its MEDIAN and FILTER formula in the formula bar. Rows 11 through 18 show the lines, revenue, and average line of each item group, with the Services row added below Furniture, Lighting, Textiles, and Accessories, a total of the groups, and a difference from the control total of zero. The Services row and the difference row are outlined.
Figure 6.1: A Profile of Fiscal 2026 Invoice Lines with a Control Total.

Step 8. Count the lines in $1,000 bins. A frequency table counts the lines in each range of amounts, the bins of the histogram you draw next.

  1. On the Profile worksheet, type From in F1 and Lines in G1.

  2. Type 0 in F2 and 1000 in F3, select F2:F3, and drag the fill handle down to F17, so that F2:F17 hold the lower bounds from 0 to 15,000.

  3. In G2, enter this formula, which counts the year’s lines from the bound in its row up to the next bound, and copy it down to G16:

    =COUNTIFS(InvoiceLines[FiscalYear],ReportYear,InvoiceLines[LineTotal],">="&F2,InvoiceLines[LineTotal],"<"&F3)

  4. In G17, the last bin, which has no upper bound, enter:

    =COUNTIFS(InvoiceLines[FiscalYear],ReportYear,InvoiceLines[LineTotal],">="&F17)

  5. Type Total in F18, and enter =SUM(G2:G17) in G18.

G18 equals the number of lines in B2, so every line of the year falls in exactly one bin. The first bin holds the most lines, and the counts generally shrink as the amounts grow. The last bin is taller than the two before it because it has no upper bound: it gathers every line of $15,000 or more.

Step 9. Chart the bins as a histogram.

  1. Select G1:G17 and choose Insert > Insert Column or Bar Chart > Clustered Column.
  2. Right-click the chart, choose Select Data, click Edit under Horizontal (Category) Axis Labels, select F2:F17, and click OK twice.
  3. Right-click a bar, choose Format Data Series, and set Gap Width to 0%.
  4. Choose Chart Design > Add Chart Element > Axis Titles, and add the titles Line total, lower bound of the $1,000 bin under the chart and Lines beside it, so that the chart says what its bars count.

The chart in Figure 6.2 rises to its highest bar at the left and trails off to the right: most lines are under a few thousand dollars, and a thin tail reaches far beyond them. That is the right skew that the average and the median suggested in Step 4, now visible at a glance. Excel labels each bar with its lower bound from column F, so your last bar reads 15000, the open-ended bin that the figure labels 15k+.

A mock of columns F and G of the Profile worksheet and a chart beside them. The table lists the lower bound of each 1,000-dollar bin, from 0 to 15,000, with the number of fiscal 2026 invoice lines in each and a total that equals the year's line count; the formula bar shows the COUNTIFS formula of the first bin. The chart is a histogram of the counts: the first bins are the tallest, and the bars generally fall toward the right into a long, thin tail of large lines, ending in an open-ended last bin, labeled 15k+, for lines of 15,000 dollars or more.
Figure 6.2: The Distribution of Fiscal 2026 Line Totals.

Step 10. Document the profile.

  1. On the Documentation worksheet, add ReportYear (Parameters!B5) to the Parameters entry and the Profile worksheet, with its histogram, to the Worksheets entry.
  2. Save the workbook.

The Documentation worksheet now records the second parameter and the new worksheet, so a reviewer can see which year the profile describes and how to change it.

Checkpoint. Check that:

  • the average line is well above the median line, and you can explain why;
  • the Profile worksheet lists five item groups, and both differences from the control total are zero;
  • the bins of the histogram add up to the year’s line count, and the chart shows a right skew;
  • changing ReportYear on the Parameters worksheet to 2025 updates every number on the Profile worksheet, and changing it back to 2026 restores them.

The profile gives the controller her first answer: how much Charles River billed in fiscal 2026, how the lines are distributed, and how the revenue divides among the five item groups. The next step is to see how each group moved from quarter to quarter.

6.6 PivotTables

The summaries in Tutorial 6.1 work, but you had to design the layout, type the categories, and write a formula for every cell. A PivotTable builds the layout for you. You choose which fields to place in its rows and columns and which to summarize, and Excel creates a cross-tabulation, a summary with a cell for every combination of the row and column items and totals for every row and column. Domino et al. (2021) built a teaching case around them, emphasizing how quickly they condense a large, complex data set into reports that address managers’ questions. That speed has a cost: a PivotTable is as easy to misread as it is to build, so this section covers both how to build one and how to check it.

6.6.1 Creating a PivotTable

Select any cell in an Excel Table and choose Insert > PivotTable, and then From Table/Range if Excel offers a choice. The dialog shows the Table’s name as the source. A Table is the right source because the PivotTable then covers every row the Table holds, including rows added the next time the query is refreshed. Place the PivotTable on a new worksheet, so that it cannot overwrite anything, and leave Add this data to the Data Model unchecked, because a PivotTable built on the Data Model has no calculated fields.

Excel creates an empty PivotTable and opens the PivotTable Fields pane, which lists every column of the Table as a field. Below the list are four areas, and the field you drag into each area plays the role shown in Figure 6.3. The Rows and Columns areas define the categories down the side and across the top. The Values area holds the fields to summarize. The Filters area limits the whole PivotTable to the rows that meet a condition, such as a single fiscal year, without changing the source.

A diagram of the PivotTable Fields pane next to the PivotTable it produces from the InvoiceLines Table. In the pane, the LineTotal, ItemGroup, FiscalYear, and Period fields are checked, and the four areas hold FiscalYear in Filters, Period in Columns, ItemGroup in Rows, and Sum of LineTotal in Values, each marked with a number from 1 to 4. The PivotTable shows the FiscalYear filter set to 2026, the four quarters of 2026 and a Grand Total across the top, the five item groups and a Grand Total down the side, and the sum of the line totals in every cell. The same numbers mark the parts of the PivotTable that each area produces, and a legend explains them.
Figure 6.3: How the PivotTable Fields Areas Build a PivotTable.

6.6.2 Summarizing Values

A numeric field in the Values area is summed by default, and a text field is counted. To change the calculation, click the field in the Values area and choose Value Field Settings, where you can choose Count, Average, Max, Min, and others, give the field a custom name, and set its number format with the Number Format button. Counting an ID column, such as SalesInvoiceLineID, counts rows, which is the PivotTable equivalent of COUNTIFS. When you place more than one field in the Values area, Excel adds a field called Σ Values to the Columns area, and dragging it to the Rows area stacks the measures vertically instead, which is often easier to read.

6.6.3 Grouping by Period

PivotTables can group a date field into months, quarters, and years: right-click a date in the PivotTable, choose Group, and select the units. Date grouping follows the calendar, and it requires every value in the field to be a real date, so a single text date, the problem Chapter 5 warned about, prevents it. For Charles River, whose fiscal year is the calendar year, calendar quarters and fiscal quarters coincide. InvoiceLines already carries FiscalYear, FiscalQuarter, and Period columns, however, and using them keeps the PivotTable consistent with every other analysis of the table. For an organization whose fiscal year does not follow the calendar, period columns calculated during preparation are the only reliable choice.

6.6.4 Calculated Fields

A calculated field is a field you define inside a PivotTable with a formula that uses other fields. It is how a PivotTable shows a measure that is not a column of the source, such as a margin percentage. Choose PivotTable Analyze > Fields, Items, & Sets > Calculated Field, give the field a name, and write its formula using the field names. The essential point is how the formula is evaluated. Microsoft’s documentation explains that a calculated field’s formula operates on the sums of the fields it uses, not on individual records (Microsoft, 2026a). For each cell of the PivotTable, Excel first sums each field over the rows behind the cell and then applies the formula to those sums. A margin percentage defined as =(LineTotal-StdCostAmount)/LineTotal is therefore the total margin divided by the total revenue of each cell, which is exactly the weighted margin an accountant wants.

The same rule explains why a margin needs a cost column that is already extended. InvoiceLines carries each item’s StandardCost, the cost of one unit, and summing a unit cost over many lines produces a meaningless number. Tutorial 6.2 therefore adds StdCostAmount, the quantity multiplied by the standard cost, to the query, and the calculated field uses that. The result is a margin on the invoice lines at standard cost, a related but different measure from the Part I case’s margin on the posted accounts, and Tutorial 6.2 compares the two.

WarningWatch out

Because a calculated field works on sums, a formula that multiplies two fields gives the wrong answer. A calculated field defined as =Quantity*UnitPrice multiplies the total quantity by the total of the unit prices, which is not revenue at any level of the PivotTable. Multiply values row by row in the source, as a custom column in Power Query or a calculated column in the Table, and let the PivotTable sum the product. Ratios and differences of totals, such as margins and averages per unit, are what calculated fields do well.

6.6.5 Show Values As

The same value field can be displayed in different ways without changing the numbers behind it. Right-click a value in the PivotTable, choose Show Values As, and pick a calculation. Table 6.2 lists the options accountants use most, with the descriptions Microsoft gives for them (Microsoft, 2026c), and Figure 6.4 shows one PivotTable displayed three ways.

Table 6.2: Show Values As options for accounting summaries
Show Values As option What it displays Accounting use
% of Grand Total Each value as a share of the grand total Each segment and product line’s share of the year’s revenue
% of Column Total Each value as a share of its column’s total The product mix within each quarter
% of Row Total Each value as a share of its row’s total How a product line’s revenue is spread across the quarters
Difference From The difference from a base item, such as the previous period The dollar change from one quarter to the next
% Difference From The percentage difference from a base item Growth or decline from the previous quarter or year
Running Total In A running total across the base field Year-to-date revenue by month
Three small PivotTables side by side, each showing the five item groups and a Grand Total for the third and fourth quarters of fiscal 2026. The first shows Sum of LineTotal with no calculation, the second shows each group's share of the quarter with % of Column Total, and the third shows the change from the previous quarter with % Difference From. The Furniture cell for the fourth quarter is outlined in all three, showing a lower revenue, a smaller share of the quarter, and a decline from the third quarter.
Figure 6.4: One PivotTable Shown Three Ways with Show Values As.

6.6.6 Refreshing and Referencing a PivotTable

A PivotTable summarizes a copy of its source, called the pivot cache, so it usually does not change when the source changes. After a query refresh, refresh the PivotTable too, once the query has finished loading, and check its fields, filters, and totals. Data > Refresh All refreshes both. A formula that refers to a PivotTable should use GETPIVOTDATA, which Excel writes for you when you type an equal sign and click a value in the PivotTable. GETPIVOTDATA identifies the value by its field and items, such as the Sum of LineTotal for Period 2026-Q4, rather than by its cell address, so it keeps returning the right value when the PivotTable’s layout changes. A change to the PivotTable’s filters, however, changes the value it returns, because the formula reads whatever the PivotTable shows. Slicers, which you used on a Table in Chapter 4, filter PivotTables in the same way, and one slicer can control several PivotTables built from the same source.

WarningWatch out

A PivotTable reports what is in its source, including rows that should not be in the analysis. In the Part I case, you found that a summary of the general ledger by quarter shows nonsensical fourth-quarter revenue and cost of goods sold, because the year-end closing entry debits the sales revenue accounts and credits the cost of goods sold accounts on the last day of the year. Before you summarize GLEntry by period, filter out the JournalEntry rows of the closing entries, or place SourceDocumentType in the Filters area and select the source documents your question concerns. The same caution applies to any source: know which rows it contains before you trust its totals.

TipIn Practice

A common task in the monthly close is a flux analysis, in which the accounting team compares each account’s balance with the previous period and with the same period of the previous year, and explains every change above a threshold. A PivotTable of the ledger by account and period, with a Difference From or % Difference From value field and a conditional formatting rule for the threshold, produces the list of changes to explain in a few minutes. The explanations still require someone who knows the business, and they are what reviewers and auditors read.

6.7 Guided Tutorial 6.2: Revenue and Margin by Product Line and Quarter

Context and objective. In this tutorial, you will answer the controller’s second request with PivotTables. You will add the extended standard cost to the InvoiceLines query, summarize fiscal 2026 revenue by item group and quarter, and reconcile the result with the control total from Tutorial 6.1. You will then add the gross margin at standard cost as a calculated field, read the same revenue as shares and as changes, compare the customer segments with the previous year, and chart the margins with a PivotChart.

Prerequisites. You need the analysis workbook from Tutorial 6.1, with the Profile worksheet and its control total. If you start here, download Charles River Furniture Analysis.xlsx as it stands at the end of Tutorial 6.1 from Companion Files, and point its queries at your copy of CharlesRiver.xlsx as that page explains.

Step-by-step instructions.

Step 1. Add the extended standard cost to the query. A margin needs the cost of each line, not the cost of one unit, so calculate it in the query.

  1. Choose Data > Get Data > Launch Power Query Editor, and select the InvoiceLines query.
  2. Choose Add Column > Custom Column. Type StdCostAmount in New column name and [Quantity] * [StandardCost] in Custom column formula, and click OK.
  3. Set the new column’s data type to Decimal Number with the icon at the left of its header.
  4. Choose Home > Close & Load.

The InvoiceLines Table now ends with a StdCostAmount column, the standard cost of each line’s quantity, which can be summed like revenue.

Step 2. Create the PivotTable.

  1. Select a cell in the InvoiceLines Table and choose Insert > PivotTable, and then From Table/Range if Excel offers a choice. Confirm that the source is the InvoiceLines Table, choose New Worksheet, leave Add this data to the Data Model unchecked, and click OK. Rename the worksheet RevenuePivot. A PivotTable built on the Data Model has no Calculated Field command, which Step 3 needs.
  2. In the PivotTable Fields pane, drag FiscalYear to Filters, Period to Columns, ItemGroup to Rows, and LineTotal to Values.
  3. Open the FiscalYear filter in cell B1 and select 2026.
  4. In the Values area, click Sum of LineTotal, choose Value Field Settings > Number Format, and choose Number with two decimals and a thousands separator.

Compare the grand total at the bottom right of the PivotTable with the control total in B3 of the Profile worksheet, and each item group’s Grand Total with its revenue in column C of the Profile worksheet. They agree to the cent, as two summaries of the same Table and year should, and the PivotTable adds the quarterly detail. Its FiscalYear filter is a setting of its own: changing ReportYear on the Parameters worksheet does not change it.

Step 3. Add the margin as a calculated field.

  1. With a cell of the PivotTable selected, choose PivotTable Analyze > Fields, Items, & Sets > Calculated Field.
  2. Type MarginPct in Name and =(LineTotal-StdCostAmount)/LineTotal in Formula, as Figure 6.5 shows. Click Add, and then OK. Excel adds the new field to the Values area as Sum of MarginPct.
  3. Format Sum of MarginPct as a percentage with two decimals, through Value Field Settings > Number Format.
  4. In the Columns area, Excel has added a field named Σ Values. Drag it to the Rows area, below ItemGroup, so that each group shows its revenue and its margin on two rows.
A mock of Excel's Insert Calculated Field dialog. The Name box holds MarginPct and the Formula box holds the formula that subtracts StdCostAmount from LineTotal and divides the result by LineTotal. The Fields list shows some of the InvoiceLines fields, with LineTotal and StdCostAmount highlighted, and the dialog has Add, Delete, Insert Field, OK, and Close buttons. The name and the formula are outlined.
Figure 6.5: The Insert Calculated Field Dialog.

The label Sum of MarginPct looks odd for a percentage, but it is accurate: Excel sums LineTotal and StdCostAmount for each cell and then applies the formula, so every cell shows the margin of its total revenue.

Step 4. Read the margins. Figure 6.6 shows the result. The Furniture margin is lower than that of the other product lines in every quarter, and it falls in the fourth quarter, while the margins of Lighting, Textiles, and Accessories barely move. The margin of the Part I case came from the posted Furniture revenue and cost of goods sold accounts, without the closing entry. Its revenue equals the invoice lines’ revenue in these quarters, but its cost is the standard cost of the goods shipped in each quarter less the cost of goods that customers returned. This margin uses the standard cost of the quantities invoiced and leaves returns out, so the two measures differ slightly in level but show the same decline.

A mock of the RevenuePivot worksheet. The PivotTable is filtered to fiscal year 2026, with the four quarters and a Grand Total across the top. Down the side, each of the five item groups shows two rows, Sum of LineTotal and Sum of MarginPct, followed by the totals of both measures. The Furniture margin row is outlined, showing a lower margin in the fourth quarter than in the third, and the Services margin row is outlined, showing 100 percent in every quarter.
Figure 6.6: Revenue and Margin at Standard Cost by Item Group and Quarter.

Services shows a margin of 100 percent. That is not a finding about design services: the Item Table carries no standard cost for them, because their cost is the time of the design staff, which the ledger records through payroll as a salaries expense rather than as a cost of the item. A margin at standard cost is meaningful only for the product lines, and a report that includes Services should say so or leave its margin out.

Step 5. Read the revenue as shares and as changes.

  1. Right-click any value in a Sum of LineTotal row and choose Show Values As > % of Column Total. Each group’s revenue now appears as its share of the quarter.
  2. Right-click a value again and choose Show Values As > % Difference From. Choose Period as the Base field and (previous) as the Base item, and click OK. Each value now shows the change from the previous quarter, and the first quarter is blank because the PivotTable has no earlier period to compare it with.
  3. Right-click a value and choose Show Values As > No Calculation to restore the dollar amounts.

In the fourth quarter, Furniture’s share of revenue fell, and its revenue declined by a few percent from the third quarter. Lighting’s revenue also declined, but its margin did not, which points to prices or costs within Furniture rather than to demand across the business. Figure 6.4 shows the same three views.

Step 6. Compare the customer segments with the previous year.

  1. Select a cell in the InvoiceLines Table and insert a second PivotTable on a new worksheet named SegmentPivot.
  2. Drag CustomerSegment to Rows, FiscalYear to Columns, and LineTotal to Values twice.
  3. Open the Column Labels filter, clear 2024, and click OK, so that the PivotTable compares 2025 with 2026.
  4. Click the second value field in the Values area and choose Value Field Settings. Type Change from previous year in Custom Name. On the Show Values As tab, choose % Difference From, with FiscalYear as the Base field and (previous) as the Base item, and click OK.
  5. Format the first value field as a number with two decimals.

Wholesale, the largest segment, with about half of the year’s revenue, grew from 2025 to 2026. The Strategic and Design Trade segments declined, Small Business grew, and total revenue rose by about one percent. The segment view does not explain the Furniture margin, but it shows where the company’s revenue comes from and how that changed, which the controller will want for the management meeting.

Step 7. Chart the margins with a PivotChart. A line chart of the margins shows the Furniture decline in one picture.

  1. Select a cell in the InvoiceLines Table and choose Insert > PivotChart, place it on a new worksheet, and rename the worksheet MarginChart.
  2. Drag FiscalYear to Filters and select 2026, Period to Axis (Categories), ItemGroup to Legend (Series), and MarginPct to Values. MarginPct is in the field list because PivotTables built from the same Table share their calculated fields; if it is missing, create it again as in Step 3.
  3. Open the ItemGroup filter on the chart and clear Services, whose 100 percent margin would squeeze the other lines together.
  4. Right-click the chart, choose Change Chart Type, and choose Line with Markers.

The chart shows four lines. The Furniture line is the lowest in every quarter and has by far the largest fall in the fourth quarter, while the other lines stay close to level. Because the chart is built on a PivotTable, changing its FiscalYear filter redraws it for another year.

Step 8. Refresh and document.

  1. Choose Data > Refresh All, and confirm that the totals on the PivotTable worksheets are unchanged. If a PivotTable does not show a column you have just added to the query, wait until the query has finished loading, and then choose Refresh All again. With background refresh, a setting that is on by default and lets you keep working while a query runs, Excel can refresh a PivotTable before its query has finished.
  2. On the Documentation worksheet, add the StdCostAmount custom column to the Query steps entry, the RevenuePivot, SegmentPivot, and MarginChart worksheets to the Worksheets entry, and a new entry, Calculated fields, with the MarginPct formula.
  3. Save the workbook.

The workbook now records how each PivotTable and chart was built, and a refresh updates them all from the query.

Checkpoint. Check that:

  • the grand total of the RevenuePivot PivotTable equals the control total on the Profile worksheet, and each group’s Grand Total equals its revenue there;
  • the Furniture margin falls from 2026-Q3 to 2026-Q4, while the margins of the other product lines change very little;
  • you can explain why Services shows a margin of 100 percent;
  • the SegmentPivot PivotTable shows each segment’s revenue in 2025 and 2026 and its change from the previous year;
  • the MarginChart shows the Furniture margin below the others and falling in the fourth quarter.

The PivotTable has described the problem precisely. Furniture has by far the largest fall in margin in the fourth quarter, the margins of the other product lines barely move, and total revenue for the year grew. Why the Furniture margin fell is a diagnostic question, and it needs the tools of the next section.

6.8 From the Ledger to the Financial Statements

The PivotTables in this chapter start from the invoice lines, the detail behind revenue. The financial statements start from the general ledger. As Chapter 2 explained, a trial balance lists every account with its balance at a date, and the statements are prepared from it. Chapter 3 showed that the Charles River database has no trial balance table: you produce one by grouping the GLEntry rows by account and summing their debits and credits. This section shows how to do that in Excel and how to turn the result into an income statement.

6.8.1 Building a Trial Balance with Power Query

GLEntry holds hundreds of thousands of rows, which is more than you want in a worksheet only to add them up. Power Query’s Group By command, on the Transform tab of the Power Query Editor, summarizes the rows before they are loaded. You choose one or more columns to group by, such as AccountID, and one or more aggregations, such as the sum of Debit and the sum of Credit. The Advanced option of the Group By dialog allows several aggregations at once. The result has one row for each account, which a merge with the Account Table then labels with the account’s number, name, type, and subtype.

Two choices decide what the trial balance shows. The first is the cutoff: only the rows posted on or before the reporting date belong in it. The second is the closing entries. At the end of each year, closing entries move the balances of the revenue and expense accounts to retained earnings, which leaves those accounts at zero. A trial balance prepared before the current year’s closing entries, a pre-closing trial balance, therefore shows the current year’s revenue and expenses, because the earlier years’ closing entries have already cleared the earlier years’ amounts. For Charles River at the end of fiscal 2026, that means every row posted through December 31, 2026, except the two 2026 closing entries: JE-2026-000296, which you met in the Part I case, and JE-2026-000297, which moves the year’s net income to retained earnings. The balance sheet accounts, which are never closed, carry their balances forward from the opening balance entry of January 2024.

In the loaded trial balance, a calculated column subtracts each account’s credits from its debits to give its balance, and two more columns place a positive balance in a DebitBalance column and a negative one in a CreditBalance column. The two columns must have equal totals, which is the trial balance’s control total.

6.8.2 From the Trial Balance to the Income Statement

The Account Table’s AccountSubType column groups the accounts into the lines of the statements: Operating Revenue, Contra Revenue, COGS, Operating Expense, and so on. SUMIFS on the trial balance then produces each line of the income statement, with the sign reversed for revenue, whose normal balance is a credit:

=-SUMIFS(TrialBalance[Balance],TrialBalance[AccountSubType],"Operating Revenue")

A common-size statement expresses every line as a percentage of net revenue, which makes the structure of the statement easy to read and to compare with another period or another company: gross margin, operating expenses, and net income each become a share of every dollar of revenue. Comparing the same lines across years is called horizontal analysis, and comparing lines within one year, as a common-size statement does, is called vertical analysis. Figure 6.7 shows the trial balance and the common-size income statement for fiscal 2026. Two checks tie the statement to the ledger. Net income equals the amount that the 2026 closing entry moved to retained earnings, and the balance sheet balances once net income is added to equity. Exercise 6.1 walks you through building both.

A mock of two worksheets. The TrialBalance worksheet holds an Excel Table with one row for each account, showing the account number, name, subtype, and a debit or credit balance, with its first rows for cash, receivables, and inventories and a Total Row in which the debit and credit balances are equal, outlined. Below, the IncomeStatement worksheet shows fiscal 2026 operating revenue, sales returns, net revenue, cost of goods sold, gross margin, operating expenses, operating income, other income and expense, and net income, each with its percentage of net revenue. The formula bar shows the SUMIFS formula for operating revenue, and the net income row is outlined.
Figure 6.7: From the Trial Balance to the Income Statement.
WarningWatch out

A trial balance built from a ledger extract is only as complete as the extract. The Charles River ledger already holds supplier payments dated in fiscal 2027, so a trial balance without a date filter would mix two years. Filter by posting date before you group, and check the result: equal debit and credit balances show that the entries in the extract balance, and comparing net income with an independent figure, the closing entry, tests the year’s result. Neither check shows that nothing is missing. An entry left out of the extract, such as the payment of a payable, would leave the debits equal to the credits and net income unchanged, and, as with the control totals of Tutorial 4.1, Step 10, only evidence from outside the extract could reveal it.

6.9 From Description to Diagnosis

Tutorial 6.2 finds on the invoice lines the decline that the Part I case found on the posted accounts, a Furniture margin that fell from the third quarter of fiscal 2026 to the fourth, and it adds that the other product lines held steady. That is a descriptive finding. The diagnostic question is why, and three moves answer it. The first is to drill down, from a total into its parts. Double-clicking a value in a PivotTable opens a new worksheet with the rows behind it, and moving a more detailed field, such as ProductType, into the Rows area splits a total into its components. The second is to compare, with the previous period, the same period a year earlier, a budget, or a peer group. The third is to decompose the change into components that add up exactly to it, so that each component can be investigated on its own.

6.9.1 Explaining a Change in Margin

Gross margin is revenue minus cost, and revenue is the number of units sold multiplied by the price per unit. Between two periods, the margin of a product line can change for four reasons. More or fewer units can be sold, which is a volume effect. The sales can shift between items that earn different margins, which is a mix effect. The same items can be sold at different prices, which is a price effect. And the same items can cost more or less, which is a cost effect. Management accountants measure these effects against a budget as sales variances, splitting the effect of volume into a sales-quantity variance, from the change in the total number of units, and a sales-mix variance, from the shift between products (Datar & Rajan, 2021). The same logic works with the previous quarter as the benchmark, which is what the controller’s question asks for.

The Charles River data lets you measure price precisely, because every invoice line records the item’s list price as well as the price actually billed. The difference between the two has two parts, which Chapter 4 introduced: the reductions of the segment and customer price lists and approved overrides, and the promotional discounts recorded in the Discount column. Per unit sold, the margin is therefore the list margin, which is the list price minus the standard cost, less the price-list reductions and less the promotional discounts. Table 6.3 turns that identity into a bridge, and every input it needs is a total that a PivotTable can provide.

Table 6.3: The effects of a margin bridge. U is units sold, M the margin per unit, LM the list margin per unit, K the price-list reduction per unit, and D the promotional discount per unit, with 3 and 4 marking the quarters
Effect What it measures Calculation, Q3 to Q4
Volume The change in units, at the third quarter’s margin per unit (U4-U3)*M3
Mix The change in list margin per unit, because different items were sold U4*(LM4-LM3)
Price: price lists The change in price-list reductions per unit -U4*(K4-K3)
Price: promotions The change in promotional discounts per unit -U4*(D4-D3)
Cost The change in each item’s standard cost Zero when no standard cost changes

The effects add up exactly to the fourth quarter’s margin minus the third’s, and checking that they do is the bridge’s control total. The mix effect deserves a word. Because each item’s list price and standard cost stay the same from one quarter to the next, the list margin per unit of a product line can change only when the items sold change, so its change comes only from mix. The two price effects measure the change in the average reduction per unit over all the items sold, so a shift toward items with larger reductions moves them too. A bridge therefore assigns a change to its effects by a stated convention, not by isolating each cause. The cost effect is zero for a related reason. InvoiceLines values every quantity at the item’s standard cost, and each item has a single standard cost, so a margin at standard cost cannot move because of cost. Differences between actual and standard costs are recorded separately in the ledger, in variance accounts such as Manufacturing Variance, when work orders close. If a cost effect were the suspected cause, that is where you would look next.

A decomposition also depends on the level at which it is calculated. The bridge in Table 6.3 treats Furniture as a whole, so its mix effect captures every shift between Furniture items. Calculated product type by product type, the same change divides differently, because each type contains items with different prices and costs, and part of what looks like a change in price or cost at that level is really a change in the items sold within each type. Neither calculation is wrong, but a report should state its level, and Exercise 6.4 asks you to compare the two.

TipIn Practice

Management reports rarely present a variance without an explanation. A typical monthly or quarterly package shows each significant variance, its components, and a short commentary that names the cause and, where one is needed, the action and its owner, such as “Furniture margin 1.5 points below last quarter: promotional discounts; the promotion has ended.” A bridge makes that commentary checkable, because each sentence rests on a number that ties to the total.

6.10 Guided Tutorial 6.3: Explaining the Furniture Margin

Context and objective. In this tutorial, you will answer the controller’s question. You will add the list amount and the promotional discount of each line to the InvoiceLines query, compare the drivers of the Furniture margin in the third and fourth quarters with a PivotTable, and decompose the change in margin into volume, mix, price, and cost effects on a bridge worksheet. You will chart the bridge as a waterfall and drill into the discounts to find the promotions behind them.

Prerequisites. You need the analysis workbook from Tutorial 6.2, with the StdCostAmount column in the InvoiceLines query and the MarginPct calculated field. If you start here, download Charles River Furniture Analysis.xlsx as it stands at the end of Tutorial 6.2 from Companion Files, and point its queries at your copy of CharlesRiver.xlsx as that page explains.

Step-by-step instructions.

Step 1. Add the list amount and the discount to the query. Chapter 4 calculated these amounts as calculated columns on the SalesInvoiceLine Table. The InvoiceLines query starts from the query rather than the Table, so it needs its own versions.

  1. Open the Power Query Editor and select the InvoiceLines query.
  2. Add the two custom columns in Table 6.4 with Add Column > Custom Column, and set both to the Decimal Number type.
  3. Choose Home > Close & Load, and then Data > Refresh All.
Table 6.4: Custom columns added to InvoiceLines for the margin bridge
New column name Custom column formula
ListAmount [Quantity] * [BaseListPrice]
DiscountAmount [Quantity] * [UnitPrice] * [Discount]

The custom columns are not rounded, so their totals can differ by a few cents from the ROUND formulas of Chapter 4. The bridge uses the query’s columns throughout, so it is consistent with itself.

Step 2. Build the driver PivotTable.

  1. Insert a PivotTable from the InvoiceLines Table on a new worksheet named Drivers.
  2. Drag ItemGroup to Filters, and select Furniture in the filter in B1.
  3. Drag Period to Columns. Open the Column Labels filter, select only 2026-Q3 and 2026-Q4, and click OK.
  4. Drag Quantity, ListAmount, LineTotal, DiscountAmount, and StdCostAmount to Values, in that order, and then drag Σ Values from Columns to Rows.
  5. Choose PivotTable Design > Grand Totals > Off for Rows and Columns, because a total of the two quarters is not part of the comparison.
  6. Format every value field as a number with two decimals and a thousands separator.

The PivotTable now shows the five Furniture totals of the third and fourth quarters side by side, the inputs of every per-unit measure and of the bridge.

Step 3. Add the per-unit measures. Calculated fields belong to the pivot cache, the copy of the source data that the PivotTables built from the same Table share, so MarginPct from Tutorial 6.2 appears in the field list.

  1. Check the MarginPct box to add it to Values, or create it again if it does not appear.
  2. Create the calculated fields in Table 6.5, as you did in Tutorial 6.2.
  3. Format the per-unit fields as numbers with two decimals, and MarginPct and MarginBeforeDiscountPct as percentages with two decimals.
  4. Drag Sum of MarginPct in the Values area to just below Sum of CostPerUnit, so that the rows follow the order of Figure 6.8.
Table 6.5: Calculated fields for the driver PivotTable
Name Formula
PricePerUnit =LineTotal/Quantity
ListPerUnit =ListAmount/Quantity
CostPerUnit =StdCostAmount/Quantity
MarginBeforeDiscountPct The margin with the discounts added back to revenue, in the block below

The formula of MarginBeforeDiscountPct is:

=(LineTotal+DiscountAmount-StdCostAmount)/(LineTotal+DiscountAmount)

Read the two columns side by side, as Figure 6.8 does. The quantity sold barely changed, and neither did the list price or the standard cost per unit. The price per unit fell, and the promotional discounts more than doubled. The margin before discounts, which adds the discounts back to revenue, did not fall at all. In this comparison at standard cost, the drivers point to one dominant component, the larger promotional discounts. The bridge in the next steps measures it separately from the smaller volume, mix, and price-list effects. The gray column in the figure is an annotation that shows each change, which you read from the two columns, and the PivotTable does not produce it.

A mock of the Drivers worksheet. A PivotTable filtered to the Furniture item group shows, for 2026-Q3 and 2026-Q4 side by side, the sums of Quantity, ListAmount, LineTotal, DiscountAmount, and StdCostAmount, the calculated fields PricePerUnit, ListPerUnit, CostPerUnit, MarginPct, and MarginBeforeDiscountPct. A gray column beside the PivotTable gives the change from Q3 to Q4 of each measure. The DiscountAmount row, which more than doubles, the PricePerUnit row, which falls, and the MarginBeforeDiscountPct row, which rises slightly, are outlined.
Figure 6.8: The Furniture Driver PivotTable for the Third and Fourth Quarters.

Step 4. Collect the driver totals. The bridge applies the calculations in Table 6.3 to the driver totals, so it starts by linking them to the PivotTable.

  1. Add a worksheet named Bridge. Type the headings Driver, 2026-Q3, and 2026-Q4 in A3:C3, and the labels Quantity, ListAmount, LineTotal, DiscountAmount, and StdCostAmount in A4:A8.

  2. In B4, type an equal sign, click the 2026-Q3 value of Sum of Quantity on the Drivers worksheet, and press Enter. Excel writes a GETPIVOTDATA formula such as this one:

    =GETPIVOTDATA("Sum of Quantity",Drivers!$A$3,"Period","2026-Q3")

  3. If Excel writes an ordinary cell reference such as =Drivers!B5 instead, choose PivotTable Analyze > Options and check Generate GetPivotData, a setting of Excel itself, and then click the value again.

  4. Fill B4:C8 in the same way, clicking the matching value of the PivotTable for each cell.

  5. Type Margin in A9, enter =B6-B8 in B9, and copy it to C9.

B4:C9 now hold the totals of both quarters and their margins, each linked to the PivotTable by GETPIVOTDATA. The formulas name the period but not the item group, so they return Furniture totals only while the ItemGroup filter in B1 of the Drivers worksheet shows Furniture.

Step 5. Calculate the per-unit measures and the bridge.

  1. In A11:A14, type Margin per unit, List margin per unit, Price-list reduction per unit, and Discount per unit.
  2. In B11:B14, enter =B9/B4, =(B5-B8)/B4, =(B5-B6-B7)/B4, and =B7/B4.
  3. Copy B11:B14 to C11:C14.
  4. Type the bridge labels 2026-Q3 margin, Volume, Mix, Price lists, Promotions, Cost, 2026-Q4 margin, and Check in A16:A23, and enter these formulas in B16:B23:
    • B16: =B9
    • B17: =(C4-B4)*B11
    • B18: =C4*(C12-B12)
    • B19: =-C4*(C13-B13)
    • B20: =-C4*(C14-B14)
    • B21: 0, with the note Each item has one standard cost in C21
    • B22: =C9
    • B23: =ROUND(B16+SUM(B17:B21)-B22,2), and in C23 =IF(B23=0,"PASS","INVESTIGATE")
  5. Format B16:B22 as numbers with no decimals, and B23 as a number with two decimals. The cells keep their full precision, so only the display of B16:B22 is rounded and the check still compares to the cent.
  6. To see that the check works, add +0.1 to the formula in B17, and confirm that B23 reads 0.10 and C23 reads INVESTIGATE. Then remove the +0.1.

B23 is zero and C23 reads PASS: the effects account for the whole change in margin. The bridge explains the change in margin dollars. The margin rate, which fell by about a point and a half, is a separate measure, and the bars do not decompose it directly. The volume effect is a small gain, because slightly more units were sold. The mix and price-list effects are small. The promotions effect is a loss larger than the entire decline in margin. It is slightly smaller than the increase in discount dollars on the Drivers worksheet, because the volume effect values the extra units at the third quarter’s margin per unit, which already deducts that quarter’s discount per unit.

Step 6. Chart the bridge as a waterfall. Chart types are compared in the section on charting earlier in this chapter; a waterfall suits a change that is built from parts.

  1. Select A16:B22 and choose Insert > Insert Waterfall, Funnel, Stock, Surface, or Radar Chart > Waterfall.
  2. Click the first bar, and click it again so that only that bar is selected. Right-click it and choose Set as Total. Do the same for the last bar.
  3. Right-click the vertical axis, choose Format Axis, and set the Minimum bound to 1600000, so that the small effects are visible.
  4. Say in the chart title or a note that the axis starts at 1.6 million, because a truncated axis exaggerates differences for a reader who does not notice it.

Figure 6.9 shows the finished bridge. One bar dominates it, and it is not volume, mix, or cost.

A waterfall chart of the Furniture margin at standard cost. It starts with a total bar for the 2026-Q3 margin, followed by a small increase for volume, small changes for mix and price lists, a large decrease for promotions, and no change for cost, and it ends with a lower total bar for the 2026-Q4 margin. Each bar is labeled with its dollar amount and sign, and a legend distinguishes totals, increases, decreases, and no effect. The vertical axis starts at 1.6 million dollars.
Figure 6.9: The Furniture Margin Bridge from the Third to the Fourth Quarter.

Step 7. Drill into the discounts. In the bridge, the promotions effect is the largest negative component of the change in margin. The next question is which promotions the discounts came from.

  1. Insert a PivotTable from the InvoiceLines Table on a new worksheet named Discounts. Drag ItemGroup to Filters and select Furniture, and drag Period to Columns and select only 2026-Q3 and 2026-Q4.
  2. Drag PromotionID to Rows, and drag SalesInvoiceLineID and DiscountAmount to Values.
  3. Excel sums SalesInvoiceLineID, because it is a number. Open its Value Field Settings, choose Count, and type the custom name Count of Lines. Give DiscountAmount the custom name Sum of Discount.
  4. Turn off the grand totals, and find the promotions that appear in the PivotTable on the PromotionProgram worksheet.

Figure 6.10 shows the result. Most of the Furniture discounts in both quarters come from promotion 8, the Furniture Seasonal Promotion, and its discounts more than doubled from the third quarter to the fourth. The reason is timing. The promotion priced orders placed in September and October, so the third quarter received only its September invoices, while the fourth quarter received the October invoices and, as you found in Tutorial 5.2, the lines of promotion orders invoiced after the promotion had ended. Promotion 9, the Design Trade Customer Promotion, which ran in November, added a smaller amount of discounts on Furniture lines in the fourth quarter.

A mock of the Discounts worksheet. A PivotTable filtered to Furniture shows, for 2026-Q3 and 2026-Q4, the count of invoice lines and the sum of discounts for lines with no promotion and for promotions 7, 8, and 9. The promotion 8 row is outlined across both quarters, with far more lines and discount in the fourth quarter, and the promotion 9 cells of the fourth quarter are outlined. Below, rows of the PromotionProgram Table give the code, name, scope, and discount rate of promotions 7, 8, and 9.
Figure 6.10: Furniture Discounts by Promotion and Quarter.

Step 8. Record the finding.

  1. On the Documentation worksheet, add the ListAmount and DiscountAmount custom columns to the Query steps entry, the four calculated fields to the Calculated fields entry, and the Drivers, Bridge, and Discounts worksheets to the Worksheets entry.
  2. Replace the open question about promotion 8, which you recorded in Chapter 4 and revised in Tutorial 5.2, with a Finding entry of two or three sentences that states the change in the Furniture margin, the effect of the promotions, and the effects that were small.
  3. Save the workbook.

The Documentation worksheet now states the answer to the controller’s question in the place where a reviewer looks first, next to the evidence that supports it.

Checkpoint. Check that:

  • the Check cell, B23 on the Bridge worksheet, is zero to the cent, and C23 reads PASS;
  • the promotions effect is larger than the decline in margin, and the volume, mix, and price-list effects are small by comparison;
  • the margin before discounts does not fall from the third quarter to the fourth;
  • promotion 8 accounts for most of the Furniture discounts in both quarters, and promotion 9 adds to them in the fourth.

The controller now has her answer, and it is one she can check. The Furniture margin fell because promotional discounts more than doubled, mostly under the Furniture Seasonal Promotion, whose orders were invoiced across the end of the third quarter and well into the fourth. Volume, mix, list prices, and standard costs explain almost none of it.

6.11 Interpreting and Communicating Results

A PivotTable is a data product. A sentence that states what it shows, how you know, and what should happen next is an analytical finding, and the move from one to the other is where professional judgment enters. For the controller, the finding is short: the Furniture margin at standard cost fell by about a point and a half because promotional discounts more than doubled, while volume, list prices, the mix of items, and standard costs changed very little. The evidence is the bridge, which ties to the change in margin, and the drill-down by promotion, which names the Furniture Seasonal Promotion as the main source of the discounts.

Interpretation starts from an expectation. Auditors formalize this in substantive analytical procedures, analytical procedures used as evidence about whether an amount is misstated. The auditing standard of the Public Company Accounting Oversight Board (PCAOB) defines analytical procedures as comparisons of recorded amounts, or of ratios calculated from them, with expectations that the auditor develops from plausible relationships. It asks the auditor to decide in advance how large a difference from the expectation can be accepted without further investigation, and to evaluate significant unexpected differences, for example by reconsidering the expectation and by inquiring of management, whose responses should ordinarily be corroborated with other evidence (PCAOB, n.d.). An expectation is only as good as the relationship and the data behind it. One built from last year’s amount, as in Exercise 6.5, assumes that nothing relevant has changed, and a difference that stays below the threshold does not show that an amount is free of misstatement. Management reporting benefits from the same discipline. Decide what you expect, and why, before you read the result, and treat a difference as a question to answer rather than a number to report.

A finding should also claim no more than its evidence supports. The bridge shows how much the discounts reduced the margin. It does not show whether the promotion was a good decision, because that depends on what the promotion achieved, such as sales it may have brought in, and on what the company intended. The quantities on the invoice lines, as you found in Chapter 5, are often fractional even for items sold by the unit. Per-unit measures calculated from totals are consistent from one quarter to the next, so they support the comparison, but a finding should not depend on whole units. Finally, match precision to the reader: a workpaper reconciles to the cent, while a memo to management rounds to the nearest thousand dollars or tenth of a percentage point.

NoteConnecting the Dots

Every summary in this chapter has an equivalent in the tools of Parts III and IV. A PivotTable of revenue by product line and quarter is a SQL query that groups rows and sums them, and a calculated field is a measure defined on totals, which is how Power BI calculates margins. The waterfall chart of Tutorial 6.3 is one of the visual forms you will meet again when you study how to present results. The questions stay the same across the tools: which rows are included, at what level the numbers are totaled, and whether the parts add up to the whole.

6.12 Looking Ahead

This chapter has taken the controller’s question from description to diagnosis. You profiled the fiscal 2026 invoice lines with statistical functions, learned why the mean and the median tell different stories about a skewed population, and drew the skew as a histogram. You calculated totals by category with conditional aggregation and used a control total to catch a category that was missing. You built PivotTables, added calculated fields that work on totals, displayed the same values as shares and changes, charted them, and learned to refresh and reference them safely. You grouped the general ledger into a pre-closing trial balance and turned it into a common-size income statement that ties to the closing entry. Finally, you drilled into the Furniture margin and decomposed its change into volume, mix, price, and cost effects that add up exactly to the change.

The analysis has answered the question that opened Part I. The Furniture margin fell because promotional discounts, mostly from the Furniture Seasonal Promotion, more than doubled from the third quarter to the fourth, and many of the promotion’s orders were invoiced after it ended. Knowing why the margin fell is the starting point for deciding what to do about it, and Chapter 7 turns from explaining what happened to estimating what could happen next.

6.13 Chapter Summary

Descriptive analytics summarizes what happened, and diagnostic analytics explains why. Much of what accountants produce, from comparative statements to variance reports, is descriptive, and diagnostic work begins when a summary shows a change that nobody expected. This chapter moved from one to the other with four families of Excel tools: statistical functions, conditional aggregation, PivotTables, and a margin bridge. It also charted the results and built the year’s income statement from the ledger.

Statistical functions describe a column with a single number. AVERAGE and MEDIAN measure its center, and a mean well above the median suggests a right-skewed distribution, the usual shape of accounting populations, which a histogram can then show. STDEV.S and STDEV.P measure its spread, and MIN, MAX, and PERCENTILE.INC describe its extremes and serve as data checks. MEDIAN, STDEV.S, and PERCENTILE.INC accept no criteria, so FILTER supplies them with part of a table. Because a statistic can hide the shape of the data it summarizes, it should be read alongside the data and calculated separately for groups that behave differently. Conditional aggregation functions, such as SUMIFS and COUNTIFS, calculate results for the rows that meet criteria kept in cells. SUMIFS and COUNTIFS fail silently, returning zero for a misspelled or missing category, while AVERAGEIFS returns #DIV/0!, so every summary by category should be compared with a control total.

A chart shows what a table of statistics cannot: the shape of the data. The question decides the chart. Columns compare categories, lines show trends, histograms show distributions, and waterfalls show how one total became another. An honest chart starts its bars at zero, states any truncated axis, and never carries meaning by color alone. A histogram of the fiscal 2026 line totals makes the right skew visible at a glance.

PivotTables build a summary from fields placed in their Rows, Columns, Values, and Filters areas, with totals for every row and column. Value field settings choose the calculation and format, Show Values As displays the same values as shares or changes, and calculated fields define measures such as margins. A calculated field works on the sums of its fields, so it suits ratios of totals but not products of fields, which belong in the source as custom or calculated columns. PivotTables summarize a cache that must be refreshed after the source changes, and formulas should refer to them with GETPIVOTDATA. A PivotChart draws the current layout of a PivotTable. A summary of the ledger by period must exclude the year-end closing entries.

The financial statements start from the general ledger. Power Query’s Group By turns hundreds of thousands of ledger rows into a trial balance, one row for each account. A pre-closing trial balance at the end of fiscal 2026 includes every entry through December 31 except that year’s closing entries. SUMIFS by account subtype then produces each line of the income statement, and a common-size statement shows each line as a share of net revenue. Two checks tie the statement to the ledger: net income equals the amount of the closing entry, and the balance sheet balances.

Diagnosis drills down, compares, and decomposes. A margin bridge breaks the change in margin between two periods into volume, mix, price, and cost effects that add up exactly to the change, and the level at which it is calculated affects how the change divides among them. Applied to Charles River, the bridge showed that the Furniture margin at standard cost fell from the third quarter of fiscal 2026 to the fourth because promotional discounts, mostly from the Furniture Seasonal Promotion, more than doubled, while volume, mix, list prices, and standard costs changed little. A finding like this should state its evidence, start from an expectation, and claim no more than the evidence supports.

6.14 Key Terms

Calculated field. A field defined inside a PivotTable with a formula that uses other fields. Excel sums each field over the rows behind a cell and then applies the formula to those sums, so calculated fields suit ratios and differences of totals, such as margin percentages.

Common-size statement. A financial statement that expresses every line as a percentage of a base, such as net revenue for the income statement, so that its structure can be compared across periods or companies. Comparing lines within one period in this way is vertical analysis.

Conditional aggregation. Calculating a total, count, average, minimum, or maximum over only the rows that meet one or more criteria, as SUMIFS, COUNTIFS, AVERAGEIFS, MINIFS, and MAXIFS do.

Control total. A total calculated independently from a set of data, which the data must agree with, such as the sum of the invoice subtotals against the sum of the invoice lines. In a summary by category, the categories must add up to the control total, so a difference reveals a missing category, a duplicated row, or a formula that fails silently.

Cost effect. The part of a change in margin caused by a change in the cost of the same items. In a margin measured at standard cost item by item, it is zero unless the standard costs themselves change; measured for a group of items, such as a product type, it also picks up shifts between items with different costs.

Cross-tabulation. A summary of a measure across two dimensions at once, with one dimension in the rows and the other in the columns, such as revenue by product line and quarter. A PivotTable produces one.

Drill-down. Moving from a total to the parts or rows behind it, for example by double-clicking a value in a PivotTable or by adding a more detailed field to its Rows area.

GETPIVOTDATA. An Excel function that returns a value from a PivotTable by naming its value field and items rather than its cell address, so that the reference stays correct when the PivotTable’s layout changes.

Group By. A Power Query command that summarizes the rows of a query by one or more columns, with aggregations such as sums and counts, before the result is loaded. It produces a trial balance from the ledger by grouping the entries by account.

Histogram. A chart of how the values of a measure are distributed, drawn as bars that count the values falling in each of a set of bins of equal width. It shows the shape of a distribution, such as a right skew.

Margin bridge. A decomposition of the change in margin between two periods into effects, such as volume, mix, price, and cost, that add up exactly to the change. It is often presented as a waterfall chart.

Mean. The arithmetic average of a set of values, their sum divided by their number, calculated in Excel with AVERAGE. A few very large or very small values pull it away from the typical value.

Median. The middle value of a set of values sorted in order, or the average of the two middle values when their number is even, calculated in Excel with MEDIAN. It is not affected by how extreme the largest or smallest values are.

Mix effect. The part of a change in margin caused by a shift in sales between items or products that earn different margins per unit, while the total number of units is held constant.

Pivot cache. The copy of the source data that a PivotTable summarizes. PivotTables built from the same source share it, with its calculated fields, and it updates only when the PivotTable is refreshed.

PivotTable. An interactive Excel summary built from a source table by placing its fields in the Rows, Columns, Values, and Filters areas. It calculates a value for every combination of row and column items, with totals, and it is updated by refreshing.

Pre-closing trial balance. A trial balance prepared at the end of a period before that period’s closing entries, so that the revenue and expense accounts still hold the period’s activity from which the income statement is prepared.

Price effect. The part of a change in margin attributed to the prices charged, including changes in price-list reductions and promotional discounts. Measured as the change in the average reduction per unit, as in Tutorial 6.3, it also picks up shifts between items with different reductions.

Show Values As. A PivotTable setting that displays a value field as a share of a total, a difference from a base item, a running total, or a rank, without changing the values behind it.

Skewed distribution. A distribution whose values are not spread symmetrically around its center. A distribution skewed to the right, the usual shape of accounting populations, has a long tail of large values, which tends to pull the mean above the median.

Standard deviation. A measure of how widely values are spread around their mean. In Excel, STDEV.S treats the values as a sample of a larger population, and STDEV.P treats them as the complete population.

Substantive analytical procedure. An analytical procedure that an auditor uses as evidence about whether an amount is misstated, by comparing the recorded amount with an expectation developed from plausible relationships and investigating differences above a threshold set in advance.

Value field. A field placed in the Values area of a PivotTable and summarized, by a calculation such as Sum, Count, or Average, for every cell of the PivotTable.

Volume effect. The part of a change in margin caused by selling more or fewer units in total, valued at the earlier period’s margin per unit.

Waterfall chart. A chart that shows how a starting value becomes an ending value through a sequence of increases and decreases, with the starting and ending values drawn as totals.

6.15 Multiple Choice Questions

1. For the Charles River invoice lines of fiscal 2026, the average line total is about half again as large as the median line total. What does this suggest about the distribution of line totals?

  1. It is symmetric, so the average and the median describe the typical line equally well.
  2. It is skewed to the right, with a minority of large lines pulling the average up.
  3. It is skewed to the left, with a minority of very small lines pulling the average down.
  4. It contains errors, because the average and the median of accounting data should be equal.

2. What is the difference between STDEV.S and STDEV.P?

  1. STDEV.S measures the spread around the median, while STDEV.P measures the spread around the mean.
  2. STDEV.S ignores blank cells in the range, while STDEV.P counts every blank cell as a zero.
  3. STDEV.S divides by n − 1, for a sample, and STDEV.P by n, for a whole population.
  4. STDEV.S works only on the columns of Excel Tables, while STDEV.P works on any range of cells.

3. An analyst wants the median line total of fiscal 2026 invoice lines only, and enters this formula:

=MEDIAN(FILTER(InvoiceLines[LineTotal],InvoiceLines[FiscalYear]=2026))

Why does the formula use FILTER?

  1. MEDIAN takes no criteria, so FILTER supplies the fiscal 2026 rows.
  2. FILTER converts the line totals from text into numbers first.
  3. FILTER removes the outliers that would otherwise distort the median.
  4. FILTER sorts the line totals into order, which MEDIAN requires.

4. A summary uses this formula, with the group name misspelled:

=SUMIFS(InvoiceLines[LineTotal],InvoiceLines[ItemGroup],"Furnture")

What does the formula return?

  1. The error #N/A, because no row of the Table matches the criterion
  2. The Furniture total, because SUMIFS corrects near matches in text
  3. The total of every row, because an invalid criterion is ignored
  4. Zero, without an error, which a control total would reveal

5. In Tutorial 6.1, the revenue of the four product lines falls short of the fiscal 2026 control total by exactly the revenue of the Services item group. What does the difference show?

  1. The summary leaves out a category and is incomplete until it is added.
  2. The SUMIFS formulas of the four product lines contain a spelling error.
  3. The control total double-counts some invoice lines of the Services group.
  4. Services revenue should be excluded from the control total of the year.

6. An analyst wants to show how the fiscal 2026 invoice line totals are distributed: how many lines are small, how many are medium, and how many are large. Which chart suits the question best?

  1. A pie chart of revenue by item group
  2. A line chart of the line totals in invoice order
  3. A histogram of the line totals, in bins of equal width
  4. A stacked column chart of each quarter’s revenue by item group

7. An analyst defines a PivotTable calculated field with the formula =Quantity*UnitPrice to show revenue. What does the field return for each cell of the PivotTable?

  1. The revenue of the cell, because the formula is applied to each row and the results are summed
  2. An error, because calculated fields cannot multiply two fields
  3. The average revenue per line in the cell
  4. The sum of the quantities multiplied by the sum of the unit prices, which is not the revenue

8. A PivotTable shows fiscal 2026 revenue with item groups in its rows and quarters in its columns. What does Show Values As % of Column Total display?

  1. Each quarter’s share of the item group’s revenue for the year
  2. Each item group’s share of the quarter’s revenue
  3. The change in each group’s revenue from the previous quarter
  4. Each value’s share of the revenue of all groups and quarters together

9. A formula on the Bridge worksheet refers to the PivotTable with GETPIVOTDATA:

=GETPIVOTDATA("Sum of Quantity",Drivers!$A$3,"Period","2026-Q3")

What is its advantage over a cell address such as =Drivers!B5?

  1. GETPIVOTDATA reads the value from the source Table, so the PivotTable can be deleted.
  2. GETPIVOTDATA refreshes the PivotTable every time the formula recalculates.
  3. It names the value by field and item, so layout changes do not break it.
  4. GETPIVOTDATA rounds the value to the number format shown in the PivotTable.

10. An analyst adds a custom column to the InvoiceLines query and loads the query, but the new field does not appear in a PivotTable built from InvoiceLines. What is the most likely explanation?

  1. A PivotTable reads a copy of the data that a refresh must update.
  2. The new custom column must first be converted to values in the Table.
  3. PivotTables cannot use any of the columns that Power Query adds.
  4. The PivotTable must be deleted and built again from the Table.

11. A PivotTable of the Charles River general ledger shows negative revenue for the Furniture account in the fourth quarter of fiscal 2026. What is the most likely cause?

  1. Credit memos issued in the fourth quarter exceeded the invoices of the quarter.
  2. The Furniture Seasonal Promotion reduced the quarter’s revenue below zero.
  3. The PivotTable sums the debits and the credits in the wrong columns.
  4. The year-end closing entry, which debits the Furniture account, is included.

12. An analyst builds a trial balance at December 31, 2026, from GLEntry and leaves out the two 2026 closing entries. Why are they left out?

  1. The closing entries do not balance, so the trial balance would not balance.
  2. The closing entries zero the accounts the income statement is built from.
  3. The closing entries are posted in fiscal 2027, after the cutoff.
  4. The closing entries hold the opening balances of the balance sheet accounts.

13. Why is the cost effect zero in the Furniture margin bridge of Tutorial 6.3?

  1. The Furniture product line has no cost of goods sold in the ledger.
  2. Costs are excluded from the bridge because they are recorded only in the ledger.
  3. Each item has one standard cost, the same in both quarters.
  4. The cost effect is always combined with the volume effect in a bridge.

14. From the third quarter to the fourth, the Furniture margin at standard cost falls, while the margin before promotional discounts rises slightly. What can you conclude?

  1. The larger discounts more than offset other effects that on balance raised the margin.
  2. The decline comes from higher standard costs of the Furniture items in the fourth quarter.
  3. The decline comes from a fall in the number of Furniture units sold in the fourth quarter.
  4. The two margins are calculated inconsistently, so no conclusion is possible.

15. Under the standard of the Public Company Accounting Oversight Board (PCAOB) on substantive analytical procedures, what should an auditor do before evaluating the difference between recorded revenue and an expectation?

  1. Ask management for the expected amount and accept it if it is close to the recorded one.
  2. Develop the expectation and decide in advance how large a difference is acceptable.
  3. Copy the previous year’s revenue without considering known changes or the reliability of the data.
  4. Select a random sample of transactions, because analytical procedures use no expectations.

6.16 Applied Exercises

As in the earlier chapters, work on these exercises in a fresh copy of your analysis workbook, made after you finish this chapter’s tutorials. Put each exercise on its own worksheets, named after it, and do not rename or edit the queries the tutorials created.

6.16.1 Financial Accounting Exercises

Exercise 6.1: From the Ledger to the Income Statement

Table group: Accounting Core, with the invoice lines from Order-to-Cash

Scenario. The controller wants the fiscal 2026 income statement for the management meeting, prepared from the general ledger rather than typed from a report, and she wants to know that its revenue agrees with the product-line revenue in your PivotTables. Build it by following the method in the section “From the Ledger to the Financial Statements”.

Requirements.

  1. Build the trial balance query in your copy of the analysis workbook:
  • Import the T3_GLEntry Table, clicking Transform Data rather than Load, and name the query TrialBalance.
  • Filter PostingDate to dates before 2027-01-01, and filter VoucherNumber to exclude JE-2026-000296 and JE-2026-000297, the 2026 closing entries.
  • Choose Transform > Group By, select Advanced, group by AccountID, and add two aggregations: Debit, the sum of Debit, and Credit, the sum of Credit.
  • Import the T1_Account Table as a second query named ChartOfAccounts, and keep AccountID, AccountNumber, AccountName, AccountType, and AccountSubType.
  • Merge TrialBalance with ChartOfAccounts on AccountID, and expand the four other columns without the prefix.
  • ChartOfAccounts only feeds the merge, so it should load as a connection only. Choose Home > Close & Load > Close & Load To, select Only Create Connection, and click OK; the choice applies to both new queries.
  • Open Data > Queries & Connections, right-click TrialBalance, choose Load To, and load it as a Table on a New worksheet.

  1. In the TrialBalance Table, add three calculated columns: Balance, equal to Debit minus Credit; DebitBalance, equal to =MAX(0,[@Balance]); and CreditBalance, equal to =MAX(0,-[@Balance]). Turn on Table Design > Total Row and show the sums of DebitBalance and CreditBalance. Explain why the revenue and expense accounts show only the activity of fiscal 2026, although the query includes 2024 and 2025.

  2. On a worksheet named IncomeStatement, build the income statement of Figure 6.7: operating revenue (the Operating Revenue subtype), sales returns and allowances (Contra Revenue), net revenue, cost of goods sold (COGS), gross margin, operating expenses (Operating Expense), operating income, other income and expense (the Other Income or Expense and Other Expense subtypes), and net income. Calculate each line that comes from the accounts with SUMIFS on the Balance column by AccountSubType, with the sign reversed, so that revenue is positive and the deductions negative, as in Figure 6.7; calculate each subtotal as the sum of the lines above it. Add a column that expresses every line as a percentage of net revenue.

  3. Check the statement in two ways. First, compare net income with the TotalAmount of JE-2026-000297, the entry that closed the income summary to retained earnings. The analysis workbook has no journal entry Table, so import the T2_JournalEntry Table as a query named JournalEntries, or read the entry’s TotalAmount on the JournalEntry worksheet of CharlesRiver.xlsx. Second, use SUMIFS by AccountType to show that the assets equal the liabilities and equity once net income is added to equity.

  4. Compare the balances of the product revenue accounts, 4010 to 4040 and 4080, with the fiscal 2026 revenue of their item groups in InvoiceLines. Three accounts differ by small amounts. Identify the invoices responsible, using what you found in Exercise 5.2 about invoices numbered in one fiscal year and dated in another, and explain which date should determine the period of their revenue and what evidence would settle it. Each invoice line’s ShipmentLineID leads, through the ShipmentLine and Shipment worksheets of CharlesRiver_Work.xlsx, to the date its goods were delivered.

  5. Write a memo of half a page to the controller that presents the common-size income statement, describes what it shows about Charles River’s cost structure, and explains the reconciling differences. State that the statement is as recorded, before any correction of the differences you found.

Deliverable. A trial balance whose debit and credit balances agree, a common-size income statement for fiscal 2026 that ties to the closing entry, a revenue reconciliation, and a half-page memo.

Exercise 6.2: Disaggregating Revenue by Segment and Product Line

Table group: Order-to-Cash, with the Customer and Item tables from Master Data

Scenario. For this disclosure-style exercise, assume that Charles River is a public business entity applying U.S. GAAP. The revenue standard of the Financial Accounting Standards Board (FASB) requires such an entity to disaggregate its revenue into categories that show how economic factors affect the nature, amount, timing, and uncertainty of its revenue and cash flows, and it offers major product lines and types of customer as examples of such categories (FASB, 2014, ASC 606-10-50-5 and 55-91). The controller asks you to prepare a first disaggregation on that basis from the invoice lines, for management, before it is reconciled to the revenue the financial statements report.

Requirements.

  1. Build a PivotTable from InvoiceLines with CustomerSegment in the Rows area, ItemGroup in the Columns area, and LineTotal in the Values area, filtered to fiscal 2026. Show the values as % of Grand Total and identify the three largest combinations of segment and product line.

  2. Keep that PivotTable, and build a second one with the same fields, adding FiscalYear to the Columns area, above ItemGroup. Show 2025 and 2026, and calculate each combination’s change from 2025 with % Difference From. Identify the two largest percentage increases and the two largest percentage decreases among the combinations with at least $500,000 of revenue in 2025.

  3. Repeat the year-over-year comparison in a third PivotTable, by Region instead of by segment, and report the change of each region as a whole.

  4. Write a disclosure-style paragraph of four to six sentences that presents the disaggregation you would recommend, states why you chose its categories, and summarizes the changes from the previous year.

  5. List and quantify the differences between this invoice-based revenue and the revenue that the income statement of Exercise 6.1 reports, which would have to be reconciled before the disaggregation could support a disclosure in the financial statements.

Deliverable. Three PivotTables, a list of the largest combinations and changes, a disclosure paragraph, and the list of differences to reconcile.

6.16.2 Managerial Accounting Exercises

Exercise 6.3: Product-Line Profitability at Standard Cost

Table group: Order-to-Cash, with the Item table from Master Data

Scenario. The vice president of sales asks which product lines contribute the most margin relative to their revenue, and which Furniture product types earn the highest and lowest margins.

Requirements.

  1. Using the StdCostAmount column and the MarginPct calculated field from Tutorial 6.2, build a PivotTable for fiscal 2026 that shows, for each item group, the revenue, the margin in dollars (a new calculated field), and the margin percentage.

  2. Add each product line’s share of revenue and share of margin, excluding Services, and identify the product lines whose share of margin is larger than their share of revenue.

  3. Copy the PivotTable, filter the copy to Furniture, and replace ItemGroup with ProductType in it. Identify the two product types with the highest margin percentage and the two with the lowest, and name them with the product type codes from Chapter 5.

  4. Explain in three to five sentences why the Services margin cannot be read from this PivotTable, and describe the data you would need to measure it.

Deliverable. A profitability PivotTable by product line and by Furniture product type, and a short written interpretation.

Exercise 6.4: The Margin Bridge by Product Type

Table group: Order-to-Cash, with the Item table from Master Data

Scenario. The controller has read your bridge from Tutorial 6.3 and asks whether the story changes if the change is decomposed product type by product type, the level at which the sales team plans its assortment.

Requirements.

  1. Build a PivotTable filtered to Furniture with ProductType in the Rows area, Period in the Columns area showing only 2026-Q3 and 2026-Q4, and the sums of Quantity, LineTotal, and StdCostAmount in the Values area. Copy the values to a new worksheet with Paste Values, or reference them with GETPIVOTDATA.

  2. For each product type, calculate the price per unit, the standard cost per unit, and the margin per unit in each quarter, and each type’s share of units in the third quarter.

  3. Calculate the four effects, and check that they sum to the change in the Furniture margin:

  • price: the sum over the types of Q4 units × (Q4 price per unit − Q3 price per unit);
  • cost: minus the sum over the types of Q4 units × (Q4 cost per unit − Q3 cost per unit);
  • volume: (total Q4 units − total Q3 units) × the Q3 margin per unit of Furniture as a whole;
  • mix: the sum over the types of (Q4 units − total Q4 units × the type’s Q3 share of units) × the type’s Q3 margin per unit.

  1. Compare the effects with those of Tutorial 6.3. Explain why the cost effect is no longer zero even though no item’s standard cost changed, using the product type with the largest change in cost per unit as an example.

  2. Recommend, in three or four sentences, the level at which the controller’s report should present the bridge, and why.

Deliverable. A product-type bridge that ties to the change in margin, and a short comparison and recommendation.

6.16.3 Auditing Exercises

Exercise 6.5: A Substantive Analytical Procedure on Quarterly Revenue

Table group: Order-to-Cash, with the Item table from Master Data

Scenario. As part of the audit of fiscal 2026 revenue, your senior asks you to perform a simple analytical procedure: develop an expectation of each product line’s revenue for each quarter, compare the recorded revenue with it, and identify the differences that need investigation, as the auditing standard of the Public Company Accounting Oversight Board (PCAOB) describes (PCAOB, n.d.). For this exercise, use the same quarter of the previous year as a deliberately simple expectation and apply the thresholds of requirement (3). In an audit, you would also assess the reliability of the data, refine the expectation for known changes, set the acceptable difference from materiality and the audit objective, and corroborate management’s explanations, and a difference below the thresholds would not by itself show that an amount is free of misstatement.

Requirements.

  1. Build a PivotTable with ItemGroup in the Rows area and FiscalQuarter and FiscalYear in the Columns area, showing 2025 and 2026.

  2. On a separate worksheet, use each group’s revenue in the same quarter of 2025 as the expectation for 2026, and calculate the difference in dollars and as a percentage of the expectation.

  3. Flag the differences that exceed both 10 percent of the expectation and $50,000, the thresholds your senior chose for this exercise, in either direction: compare the absolute values, with ABS, so that decreases are flagged as well as increases. Store the two thresholds in labeled input cells, and refer to those cells in the flag formula. List the flagged differences.

  4. Identify the largest difference in dollars that your threshold does not flag, and explain what that says about the design of the threshold.

  5. Choose one flagged difference and describe the evidence you would obtain and the questions you would ask management to explain it.

Deliverable. An expectation worksheet with the flagged differences and a half-page explanation of the threshold and the follow-up.

Exercise 6.6: A Descriptive Profile of the Revenue Population

Table group: Order-to-Cash, with the Customer and Item tables from Master Data

Scenario. Before the audit team designs its detailed tests of revenue, your senior asks for a profile of the fiscal 2026 invoice lines that describes what a normal line looks like, where the revenue is concentrated, and how the lines were priced. The profile will be the baseline for the audit work later in Part II.

Requirements.

  1. For each item group, calculate the number of lines, the total revenue, and the average and median line total for fiscal 2026. For the median, combine two conditions in FILTER by multiplying them, as in:
FILTER(InvoiceLines[LineTotal],(InvoiceLines[FiscalYear]=2026)*(InvoiceLines[ItemGroup]=A2))

  1. Build a PivotTable of fiscal 2026 revenue by CustomerID, sorted from largest to smallest, and bring each customer’s name and segment from the Customer Table. Calculate the share of revenue of the five and the ten largest customers, and note the segment they belong to.

  2. Build a PivotTable of the count of lines and the revenue by PricingMethod and ItemGroup, and show each pricing method’s share of the year’s revenue.

  3. Build a PivotTable of the count of lines and the sum of DiscountAmount by PromotionID for fiscal 2026, and calculate the share of each item group’s lines that carry a promotion. Identify the promotions whose lines were invoiced in 2026 although the promotion ran in 2025.

  4. Write a profile of three to five paragraphs that describes the population, its concentration, and its pricing, and notes anything that might warrant attention during the audit.

Deliverable. A profile workbook with the four summaries and a written profile suitable for the audit workpapers.

6.17 Further Reading

Anscombe, F. J. (1973). Graphs in statistical analysis. The American Statistician, 27(1), 17–21. https://doi.org/10.1080/00031305.1973.10478966 A short classic that presents four small data sets with the same means, variances, correlation, and regression line, whose plots look completely different. It is the standard argument for looking at data before trusting the statistics that summarize it.

Datar, S. M., & Rajan, M. V. (2021). Horngren’s cost accounting: A managerial emphasis (17th ed.). Pearson. https://www.pearson.com/en-us/pearsonplus/p/9780136713845 A widely used cost accounting text. Its chapter on customer-profitability and sales-variance analysis develops the sales-volume, sales-mix, and sales-quantity variances against a budget, the budget-based counterpart of the margin bridge in this chapter.

Domino, M. A., Schrag, D., Webinger, M., & Troy, C. (2021). Linking data analytics to real-world business issues: The power of the pivot table. Journal of Accounting Education, 57, Article 100744. https://doi.org/10.1016/j.jaccedu.2021.100744 A teaching case in which you use pivot tables to analyze a moderately large and complex data set from a privately owned company and write memorandums to managers about their concerns. It practices the same move from summary to recommendation as this chapter.

Microsoft. (2026a). Calculate values in a PivotTable. Microsoft Support. https://support.microsoft.com/en-us/office/calculate-values-in-a-pivottable-11f41417-da80-435c-a5c6-b0185e59da77 Microsoft’s reference for summary functions, calculated fields, and calculated items in PivotTables. It explains why a calculated field works on the sums of its fields while a calculated item works on individual records, with examples.

Microsoft. (2026c). Show different calculations in PivotTable value fields. Microsoft Support. https://support.microsoft.com/en-us/office/show-different-calculations-in-pivottable-value-fields-014d2777-baaf-480b-a32b-98431f48bfec A description of every Show Values As option, from % of Grand Total to Index, with base-field and base-item settings where required.

Palocsay, S. W., Markham, I. S., & Markham, S. E. (2010). Utilizing and teaching data tools in Excel for exploratory analysis. Journal of Business Research, 63(2), 191–206. https://doi.org/10.1016/j.jbusres.2009.03.008 An article that presents Excel as an introductory tool for business intelligence and exploratory analysis, covering Excel as a database, PivotTables and PivotCharts, and importing data. It was written for an older version of Excel, but its approach to exploring business data carries over.

Public Company Accounting Oversight Board. (n.d.). AS 2305: Substantive analytical procedures. https://pcaobus.org/oversight/standards/auditing-standards/details/AS2305 The auditing standard that governs analytical procedures used as substantive tests in audits of public companies. It explains how auditors develop expectations, decide how large a difference can be accepted without further investigation, and evaluate significant unexpected differences.

6.18 References

Anscombe, F. J. (1973). Graphs in statistical analysis. The American Statistician, 27(1), 17–21. https://doi.org/10.1080/00031305.1973.10478966

Datar, S. M., & Rajan, M. V. (2021). Horngren’s cost accounting: A managerial emphasis (17th ed.). Pearson. https://www.pearson.com/en-us/pearsonplus/p/9780136713845

Domino, M. A., Schrag, D., Webinger, M., & Troy, C. (2021). Linking data analytics to real-world business issues: The power of the pivot table. Journal of Accounting Education, 57, Article 100744. https://doi.org/10.1016/j.jaccedu.2021.100744

Financial Accounting Standards Board. (2014). Revenue from contracts with customers (Topic 606) (Accounting Standards Update No. 2014-09). https://storage.fasb.org/ASU%202014-09_Section%20A.pdf

Institute of Management Accountants. (2025). IMA expanded management accounting competency framework: Advancing competencies for the 21st-century management accountant. https://mc-69e30ef4-758e-4371-ac6f-2657-cdn-endpoint.azureedge.net/-/media/IMA/Files/Home/Career-Resources/Competency-Framework/Competency-Framework-White-Paper.ashx

Microsoft. (2026a). Calculate values in a PivotTable. Microsoft Support. https://support.microsoft.com/en-us/office/calculate-values-in-a-pivottable-11f41417-da80-435c-a5c6-b0185e59da77

Microsoft. (2026b). Create a histogram. Microsoft Support. https://support.microsoft.com/en-us/office/create-a-histogram-85680173-064b-4024-b39d-80f17ff2f4e8

Microsoft. (2026c). Show different calculations in PivotTable value fields. Microsoft Support. https://support.microsoft.com/en-us/office/show-different-calculations-in-pivottable-value-fields-014d2777-baaf-480b-a32b-98431f48bfec

Public Company Accounting Oversight Board. (n.d.). AS 2305: Substantive analytical procedures. https://pcaobus.org/oversight/standards/auditing-standards/details/AS2305