Monthly Department Reports with Pivot Tables

The monthly meeting starts at nine. Finance reports IDR18 million in Operations spending, while the department manager brings a figure of IDR21 million. Both used the same workbook. One counted payments by payment date; the other counted requests by submission date.

For this worked example, imagine you are preparing that meeting for two departments. A monthly department report needs an agreed definition of which records count, which date determines the period, and what the meeting should decide. A pivot table can group the records once you have settled those rules.

Think of a shared shopping list. Someone counting orders and someone counting paid purchases can produce different totals without making an arithmetic mistake. Before adjusting the chart, check that everyone is counting the same basket.

Define the amount before choosing a chart

Put a sentence above the source worksheet: this report includes payments made during October, using payment date, for records with Paid status. That sentence gives you a direct answer when someone asks why a particular request is missing.

An approved request may still be unpaid. An invoice received this month may be due next month. If the manager needs to see both payments and outstanding obligations, provide separate views. Adding the two together can count the same transaction at different stages.

Decide what department means, too. Does the report assign spending to the requesting department or the department receiving the goods? A laptop ordered by Procurement for Sales should follow your agreed allocation rule. Add a Charged department field if it differs from Requesting department, then select the appropriate field for the report.

Write down the start and end dates, currency, and treatment of cancellations. For international teams, keep different currencies in separate subtotals unless you have an agreed conversion method. A total that combines currencies without a conversion basis cannot support a useful spending decision.

Give each source row a consistent meaning

Use a payment reference so you can trace a summary figure back to its evidence. In this example, one row represents one recorded payment. A request paid in two installments has two payment references and one shared request reference linking them.

Payment reference	Payment date	Charged department	Purpose	Status	Amount
PAY-101	3 October	Operations	Equipment maintenance	Paid	IDR6,000,000
PAY-102	8 October	Operations	Warehouse rent	Paid	IDR12,000,000
PAY-103	9 October	Sales	Customer visit travel	Paid	IDR4,000,000
PAY-104	22 October	Sales	Customer event	Paid	IDR6,000,000

The four payments total IDR28 million: IDR18 million for Operations and IDR10 million for Sales. An additional IDR3 million awaiting payment belongs in the obligations view. It does not belong in October's payment total.

Store amounts as numbers, with currency in a separate field where needed. Avoid mixing currency symbols, explanatory notes, and values in the same cell. Use a controlled department list so Operations and Ops do not become separate groups for the same team.

When one invoice covers several departments, record allocations that reconcile to the invoice total. Copying the full invoice amount into every department row will overstate company spending. Check the allocation total before the records enter the report, especially when several people maintain the source workbook.

Keep the source reference alongside each allocation. A reviewer should be able to identify the invoice and understand which portion belongs to each team without searching through a separate conversation. If you change an allocation later, retain the reason for the correction.

Check whether the pivot is adding or counting

Place Charged department in rows, payment month in columns, and Amount in values. Filter Status to Paid. For the four example payments, the sum should show IDR18 million for Operations and IDR10 million for Sales, with a grand total of IDR28 million.

Read the calculation label in the values area. Microsoft's PivotTable calculation guidance explains that Sum adds values and Count counts nonempty values. Numeric fields use Sum by default, while a field containing blanks or nonnumeric values can use Count when added to the values area.

If the Amount summary displays 2 for each department, check whether it is counting entries rather than adding money. In Excel, right-click the value field, choose Summarize Values By, and select the function you need. Microsoft also notes that blanks or nonnumeric values are treated as zero after switching that calculation to Sum.

Changing the calculation does not repair an amount stored as text. Find the affected source row, compare it with the supporting record, and correct it there. Replacing every blank with zero can conceal payments whose amounts have not been entered.

Add a payment count only when someone needs it. Row count is not necessarily unique invoice count: one invoice may have several payments. Name the measure Payment count so readers do not mistake it for Invoice count.

Resolve differences through the underlying records

When the Operations manager brings IDR21 million and the payment report shows IDR18 million, open the records behind both totals. Look for the IDR3 million difference in unpaid requests, records outside the reporting period, different department allocations, or duplicate rows.

Write a reconciliation note with a document reference and a next action. “Timing difference” leaves the next reviewer guessing. A useful note says: “Request REQ-208 for IDR3 million was approved in October and remained unpaid at month-end; included in outstanding obligations.”

Assign one person to correct the source. Department managers can supply evidence and explain allocations, while the designated record owner makes the change. Keep a reason for the correction so an updated report does not erase the explanation for a number discussed at an earlier meeting.

Match payments against the relevant cash or bank records. A transfer between company accounts should not become department spending simply because money left one account. Establish the transaction type before grouping it into a spending report.

Bring a decision record to the meeting

Give managers somewhere to explain a change that needs a decision. Higher maintenance spending needs a work reference and a reason, not just a taller chart column. Record a follow-up with a named role responsible and an agreed due date.

Meeting finding	Evidence to open	Next action	Responsible role
IDR3 million remains unpaid	REQ-208 and payment schedule	Confirm scheduled payment date	Finance officer
Department names differ	Transactions labelled Ops	Reconcile department codes before the next report	Reporting analyst

Do not describe a month-to-month difference as increased spending if the definition changed between periods. If last month used request date and this month uses payment date, explain the difference or recalculate the comparison on the same basis. Until that is done, the movement does not establish a change in the department's payment activity.

Close each finding with a recorded action. The next meeting can start with outstanding follow-ups, instead of repeating the same disagreement without opening new evidence. Save the reporting definition with the period's records so a later reviewer can reproduce the scope.

When the report needs to follow the team's workflow

If staff keep copying a separate workbook for every branch, consider whether recording and reporting should share an application. The Sora homepage describes reports using charts, totals, and pivot tables, including grouping by a field and filtering by period. Its reporting examples include spending by department.

Describe your payment records, department field, and reporting period, then review what Sora proposes before applying it. The Sora applications page offers a starting point for matching an application to your team's work. Keep access decisions separate from the report layout: hiding a column in a summary does not establish that every underlying permission is correct.

The guide to role-based access and sensitive data covers that separate concern. Someone reviewing department totals may have a different access need from the person entering payments. Check both roles with sample records rather than assuming a useful chart also has the intended visibility.

Start with the four example payments. Confirm the IDR28 million total, trace each department group back to its records, and try a period with no payments. Review the result with the people who will use the report before adding the next set of working records.

Today, choose one department report due for discussion. Write its date and status rules above the source, reconcile five transactions to their evidence, and record one unresolved difference with a document reference and the person responsible for the next action.