Comprehensive Case: Deciding on the 2027 Promotion Calendar

Case slides View slides Download PowerPoint

This case closes Part II. It asks you to use the five Excel chapters together on a decision that management has to make: whether Charles River should keep running sales promotions. You will build and document a workbook with Power Query, as in Chapters 4 and 5. You will describe the promotions’ cost and reconcile it to the ledger with the techniques of Chapter 6, and test and model their effect on sales with the tools of Chapter 7. Finally, you will test the controls over them 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 that someone will act on.

The case uses a new workbook that reads from CharlesRiver.xlsx, so the analysis and audit workbooks from the chapters stay as they are. Every requirement can be completed with techniques you have already practiced.

The Situation

It is early 2027. 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 approved 2027 budget includes no promotions. The sales team has now come back with a broader proposal. In each of the last three years, Charles River ran three promotions: one on a collection of items in the spring, one on an item group in the fall, and one on a customer segment in November. The team proposes to repeat the 2026 calendar in 2027 at the same rates:

the Harbor Collection Promotion, 8 percent off the items of the Harbor collection in March and April;
the Furniture Seasonal Promotion, 10 percent off every Furniture item in September and October;
the Design Trade Customer Promotion, 12 percent off every order from Design Trade customers in November.

The team argues that the Chapter 7 model looked at one promotion in one year, and that promotions bring in new customers. The controller wants a decision memo from you before the proposal goes to the chief financial officer. She wants to know four things:

what three years of promotions have cost, and where that cost appears in the financial statements;
whether any of the promotions brought in sales that would not otherwise have been made;
what the proposed calendar would do to the 2027 plan;
whether the controls over promotions are good enough to run one.

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.

The Data You Will Use

Create a new workbook named Charles River Promotion Case.xlsx in the same folder as CharlesRiver.xlsx. Import these Tables with Power Query (Data > Get Data > From File > From Excel Workbook), using the query names given:

Promotions, from T7_PromotionProgram: the promotions, with their scope, rate, effective dates, and approval;
Orders (T9_SalesOrder), Invoices (T16_SalesInvoice), Items (T44_Item), and Customers (T4_Customer), used as lookups;
OrderLines, built from T10_SalesOrderLine and merged with the lookups and Promotions: what customers ordered, and when;
BilledLines, built from T17_SalesInvoiceLine with the invoice date from Invoices: what was billed and recognized as revenue;
RevenuePostings, from T3_GLEntry, with account numbers from Accounts (T1_Account): the revenue in the ledger;
BudgetPrices, from T77_BudgetLine: the prices that each year’s budget planned;
Overrides (T8_PriceOverrideApproval) and Employees (T74_Employee): the price override requests and the people who made and approved them.

Follow the Power Query conventions of Part II:

check the data types in each Changed Type step: Discount in OrderLines and BilledLines must be a decimal number, as in Tutorial 4.1 Step 6;
expand merged columns without the prefix;
import T3_GLEntry with Transform Data, and filter it before anything is loaded;
load the queries that only feed others as connections only, and the others as Tables on new worksheets, as in Tutorial 8.1, Step 1;
rename each loaded worksheet after its query.

Watch out

PromotionProgram has three columns with the same names as columns of Item and Customer: ItemGroup, CollectionName, and CustomerSegment. Rename them in the Promotions query, for example to PromoItemGroup, PromoCollection, and PromoSegment, before you merge it into OrderLines, because two columns in one query cannot share a name. Keep the dates apart, too. A promotion applies to a line by its order date, so questions about timing and volume use OrderLines. Billed revenue and discounts follow the invoice date in BilledLines, as you saw in Chapter 5. The ledger records each invoice on its posting date, which can fall in a later period, as Exercise 6.1 showed, so a tie to the ledger reconciles the two dates.

Requirements

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

Start the workbook with a Documentation sheet laid out as in Tutorial 4.3. Add a Parameters sheet that holds the proposal’s three discount rates and the 2026 commission rate you calculated in Tutorial 7.3 as named cells (HarborRate, FurnitureRate, DesignTradeRate, and CommissionRate), with the inputs in blue.
Build the queries listed above. In OrderLines, bring in the OrderDate and CustomerID from Orders, and the ItemCode, ItemName, ItemGroup, CollectionName, and StandardCost from Items. Add the CustomerSegment from Customers, and the scope, rate, and effective dates from Promotions. Then add columns for OrderYear and OrderMonth, and three amounts: ListAmount (Quantity times BaseListPrice), GrossAmount (Quantity times UnitPrice), and DiscountAmount (GrossAmount times Discount).
Give BilledLines the same three amounts, the InvoiceDate with its year and quarter, and the ItemGroup and StandardCost of each item.
Check the queries against their sources before you use them. Compare the row count of each loaded query that keeps every row of its source Table with that Table, and state what the filters of RevenuePostings and BudgetPrices keep. Compare the total LineTotal of OrderLines with the total OrderTotal of Orders. Confirm that no line without a PromotionID carries a discount. Record the checks on the Documentation sheet.

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

Build a PivotTable from BilledLines of the promotional discount and the number of lines by promotion and invoice year, and chart the yearly totals. Express each year’s discount as a percentage of that year’s billed revenue.
For fiscal 2026, build a gross-to-net schedule by item group. It runs from the list amount, through the price reductions made before the promotions (by the price lists and overrides) and then the promotional discounts, to the net amount billed.
Tie the net amount billed in fiscal 2026, by invoice date, to the revenue that RevenuePostings records from sales invoices by posting date, and explain any difference. Then find the account in the chart of accounts where discounts could be recorded, and explain what the income statement shows, and does not show, about the cost of promotions.
Show in which quarters of fiscal 2026 the discounts of the three promotions in the proposal were billed.

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

For each promotion, find the months in which it actually ran from the order dates of its lines, and compare them with its recorded effective dates. Identify its scope: an item group, a collection, or a customer segment.
Measure the volume ordered in each promotion’s scope, month by month, with the ListAmount of OrderLines. Explain why the list amount measures volume better than the net amount billed.
Compare the volume in each promotion’s months with two expectations from Chapter 7. The first is the average of the other months of the same year, scaled to the promotion’s number of months. The second is the same months a year earlier, where the data has them. Use the standard deviation of the other months to judge whether a difference is larger than the scope’s normal variation from month to month. These comparisons describe the orders and cannot show what would have been ordered without a promotion, so say what further evidence would.
Test the sales team’s claim that promotions bring in new customers: find each customer’s first order in the data, and whether it included a promotion line. Consider what the first months of the data can and cannot tell you.

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

Extend the contribution margin model of Tutorial 7.3 to the three promotions in the proposal, with one column for each. Use the units, price before discount, and standard cost of each promotion’s 2026 billed lines, together with the rates and the commission rate on your Parameters sheet. Find each promotion’s break-even lift, the increase in volume at which it just pays for itself.
Build a data table of the Furniture promotion’s difference in contribution for discount rates of 5, 8, 10, and 12 percent and increases in volume from 0 to 40 percent in steps of 5 points. Then use Goal Seek to find the Furniture discount at which the largest increase you measured in Requirement 3 would just pay for the promotion. Label the result a sensitivity, because that increase may come from another scope and year, and it is an observed difference rather than an effect of a promotion.
Show from BudgetPrices how the budgets for 2024 through 2026 planned their promotions, and whether the 2027 budget plans any. Compare each month’s budgeted revenue with its budgeted quantity at list price.
Estimate what the proposed calendar would cost Charles River in 2027 against a budget without promotions, and in which quarters the cost would appear. Present it as a sensitivity on stated assumptions. State the volume and mix you base the estimate on, note any promotion orders not yet billed, and explain why you did not use the quantities in the budget.

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

Test four conditions in OrderLines, and count the exceptions by promotion. The first three test every promotion line, and the fourth starts from all order lines:
- the order date falls within the promotion’s recorded effective dates;
- the item or the customer falls within the promotion’s scope;
- the discount equals the promotion’s rate;
- for completeness, every line in a promotion’s scope that was ordered during the promotion carries it, so a line with a blank or a different PromotionID is an exception.
For the promotions whose recorded dates fail, look for other evidence of when they were meant to run, such as the months in which their lines were ordered and the months in which the budgets planned their discounts. Say what kind of error the exception most likely shows, and what evidence would confirm it. Keep the completeness result on the recorded dates, even if those dates hold no line to test, and label any test on the months the evidence suggests as provisional.
Investigate each line outside its promotion’s scope in the item master, and say whether the discount or the master record is more likely wrong, and which record would settle it.
Review who approved each promotion and when, and who requested and approved the price overrides in Overrides. Count the lines that received a promotional discount on top of an approved override price, and state the question this raises for management.
Record a disposition for each exception. Classify each one provisionally as a possible weakness in the design of a control or a possible failure in its operation, and name the evidence that would confirm the classification. List the conditions that should be met before any promotion runs in 2027.

Requirement 6: Write the decision memo. Write a memorandum of no more than two pages to the controller. Organize it in three sections:

Financial reporting: what the promotions have cost, how that cost is recorded and why the ledger does not show it, and when a promotion’s discounts reach the quarterly results.
Managerial accounting: what the evidence shows about the promotions’ effect on volume and on new customers, the break-even increases the proposed calendar would need, what it would cost against the 2027 budget, and any alternative worth testing.
Auditing: the exceptions in the controls over promotions, their dispositions, and the conditions for running any promotion.

Close the memo with your recommendation on the proposal.

Deliverable. 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.

What a Strong Submission Includes

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 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.

Connecting the Dots

Much of this case went into bringing tables together and summarizing them by month and by scope. Part III asks the same kinds of questions of the database directly with SQL, where a join and a grouped query do the work of a merge and a PivotTable. Part IV then turns evidence like yours into reports that management can explore for itself.