2. Review Design Work and Billing

The design lead is preparing a quarterly review with the controller. Several engagements overlap, and a busy month does not necessarily mean that the same month contains all related invoices. You will explain the relationship before suggesting where the lead should spend follow-up time. Predict which date describes work and which date establishes billing.

Basic · 2 sessions of 60–90 minutes

Prepare

• 1. Meet the Records
• 2. Read an Order
• 3. Connect the Records
• 7. Follow Design Time into Billing
• 12. Turn Records into a Conclusion

Readiness

• Complete your chosen Excel or SQL setup. Preserve source row keys and check row counts, unique keys, and amounts after descriptive joins.
• Before starting, state the cohort, cutoff, row key, independently aggregated measures, and one evidence limitation.
• Distinguish work dates, approval dates, billing coverage, and invoice dates when summarizing hours and costs at engagement/month grain.

Tools

Complete either Excel or SQL. Both routes include worked checks.

Produce

An engagement-period reconciliation, an hours/billing/cost bridge, and a design review memo.

The worked core teaches the method. The independent challenge asks you to apply it and defend your own conclusion.

Worked scope. Service work dated January 1–March 31, 2024: 204 time entries across 21 engagements. Begin with engagement 2, then use the whole defined population. Select billing periods by their work coverage and retain the invoice dates separately.

Choose Excel or SQL. The site retains your selection between lessons and cases.

Preparation: 1. Meet the Records, 2. Read an Order, 3. Connect the Records, 7. Follow Design Time into Billing, 12. Turn Records into a Conclusion. Use the Excel setup or SQL setup for your chosen tool.

Ready to begin:

• Complete your chosen Excel or SQL setup. Preserve source row keys and check row counts, unique keys, and amounts after descriptive joins.
• Before starting, state the cohort, cutoff, row key, independently aggregated measures, and one evidence limitation.
• Distinguish work dates, approval dates, billing coverage, and invoice dates when summarizing hours and costs at engagement/month grain.

Key Data Sources

Each source below has a distinct row meaning. Import only the fields and populations requested in each stage. Working tables default to the source name plus Data unless a stage assigns a more specific name.

Source	Published Excel Table	First working name
ServiceEngagement	T11_ServiceEngagement	ServiceEngagementData
ServiceTimeEntry	T13_ServiceTimeEntry	ServiceTimeEntryData
ServiceBillingLine	T18_ServiceBillingLine	ServiceBillingLineData
SalesInvoiceLine	T17_SalesInvoiceLine	SalesInvoiceLineData
SalesInvoice	T16_SalesInvoice	SalesInvoiceData

Import, merge, group, and retain evidence

Use Microsoft 365 desktop and a separate analysis workbook. Keep the released workbook unchanged. The table above names the exported Excel Tables, which differ from worksheet names. For first-time setup, follow Set Up Excel.

1. Import: On Windows, choose Data → Get Data → From File → From Workbook. On Mac, choose Data → Get Data (Power Query) → Excel workbook. Select the published workbook, choose the named table in Navigator, then Transform Data. Rename the query in Query Settings. Set identifiers to Whole Number, dates to Date, and amounts/hours to Decimal Number. Keep reference numbers as text. Apply the stage’s filters before loading large tables.
2. Merge. In the Power Query editor choose Home → Merge Queries → Merge Queries as New. Select the two queries and matching key columns. For multiple keys, select them in the same order with Ctrl on Windows or Command on Mac. Use the join type specified in the stage. Expand only the required fields using the button on the merged column. Clear Use original column name as prefix when a stage uses bare column names, and rename summary columns exactly as specified. Compare row counts before and after expansion. A lookup requires a unique key, while a one-to-many relationship needs a merge or a separate aggregate.
3. Group: Choose Home → Group By. Use Advanced for multiple keys or totals. Enter each output name, operation (Sum or Count Rows), and source column explicitly. Group each detail source before joining independent totals. Right-click a query and choose Reference when building a new summary from its existing filters.
4. Load. Load the final query as a table on a new worksheet. On Windows, Close & Load To can leave staging queries as connection-only. On Mac, use the editor’s Close & Load for the final result. Select the loaded table and set its Table Name to the name used in the formulas. Keep source queries in the editor so filters and relationships remain inspectable. Microsoft documents the Mac editor and loading workflow and matching multiple merge keys.
5. Summarize. Select a working table, choose Insert → PivotTable, and place the specified fields in Rows and Values. In Value Field Settings, count identifiers and sum amounts. Ordinary SUM includes filtered-out rows. Use SUBTOTAL(109,table[column]) for a visible sum or SUMIFS for an explicit population. Refresh queries and PivotTables after changing a filter.
6. Retain. Save each session’s workbook and review notes. Record the source file, cutoff, key fields, row meaning, and checks. SQL learners save each supplied statement and its result from DB Browser’s Execute SQL tab. Run one statement at a time using the cases' read-only queries. Follow Save the Result Not Just the Query to retain and verify the exported rows.

Copy one formula at a time. For a block labeled Excel, create the named column and paste into its first data cell, or use the named worksheet cell. If a table formula does not fill down automatically, copy it through the remaining data rows. Paste a block labeled Power Query into Add Column → Custom Column in the query editor without a leading equals sign. Resolve No match results before using a lookup in another calculation.

The examples use English function names and comma separators. Regional Excel installations may use translated names or semicolons with the same fields and criteria. Investigate a blank relationship or date before converting it to zero.

Session 1 — Establish the work and its terms

Create Case02.xlsx or Case02.sql. Your Review note should distinguish work performed, approved billable hours, invoiced amounts, and cost evidence. Each has a different purpose.

Who turns time into a customer bill?

Reading guide: Rows show proposed teaching responsibilities. They do not establish employee access independence or control operation.

Expand the diagram for a larger view with zoom and pan controls.

Download editable Draw.io

Read the figure in words

• Design lead establishes engagement terms and staffing.
• Staff record billable and non-billable time; approval supports billing.
• Billing connects covered time to an invoice; the controller compares measures.

Conceptual teaching diagram. Rows show proposed teaching responsibilities. They do not establish employee access independence or control operation.

1.1. Read engagement terms

Predict before you run: Does a planned-hours figure establish how much work actually occurred?

Excel

Named inputs: ServiceTimeEntryData ← ServiceTimeEntry (import here; filter Q1 2024 into TimeData), Engagements ← ServiceEngagement (import here).

Produced outputs: TimeData: 204 time-entry rows with HourlyRate formula, SelectedEngagements: unique selected engagements.

1. Import ServiceTimeEntry as ServiceTimeEntryData; reference it as TimeData and filter WorkDate from January 1 through March 31, 2024.

2. Load as TimeData.

3. Import ServiceEngagement as Engagements.

4. Filter TimeData to ServiceEngagementID 2 and inspect its dates before clearing the filter.

5. Add a column named HourlyRate to TimeData and paste this formula into its first data row; copy it down through the table column if Excel does not fill it automatically:

   Excel — TimeData[HourlyRate]

   =XLOOKUP([@ServiceEngagementID],Engagements[ServiceEngagementID],Engagements[HourlyRate],"No match",0)

6. The final 0 requires an exact match.

7. Investigate No match rather than treating it as a zero rate.

8. In Power Query create a Reference of the filtered time query as SelectedEngagements, keep ServiceEngagementID, remove duplicates, and merge with Engagements; load as SelectedEngagements.

9. The reference reads the imported query, so worksheet formula columns are not required for this selection.

SQL

Run each statement separately in the SQL editor. Save its text and result with the stage title. Read the result at the grain described in the question and compare the checkpoint before continuing.

c02-terms.sql

SELECT ServiceEngagementID,
       CustomerID,
       StartDate,
       EndDate,
       PlannedHours,
       HourlyRate
FROM ServiceEngagement
WHERE ServiceEngagementID IN (SELECT ServiceEngagementID
FROM ServiceTimeEntry
WHERE WorkDate>='2024-01-01' AND WorkDate<'2024-04-01')
ORDER BY ServiceEngagementID;

Checkpoint. 21 engagement terms. Engagement 2 belongs to customer 30, has 69.59 planned hours, and an hourly rate of 210. Its start and end dates are January 12 and February 29.

The terms provide a rate and planning context. They do not replace recorded hours or establish whether time was approved.

If your result differs

A lookup is appropriate here because ServiceEngagementID is unique in the terms table. Confirm 21 distinct keys before merging.

Keep for the next stage: Save the selected engagement list and engagement 2’s terms.

1.2. Separate time, approval, and cost

Predict before you run: Will all first-quarter work be approved by the quarter end?

Excel

Named inputs: TimeData and its HourlyRate column (Session 1.1).

Produced outputs: TimeData: WorkMonth and ApprovedBillable columns, TimeByEngagementMonth: time/approval/cost PivotTable.

1. In TimeData add WorkMonth:

   Excel — TimeData[WorkMonth]

   =DATE(YEAR([@WorkDate]),MONTH([@WorkDate]),1)

2. Format WorkMonth as yyyy-mm.

3. Add ApprovedBillable; the numeric-date check prevents a blank approval from counting as approved:

   Excel — TimeData[ApprovedBillable]

   =IF(AND(ISNUMBER([@ApprovedDate]),[@ApprovedDate]<=DATE(2024,3,31)),[@BillableHours],0)

4. Insert a PivotTable with ServiceEngagementID and WorkMonth in Rows; Values = Count of ServiceTimeEntryID and sums of BillableHours, NonBillableHours, ApprovedBillable, ExtendedCost.

5. Rename the last measure RecordedTimeCost.

6. Clear worksheet filters before creating the PivotTable; its source should include all 204 imported rows.

SQL

substr(WorkDate,1,7) creates a month key from the stored ISO date. GROUP BY engagement and month keeps different engagements separate. CASE includes billable hours only when approval occurred by the cutoff.

Run each complete statement separately and retain its result before continuing.

c02-time.sql

SELECT ServiceEngagementID,
       substr(WorkDate,1,7) AS WorkMonth,
       COUNT(*) AS Entries,
       ROUND(SUM(BillableHours),2) AS BillableHours,
       ROUND(SUM(NonBillableHours),2) AS NonBillableHours,
       ROUND(SUM(CASE WHEN ApprovedDate<='2024-03-31' THEN BillableHours ELSE 0 END),2) AS ApprovedBillable,
       ROUND(SUM(ExtendedCost),2) AS RecordedTimeCost
FROM ServiceTimeEntry
WHERE WorkDate>='2024-01-01' AND WorkDate<'2024-04-01'
GROUP BY ServiceEngagementID,substr(WorkDate,1,7)
ORDER BY ServiceEngagementID,WorkMonth;

c02-cutoff.sql

SELECT COUNT(*) AS Entries,
       COUNT(DISTINCT ServiceEngagementID) AS Engagements,
       ROUND(SUM(BillableHours),2) AS BillableHours,
       ROUND(SUM(NonBillableHours),2) AS NonBillableHours,
       ROUND(SUM(ExtendedCost),2) AS TimeCost,
       SUM(CASE WHEN ApprovedDate IS NULL OR ApprovedDate>'2024-03-31' THEN 1 ELSE 0 END) AS NotApprovedAtCutoff
FROM ServiceTimeEntry
WHERE WorkDate>='2024-01-01' AND WorkDate<'2024-04-01';

Checkpoint. 33 engagement-month groups. Total billable hours 1,409.88; non-billable hours 73.62; recorded time cost 49,022.40. No selected entry remains unapproved at March 31. Engagement 2 has 32.67 billable hours in January and 36.92 in February.

An empty exception list is a finding about this criterion and scope. It does not justify inventing an approval problem. Non-billable time still consumes staff capacity and contributes to the recorded cost measure.

If your result differs

Use actual date types. A text date or an empty date treated as zero can distort the approval test.

Keep for the next stage: Save the 33-row summary and the approval cutoff check.

Session stopping point

Save terms, the 204-row source selection, and the engagement-month summary. Write what each total includes before adding invoice data.

Session 2 — Connect covered time to billing

A time row is not a billing row. Aggregate within the billing period before comparing hours to avoid repeating each invoice amount for every time entry.

How does a billing period connect time and invoice evidence?

Reading guide: Match engagement and the inclusive billing period, then aggregate approved billable time. This is distinct from a stored billing-line key on each time entry.

Expand the diagram for a larger view with zoom and pan controls.

Download editable Draw.io

Read the figure in words

• Time is identified by engagement and work date.
• A billing line supplies an inclusive start/end period and billed hours.
• SalesInvoiceLine identifies the invoice; InvoiceDate establishes billing timing.

Conceptual teaching diagram. Match engagement and the inclusive billing period, then aggregate approved billable time. This is distinct from a stored billing-line key on each time entry.

2.1. Build the period reconciliation

Predict before you run: What happens if you join all engagement time directly to every billing line?

Excel

Named inputs: ServiceBillingLineData ← ServiceBillingLine (import here; filter Q1 2024 into Billing), InvoiceLines ← SalesInvoiceLine and Invoices ← SalesInvoice (import here), TimeData (Session 1).

Produced outputs: Billing: billing-line detail with invoice date, covered hours, and approval comparison.

1. Import ServiceBillingLine as ServiceBillingLineData; reference it as Billing and filter BillingPeriodStartDate on or after January 1, 2024 and BillingPeriodEndDate before April 1, 2024.

2. Load as Billing.

3. Import SalesInvoiceLine as InvoiceLines and SalesInvoice as Invoices.

4. Keep ServiceBillingLineID as the unique row key.

5. In Billing add SalesInvoiceID using the unique invoice-line key:

   Excel — Billing[SalesInvoiceID]

   =XLOOKUP([@SalesInvoiceLineID],InvoiceLines[SalesInvoiceLineID],InvoiceLines[SalesInvoiceID],"No match",0)

6. Add InvoiceDate using the unique invoice header key, then format it as a date:

   Excel — Billing[InvoiceDate]

   =XLOOKUP([@SalesInvoiceID],Invoices[SalesInvoiceID],Invoices[InvoiceDate],"No match",0)

7. Resolve any No match before calculating the approval comparison.

8. Add CoveredBillable:

   Excel — Billing[CoveredBillable]

   =SUMIFS(TimeData[BillableHours],TimeData[ServiceEngagementID],[@ServiceEngagementID],TimeData[WorkDate],">="&[@BillingPeriodStartDate],TimeData[WorkDate],"<="&[@BillingPeriodEndDate])

9. Add HoursApprovedByInvoice; approval must be present and no later than the invoice date:

   Excel — Billing[HoursApprovedByInvoice]

   =SUMIFS(TimeData[BillableHours],TimeData[ServiceEngagementID],[@ServiceEngagementID],TimeData[WorkDate],">="&[@BillingPeriodStartDate],TimeData[WorkDate],"<="&[@BillingPeriodEndDate],TimeData[ApprovedDate],">0",TimeData[ApprovedDate],"<="&[@InvoiceDate])

10. Add HoursDifference:

    Excel — Billing[HoursDifference]

    =ROUND([@BilledHours]-[@HoursApprovedByInvoice],2)

SQL

The correlated SUM uses the billing line’s engagement and inclusive coverage dates, plus approval by InvoiceDate. It returns one hours total per billing row without expanding that row once for every time entry.

Run each complete statement separately and retain its result before continuing.

c02-billing.sql

SELECT b.ServiceBillingLineID,
       b.ServiceEngagementID,
       b.BillingPeriodStartDate,
       b.BillingPeriodEndDate,
       b.BilledHours,
       b.LineAmount,
       i.SalesInvoiceID,
       i.InvoiceDate,
       ROUND(COALESCE((SELECT SUM(t.BillableHours)
FROM ServiceTimeEntry t
WHERE t.ServiceEngagementID=b.ServiceEngagementID AND t.WorkDate BETWEEN b.BillingPeriodStartDate AND b.BillingPeriodEndDate AND t.ApprovedDate<=i.InvoiceDate),0),2) AS HoursApprovedByInvoice
FROM ServiceBillingLine b
JOIN SalesInvoiceLine l ON l.SalesInvoiceLineID=b.SalesInvoiceLineID
JOIN SalesInvoice i ON i.SalesInvoiceID=l.SalesInvoiceID
WHERE b.BillingPeriodStartDate>='2024-01-01' AND b.BillingPeriodEndDate<'2024-04-01'
ORDER BY b.ServiceEngagementID,b.BillingPeriodStartDate,b.ServiceBillingLineID;

Checkpoint. 33 billing lines cover 1,409.88 hours and 244,865.45 of line amounts. Every billed-hours comparison agrees. Billing line 2 covers January 12–30 for engagement 2; its 32.67 hours become 6,860.70 on invoice 241 dated January 31.

Coverage dates explain which work is billed. Invoice date explains when it is invoiced. A future population may have uncovered time, overlapping periods, or approval after invoicing. Retain those exceptions rather than filling in a match. For a complete accounting checkpoint, retrieve invoice 241 and explain why approved time creates no additional revenue posting. Follow the paired Excel and SQL procedure in Lesson 7. Keep this single-engagement example separate from the case population.

If your result differs

If totals multiply, check whether you merged raw time rows into Billing. SUMIFS keeps the billing row unique. Compare period boundaries inclusively.

Keep for the next stage: Save Billing and a zero/nonzero HoursDifference check.

2.2. Compare effort, price, and limited cost

Predict before you run: Does the difference between billed amounts and recorded time cost equal engagement profit?

Excel

Named inputs: TimeData and Billing (earlier stages).

Produced outputs: EffortByEngagementMonth and BillingByEngagementMonth: separate PivotTables, Review: billable share and limited cost comparison; chart.

1. Create a PivotTable from TimeData with ServiceEngagementID and WorkMonth in Rows and sums of BillableHours, NonBillableHours, and ExtendedCost in Values.

2. In Billing add WorkMonth:

   Excel — Billing[WorkMonth]

   =DATE(YEAR([@BillingPeriodStartDate]),MONTH([@BillingPeriodStartDate]),1)

3. Format WorkMonth as yyyy-mm.

4. Create a second PivotTable from Billing using the same Rows and sums of BilledHours and LineAmount.

5. Align the summaries by engagement and month, not row position.

6. For engagement 2 inspect January and February separately.

7. On Review label B2 Quarter billable share, enter the formula below, and format it as a percentage:

   Excel — Review!B2

   =SUM(TimeData[BillableHours])/(SUM(TimeData[BillableHours])+SUM(TimeData[NonBillableHours]))

8. Label B3 Billed amounts less recorded time cost and enter:

   Excel — Review!B3

   =SUM(Billing[LineAmount])-SUM(TimeData[ExtendedCost])

9. These totals use the full Q1 imported populations; ordinary SUM includes rows hidden by worksheet filters.

10. Create a clustered-column chart from engagement-level billable and non-billable hours using the verified summary.

SQL

effort and billing each return one aggregate row before they are combined. The ratio uses all selected hours, while the difference compares billed line amounts with recorded time cost within the stated boundary.

Run each complete statement separately and retain its result before continuing.

c02-bridge.sql

WITH effort AS (SELECT SUM(BillableHours) AS Billable,
       SUM(NonBillableHours) AS NonBillable,
       SUM(ExtendedCost) AS TimeCost
FROM ServiceTimeEntry
WHERE WorkDate>='2024-01-01' AND WorkDate<'2024-04-01'), billing AS (SELECT SUM(LineAmount) AS Billed
FROM ServiceBillingLine
WHERE BillingPeriodStartDate>='2024-01-01' AND BillingPeriodEndDate<'2024-04-01') SELECT ROUND(e.Billable,2) AS BillableHours,
       ROUND(e.NonBillable,2) AS NonBillableHours,
       ROUND(100.0*e.Billable/(e.Billable+e.NonBillable),2) AS BillableSharePct,
       ROUND(b.Billed,2) AS BilledAmount,
       ROUND(e.TimeCost,2) AS RecordedTimeCost,
       ROUND(b.Billed-e.TimeCost,2) AS BilledLessTimeCost
FROM effort e CROSS
JOIN billing b;

Checkpoint. Engagement 2: January billed amount 6,860.70 and time cost 937.02; February 7,753.20 and 1,042.22. Quarter billable share is 95.04%; billed amounts less recorded time cost is 195,843.05.

The release supports an hours-to-billing reconciliation and a limited time-cost comparison. It does not allocate every overhead, selling, support, or financing cost to each engagement. Also, an invoice does not establish cash collection. Use Case 1 to investigate settlement.

If your result differs

Do not subtract a full employee’s payroll from each engagement. Work-date cost and invoice-date revenue need aligned coverage before comparison.

Keep for the next stage: Retain the matched summaries, chart, and a 250-word memo explaining one follow-up and one limitation.

Session stopping point

Your memo should explain that the worked hours reconcile, state the cost boundary, and recommend a targeted review of non-billable effort or engagement terms without alleging missing billing.

Which measures belong in the design review?

Reading guide: These measures are compared side by side. Their different definitions and grains do not form a transaction sequence.

Expand the diagram for a larger view with zoom and pan controls.

Download editable Draw.io

Read the figure in words

• 1,409.88 billable hours and 73.62 non-billable hours describe effort.
• 33 billing lines total 244,865.45 for covered work.
• Time cost of 49,022.40 supports a limited comparison, not complete profit.

Selected released evidence and teaching explanation. These measures are compared side by side. Their different definitions and grains do not form a transaction sequence.

Prepare the Decision

The reconciliation does not reveal unapproved quarter-end time or a billed-hours mismatch in this population. A useful management response is to discuss where non-billable effort occurred and whether it was planned, while preserving the distinction between billing rates and staff-cost snapshots. The evidence supports a focused operational conversation, not a claim that all engagements are equally profitable.

Deliverable: An engagement-period reconciliation, an hours/billing/cost bridge, and a design review memo. Include the source file, selection dates, source IDs, measure definitions, checks, evidence limitations, and the next action. A clearly ordered SQL result table can support the conclusion, with a chart added if useful.

Independent Challenge

Repeat the investigation for April–June 2024 work. Rebuild the engagement and billing-period populations. Identify one changed result, inspect its source rows, and explain whether it reflects mix, timing, price, non-billable effort, or unresolved evidence.

Planning hint

Begin with the new work-date population. Check whether each billing period stays inside the selected quarter before comparing its entire amount with quarter-only time.

Assessment criteria: Assess population completeness, approval-date handling, unique billing rows, aligned coverage, precise cost labels, and a conclusion supported by source evidence. Your instructor evaluates the method, evidence, and reasoning. Model answers for this challenge remain in private course materials.

Further queries and earlier case topics

Recommended Query Sequence

These canonical queries extend the investigation. Their populations, dates, and measures may differ from the worked case, so read their definitions before comparing results. These optional references extend the complete guided routes above. Earlier specialist topics consolidated into this case remain accessible through this reference sequence.

Choose a reference after completing the guided investigation and verify its scope before comparing totals.

1. Design Service Revenue and Billed Hours by Customer Month

   Compare the query’s coverage and approval criteria before interpreting its result.

2. Design Service Engagement Utilization and Labor Margin

   Compare the query’s coverage and approval criteria before interpreting its result.

3. Design Service Approved vs Billed Hours Review

   Compare the query’s coverage and approval criteria before interpreting its result.

Next Steps

Continue to Explain Supplier Obligations at Month End. Keep your completed evidence and use the Query Library for a separate extension.