Option 1
Step 1: Go to Inventory app, open Studio, and add a menu for report.stock.quantity model
Tip: From this page, you could also move the report menu inside any other menu.
Step 2: Inside the new menu page, set the next filter as favorite/default filter
Tip: In case you need it, you can also filter by forecasted receipts or deliveries
Now you can filter/group your report based on different parameters that belong to product template, product variant, date, company and/or warehouse
Example: Report for Product Category Furtniture/Office between September 1 and 30 in the Warehouse "YourCompany"
Tip: Using Studio, you could also add a pivot table view
Example: Pivot table showing forecasted quantity in products in the category "Furtniture/Office" grouped by month and warehouse
Option 2
Step 1: Add the following models to Spreadhsheets using the action button from the list view
Make sure to add the appropiate filters before inserting the list in Spreadsheet
Models to insert in spreadsheets:
Model | Technical Model Name | Filters |
Locations | stock.quant | "Internal Locations" "In Stock" |
Moves Analysis | stock.move | "Incoming" Status is not in "Done" or "Cancelled" |
Moves Analysis | stock.move | "Outgoing" Status is not in "Done" or "Cancelled" |
You can change the name of the sheets and the lists to easier indentify each table and manage the Spreadhsheets formulas.
Example:
Step 2: Add a new sheet and manually create a table with the following columns
- Date
- All Dates
- Pending Incoming
- Pending Outgoing
- Current Stock
- Accumulated Incoming
- Accumulated Outgoing
- Forecast
Use other 2 cells in the same sheet to stablish the Minimun Date and Maximun Date with the following formulas:
Cell | Formula | Explanation |
Min Date | =ROUNDDOWN( MIN( FILTER('Incoming quantities'!A:A, 'Incoming quantities'!A:A>0), FILTER('Outgoing quantities'!A:A, 'Outgoing quantities'!A:A>0), TODAY() ),0 ) | Retrieves the minimum date value between the incoming and outgoing quantities table (the Date in both tables is stored in column A) and today. This formula is ignoring the empty cells by applying the filter > 0, and rounding down to ignore the hours/minutes/seconds |
Max Date | =ROUNDDOWN( MAX( FILTER('Incoming quantities'!A:A, 'Incoming quantities'!A:A>0), FILTER('Outgoing quantities'!A:A, 'Outgoing quantities'!A:A>0) ),0 ) + 10 | Retrieves the maximum date value between the incoming and outgoing quantities table (the Date in both tables is stored in column A) and today. This formula is ignoring the empty cells by applying the filter > 0, and rounding down to ignore the hours/minutes/seconds The formula is adding 10 days more just to have a better looking graph |
For the table generated in Step 2, add the following formulas and drag them down:
A | B | C | D | E | F | G | H | I | J | K | |
1 | Date | All Dates | Pending Incoming | Pending Outgoing | Current Stock | Accumulated Incoming | Accumulated Outgoing | Forecast | |||
2 | =K2 | =IF(K2=0,TODAY(),K2) | =SUMIFS('Incoming quantities'!F:F,ROUNDDOWN('Incoming quantities'!A:A),A2) | =SUMIFS('Outgoing quantities'!F:F,ROUNDDOWN('Outgoing quantities'!A:A),A2) | =SUM('Quantities in Stock'!D:D) | =C2 | =D2 | =IF(A2="","",$E$2+F2-G2) | Minimum Date | (Formula mentioned above) | |
3 | =IF(B3>$K$3,"",B3) | =B2+1 | Drag the previous formula down from here | Drag the previous formula down from here | Don't drag formula down | =F2+C3 | =G2+D3 | Drag the previous formula down from here | Maximum Date | Formula mentioned above) | |
4 | Drag the previous formula down from here | Drag the previous formula down from here |
Brief explanation:
- K2 and K3 in the same sheet are the minimun and maximum dates respectively in my spreadsheet
- Sheet "Incoming quantities" (list from Move Analysis model):
- Column A: Dates for each pending receipt in Odoo. Rounding down the dates to ignore the hours/minutes/seconds
- Column F: "Demand" quantities for each pending receipt in Odoo
- Sheet "Outgoing quantities" (list from Move Analysis model):
- Column A: Dates for each pending delivery in Odoo. Rounding down the dates to ignore the hours/minutes/seconds
- Column F: "Demand" quantities for each pending delivery in Odoo
- Sheet "Quantities in Stock" (list from Location Report model):
- Columnd D: Inventoried Quantity
Step 3: Add a graph to show the forecast based on date
Step 4: Add global filters to filter the data you would like to see
Example of filters by location, product categories, and specific product.
Note: For locations, I stablished only one filter as "Intermediate location" for receipts and as "Source location" for deliveries, but you can create two independent filters to have more control over the location filter for receipts and deliveries. You can set one or more locations per filter at the same time, so you can see a report of one or more warehouses
Final result:
Explanation:
- I currently have 100 units in my warehouse
- I will deliver 20 units on Sept 15 Then I will have 80 forecasted units
- I will receive 10 units on Sep 22 Then I will have 90 forecasted units
- I will receive 50 units on Oct 01 Then I will have 140 forecasted units
Aditional: Add conditional formating
In order to identify easier:
- Today's date in column A and B
- Any pending recepits/deliveries in column C and D
- Current Stock in E2
- The dates when the forecasted quantities are less than or equal to 0
