Predicting the Sales Growth – Πρόβλεψη Ανάπτυξης Πωλήσεων
So in this example, we have existing sales data of a company for the year 2017 that increases linearly from Jan 2017 to Dec 2017.
We need to figure out the sales for 2018.
The existing data contains the dates in column D and the sales revenue in column F. We need to calculate the estimated sales value for the next 12 months.
Historical Data is given below:
In order to predict the sales for the given upcoming months in the next year, we will use the TREND function in excel since the sales value is increasing linearly, the given known values of Y is the sales revenue, and the known values of X are the end dates of the month, the new values of X are the dates for next 12 months that is
31/Jan/2018, 28/Feb/2018, 31/March/2018, 30/April/2018, 31/May/2018, 30/June/2018, 31/July/2018,
31/Aug/2018, 30/Sep/2018, 31/Oct/2018, 30/Nov/2018, 31/Dec/2018 and we need to compute the estimated sales values based on historical data given in range D4:F15.
The TREND formula in excel will take the existing values of known X and Y, and we will pass the new values of X to calculate the values of Y in cell H4, H5, H6, H7, H8, H9, H10, H11, H12, H13, H14 and H15.
The TREND formula in excel will be:
H4=TREND(F4:F15,D4:D15;G4) for Jan 2018
H5=TREND(F4:F15,D4:D15;G5) for Feb 2018
H6=TREND(F4:F15,D4:D15;G6) for March 2018
H7=TREND(F4:F15,D4:D15;G7) for April 2018
H8=TREND(F4:F15,D4:D15;G8) for May 2018
H9=TREND(F4:F15,D4:D15;G9) for June 2018
etc
We fixed the range for known values of X and Y and passed the new value of X as a reference value. Applying the same TREND formula in excel to other cells we have,
Output:
So, using the TREND Function above, we have predicted the estimated sales values for the given upcoming months in cell H4, H5, H6, H7, H8, H9, H10, H11, H12, H13, H14 and H15.
Views: 2
Comments are closed.