+ Reply to Thread
Results 1 to 3 of 3

Issue with Excel Solver for optimizing smoothning constant in winthers forecasting model

  1. #1
    Registered User
    Join Date
    04-23-2020
    Location
    Odense, Denmark
    MS-Off Ver
    365
    Posts
    2

    Issue with Excel Solver for optimizing smoothning constant in winthers forecasting model

    I have the assignment of creating a winthers model for forecasting.

    I have to optimize the smoothening constants using the solver, but when i sol using the non-linear function nothing happens.

    If there is anyone able to help me locate the issue i would be very grateful.

    The task can be found in the tab "Winthers" and the solver parameters should be set up correctly, but is imply cannot figure out why it does not work


    BR

    OKB1996
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Issue with Excel Solver for optimizing smoothning constant in winthers forecasting mod

    tl:dr version -- as written, your objective function (W23) is a step function (a type of nonsmooth function). Small changes in alpha, beta, or gamma results in no change in W23, so Solver thinks it has found the solution.

    From Frontline's Solver tutorial, a page about the difficulties of solving nonsmooth problems: https://www.solver.com/nonsmooth-optimization

    I don't understand exactly what you are doing here, so I cannot offer anything concrete. A couple of considerations:
    As mentioned in the link, perhaps switch to an evolutionary algorithm.
    I notice that there are a couple of rounding functions in the computation trail (columns Q, R, and S). Rounding functions are an easy to recognize source of step functions. Are these ROUNDUP() functions absolutely necessary, or could you solve the model without them (then round after finding the solution)?

    Basically, you need to figure out how to deal with the step function. Since I don't understand everything that is going on here, I cannot make any concrete suggestions, but perhaps those ideas will allow you to explain what is going on and/or try something.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    04-23-2020
    Location
    Odense, Denmark
    MS-Off Ver
    365
    Posts
    2

    Re: Issue with Excel Solver for optimizing smoothning constant in winthers forecasting mod

    Hi Shorty

    The rounds were messing things up.

    Many thanks, I donīt know if it is fully correct, but now my solver is at least doing something

    I will compare with my peers to hopefully get the right result. This was my first post in this forum, and i am very glad you would help me!

    BR

    OKB

+ 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. Replies: 6
    Last Post: 12-20-2017, 02:59 PM
  2. Possible to use solver to break excel model?
    By kevindorward in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-30-2015, 08:14 AM
  3. Replies: 0
    Last Post: 07-20-2014, 12:45 PM
  4. Solver in Excel Model
    By Jardman123 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-26-2013, 12:10 PM
  5. Automatic use of Solver in an Excel model
    By Jasper in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2006, 08:35 AM
  6. how do i model discounts in excel solver
    By Samir Syed in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-23-2005, 01:30 PM
  7. Excel Solver background in forecasting modeling
    By RL in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2005, 06:06 PM

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