asked on

Excel solver

Hello,
I have a table in excel spreadsheet that summarize data from a number of detail tabs.
The cost function is represented in Table 1: There are three competing alternatives. Their cost depends on the year they become operational

The decision parameter is the year (represented in table 2 or equivalently Table3)

THe objective is to select the year for each alternatives so that total cost is minimal, subjected to 3 constraints (based on Table 3):

1) The choice value in Table 3 (B11 to F13) is either 1 or zero.
2) The sum of each row (G11 to G13) is strictly = 1.
3) The sum of each column in table 1 (B14 to F14) is bounded by a parameter, say 2 in this example.

I never use Excel to perform optimization, often get by using Perl. This time, the users request the result in Excel....

I would greatly appreciate any help from the respectable gurus in this board.
Thank you in advance,
pax

Table 1: dollar amount
    A      B      C      D      E      F
1   year   2013   2014   2015   2016   2017
2   Alt1   23     34     36     39     41
3   Alt2   0      15     24     32     42
4   Alt3   0      0      40     41     43

Table 2: decision parameter. Column B values can be any val;ue matching the values (year) in row 1 of Table 1 (subjected to the constraint given below:

    A       B
6   alt1    2013
7   alt2    2015
8   alt3    2015

Equivalently, Table 2 can be written as
Table 3: Constraintd

    A      B      C      D      E      F      G
10  year   2013   2014   2015   2016   2017  SUM(B:F)
11  Alt1   1      0      0      0      0      1
12  Alt2   0      0      1      0      0      1
13  Alt3   0      0      1      0      0      1
14  Sum    1      0      2      0      0      3

Open in new window

m4trix

seems simple enough if I'm understanding it correctly. One question though: is total cost simply the one value corresponding to the year the alternative starts? or is it that year plus all subsequent years?
If the former, then in your example would the correct solution be Alt1: 2013, Alt2: 2013, Alt3: 2014?
(total cost = B2 + B3 + C4 = 23 + 0 + 0 = 23)
or if the latter, would the correct solution be Alt1: 2017, Alt2: 2016, Alt3: 2017?
(total cost = F2 + E3 + F3 + F4 = 41 + 32 + 42 + 43 = 158)

cpeters5

ASKER

m4trix,
What I am looking for is the algorithm rather than the anser. The actual problem is much bigger to solve manually. This is just a simplify example to demonstrate how the solver works.

The cost function: what we are minimizing is the sum of each algernative value for the year they start.

Thanks,
pax

ASKER CERTIFIED SOLUTION

m4trix

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

redmondb

m4trix,

If I've understood the requirements correctly, your solution does meet the constraints, but it's not the minimum (e.g. "2013, 2013, 2014" gives a total cost of 23). The problem is that the GRG Nonlinear method you used can return local (not global) minimums. On the other hand, the Evolutionary does give the global minimum - but I don't know whether that's guaranteed (as it would be with Simplex LP).

cpeters5,

Are zero-values allowed - there's no explicit constraint preventing them?

Regards,
Brian.

cpeters5

ASKER

Thanks!
Though it does not lead to a solution, there is enough information for me to follow and eventually got there!
pax