2017 Revenue Forecast Trend Formula

Dear Experts,

Please find here attached workbook, Where we do have actuals for 03 Years (2014, 2015, & 2016) and i am looking for the formula so that i can forecast in 2017 Forecast Trend Tab based on last 03 Years Trend. So would like to request to you kindly do provide the formula for trend and forecast.

Thank you for you precious phase and valuable contribution.

Regards,

Neilesh

So are you trying to do linear regression trend?

If so, plot data in flat table with 3 columns {"Series#", "Hosting", License"}
Where series# = 1 ~ 36 corresponding to each month of 3 year period.

Plot as x-y scatter chart.

Add linear trend line and display formula on chart.

You should get something like.
y = 2074.7x - 7975.2 for License
y = 418.21x + 1263 for Hosting

See attached with series extended to 48.

After this, you may want to consider seasonal trend/pattern variance as well.

Originally Posted by CK76

So are you trying to do linear regression trend?

If so, plot data in flat table with 3 columns {"Series#", "Hosting", License"}
Where series# = 1 ~ 36 corresponding to each month of 3 year period.

Plot as x-y scatter chart.

Add linear trend line and display formula on chart.

You should get something like.
y = 2074.7x - 7975.2 for License
y = 418.21x + 1263 for Hosting

See attached with series extended to 48.

After this, you may want to consider seasonal trend/pattern variance as well.

Dear Experts,

Thank you so much for your precious phase, But is it possible to forecast the numbers with the help of provided formula or trend in 2017 Forecast Trend Format, if it is so, Can you please put the formula in the format and do provide to me.

Thank you once again.

Regards,

Neilesh

Originally Posted by CK76

So are you trying to do linear regression trend?

If so, plot data in flat table with 3 columns {"Series#", "Hosting", License"}
Where series# = 1 ~ 36 corresponding to each month of 3 year period.

Plot as x-y scatter chart.

Add linear trend line and display formula on chart.

You should get something like.
y = 2074.7x - 7975.2 for License
y = 418.21x + 1263 for Hosting

See attached with series extended to 48.

After this, you may want to consider seasonal trend/pattern variance as well.

Dear Expert,

Can you please provide the formula for getting y = 2074.7x - 7975.2 for License & y = 418.21x + 1263 for Hosting so that i can apply the same for rest of the fields as well.

Thank you for your precious phase and valuable contribution.

Regards,

Neilesh

So using above formula.

Month 37 (i.e. Jan 2017)
Hosting = 418.21*37+1263
License = 2074.7*37 - 7975.2

Therefore your formula in 2017 Forecast trend...
In C6: =418.21*(36+C3)+1263
In C7: =2074.7*(36+C3)-7975.2

Copied across.

FYI - For any sort of data analysis. I'd recommend starting from single flat table. Rather than data split into multiple sheets in cross-tab format.

Can you please provide the formula for getting y = 2074.7x - 7975.2...

With your current set up you can't arrive at this formula. See what I did in Sheet1 of attached in my original post.

Alternate to what I did using x-y scatter chart, you can use LINEST array to get slope & intercept.

See attached. M37:N41 contains the array formula.

Originally Posted by CK76

With your current set up you can't arrive at this formula. See what I did in Sheet1 of attached in my original post.

Alternate to what I did using x-y scatter chart, you can use LINEST array to get slope & intercept.

See attached. M37:N41 contains the array formula.

Dear Expert,

Thank you so much for your precious phase and positive support. I would like to know if i do paste the data for the rest Categories from Column K to P, So, the last value will be subtracted or added. Please do let me know.

Thank you once again for your valuable support.

Regards,

Neilesh

Not sure I get you.

If you mean add or subtract intercept... Always add the intercept.
It will be either + or - value (ex. for License in your example, intercept is -7974.8...)

For your purpose, you don't need anything other than slope and intercept (r-squared may be, if you are doing variance plot).

Originally Posted by CK76

Not sure I get you.

If you mean add or subtract intercept... Always add the intercept.
It will be either + or - value (ex. for License in your example, intercept is -7974.8...)

For your purpose, you don't need anything other than slope and intercept (r-squared may be, if you are doing variance plot).

Dear Expert,

I meant, In the provided worksheet {2017 Revenue Forecast Trend_Linest} if i do paste in Sheet 1 from Column K to Column P some more categories for three years, So do i need to use Subtraction or Addition. Please do let me know.

Regards,

Neilesh

Originally Posted by CK76

Not sure I get you.

If you mean add or subtract intercept... Always add the intercept.
It will be either + or - value (ex. for License in your example, intercept is -7974.8...)

For your purpose, you don't need anything other than slope and intercept (r-squared may be, if you are doing variance plot).

Dear Expert,

I meant, In the provided worksheet {2017 Revenue Forecast Trend_Linest} if i do paste in Sheet 1 from Column K to Column P some more categories for three years, So do i need to use Subtraction or Addition. Please do let me know.

Regards,

Neilesh

Without knowing exactly what goes into those columns. I can't answer you.

If those are going to be separate series with it's own trend line.... then my previous reply applies. LINEST will tell you to subtract or add.