+ Reply to Thread
Results 1 to 4 of 4

Forecasting from historical data with Solver or other technique

  1. #1
    Registered User
    Join Date
    12-09-2013
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2010
    Posts
    11

    Forecasting from historical data with Solver or other technique

    I believe the thread title makes the issue seems a lot easier than it actually is. I am trying to forecast how many applications our office with receive by the end of the application period by comparing how many we have today to the average of the last four years (generally speaking). Last year this worked fine because there was not much variance in the date (couple days) at which the application period started in the first three years. This year the application period opened nearly an entire month earlier. Also the priority application period has ended on November 1st for all prior years and will as well this year. After that date the numbers are fine, however because the forecasting needs to consider the entire application period to predict how many applications we will have received in entirety when the year is said and done creates an issue. I've tried normalizing the data based upon the percentage of days the application has been open relative to the entire period, but that did not work. I feel as though there may be some way in which solver can help me with this issue without having to analyze trendline equations for previous years? I'm sure many of you have encountered this issue and your direction would be much appreciated. I want to be sure that I forecast with utmost accuracy and do not want to rush and roughly draw conclusions about how previous years differed. Certainly it is also difficult because no one can actually know whether starting the application a month earlier actually results in individuals being more proactive about applying, but one must consider it when determining if the organization is on track to reaching the goal (which the forecast informs if will be achieved). The earlier applications are not simply a "bonus."

    Thanks in advance for your help. Here are the application period dates for each year to help give you more insight (Fall 2015 being the current application period):
    times.PNG

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Forecasting from historical data with Solver or other technique

    Could you upload a sample workbook instead of a picture? Also, aren't you interested in the number of applications you obtained? It is unclear in the picture how many were received (if it even shows any).

  3. #3
    Registered User
    Join Date
    12-09-2013
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Forecasting from historical data with Solver or other technique

    Here is a quick file I through together to resort to trying to figure it out mathematically. It shows the the percentage of total applications received on a day-to-day basis for each of the past four years. Lets presume that currently there at 1,000 applications for this application period which began on August 10th.
    Historic Application Periods.xlsx

  4. #4
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Forecasting from historical data with Solver or other technique

    I think this is more of a statistics question more than an Excel question. Taking a quick look at your data, you can definitely see days where there is an influx of received applications. Creating a bell curve that mimics the data, then extending the population size may help give you a sense of things; however, because you said this needs to be a precise analysis, this may not be the most effective route since errors pile up when making assumptions.

+ 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. goal seeker or solver for forecasting vs. actuals
    By amartino44 in forum Excel General
    Replies: 3
    Last Post: 02-12-2013, 04:09 PM
  2. Replies: 19
    Last Post: 09-24-2012, 07:16 PM
  3. Replies: 9
    Last Post: 01-16-2010, 08:41 PM
  4. Replies: 1
    Last Post: 07-21-2009, 09:09 PM
  5. Excel Solver background in forecasting modeling
    By RL in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2005, 06:06 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