25, 26, 27…. wait a second, did I count those right?
Inventory can sometimes be a bit tedious, but that doesn’t necessarily mean it has to be difficult. In addition to making sure that all of your products are accounted for in the backroom, there are times when you might also want to know the total value of all of these products combined.
You may be interested in the total retail value of all of these items to have a better understanding of the potential revenue that could be earned, or you may want to know the total cost of all of the products to the clinic so that you can review your expenses accordingly.
To learn more about these two factors, we are going to work with Jane’s Inventory Report in tandem with some introductory Excel calculations— we promise it won’t be too bad!
IMPORTANT: Since the Inventory Report will only show you the current state of your clinic’s inventory as of today’s date, you’ll want to make sure that you are exporting your report on the date that you would like to use as a reference point (e.g. end of the month, end of the calendar year, end of the fiscal year, etc.)
A staff member with either the Administrative/ All Billing or Full Access permission level will be able to access the Inventory Report under the Reports Tab.
When viewed in Jane, this report will display the name of the product, the number sold within the past 30 days, how many are in stock at the various clinic locations, as well as the supplier, manufacturer, and reorder quantity.
You will be able to download this report to a spreadsheet file (Excel or CSV) by clicking on the Export button in the top right-hand corner.
If you were to open up this file in your preferred spreadsheet program, you’ll notice that a few additional pieces of information will be available to you. Most importantly, this includes the price per unit and cost per unit for each product.
Since we are interested in the total retail value of all of our inventory, we will want to determine the value of the remaining number of products in stock for each location.
In other words, we will want to multiply the number of units for that particular product by its retail price (amount charged to your patient). For example, $10 price x 5 items = $50.
If you are interested in the total cost of these products to the clinic, you would want to multiple the total number of units by the cost column instead. For example, $5 cost x 5 items = $25.
From here, you’d want to create yourself a new column which can hold the calculation between these two numbers. You could call this column something like, “Location Name - Total Retail Value” for your reference.
From here, this is where those fancy formulas come in… but not to worry! It’s actually much simpler than it initially sounds. To let Excel (or similar program) know that you are looking to calculate a multiplication formula, you’ll want to start typing in the first available box of your new column
In our particular spreadsheet, this would be
=(C2)*(L2) where “C2” is the first value in the price column and “L2” is the first value in the inventory column for that location.
Poof! Excel will take a peek at what information is stored in “C2” and “L2”, then go ahead and multiply them together for you.
You’ll be able to drag the edge of this newly calculated cell in your spreadsheet down the rest of the column, and Excel will go ahead and use the same formula for the remainder of the rows.
Finally, if you wish to produce a grand total of all of these individual product totals, you can SUM up the entirety of the row with a formula similar to
=SUM(M2:M6), where in our example M2 is the very start of the column, M6 is the end of the column, and the “:” sign indicates to include everything in between.
And voila! You should now have a final total value that reflects the price or cost of all of the items in your inventory for a certain period of this.
We would recommend creating a new column in excel that will calculate the value of all of the stock for a particular item. You can create multiple columns like this if you are looking to separate the totals for your different locations.
Want to learn more about working with exported reports in Jane? Check out our other handy article on Excel Exports for more spreadsheet tips and tricks.