Help over Forecast formula

[bmarroush]

Dear

I am trying to apply forecast formula on income statement and cash flow. I have known amounts for 2 years 2016 and 2017 and for 2018 and 2019 i entered zero values.

I need to forecast the amount of sales for the 5th year 2020 up to 2023.

In 2016 and 2017 there are positive sales value but when applying the forecast formula the 2020 up to 2023 cells a value is turning negative

Here is the formula i am using

= Forecast(5,G12:J12,G10:J10) the 5= Period i want to forecast for, G12:J12 range of actual results, G:10:J10 range of periods from 1 to 4 and the 6th year forecast will include 1 to 5 and so on

Could anyone help over this

 

[bmarroush]

Please if can anyone help

 

[joeu2004]

@bmarroush.... Several comments.

First, we cannot help you until you tell us the values in all referenced cells.

In particular, if G10:J10 contain year numbers 2016, 2017, 2018 and 2019, then your formula would be =FORECAST(2020, G12:J12, G10:J10).

On the other hand, if G10:J10 contain period numbers 1, 2, 3 and 4, then your original formula is okay as written, to wit: =FORECAST(5, G12:J12, G10:J10). The zero data is the reason why the trendline has a negative slope.

-----

Second, forecasting is more art than science. The FORECAST function assumes linear change -- a straight line. That might not be a good fit for your data. So the first step in forecasting usually requires that you graph (chart) the known data and see what trendline is the best fit.

(And it is possible that none of the standard Excel trendline work for you. In that case, you might need to get creative.)

But you might be misleading the forecast by entering zero for 2018 and 2019. Did you really have no sales in those years? Or are the zeros just "placeholders" because the amounts are unknown?

If the latter, it might be better to forecast using only the known data for 2016 and 2017. Unfortunately, that is not much to go by; the forecast will probably be very poor. But it will be better than including zeros arbitrarily.

-----

Finally, you write: "the 6th year forecast will include 1 to 5 and so on".

I'm not sure what you mean.

If you are trying to "bootstrap" the forecast, incorporated forecasted periods should not change the straight-line estimate. In other words, the estimated straight-line based on periods 1 to 4 should be the same as the estimated straight-line based on periods 1 to 5, where 5 was derived from the straight-line based on 1 to 4.

In any case, the syntax for such "bootstrapping" is =FORECAST(K10, $G$12:J12, $G$10:J10) in K12, where K10 is the period or year number (same form as G10:J10).

Note the careful use of absolute references ($G$12 and $G$10) and relative references (K10, J12 and J10). As you copy the formula across into column L, M etc, K10 with change to L10 etc, and J12 and J10 will change to K12 and K10 etc.