The 'balance = receipts − issues' formula in Excel

The formula "balance = receipts − issues" is usually typed into every row of an Excel sheet and dragged down. While there are few goods this works. But once the list grows, rows are added and the table gets sorted, the dragged formulas break and the balance suddenly changes "by itself". There is a more reliable way: write every movement into one long journal and let a formula calculate the balance on a separate sheet.

Two sheets: the journal and the balances

Name the first sheet "Movements". Every receipt and every issue is a separate row, and the columns are:

• A: date;
• B: item code (for example LMP-09);
• C: type (only "receipt" or "issue");
• D: quantity (always a positive number);
• E: note (sale, breakage, return, gift).

The second sheet is "Balance". Each item appears there once: the code in A, the name in B, the calculated balance in C and the minimum stock in D. When you start, put the quantity you actually counted for each item into the journal as its first row: type "receipt", note "opening".

The formula stays in one place however many rows the journal gets. Deleting a row or sorting does not break the balance, and a filter shows the whole history of one item.

The main formula

In cell C2 of the "Balance" sheet type:

=SUMIFS(Movements!D:D, Movements!B:B, A2, Movements!C:C, "receipt") - SUMIFS(Movements!D:D, Movements!B:B, A2, Movements!C:C, "issue")

The formula adds up all "receipt" rows in the journal with that code and subtracts all the "issue" rows. Drag it down and you are done: as records are added, the balance updates itself. With some regional settings Excel wants semicolons instead of commas, and the Russian version calls the function СУММЕСЛИМН.

An example: an electrical goods shop, a 9 W LED bulb, code LMP-09.

• 2 March, opening count: receipt 120;
• 5 March, from the supplier: receipt 200;
• 5 March, sales for the day: issue 35;
• 6 March, sales for the day: issue 48;
• 7 March, breakage: issue 2.

The sum: 120 + 200 − 35 − 48 − 2 = 235 pieces. The formula returns exactly that number.

Control columns

Add three more columns to the "Balance" sheet:

• E, "Status": =IF(C2<=D2, "Order", ""). If the balance is at or below the minimum, the word "Order" appears. Colour the cell red with conditional formatting and a glance is enough;
• F, "Counted": the number you actually counted on the shelf, entered by hand;
• G, "Difference": =F2-C2. Any row that is not zero shows a shortage or a surplus.

After the weekly count, filter column G so that only items with a difference remain, and start looking for the cause there.

For example, for LMP-09 you count 231 pieces on the shelf while the formula shows 235. The difference is −4: perhaps two sales were not recorded and two bulbs were short on delivery. Look for the cause at once and add a correcting row with a note. Do not delete old rows: the correction is part of the history too.

A new item and a backup copy

When a new item appears, add it to the end of the "Balance" sheet: enter the code, name and minimum stock, drag the formulas from columns C, E and G down to that row, and put an opening row in the journal. Once a week copy the journal file with the date in its name and keep the copy in the cloud or in your own Telegram chat. One corrupted file can wipe out a month of records.

What most often gets in the way

1. Identical codes. "LMP-09" and "LMP-09 " (with a trailing space) are two different items to Excel.
2. Fractions. If your Excel uses a decimal comma, a cable length typed as 12.5 can turn into a date or text and the formula will not add it. Type 12,5.
3. Pick the type from a list. Through Data → Data Validation → List, allow only "receipt, issue" in column C. A row with a typo such as "isue" simply drops out of the calculation.
4. One unit of measure. Bulbs in pieces, cable in metres: decide it once and keep to it.

Conclusion

A journal, a balance sheet and one formula are enough for a small shop, with no need to move to a program. The one condition is a real opening figure. If you have no time to count, the Sanab team counts the goods and hands over an Excel table that can go straight into the journal as the "opening" rows.