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.
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.
Spreadsheet Data 2
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.
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.
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.
Spreadsheet Data 5
And finally, to calculate the stock value for each product, we multiply the cost with the finishing stock, and then sum these for the grand total as in Spreadsheet Data 6.
Spreadsheet Data 6
You would have to agree that this is a very quick way to calculate, and substantiate, your final stock levels and values. It will also give you a concrete starting point if you want to do a stock check. There are many time saving, business-enhancing tools available in the average PC nowadays. Working Data can help you unlock them. Call us on 01295 680 986 and we will be glad to talk it over with you.
Add a Comment