Kaip įmonė gali naudoti "Solver", kad nustatytų, kokių projektų ji turėtų imtis?
Kiekvienais metais tokia įmonė kaip "Eli Lilly" turi nustatyti, kokius vaistus kurti; tokia įmonė kaip "„Microsoft“", kokias programinės įrangos programas kurti; tokia įmonė kaip "Proctor" & "Gamble", kurią naujus vartojimo produktus kurti. "Excel" sprendimo paieškos funkcija gali padėti įmonei priimti šiuos sprendimus.
Kaip įmonė gali naudoti "Solver", kad nustatytų, kokių projektų ji turėtų imtis?
Dauguma korporacijų nori imtis projektų, kurie prisideda prie didžiausios grynosios dabartinės vertės (NPV), atsižvelgiant į ribotus išteklius (paprastai kapitalą ir darbą). Tarkime, kad programinės įrangos kūrimo įmonė bando nustatyti, kurio iš 20 programinės įrangos projektų ji turėtų imtis. Kiekvieno projekto įneštas NPV (milijonais dolerių), taip pat kapitalas (milijonais dolerių) ir programuotojų skaičius, reikalingas per kiekvienus ateinančius trejus metus, yra pateiktas Pagrindinio modelio darbalapyje failų Capbudget.xlsx, kuris parodytas 30-1 paveiksle kitame puslapyje. Pvz., 2 projekto grąža yra 908 mln. Tam reikia 151 milijono dolerių per 1 metus, 269 milijonus dolerių per 2 metus ir 248 milijonus dolerių per 3 metus. 2 projektui reikalingi 139 programuotojai 1 metais, 86 programuotojai 2 metais ir 83 programuotojai 3 metais. Langeliuose E4:G4 rodomas kapitalas (milijonais dolerių) per kiekvienus iš trejų metų, o langeliuose H4:J4 rodoma, kiek programuotojų yra laisvų. Pavyzdžiui, per 1 metus yra iki 2,5 milijardo dolerių kapitalo ir 900 programuotojų.
Įmonė turi nuspręsti, ar ji turėtų imtis kiekvieno projekto. Tarkime, kad negalime imtis programinės įrangos projekto dalies; Pavyzdžiui, jei skirtume 0,5 reikalingų išteklių, turėtume neveikiančią programą, kuri atneštų 0 USD pajamų!
Modeliuojant situacijas, kai kažką darote arba nedarote, reikia naudoti dvejetainius kintančius langelius. Dvejetainis kintantis langelis visada lygus 0 arba 1. Kai dvejetainis kintantis langelis, atitinkantis projektą, yra lygus 1, mes darome projektą. Jei dvejetainis kintantis langelis, atitinkantis projektą, lygus 0, projekto nedarome. Nustatėte, kad sprendimo paieška naudotų dvejetainių kintančių langelių diapazoną įtraukdami apribojimą – pasirinkite keičiamuosius langelius, kuriuos norite naudoti, tada dialogo lange Pridėti apribojimą sąraše pasirinkite Šiukšlinė.
Book 
Atsižvelgdami į tai, esame pasirengę išspręsti programinės įrangos projekto atrankos problemą. Kaip visada naudodami sprendimo paieškos modelį, pradedame identifikuodami tikslinį langelį, besikeičiančius langelius ir apribojimus.
- Tikslinis langelis. Maksimizuojame atrinktų projektų sugeneruotą NPV.
- Kintantys langeliai. Kiekvienam projektui ieškome 0 arba 1 dvejetainio kintančio langelio. Šiuos langelius radau diapazone A6:A25 (ir pavadinau diapazoną doit). Pavyzdžiui, 1 langelyje A6 nurodo, kad vykdome 1 projektą; 0 langelyje C6 reiškia, kad mes nesiimame 1 projekto.
- Apribojimai. Turime užtikrinti, kad kiekvienais t metais (t = 1, 2, 3) t metų panaudotas kapitalas būtų mažesnis arba lygus turimam t metų kapitalui, o t metais panaudota darbo jėga būtų mažesnė arba lygi t metų darbo jėgai.
Kaip matote, mūsų darbalapis turi apskaičiuoti bet kokios projektų atrankos NPV, kasmet naudojamą kapitalą ir kiekvienais metais naudojamus programuotojus. B2 langelyje naudoju formulę SUMPRODUCT(doit,NPV), kad apskaičiuočiau bendrą pasirinktų projektų sugeneruotą NPV. (Diapazono pavadinimas NPV nurodo diapazoną C6:C25.) Kiekvienam projektui, kurio stulpelyje A yra reikšmė 1, ši formulė paima projekto NPV, o kiekvienam projektui, kurio stulpelyje A stulpelyje yra 0, ši formulė nepaima projekto NPV. Todėl galime suskaičiuoti visų projektų NPV, o tikslinis langelis yra tiesinis, nes jis apskaičiuojamas sumuojant terminus, atitinkančius formą (kintantis langelis)*(konstanta). Panašiu būdu apskaičiuoju kiekvienais metais sunaudotą kapitalą ir kiekvienais metais sunaudojamą darbą, kopijuodamas iš E2 į F2:J2 formulę SUMPRODUCT(doit,E6:E25).
Dabar užpildau dialogo langą Sprendimo paieškos parametrai, kaip parodyta 30-2 paveiksle.
Book 
Mūsų tikslas – maksimizuoti pasirinktų projektų NPV (langelis B2). Mūsų kintantys langeliai (diapazonas, pavadintas doit) yra kiekvieno projekto dvejetainiai kintantys langeliai. Apribojimas E2:J2<=E4:J4 užtikrina, kad kiekvienais metais sunaudotas kapitalas ir darbo jėga būtų mažesni arba lygūs turimam kapitalui ir darbo jėgai. Norėdami pridėti apribojimą, dėl kurio besikeičiantys langeliai tampa dvejetainiais, dialogo lange Sprendimo paieškos parametrai paspaudžiu Įtraukti, tada dialogo lango viduryje esančiame sąraše pasirenku Šiukšliadėžė. Dialogo langas Pridėti apribojimą turėtų atrodyti taip, kaip parodyta 30-3 paveiksle.
Book 
Mūsų modelis yra linijinis, nes paskirties langelis apskaičiuojamas kaip terminų, turinčių formą (kintantis langelis)*(konstanta), suma ir dėl to, kad išteklių naudojimo apribojimai yra apskaičiuojami lyginant (kintančių langelių)*(konstantų) sumą su konstanta.
Užpildę dialogo langą Sprendimo paieškos parametrai, spustelėkite Spręsti ir gausime rezultatus, parodytus anksčiau 30-1 paveiksle. Bendrovė gali gauti maksimalią 9 293 milijonų JAV dolerių (9,293 milijardo JAV dolerių) NPV pasirinkusi 2, 3, 6–10, 14–16, 19 ir 20 projektus.
Kitų apribojimų tvarkymas
Kartais projektų atrankos modeliai turi kitų apribojimų. Pavyzdžiui, tarkime, kad pasirinkę 3 projektą, turime pasirinkti ir 4 projektą. Kadangi mūsų dabartinis optimalus sprendimas pasirenka 3 projektą, bet ne 4 projektą, žinome, kad dabartinis sprendimas negali išlikti optimalus. Norėdami išspręsti šią problemą, tiesiog pridėkite apribojimą, kad 3 projekto dvejetainis kintantis langelis yra mažesnis arba lygus 4 projekto dvejetainiam kintančiam langeliui.
Šį pavyzdį galite rasti failo Capbudget.xlsx darbalapyje Jei 3 , tai 4, kuris parodytas 30-4 paveiksle. Langelis L9 nurodo dvejetainę reikšmę, susijusią su 3 projektu, o langelis L12 nurodo dvejetainę reikšmę, susijusią su 4 projektu. Pridėjus apribojimą L9<=L12, jei pasirinksime 3 projektą, L9 bus lygus 1, o mūsų apribojimas priverčia L12 (4 projekto dvejetainį) būti lygų 1. Mūsų apribojimas taip pat turi palikti dvejetainę reikšmę kintančiame 4 projekto langelyje neapribotą, jei nepasirenkame 3 projekto. Jei nepasirinksime 3 projekto, L9 bus lygus 0, o mūsų apribojimas leidžia 4 projekto dvejetainiam skaičiui būti lygiam 0 arba 1, ko mes norime. Naujas optimalus sprendimas parodytas 30-4 paveiksle.
Book 
Naujas optimalus sprendimas apskaičiuojamas, jei pasirinkus 3 projektą taip pat reikia pasirinkti 4 projektą. Dabar tarkime, kad galime atlikti tik keturis projektus nuo 1 iki 10 projektų. (Žr. darbalapį Daugiausiai 4 iš P1–P10, parodytą 30-5 pav.) Langelyje L8 apskaičiuojame dvejetainių reikšmių, susietų su 1–10 projektais, sumą naudodami formulę SUM(A6:A15). Tada pridedame apribojimą L8<=L10, kuris užtikrina, kad bus pasirinkti daugiausia 4 iš pirmųjų 10 projektų. Naujas optimalus sprendimas parodytas 30-5 paveiksle. NPV nukrito iki 9,014 mlrd.
Dvejetainio ir sveikųjų skaičių programavimo problemų sprendimas
Linijinių sprendimų paieškos modelius, kuriuose kai kurie arba visi kintantys langeliai turi būti dvejetainiai arba sveikieji skaičiai, paprastai sunkiau išsprendžiami nei linijinius modelius, kuriuose visi kintantys langeliai turi būti trupmenos. Dėl šios priežasties dažnai esame patenkinti beveik optimaliu dvejetainio ar sveikojo skaičiaus programavimo problemos sprendimu. Jei sprendimo paieškos modelis naudojamas ilgą laiką, galite pakoreguoti leistino nuokrypio parametrą dialogo lange Sprendimo paieškos parinktys. (Žr. 30-6 pav.) Pvz., 0,5 % leistino nuokrypio parametras reiškia, kad sprendimo paieška sustos, kai pirmą kartą ras galimą sprendimą, kuris neviršija 0,5 procento teorinės optimalios tikslinės langelio reikšmės (teorinė optimali tikslinė langelio reikšmė yra optimali tikslinė reikšmė, aptinkama praleidus dvejetainius ir sveikųjų skaičių apribojimus). Dažnai susiduriame su pasirinkimu, ar rasti atsakymą per 10 procentų optimalaus per 10 minučių, ar rasti optimalų sprendimą per dvi savaites kompiuterio laiko! Numatytoji leistino nuokrypio reikšmė yra 0,05 %, o tai reiškia, kad sprendimo paieška sustabdoma, kai randa tikslinio langelio reikšmę 0,05 proc. teorinės optimalios tikslinės langelio reikšmės.
Problemos
- Įmonė turi devynis svarstomus projektus. Kiekvieno projekto pridėta NPV ir kiekvienam projektui reikalingas kapitalas per ateinančius dvejus metus parodyti toliau pateiktoje lentelėje. (Visi skaičiai yra milijonais.) Pvz., įgyvendinant 1 projektą NPV bus pridėta 14 milijonų JAV dolerių ir reikės 12 milijonų JAV dolerių išlaidų per 1 metus ir 3 milijonus JAV dolerių per 2 metus. 1 metais projektams skiriama 50 milijonų dolerių kapitalo, o 2 metais – 20 milijonų dolerių.
| NPV | 1 metų išlaidos | 2 metų išlaidos | |
|---|---|---|---|
| 1 projektas | 14 | 12 | 3 |
| 2 projektas | 17 | 54 | 7 |
| 3 projektas | 17 | 6 | 6 |
| 4 projektas | 15 | 6 | 2 |
| 5 projektas | 40 | 30 | 35 |
| 6 projektas | 12 | 6 | 6 |
| 7 projektas | 14 | 48 | 4 |
| 8 projektas | 10 | 36 | 3 |
| 9 projektas | 12 | 18 | 3 |
- Jei negalime imtis projekto dalies, bet turime imtis viso projekto arba nė vieno, kaip galime maksimaliai padidinti NPV?
- Tarkime, jei vykdomas 4 projektas, reikia imtis 5 projekto. Kaip galime maksimizuoti NPV?
Leidykla bando nustatyti, kurią iš 36 knygų ji turėtų išleisti šiais metais. Failo Pressdata.xlsx pateikiama tokia informacija apie kiekvieną knygą:
- Numatomos pajamos ir plėtros išlaidos (tūkstančiais dolerių)
- Kiekvienos knygos puslapiai
- Ar knyga skirta programinės įrangos kūrėjų auditorijai (nurodyta 1 E stulpelyje)
Leidykla šiais metais gali išleisti iki 8500 puslapių knygas ir turi išleisti bent keturias knygas, skirtas programinės įrangos kūrėjams. Kaip įmonė gali maksimaliai padidinti savo pelną?
Apie straipsnį
Šis straipsnis adaptuotas iš Wayne L. Winston knygos " Microsoft Office Excel 2007" duomenų analizė ir verslo modeliavimas .
Ši klasės stiliaus knyga buvo sukurta iš Wayne Winston, gerai žinomo statistikaus ir verslo profesoriaus, kuris specializuojasi kūrybiniame, praktiniame "Excel" taikyme, pristatymų serijos.