Part II Case: Deciding on the 2027 Promotion Calendar

Accounting Analytics: An Integrated Approach

In this case

Part II of V: Accounting Analytics with Excel · The case that closes this Part

• The Situation
• The Data You Will Use
• Requirements
• Looking Ahead

This is the comprehensive case that closes Part II, not a chapter. The deck opens with the decision, what you hand in and how it is judged, then walks through the situation, the data, and the six requirements in order. Present the opening before students start, and each requirement when they reach it.

Should Charles River keep running promotions?

• The sales team proposes repeating last year’s three promotions
• The approved budget for the new year includes none
• Chapter 7 found one promotion needed far more volume than came
• The controller wants your decision memo before the CFO sees it

The case puts the five Excel chapters to work on one decision. You build and document a workbook with Power Query as in Chapters 4 and 5, describe the promotions’ cost and reconcile it with Chapter 6, test and model their effect with Chapter 7, and test the controls as the internal auditors did in Chapter 8. The Part I case ended with a memo on whether the data could be trusted; this one ends with a recommendation someone will act on.

What you will hand in

A decision memorandum of no more than two pages. Add a one-page appendix that summarizes five numbered exhibits: the promotion cost by year, the gross-to-net schedule, the volume comparison, the break-even results, and the summary of exceptions. Submit the case workbook as well, with its Documentation sheet complete and the full schedules, eligible-line counts, assumptions, and source traces on named worksheets. The appendix names the worksheet behind each exhibit, in a readable font.

The memo carries the recommendation, the one-page appendix carries the five exhibits behind it, and the workbook lets a reviewer rerun every number. Requirement 6 sets out the memo’s three sections.

How your work will be judged

• A workbook that another person could refresh and follow: named queries, control totals that agree, parameters in named cells, and a Documentation sheet that records the sources, the checks, and the open questions.
• A cost of the promotions that ties to the ledger, with a clear explanation of why the income statement cannot show it.
• A volume test that holds price constant, compares each promotion with a stated expectation, and judges each difference against normal variation before calling it an increase.

A strong submission can be refreshed and followed by someone else, ties its cost to the ledger, and judges every apparent increase against normal variation before calling it one. Read these criteria now and again before you hand in the memo.

How your work will be judged (continued)

• A model with stated assumptions, a break-even result for each proposed promotion, and a justified volume basis for 2027.
• Control exceptions traced to their most likely cause with evidence from outside the record being tested, each with a disposition that says what is confirmed and what is still open.
• A memo written for the controller: concise, organized by perspective, and ending with a recommendation she can take to the chief financial officer.

The memo is written for the controller to take to the chief financial officer: concise, organized by the book’s three perspectives, and ending with a recommendation on the proposal.

The Situation

Three promotions a year, every year

• A collection of items in the spring
• An item group in the fall
• A customer segment late in the year
• The proposal repeats last year’s calendar, at the same rates

The proposed promotions are the Harbor Collection Promotion on the items of the Harbor collection, the Furniture Seasonal Promotion on every Furniture item, and the Design Trade Customer Promotion on every order from Design Trade customers. The case gives their rates and months; put the rates on your Parameters sheet.

The sales team’s two arguments

• The Chapter 7 model looked at one promotion in one year
• Promotions bring in new customers
• Both are claims the data can test

Your analysis in Chapter 7 showed that the Furniture Seasonal Promotion would have needed about a third more volume, at the same mix, to pay for itself, and that the volume ordered in its months rose only slightly, far short of that. The comparison could not show what would have sold without the promotion. The team’s answer is that one promotion in one year is not the whole story. Requirement 3 tests both arguments across every promotion and every year in the data.

Four questions from the controller

• What have three years of promotions cost, and where does it show?
• Did any promotion bring in sales that would not otherwise be made?
• What would the proposed calendar do to the new year’s plan?
• Are the controls over promotions good enough to run one?

The first question asks where the cost appears in the financial statements. The chief audit executive has shared the pricing-control findings of Exercise 8.6, and asks you to extend those tests to every promotion before anyone relies on them: that is the fourth question.

The Data You Will Use

A new workbook for the case

• Charles River Promotion Case.xlsx, next to CharlesRiver.xlsx
• It imports the Tables with Power Query, under the names given
• The analysis and audit workbooks of the chapters stay as they are
• Every requirement uses techniques you have already practiced

Import the Tables with Data > Get Data > From File > From Excel Workbook, using the query names the case gives, so that a reviewer can follow the workbook from its queries.

The queries the case builds

• Promotions, with lookups of orders, invoices, items and customers
• OrderLines: what customers ordered, and when
• BilledLines: what was billed and recognized as revenue
• RevenuePostings and BudgetPrices: the ledger and the plans
• Overrides and Employees: who requested and approved prices

OrderLines is built from the order lines and merged with the lookups and Promotions. BilledLines takes the invoice date from Invoices. RevenuePostings comes from GLEntry with account numbers from Accounts, and BudgetPrices from the budget lines.

Follow the Part II Power Query conventions

• Set Discount to a decimal number, as in Tutorial 4.1
• Expand merged columns without the prefix
• Import GLEntry with Transform Data, and filter it first
• Load queries that only feed others as connections only
• Rename each loaded worksheet after its query

Check the data types in each Changed Type step: Discount in OrderLines and BilledLines must be a decimal number, or the rates round to zero. GLEntry is far too large to load before it is filtered. In Excel, a query that only feeds others is loaded with Close & Load To and Only Create Connection; Enable load is Power BI’s name for the same idea.

Watch out: names that collide, and three dates

• PromotionProgram repeats three column names of Item and Customer
• Rename them in Promotions before you merge it into OrderLines
• A promotion applies by order date: timing and volume use OrderLines
• Billed revenue follows the invoice date, the ledger its posting date

The three columns are ItemGroup, CollectionName and CustomerSegment; rename them, for example to PromoItemGroup, PromoCollection and PromoSegment, because two columns in one query cannot share a name. Chapter 5 showed why the order date and the invoice date must be kept apart. The ledger records each invoice on its posting date, which can fall in a later period, as Exercise 6.1 showed, so the tie to the ledger in Requirement 2 reconciles the two.

Check your understanding

Which date or dates?

1. Whether a line falls within a promotion’s dates
2. The quarter in which a discount was billed
3. The volume ordered in a promotion’s months
4. Tying billed revenue to the ledger

Ask students which query each question belongs in, OrderLines or BilledLines, and which date or dates it needs, since the ledger adds a posting date.

Each question has its date

1. Order date: a promotion applies when the order is placed
2. Invoice date, in BilledLines
3. Order date, in OrderLines
4. Both: the invoice date of the lines, the posting date of the ledger

The dates can fall in different months, and even in different years. Using the wrong one misplaces both the promotion’s months and its cost. The ledger has a third date, the posting date, so the tie in Requirement 2 explains the invoices dated in one year and posted in the next.

Requirements

Six requirements, one decision

• Requirement 1: Build and validate the case workbook (Chapters 4 and 5)
• Requirement 2: Measure what the promotions cost and where the cost appears (Chapter 6; financial reporting)
• Requirement 3: Test whether the promotions brought in sales (Chapters 6 and 7; managerial accounting)
• Requirement 4: Model the proposed 2027 calendar (Chapter 7; managerial accounting)
• Requirement 5: Test the controls over promotions (Chapter 8; auditing)
• Requirement 6: Write the decision memo

The requirements follow the controller’s questions: build and validate the workbook, measure the cost, test the effect on sales, model the proposal, test the controls, and write the memo. Each of the middle four takes one of the book’s perspectives.

Build and validate the case workbook

Requirement 1 · Build and validate the case workbook (Chapters 4 and 5)

• Produce: the documented workbook, its queries and completeness checks
• Tool: Power Query, and a Parameters sheet of named cells
• Revisit: Tutorials 4.1 and 4.3, and Chapter 5’s merges
• Check: each control total agrees, and the checks are documented

Start with a Documentation sheet as in Tutorial 4.3, and a Parameters sheet holding the proposal’s three rates and the commission rate of Tutorial 7.3 as named cells, with the inputs in blue. Build OrderLines and BilledLines with their three amounts. Then check the queries against their sources: row counts against the source Tables, what the filters keep, the order lines against the orders, and no discount without a PromotionID.

Measure what the promotions cost

Requirement 2 · Measure what the promotions cost and where the cost appears (Chapter 6; financial reporting)

• Produce: the discount by promotion and year, and a gross-to-net schedule
• Tool: PivotTables and a chart on BilledLines
• Revisit: Chapter 6’s PivotTables, and its tie from ledger to statements
• Check: the net billed ties to the ledger, with any difference explained

Express each year’s discount as a share of that year’s billed revenue. For the latest year, the gross-to-net schedule runs from the list amount through the price reductions of the price lists and overrides, then the promotional discounts, to the net billed. The schedule measures the reductions, but it does not show that each was approved. Find the account where discounts could be recorded, and explain what the income statement shows about the cost. Then show in which quarters the proposed promotions’ discounts were billed.

Test whether the promotions brought in sales

Requirement 3 · Test whether the promotions brought in sales (Chapters 6 and 7; managerial accounting)

• Produce: each promotion’s months and scope, and its volume test
• Tool: PivotTables of ListAmount by scope and month, with STDEV.S
• Revisit: Chapter 7’s expectations, and Tutorial 7.3’s look at orders
• Check: each difference is judged against the scope’s normal variation

Find the months each promotion actually ran from its lines’ order dates, and compare them with its recorded dates. Measure volume at list amount, and say why. Compare each promotion’s months with the average of the other months of its year and with the same months a year earlier. Then test the claim about new customers from each customer’s first order, and say what the first months of the data cannot tell you. The comparisons describe the orders, so they cannot show what would have been ordered without a promotion.

Model the proposed calendar

Requirement 4 · Model the proposed 2027 calendar (Chapter 7; managerial accounting)

• Produce: break-even lifts, a data table, a Goal Seek discount, a cost
• Tool: Tutorial 7.3’s model over the Parameters sheet
• Revisit: Chapter 7’s contribution model, data tables and Goal Seek
• Check: you state the volume basis, and why it is not the budget’s

Extend the model to the three proposed promotions, one column each, from their latest-year billed lines. Build the data table of the Furniture promotion’s contribution over discount rates and increases in volume, and use Goal Seek for the discount at which your largest measured increase would just pay, a sensitivity rather than a recommended rate. Show how the budgets planned promotions, and estimate the calendar’s cost against a budget without them, by quarter, as a sensitivity on the latest year’s billed volume and mix.

Check your understanding

Does it raise a promotion’s break-even lift?

1. A deeper discount
2. A higher standard cost per unit
3. A higher commission rate
4. More units sold before the promotion

Ask students to reason from Tutorial 7.3’s model: the lift is the extra volume the discounted margin needs to earn what the full margin earned.

Each change moves the lift, or not

1. Yes: each unit earns less during the promotion
2. Yes: the margin left after the discount shrinks faster
3. Yes, a little: commission takes a share of every price
4. No: the lift is a share of the volume, whatever its size

The break-even lift is the margin before the discount divided by the margin after it, less one. Computing it for each proposed promotion is the work of Requirement 4.

Test the controls over promotions

Requirement 5 · Test the controls over promotions (Chapter 8; auditing)

• Produce: the four tests, the likely causes, and the dispositions
• Tool: custom columns and PivotTables on OrderLines
• Revisit: Exercise 8.6’s pricing tests, and Chapter 8’s dispositions
• Check: each exception has evidence from outside the record it tests

Test that each promotion line was ordered within the recorded dates, falls within the scope and carries the promotion’s rate. For completeness, start from all order lines, because every line in scope during the promotion should carry it. Where recorded dates fail, look for other evidence of when a promotion was meant to run, keep the result on the recorded dates, and label a test on other dates provisional. Investigate lines outside scope in the item master, review who approved promotions and overrides, and list the conditions for running any promotion.

Check your understanding

A weakness in design, or a failure in operation?

1. Nothing requires a second approval before a promotion starts
2. A required approval is given after the promotion ended
3. The system has no required promotion-tag validation before a discount is accepted
4. A monthly exception report exists, but nobody reviews it

Ask students whether the control is missing or badly designed, or whether a sound control did not work as intended, as Chapter 8 defined a control deficiency.

Design or operation decides the fix

1. Design: the control does not exist
2. Operation: the control exists but did not work in time
3. Design: the system allows what the control should prevent
4. Operation: the report is sound, but it is not used

A design weakness needs a new or changed control; an operating failure needs the existing control to be performed. Requirement 5 asks you to make this call provisionally for each exception the case’s own tests find, and to name the evidence that would confirm it.

Write the decision memo

Requirement 6 · Write the decision memo

• At most two pages, to the controller, in three sections
• Financial reporting: the cost, how it is recorded, when it reaches results
• Managerial accounting: the effect on volume, break-even, and the cost
• Auditing: the exceptions, their dispositions, and the conditions to run

The managerial section also covers new customers and any alternative worth testing. Close the memo with your recommendation on the proposal, which the controller will take to the chief financial officer.

Before you submit

• The workbook refreshes, and its Documentation sheet is complete
• Every control total agrees, and every parameter is a named cell
• The appendix holds the five exhibits the case lists
• The memo keeps to its length, and ends with a recommendation

These are self-checks, not answers. A reviewer should be able to start from any number in the memo and find the query or PivotTable behind it.

Looking Ahead

From workbooks to queries and reports

• Much of this case brought tables together and summarized them
• Part III asks the same kinds of questions of the database with SQL
• A join and a grouped query do the work of a merge and a PivotTable
• Part IV turns evidence like yours into reports management explores

The case’s work by month and by scope is the kind of question SQL answers directly against the database, and the kind of evidence Power BI lets management explore for itself.

About these slides

These slides accompany Accounting Analytics: An Integrated Approach, licensed under CC BY-SA 4.0. Every requirement shown comes from the case:

https://aa.accountinganalyticshub.com/cases/part-2-case.html

Students can find the full case, with every requirement and its tasks, in the online book.