Demand Planning: Sample Calculation for Item Demand/Supply using Linear Regression

Demand Planning feature must be turned on under Setup>Company>Enable Features>Items & Inventory>Demand Planning.

 

Linear Regression formula uses the equation "y=a+bX".

This equation will be used when projecting future Item Demand/Supply in NetSuite.

Given:

(Cash Sale Transactions Imported)

Date..........Number...............Item.........Quantity......Amount

1/15/2012....CS-001..............Item A..........10..........100.00

2/1/2012......CS-002..............Item A..........15..........150.00

3/1/2012......CS-003.............Item A..........20...........200.00

3/15/2012....CS-004..............Item A..........25..........250.00

4/20/2012....CS-005..............Item A..........10..........100.00

 

Navigate to Setup>Accounting>Inventory Management Preferences>Transactions to Consider=Actual Sales.

 

Plot the data above in a table for easier computation. The table will depend on what you set as your Historical Analysis Duration and the current date when you go to Transactions>Demand Planning>Calculate Item Demand Plans. In this example, it is set to a period of 6 months and the demand is being calculated as of May 2012. 

From May 2012, I will count 6 periods back. The first period in the computation will be November 2011.

We then let X=Period and let Y=Quantity.

Take the sum of the Quantities sold per month. In March, there were two transactions so we get a total of 45 Quantity sold.

Since during November and December 2011, no quantities have been sold, Y (Quantity) should be set as zero (0).

	Period	Quantity
Month	X	Y	XY	X2	Y2
November	1	0	0	1	0
December	2	0	0	4	0
January	3	10	30	9	100
February	4	15	60	16	225
March	5	45	225	25	2025
April	6	10	60	36	100
Total	21	80	375	91	2450

^{XY= X*Y}

 

Once you have plotted the data in a table, get the values for XY, X², Y² as this will be used in our formula below:

            (∑Y)(∑X² ) – (∑X)(∑XY)                 (80)(91) – (21)(375)                -595

   a = --------------------------------  = ---------------------------- = --------------- = -5.66667

                   n(∑X² ) - (∑X) ²                              6(91) – (21)²                           105

                  n (∑XY) – (∑X)(∑Y)                          6(375) – (21)(80)               570

   b = -------------------------------- = ----------------------------- = --------------- = 5.428571

                   n(∑X² ) - (∑X) ²                                6(91) – (21)²                                     105

To project demand for three periods, NetSuite calculates it using the equation, y=a+bX.

Projected Values computed by the system:
Month	Period (X)	Quantity (Y)	Rounded value
May	7	32.33	32
June	8	37.76	38
July	9	43.19	43
August	10	48.62	49
September	11	54.05	54

 

Example: y=-5.66667+(5.428571)(7)=32.33