+ Reply to Thread
Results 1 to 11 of 11

Creating dynamic forecast values, each month when new data is added

  1. #1
    Forum Contributor
    Join Date
    05-09-2019
    Location
    South West
    MS-Off Ver
    365
    Posts
    113

    Creating dynamic forecast values, each month when new data is added

    Hello,

    I'm wondering if anyone can help. I have a simple data file (which is an example of my actual data I work with), whereby I have dates, actual sales, and forecast sales.

    I have historic, actual, monthly sales data between Jan 2022- May 2024 (column B), and I have calculated in column C of the attached workbook, the forecast values for June 2024 - Dec 2024.

    However, the formulas in column C reference the fixed range of the actual data I have (e.g. the forecast figure for December still only looks at Jan 2022- May 2024), however, the actual data will update each month.

    What I would like to be able to do is each month, reference the latest actual data I have in my forecasts, without having to manually update the formulas (i.e. drag the ranges down one cell for the date and sales ranges in column A + B) - but I can't figure out the best way to go about this.

    Does anyone have any advice or pointers on this? I feel like I may be overlooking something very obvious with this.

    Thanks,
    Attached Files Attached Files

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,660

    Re: Creating dynamic forecast values, each month when new data is added

    Try this in C37:

    =IF(B37>0,"",FORECAST.LINEAR($A37,IF($B$2:B32>0,$B$2:B32),$A$2:A32))

  3. #3
    Forum Contributor
    Join Date
    05-09-2019
    Location
    South West
    MS-Off Ver
    365
    Posts
    113

    Re: Creating dynamic forecast values, each month when new data is added

    Hi Zbor,

    thank you for your reply! Sorry, I should have said, I would be looking to replace the formulas that start in cell C31, with a formula that I can drag down (in this case up to cell C37, but the spreadsheet would continue to grow with time).

    The values that I have in C31:C37, are all using the current known values (i.e. actual sales up to May 2024) - which is fine for the forecast value of June 2024.

    However, next month when I get to cell C32 (i.e. July 2024), by next month I will have had the actual sales value for June added to the sheet, and so the forecast value for July, will then be able to include the actual June sales value -- but the issue I have is that currently, I would have to manually update the formula in C32 to include the June value, and this would be a process I'd have to remember to do each month.

    So I guess in effect, I'm looking to replace the formulas in column C that will forecast future sales, only using actual sales values - and not including where there are any 0's or blanks in column B.

  4. #4
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Australia
    MS-Off Ver
    365 V2403 and WPS V2022
    Posts
    3,629

    Re: Creating dynamic forecast values, each month when new data is added

    Cell C31 formula , Drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    3,240

    Re: Creating dynamic forecast values, each month when new data is added

    Add an IF statement in front of your formula in C31 and copy down:
    =IF(B31>0,"",FORECAST.LINEAR($A31,$B$2:B30,$A$2:A30))

  6. #6
    Forum Contributor
    Join Date
    05-09-2019
    Location
    South West
    MS-Off Ver
    365
    Posts
    113

    Re: Creating dynamic forecast values, each month when new data is added

    wk9128 - thanks for your reply. However, I can see that you formula locks the range with the current actual sales, which is the same I think as what I had, and means when the formula is dragged down, it wouldn't then reference actual sales for future months when they are added?

    josephteh - thanks for your reply as well. I tried this, but I think it is still referencing / including the sales which are 0 / blank, whereas, I'm trying to only look at the values where there is an actual sales value. I tried to think if there was a way to edit your formula to only look at the values in column B where a value is not ) or blank, but wasn't sure how to adjust this.

  7. #7
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    3,240

    Re: Creating dynamic forecast values, each month when new data is added

    Try this in C2, =IF(B2>0,"",FORECAST.LINEAR($A2,B$1:INDEX(B:B,COUNTIF($B$1:$B1,">0")+1),A$1:INDEX(A:A,COUNTIF($B$1:$B1,">0")+1))), copy down.

  8. #8
    Forum Contributor
    Join Date
    05-09-2019
    Location
    South West
    MS-Off Ver
    365
    Posts
    113

    Re: Creating dynamic forecast values, each month when new data is added

    josephteh - this worked !!!! This is exactly what I needed, thank you so much !!

    I'm trying to figure out how exactly it works. If you wouldn't mind, would you mind explaining what the INDEX part is doing, although with the "+1" element?

  9. #9
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    3,240

    Re: Creating dynamic forecast values, each month when new data is added

    This part of formula "=B$1:INDEX(B:B,COUNTIF($B$1:$B30,">0")+1)" in cell C31 will evaluate (using Formulas > Evaluate Formula) to "=$B1:$B30".

  10. #10
    Forum Contributor
    Join Date
    05-09-2019
    Location
    South West
    MS-Off Ver
    365
    Posts
    113

    Re: Creating dynamic forecast values, each month when new data is added

    That's great, thanks josephteh

  11. #11
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    3,240

    Re: Creating dynamic forecast values, each month when new data is added

    You are welcome, thanks for the Rep and for marking the thread "Solved"!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Creating a forecast based on sales data
    By goldeneagle24 in forum Excel General
    Replies: 3
    Last Post: 03-10-2023, 06:25 PM
  2. Replies: 2
    Last Post: 01-13-2023, 05:25 AM
  3. how to apply a formula for a rolling forecast that move month over month
    By salimus16 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-08-2023, 02:45 PM
  4. [SOLVED] Creating a moving chart when Data is added.
    By ROWENA1983 in forum Excel Charting & Pivots
    Replies: 14
    Last Post: 01-08-2019, 05:30 AM
  5. [SOLVED] Multiple values need to be sorted by month and then added in a seperate column
    By Milwaukeespurs in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-07-2016, 11:30 AM
  6. [SOLVED] Vlookup when a new data column is added each month
    By bkeat in forum Excel General
    Replies: 5
    Last Post: 08-20-2014, 04:43 AM
  7. Challenging Forecast Wape - Rolling 12 Month Sum Of Orders And Forecast
    By nguyeda in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-17-2013, 06:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1