# Repeatable Excel reporting exercise

Prepared for Amir Rosely's resource library, 29 October 2026.

The workbook uses fictional group registration records. Rates are teaching examples, not actual training prices. One record represents a group registration, not one individual participant.

## Read the workbook

The **Jawapan** sheet contains the reference report and checks. **Data** contains eight source rows in A2:F9 and two calculated columns. The worksheet labels are in Bahasa Malaysia:

| Column         | Meaning                            |
| -------------- | ---------------------------------- |
| ID             | Registration identifier            |
| Tarikh         | Date                               |
| Program        | Programme                          |
| Status         | Registration status                |
| Peserta        | Participant count                  |
| Kadar_RM       | Example rate per participant in RM |
| Bil_ID         | Number of occurrences of this ID   |
| Nilai_Baris_RM | Participant count × example rate   |

## Agree the reporting rule first

This exercise includes only `Confirmed` rows whose ID appears exactly once. Both copies of R004 are held for review; the first copy is not silently accepted. The blank status on R005 is not treated as confirmed. The cancelled R003 is excluded. R006 has a valid zero participant count.

This produces a provisional checked subset, not a complete revenue statement.

## Trace the formulas

```excel
Data!G2 =COUNTIFS($A$2:$A$9,A2)
Data!H2 =E2*F2
```

Both formulas are filled to row 9. The reference report then applies the agreed conditions:

```excel
=SUMIFS(Data!$H$2:$H$9,Data!$D$2:$D$9,"Confirmed",Data!$G$2:$G$9,1)
```

The baseline checked subset is **4 records, 25 participants and RM3,240**. Verify this independently: R001 RM1,200 + R002 RM1,200 + R006 RM0 + R007 RM840. There are 8 source rows, 7 different IDs, 2 duplicate-affected rows and 1 missing status. The raw sum is RM7,620; filtering only for Confirmed still gives the incorrect-for-this-rule RM6,120.

## Repeat with a new source

1. Keep the original export unchanged in a separate file.
2. Confirm the meaning of a row and the relevant reporting period.
3. Check IDs, statuses, date types and numeric columns before calculating.
4. Prepare a working copy. Document any correction and its reason.
5. The exercise uses fixed ranges ending at row 9. For a different row count, extend **every** referenced source range, fill G/H to the new final row, and update the report formulas. New rows are not included automatically.
6. Reconcile row counts and a few known amounts to the source. Test missing status, a repeated ID and a genuine zero.
7. Record the source date, reporting rule and unresolved records beside the report.

For a production workflow that grows each month, use an appropriately tested Excel Table or Power Query process rather than relying on this fixed-size teaching example.

## Recalculation tests

On a disposable copy, change Data!E9 from 7 to 8. The subset should become 26 participants and RM3,360. Restore 7, then clear Data!D9: the subset becomes 3 records, 18 participants and RM2,400, with two missing statuses. Restore `Confirmed` before continuing.

Reference explanations are editorial teaching material. They are not a transcript of a particular AI model's response.

Read the guide: https://www.amirrosely.com/insights/build-a-repeatable-excel-reporting-workflow