Miten yritys voi Ratkaisimen avulla määrittää, mitkä projektit sen on syytä toteuttaa?
Joka vuosi Eli Lillyn kaltaisen yrityksen on määritettävä, mitä lääkkeitä kehittää; Microsoftin kaltainen yritys, jota ohjelmistoja kehitetään; Proctorin & Gamblen kaltainen yritys, jota uusia kuluttajatuotteita kehitetään. Excelin Ratkaisin-toiminto voi auttaa yrityksiä näiden päätösten tekemisessä.
Miten yritys voi Ratkaisimen avulla määrittää, mitkä projektit sen on syytä toteuttaa?
Useimmat yritykset haluavat toteuttaa projekteja, jotka tuottavat suurimman nettonykyarvon (NPV) rajallisten resurssien (yleensä pääoman ja työvoiman) alaisena. Oletetaan, että ohjelmistokehitysyritys yrittää määrittää, mihin 20 ohjelmistoprojektista sen pitäisi ryhtyä. Kunkin projektin nettonykyarvo (miljoonina dollareina) sekä pääoma (miljoonina dollareina) ja kunkin seuraavan kolmen vuoden aikana tarvittavien ohjelmoijien määrä on ilmoitettu perusmallin laskentataulukossa tiedostossa Capbudget.xlsx, joka on esitetty seuraavan sivun kuvassa 30-1. Esimerkiksi Project 2 tuottaa 908 miljoonaa dollaria. Se vaatii 151 miljoonaa dollaria vuonna 1, 269 miljoonaa dollaria vuonna 2 ja 248 miljoonaa dollaria vuonna 3. Projekti 2 tarvitsee 139 ohjelmoijaa vuonna 1, 86 ohjelmoijaa vuonna 2 ja 83 ohjelmoijaa vuonna 3. Soluissa E4:G4 näytetään käytettävissä oleva pääoma (miljoonina dollareina) kunkin kolmen vuoden aikana ja soluissa H4:J4 näytetään, kuinka monta ohjelmoijaa on käytettävissä. Esimerkiksi vuoden 1 aikana pääomaa on saatavilla jopa 2,5 miljardia dollaria ja 900 ohjelmoijaa.
Yrityksen on päätettävä, pitäisikö sen ryhtyä kuhunkin projektiin. Oletetaan, että emme voi suorittaa murto-osaa ohjelmistoprojektista; Jos varaamme esimerkiksi 0,5 resurssia tarvittavista resursseista, tuloksena on vapaa-ohjelma, joka toisi 0 euron tuoton!
Mallinnustilanteissa, joissa joko teet tai jätät tekemättä jotain, temppu on käyttää binaarisia muuttuvia soluja. Binaarinen muuttuva solu on aina yhtä suuri kuin 0 tai 1. Kun projektia vastaavan binaarisen muuttuvan solun arvo on 1, me suoritamme projektin. Jos projektia vastaavan binaarisen muuttujasolun arvo on 0, projektia ei tehdä. Voit määrittää Ratkaisimen käyttämään binaarisesti muuttuvien solujen aluetta lisäämällä rajoituksen valitsemalla haluamasi muuttuvat solut ja valitsemalla sitten Bin Lisää rajoite -valintaikkunan luettelosta.
Tätä taustaa vasten olemme valmiita ratkaisemaan ohjelmistoprojektin valintaongelman. Kuten aina Ratkaisin-mallia käytettäessä, aloitamme tunnistamalla kohdesolun, muuttuvat solut ja rajoitukset.
- Kohdesolu. Maksimoimme valittujen projektien tuottaman nettonykyarvon.
- Muuttuvat solut. Etsimme 0 tai 1 binaarisen vaihtuvan solun jokaiselle projektille. Löysin nämä solut alueelta A6:A25 (ja annoin alueelle nimeksi doit). Esimerkiksi solussa A6 oleva luku 1 tarkoittaa, että suoritamme projektin 1. 0 solussa C6 ilmaisee, että projektia 1 ei toteuteta.
- Rajoitukset. Meidän on varmistettava, että jokaista vuotta t (t=1, 2, 3) kohden käytetty pääoma vuonna t on pienempi tai yhtä suuri kuin vuonna t käytettävissä oleva pääoma ja käytetty työvoima vuonna t on pienempi tai yhtä suuri kuin vuoden t käytettävissä oleva työvoima.
Kuten näet, laskentataulukkomme on laskettava kaikista projekteista nettonykyarvo, vuosittain käytetty pääoma ja vuosittain käytetyt ohjelmoijat. Solussa B2 käytän kaavaa TULOJEN.SUMMA(KORKO,NNA) valittujen projektien tuottaman kokonaisnettonykyarvon laskemiseen. (Alueen nimi NPV viittaa alueeseen C6:C25.) Tämä kaava poimii projektin nettonykyarvon jokaiselle projektille, jonka sarakkeessa A on arvo 1. Tämä kaava ei poimi projektin nettonykyarvoa kullekin projektille, jonka sarakkeessa A on arvo 0. Pystymme siis laskemaan kaikkien projektien nettonykyarvon, ja kohdesolumme on lineaarinen, koska se lasketaan laskemalla yhteen termit, jotka noudattavat muotoa (muuttuva solu)*(vakio). Samalla tavalla lasken vuosittain käytetyn pääoman ja vuosittain käytetyn työvoiman kopioimalla solusta E2 soluun F2:J2 kaavan TULOJEN.SUMMA(doit,E6:E25).
Täytän nyt Ratkaisimen parametrit -valintaikkunan kuvan 30-2 mukaisesti.
Tavoitteenamme on maksimoida valittujen projektien nettonykyarvo (solu B2). Muuttujasolumme (alue nimeltä doit) ovat kunkin projektin binaarimuuttujasoluja. Rajoitus E2:J2<=E4:J4 varmistaa, että jokaisen vuoden aikana käytetty pääoma ja työvoima ovat pienempiä tai yhtä suuria kuin käytettävissä oleva pääoma ja työvoima. Kun haluan lisätä rajoitteen, joka tekee muuttuvista soluista binaarisen, valitsen Ratkaisimen parametrit -valintaikkunassa Lisää ja valitsen sitten valintaikkunan keskellä olevasta luettelosta Bin. Lisää rajoitus -valintaikkunan pitäisi näkyä kuvan 30-3 mukaisesti.
Mallimme on lineaarinen, koska kohdesolu lasketaan niiden termien summana, joiden muoto on (muuttuva solu)*(vakio) ja koska resurssien käyttörajoitukset lasketaan vertaamalla ( muuttuvien solujen)*(vakioiden) summaa vakioon.
Kun Ratkaisimen parametrit -valintaikkuna on täytetty, valitse Ratkaise ja saat kuvasta 30-1 näytetyt tulokset. Yhtiö voi saada enintään 9 293 miljoonan dollarin (9,293 miljardin dollarin) nettonykyarvon valitsemalla projektit 2, 3, 6–10, 14–16, 19 ja 20.
Muiden rajoitteiden käsitteleminen
Joskus projektinvalintamalleilla on muita rajoitteita. Oletetaan esimerkiksi, että jos valitsemme projektin 3, meidän on valittava myös projekti 4. Koska nykyinen optimaalinen ratkaisumme valitsee Projekti 3:n, mutta ei Projektia 4, tiedämme, että nykyinen ratkaisumme ei voi pysyä optimaalisena. Voit ratkaista tämän ongelman lisäämällä rajoituksen, jonka mukaan Projektin 3 binaarinen muuttujasolu on pienempi tai yhtä suuri kuin Projektin 4 binaarinen muuttujasolu.
Tämä esimerkki on tiedoston Jos 3 niin 4 -laskentataulukossa Capbudget.xlsx, joka on esitetty kuvassa 30-4. Solu L9 viittaa projektiin 3 liittyvään binaariarvoon ja solu L12 projektiin 4 liittyvään binaariarvoon. Kun lisäämme rajoitteen L9<=L12, jos valitsemme projektin 3, L9 on yhtä suuri kuin 1 ja rajoitteemme pakottaa L12:n (projektin 4 binaariluvun) yhtä suureksi kuin 1. Rajoituksen on myös jätettävä Projekti 4:n muuttuvan solun binaariarvo rajoittamattomaksi, jos projektia 3 ei valita. Jos emme valitse projektia 3, L9 on 0 ja rajoitteemme sallii projektin 4 binaarin olla yhtä suuri kuin 0 tai 1, mitä haluamme. Uusi optimaalinen ratkaisu on esitetty kuvassa 30-4.
Uusi optimaalinen ratkaisu lasketaan, jos Projekti 3 valitseminen tarkoittaa, että meidän on valittava myös Projekti 4. Oletetaan nyt, että voimme toteuttaa vain neljä projektia projekteista 1–10. (Katso Enintään 4/P1–P10 -laskentataulukkoa, joka on esitetty kuvassa 30-5.) Solussa L8 lasketaan projekteihin 1–10 liittyvien binaariarvojen summa kaavalla SUMMA(A6:A15). Lisäämme sitten rajoitteen L8<=L10, joka varmistaa, että enintään neljä ensimmäistä kymmentä projektia valitaan. Uusi optimaalinen ratkaisu on esitetty kuvassa 30-5. NNA on pudonnut 9,014 miljardiin dollariin.
Binaari- ja kokonaislukuohjelmointiongelmien ratkaiseminen
Lineaariset ratkaisinmallit, joissa joidenkin tai kaikkien muuttuvien solujen on oltava binaari- tai kokonaislukuja, ovat yleensä vaikeampia ratkaista kuin lineaariset mallit, joissa kaikkien muuttuvien solujen sallitaan olla murtolukuja. Tästä syystä olemme usein tyytyväisiä lähes optimaaliseen ratkaisuun binaari- tai kokonaislukuohjelmointiongelmaan. Jos Ratkaisin-malli toimii pitkään, voit harkita toleranssiasetuksen muuttamista Ratkaisimen asetukset -valintaikkunassa. (Katso kuva 30-6.) Esimerkiksi toleranssiasetus 0,5 % tarkoittaa, että Ratkaisin pysähtyy, kun se löytää toteuttamiskelpoisen ratkaisun, joka on enintään 0,5 prosenttia teoreettisesta optimaalisesta kohdesolun arvosta (teoreettinen optimaalinen kohdesolun arvo on optimaalinen tavoitearvo, joka saavutetaan, kun binaari- ja kokonaislukurajoitukset jätetään pois). Usein joudumme valitsemaan, löydämmekö vastauksen 10 prosentin sisällä optimaalisesta 10 minuutissa vai löydämmekö optimaalisen ratkaisun kahdessa viikossa tietokoneajassa! Toleranssin oletusarvo on 0,05 %, mikä tarkoittaa, että Ratkaisin pysähtyy, kun se löytää kohdesolun arvon, joka on enintään 0,05 prosenttia teoreettisesta optimaalisesta kohdesolun arvosta.
Ongelmat
- Yrityksellä on yhdeksän harkinnassa olevaa hanketta. Kunkin hankkeen nettonykyarvo ja kunkin projektin tarvitsema pääoma seuraavan kahden vuoden aikana esitetään seuraavassa taulukossa. (Kaikki luvut ovat miljoonia.) Esimerkiksi projekti 1 lisää nettonykyarvoa 14 miljoonalla dollarilla ja vaatii 12 miljoonan dollarin menot vuonna 1 ja 3 miljoonan dollarin menot vuonna 2. Vuoden 1 aikana 50 miljoonaa dollaria pääomaa on käytettävissä projekteihin ja 20 miljoonaa dollaria vuoden 2 aikana.
| NNA | Vuoden 1 menot | Vuoden 2 menot | |
|---|---|---|---|
| Projekti 1 | 14 | 12 | 3 |
| Projekti 2 | 17 | 54 | 7 |
| Projekti 3 | 17 | 6 | 6 |
| Projekti 4 | 15 | 6 | 2 |
| Projekti 5 | 40 | 30 | 35 |
| Projekti 6 | 12 | 6 | 6 |
| Projekti 7 | 14 | 48 | 4 |
| Projekti 8 | 10 | 36 | 3 |
| Projekti 9 | 12 | 18 | 3 |
- Jos emme voi toteuttaa murto-osaa projektista, mutta meidän on toteutettava joko koko projekti tai ei mitään projektia, miten voimme maksimoida nettonykyarvon?
- Oletetaan, että jos projekti 4 on käynnissä, projekti 5 on toteutettava. Miten voimme maksimoida nettonykyarvon?
Kustantamo yrittää määrittää, mitkä 36 kirjasta sen pitäisi julkaista tänä vuonna. Tiedostossa on Pressdata.xlsx seuraavat tiedot kustakin kirjasta:
- Arvioidut tuotot ja kehityskustannukset (tuhansina euroina)
- Kunkin kirjan sivut
- Onko kirja suunnattu ohjelmistokehittäjille (merkitty 1:llä sarakkeessa E)
Kustantamo voi julkaista tänä vuonna jopa 8500-sivuisia kirjoja, ja sen on julkaistava vähintään neljä ohjelmistokehittäjille suunnattua kirjaa. Miten yritys voi maksimoida voittonsa?
Artikkelin sisältö
Tämä artikkeli on mukautettu Wayne L. Winstonin kirjoittamasta Microsoft Office Excel 2007 Data Analysis and Business Modeling -kirjasta .
Tämä luokkahuonetyylinen kirja on kehitetty Wayne Winstonin, tunnetun tilastotieteilijän ja liiketalouden professorin, joka on erikoistunut Excelin luoviin ja käytännöllisiin sovelluksiin, esityssarjasta.