+ Reply to Thread
Results 1 to 4 of 4

Building 'advanced' forecast model

  1. #1
    Registered User
    Join Date
    10-07-2019
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    3

    Lightbulb Building 'advanced' forecast model

    Please provide assistance in the following example (attached):

    DATA is collected from a historical database.
    EXAMPLES (A,B,C) are particular sources that are most comparable to a new product.
    TEST is an input/output table which supposedly takes existing EXAMPLES and DATA and forecasts for a given future year the result.

    TITLE is a manually selected data validation cell referring to A, B, C.
    YEAR is automatically recalled based on the information in TITLE.
    COLOR is a manually selected category of TITLE which has a specific VALUE.
    VALUE cell is automatically recalled based on the information in COLOR.
    FORECAST is a manually selected future year.
    MULTIPLIER is automatically returned as excel's forecasted calculation divided by the original YEAR.
    ULTIMATE is automatically returned as the MULTIPLIER multiplied by the original VALUE.

    In order to achieve an efficient model which can handle changing examples, which formulae should be used for the TEST tables?
    Please bear in mind that if the database has a zero under our selection, we would require the first and nearest preceding or subsequent non-zero number (e.g. ORANGE 2006).

    Thank you!
    Attached Files Attached Files
    Last edited by FDTFQV65; 10-08-2019 at 05:26 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Building 'advanced' forecast model

    Hi, welcome to the forum

    I dont know what that link is, but I doubt that many members will go there. Rather, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-07-2019
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Building 'advanced' forecast model

    Uploaded file.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Building 'advanced' forecast model

    This may move the project forward a bit:
    To populate H14 with a drop down:
    1. A helper row (which may be moved and/or hidden for aesthetic purposes) is populated using: =IFERROR(AGGREGATE(15,6,$B2:$L2/($B2:$L2>$E14),COLUMNS($A1:A1)),"")
    2. A named range (Forecast) references the helper row using the 'refers to' of: =OFFSET(Sheet1!$T$2,0,0,1,SUMPRODUCT(--(Sheet1!$T$2:$AD$2<>"")))
    3. The data validation for that cell has the source of: =Forecast
    I am confused as to how the MULTIPLIER cell (I14) should be populated. Perhaps telling the expected MULTIPLIER based on the values in D14:H14 of the attached file will help. Please also include an explanation of how it is derived, as in I6 is divided by G14.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Forecast Model for Construction Projects
    By dee38 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-27-2016, 09:29 AM
  2. Help building a regression model
    By Wazo11 in forum Excel General
    Replies: 1
    Last Post: 09-03-2015, 12:08 PM
  3. Help building a regression model
    By Wazo11 in forum Excel General
    Replies: 0
    Last Post: 09-03-2015, 10:28 AM
  4. building data model
    By Tuanfeng in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-29-2014, 03:04 AM
  5. building a revenue forecasting model and need calendar building help
    By miken33 in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 03-11-2013, 06:53 PM
  6. Building a Pricing Model
    By AJAG in forum Excel General
    Replies: 4
    Last Post: 02-12-2013, 01:13 PM
  7. Help with Forecast demand model
    By jame24 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-15-2011, 01:33 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