Not an InventoryLab subscriber as of the end of last year? Afraid that you can't get your Inventory Valuation from our Inventory Valuation report?
Fear not, we have a solution for you!
Seller Central has an inventory report that will let you either display or download inventory information for a specific day or range of dates in their system.
Or to navigate there on your own.
1. Log in to Seller Central, hover over Reports then Fulfillment, and along the left side expand the inventory section (click on "show more" ) to see Daily Inventory Report:
2. Click on Daily Inventory History.
The default view is View Online:
3. Click on the Download tab.
4. Click on the button labeled "last day" to change selection to Exact Dates:
5. Select the end of the previous year.
In this case, 12/31/2019 is selected for both the From and the To dates to give the inventory total for that day.
6. Click Request .csv Download to start processing the request.
Once completed, the download button will appear in place of the In Progress indicator ( it refreshes automatically for you but if it doesn't, just refresh the page).
7. Click to download the .csv file and the result is below:
Now that you have the .csv file, you can enter in your buy costs and calculate the value of this inventory.
1. Add a column for Buy Cost and one for Total Value, as shown below:
2. Manually enter the buy cost in the Buy Cost column as a per item amount for each item.
For inventory with multiple buy costs, use an average Cost/Unit value for this field.
For example, you purchased 100 units at $5.00, and 200 units at $4.00.
Calculate the total buy cost at each unit cost:
100 x 5 = 500, then 200 x 4 = 800.
Add the two total buy costs:
500 + 800 = 1300.
Divide this by the total units to get the average buy cost of $4.33.
1300÷ 300 = 4.33
3. Multiply the Buy Cost field by the Quantity field for each item and put the total in the Total Value field.
For example, with a Buy Cost of $10 and a Quantity of 1, you would enter $10 in the Buy Cost column of the Inventory Valuation report and $10 in the Total Value column of the report.
You can also use an Excel formula to auto calculate the Total Value.
The formula would be SUM=(Buy Cost Letter and Row Number)*(Buy Cost Column Letter and Row Number) .
Using the above example, the formula would be SUM=(E2*I2).
Place this formula into the Total Value field on the same Row under column J.
To copy this formula down the columns (the row numbers will change to the appropriate ones), click into the Total Value box with the formula and hover over the small green box to the bottom right. Click and drag the small box into the remaining rows below.
After you copy the cells down, Click on Edit>Fill>Down :
and the cells will copy down to the end of your selection area.
4. Total the cells in the Total Value column to give you your Total Inventory Valuation.
- Click in an empty cell where you want to display the Total Valuation (usually a blank cell at the bottom of the Total Value column.
- Type =sum( into the cell
- Click and drag from the last cell up to the first cell in the column with a value
- Type ) to close the formula and hit enter to reveal the total value of your inventory as of that date.
You can also type in the formula to the cell.
If the first cell was J2 and the last cell was J10, the formula would look like this:
5. Click Enter to display the sum of the Total Value column.
This will calculate the total cost of the inventory in Seller Central as of the end of the year.
6. Add in any merchant fulfilled inventory and/or local inventory as of the same date to get the true inventory valuation for that date.
Going forward, as long as you are adding in those buy costs either when listing or on the Replenishments page (if you create your shipments in Seller Central or split out different costs manually), the Inventory Valuation Report will calculate this for you automatically.
Make sure to check out our other support article if not all of your buy costs were added in before year end but need to calculate your end of year valuation called: