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. This guide walks through all things Inventory so let’s dive in!
The Inventory Report
This report displays 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 can access the Inventory Report by going to Reports > Inventory.
NOTE📍: The Inventory Report is like a snapshot in time. It will only show the current state of your clinic’s inventory as of today’s date. So, if you want to use the report as a reference point for a specific date (e.g., end of the month, end of the calendar year, end of the fiscal year, etc.), make sure to export your report on that date.
Leaving the report unfiltered will generate a complete list of all the products entered in Jane.
Product Price List
Often times with reports in Jane, we like to include a little extra information that isn’t shown when it’s viewed in Jane. With the Inventory report, Jane will generate not only a list of all the products and their current inventory levels but exporting it will include the Price and Cost columns.
To export the Inventory report, you’ll want to click on the three horizontal black dots to the right of the report and select the desired format you’d like to export to. We like to recommend Excel or CSV so you can customize the columns and add any fancy formulas.
Alternatively, staff with Full Access to the account can head over to the Settings tab and then click on Products to view the full list of products and their prices.
For staff that don’t have this access level, a user with a higher access level exporting this report is a handy way to have a price list on hand.
Calculating Revenue/Cost of Inventory
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 the Inventory Report in tandem with some introductory Excel calculations— we promise it won’t be too bad!
Once you’ve exported the report to 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 multiply 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 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 a 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 =(Price)*(Inventory).
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 Exporting Reports and Customizing them in Excel for more spreadsheet tips and tricks.