Building a simple forecast solution on SQL Server

Pricing Forecast is a marketing function that requires a lot of data crunching.

Normally there are some factors that the Marketing team wants to adjust to find out just the optimum pricing for their products.

Accordingly, the forecast solution needs to be fairly flexible but still be powerful enough to make the necessary and sometimes even complex calculation. Otherwise, if the design is too rigid and requirements change slightly, then major changes in design need to be made, which in turn means whole rounds of testing.

The Challenge

To make a pricing forecast on future sales based on historical by changing the resolution split and prices, taking into account current and future market trends as well as business objectives and limitations.

Given models

Here is the new verses current pricing.

Current Price(£) NEW Price(£)
Product 1 5
Product 2 25 Product 2 25
Product 3 50 Product 3 50
Product 4 100 Product 4 100
Product X 150
Product 5 200 Product 5 250
Product 6 300 Product 6 350

It is assume that Product 1 will be removed. The strategy also estimates that people who used to buy Product 1 will buy Product 2. A new Product X is introduced that is the bridge between Product 4 and 5 and there are price increases for Products 5 and 6.

The Design

Next, a mapping table needs to be created to map the volume of sales of the original products to the new products based on the new model. Here’s the table in SSMS.

Product Mapping

Figure 1: Product Mapping (in SSMS)

Column headers represent the original products and row headers represent the new products.

From the table you can see that the assumptions were made that people who used to buy Product 1 will now purchase Product 2 – all of product 1 sales volume is added to the new Product 2 volume. Also, an introduction of Product X will probably split buyers who were buying product 5 due to the price change. It is assumed 50% of buyers will buy Product X and the other 50% will buy Product 5.

The reason for having a grid structure for resolution mapping is for flexibility. The split ratio can be modified at any time and increased beyond 100% as well to simulate projected volume increases without any change in design. The result is instantaneously calculated as you will see later.

Pricing

The pricing is updated in a separate table. Here’s a snapshot from SSMS:

Figure 2: Pricing Table

This table allow for flexibility of Price changes without any design change.

Algorithm Application:

Historical Data:

ID DATE Product Revenue Volume
1 01/08/2013 Product 1 100 1
2 01/08/2013 Product 2 200 1
3 01/08/2013 Product 3 200 1
4 01/08/2013 Product 4 400 1
5 01/08/2013 Product 5 300 1
6 01/08/2013 Product 6 100 1

Here’s a sample of the sales data.

Aggregated Data

  1. First, in SQL Server Management Studio (SSMS), create a view of the aggregated data:
CREATE VIEW [dbo].[vw_CurrentData]
AS
SELECT [DATE]
,[Product]
,COUNT([Product]) AS [Product Count]
FROM [Test].[dbo].[Products]
GROUP BY [DATE]
,[Product]

Here’s the result:

DATE Product Product Count
01/08/2013 Product 1 50
01/08/2013 Product 2 140
01/08/2013 Product 3 30
01/08/2013 Product 4 80
01/08/2013 Product 5 170
01/08/2013 Product 6 150
  1. Create a view of the Pivot data to get the volume sold.
CREATE VIEW [dbo].[vw_CurrentVolume]
AS
WITH PIVOTDATA AS (
SELECT [DATE]
,[Product]
,[Product Count]
FROM [dbo].[vw_CurrentData]
)
SELECT [DATE]
,ISNULL([Product 1],0) AS [Product 1]
,ISNULL([Product 2],0) AS [Product 2]
,ISNULL([Product 3],0) AS [Product 3]
,ISNULL([Product 4],0) AS [Product 4]
,ISNULL([Product 5],0) AS [Product 5]
,ISNULL([Product 6],0) AS [Product 6]
FROM PIVOTDATA
PIVOT (SUM([Product Count]) FOR [Product] IN ([Product 1],[Product 2],[Product 3],[Product 4],[Product 5],[Product 6])) AS P

Here’s the result:

DATE Product 1 Product 2 Product 3 Product 4 Product 5 Product 6
01/08/2013 50 140 30 80 170 150
  1. Based on the volume sold (above), multiple the result by the Price to get the current turnover. I am ignoring the actual price in the original sales table because we want to compare like with like, and actual prices could include discounts.

I have created a view again because I will reuse the turnover and volume views to calculate the new turnover and volume forecasts.

CREATE VIEW [dbo].[vw_CurrentTurnover]
AS
WITH PIVOTDATA AS (
SELECT D.[DATE]
      ,D.[Product]
      ,D.[Product Count]*P.[Price] AS Turnover
  FROM [dbo].[vw_CurrentData] D
  INNER JOIN [dbo].[Pricing] P ON D.Product = P.[Product] AND P.[Model] = 'Current'
)
SELECT [DATE]
            ,ISNULL([Product 1],0) AS [Product 1]
            ,ISNULL([Product 2],0) AS [Product 2]
            ,ISNULL([Product 3],0) AS [Product 3]
            ,ISNULL([Product 4],0) AS [Product 4]
            ,ISNULL([Product 5],0) AS [Product 5]
            ,ISNULL([Product 6],0) AS [Product 6]
FROM PIVOTDATA
PIVOT (SUM(Turnover) FOR [Product] IN ([Product 1],[Product 2],[Product 3],[Product 4],[Product 5],[Product 6])) AS P

Here’s the result:

DATE Product 1 Product 2 Product 3 Product 4 Product 5 Product 6
01/08/2013 250 3500 1500 8000 34000 45000

 

Building the forecast data

  1. Start by doing a mapping of the original historical volume based on the aggregated data by the product map we did earlier (See image 1). We use a Cross Join to achieve this, then add up the volume for each new product.
CREATE VIEW [dbo].[vw_NewData]
AS
WITH CTE AS (
SELECT CU.[DATE]
,CU.[Product 1]
,CU.[Product 2]
,CU.[Product 3]
,CU.[Product 4]
,CU.[Product 5]
,CU.[Product 6]
,CO.[Product]
,CAST(CO.[Product 1] AS DECIMAL(10,5))/100 AS [RatioProduct1]
,CAST(CO.[Product 2] AS DECIMAL(10,5))/100 AS [RatioProduct2]
,CAST(CO.[Product 3] AS DECIMAL(10,5))/100 AS [RatioProduct3]
,CAST(CO.[Product 4] AS DECIMAL(10,5))/100 AS [RatioProduct4]
,CAST(CO.[Product 5] AS DECIMAL(10,5))/100 AS [RatioProduct5]
,CAST(CO.[Product 6] AS DECIMAL(10,5))/100 AS [RatioProduct6]
FROM [dbo].[vw_CurrentVolume] CU
CROSS JOIN [dbo].[ProductMapping] CO
)
SELECT [DATE]
,[Product 1]
,[Product 2]
,[Product 3]
,[Product 4]
,[Product 5]
,[Product 6]
,[Product]
,[RatioProduct1]
,[RatioProduct2]
,[RatioProduct3]
,[RatioProduct4]
,[RatioProduct5]
,[RatioProduct6]
,(([Product 1]*[RatioProduct1]) + ([Product 2]*[RatioProduct2]) + ([Product 3]*[RatioProduct3])
+ ([Product 4]*[RatioProduct4]) + ([Product 5]*[RatioProduct5]) + ([Product 6]*[RatioProduct6])) AS [New Volume]
,CAST(ROUND((([Product 1]*[RatioProduct1]) + ([Product 2]*[RatioProduct2]) + ([Product 3]*[RatioProduct3])
+ ([Product 4]*[RatioProduct4]) + ([Product 5]*[RatioProduct5]) + ([Product 6]*[RatioProduct6])),0) AS INT) AS [New Volume Round]
FROM CTE

Here’s the result:

DATE Product 1 Product 2 Product 3 Product 4 Product 5 Product 6 Product New Volume
01/08/2013 50 140 30 80 170 150 Product 2 190
01/08/2013 50 140 30 80 170 150 Product 3 30
01/08/2013 50 140 30 80 170 150 Product 4 80
01/08/2013 50 140 30 80 170 150 Product X 85
01/08/2013 50 140 30 80 170 150 Product 5 85
01/08/2013 50 140 30 80 170 150 Product 6 150

The column headers are the old products, and the “Product” column represent the new products. I have omitted the intermediary columns used to calculate the final New Volume column. Note that I have also rounded the numbers because product volume should be whole numbers.

The New Volume for Product 2 is the volume sum of Old Products 1 and 2 as we had planned. And the volume of Old Product 5 is now split 50/50 between New Products 5 and X. Basically, this should follow the mapping we stated in the mapping table.
Create the New Volume table by pivoting the data

CREATE VIEW [dbo].[vw_NewVolume]
AS
WITH PIVOTDATA AS (
SELECT [DATE]
      ,[Product]
      ,[New Volume Round]
  FROM [dbo].[vw_NewData]
)
SELECT
    [DATE]
    ,ISNULL([Product 2],0) AS [Product 2]
    ,ISNULL([Product 3],0) AS [Product 3]
    ,ISNULL([Product 4],0) AS [Product 4]
    ,ISNULL([Product X],0) AS [Product X]
    ,ISNULL([Product 5],0) AS [Product 5]
    ,ISNULL([Product 6],0) AS [Product 6]
FROM PIVOTDATA
PIVOT (SUM([New Volume Round]) FOR [Product] IN ([Product 2],[Product 3],[Product 4],[Product X],[Product 5],[Product 6])) AS P

The result:

DATE Product 2 Product 3 Product 4 Product X Product 5 Product 6
01/08/2013 190 30 80 85 85 150

Here is the volume again in a more readable format.

3. Calculate the forecast Turnover

CREATE VIEW [dbo].[vw_NewTurnover]
AS
WITH UNPIVOTDATA AS (
SELECT [DATE], [Product], [Product Count]
FROM [dbo].[vw_NewVolume]
UNPIVOT( [Product Count] FOR [Product] IN  ([Product 2],[Product 3],[Product 4],[Product X],[Product 5],[Product 6])) AS U
),
PIVOTDATA AS (
SELECT U.[DATE]
,U.[Product]
,U.[Product Count] * P.Price AS Turnover
FROM UNPIVOTDATA U
INNER JOIN [dbo].[Pricing] P ON U.[Product] = P.[Product] AND P.[Model] = 'New'
)
SELECT [DATE]
,ISNULL([Product 2],0) AS [Product 2]
,ISNULL([Product 3],0) AS [Product 3]
,ISNULL([Product 4],0) AS [Product 4]
,ISNULL([Product X],0) AS [Product X]
,ISNULL([Product 5],0) AS [Product 5]
,ISNULL([Product 6],0) AS [Product 6]
FROM PIVOTDATA
PIVOT (SUM(Turnover) FOR [Product] IN ([Product 2],[Product 3],[Product 4],[Product X],[Product 5],[Product 6])) AS P

The result:

DATE Product 2 Product 3 Product 4 Product X Product 5 Product 6
01/08/2013 4750 1500 8000 12750 21250 52500

Comparison

Now, compare the historical turnover with the forecast turnover.

DATE Product 1 Product 2 Product 3 Product 4 Product 5 Product 6
01/08/2013 250 3500 1500 8000 34000 45000

Table 1: Historical Turnover

Total historical turnover = £92,250

DATE Product 2 Product 3 Product 4 Product X Product 5 Product 6
01/08/2013 4750 1500 8000 12750 21250 52500

Table 2: Forecast Turnover

Forecast total turnover = £100,750

That is a 9% increase in turnover. And all from removing Product 1 which was not selling very well, and an introduction of Product X. Not forgetting that the price of products 5 and 6 were increased.

Reality Check

Hold on a minute, does this reflect the real business scenario?

If Product 1 is removed, it is estimated that 20% of people who used to buy Product 1 will not buy Product 2. These customers will be lost.

Therefore going back to the product mapping table, we need to change the percentage of Product 1 that maps to Product 2 from 100 to 80.

Back in SSMS, right-click on the Product Mapping table and click on EDIT.

Change the Change Type to UPDATE:

Here’s the table with the SQL pane active. You can just use pure SQL if you prefer but this is so much easier because of the user interface.

Where the value of Old Product 1 intersects New Product 2, change the value from 100 to 80.

You don’t even have to click anything to update. Just hit enter after you are done.

Since you have build all the views previously, no changes are necessary, just run the new forecast view that you created earlier. Here’s the result:

DATE Product 1 Product 2 Product 3 Product 4 Product 5 Product 6
01/08/2013 250 3500 1500 8000 34000 45000

Table 3: Historical Turnover

Total historical turnover = £92,250

DATE Product 2 Product 3 Product 4 Product X Product 5 Product 6
01/08/2013 4750 1500 8000 12750 21250 52500

Table 4: Original Forecast Turnover

Original forecast turnover = £100,750

DATE Product 2 Product 3 Product 4 Product X Product 5 Product 6
01/08/2013 4500 1500 8000 12750 21250 52500

Table 5: New Forecast Turnover

New Forecast Turnover = £100,500

There is still a 8.9% increase in turnover, which is not bad at all. This is mainly because the volume of Product 1 sold was low, compare to the other products.

2nd Reality Check

The introduction of Product X should increase sales because a need was identified and met. However, the price increase in Products 5 and 6 might put a small number of people off. So let’s assume there is a 10% increase in sales volume of Product X from its current value and a 5% drop in volume sales for Products 5 and 6.

Here is the product mapping table again with the changes we just specified.

Note that the volume of Product 5 was reduced by only 3%. This is because we are specifying a 5% increase in the forecasted Product 5 volume, rather than the old Product 5 volume which was split 50/50 previously. Hence a 2.5% decrease in the original value will result in a 5% decrease in the forecasted value.

The current product mapping table does not take decimal value, so the figure was rounded up. You can always set the column type to be decimal instead of integer as it is set here.

DATE Product 2 Product 3 Product 4 Product X Product 5 Product 6

01/08/2013

180

30

80

85

85

150

Table 6: Forecasted Volume before adjustment

DATE Product 2 Product 3 Product 4 Product X Product 5 Product 6

01/08/2013

180

30

80

94

80

143

Table 7: Forecasted Volume after adjustment

We have made an increase of 10.6% for Product X and a decrease of 5.8% for Product 5 and a decrease of 4.7% for Product 6.

This is not exact because of the small sample size used.

Result

DATE Product 1 Product 2 Product 3 Product 4 Product 5 Product 6
01/08/2013 250 3500 1500 8000 34000 45000

Table 8: Historical Turnover

Total historical turnover = £92,250

DATE Product 2 Product 3 Product 4 Product X Product 5 Product 6
01/08/2013 4750 1500 8000 12750 21250 52500

Table 9: Original Forecast Turnover

Original forecast turnover = £100,750

DATE Product 2 Product 3 Product 4 Product X Product 5 Product 6
01/08/2013 4500 1500 8000 12750 21250 52500

Table 10: Forecast Turnover with lost of 20% of Product 1 buyers

Forecast Turnover with 20% loss of Product 1 buyers = £100,500

DATE Product 2 Product 3 Product 4 Product X Product 5 Product 6
01/08/2013 4500 1500 8000 14100 20000 50050

Table 11: Forecast with Products X, 5 and 6 adjusted

Forecast Turnover with Products X, 5 and 6 adjusted = £98,150

Overall, there is still an increase of 6.4% from the historical turnover.

Conclusion

A pricing forecast solution was developed that is flexible for adjustments of ratio splits and pricing changes. Adjustments could be made to the product mapping table or the price to see what the result will be, and to find the optimum pricing for products.

Note:

Analysts should take into account actual business limitations when using the figures like external competition. Volume increases applied should be realistic.

8 thoughts on “Building a simple forecast solution on SQL Server

  1. Generally I do not learn article on blogs, however I wish to say that this write-up very compelled me to take a look at and do so! Your writing taste has been surprised me. Thanks, quite great article.

  2. What’s up,I read your blog named “Building a simple forecast solution on SQL Server | Terry’s Business Intelligence” daily.Your writing style is awesome, keep it up! And you can look our website about اغانى شعبى 2017.

  3. Hello admin, i must say you have very interesting content here.
    Your website should go viral. You need initial traffic only.
    How to get it? Search for: Mertiso’s tips go viral

  4. Hi there,I read your blog named “Building a simple forecast solution on SQL Server | Terry’s Business Intelligence” like every week.Your story-telling style is witty, keep up the good work! And you can look our website about love spell.

Leave a Reply to اغانى شعبى 2017 Cancel reply

Your email address will not be published.