Korištenje rješavača za budžetiranje kapitala

Kako tvrtka za Rješavatelj može koristiti da bi ustanovio koje projekte treba poduzeti?

Svake godine tvrtka kao što je Eli Lilly mora odrediti koji će se lijek razviti; tvrtka kao što je Microsoft, koje softverske programe razvijati; kompanija kao što je Proctor & Gamble, koji novi potrošač proizvodi za razvoj. Značajka Rješavatelj u programu Excel može pomoći tvrtki da donosi te odluke.

Većina korporacija želi poduzeti projekte koji pridonose najvećoj neto sadašnjoj vrijednosti (NPV), podložno ograničenim resursima (obično kapital i rad). Recimo da tvrtka za razvoj softvera pokušava utvrditi koji od 20 softverskih projekata treba poduzeti. NPV (u milijunima dolara) doprinijeli su svaki projekt, kao i kapital (u milijunima dolara), a broj programerima potrebnih tijekom svake od sljedećih triju godina daje se na radni list osnovnog modela u datoteci capbudget. xlsx, koji se prikazuje na slici 30-1 na sljedećoj stranici. Na primjer, Project 2 donosi $908.000.000. Za to je potrebno $151.000.000 tijekom godine 1, $269.000.000 tijekom godine 2 i $248.000.000 tijekom godine 3. Project 2 zahtijeva 139 programere tijekom godine 1, 86 programerima tijekom godine 2 i 83 programerima tijekom godine 3. Ćelije E4: G4 prikazuje kapital (u milijunima dolarima) dostupne tijekom svake od tri godine, a ćelije,, J4, ukazuju na to koliko je programerima dostupno. Primjerice, tijekom godine 1 do $2.500.000.000 u kapitalu i 900 programeri su dostupni.

Tvrtka mora odlučiti treba li poduzeti svaki projekt. Pretpostavimo da ne možemo poduzeti djelić softverskog projekta; Ako dodjeljujemo 0,5 potrebnih resursa, na primjer, mogli bismo imati neradni program koji će nam donijeti $0 prihod!

Izigrati u modeliranju situacijama u kojima ili ne učiniti nešto je koristiti binarni mijenja ćelije. Binarni promjenjiva ćelija uvijek je jednaka 0 ili 1. Kada binarna promjena ćelije koja odgovara projektu jednako 1, mi radimo projekt. Ako binarna promjena ćelije koja odgovara projektu jednaka 0, mi ne radimo projekt. Rješavatelj možete postaviti da koristi raspon binarnih promjena ćelija dodavanjem ograničenja – odaberite promjene ćelije koje želite koristiti, a zatim na popisu u dijaloškom okviru Dodavanje ograničenja odaberite regal.

Book image

S ovom pozadinom spremni smo riješiti problem s odabirom softverskih projekata. Kao i uvijek u modelu rješavača, počinjemo s identifikiranjem ciljne ćelije, promjenom ćelija i ograničenjima.

  • Odredišna ćelija. Maksimizirali smo NPV generirane po odabranim projektima.

  • Promjena ćelija.Potražite 0 ili 1 binarni promjenjive ćelije za svaki projekt. Locirala sam ove ćelije u rasponu A6: A25 (i pod nazivom Range doit). Na primjer, 1 u ćeliji a6 upućuje na to da poduzmemo projekt 1; 0 u ćeliji C6 upućuje na to da ne poduzimamo projekt 1.

  • Ograničenja.Moramo osigurati da se za svake godine t (t = 1; 2; 3), godina t -kapitala koja se koristi za godinu dana, broj kapitala igodina t -a koristi manje od ili jednako godišnje.

Kao što možete vidjeti, naš radni list mora izračunati bilo koji odabir projekata NPV, kapital koji se koristi godišnje i programerima koji se koriste svake godine. U ćeliji B2 koristim formulu SUMPRODUCT (doit, NPV) da biste izračunali ukupni NPV generirani od strane odabranih projekata. (Naziv raspona NPV odnosi se na raspon C6: C25.) Za svaki projekt s 1 u stupcu A, ova formula preuzima NPV projekta, a za svaki projekt s 0 u stupcu A, ova formula ne podiže NPV projekta. Stoga smo u mogućnosti izračunati NPV svih projekata, a naša je ciljna ćelija linearna jer je izračunat prema uvjetima zbrajanja koji slijede obrazac (promjena ćelije) * (konstanta). Na sličan način izračunat ću kapital koji se koristi svake godine, a rad svake godine pomoću kopiranja s programa E2 na F2: J2 formula SUMPRODUCT (doit, E6: E25).

Sada ispunjavam dijaloški okvir Parametri rješavača kao što je prikazano na slici 30-2.

Book image

Naš je cilj maksimizirati NPV odabranih projekata (ćelija B2). Naše promjenjive ćelije (raspon pod nazivom doit) binarni su promjenjive ćelije za svaki projekt. Ograničenje E2: J2<= E4: J4 jamči da će tijekom svake godine kapital i rad koji se koriste biti manji ili jednaki kapitalu i radu na raspolaganju. Da biste dodali ograničenje koje je izmjenjive ćelije čine binarni, kliknut ću u dijaloškom okviru Parametri rješavača, a zatim na popisu u sredini dijaloškog okvira odaberite regal. Dijaloški okvir Dodavanje ograničenja trebao bi se prikazati kao što je prikazano na slici 30-3.

Book image

Naš je model linearan jer je ciljna ćelija izračunata kao zbroj pojmova koji imaju obrazac (promjena ćelije) * (konstanta) i zbog toga što se ograničenja korištenja resursa izračunavaju uspoređujući zbroj (promjena ćelija) * (konstante) do konstante.

Ako ste ispunili dijaloški okvir Parametri rješavača, kliknite riješi, a rezultati su prethodno prikazani na slici 30-1. Tvrtka može nabaviti maksimalnu NPV od $9.293.000.000 ($9.293.000.000) odabirom projekata 2, 3, 6 – 10, 14 – 16, 19 i 20.

Ponekad modeli za odabir projekata imaju druga ograničenja. Pretpostavimo, primjerice, da ako odaberu Project 3, moramo odabrati i Project 4. Budući da naše trenutno optimalno rješenje odabire projekt 3, ali ne i Project 4, znamo da trenutno rješenje ne može ostati optimalno. Da biste riješili taj problem, jednostavno dodajte ograničenje da je binarni promjena ćelije za projekt 3 manja ili jednaka binarnoj promjeni ćelije za Project 4.

Ovaj primjer možete pronaći na radnom listu Ako 3, a zatim 4 u datoteci capbudget. xlsx, koji se prikazuje na slici 30-4. Cell L9 odnosi se na binarni vrijednost povezanu sa projektom 3 i ćelijom L12 na binarnu vrijednost povezanu sa projektom 4. Dodavanjem ograničenja L9<= L12, ako biramo Project 3, L9 je jednako 1, a naša ograničenja sile L12 (binarni projekt 4) na jednak 1. Naš ograničenje mora ostaviti i binarni vrijednost u promjenjivom ćeliji projekta 4 bez ograničenja ako ne Odaberimo projekt 3. Ako ne odaberu Project 3, L9 iznosi 0, a naša ograničenja omogućuje da projekt 4 binarni bude jednak 0 ili 1, što je ono što želimo. Novo optimalno rješenje prikazano je na slici 30-4.

Book image

Ako odaberete projekt 3, može se izračunati novo optimalno rješenje, pa moramo odabrati i Project 4. Sada pretpostavimo da možemo raditi samo četiri projekta između projekata od 1 do 10. (Pogledajte članak najviše 4 od servisa P1 – P10 , prikazan na slici 30-5.) U ćeliji L8 izračunali smo zbroj binarnih vrijednosti povezanih s projektima od 1 do 10 pomoću formule Sum (A6:, Zatim ćemo dodati ograničenje L8<= 10 funti, čime se jamči da će se, najviše, 4 od prvih 10 projekata odabrati. Novo optimalno rješenje prikazano je na slici 30-5. NPV je pao na $9.014.000.000.

Book image

Linearni modeli rješavača u kojima su neke ili sve promjenjive ćelije obavezne da budu binarni ili cijeli broj obično je teže riješiti nego linearni modeli u kojima su svim promjenjivim ćelijama dopuštene razlomke. Iz tog razloga često smo zadovoljni mogućnošću gotovo optimalnog rješenja za binarni ili cjelobrojni programiranje. Ako se model Rješavatelj već dugo izvršava, preporučujemo da prilagodite postavku tolerancije u dijaloškom okviru Mogućnosti rješavača. (Pogledajte sliku 30-6.) Ako, primjerice, postavka tolerancije na 0,5% znači da će Rješavatelj zaustaviti prvi put kada pronađe izvodljivo rješenje koje se nalazi unutar 0,5 posto teoretske optimalne vrijednosti ciljne ćelije (teoretska optimalna vrijednost ciljne ćelije predstavlja optimalnu ciljnu vrijednost koja se nalazi kada je binarna i cjelobrojna ograničenja su izostavljena). Često smo suočeni s izborom između pronalaženja odgovora u roku od 10% od optimalnog broja za 10 minuta ili pronalaženja optimalnog rješenja u dva tjedna računalnog vremena! Zadana vrijednost tolerancije jest 0,05%, što znači da se rješavatelj prestaje pri pronalasku vrijednosti ciljne ćelije u roku od 0,05 posto teoretske optimalne vrijednosti ciljne ćelije.

Book image

  1. 1. tvrtka ima devet projekata u obzir. U sljedećoj su tablici prikazani NPV koji je dodao svaki projekt i kapital koji je potreban za svaki projekt tijekom sljedeće dvije godine. (Svi brojevi su u milijunima.) Na primjer, Project 1 dodat će $14.000.000 u NPV i zahtijevati izdatke od $12.000.000 tijekom godine 1 i $3.000.000 tijekom 2. Tijekom godine 1, $50.000.000 u glavnom gradu dostupan je za projekte, a $20.000.000 je dostupna tijekom godine 2.

NPV (net present value, neto trenutna vrijednost)

Izdaci za godinu 1

Godina 2 Rashodi

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

Project 7

14

48

4

Projekt 8

10

36

3

Projekt 9

12

18

3

  • Ako ne možemo poduzeti ni djelić projekta, ali moramo poduzeti sve ili ništa od projekta, kako maksimizirati NPV?

  • Pretpostavimo da ako je projekt 4 poduzet, projekt 5 mora biti poduzet. Kako možemo povećati NPV?

  • Izdavaštvo tvrtka pokušava utvrditi koje od 36 knjiga treba objaviti ove godine. Podaci o presici. xlsx daju sljedeće informacije o svakoj knjizi:

    • Predviđeni prihodi i razvojni troškovi (u tisućama dolara)

    • Stranice u svakoj knjizi

    • Je li knjiga usmjerena na publiku programerima softvera (naznačeno od strane 1 u stupcu E)

      Tvrtka za objavljivanje može objavljivati knjige u ukupnom iznosu do 8500 stranica ove godine i mora objaviti najmanje četiri knjige usmjerene prema softverskim programerima. Kako tvrtka može maksimizirati profit?

Ovaj je članak prilagođen iz programa Microsoft Office Excel 2007 za analizu podataka i poslovno modeliranje Waynea L. Winstona.

Ova knjiga u stilu učionice razvijena je iz niza prezentacija Waynea Winstona, poznatog statističara i poslovnog profesora koji se specijalizirao za kreativne, praktične aplikacije programa Excel.

Napomena:  Ova je stranica strojno prevedena te može sadržavati gramatičke pogreške ili netočnosti. Naša je namjera da vam ovaj sadržaj bude koristan. Jesu li vam te informacije bile korisne? Kao referencu možete pogledati i članak na engleskom jeziku.​

Proširite svoje vještine korištenja sustava Office
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×