Calculating Forward Stock Cover in BigQuery

1. Calculating Forward Stock Cover Using Google Big Query Analytics Tips and Timesavers https://www.bensound.com/ (energy) https://www.bensound.com/

2. What is Forward Stock Cover?How is it calculated?How can we use Google Big Query to set stock holding targets using Forward Stock Cover?

3. What is Forward Stock Cover? Forward stock cover is a technique used by retailers to determine how long the current stock will last considering forecasted sales quantitiesIt is used to control the levels of stock to help ensure that the stock is held at an appropriate level with the risk of being short of stock or left with an over stock position at the end of the product life cycle

4. An Example The inventory forward cover calculation is fairly straight forward, in this example we will use a series of future monthly sales forecasts to set a target stock holdingUsing the example in the above chart we can see the following sales by monthJanuary 400February 200March 100April 50So, in total at the beginning of January if we want the product to last 4 months forwards. We need to take the forward looking total of 400+200+100+50 = 750 units to be the target stock holding

5. Risks of not getting it right Assuming we can reliably predict the demand to be 750 unitsIf we have 100 too many in January, we will be left with 100 too many in May, when there is no demand, these may have to be cleared by heavy discountingIf we have 100 too few in January, then we will miss the sales for that 100 through March and April.

6. Step by Step Guide to Calculating We will show you step by step how to use Google Big Query to calculate Forward Stock CoverFirstly, we will start from a trusted Sales Forecast by productselect* from`Sales_Forecast`s orderbyproduct, Month, Sales_Forecast

7. Step 2 – Define target stock holding Keeping our example simple, we have three productsProduct 1 is our most important product, we never want to risk being out of stock. For this we set the target to 4 months forwardsProduct 2 is less important, so we will target three monthsProduct 3 is a not so important product, we want to actually run out as the risk of not being able to sell it is too greatselects.* , case whenproduct = 1then4 whenproduct = 2then3 whenproduct = 3then2 endasStock_Target_Months from`Sales_Forecast`s orderbyproduct, Month, Sales_Forecast

8. Step 3 – Part 1 the SQL window function We use a SQL Window function to calculate the forward stock cover, We will show you the steps The window function is made up of two parts initially sum(Sales_Forecast), this is the calculation to be performedover (partition by product), this is the 'window’ In this case we want the column to return the same sub total for all sales forecasts for each product selecta.* , sum(Sales_Forecast)over(partitionbyproduct)asTotal_Product_Sales_Forecast from ( selects.*     , case whenproduct = 1then4 whenproduct = 2then3 whenproduct = 3then2 endasStock_Target_Months from`Sales_Forecast`s )a orderbyproduct, Month

9. Step 3 – Part 2 the SQL Running Total We now add an order by clause into the partition This changes the sub total into a running total selecta.* , sum(Sales_Forecast)over(partitionbyproductorderbymonth)asRunning_Total_Product_Sales_Forecast from ( selects.*     , case whenproduct = 1then4 whenproduct = 2then3 whenproduct = 3then2 endasStock_Target_Months from`Sales_Forecast`s )a orderbyproduct, Month

10. Step 3 – Part 3 the SQL Rolling Total The next thing we can do is to enhance the window function by telling it how many rows we want to use for a forward looking forecastTo do this we add ROWS BETWEEN CURRENT ROW AND 2 FOLLOWINGThis instructs the window to use the current row and the next two months give a fixed three month forward requirement figureselecta.* , sum(Sales_Forecast)over(partitionbyproductorderbymonth ROWSBETWEENCURRENTROWAND2FOLLOWING)asThree_Month_Fixed_forecast from ( selects.*     , case whenproduct = 1then4 whenproduct = 2then3 whenproduct = 3then2 endasStock_Target_Months from`Sales_Forecast`s )a orderbyproduct, Month

11. But we want to have the number of months forward vary by the setting at product level????

12. Step 3 – Part 4 the Dynamic SQL Forward Cover We need to number of rows to adjust based on the number of target months, so we use a case statement to select which optionselecta.* , case whenStock_Target_Months = 1thenSales_Forecast whenStock_Target_Months = 2thensum(Sales_Forecast)over(partitionbyproductorderbymonthROWSBETWEENCURRENTROWAND1FOLLOWING) whenStock_Target_Months = 3thensum(Sales_Forecast)over(partitionbyproductorderbymonthROWSBETWEENCURRENTROWAND2FOLLOWING) whenStock_Target_Months = 4thensum(Sales_Forecast)over(partitionbyproductorderbymonthROWSBETWEENCURRENTROWAND3FOLLOWING) else0endasDynamic_Forward_Stock_Target from ( selects.*     , case whenproduct = 1then4 whenproduct = 2then3 whenproduct = 3then2 endasStock_Target_Months from`Sales_Forecast`s )a orderbyproduct, Month

13. By breaking down the steps to calculate the forward stock cover, you can also see how the SQL window functions can be used to calculate :-Product Sub TotalsProduct RUNNING totalsProduct ROLLING totals and finally, the Dynamic Forward Stock Cover

14. For more Tips, Tricks and Timesavers, visit our websiteBusiness Analytics Blog – Select DistinctCredit: simon.harrison@selectdistinct.co.uk