W jaki sposób firma może używać dodatku Solver do określania projektów, które powinna realizować?
Każdego roku firma taka jak Eli Lilly musi określić, które leki opracować; firma taka jak Microsoft, jakie programy opracować; firma taka jak Proctor & Gamble, które nowe produkty konsumenckie opracować. Dodatek Solver w programie Excel może ułatwić firmie podejmowanie takich decyzji.
W jaki sposób firma może używać dodatku Solver do określania projektów, które powinna realizować?
Większość korporacji chce realizować projekty, które wnoszą największą wartość bieżącą netto (NPV), przy ograniczonych zasobach (zwykle kapitale i pracy). Załóżmy, że firma tworząca oprogramowanie próbuje określić, którego z 20 projektów programistycznych powinna się podjąć. Wartość NPV (w milionach dolarów) wniesiona przez każdy projekt, jak również kapitał (w milionach dolarów) i liczba programistów potrzebnych w każdym z następnych trzech lat są podane w arkuszu Model podstawowy w Capbudget.xlsx pliku, który jest pokazany na rysunku 30-1 na następnej stronie. Na przykład projekt 2 przynosi 908 milionów dolarów. 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. Projekt 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 złotych) dostępny w każdym z trzech lat, a komórki H4:J4 wskazują liczbę dostępnych programistów. Na przykład w roku 1 dostępny jest do 2,5 miliarda dolarów kapitału i 900 programistów.
Firma musi zdecydować, czy powinna podjąć się każdego projektu. Załóżmy, że nie możemy podjąć się ułamka projektu programistycznego; Na przykład, jeśli przeznaczymy 0,5 potrzebnych zasobów, będziemy mieli niedziałający program, który przyniesie nam 0 USD przychodu!
Sztuczka w modelowaniu sytuacji, w których coś robisz lub nie robisz, polega na użyciu binarnej zmiany komórek. Wartość komórki zmieniającej wartość binarną jest zawsze równa 0 lub 1. Gdy binarna zmieniająca się komórka odpowiadająca projektowi równa się 1, wykonujemy projekt. Jeśli wartość binarna zmieniająca komórkę odpowiadająca projektowi jest równa 0, projekt nie jest wykonywany. Dodatek Solver jest skonfigurowany do używania zakresu binarnych zmienianych komórek przez dodanie ograniczenia — zaznacz odpowiednie komórki, a następnie wybierz z listy pozycję Przedział, korzystając z okna dialogowego Dodawanie ograniczenia.
Mając to na uwadze, jesteśmy gotowi rozwiązać problem wyboru projektu oprogramowania. Jak zwykle w przypadku modelu dodatku Solver, należy najpierw zidentyfikować komórkę docelową, zmieniane komórki i ograniczenia.
- Komórka docelowa. Maksymalizujemy NPV generowane przez wybrane projekty.
- Zmienianie komórek. Szukamy 0 lub 1 binarnej komórki zmieniającej dla każdego projektu. Zlokalizowałem te komórki w zakresie A6:A25 (i nazwałem zakres doit). Na przykład wartość 1 w komórce A6 wskazuje, że realizujemy Projekt 1; 0 w komórce C6 oznacza, że nie realizujemy Projektu 1.
- Ograniczenia. Musimy upewnić się, że dla każdego roku t (t=1, 2, 3) zużyty kapitał t roku jest mniejszy lub równy dostępnemu kapitałowi w roku t , a rok t wykorzystany nakład pracy jest mniejszy lub równy dostępnej pracy w roku t .
Jak widać, nasz arkusz musi obliczać dla dowolnych wybranych projektów NPV, kapitał wykorzystywany rocznie i programistów wykorzystywanych każdego roku. W komórce B2 używam formuły SUMA.ILOCZYNÓW(doit;NPV) do obliczenia całkowitej wartości NPV wygenerowanej w wybranych projektach. (Nazwa zakresu NPV odwołuje się do zakresu C6:C25). W przypadku każdego projektu mającego wartość 1 w kolumnie A ta formuła pobiera wartość NPV projektu, a w przypadku każdego projektu mającego wartość 0 w kolumnie A nie pobiera wartości NPV projektu. W związku z tym jesteśmy w stanie obliczyć NPV wszystkich projektów, a nasza komórka docelowa jest liniowa, ponieważ jest obliczana przez sumowanie warunków następujących po postaci (zmiana komórki)*(stała). W podobny sposób obliczam kapitał zużywany każdego roku i pracę zużywaną każdego roku, kopiując z komórki E2 do komórki F2:J2 formułę SUMA.ILOCZYNÓW(doit;E6:E25).
Teraz wypełnię okno dialogowe Parametry dodatku Solver, jak pokazano na rysunku 30-2.
Naszym celem jest maksymalizacja NPV wybranych projektów (komórka B2). Nasze komórki zmieniające (zakres o nazwie doit) to binarne komórki zmieniające się dla każdego projektu. Ograniczenie E2:J2<=E4:J4 zapewnia, że w ciągu każdego roku wykorzystany kapitał i praca są mniejsze lub równe dostępnemu kapitałowi i pracy. Aby dodać ograniczenie, które powoduje, że zmieniane komórki są binarne, klikam przycisk Dodaj w oknie dialogowym Parametry dodatku Solver, a następnie z listy w środku okna dialogowego wybieram pozycję Kosz. Powinno zostać wyświetlone okno dialogowe Dodawanie ograniczenia, jak pokazano na rysunku 30-3.
Nasz model jest liniowy, ponieważ komórka docelowa jest obliczana jako suma warunków mających postać (komórka zmieniająca)*(stała), a ograniczenia użycia zasobów są obliczane przez porównanie sumy (zmiana komórek)*(stałych) ze stałą.
Po wypełnieniu okna dialogowego Parametry dodatku Solver kliknij przycisk Rozwiąż, aby uzyskać wyniki pokazane wcześniej na rysunku 30-1. Firma może uzyskać maksymalną wartość NPV w wysokości 9 293 mln USD (9,293 mld USD), wybierając projekty 2, 3, 6–10, 14–16, 19 i 20.
Obsługa innych ograniczeń
Czasami modele wyboru projektów mają inne ograniczenia. Załóżmy na przykład, że jeśli wybierzemy Projekt 3, musimy również wybrać Projekt 4. Ponieważ nasze bieżące optymalne rozwiązanie wybiera projekt 3, ale nie projekt 4, wiemy, że nasze bieżące rozwiązanie nie może pozostać optymalne. Aby rozwiązać ten problem, wystarczy dodać ograniczenie polegające na tym, że komórka zmiany danych binarnych w programie Project 3 jest mniejsza lub równa komórce zmieniającej dane binarne w programie Project 4.
Przykład ten można znaleźć w arkuszu Jeśli 3, to 4 w Capbudget.xlsx pliku, który pokazano na rysunku 30-4. Komórka L9 odwołuje się do wartości binarnej związanej z projektem 3, a komórka L12 do wartości binarnej związanej z projektem 4. Po dodaniu ograniczenia L9<=L12, jeśli wybierzemy Projekt 3, L9 będzie równe 1, a nasze ograniczenie wymusza, aby L12 (plik binarny Projekt 4) było równe 1. Nasze ograniczenie musi również pozostawiać wartość binarną w komórce zmieniającej projekt 4 nieograniczoną, jeśli nie wybierzemy projektu 3. Jeśli nie wybierzemy Projektu 3, L9 równa się 0, a nasze ograniczenie pozwoli, aby plik binarny Projekt 4 był równy 0 lub 1, co jest tym, czego chcemy. Nowe optymalne rozwiązanie pokazano na rysunku 30-4.
Nowe optymalne rozwiązanie jest obliczane, jeśli wybranie projektu 3 oznacza, że należy również wybrać projekt 4. Załóżmy teraz, że mogę wykonać tylko cztery projekty z zakresu projektów od 1 do 10. (Zobacz arkusz Co najwyżej 4 z P1–P10, pokazany na rysunku 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, dzięki czemu wybierane są co najwyżej 4 z pierwszych 10 projektów. Nowe optymalne rozwiązanie przedstawiono na rysunku 30-5. Wartość NPV spadła do 9,014 mld USD.
Rozwiązywanie problemów związanych z programowaniem binarnym i całkowitym
Modele dodatku Solver liniowe, w których wszystkie lub niektóre 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 prawie optymalnego rozwiązania problemu programowania binarnego lub całkowitego. Jeśli model dodatku Solver działa przez długi czas, warto rozważyć dostosowanie ustawienia tolerancji w oknie dialogowym Opcje dodatku Solver. (Patrz rysunek 30-6). Na przykład ustawienie tolerancji na poziomie 0,5% oznacza, że dodatek Solver zatrzyma się, gdy po raz pierwszy znajdzie wykonalne rozwiązanie mieszczące się w zakresie 0,5% teoretycznej optymalnej wartości komórki docelowej (teoretyczna optymalna wartość komórki docelowej to optymalna wartość docelowa znaleziona po pominięciu ograniczeń binarnych i całkowitych). Często stajemy przed wyborem między znalezieniem odpowiedzi w granicach 10 procent optymalnej wartości w ciągu 10 minut lub znalezieniem optymalnego rozwiązania w ciągu dwóch tygodni pracy przy komputerze! Domyślna wartość tolerancji wynosi 0,05%, co oznacza, że dodatek Solver zatrzymuje się, gdy znajdzie wartość komórki docelowej w zakresie 0,05% teoretycznej optymalnej wartości komórki docelowej.
Problemy
- Firma rozważa dziewięć projektów. Poniższa tabela przedstawia wartość bieżącą netto dodaną przez poszczególne projekty oraz kapitał wymagany przez każdy z projektów w ciągu najbliższych dwóch lat. (Wszystkie liczby podane są w milionach). Na przykład Projekt 1 zwiększy wartość bieżącą netto o 14 mln USD i będzie wymagał wydatków w wysokości 12 mln USD w roku 1 i 3 mln USD w roku 2. W pierwszym roku 50 milionów dolarów kapitału jest dostępnych na projekty, a 20 milionów dolarów jest dostępnych w roku 2.
| NPV | Wydatki w roku 1 | Wydatki w roku 2 | |
|---|---|---|---|
| Projekt 1 | 14 | 12 | 3 |
| Projekt 2 | 17 | 54 | 7 |
| Projekt 3 | 17 | 6 | 6 |
| Projekt 4 | 15 | 6 | 2 |
| Projekt 5 | 40 | 30 | 35 |
| Projekt 6 | 12 | 6 | 6 |
| Projekt 7 | 14 | 48 | 4 |
| Projekt 8 | 10 | 36 | 3 |
| Projekt 9 | 12 | 18 | 3 |
- Jeśli nie jesteśmy w stanie podjąć się realizacji ułamka projektu, ale musimy podjąć się całości projektu lub nie zrealizować go wcale, jak możemy zmaksymalizować NPV?
- Załóżmy, że jeśli Projekt 4 jest realizowany, to Projekt 5 musi zostać zrealizowany. Jak zmaksymalizować NPV?
Wydawnictwo próbuje ustalić, którą z 36 książek powinna wydać w tym roku. Plik Pressdata.xlsx zawiera następujące informacje o każdej książce:
- Przewidywane przychody i koszty rozwoju (w tysiącach dolarów)
- Liczba stron w każdej książce
- Czy książka jest skierowana do odbiorców programistów (na co wskazuje 1 w kolumnie E)
Firma wydawnicza może w tym roku opublikować książki o łącznej objętości do 8500 stron i musi opublikować co najmniej cztery książki skierowane do programistów. W jaki sposób firma może maksymalizować swój zysk?
O artykule
Ten artykuł został zaadaptowany z książki Wayne'a L. Winstona "Analiza danych i modelowanie biznesowe programu Microsoft Office Excel 2007 ".
Ta książka została opracowana na podstawie serii prezentacji Wayne'a Winstona, znanego statystyka i profesora biznesu, który specjalizuje się w kreatywnych i praktycznych zastosowaniach programu Excel.