Przykładowe modele

Portfel inwestycyjny

Opis sytuacji

Mamy do dyspozycji 400 tys. zł, które możemy zainwestować w 4 różne przedsięwzięcia; ich charakterystykę zawiera poniższa tabela:

Cel

Opracować arkusz kalkulacyjny do maksymalizacji rocznego zysku z inwestycji.

Zadania

1. Sformułować model matematyczny zagadnienia,
2. Opracować arkusz,
3. Rozwiązać problem.

Rozwiązanie

1. Model matematyczny

Jeśli przez x1, x2, x3, x4 oznaczymy kwotę zainwestowaną w przedsięwzięcia 1 - 4, to problem możemy zapisać następująco:

Znaleźć x1*z1+x2*z2+x3*z3+x4*z4->max
przy ograniczeniach
x1+x2+x3+x4=B
x1>=dg1, x1<=gg1
x2>=dg2, x2<=gg2
x3>=dg3, x3<=gg3
x4>=dg4, x4<=gg4
gdzie:
zi - stopa zwrotu z poszczególnych inwestycji,
B - dysponowany budżet,
dgi - dolna granica inwestycji,
ggi - górna granica inwestycji.

2. Arkusz

Obraz arkusza przedstawia rysunek:

Proszę ściągnąć ze strony na swój dysk H: gotowy arkusz PORTFEL.XLS.

3. Rozwiązanie

Jest to prosty problem, który - po krótkim zastanowieniu - można rozwiązać na piechotę. My wykorzystamy możliwości Solvera.
Proszę ustawić kursor w komórce C20 (Solver przyjmie tę komórkę jako funkcję celu).
Z opcji Narzędzia wybrać Solver (jeśli go nie ma, to znaczy, że nie jest zainstalowany; proszę wtedy wybrać podopcję Dodatki, zaznaczyć pole przy Solver i nacisnąć OK).
Pojawi się okienko Solvera:

Proszę zwrócić uwagę, że komórka C20 została domyślnie przyjęta za komórkę celu, która będzie maksymalizowana (gdybyśmy szukali minimum lub chcieli nadać komórce C20 pożądaną wartość, zaznaczylibyśmy odpowiednie pola wyboru).
Drugim krokiem jest podanie komórek ze zmiennymi decyzyjnymi: w naszym przypadku są to komórki C15:C18 zawierające wielkości nakładów na poszczególne inwestycje. Należy kliknąć w wierszu Komórki zmieniane, po czym zaznaczyć obszar C15:C18, zaznaczone komórki zostaną przeniesione do Solvera.
Z kolei należy podać wszystkie ograniczenia w okienku Warunki ograniczające. Klikamy Dodaj - rozwinie się okienko Dodaj warunek ograniczający

w którym wpisujemy (adresy możemy wpisywać lub zaznaczać w arkuszu) nasze 3 ograniczenia:

Uwaga: po każdym ograniczeniu naciskamy Dodaj, za wyjątkiem ostatniego, po którym naciskamy OK.

W końcu powinniśmy otrzymać następujące okienko Solvera:

Jako że wszystko jest wpisane prawidłowo, możemy nacisnąć Rozwiąż i otrzymać rozwiązanie. Proszę zauważyć, że w tym przypadku jest ono dość trywialne.

Więcej o narzędziu Solver można znaleźć tutaj.