Używanie dodatku Solver do budżetowania kapitałowego

Większość korporacji chce podejmować projekty, które przyczyniają się do największej bieżącej wartości netto (NPV), z zastrzeżeniem ograniczonych zasobów (zazwyczaj kapitału i pracy). Załóżmy, że firma zajmująca się tworzeniem oprogramowania próbuje ustalić, które z 20 projektów oprogramowania powinna podjąć. NpV (w milionach dolarów) przyczynia się do każdego projektu, jak również kapitału (w milionach dolarów) i liczba programistów potrzebnych w ciągu każdego z najbliższych trzech lat jest podany w arkuszu Model podstawowy w pliku Capbudget.xlsx, który jest pokazany na rysunku 30-1 na następnej stronie. Na przykład program Project 2 daje 908 mln USD. Wymaga 151 milionów dolarów w roku 1, 269 milionów dolarów w roku 2 i 248 milionów dolarów w roku 3. Program Project 2 wymaga 139 programistów w roku 1, 86 programistów w roku 2 i 83 programistów w roku 3. Komórki E4:G4 pokazują kapitał (w milionach dolarów) dostępny w ciągu każdego z trzech lat, a komórki H4:J4 wskazują, ilu programistów jest dostępnych. Na przykład, w roku 1 do 2,5 miliardów dolarów kapitału i 900 programistów są dostępne.

Firma musi zdecydować, czy powinna podjąć każdy projekt. Załóżmy, że nie możemy podjąć ułamek projektu oprogramowania; jeśli przydzielimy 0,5 z potrzebnych zasobów, na przykład, będziemy mieli program niepracujący, który przyniesie nam 0 usd przychodów!

Sztuczka w sytuacjach modelowania, w których albo coś robisz, albo czegoś nie robisz, to używanie binarnych komórek zmieniających. Binarna komórka zmieniająca zawsze równa się 0 lub 1. Gdy binarna komórka zmieniająca odpowiadająca projektowi równa się 1, wykonujemy projekt. Jeśli binarna komórka zmieniająca odpowiadająca projektowi równa jest 0, nie wykonujemy projektu. Dodatek Solver został skonfigurowany do używania zakresu binarnych komórek zmieniających się przez dodanie ograniczenia — zaznacz komórki, których chcesz użyć, a następnie wybierz pozycję Bin z listy w oknie dialogowym Dodawanie ograniczenia.

W tym tle jesteśmy gotowi rozwiązać problem z wyborem projektu oprogramowania. Jak zawsze w przypadku modelu dodatku Solver, zaczynamy od zidentyfikowania komórki docelowej, zmieniających się komórek i ograniczeń.

• Komórka docelowa. Maksymalizujemy npv wygenerowany przez wybrane projekty.

• Zmienianie komórek. Dla każdego projektu szukamy 0 lub 1 binarnej komórki zmieniającej. Te komórki znajdują się w zakresie A6:A25 (i nazwano zakres doit). Na przykład wartość 1 w komórce A6 oznacza, że realizujemy program Project 1. wartość 0 w komórce C6 oznacza, że nie podejmujemy programu Project 1.

• Ograniczenia. Musimy upewnić się, że dla każdego roku t (t=1, 2, 3), Użyty kapitał roczny t jest mniejszy lub równy dostępnemu kapitałowi Year t, a Liczba używanego roku pracy jest mniejsza lub równa dostępnej pozycji Rok t pracy.

Jak widać, nasz arkusz musi obliczyć dla każdego wyboru projektów NPV, kapitału używanego rocznie i programistów używanych każdego roku. W komórce B2 za pomocą formuły SUMA.ILOCZYNÓW(doit;NPV) obliczam całkowitą wartość NPV wygenerowaną przez wybrane projekty. (Nazwa zakresu NPV odwołuje się do zakresu C6:C25). W przypadku każdego projektu z wartością 1 w kolumnie A ta formuła pobiera wartość NPV projektu, a w przypadku każdego projektu z wartością 0 w kolumnie A ta formuła nie pobiera wartości NPV projektu. Dlatego możemy obliczyć NPV wszystkich projektów, a nasza komórka docelowa jest liniowa, ponieważ jest obliczana przez sumowanie terminów zgodnych z formularzem (zmiana komórki)*(stała). W podobny sposób obliczam kapitał używany każdego roku i pracę używaną każdego roku, kopiując z E2 do F2:J2 formułę SUMA.ILOCZYNÓW(doit;E6:E25).

Teraz wypełniam okno dialogowe Parametry dodatku Solver, jak pokazano na ilustracji 30-2.

Naszym celem jest zmaksymalizowanie NPV wybranych projektów (komórka B2). Nasze zmieniające się komórki (zakres nazwany doit) są binarnymi komórkami zmieniającymi dla każdego projektu. Ograniczenie E2:J2<=E4:J4 gwarantuje, że w ciągu każdego roku kapitał i używana praca są mniejsze lub równe dostępnemu kapitałowi i pracy. Aby dodać ograniczenie powodujące binarne zmienianie komórek, klikam pozycję Dodaj w oknie dialogowym Parametry dodatku Solver, a następnie wybieram pozycję Kosz z listy pośrodku okna dialogowego. Okno dialogowe Dodawanie ograniczenia powinno wyglądać tak, jak pokazano na rysunku 30–3.

Nasz model jest liniowy, ponieważ komórka docelowa jest obliczana jako suma terminów, które mają postać (zmiana komórki)*(stała) i ponieważ ograniczenia użycia zasobów są obliczane przez porównanie sumy (zmieniających się komórek)*(stałych) ze stałą.

Gdy zostanie wypełnione okno dialogowe Parametry dodatku Solver, kliknij przycisk Rozwiąż, a wyniki zostaną wyświetlone wcześniej na ilustracji 30-1. Firma może uzyskać maksymalną wartość NPV w wysokości 9293 mln USD (9,293 mld USD), wybierając pozycję Projekty 2, 3, 6–10, 14–16, 19 i 20.

Czasami modele wyboru projektu mają inne ograniczenia. Załóżmy na przykład, że jeśli wybierzemy pozycję Project 3, musimy również wybrać pozycję Projekt 4. Ponieważ nasze obecnie optymalne rozwiązanie wybiera program Project 3, ale nie program Project 4, wiemy, że nasze bieżące rozwiązanie nie może pozostać optymalne. Aby rozwiązać ten problem, po prostu dodaj ograniczenie, że binarna komórka zmieniająca w programie Project 3 jest mniejsza lub równa binarnej komórce zmieniającej dla programu Project 4.

Ten przykład można znaleźć w arkuszu Jeżeli 3 to 4 w Capbudget.xlsx pliku, który jest pokazany na ilustracji 30-4. Komórka L9 odwołuje się do wartości binarnej związanej z programem Project 3, a komórka L12 do wartości binarnej związanej z programem Project 4. Dodając ograniczenie L9<=L12, jeśli wybierzemy projekt 3, L9 równa się 1, a nasze siły ograniczeń L12 (binarne projektu 4) są równe 1. Nasze ograniczenie musi również pozostawić wartość binarną w zmieniającej się komórce programu Project 4 bez ograniczeń, jeśli nie zaznaczymy programu Project 3. Jeśli nie wybierzemy pozycji Project 3, L9 równa się 0, a nasze ograniczenie pozwoli binarnym programowi Project 4 na równe 0 lub 1, co jest tym, czego chcemy. Nowe optymalne rozwiązanie przedstawiono na ilustracji 30-4.

Nowe optymalne rozwiązanie jest obliczane, jeśli wybranie programu Project 3 oznacza, że musimy również wybrać pozycję Project 4. Teraz załóżmy, że możemy wykonać tylko cztery projekty spośród projektów od 1 do 10. (Zobacz co najwyżej 4 arkusze P1–P10 przedstawione na ilustracji 30-5). W komórce L8 obliczamy sumę wartości binarnych skojarzonych z projektami od 1 do 10 za pomocą formuły SUMA(A6:A15). Następnie dodajemy ograniczenie L8<=L10, co zapewnia, że co najwyżej zaznaczono 4 z pierwszych 10 projektów. Nowe optymalne rozwiązanie przedstawiono na ilustracji 30-5. NPV spadła do 9,014 miliardów dolarów.

Modele dodatku Solver liniowego, w których niektóre lub wszystkie zmieniające się komórki muszą być binarne lub całkowite, są zwykle trudniejsze do rozwiązania niż modele liniowe, w których wszystkie zmieniające się komórki mogą być ułamkami. Z tego powodu często jesteśmy zadowoleni z niemal optymalnego rozwiązania problemu z programowaniem binarnym lub całkowitym. Jeśli twój model dodatku Solver działa przez długi czas, warto rozważyć dostosowanie ustawienia Tolerancja w oknie dialogowym Opcje dodatku Solver. (Zobacz rysunek 30–6). Na przykład ustawienie tolerancji 0,5% oznacza, że dodatek Solver zatrzyma się po raz pierwszy, gdy znajdzie rozwiązanie możliwe do znalezienia w granicach 0,5 procent teoretycznej optymalnej wartości docelowej komórki docelowej (teoretyczna optymalna wartość docelowa jest optymalną wartością docelową znalezioną, gdy ograniczenia binarne i całkowite zostaną pominięte). Często mamy do czynienia z wyborem między znalezieniem odpowiedzi w ciągu 10 procent optymalnej w 10 minut lub znalezieniem optymalnego rozwiązania w ciągu dwóch tygodni czasu komputerowego! Domyślna wartość tolerancji to 0,05%, co oznacza, że dodatek Solver zatrzymuje się po znalezieniu wartości komórki docelowej w granicach 0,05 procent teoretycznej optymalnej wartości docelowej komórki.

1. Firma ma dziewięć projektów pod uwagę. Wartość NPV dodana przez każdy projekt oraz kapitał wymagany przez każdy projekt w ciągu najbliższych dwóch lat przedstawiono w poniższej tabeli. (Wszystkie liczby są w milionach). Na przykład program Project 1 doda 14 mln USD w funkcji NPV i będzie wymagać wydatków w wysokości 12 mln USD w roku 1 i 3 mln USD w roku 2. W roku 1, 50 milionów dolarów kapitału jest dostępne dla projektów, a 20 milionów dolarów jest dostępne w roku 2.

	NPV	Wydatki na rok 1	Wydatki na rok 2
Project 1	14	12	3
Project 2	17	54	7
Project 3	17	6	6
Project 4	15	6	2
Project 5	40	30	35
Project 6	12	6	6
Project 7	14	48	4
Project 8	10	36	3
Project 9	12	18	3

• Jeśli nie możemy podjąć ułamka projektu, ale musimy podjąć albo cały projekt, albo żaden z nich, w jaki sposób możemy zmaksymalizować NPV?

• Załóżmy, że w przypadku realizacji projektu 4 należy podjąć projekt 5. Jak można zmaksymalizować npv?

• Firma wydawniccza próbuje ustalić, która z 36 książek powinna opublikować w tym roku. Pressdata.xlsx plików zawiera następujące informacje o każdej książce:

  • Przewidywane koszty przychodów i rozwoju (w tysiącach dolarów)

  • Strony w każdej książce

  • Czy książka jest skierowana do odbiorców programistów (wskazywane przez 1 w kolumnie E)

    Firma wydawniccza może publikować książki o łącznej sumie do 8500 stron w tym roku i musi opublikować co najmniej cztery książki skierowane do deweloperów oprogramowania. Jak firma może zmaksymalizować swój zysk?

Ten artykuł został zaadaptowany przez Wayne'a L. Winstona z analizy danych i modelowania biznesowego programu Microsoft Office Excel 2007 .

Ta książka w stylu klasy została opracowana na podstawie serii prezentacji Wayne'a Winstona, znanego statystyka i profesora biznesu, który specjalizuje się w kreatywnych, praktycznych zastosowaniach programu Excel.