Amazon FBA Inventory Spreadsheet Template
Haven’t downloaded the Amazon inventory spreadsheet template yet?
Use the Quick Tutorial Steps below with the video above to get started, or use the Full Written Tutorial, depending on your learning preference.
Quick Tutorial Steps
Inventory Planner
The inventory planner is composed of 3 tabs.
Cells with formulas are colored yellow. These cells must not be changed.
Danger areas will turn the cells red and should signal to you to take action immediately to remedy a situation.
The pink cells are those that need initial entries during the initial set up phase
Inventory Calculator
Initial Setup
- Add Your ASIN, Product Name, Supplier & Warehouse (if applicable).
- Add Standard Lead Time & Transfer Time.
- Add Total Target Max Days of Stock. This is the total amount that you want to have in stock when your inventory lands. This includes FBA and Warehouse stock. If you do not have a warehouse, this should be the same number as your FBA Target Max Days of Stock. This is what your order amounts will be based on.
- Add FBA Target Max Days of Stock. This is only for FBA and does not include your warehouse inventory. This is what your transfer amounts will be based on.
- Add FBA Buffer Stock amount in days. This will allow your buffer amount to adjust to accommodate both high and low sales periods.
- Add Warehouse Buffer Stock amount in days.
Sales Spikes Log
No Initial Setup Required
Logging Sales Spikes
When a sales spike occurs, enter the date it happened. Then enter the total number of units sold on the spike day. This full day of sales will be removed from the velocity calculation and the average will be offset.
That’s it. Everything else will auto-calculate and this data will be plugged into your Inventory Calculator.
Stock Out Log
No Initial Setup Required
Logging Stock Outs
When a stock out occurs, log the start and end dates. If you are projecting your sales during a stock out, enter the end date as today’s date. This will deduct these days from your average sales velocity.
That’s it. Everything else will auto-calculate and this data will be plugged into your Inventory Calculator.
Inventory Planning Method
- Add any sales spikes to your Sales Spikes Log per above.
- Add any stock outs to your Stock Out Log per above. The inputs of both of these logs will calculate into your Inventory Calculator to filter out Sales Spikes & Stock Outs.
- Select Averages you want to include in your Adjusted Sales Velocity (180, 90, 60 etc.) but putting a checkbox in the box under columns labeled INCLUDE x Day Average.
- Download every average you intend to use (180, 90, etc.). You can easily do this by downloading the following report: Reports > Business Reports > Detail Page Sales & Traffic by Child Item > Set Date Range > Download to CSV. Your Start & End Dates for date range (180, 90, etc.) can be found in the Sales Spikes or Stock Out Logs at the top of each section. Ensure that Amazon has updated the reports for the previous day so that you are capturing sales for yesterday in your calculation.
- Plug in your averages, i.e. 180, 90, 60, 30, 15, 7, 2 Days of Sales per ASIN. NOTE: You do not have to plug in averages for time periods you are not used, BUT [WARNING!] you must deselect the checkbox next to any sales you are not including or it will likely throw off your velocity calculator.
- OPTIONAL: Plug in any daily velocity for Multi-Channel Fulfillment Sales such as Shopify.
- OPTIONAL: Plug in any Lightning Deals or Sales you may have in the Lightning Deals/Sales section.
- OPTIONAL: Plug in any seasonal sales increases in the Seasonality section.
- Plug in FBA Inventory amount.
- RECOMMENDED: Plug in FBA Receiving amount as well as the Customer Orders amount. This is for increase accuracy and becomes quite important when you have inventory currently checking it and your Receiving amount is high. You can find this information under Inventory > Manage Inventory under the Reserved column of each SKU.
- Enter any Warehouse Inventory you have.
- Enter any POs you have on order.
- Enter any transfers you have en route to FBA. This should be inventory that is not currently counted in our Warehouse, FBA or Reserve amount so as not to double count.
- Once all these data points are entered, you will be able to see your remaining days of inventory, your reorder and transfer dates and the quantities needed for each.
That’s it! I hope you have success with the inventory spreadsheet, outgrow it, and then decide to use our amazon inventory management software someday! SoStocked.com was built to take the confusion and human error out of inventory management ❤️
Full Written Tutorial
Let’s walk through the DIY Amazon inventory spreadsheet template.
I built this Amazon inventory template using similar strategies that my team used to create our Amazon inventory management software.
With this FBA inventory spreadsheet template, you’ll be able to:
- Factor out stock outs and sales spikes
- Add additional sales growth
- Factor in things such as Lightning Deals
- Factor seasonal sales spikes
- Incorporate any incoming shipments and any inventory in your warehouse
- Come up with the total order needed for purchase orders or the total amount needed to be transferred from your warehouse to FBA
Heads up, this DIY Amazon inventory template is best for those who have a low number of SKUs. The Amazon inventory spreadsheet template allows you to calculate up to 10 different ASINs/SKUs.
If you’re like me, though, you may want to consider automating with software when you have more than 2-3 SKUs. So consider taking an in-depth look at our SoStocked Review when managing your Amazon inventory in a spreadsheet starts feeling like a nightmare.
As you increase your number of SKUs, it gets a lot more stressful and time-consuming to manage inventory using spreadsheets manually. The margin for manual mistakes increases as well.
Do’s and Don’ts
All the pink cells in the spreadsheet are for the initial setup of your brand. All the cells in yellow are formulas.
These cells must not be changed because changing them would negatively impact your entire forecast. All the cells in white are meant for entering in your actual Amazon data.
Danger areas will turn the cells red and signal you to remedy a situation immediately.
Calculating Sales Velocity
There are a couple of different ways to calculate your sales velocity with the FBA inventory spreadsheet template.
Adjusted Average Velocity
For calculating the adjusted average velocity, you have to first put in the averages; you can put the 180 days, 90 days, 60 days, 30 days, 15 days, 7 days, and 2 days averages and then you use the checkbox to determine what averages you’ll be using. For example, if we wanted to use a 7 day, a 2 day, and a 15-day average, you can check their respective boxes to factor them into your averages.
Now, we can also factor in growth. For instance, if we wanted to grow fairly aggressively with one of our products (maybe by doing some Sponsored Ads), we can increase our sales growth by 10%. Just add ten under the growth column, and this will increase the average velocity by 10%.
If we have sales that are off-Amazon and are not calculated into our Amazon sales, we can factor them in by adding them under the MCF Sales Velocity (Daily) column. For example, suppose we sell 10 units per day on our Shopify account. In that case, we can add 10 under the MCF Sales Velocity (Daily), and it’s going to increase our average daily velocity by 10.
Adjusted Average Velocity
You can also calculate a manual velocity with the Amazon inventory spreadsheet.
Manual velocity is useful if you don’t have any data on the product such as when you are going to launch a brand-new product.
Last Year’s Sales
And finally, you can put the numbers of units you sold last year under the Past Sales Override column and put a start and end date for the period in which you made these sales, which would calculate your average daily velocity for last year. Then, make sure to check the Override Averages with Past Sales box to replace the Adjusted Average Velocity with this new velocity based on last year’s sales.
So, if you’re going to order for Christmas, pull the same sales period from the past year and put it here. For instance, if you want to order your stock to cover a certain period, just enter the start and end dates for the period.
Next, pull your past year sales data for that period and put them under the Past Sales Override column. One issue here though is that you’ll have to remove your stockouts and sales spikes. If you don’t filter these outliers out, you may end up over or under-ordering based on spikes or stock outs that you don’t intend to repeat next year. More on that in the next section.
Factoring in the Sales Spikes and Stockouts
Tracking and calculating your sales spikes and stockouts is extremely important for accurately calculating your inventory needs. Often, when you stock outs or over-order, it’s because the data that you put into your reorder calculation is wrong.
For example, if you had a sales spike because of a Lightning Deal that you’re not going to have in the future. That would have increased your sales significantly more than expected, which increases the amount of inventory you thought you had to order.
Or you had a stockout for 10 of the last 30 days, which you failed to factor into your calculation, and it completely threw off your average. Therefore, we need to figure out what the true average would have been if you had been in stock the entire time.
We built Amazon inventory forecasting software to factor these things automatically. So when building out the Amazon inventory spreadsheet, we incorporated these features as well. It’s more manual in the spreadsheet, but still useful and accurate.
Logging Sales Spikes
In the Amazon inventory template, there are separate sheets for logging your stockout and your sales spike days. So, if you notice that you have a particular spike day and you don’t think it’s going to happen again, make sure to log this date and the number of units sold into the Sales Spike Log sheet. A good rule of thumb is to log anything that is 150% of your average sales.
When you plug in a sales spike and the date it occurred, the spreadsheet will compare it with the current date to determine whether it’s a valid or an expired spike, meaning whether it occurred within or before 180 days.
Any of the expired spikes that occurred earlier than 180 days will not be factored into the calculations. If it is a valid spike, it is going to automatically be subtracted from your average velocity calculations. This gives you an accurate representation of your true average for that product.
Logging Stock Outs
You can log your stock outs in the Stock Out Log sheet. In this sheet, you need to enter the date you stocked out and the date you came back into stock. The spreadsheet formula will then calculate the number of days you stocked out, then it’s going to adjust your formulas to count and average only the days you were actually in stock.
For example, if you stocked out for 12 days in the past 30 days, it’s going to take the adjusted number of days as 18 days instead of 30. If you also had, say, five spiked days within those 30 days, it’s going to subtract those days as well. The adjusted number of days will come out to be 13. This will more accurately calculate the velocity because it’s only factoring in the number of days during which you were actually in stock and selling normally.
Initial Setup
This part of the Amazon inventory spreadsheet is colored in pink and is used for entering your:
- Brand information
- Lead time
- Transfer time
- Total Target Max Days of Stock: the total amount of inventory you want to be holding when your inventory lands.
- FBA Target Max Days of Stock: the number of days worth of stock you want at FBA.
If you don’t have a warehouse, the Total Target Max Days of Stock and FBA Target Max Days of Stock are going to be the same.
Incorporating Marketing Plans
I believe that adding your marketing plan into your inventory calculations is extremely important if you want to get a true picture of your inventory. Otherwise, you’ll advertise (Sponsored Ads, Lightning Deals, etc.) your way right into a stock out. This is just wasteful because you could have sold that inventory at full price, and now you’re taking a discount due to the Lightning Deal, a coupon discount or the cost of your ads, and ultimately stocking out when you could have sold for full price, kept your margins high and not stocked out.
Additionally, there may be times when you plan for Valentine’s Day, for instance, but you didn’t plan for Mother’s Day or Father’s Day, and you have a Mother’s Day Sale on a whim, which causes you to run yourself into a stock out before you even get to Father’s Day. Therefore, doing this planning in advance is very important to ensure you’re scaling your business properly.
In the Amazon inventory spreadsheet template, you can easily factor in your marketing campaigns by putting the extra number of units you need for those campaigns under the Lightning Deals section. Each of these extra units gets added to the entire order recommendation.
Incorporating Seasonality
Similar to Lighting Deals, you can also incorporate seasonal changes in the FBA inventory spreadsheet template. All you need to do is put the seasonality start and end date and the percentage increase in your sales during that period. The inventory management spreadsheet will add this amount to your final recommendation, so you can always be sure to have enough units available.
We don’t recommend you using the past sales override along with the seasonality. The reason for this is that if you’re pulling past sales data and then you’re also adding a seasonal increase those numbers can get over-bloated, and the velocity would potentially double your sales projections.
Inventory Section
Under the inventory section of your Amazon inventory template, you can place your FBA Available Inventory, your FBA Receiving, and your Reserved Inventory minus Customer Orders. Reserved minus Customer Orders can be calculated from your Amazon Seller Central under Inventory > Manage Inventory under each SKU’s Reserved column.
When you plug in all this information, you will get an accurate picture of your total FBA inventory under the Total FBA Inventory column by adding FBA Available Inventory and FBA Receiving minus your customer orders.
You can also put in the FBA buffer stock days, meaning the number of days worth of stock that you want to have in place at all times. It’s just the little extra cushion, which helps to guarantee that you’re not going to run out of stock.
This helps you to ship early enough so that by the time your shipment arrives at Amazon, you’ll have reached your buffer stock limit. The Total FBA Less Buffer column shows how much inventory you have outside of your buffer stock.
If you have a warehouse, you can also enter your warehouse inventory and your warehouse buffer stock days to get similar results for your warehouse too.
Incorporating Inbound Inventory
Your inventory spreadsheet for Amazon also gives you the option to plan in terms of your Inbound Shipments.
You can put your Purchase Orders (POs) and your warehouse transfers into the spreadsheet. Your Purchase Orders (POs) are items that you’ve ordered from your supplier and your warehouse transfers represent the inventory that you’ve asked your warehouse to send to Amazon.
We don’t have an Order Tracker built into this spreadsheet (check out or SoStocked software for that), but you can do this manually in the spreadsheet by entering the date when you place the PO and the quantity you ordered.
Don’t forget to remove the Purchase Orders and warehouse transfers once they’ve landed at the warehouse or at Amazon.
Days Remaining Section
All of the inventory amounts are added and then divided by your velocity in order to calculate how many more days your stock will last you on Amazon. This then goes into the Days Remaining section which will show you:
- Your remaining days for FBA
- Remaining Days for FBA + Warehouse
- Remaining for FBA + Warehouse + Inbound
- FBA less buffers
- FBA + Warehouse less buffers
- FBA + Warehouse + Inbound less buffers.
Reorder Recommendations
This section of the inventory spreadsheet template gives you all the recommendations for reordering inventory to Amazon or to your Warehouse.
Once you’ve entered all the data points, you will be able to see your remaining days of inventory, your reorder and transfer dates, and the quantities needed for each.
The Order Quantity column will give you the order quantity based on a lot of different factors such as your velocity, Lightning Deals, seasonality, Inbound orders, and buffer stocks.
Conclusion
Was this tutorial valuable? I hope so 🙂
It will take some time and a little trial-and-error to get used to this spreadsheet as it’s pretty sophisticated.
If you have 2-3+ SKUs and want to automate your inventory management and forecasting, consider using an inventory software like SoStocked.com instead of an Amazon inventory spreadsheet.
That way, you’ll get a crystal-clear view into your inventory, keep track of your orders from production through FBA check-in, accurately forecast orders, and make decisions based on good date vs. gut-feel.
Comment below if you have any questions about the spreadsheet. Thanks for stopping by!
Need more information?
- Send Message: We typically reply within 2 hours during office hours.
- Schedule Demo: Dive deeper into the nuances of our software with Chelsea.
- Join Live Upcoming Webinar: New to Amazon inventory management? Learn three inventory techniques you can implement right away.