Stock count as of an earlier date

The books have to be closed as of last month, but the stock count was only done today. So you need a quantity on hand that is no longer the current one. Here we go through what Calculate Inventory does in standard BC, why calculating backwards is hard, and which solutions exist without buying anything. It can mean extra work and uncertain figures. Read it as a checklist.

What Calculate Inventory does

In the Phys. Inventory Journal, the Calculate Inventory action creates one line per item with the expected quantity. You can filter by items, locations, bins and dimensions, and you can choose whether items with no stock should be included. The field for the calculated quantity and the field for the counted quantity start with the same value, so you only correct the lines where the count differs.

Microsoft Learn describes the calculation as a snapshot of the item ledger entries at the moment you run it. The page does not mention an option to calculate as of an earlier date.

Why going backwards is hard

The useful rule of thumb from Learn is that you should keep the lines you originally calculated and not calculate the expected inventory again, because stock can change while the count is going on. In other words, the expected quantity should apply at the time of the count.

The problem with an earlier date is that item entries also carry values, and that changes posted after the date may have moved both quantity and cost. The idea of calculating back in time has over 250 votes at Microsoft (251). That shows that many people miss it, and that standard has no direct solution, as far as we have been able to confirm.

What you can do in standard

Count and calculate on the same day. Run Calculate Inventory just before the count, and keep the lines until you post.
Run one journal batch per location or item category. Learn recommends separate journal batches if you need to print several lists.
Post the count with the date the count actually applies to. Check for closed periods before you start.
Use inventory valuation reports and the Item Ledger Entries overview with a date filter to see quantities as of an earlier date. Check for yourselves that the figures match your stock before you use them as quantity on hand.
Save an Excel export of the inventory on the closing date if you know the count will come afterwards.

When the date is in the past anyway

If the count only happens after the end of the period, you can count and then subtract the movements after the date. This is a manual reconciliation: quantity counted today minus entries after the closing date gives the quantity on hand as of the date. Use the filter on posting date in the Item Ledger Entries overview, and keep the spreadsheet as documentation for the auditor.

Then post any differences, and write in the journal description what the figure has been reconciled from. Good traceability matters more than speed when the auditor asks.

What you should check yourself

Check in your own BC whether Calculate Inventory has a date field, and what it does. Test in a sandbox environment with a copy of the data before you use it in a close. Also read Microsoft Learn under Count, adjust, and reclassify inventory by using journals.

How to document the count

The auditor will typically ask how the stock at the balance sheet date was determined. Keep three things together: the printed count list, the posted journal and an export of the item entries up to the date. Write a note saying who counted, when, and which calculation lies behind it. It takes half an hour and saves long emails when the accounts are to be approved. Also agree with the auditor in advance which method you will use if the count falls after the end of the period.

The idea on our list

At Addverk we collect ideas for small apps that could close gaps like this. They are ideas we are considering, not something you can download or buy today. The list of 30 ideas is on the Coming up page, and you can see which of them look like what you are missing yourselves.