Manage activity with reports, analyses, pivot tables and Excel exports

Objective. Answer a management question with reports, pivot tables and Excel while preserving scope, source definitions and the distinction between budgeted, actual, invoiced and paid values.

ProfileManagementLevelIntermediateTypeControlEstimated duration1 h 10Data usedReports, pivots, Excel, budgets, time, expenses, margins and balances

Before you start: select the scope to manage

In your subscription, choose a matter with one consistent period across budget, time, expenses, invoices and payments. Record company, customer, matter, dates, currency and each indicator definition. Check your rights to reports and pivots before comparing results.

Continue when every source can be filtered to the same matter and period.

To understand the formulas, take a fictitious matter

AFFAIRE-PILOTAGE is fictitious: commercial budget EUR 12,000.00 net; time budget 80 hours at EUR 50.00, or EUR 4,000.00; actual time 110 hours at EUR 52.00, or EUR 5,720.00; expenses EUR 480.00; invoices EUR 10,000.00 net; payments EUR 8,000.00. The formulas give +30 hours, EUR 2,000.00 still to invoice, EUR 2,000.00 still to collect and a simple contribution of 10,000 − 5,720 − 480 = EUR 3,800.00. This paragraph is only for understanding the calculations; do not search for these values in Tempolia.

Management question on your data

For the matter and period you selected, ask: Why does profitability differ from budget, and which point should be addressed first? Use only the values shown for that scope and compare every formula with the chosen report definition.

Prerequisites

One company, one matter and one period common to all sources are selected.
Budget, time, expenses, invoices and payments can all be filtered to that matter and period.
Historical cost rates and report definitions are known.
The business question and decision sought are written before choosing the tool.

70-minute schedule

8 min: state the question and freeze scope.
12 min: choose the report and set filters.
15 min: reconstruct budget, time and expenses.
15 min: reconcile invoices, payments and balance.
12 min: reproduce the analysis in pivot or export.
8 min: formulate diagnosis, decision and indicator limits.

Before continuing

Every source uses the same company, matter and period.
Hours, time cost and expenses in the report reconcile to the actual detail lines retained.
Invoiced and collected totals reconcile respectively to the sales journal and payments for the same scope.
The conclusion separates workload variance, amount still to invoice and amount still to collect.

Choose the report that answers the question

A standard report answers a recurring question with fixed columns and presentation. A pivot explores axes and groupings interactively. An export supplies detailed data for Excel or another system. None is a truth outside its filters, rights, date conventions and calculation definitions.

Begin with grain: one time line, expense, budget line, invoice or payment. Verify samples before aggregating. Define whether cost uses historical or current rates, whether margin is based on production or invoicing, and whether amounts are net or gross. A plausible total can still mix incompatible measures.

Start with the detailed lines, calculate the variance, then look for its cause. In the worksheet, +30 hours is the variance; scope growth is only one possible explanation until the selected matter confirms it. Use the real result to decide whether to replan work, complete billing, follow up payment or leave the plan unchanged.

1. Lock the real perimeter and run the report

Open Reports / Analyses and select the customer, matter and complete period chosen in your subscription, together with the relevant company, currency and net or gross basis. Show active filters and columns. If the report returns no rows, check period, status, company and access rights before continuing.

Record the total exactly as displayed and understand each calculated column. Do not expect 110 hours: that number belongs to the separate AFFAIRE-PILOTAGE worksheet. If two users obtain different totals for the selected matter, inspect dates, approval, task exclusions, saved views and rights before interpreting the difference.

Report filtered to one customer, one matter and the complete selected period — Record the selected filters, then copy the displayed hours, costs, invoiced value, budget and progress.

Budget fields for the selected matter — Record the planned hours and time sale value for the selected matter; keep the EUR 12,000 AFFAIRE-PILOTAGE exercise value on the worksheet.

2. Return to the real time, expenses, invoices and payments

Within the same selected perimeter, open time detail and record its actual total and lines. Do the same for expenses, issued invoices and payments. List lines excluded by status or date. These values form the Tempolia column; do not replace any missing value with a worksheet assumption.

In a separate example column, calculate 110 - 80 = +30 hours; 12,000 - 10,000 = EUR 2,000.00 still to invoice; 10,000 - 8,000 = EUR 2,000.00 still to collect; 10,000 - 5,720 - 480 = EUR 3,800.00 simple contribution. Do not call it official margin unless the report definition matches, and never present it as a result of the selected matter.

Time-detail table filtered to one customer, matter and period — Check customer, matter, period and approval status, then sum durations; the row count is not a number of hours.

Expense rows filtered to one customer, matter and period — Check every amount, date and code included in the selected scope before totalling expenses.

Issued-invoice journal filtered to one customer, matter and period — Check the customer, matter, dates, statuses and amounts before calculating turnover.

Payment list filtered to one customer, matter and period — Check sign, status and matching before calculating the amount actually collected.

3. Reproduce the analysis in a pivot and export

Create a pivot by matter and measure, with budget, time cost, expenses, invoiced and paid in separate columns. Preserve filters and units. Reconcile pivot totals to the five sources; never sum measures that describe different stages.

Export only after validation. Add a control sheet or file metadata with company, period, matter, filters, generation date, units and report definition. Excel calculations must remain traceable to source columns.

Populated pivot filtered to the selected customer, matter and period — Reconcile the displayed formula and totals with the report before interpreting the variance.

Export form set to Time entries with included columns and no generated file — Choose Time entries, select the required columns, then run the export once the scope is correct.

Summary exercise

1. Freeze the selected scope, run the populated report and record the actual total.
2. Reconcile the selected matter’s sources, then calculate the AFFAIRE-PILOTAGE worksheet separately.
3. Reproduce the real result in a pivot and choose actions that follow from the detailed lines.

The selected matter’s sources use the same matter and period.
+30 hours, EUR 2,000 to invoice and EUR 2,000 to collect remain labelled as example calculations.
Keep the worksheet contribution and the selected matter contribution in separate, clearly named columns.

Diagnosis

Unexpected total: inspect date, approval, rights and excluded tasks.
Worksheet EUR 12,000 called invoiced on screen: the example and real columns have been confused.
Different user totals: compare filters, saved views and rights.
Different margin: check rates, included costs and the production-versus-invoicing basis.

Time pivot with customer rows, collaborator columns and time totals — Apply the selected customer, matter and period before comparing time totals with the report.

Common mistakes

Commenting on a number without preserving its filters.
Mixing budget, production, invoicing and cash.
Comparing reports with different periods or companies.
Losing the source perimeter after Excel export.
Ignoring access rights as a cause of different totals.

Before you finish

Recalculate the worksheet totals of 110 hours, EUR 5,720 cost, EUR 480 expenses, EUR 10,000 invoiced and EUR 8,000 paid. Then repeat the formulas with the selected matter’s own totals without mixing the two sets of values.

Repeat the navigation for the selected customer, matter and complete period. Record the displayed total and explain why the 110-hour worked example does not replace it. Keep the example and the matter’s actual figures clearly separated. Note the report name, filters, generation time and indicator definition so you can run the same comparison again later.

Build an indicator dictionary before sharing the dashboard

For each displayed measure, record its business meaning, source screen, date rule, unit, sign, inclusions and exclusions. “Time cost” must say whether it uses the rate at the work date or the current employee rate. “Invoiced” must say issued or prepared and net or gross. “Paid” must say whether unmatched payments and credits are included. “Margin” must provide its formula rather than relying on the heading.

Add one worked line from the example worksheet: 110 hours at EUR 52.00 equals EUR 5,720.00; EUR 10,000.00 invoiced minus that time cost and EUR 480.00 expenses equals EUR 3,800.00. Label this simple contribution in the example. It becomes an official indicator only if the selected report uses the identical basis. This makes it easier to understand why two plausible reports can disagree without assuming that one is broken.

Diagnose differences without changing the scope

If totals differ, keep both perimeters unchanged while you compare them. Check company, matter, date field, status, rights, currency, net or gross basis and saved filters. Then return to line detail: one omitted timesheet, expense, credit note or unmatched payment often explains the difference. Keep the export and its generation timestamp with the comparison. Do not adjust the period merely to obtain the total from the example.

When pvcolaff or pvqaff contains no row for the selected matter, conclude only that no matter-specific override is observed there. Continue to the ordinary cost, sale and quantity sources used by the report. If the override table is empty, check the ordinary rate used by the report before calculating the margin. Ask the person who maintains that rate if the calculation remains ambiguous.

Choose the next useful actions

Separate the workload, billing and collection conclusions. In the worked example, +30 hours prompts a review of scope, planning and production; EUR 2,000.00 below the commercial budget prompts a billing or engagement review; EUR 2,000.00 between invoiced and paid prompts customer follow-up. The EUR 3,800.00 contribution follows from the stated formula; it does not by itself determine what to do.

For the selected matter, use the period chosen at the start and the corresponding detailed lines. Before changing price, staffing or collection strategy, reproduce the total and check what is still missing. Then choose the practical next step and note when it will be reviewed.

Repeat the calculation on the selected matter

Use the selected customer, matter and complete period and record the total displayed. The 110-hour versus 80-hour calculation remains a separate example worksheet. An empty pvcolaff or pvqaff table only means that no matter-specific rate is shown there; check the standard rate used by the report.

Step back

A useful dashboard does not remove complexity; it makes definitions, perimeters and decisions explicit. Automate reproducible filters and reconciliations, but keep causal interpretation and management action under human review. The best indicator is one whose source and limit are understood well enough to know when not to use it.