Forecast Your Sales For Better Planning
The most significant benefit of Defog is that it integrates Amazon Seller Central’s data with Google Sheets. Sellers may then use the data to do many things, one of the most interesting being forecasting their future sales.
In this previous article, we used Defog to forecast the total units and revenue. In this article, we will show you how to forecast units and revenue per SKU by walking you through creating a simple table with each SKU with past data and another table for the forecast. You may also use the principle illustrated in this post to generate a forecast for next week’s or quarter’s sales. You will be making a table like the one below:
As soon as Defog updates the data, the forecast table we will create below will be automatically updated. We will use Google Sheets’ forecast formula ().
Step 1: On your desktop browser, open your Defog spreadsheet on the OrdersTable tab.
Image 1 – Open Defog on the OrdersTable tab
Step 2: Start by selecting the initial column of your pivot table. Click on the Column E.
Image 2 – Click column E
Step 3: Now select the final column of your pivot table. Shift-click on Column AC. You have selected all rows between Column E and AC, including the ones that will be added in future updates to the OrdersTable.
Image 3 – Shift-click column AC
Number of units and sales amount per month per SKU
Step 4: With the table selected in the previous steps, click Insert on the top Google Sheets menu and then click Pivot table (image 4.1). Then click Create in the pop-up window (Image 4.2). You will create a new tab on your Defog that will look like Image 4.3 below.
Image 4.1 – Click Insert > Pivot table
Image 4.2 – Create a pivot table on a new sheet
Image 4.3 – Your new pivot table
Step 5: Now is the time to populate your pivot table with the desired data. Let’s start by selecting the date period. First, click Purchase Month Year (PT) on the data columns to the right of the tab.
Image 5 – Click on Purchase Month Year (PT)
Step 6: Drag and drop the Purchase Month Year (PT) column to the area below the session Columns on the pivot table configuration column (image 6.1). Unselect the option to show totals in the new Purchase Month Year (PT) box. All months with data will appear in ascending order in the top row on your pivot table.
Image 6.1 – Purchase Month Year (PT) under Columns.
We must ensure that our table only has closed months to avoid using incomplete data from the current month in the forecast calculation. Also, we don’t need all the months available for this forecast; let’s filter the last six complete months with data. When we created this post, the current month was September 2024, so we will filter out 09/01/24 and start on 02/01/2024, six months before August 2024.
Drag and drop the Purchase Month Year (PT) column below the session Filters on the pivot table configuration column to do that. Next, on the Purchase Month Year (PT) box on Filters, click on Filter by condition and then on Custom formula is (the last option at the bottom of the menu). Copy and paste the following formula in the blank text box and click OK (Image 6.2).
=AND('Purchase Month Year (PT)' >= date(year(today()),month(today())-7,1) , 'Purchase Month Year (PT)' < date(year(today()),month(today()),1))
Image 6.2 – Purchase Month Year (PT) box on the Filters session
Select, drag, and drop the SKU column to the area below the session Rows on the pivot table configuration column (image 6.3). On the new SKU box, unselect the option Show totals. All unique SKUs appear in the first row on your pivot table.
Image 6.3 – SKU under Rows.
Step 7: To select the pivot table values, let’s use the exact drag and drop mechanics above. Drop the Quantity Ordered below Values.
Image 7 – Quantity Ordered on Values
Step 8: After a few cosmetic adjustments, the table looks like the image below.
Image 8 – Base table with units ordered for the forecasting
Step 9: Copy and paste the pivot table to the blank space on its right. Edit this new table so it has the Item Price (the sales amount) instead of the Quantity Ordered in the Values session of the Pivot Table configuration. Again, after a few cosmetic adjustments, the two parallel tables look like the image below.
Image 9 – Base tables with units ordered and sales amount for the forecasting
The forecast
You have completed your pivot tables with the data for the forecasting. We will provide the forecast formula with data from the last six months and forecast the next two months.
Step 10: On the right side of your base pivot tables, a couple of columns after them, create a new table like the one below with 09/01/2024 on cells V2 and Y2 and 10/01/2024 on cells W2 and Z2.
Image 10 – Blank forecast table
Step 11: Add the formula below on cell V3 (image 11.1) and copy it to the last row with a SKU. Then, copy the same formula to W3 and all the cells below it.
=round(FORECAST(V$2,$B3:$H3,$B$2:$H$2))
Image 11.1 – Adding the formula to forecast total units ordered
Then, add the formula below to cell Y2 and repeat the copies above to add the formula to all blank cells.
=FORECAST(Y$2,$L3:$R3,$L$2:$R$2)After a few other cosmetic adjustments, your forecast is ready and looks like Image 11.2 below.
Image 11.2 – 2-Months forecast for units and sales amount per SKU
Please note that if you sell in more than one country in the same Amazon Region (North America, Europe, or Far East), Defog will download data from all countries (all marketplaces) to the same OrdersTable. You will have to do the forecast per country or currency, as you cannot sum the total sales amount in different currencies.
If you want to learn what a particular column stands for on Defog’s tables, please visit our glossary.
Thank you for reading this post. If you still haven’t used Defog, you can do so for free here.
If you need any help, we are here for you.
Disclaimer: Defog is not responsible for any decisions made by the reader of this post regarding forecasting or any other use of the data and formulas provided.
