If you weren't able to add in your buy costs for all of your inventory before December 31st, you may notice that the Inventory Valuation report shows some $0 buy costs.
While this report cannot be updated retroactively, you can still add buy costs when exported, just follow the below steps:
1. Be sure to add in your buy costs to the Inventory page! This will filter through to your sales transactions, profitability reports, and also your Profit and Loss report (another important part of the tax documentation you will need!)
Follow the steps in the below articles to ensure your buy costs are entered:
Adding Individual Cost per Unit, Supplier, or Date Purchased Manually
Bulk Import Accounting Details into Inventory via .csv
For items no longer in Inventory, update these costs in the Sales pages:
Bulk Import Accounting Details into Sales via .csv
2. On the Reports>Inventory Valuation report page, change the view date to 12/31.
Click the calendar icon next to "View", and change the date to 12/31. Click Done, then View.
3. Click the Export button to download your Inventory Valuation report as a .csv file to your computer.
Once the download is complete, open the file.
4. Add in the buy costs where needed under the Cost/Unit column.
You will have to manually calculate the total cost for the inventory at this point.
5. Multiply the Cost/Unit by the total of the On Hand and Inbound inventory to get Total Value.
For example, with a buy cost (Cost/Unit) of $5 and a total quantity of 15 (Inbound of 7 plus On Hand of 8), you would enter $5 in the Cost/Unit column of the Inventory Valuation report and $75 in the Total Value column of the report.
5 x 15 = 75
You can also use an Excel formula to auto calculate the Total Value.
The formula would be:
SUM=((Inbound Qty Column Letter and Row Number + On Hand Column Letter and Row Number)*Cost/Unit Column Letter and Row Number) .
Using the above example, the formula would be SUM=((D2+E2)*F2) and you would place this formula into the Total Value column.
To copy this formula down the columns (the row numbers will change to the appropriate ones), click into the Total Value box where the formula is entered and hover over the small green box to the bottom right.
Click and drag the small box into the remaining rows below.
6. For inventory with multiple buy costs, use an average cost value for the Cost/Unit 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
Add the cells in the Total Value column to get your Inventory Valuation. You can use the same method shown above to total the cells by using the sum feature.
- Select the first empty cell at the bottom of the column for Total Value
- 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 without the drag feature.
If the first cell was D1 and the last cell was D10, the formula would look like this:
=sum(d1:d10)
Once you hit enter the Total Value will display in the cell.
Going forward, as long as you are adding in those buy costs while listing (or on the Replenishments page if you create shipments in Seller Central or need to split out multiple buy costs), the Inventory Valuation Report will calculate this for you automatically.
Make sure to check out our other support article if you were not a subscriber to InventoryLab as of the end of the year, in order to calculate your end of year valuation called:
0 comments
Article is closed for comments.