Interfacing with Excel

This forum contains all archives from the SD Mailing list (go to http://www.systemdynamics.org/forum/ for more information). This is here as a read-only resource, please post any SD related questions to the SD Discussion forum.
Locked
system-dynamics@VENSIM.COM
Junior Member
Posts: 3
Joined: Fri Mar 29, 2002 3:39 am

Interfacing with Excel

Post by system-dynamics@VENSIM.COM »

Decisions
Date: Sat Aug 27 06:41:13 2005
From: Francisco Perez francisco77pp yahoo.com <system-dynamics@vensim.com>
To: sdmail@listserv.albany.edu

Posted by Francisco Perez <francisco77pp@yahoo.com>

I wonder whether any of you has used a System DYnamics Model with Excel for optimizing decsion at specific intervals. For example. If the model is to simulate behavior of a system and whe make a pause in order to introduce a decision, which may be done supported in Excel as an optimization problems taking as inputs the outputs of the SD model for that time and returning as an answer the decision levers to be introduced back to the SD model. This might be useful for simulating the effect of different decision approaches regarding planning and resource allocation or even multi-objective/criteria optimization in SD.

All comments are welcome. If anyone has any paper or model involving such a topic do not hesitate to share it with us.

Regards
Francisco
Posted by Francisco Perez <francisco77pp@yahoo.com>
posting date Thu, 25 Aug 2005 13:46:38 -0700 (PDT)
Keith Linard klin4960 bigpond.ne
Junior Member
Posts: 7
Joined: Fri Mar 29, 2002 3:39 am

Interfacing with Excel

Post by Keith Linard klin4960 bigpond.ne »

Posted by ""Keith Linard"" <klin4960@bigpond.net.au>
Mr Perez asked re using optimising for specific decisions at specific intervals. The paper 'Optimising Workforce Structure the System Dynamics of Employment Planning', proceedings 1999 ISD conference discuss the integration of Powersim Constructor with Excel & the Excel genetic algorithm optimiser, Evolver'. Subsequently, Powersim fully integrated genetic algoritm optimisation & Monte Carlo simulation with Powersim Studio, and the concept discussed by My Perez is now very simple --- compared with the rest of the modelling task!

Keith Linard
134 Gisborne Road
Bacchus Marsh
Vic 3340

Mobile: 0412-376-317
Phone: 03-5367-3456
Email: klin4960@bigpond.net.au
Posted by ""Keith Linard"" <klin4960@bigpond.net.au>
posting date Sat, 27 Aug 2005 22:43:51 +1000
Colm Toolan subscriptions toolan
Junior Member
Posts: 9
Joined: Fri Mar 29, 2002 3:39 am

Interfacing with Excel

Post by Colm Toolan subscriptions toolan »

Posted by ""Colm Toolan"" <subscriptions@toolan.de>

Mohammad Mojtahedzadeh at the Attune Group, has built a very interesting tool - ""Exposé"" - as an Excel add-in which should be of interest in this context.

The evaluation version (V1.1) is available at www.attunegroup.com. Version 2.0, which provides much fuller SD integration is due for release at the end of the month.

Basically, Exposé allows mapping of the relationships within a spreadsheet and can recognise and map time series, feedback loops, stocks etc. It should be ideally for any SD modeller who has to integrate Excel type models into their work.

[Tip for readers who use German language Windows - change you language settings in XP to Swiss German or better to English, to avoid untold grief with the demo versions of Exposé. The ""decimal comma"" in German/German causes difficulties for **all** international Excel add-ins!]

Regards,
Colm Toolan, Business Architect
Posted by ""Colm Toolan"" <subscriptions@toolan.de>
posting date Sat, 27 Aug 2005 16:10:09 +0200
Francisco Perez francisco77pp ya
Junior Member
Posts: 18
Joined: Fri Mar 29, 2002 3:39 am

Interfacing with Excel

Post by Francisco Perez francisco77pp ya »

Posted by Francisco Perez <francisco77pp@yahoo.com>
Thanks for your response. Let me try to clarify a bit more my query. I know that optimization is now embedded in Powersim and Vensim, but the main purpose fur such engines is for model optimization (be it policy otimization, or the like) and calibration via model parameter optimization/adjustment. To what I am refering now is the capability of running from simple to complex algorithms as decision within the model. Suppose you are given a model describing the market behavior for several products. You are the marketing manager. according to the development of your market-enterprise system you will have to choose among certain decision variables in order to ""optimized"" your portfolio, at every ""certain"" interval of decision. This is the problem. It can be perfectly extended to business planning, distirbution planning, sales planning, production planning, etc.

I have searched through the SD papers from 1996 to 2005 and have found no practical answer. I think there's room for research and development on that area.

with Regards

Francisco Perez
OptimesGroup
Caracas, Venezuela
Posted by Francisco Perez <francisco77pp@yahoo.com>
posting date Sun, 28 Aug 2005 13:36:18 -0700 (PDT)
Ricardo C. Galan rgalan iditarod
Newbie
Posts: 1
Joined: Fri Mar 29, 2002 3:39 am

Interfacing with Excel

Post by Ricardo C. Galan rgalan iditarod »

Posted by ""Ricardo C. Galan"" <rgalan@iditarodsys.com>
Francisco,

The way I have handled this in the past is to create a decision variable
that has a time interval array. This will allow you to record the changes
in the decision over the period of the simulation.

Ricardo Galán
Iditarod Systems
1600 International Drive, Suite 100
McLean, VA 22102
Posted by ""Ricardo C. Galan"" <rgalan@iditarodsys.com>
posting date Mon, 29 Aug 2005 09:57:35 -0400
Bob Eberlein bob vensim.com
Junior Member
Posts: 3
Joined: Fri Mar 29, 2002 3:39 am

Interfacing with Excel

Post by Bob Eberlein bob vensim.com »

Posted by Bob Eberlein <bob@vensim.com>
Hi Francisco,

Thanks for the clarification. This is actually a topic that has come up
before under different headings.

There is absolutely nothing in the overall methodology of system
dynamcis that precludes the inclusion of complicated decision functions
within a model so long as these respect causality and and make use of
information that is available to the decisionmakers being represented.
In most modeling we tend not to make use of these for two reasons. One
is that a great deal of human decision making is based on heuristics
that are pretty easy to represent with simple formulas. The second is
that adding complexity to decision making rules also makes the model
more difficult to understand and may not really help in solving the
problem the model is intended to address.

That said, it is pretty common to include optimization, market
clearing mechanisms and related concepts in models. Sometimes this is
done using specialized built in functions which tend to be different
between different software packages, and sometimes it is done by writing
externally callable routines to do the computation. Connecting to Excel
would be an example of the latter, though things like linear programming
and network optimization tend to be more commonly used. You may be
correct that people do not write very much about this. Most the people I
know of who are making use of this type of thing do not tend to publish
anything about it. Still, I would be surprised if there is not quite a
bit written if you look in the right places. Perhaps Game Theory and
System Dynamics would be a good place to search.

As far as actually executing the connections - that is something that
depends on the software you are using. A help forum for that software is
probably the most sensible place to look for that type of detail.

Bob Eberlein
Posted by Bob Eberlein <bob@vensim.com>
posting date Mon, 29 Aug 2005 08:44:41 -0400
Jean-Jacques Laublé jean-jacques
Senior Member
Posts: 68
Joined: Fri Mar 29, 2002 3:39 am

Interfacing with Excel

Post by Jean-Jacques Laublé jean-jacques »

Posted by =?iso-8859-1?Q?Jean-Jacques_Laubl=E9?= <jean-jacques.lauble@wanadoo.fr>
Hi Francisco.



I have more or less the same problem.

It is like dynamic programming in O.R.

One has to take decision every time step.

This decision depends on the past events and on future events that are
stochastic.

So you have to choose a policy that takes into account the future events
plus the future policies that may be taken.



I use Vensim, and the solution is not really embedded in the software, but I
use expedients to solve it, that run much more quickly than with Excel using
a solver, which is for me absolutely impracticable, as I simulate about 100
parallel simulations to simulate the stochastic future events and together
with optimisation and numerous parameters would take days to perform.



I take a simple example but it works for any other one.

Suppose that you have the periods P1..P4 4 periods, S1..S4 the policies to
define for each period, and the constraints for every period represented by
Vi <= 0, i = 1,4 where vi is a variable of the model, auxiliary or level.
The objective is to maximize for every period an objective function Oi i =
1,4 Oi = Oi(Si)

Maximizing the function Oi is equivalent to maximizing the sum of (Oi(si))
on the 4 periods.

To represent the constraints every step of the simulation I define a
function ci(vi) = (if then else (vi <= 0,0,-1000000)).


So you have converted your optimization step by step into en overall
optimization solvable by Vensim. the only thing to do is to optimize the
function F = sum (Oi(si)) + sum (ci(vi)).


What are the optimization parameters? All the Si policies together.


It works well when you have not too much periods, not too much parameters
for the si, and non stochastic models. Otherwise, the solution to minimize
the number of parameters to optimize is to define optimization rules as
parameters, that will generate the policies for each period automatically.
This may reduce considerably the number of parameters to optimize.

For instance with the first method if you have 10 parameters to optimize
each period, this results for a 100 period model in 1000 parameters to
optimize. Generally optimizing with rules gives less accurate result then
optimizing directly the parameters, but is becomes unavoidable with middle
to big models.

Of course finding the right rules and the parameters of the rules is not
evident

In the first solution of direct optimization of the parameters, you can end
up with a no loop model, while the rules solution have loops.

It may too be necessary to use external functions written in a procedural
language, where complex algorithms are much more easy to write then in
Vensim or Powersim. In Excel you will have too to use VBA.

Regards.

J.J. Laublé Allocar

Strasbourg France.
Posted by =?iso-8859-1?Q?Jean-Jacques_Laubl=E9?= <jean-jacques.lauble@wanadoo.fr>
posting date Mon, 29 Aug 2005 14:54:27 +0200
Jim Duggan jim.duggan nuigalway.
Junior Member
Posts: 6
Joined: Fri Mar 29, 2002 3:39 am

Interfacing with Excel

Post by Jim Duggan jim.duggan nuigalway. »

Posted by Jim Duggan <jim.duggan@nuigalway.ie>
Hi,

The following paper is definitely worth a look (this
years conference) as it explores ways of combining
decision analysis and system dynamics.

Osgood, Nathaniel Combining System Dynamics and Decision Analysis for
Rapid Strategy Selection

http://www.systemdynamics.org/conf2005/indexpapers.htm

Also, I think it's useful to make a distinction between
(1) SD models that call complex decision logic and (2)
complex decision logic that invoke SD models. In the
first case, the decision logic uses information cues from the model to generate a value of decision variable, in the second case (e.g. optimisation), the SD model is called repeatedly by an algorithm in order to minimise or maximise a payoff function.

It sounds like your task falls under the first
category.

Also, if you want to invoke a compex decision on
an infrequent basis (from Vensim, Stella, Powersim, etc.,) the MODULO inbuilt function is very useful (using the current simulation time and the decision time interval), and of course the decision can be encapsulated in some form of compatible external function (using a programming language).

best regards,
Jim.
Posted by Jim Duggan <jim.duggan@nuigalway.ie>
posting date Wed, 31 Aug 2005 10:13:41 +0100
Locked