Kā uzņēmums var izmantot Risinātāju, lai noteiktu, kādi projekti tam jāveic?
Katru gadu tādam uzņēmumam kā Eli Lilly ir jānosaka, kuras zāles izstrādāt; tāds uzņēmums kā Microsoft, kuras programmatūras programmas izstrādāt; tāds uzņēmums kā Proctor & Gamble, kuru izstrādāt jaunus patēriņa produktus. Risinātāja līdzeklis programmā Excel var palīdzēt uzņēmumam pieņemt šādus lēmumus.
Kā uzņēmums var izmantot Risinātāju, lai noteiktu, kādi projekti tam jāveic?
Lielākā daļa korporāciju vēlas veikt projektus, kas dod vislielāko neto pašreizējo vērtību (NPV), ievērojot ierobežotus resursus (parasti kapitālu un darbaspēku). Pieņemsim, ka programmatūras izstrādes uzņēmums cenšas noteikt, kurš no 20 programmatūras projektiem tam jāveic. Katra projekta ieguldītais NPV (miljonos dolāru), kā arī kapitāls (miljonos dolāru) un nepieciešamo programmētāju skaits katrā no nākamajiem trim gadiem ir norādīts pamata modeļa darblapā faila Capbudget.xlsx, kas parādīts 30-1. attēlā nākamajā lapā. Piemēram, 2. projekta ienesīgums ir 908 miljoni dolāru. Tas prasa 151 miljonu ASV dolāru 1. gadā, 269 miljonus ASV dolāru 2. gadā un 248 miljonus ASV dolāru 3. gadā. 2. projektam nepieciešami 139 programmētāji 1. gadā, 86 programmētāji 2. gadā un 83 programmētāji 3. gadā. Šūnās E4:G4 ir redzams katrā no šiem trim gadiem pieejamais kapitāls (miljonos dolāru), bet šūnā H4:J4 norādīts, cik programmētāju ir pieejams. Piemēram, 1. gadā ir pieejams kapitāls līdz 2,5 miljardiem ASV dolāru un 900 programmētāji.
Uzņēmumam ir jāizlemj, vai tam jāuzņemas katrs projekts. Pieņemsim, ka mēs nevaram uzņemties daļu no programmatūras projekta; Piemēram, ja mēs piešķiram 0,5 no nepieciešamajiem resursiem, mums būtu nestrādājoša programma, kas mums dotu 0 USD ieņēmumus!
Modelēšanas situācijās, kad jūs kaut ko darāt vai nedarāt, ir izmantot binārās mainīgās šūnas. Mainīgā binārā šūna vienmēr ir vienāda ar 0 vai 1. Kad binārā mainīgā šūna, kas atbilst projektam, ir vienāda ar 1, mēs veicam projektu. Ja binārā mainīgā šūna, kas atbilst projektam, ir vienāda ar 0, mēs neveicam projektu. Risinātājs tiek iestatīts izmantot bināro mainīgo šūnu diapazonu, pievienojot ierobežojumu — atlasiet mainīgās šūnas, kuras vēlaties izmantot, un pēc tam izvēlieties Nodalījums dialoglodziņa Ierobežojuma pievienošana sarakstā.
Ņemot vērā iepriekš minēto, mēs esam gatavi atrisināt programmatūras projektu atlases problēmu. Kā vienmēr ar risinātāja modeli, mēs sākam ar mērķa šūnas, mainīgo šūnu un ierobežojumu identificēšanu.
- Mērķa šūna. Mēs maksimāli palielinām atlasīto projektu ģenerēto NPV.
- Mainīgās šūnas. Mēs meklējam 0 vai 1 bināro mainīgo šūnu katram projektam. Es esmu atradis šīs šūnas diapazonā A6:A25 (un nosaucis diapazonu doit). Piemēram, 1 šūnā A6 norāda, ka mēs veicam 1. projektu; 0 šūnā C6 norāda, ka mēs neveicam 1. projektu.
- Ierobežojumi. Mums ir jānodrošina, ka katram gadam t (t = 1, 2, 3) izmantotais kapitāls ir mazāks vai vienāds ar gada t pieejamo kapitālu, un gadā t izmantotais darbaspēks ir mazāks vai vienāds ar gada t pieejamo darbaspēku.
Kā redzat, mūsu darblapā jebkurai projektu atlasei ir jāaprēķina NPV, katru gadu izmantotais kapitāls un katru gadu izmantotie programmētāji. Šūnā B2 es izmantoju formulu SUMPRODUCT(doit,NPV), lai aprēķinātu kopējo NPV, ko ģenerē atlasītie projekti. (Diapazona nosaukums NPV attiecas uz diapazonu C6:C25.) Katram projektam, kam kolonnā A ir vērtība 1, šī formula izvēlas projekta NPV, un katram projektam, kam kolonnā A ir nulle, šī formula neuzņem projekta NPV. Tādējādi mēs varam aprēķināt visu projektu NPV, un mūsu mērķa šūna ir lineāra, jo tā tiek aprēķināta, summējot terminus, kas seko formai (mainīgā šūna)*(konstante). Līdzīgā veidā es aprēķināju katru gadu izmantoto kapitālu un katru gadu patērēto darbaspēku, kopējot no E2 uz F2:J2 formulu SUMPRODUCT(doit,E6:E25).
Tagad es aizpildu dialoglodziņu Risinātāja parametri, kā parādīts 30-2. attēlā.
Mūsu mērķis ir maksimizēt atlasīto projektu NPV (šūna B2). Mūsu mainīgās šūnas (diapazons ar nosaukumu doit) ir katra projekta binārās mainīgās šūnas. Ierobežojums E2:J2<=E4:J4 nodrošina, ka katra gada laikā izmantotais kapitāls un darbaspēks ir mazāks vai vienāds ar pieejamo kapitālu un darbaspēku. Lai pievienotu ierobežojumu, kas mainīgās šūnas padara binārās, dialoglodziņā Risinātāja parametri noklikšķinu uz Pievienot un pēc tam dialoglodziņa vidū esošajā sarakstā atlasu Bin. Dialoglodziņam Ierobežojuma pievienošana jāatrodas, kā parādīts 30-3. attēlā.
Mūsu modelis ir lineārs, jo mērķa šūna tiek aprēķināta kā to terminu summa, kuriem ir forma (mainīgā šūna)*(konstante), un resursu lietojuma ierobežojumi tiek aprēķināti, salīdzinot (mainīgās šūnas)*(konstantes) summu ar konstanti.
Kad ir aizpildīts dialoglodziņš Risinātāja parametri, noklikšķiniet uz Risināt, un mēs paturam rezultātus, kas parādīti iepriekš 30-1. attēlā. Uzņēmums var iegūt maksimālo NPV 9,293 miljonu ASV dolāru (9,293 miljardu ASV dolāru) apmērā, izvēloties 2., 3., 6.–10., 14.–16., 19. un 20. projektu.
Citu ierobežojumu izmantošana
Dažreiz projektu atlases modeļiem ir citi ierobežojumi. Piemēram, ja mēs atlasām 3. projektu, ir jāizvēlas arī 4. projekts. Tā kā mūsu pašreizējais optimālais risinājums atlasa 3. projektu, bet ne 4. projektu, mēs zinām, ka mūsu pašreizējais risinājums nevar palikt optimāls. Lai atrisinātu šo problēmu, vienkārši pievienojiet ierobežojumu, ka binārā mainīgā šūna projektam 3 ir mazāka vai vienāda ar projekta 4 bināro mainīgo šūnu.
Šis piemērs ir atrodams faila Capbudget.xlsx darblapā Ja 3, tad 4 , kas parādīts 30-4. attēlā. Šūna L9 attiecas uz bināro vērtību, kas saistīta ar projektu 3, un šūna L12 attiecas uz bināro vērtību, kas saistīta ar projektu 4. Ja izvēlamies 3. projektu, L9 pievieno ierobežojumu<, tad L9 ir vienāds ar 1 un mūsu ierobežojums liek L12 (projekta 4 binārajam failam) būt vienādam ar 1. Mūsu ierobežojumam ir arī jāatstāj neierobežota binārā vērtība mainīgajā projekta 4. šūnā, ja mēs neatvēlamies 3. projektu. Ja mēs neizvēlamies 3. projektu, L9 ir vienāds ar 0, un mūsu ierobežojums ļauj projekta 4 binārajam vienādam ar 0 vai 1, kas ir tas, ko mēs vēlamies. Jaunais optimālais risinājums ir parādīts 30-4. attēlā.
Jauns optimāls risinājums tiek aprēķināts, ja atlasot 3. projektu, mums ir jāizvēlas arī 4. projekts. Pieņemsim, ka varam veikt tikai četrus projektus no 1. līdz 10. projektam. (Skatīt darblapu At maximum 4 Of P1–P10, kas parādīta 30-5. attēlā.) Šūnā L8 ar projektiem no 1 līdz 10 saistīto bināro vērtību summa tiek aprēķināta, izmantojot formulu SUM(A6:A15). Pēc tam mēs pievienojam ierobežojumu L8<=L10, kas nodrošina, ka tiek atlasīti ne vairāk kā 4 no pirmajiem 10 projektiem. Jaunais optimālais risinājums ir parādīts 30-5. attēlā. NPV ir samazinājies līdz 9,014 miljardiem ASV dolāru.
Binārās un veselo skaitļu programmēšanas problēmu risināšana
Lineāro risinātāju modeļus, kuros dažām vai visām mainīgajām šūnām ir jābūt binārām vai veseliem skaitļiem, parasti ir grūtāk atrisināt nekā lineāros modeļus, kuros visas mainīgās šūnas ir atļauts būt daļskaitļiem. Šī iemesla dēļ mēs bieži esam apmierināti ar gandrīz optimālu binārās vai veselo skaitļu programmēšanas problēmas risinājumu. Ja jūsu Risinātāja modelis darbojas ilgu laiku, varat apsvērt pielaides iestatījuma pielāgošanu dialoglodziņā Risinātāja opcijas. (Skatīt 30-6. attēlu.) Piemēram, pielaides iestatījums 0,5% nozīmē, ka risinātājs tiks pārtraukts, pirmo reizi atrodot iespējamu risinājumu, kas nepārsniedz 0,5 procentus no teorētiski optimālās mērķa šūnas vērtības (teorētiski optimālā mērķa šūnas vērtība ir optimālā mērķa vērtība, kas tiek atrasta, izlaižot bināro un veselo skaitļu ierobežojumu). Bieži vien mēs saskaramies ar izvēli starp atbildes atrašanu 10 procentu robežās no optimālā 10 minūtēs vai optimāla risinājuma atrašanu divu nedēļu laikā pēc datora laika! Noklusējuma pielaides vērtība ir 0,05%, kas nozīmē, ka risinātājs pārtrauc darboties, kad atrod mērķa šūnas vērtību 0,05 procentos no teorētiski optimālās mērķa šūnas vērtības.
Problēmas
- Uzņēmumam tiek izskatīti deviņi projekti. Nākamajā tabulā parādīts katra projekta pievienotais NPV un katram projektam nepieciešamais kapitāls nākamajos divos gados. (Visi skaitļi ir miljonos.) Piemēram, 1. projekts pievienos 14 miljonus dolāru NPV un prasīs izdevumus 12 miljonu dolāru apmērā 1. gadā un 3 miljonus dolāru 2. gadā. 1. gadā projektiem ir pieejami 50 miljoni ASV dolāru kapitāla, bet 20 miljoni ASV dolāru ir pieejami 2. gadā.
| NPV | 1. gada izdevumi | 2. gada izdevumi | |
|---|---|---|---|
| 1. projekts | 14 | 12 | 3 |
| 2. projekts | 17 | 54 | 7 |
| 3. projekts | 17 | 6 | 6 |
| 4. projekts | 15 | 6 | 2 |
| 5. projekts | 40 | 30 | 35 |
| 6. projekts | 12 | 6 | 6 |
| Project 7 | 14 | 48 | 4 |
| Project 8 | 10 | 36 | 3 |
| 9. projekts | 12 | 18 | 3 |
- Ja mēs nevaram uzņemties daļu no projekta, bet mums ir jāuzņemas viss vai neviens no projekta, kā mēs varam maksimāli palielināt NPV?
- Pieņemsim, ka, ja tiek uzsākts 4. projekts, ir jāuzņemas 5. projekts. Kā mēs varam maksimizēt NPV?
Izdevniecība cenšas noteikt, kura no 36 grāmatām tai vajadzētu izdot šogad. Faila Pressdata.xlsx sniedz šādu informāciju par katru grāmatu:
- Prognozētie ieņēmumi un izstrādes izmaksas (tūkstošos dolāru)
- Lappuses katrā grāmatā
- Vai grāmata ir paredzēta programmatūras izstrādātāju auditorijai (norāda ar 1 E kolonnā)
Izdevniecība šogad var publicēt grāmatas, kuru kopējais apjoms ir līdz 8500 lappusēm, un tai ir jāpublicē vismaz četras grāmatas, kas paredzētas programmatūras izstrādātājiem. Kā uzņēmums var maksimāli palielināt savu peļņu?
Par rakstu
Šis raksts ir pielāgots no Wayne L. Winston grāmatas Microsoft Office Excel 2007 Data Analysis and Business Modeling (Vides analīze un biznesa modelēšana ) parauga.
Šī klases stila grāmata tika izstrādāta no prezentāciju sērijas, ko sagatavojis pazīstams statistiķis un biznesa profesors Veins Vinstons, kurš specializējas radošos, praktiskos Excel pielietojumos.