Oplosser gebruiken voor kapitaalbudgettering

De meeste bedrijven willen projecten uitvoeren die de grootste netto huidige waarde (NHW) bijdragen, afhankelijk van beperkte middelen (meestal kapitaal en arbeid). Stel dat een softwareontwikkelingsbedrijf probeert te bepalen welke van de 20 softwareprojecten het moet uitvoeren. De NHW (in miljoenen dollars) die door elk project zijn bijgedragen, evenals het kapitaal (in miljoenen dollars) en het aantal programmeurs dat nodig is in elk van de volgende drie jaar, wordt vermeld op het werkblad Basismodel in het bestand Capbudget.xlsx, dat wordt weergegeven in afbeelding 30-1 op de volgende pagina. Project 2 levert bijvoorbeeld $ 908 miljoen op. Hiervoor is $ 151 miljoen vereist tijdens jaar 1, $ 269 miljoen in jaar 2 en $ 248 miljoen tijdens jaar 3. Project 2 vereist 139 programmeurs in jaar 1, 86 programmeurs in jaar 2 en 83 programmeurs in jaar 3. Cellen E4:G4 tonen het kapitaal (in miljoenen dollars) dat beschikbaar is gedurende elk van de drie jaar, en de cellen H4:J4 geven aan hoeveel programmeurs er beschikbaar zijn. In jaar 1 zijn bijvoorbeeld maximaal $ 2,5 miljard aan kapitaal en 900 programmeurs beschikbaar.

Het bedrijf moet beslissen of elk project moet worden uitgevoerd. We gaan ervan uit dat we niet een fractie van een softwareproject kunnen uitvoeren; Als we bijvoorbeeld 0,5 van de benodigde resources toewijzen, hebben we een nonworking-programma dat ons $ 0 omzet zou opleveren!

De truc bij het modelleren van situaties waarin u iets wel of niet doet, is het gebruik van binaire veranderende cellen. Een binaire wijzigingscel is altijd gelijk aan 0 of 1. Wanneer een binaire veranderende cel die overeenkomt met een project gelijk is aan 1, doen we het project. Als een binaire veranderende cel die overeenkomt met een project gelijk is aan 0, doen we het project niet. U stelt Oplosser in om een bereik van binaire cellen te gebruiken door een beperking toe te voegen. Selecteer de cellen die u wilt gebruiken en kies vervolgens Bin in de lijst in het dialoogvenster Beperking toevoegen.

Met deze achtergrond zijn we klaar om het probleem met de selectie van softwareproject op te lossen. Zoals altijd met een Oplosser-model beginnen we met het identificeren van onze doelcel, de veranderende cellen en de beperkingen.

• Doelcel. We maximaliseren de NHW die door geselecteerde projecten wordt gegenereerd.

• Cellen wijzigen. We zoeken naar een binaire cel van 0 of 1 voor elk project. Ik heb deze cellen in het bereik A6:A25 (en het bereik doit genoemd). Een 1 in cel A6 geeft bijvoorbeeld aan dat we Project 1 uitvoeren; een 0 in cel C6 geeft aan dat we project 1 niet uitvoeren.

• Beperkingen. We moeten ervoor zorgen dat voor elk jaar t (t=1, 2, 3), jaar t gebruikt kapitaal kleiner is dan of gelijk is aan jaar t beschikbaar kapitaal, en jaar t gebruikt arbeid kleiner is dan of gelijk is aan Jaar t beschikbare arbeid.

Zoals u ziet, moet ons werkblad voor elke selectie van projecten de NHW, het jaarlijks gebruikte kapitaal en de programmeurs die elk jaar worden gebruikt, berekenen. In cel B2 gebruik ik de formule SOMPRODUCT(doit;NHW) om de totale NHW te berekenen die door geselecteerde projecten wordt gegenereerd. (De bereiknaam NHW verwijst naar het bereik C6:C25.) Voor elk project met een 1 in kolom A haalt deze formule de NHW van het project op en voor elk project met een 0 in kolom A wordt met deze formule niet de NHW van het project opgehaald. Daarom kunnen we de NHW van alle projecten berekenen en onze doelcel is lineair omdat deze wordt berekend door termen op te sommen die de vorm volgen (cel wijzigen)*(constant). Op een vergelijkbare manier bereken ik het kapitaal dat elk jaar wordt gebruikt en de arbeid die elk jaar wordt gebruikt door de formule SOMPRODUCT(doit,E6:E25) te kopiëren van E2 naar F2:J2.

Ik vul nu het dialoogvenster Parameters voor oplosser in, zoals wordt weergegeven in afbeelding 30-2.

Ons doel is om de NHW van geselecteerde projecten (cel B2) te maximaliseren. Onze veranderende cellen (het bereik met de naam doit) zijn de binaire veranderende cellen voor elk project. De beperking E2:J2<=E4:J4 zorgt ervoor dat het gebruikte kapitaal en de gebruikte arbeid gedurende elk jaar kleiner zijn dan of gelijk zijn aan het beschikbare kapitaal en de beschikbare arbeid. Als u de beperking wilt toevoegen waardoor de veranderende cellen binair worden, klik ik op Toevoegen in het dialoogvenster Parameters van Oplosser en selecteer ik vervolgens Bin in de lijst in het midden van het dialoogvenster. Het dialoogvenster Beperking toevoegen moet worden weergegeven zoals weergegeven in afbeelding 30-3.

Ons model is lineair omdat de doelcel wordt berekend als de som van termen met de vorm (cel wijzigen)*(constant) en omdat de resourcegebruiksbeperkingen worden berekend door de som van (veranderende cellen)*(constanten) te vergelijken met een constante.

Klik in het dialoogvenster Parameters voor oplosser op Oplossen. De resultaten worden eerder weergegeven in afbeelding 30-1. Het bedrijf kan een maximaal NHW van $ 9.293 miljoen ($ 9.293 miljard) verkrijgen door projecten 2, 3, 6-10, 14-16, 19 en 20 te kiezen.

Soms hebben projectselectiemodellen andere beperkingen. Stel dat als we Project 3 selecteren, we ook Project 4 moeten selecteren. Omdat onze huidige optimale oplossing Project 3 selecteert, maar niet Project 4, weten we dat onze huidige oplossing niet optimaal kan blijven. U kunt dit probleem oplossen door de beperking toe te voegen dat de binaire veranderende cel voor Project 3 kleiner is dan of gelijk is aan de binaire wijzigingscel voor Project 4.

U vindt dit voorbeeld op het werkblad If 3 then 4 in het bestand Capbudget.xlsx, dat wordt weergegeven in afbeelding 30-4. Cel L9 verwijst naar de binaire waarde met betrekking tot Project 3 en cel L12 naar de binaire waarde met betrekking tot Project 4. Door de beperking L9 toe te voegen<=L12, als we Project 3 kiezen, is L9 gelijk aan 1 en dwingt onze beperking L12 (het binaire project 4) tot 1. Onze beperking moet ook de binaire waarde in de veranderende cel van Project 4 onbeperkt laten als we Project 3 niet selecteren. Als we Project 3 niet selecteren, is L9 gelijk aan 0 en staat onze beperking toe dat het binaire bestand van Project 4 gelijk is aan 0 of 1, wat we willen. De nieuwe optimale oplossing wordt weergegeven in afbeelding 30-4.

Er wordt een nieuwe optimale oplossing berekend als het selecteren van Project 3 betekent dat we ook Project 4 moeten selecteren. Stel nu dat we slechts vier projecten kunnen uitvoeren tussen projecten 1 tot en met 10. (Zie het werkblad Maximaal 4 van P1-P10 , weergegeven in afbeelding 30-5.) In cel L8 berekenen we de som van de binaire waarden die zijn gekoppeld aan projecten 1 tot en met 10 met de formule SOM(A6:A15). Vervolgens voegen we de beperking L8 toe<=L10, zodat maximaal 4 van de eerste 10 projecten worden geselecteerd. De nieuwe optimale oplossing wordt weergegeven in afbeelding 30-5. De NHW is gedaald tot $ 9,014 miljard.

Lineaire oplossermodellen waarbij sommige of alle veranderende cellen binair of geheel getal moeten zijn, zijn meestal moeilijker op te lossen dan lineaire modellen waarin alle veranderende cellen breuken mogen zijn. Daarom zijn we vaak tevreden met een bijna optimale oplossing voor een binair of geheel getal programmeerprobleem. Als uw oplossermodel lang wordt uitgevoerd, kunt u overwegen om de instelling Tolerantie aan te passen in het dialoogvenster Opties voor oplosser. (Zie afbeelding 30-6.) Een tolerantieinstelling van 0,5% betekent bijvoorbeeld dat Oplosser stopt wanneer een haalbare oplossing wordt gevonden die binnen 0,5 procent van de theoretische optimale doelcelwaarde ligt (de theoretische optimale doelcelwaarde is de optimale doelwaarde die wordt gevonden wanneer de binaire en gehele getalbeperkingen worden weggelaten). Vaak staan we voor een keuze tussen het vinden van een antwoord binnen 10 procent van optimaal in 10 minuten of het vinden van een optimale oplossing in twee weken computertijd! De standaardwaarde Tolerantie is 0,05%, wat betekent dat Oplosser stopt wanneer een doelcelwaarde wordt gevonden binnen 0,05 procent van de theoretische optimale doelcelwaarde.

1. Een bedrijf heeft negen projecten in behandeling. De NHW die door elk project wordt toegevoegd en het kapitaal dat de komende twee jaar voor elk project is vereist, worden weergegeven in de volgende tabel. (Alle getallen zijn in miljoenen.) Project 1 voegt bijvoorbeeld $ 14 miljoen aan NHW toe en vereist uitgaven van $ 12 miljoen tijdens jaar 1 en $ 3 miljoen in jaar 2. In jaar 1 is $ 50 miljoen aan kapitaal beschikbaar voor projecten en $ 20 miljoen beschikbaar in jaar 2.

	NHW	Uitgaven van jaar 1	Uitgaven van jaar 2
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

• Hoe kunnen we NHW maximaliseren als we geen deel van een project kunnen uitvoeren, maar een geheel of geen project moeten uitvoeren?

• Stel dat als Project 4 wordt uitgevoerd, Project 5 moet worden uitgevoerd. Hoe kunnen we NHW maximaliseren?

• Een uitgeverij probeert te bepalen welke van de 36 boeken het dit jaar moet publiceren. Het bestand Pressdata.xlsx bevat de volgende informatie over elk boek:

  • Verwachte omzet en ontwikkelingskosten (in duizenden dollars)

  • Pagina's in elk boek

  • Of het boek is gericht op een publiek van softwareontwikkelaars (aangegeven door een 1 in kolom E)

    Een uitgeverij kan dit jaar boeken publiceren met in totaal 8500 pagina's en moet ten minste vier boeken publiceren die zijn gericht op softwareontwikkelaars. Hoe kan het bedrijf de winst maximaliseren?

Dit artikel is aangepast van Microsoft Office Excel 2007 Data Analysis and Business Modeling door Wayne L. Winston.

Dit lesboek is ontwikkeld op basis van een reeks presentaties van Wayne Winston, een bekende statisticus en zakelijk professor die gespecialiseerd is in creatieve, praktische toepassingen van Excel.