Comprehensive Case: Planning the Plant’s Hours for 2027
This case closes Part III. It asks you to use the four SQL chapters together on the question the controller must settle before the year’s books are closed: what to do about the plant’s hours. You will build a documented script and profile the records it relies on, as in Chapters 9 and 10. You will compare and summarize those records with the CASE expressions, window functions, and views of Chapter 11, and you will test them as the internal auditors did in Chapter 12. The Part II case ended with a recommendation on promotions. This one ends with a plan for the plant and the entries that close its year.
The case works in CharlesRiver_Work.sqlite, and every requirement can be completed with techniques you have already practiced. Save all your queries in one script, Case.sql.
The Situation
It is early 2027, and the internal auditors have reported on the manufacturing variance (Chapter 12). The plant’s records reconcile, and the pay agrees with the hours at the rates on file. The growth in overtime, though, sits on surge days, days on which every hourly manufacturing employee clocked the same hours, and the timekeeping records alone cannot show that those hours were worked. The audit committee has asked management to respond, and the chief audit executive will review the response.
You are the staff accountant again, as in Chapters 9 to 11. The controller asks you for a decision memo before the fiscal 2026 statements are final and the 2027 plan is set. She wants to know four things:
- whether records outside the time clock support the surge-day hours;
- what the extra hours produced, and what they cost;
- what the plant should plan for 2027, in hours, overtime, and conversion cost, and whether the standards should change;
- what the fiscal 2026 statements need before they are final.
She adds a caution. The records show hours that cannot be supported, not hours that were never worked. Each employee was paid on their own register, and time entered in bulk for work that was really done is still a possible explanation, so the memo should call the hours unsupported until the evidence says more.
The Data You Will Use
Work in CharlesRiver_Work.sqlite, the working copy of Chapter 9, in a new editor tab. Start Case.sql with a header comment like the one of Audit.sql, and put each query under a two-line comment that names what it tests, then its population and the result you expect. The case uses tables that the chapters did not query:
- TimeClockPunch, the badge punches behind each time clock entry: clock in, clock out, and the start and end of a meal break;
- EmployeeShiftRoster, the shift each employee was scheduled to work each day, with its scheduled times and hours, its status, and who created it and when, and ShiftDefinition, which names the shifts;
- EmployeeAbsence, the absences recorded for each employee;
- AttendanceException, the exceptions the timekeeping system raised for review, with their type, status, and reviewer;
- WorkCenterCalendar, each work center’s working days and available hours, with the reason for any exception, such as maintenance or reduced capacity;
- RoughCutCapacityPlan, the planned load and the available hours of each work center by week;
- FixedAsset, the equipment of the company, with the account its depreciation is charged to.
The case also uses tables you know from the chapters: TimeClockEntry, OvertimeApproval, LaborTimeEntry, WorkOrderOperation, ProductionCompletion and its lines, Item, Employee, CostCenter, the payroll tables, GLEntry, Account, and JournalEntry. Reuse what you have already built: the view MonthlyLaborEfficiency from Tutorial 11.3, and tests L5 and L6 of Audit.sql from Tutorial 12.1. If you don’t have them, Companion Files offers Setup_Part3_Views.sql, which recreates the view in your working copy, and Audit.sql as it stands at the end of Chapter 12.
Requirements
Requirement 1: Build the case script and its populations (Chapters 9 and 10).
- Start Case.sql with its header, and give every query the two-line comment of the audit query library.
- Profile TimeClockPunch, EmployeeShiftRoster, AttendanceException, and WorkCenterCalendar with the tests of Table 10.3: the rows and dates each covers, the values of its code columns, the columns that are not always filled in, and any key that appears more than once.
- Test the links that the later requirements rely on: every punch to its clock entry, every clock entry to its roster, and every attendance exception to its clock entry, comparing the employee and the date on each side. Give each exception a disposition, but do not pursue the isolated ones further.
- Save the surge days as a view named SurgeDays, with the definition of the SurgeDays CTE in test H9 of Tutorial 12.3, and reconcile it to the surge days and clock entries that Tutorial 12.3 found. Then save a second view with one row for each hourly manufacturing clock entry and a DayType column of Surge or Ordinary, for the requirements that follow.
Requirement 2: Test whether other records support the surge days (Chapters 10 to 12; auditing).
- Compare surge days with ordinary days for the hourly manufacturing employees, with conditional aggregation and a window function: the employees who clocked in each day, the number of different clock-in times, the largest group of employees sharing one clock-in time, the share of entries with meal punches, and the rostered hours against the hours clocked.
- Reperform the attendance-exception control. Measure each entry’s minutes of clock time before and after its roster, in whole minutes, infer the rule that the Off Shift Clocking exceptions the system raised point to, and apply it to the surge-day entries. Decide whether the control failed in its design, in its operation, or both, and say what the stored exceptions cannot tell you about how the rule is set up.
- Test the reasons behind the overtime against the plant’s own plans: the calendar status of each surge day, the planned load of the weeks with surge days against other weeks in RoughCutCapacityPlan, and whether the operation that each surge-day overtime approval cites was still open on the work date. This tests the authorization of the overtime, where test H9 tested the labor time recorded against operations.
- Summarize your tests in a corroboration matrix: for each, whether it supports the hours, contradicts them, or says nothing, and whether its record is independent of the Production Manager and of the timekeeping system. Name two explanations that fit the records, and the evidence outside the database that would tell them apart.
Requirement 3: What the hours produced and what they cost (Chapters 10 and 11; managerial accounting).
- Compare output with hours, first by year and then by week. For each Monday week through the week of December 7, 2026, total the hours clocked and the standard hours of the units completed, and group the weeks by their number of surge days. Add the average output of the six weeks that follow each week, with a window frame, and count the weeks each average covers. State what the weekly comparison can and cannot show, given how completions are dated.
- Measure how much of the plant’s regular capacity the ordinary days used: the hours each employee clocked per day, and the straight-time hours left unused, against the overtime worked on surge days.
- Put a cost on the surge-day overtime in two ways, each with the employer’s payroll taxes and benefits: the premium alone, if the hours were needed but could have been worked at straight time, and the full cost of the overtime, if they were not needed. Use the base rates in the Employee table, as test H5 did.
- Bridge the growth of account 5080 from fiscal 2024 to fiscal 2026 on the ledger. First split it into the ledger lines that make it up: the payroll charged to account 1090, the journal entries charged to it, the standard cost that completions released, the change in the balance of 1090, the material variance, and the postings made directly to 5080. Then explain the payroll line with three measures: the calendar, the straight-time cost of the hours beyond the 2024 hours per standard hour, and the growth of the surge premium. Explain why the three measures do not add up exactly to the payroll line.
Requirement 4: Plan the 2027 hours and conversion cost (Chapter 11, with the planning of Chapter 7; managerial accounting).
- Put the plan’s assumptions in a one-row Params CTE, and record the value, source, and effect of each in the script’s comments, as Chapter 7 documented a model:
- the volume, from the 2026 output, and why not from the budget or the demand forecast;
- a target of hours per standard hour;
- the straight-time rate and the burden;
- the working days, taken from 2026, because the calendar stops in March 2027;
- the run rate of the Factory Overhead journal entries;
- the 2027 depreciation of the manufacturing equipment in FixedAsset, after you reproduce from FixedAsset the depreciation that fiscal 2026 charged to account 1090.
- Compare the hours the plan needs with the plant’s regular capacity, and decide how much routine overtime the plan should allow.
- Build the plan’s conversion cost from hourly labor, the salaried manufacturing staff, factory overhead, and depreciation, and compare it with the standard conversion cost of the planned output. Put three ratios in a Scenarios CTE built with UNION ALL: standard hours, the 2024 level, and the 2026 level with its overtime. Show the expected variance of each.
- Explain the variance that remains even at standard hours, and decide whether the standards should change: the direct-hour standards and the overhead rates.
Requirement 5: Close the fiscal 2026 statements (Chapters 11 and 12; financial reporting).
- Estimate the payroll cost of the work of December 14 to 31, 2026, by cost center. Take the cost per working day of the last eight processed pay periods, counting working days in WorkCenterCalendar, and multiply it by the working days left in the year. Write the adjusting entries, with the accounts that the payroll postings of each cost center use, and explain where the manufacturing part ends up.
- The year-end before was not accrued either. With the same working-day method, measure the 2025 work paid in fiscal 2026 from the registers of the pay periods that cross the year-end. Show the effect of recording both accruals on fiscal 2026 income and on the liability at December 31, 2026, say how you treated the manufacturing part in those figures, and explain why they differ. Decide what to do about the balance that account 2030 has carried since the opening entry.
- Explain where the cost of the hours beyond the work belongs. The inventory standard of U.S. GAAP (FASB Accounting Standards Codification Topic 330) allocates fixed production overhead to inventory on the normal capacity of the production facilities, and charges abnormal amounts, such as idle facility expense, to the period in which they are incurred. Consider what raising the standards to absorb 2026’s actual conversion cost would do to the finished goods inventory.
- Rerun tests L5 and L6 of Audit.sql for the revenue cutoff. Summarize the proposed adjustments and their effect on fiscal 2026 net income, starting from the net income of Chapter 6’s income statement, and on the balance sheet, and judge whether they are material.
Requirement 6: Write the decision memo. Write a memorandum of no more than two pages to the controller, organized in the order of her questions:
- Auditing: what the records outside the time clock show about the surge-day hours, how independent they are, which explanations remain open and what evidence would settle them, and what the attendance-exception control needs.
- Managerial accounting: what the extra hours produced and what they cost, the bridge of the variance, and the 2027 plan with its assumptions and the overtime it allows.
- Financial reporting: the proposed adjustments to the fiscal 2026 statements, their effect, why identified abnormal costs are expensed, and why normal conversion costs need a separate inventory-allocation analysis.
Close the memo with your recommendation.
Deliverable. A decision memorandum of no more than two pages, with a one-page appendix of exhibits: the corroboration matrix, output against hours, the bridge of the variance, the 2027 plan against the status quo, and the proposed adjustments. Each exhibit shows only the results that carry the decision, and the full tables stay in Case.sql. Submit Case.sql as well, with its header complete and a comment above every query.
What a Strong Submission Includes
- A script that another person could rerun: a documented header, a comment on every query, views for the populations it reuses, and a reconciliation of each population before it is tested.
- Evidence graded for independence as well as agreement, with honest limits on what the output comparison can show.
- A bridge of the variance that sums to the ledger, and measures that explain its payroll line on stated calendars.
- A plan with its assumptions in one place, a capacity check, and scenarios that show what each assumption is worth.
- Adjusting entries that separate the effect on income from the effect on the balance sheet, and a clear reason why identified abnormal costs are expensed while normal conversion costs need a separate allocation analysis.
- A memo written for the controller: concise, organized by perspective, careful with the word “unsupported”, and ending with a recommendation she can act on.