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!
This is a two-part process, the first part is getting your inventory total from Seller Central and the second part is finding the inbound inventory that Amazon doesn't yet count in the inventor totals.
First, we find the inventory total in Seller Central.
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>Fulfillment, and along the left side expand the inventory section (click on "show more" ) to see Daily Inventory History Report:
2. Click on Daily Inventory History.
The default view is View Online:
3. Click on the Download tab.
4. Click the "Event Day" drop-down to change the selection to Exact Dates:
5. Select the end of the previous year.
In this case, 12/31/2020 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 to your computer and open it. 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.
Next, we'll add the costs.
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 Unlisted Inventory as of the same date to get the true Inventory Valuation for that date.
The next part is finding the Inbound Inventory Totals.
To do this, navigate to your Inventory>Manage FBA Shipments page:
Once there, select Custom date range for the date range from the default All that is selected:
Once Custom date range is selected, you will have the option to input a from and to date for the search range. We suggest putting in a range so that you see the shipments during that time frame and can see when the last update was done.
The items would be inbound if the shipment creation was prior to 12/31 and the last update was after that date from what we can tell on this page. Once you have set the dates, click Filter to display the results.
Click the arrow next to Track shipment and select the Download SKU list for any shipments that are applicable:
You would then have to add these to the spreadsheet you created in the previous step and add in the buy cost to get a total of inbound inventory.
Keep in mind that, depending on when the shipment arrived, some of the items may have been checked in and active by the 31st. This is not an exact science and only serves to give you a general Inventory Valuation number for inbound. For the exact figures, you may be able to reach out to Seller Central in order to get that information.
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: