Guided project · 5 steps Cellwise Plus
Month-end accruals in Excel
Work out which October costs need accruing because no invoice has arrived, total them, and write the journal memo.
You will use: IF, SUM, COUNTIF, MAX, MATCH, INDEX, TEXT
Starting data
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Vendor | Service | Estimate | Invoice received? | Accrue | Total to accrue | |
| 2 | Cleanway Janitorial | October cleaning | 640 | No | Items to accrue | ||
| 3 | Staff wages | Oct 26-31, paid in November | 3850 | No | Largest accrual | ||
| 4 | Fiberlink Internet | October service | 89 | Yes | Journal memo | ||
| 5 | Contract designer | October hours | 1200 | No | |||
| 6 | Brightpath Freight | October deliveries | 315 | Yes | |||
| 7 | Liftco Rentals | Forklift rental, October | 450 | No |
Build it step by step
Step 1
A cost needs accruing when you received the service in October but no invoice has arrived. In E2, show the estimate when D2 is "No", otherwise 0. Fill to E7.
Step 2
Total accrual for the month, in H1.
Step 3
How many items need accruing? Count the "No" answers, in H2.
Step 4
Which vendor has the largest accrual? Put the name in H3.
Step 5
Write the journal memo in H4: "Accrue $6,140.00 for 4 uninvoiced October costs", built from H1 and H2.