Comprehensive Case: Getting the Data Ready for the Furniture Margin Question
This case closes Part I. It asks you to use the three chapters together on one realistic assignment: preparing the data for an analysis before the analysis itself begins. You will frame the question with the concepts from Chapter 1, test the data with the techniques from Chapter 2, and navigate the database with the keys and relationships from Chapter 3. The analysis that answers the question comes in Part II, where you will learn the Excel techniques it requires. The work you do here decides whether that analysis can be trusted.
The case uses CharlesRiver_Work.xlsx, the working copy of the Part I tutorials. It combines the filters, lookup functions, and PivotTables you have already met in ways the tutorials did not work through, such as a lookup that brings each invoice’s date onto its lines, so plan your worksheets before you start.
The Situation
You are the staff accountant from the opening scenarios of Chapters 1 and 3. The controller’s question is still open: why did gross margins on the Furniture product line decline in the most recent quarter? Before the team spends time on a full diagnostic analysis, the controller wants a short data readiness memo from you. She wants to know which data answers the question, whether that data can be trusted, and anything that could make an analysis of it misleading. She also reminds you that the quarter in question ends on the last day of the fiscal year, when the books are closed.
The Data You Will Use
You will work mainly with these worksheets in CharlesRiver_Work.xlsx:
- Item, which identifies each product’s item group and the accounts its revenue and cost post to;
- SalesInvoice and SalesInvoiceLine, which record what was billed and when;
- Shipment and ShipmentLine, which record what was shipped and its standard cost;
- GLEntry, Account, and JournalEntry, which record the postings, the chart of accounts, and the manual journal entries.
The GLEntry worksheet is large, so summarize it with filters and PivotTables rather than copying formulas down every row, as you did in Tutorial 2.2.
Requirements
Requirement 1: Frame the question (Chapter 1). Restate the controller’s request as two questions: a descriptive question about what happened to the Furniture margin, and a diagnostic question about why. Identify the type of analytics each question calls for. Then list the stages of the analytics workflow and, for each one, say what this case completes or begins and what it leaves for Part II.
Requirement 2: Map the data (Chapter 3). Identify where Furniture revenue and Furniture cost are recorded.
- In the Item worksheet, find the column that identifies Furniture items and the columns that record the accounts their revenue and cost post to. Look up those accounts in the Account worksheet.
- Sketch the join path from SalesInvoiceLine to SalesInvoice and to Item, from ShipmentLine to Shipment and to Item, and from GLEntry to Account. For each join, name the key and state the cardinality.
- Examine where returns are recorded in the ledger. Identify the part of a return that posts to a Furniture account and the part that posts to an account shared by every product line, and explain what this means for a product-line margin.
Requirement 3: Test the data (Chapter 2). Decide which data quality findings matter for this question.
- Determine the most recent complete quarter in the data. Look at the edges of the extract, as you did in Tutorial 2.2, and explain why the latest periods in the ledger are not the quarter to analyze.
- Review the findings from the Chapter 2 tutorials: invoices dated before their order’s first shipment, customers that share a name, and list prices that are blank by design. For each one, decide whether it could distort a comparison of the Furniture margin between the two most recent complete quarters, and explain why.
Requirement 4: Reconcile the sub-ledger to the ledger (Chapters 2 and 3). For each of the two quarters:
- Total the Furniture revenue on the invoice lines. You will need the InvoiceDate from SalesInvoice and the ItemGroup from Item for each line. Compare the total with the credits to the Furniture revenue account in GLEntry that come from sales invoices.
- Total the standard cost on Furniture shipment lines shipped in the quarter, using the ShipmentDate from Shipment and the ItemGroup from Item, and compare it with the Shipment debits to the Furniture COGS account.
- Explain what agreement tells you, and identify the other source document types that post to the Furniture COGS account.
Requirement 5: Find what could mislead (Auditing). Using GLEntry alone, calculate the Furniture gross margin percentage for each of the two quarters from all the rows posted to the Furniture revenue and COGS accounts. Look at the revenue and cost totals behind each percentage, not only the percentage itself. Then examine the source document types behind the most recent quarter’s totals, find the entry responsible for what you see, and identify its EntryType in the JournalEntry worksheet. Explain why this entry must be excluded from a quarterly margin analysis, recompute both margins, and state what they show.
Requirement 6: Write the data readiness memo. Write a memorandum of one to two pages to the controller. Organize it in three sections:
- Financial reporting: the reconciliation of Furniture revenue and cost between the sub-ledger and the ledger, and what the cutoff findings from Chapter 2 do and do not mean for this quarter.
- Managerial accounting: the margin measure you recommend (the accounts, the rows to include and exclude, and the quarters to compare), what the measure shows, and the product-level data available for the diagnostic analysis in Part II.
- Auditing: the evidence on whether the data can be relied on for this question, the closing-entry problem and how to avoid it, and any limitation, such as returns recorded in an account shared by every product line, or revenue dated by the invoice and cost by the shipment.
Close the memo with a recommendation for the analysis the team should perform next.
Deliverable. A one-to-two page data readiness memorandum, supported by a one-page appendix with your data map (the join-path sketch from Requirement 2) and your reconciliation table (Requirement 4).
What a Strong Submission Includes
- A clear distinction between the descriptive and the diagnostic question, and between what this case settles and what Part II must analyze.
- A data map that names the keys and the cardinality of every join, and that translates AccountIDs into account numbers.
- A justified choice of the two quarters to compare, based on the edges of the extract.
- Reconciliations that agree to the cent, with an explanation of what agreement shows and what it does not.
- Recognition that a reasonable-looking margin percentage can hide totals that make no sense, and a correct treatment of the year-end closing entry.
- A memo written for the controller: concise, organized by perspective, and ending with a recommendation.