Ratkaisimen käyttäminen pääoman budjetointiin

Useimmat yritykset haluavat toteuttaa projekteja, joilla on suurin nettonykyarvo (NPV), johon sovelletaan rajallisia resursseja (yleensä pääomaa ja työvoimaa). Oletetaan, että ohjelmistokehitysyritys yrittää määrittää, mihin 20 ohjelmistoprojektiin sen tulisi ryhtyä. Kunkin hankkeen antama nettonykyarvo (miljoonina dollareina) sekä pääoma (miljoonina dollareina) ja ohjelmoijien määrä kolmen seuraavan vuoden aikana annetaan perusmallitaulukossa tiedostossa Capbudget.xlsx, joka näkyy seuraavan sivun kuvassa 30-1. Esimerkiksi Project 2 tuottaa 908 miljoonaa dollaria. Se vaatii 151 miljoonaa dollaria vuoden 1 aikana, 269 miljoonaa dollaria vuonna 2 ja 248 miljoonaa dollaria vuonna 3. Hankkeeseen 2 tarvitaan 139 ohjelmoijaa vuoden 1 aikana, 86 ohjelmoijaa vuoden 2 aikana ja 83 ohjelmoijaa vuoden 3 aikana. Solut E4:G4 näyttävät kunkin kolmen vuoden aikana käytettävissä olevan pääoman (miljoonina dollareina) ja solut H4:J4 osoittavat, kuinka monta ohjelmoijaa on käytettävissä. Esimerkiksi vuoden 1 aikana käytettävissä on jopa 2,5 miljardia dollaria pääomaa ja 900 ohjelmoijaa.

Yrityksen on päätettävä, toteuttaako se jokaisen hankkeen. Oletetaan, että emme voi suorittaa murto-osaa ohjelmistoprojektista; Jos varaamme esimerkiksi 0,5 tarvittavaa resurssia, meillä olisi vapaa-ajan ohjelma, joka toisi meille 0 dollarin tulot!

Mallinnustilanteissa, joissa joko teet tai et tee jotain, on käyttää binaariluvun muuttuvia soluja. Binaarinen muuttuva solu on aina 0 tai 1. Kun projektia vastaava binaarinen solu on yhtä suuri kuin 1, projekti tehdään. Jos projektia vastaava binaarinen muuttuva solu on 0, projektia ei tehdä. Määrität Ratkaisimen käyttämään binaaristen solujen aluetta lisäämällä rajoitteen. Valitse muutettavat solut ja valitse sitten Lisää rajoitus -valintaikkunan luettelosta Bin.

Tämän taustan ansiosta olemme valmiit ratkaisemaan ohjelmistoprojektin valintaongelman. Kuten aina Ratkaisin-mallissa, aloitamme tunnistamalla kohdesolumme, muuttuvat solut ja rajoitukset.

• Kohdesolu. Maksimoimme valittujen projektien luoman nettonykyarvon.

• Solujen muuttaminen. Kullekin projektille etsitään 0- tai 1-binaarinen muuttuva solu. Olen paikantanut nämä solut alueelta A6:A25 (ja nimennyt alueen pisteeksi). Esimerkiksi solussa A6 oleva 1 ilmaisee, että projekti 1 on käytössä. Solussa C6 oleva 0 ilmaisee, että projektia 1 ei suoriteta.

• Rajoitukset. Meidän on varmistettava, että kunkin vuoden t (t=1, 2, 3), käytetyn pääoman vuosi t on pienempi tai yhtä suuri kuin käytettävissä oleva Vuosi t -pääoma ja käytetyn työvoiman vuosi on pienempi tai yhtä suuri kuin käytettävissä oleva vuosi t työvoima.

Kuten näette, laskentataulukkomme on laskettava kaikkien projektien valinnat nettonykyarvosta, vuosittain käytettävästä pääomasta ja vuosittain käytettävistä ohjelmoijista. Solussa B2 käytän kaavaa TULOJEN.SUMMA(doit,NNA) valittujen projektien luoman nettonykyarvon laskemiseen. (Alueen nimi NNA viittaa alueeseen C6:C25.) Jokaisessa projektissa, jonka sarakkeessa A on 1, tämä kaava poimii projektin nettonykyarvon, ja jokaista projektia, jonka sarakkeessa A on 0, tämä kaava ei poimi projektin nettonykyarvoa. Siksi pystymme laskemaan kaikkien projektien nettonykyarvon, ja kohdesolumme on lineaarinen, koska se lasketaan laskemalla yhteen lomakkeen perässä olevat termit (solun muuttaminen)*(vakio). Samalla tavalla lasken vuosittain käytetyn pääoman ja vuosittain käytetyn työvoiman kopioimalla E2:sta F2:J2:een kaavan TULOJEN.SUMMA(doit,E6:E25).

Täytän nyt Ratkaisimen parametrit -valintaikkunan kuvassa 30–2 esitetyllä tavalla.

Tavoitteenamme on maksimoida valittujen projektien nettonykyarvo (solu B2). Muuttuvat solut ( pistealue) ovat kunkin projektin binaarisia muuttuvia soluja. Rajoitus E2:J2<=E4:J4 varmistaa, että kunkin vuoden aikana pääoma ja työvoima ovat pienempiä tai yhtä suuria kuin käytettävissä oleva pääoma ja työvoima. Jos haluat lisätä muuttuvan solujen binaarisen rajoituksen, valitsen Ratkaisimen parametrit -valintaikkunassa Lisää ja valitsen sitten Valintaikkunan keskellä olevasta luettelosta Bin. Lisää rajoite -valintaikkunan pitäisi näkyä kuvassa 30–3 esitetyllä tavalla.

Mallimme on lineaarinen, koska kohdesolu lasketaan lomakkeen (muuttuvan solun) *(vakio) sisältävien termien summana ja koska resurssien käyttörajoitukset lasketaan vertaamalla (solujen muuttaminen)*(vakiot) summaa vakioon.

Kun Ratkaisimen parametrit -valintaikkuna on täytetty, valitse Ratkaise, niin tulokset näkyvät aiemmin kuvassa 30-1. Yritys voi saada NNA-enimmäisarvon 9 293 miljoonaa dollaria (9,293 miljardia dollaria) valitsemalla Projektit 2, 3, 6–10, 14–16, 19 ja 20.

Joskus projektinvalintamalleilla on muita rajoituksia. Oletetaan esimerkiksi, että jos valitsemme Projektin 3, meidän on valittava myös Projekti 4. Koska nykyinen optimaalinen ratkaisumme valitsee Project 3:n, mutta ei Project 4:ttä, tiedämme, että nykyinen ratkaisumme ei voi pysyä optimaalisena. Voit ratkaista tämän ongelman lisäämällä rajoituksen, jonka mukaan Project 3:n binaaria muuttava solu on pienempi tai yhtä suuri kuin Project 4:n binaaria muuttava solu.

Tämä esimerkki on Tiedosto-Capbudget.xlsx Jos 3 ja sitten 4 -laskentataulukossa, joka näkyy kuvassa 30-4. Solu L9 viittaa projektiin 3 liittyvään binaariarvoon ja soluun L12 projektiin 4 liittyvään binaariarvoon. Lisäämällä rajoituksen L9<=L12, jos valitsemme Projektin 3, L9 on yhtä suuri kuin 1 ja rajoitus pakottaa L12:n (Project 4 -binaariluvun) arvoksi 1. Rajoituksemme on myös jätettävä binaariarvo Project 4:n muuttuvassa solussa rajoittamattomaksi, jos emme valitse Projekti 3:a. Jos emme valitse Projekti 3:a, L9 on yhtä suuri kuin 0 ja rajoitus sallii Project 4:n binaariluvun olevan 0 tai 1, mitä me haluamme. Uusi optimaalinen ratkaisu näkyy kuvassa 30-4.

Uusi optimaalinen ratkaisu lasketaan, jos Project 3:n valitseminen tarkoittaa, että meidän on valittava myös Projekti 4. Oletetaan, että projektien 1–10 välillä voi tehdä vain neljä projektia. (Katso enintään 4/P1–P10-laskentataulukko kuvassa 30–5.) Laskemme solussa L8 projektiin 1–10 liittyvien binaariarvojen summan kaavalla SUMMA(A6:A15). Lisäämme sitten rajoituksen L8<=L10, joka varmistaa, että enintään neljä kymmenestä ensimmäisestä projektista on valittuna. Uusi optimaalinen ratkaisu näkyy kuvassa 30–5. NNA on pudonnut 9,014 miljardiin dollariin.

Lineaariset Ratkaisin-mallit, joissa joidenkin tai kaikkien muuttuvien solujen on oltava binaari- tai kokonaislukuja, ovat yleensä vaikeampia ratkaista kuin lineaariset mallit, joissa kaikki muuttuvat solut saavat olla murtolukuja. Tästä syystä olemme usein tyytyväisiä lähes optimaaliseen ratkaisuun binaari- tai kokonaislukuohjelmointiongelmaan. Jos Ratkaisin-malli toimii pitkään, voit harkita Toleranssi-asetuksen säätämistä Ratkaisimen asetukset -valintaikkunassa. (Katso kuva 30–6.) Esimerkiksi Toleranssi-asetus 0,5 % tarkoittaa, että Ratkaisin lopettaa ensimmäisen kerran, kun se löytää toteuttamiskelpoisen ratkaisun, joka on 0,5 prosentin etäisyydellä teoreettisesta optimaalisen kohdesolun arvosta (teoreettinen optimaalinen tavoitesolun arvo on optimaalinen tavoitearvo, joka löytyy, kun binaari- ja kokonaislukurajoitukset jätetään pois). Usein meillä on mahdollisuus löytää vastaus 10 prosentin kuluessa optimaalisesta 10 minuutissa tai löytää optimaalinen ratkaisu kahden viikon kuluttua tietokoneajasta! Oletusarvo Toleranssi-arvo on 0,05 %, mikä tarkoittaa, että Ratkaisin pysähtyy, kun se löytää Kohde-solun arvon 0,05 prosentin säteellä teoreettisesta optimaalisen kohdesolun arvosta.

1. Yrityksellä on yhdeksän projektia harkinnassa. Kunkin projektin lisäämä nettonykyarvo ja kunkin projektin vaatima pääoma seuraavan kahden vuoden aikana esitetään seuraavassa taulukossa. (Kaikki luvut ovat miljoonia.) Esimerkiksi Project 1 lisää 14 miljoonaa dollaria nettonykyarvona ja vaatii 12 miljoonan dollarin menot vuoden 1 aikana ja 3 miljoonaa dollaria vuoden 2 aikana. Vuoden 1 aikana projekteihin on saatavilla 50 miljoonaa dollaria pääomaa ja 20 miljoonaa dollaria vuoden 2 aikana.

	NNA	Vuoden 1 menot	Vuoden 2 menot
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

• Jos emme voi suorittaa murto-osaa projektista, mutta meidän on suoritettava joko kaikki projektit tai ei mitään, miten voimme maksimoida nettonykyarvon?

• Oletetaan, että jos projekti 4 toteutetaan, projekti 5 on suoritettava. Miten nettonykyarvo voidaan maksimoida?

• Kustannusyhtiö yrittää selvittää, mitkä 36 kirjasta sen pitäisi julkaista tänä vuonna. Tiedoston Pressdata.xlsx antaa seuraavat tiedot kustakin kirjasta:

  • Ennustetut liikevaihto- ja kehityskustannukset (tuhansina dollareina)

  • Kunkin kirjan sivut

  • Onko kirja suunnattu ohjelmistokehittäjien yleisölle (merkitty sarakkeen E 1 avulla)

    Kustannusyhtiö voi julkaista tänä vuonna jopa 8 500 sivun kirjoja, ja sen on julkaistava vähintään neljä ohjelmistokehittäjille suunnattua kirjaa. Miten yritys voi maksimoida voittonsa?

Tämä artikkeli on mukautettu Wayne L. Winstonin Microsoft Office Excel 2007 Data Analysis and Business Modeling -artikkelista.

Tämä luokkahuonetyylinen kirja on kehitetty Wayne Winstonin, tunnetun tilastotieteilijän ja liike-elämän professorin, esityksistä, jotka ovat erikoistuneet Excelin luoviin, käytännöllisiin sovelluksiin.