PRODUCT PREP: Reconcile Inbound/Outbound Shipping Process

WHAT IS THIS TEMPLATE FOR: This template is run for each FNSKU in a shipment to reconcile inventory, calculate prep & shipping costs, update lead times, etc. This template will help you to maintain accurate Cost of Goods Sold (COGS).

Inventory Management:

Update Inventory Management

Damaged Inventory:

Account for Damaged Inventory

Check and Update Information:

Prep & Supplies Costs

Shipping Costs

For All COGS

Update Lead Time Tracking

For any in house stored inventory

Complete Workflow & File

Inventory Management:

Update Inventory Management

If there are any new & relevant prep notes (e.g., the size of poly bag needed, size of master cases needed, individual boxes need to be taped shut, etc.), add them to the most recent MM-YYYY tab of the INVENTORY MANAGEMENT Spreadsheet.

1

Update Prep Notes in Inventory Management Spreadsheet if needed

Confirm that the inventory numbers have been reduced In Transit in Seller Legend.

Confirm that the Shipment IDs have been entered in the INVENTORY MANAGEMENT Spreadsheet.

1

The In Transit units have been reduced
2

The Shipment IDs have been entered and colored appropriately

Damaged Inventory:

Account for Damaged Inventory

If any inventory was damaged/unsellable, add it to the MM-YYYY of the INVENTORY MANAGEMENT Spreadsheet (Figure 1).

1

Added damaged/unsellable inventory

Is there over $100 worth of damage? If so, start to open a case with either the supplier or shipper for reimbursement.

Note: The $100 is arbitrary. You will have to determine what amount of damage is worth it for you to ask for reimbursement.

1

I have checked the total value of the damage
2

I have decided whether or not to ask for reimbursement

Check and Update Information:

Prep & Supplies Costs

Enter the product in the INTERNAL PRODUCT PREP TRACKING Spreadsheet and calculate prep costs.

If prep or supply costs have changed since the prior order, update those in the INVENTORY MANAGEMENT Spreadsheet MM-YYYY tab (it will be in the MM month that the PO was issued).

Shipping Costs

If the product shipping costs are easy to calculate and isolate (e.g., product wasn't shipped in a group with many others), enter the current shipping costs in the INVENTORY MANAGEMENT Spreadsheet MM-YYYY.

Note: If products were shipped LTL, you will do shipping costs when you do overall pallet reconciliation. If some products were shipped SPD and some LTL, enter the SPD costs as a note in the Google sheet and calculate overall costs when the LTL pricing is available.

For All COGS

If you are updating an older MM-YYYY sheet, you need to copy costs over to the following sheets. For instance, if prep costs changed in 01-2017, and it's now Feb of 2017, copy the Jan 2017 costs into the 02-2017 sheet.

Expiry Dates

If you have a variety pack, enter the earliest expiry date in the spreadsheet, and note the individual expiry dates in a note in that cell.

If COGS changed from the last order, transfer COGs from the INVENTORY MANAGEMENT Spreadsheet to the Amazon Product COGS Spreadsheet.

1

Enter the product in the Internal Product Prep Tracking Spreadsheet
2

Enter any updated prep costs in the Inventory Management Spreadsheet MM-YYYY
3

Update outbound shipping costs in MM-YYYY sheet
4

Update any other COGS in MM-YYYY sheet
5

Copy all updated COGS to future MM-YYYY tabs
6

Add expiry date to MM-YYYY sheet
7

Update Amazon Product COGS Spreadsheet

Update Lead Time Tracking

Update Lead Time Tracking tab on INVENTORY MANAGEMENT Spreadsheet (Figure 1).

Date Shipped (if not already entered and shipping document is included - if you cannot find this information it is okay to leave blank)
Date Received
Date Processed
Date Shipped in house to Amazon

1

Update Lead Time Tracking tab

For any in house stored inventory

If any units received will be stored in house, you'll need to update inventory numbers.

Enter the number that will be stored in house in the INVENTORY MANAGEMENT Spreadsheet MM-YYYY tab (Units Received (in house), column AA) - as shown in Figure 1.