How to do Inventory Year End Calculations

Keeping check on inventory; how much has gone out, how much has come in, what value goes down on my tax return? At this time of year, it is a question that your accountant will want the answer to. In this month’s spreadsheet tip, we show you how to get this figure quickly by combining arithmetics with the SUMIF function.

Calculating an inventory value using SUMIF

Now the big day has passed, the stock controller at Bewitchin’ Data wants to get an idea of inventory levels. She has last year’ results along with this year’s sales and receipts, as in Spreadsheet Data 1. The total stock value last year was £5.40, the figure in bold. In cell C28, we use the SUMIF function to determine the total number of Hats sold. SUMIF requires three pieces of information. The first is RANGE, which lets it know where we are looking. The second is CRITERIA, which tells it what we are looking for. The third is SUM_RANGE, which tells it what it should be adding up. In our example, the RANGE is A13:A24, the list of products in the sales table. The CRITERIA is A28, which is the product. The SUM_RANGE is B13:B24 which is the quantity sold each time. In cell C28 we write (See Spreadsheet Data 2):

=SUMIF(\$A\$13:\$A\$24,A28,\$B\$13:\$B\$24)

We put dollar signs next to each part of the cell references in the RANGE and SUM_RANGE as this locks the positions of these ranges within the formula. It allows us to drag the formula down into the cells below without affecting these ranges. Hitting return will calculate the function for us, giving 23. We can now drag this formula down into the two cells below and it will automatically calculate the total sales for Brooms and Wands, respectively 23 and 21. See Spreadsheet Data 3. We repeat this process to calculate the total receipts. We enter the formula

=SUMIF(\$D\$13:\$D\$23,A28,\$E\$13:\$E\$23)

into cell D28 and copy it down into rows 29 and 30, giving 65 Hats, 60 Brooms and 45 Wands received. See Spreadsheet Data 4. To calculate the finishing stock level for each product we take the starting inventory, subtract sales and add receipts, see Spreadsheet Data 5.  