7. Follow Design Time into Billing
The design lead has approved the team's January work. Customer accounting must decide what can be billed, while the controller needs to understand the cost information. Here the delivered activity is staff time, so a shipment is not the bridge to the invoice.
Your task. Reconcile approved time to one service billing period and distinguish revenue from time-cost support.
How does approved time become billed revenue?
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.
Read the figure in words
- Engagement 2: January: Billable: 32.67 hours; Non-billable: 1.21 hours; Time-cost support: 937.02
- Billing line 2: 32.67 hours × 210.00; LineAmount: 6,860.70; Match engagement and period
- Invoice line 387: Service billing link; Recognize invoiced revenue; Collection follows O2C
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.
Before You Start
Finish the sales tutorials and read Design Services. This separate example uses engagement 2, January 2024, and service billing line 2.
- Excel
- SQL
In Lesson07.xlsx, import the following tables and convert date fields to Date.
| Published table | Working name | Power Query filter |
|---|---|---|
T11_ServiceEngagement | EngagementData | ServiceEngagementID = 2 |
T13_ServiceTimeEntry | ServiceTimeData | ServiceEngagementID = 2 and January 2024 WorkDate |
T18_ServiceBillingLine | BillingData | ServiceBillingLineID = 2 |
T17_SalesInvoiceLine | InvoiceLineData | SalesInvoiceLineID = 387 |
Open the SQLite database in your SQL tool. Save this lesson's statements as Lesson07.sql. Run each supplied query separately, using its filters to identify the records and dates for that step.
1. Read the Engagement and Approved Time
The engagement establishes the billing arrangement. Individual time entries distinguish billable work from other work. Approval is a separate field from the work date.
- Excel
- SQL
Read EngagementData: engagement SE-2024-000002, customer 30, hourly rate 210.00. In ServiceTimeData, retain entries with a nonblank ApprovedDate on or before 2024-01-31. On Notes, label B2 Approved entries, B3 Billable hours, B4 Non-billable hours, and B5 Recorded time cost. Enter each visible-row calculation:
=SUBTOTAL(103,ServiceTimeData[ServiceTimeEntryID])
=SUBTOTAL(109,ServiceTimeData[BillableHours])
=SUBTOTAL(109,ServiceTimeData[NonBillableHours])
=SUBTOTAL(109,ServiceTimeData[ExtendedCost])
Read the agreement, then total approved time for this engagement and work period. Approval and date filters make the population explicit. IS NOT NULL requires an approval date to be present, and the following date condition checks when approval occurred.
SELECT ServiceEngagementID, EngagementNumber, CustomerID, HourlyRate
FROM ServiceEngagement
WHERE ServiceEngagementID = 2;
SELECT COUNT(*) AS TimeEntries,
ROUND(SUM(BillableHours), 2) AS BillableHours,
ROUND(SUM(NonBillableHours), 2) AS NonBillableHours,
ROUND(SUM(ExtendedCost), 2) AS TimeCost
FROM ServiceTimeEntry
WHERE ServiceEngagementID = 2
AND WorkDate >= '2024-01-01' AND WorkDate < '2024-02-01'
AND ApprovedDate IS NOT NULL AND ApprovedDate <= '2024-01-31';
Expect 7 approved entries, 32.67 billable hours, 1.21 non-billable hours, and 937.02 of recorded time cost. Non-billable hours represent work that is excluded from this billing quantity.
2. Check the Billing Bridge
The billing record identifies the engagement, covered dates, and invoice line. Time entries support this calculation through their engagement and work period rather than an invoice-line key on each entry.
- Excel
- SQL
Read BillingData: start 2024-01-12, end 2024-01-30, billed hours 32.67, rate 210.00, invoice line 387. Check all seven selected work dates fall in that interval. In Notes!B7, multiply billed hours by the rate for the single imported billing row and compare with LineAmount. SUMPRODUCT multiplies corresponding values and adds the products:
=SUMPRODUCT(BillingData[BilledHours],BillingData[HourlyRate])
Inspect InvoiceLineData: it belongs to invoice 241, has LineTotal 6860.70, and a blank ShipmentLineID.
Use the stored billing-to-invoice-line link. The empty shipment key is expected for this service line.
SELECT b.ServiceBillingLineID, b.BillingPeriodStartDate,
b.BillingPeriodEndDate, b.BilledHours, b.HourlyRate,
b.LineAmount, sil.SalesInvoiceLineID, sil.SalesInvoiceID,
sil.LineTotal, sil.ShipmentLineID
FROM ServiceBillingLine AS b
JOIN SalesInvoiceLine AS sil
ON sil.SalesInvoiceLineID = b.SalesInvoiceLineID
WHERE b.ServiceBillingLineID = 2;
32.67 × 210.00 = 6,860.70, agreeing with the billing amount and invoice-line amount. In this dataset, the invoice posts service revenue, while approved time supports billing without an additional revenue posting. Collection uses the receipt/application process you already investigated.
3. Retrieve the Invoice Voucher
Reuse the complete-voucher method from Lesson 6. In Excel, import T3_GLEntry as ServiceInvoiceGL, filtered to SourceDocumentType = SalesInvoice and SourceDocumentID = 241, and add account names from T1_Account. In SQL, adapt Lesson 6's paired source filters to the same type and ID. Retrieve all accounts before totaling debits and credits.
Expect three ledger rows dated January 31, 2024, with 7,306.65 on each side. AR is debited for 7,306.65, service revenue is credited for 6,860.70, and sales tax payable is credited for 445.95. Explain why the seven approved time entries support the invoice without recognizing another 6,860.70 of revenue. Approval establishes a billing input, while the invoice is the revenue posting source.
Keep Your Evidence
Save a short hours-to-billing bridge with the seven-entry count, billable/non-billable totals, time cost, billing period, and invoice-line link. Record the source file, selected keys, filters, and what one result row represents. Keep your workbook or saved SQL with your explanation.
The 937.02 is recorded time-cost support, including the selected non-billable work. It is not a reconciliation of payroll expense or all engagement costs. Subtracting it from billed revenue gives a limited comparison, not full profit. Other engagements or periods may need different approval and billing cutoffs.
Try One Change
Calculate the total hours worked and the billable share of those hours. Explain why neither number is an invoice amount. Then transfer the billing and ledger method to billing line 3, a separate engagement example. Predict the posting route before retrieving its linked invoice.
Hint for the second billing record
Filter ServiceBillingLine to ID 3 and follow its stored SalesInvoiceLineID to the invoice header. Keep this engagement and billing period separate from engagement 2. Retrieve that invoice's complete voucher with its actual source type and ID.
Compare your reasoning
Of the 33.88 recorded hours, 32.67 are billable, a share of about 96.43%. Hours are a quantity of work. Applying the billing rate to the appropriate billable hours produces a billing amount, while payroll expense requires different evidence.
Billing line 3 identifies invoice line 388 on invoice 242. Its 31.18 billed hours at 185.00 produce 5,768.30 service revenue. The complete invoice voucher balances at 6,143.24, including 374.94 tax payable. A different total from invoice 241 is expected because the selected work, rate, and tax differ. Time approval is still support for billing rather than an additional revenue event.
Next Steps
The company also needs goods and components. Next, follow a supplier transaction. Continue to Follow a Purchase.