Hoe kan een bedrijf Oplosser gebruiken om te bepalen welke projecten moeten worden uitgevoerd?
Elk jaar moet een bedrijf als Eli Lilly bepalen welke medicijnen moeten worden ontwikkeld; een bedrijf als Microsoft, welke softwareprogramma's moeten worden ontwikkeld; een bedrijf als Proctor & Gamble, welke nieuwe consumentenproducten te ontwikkelen. De functie Oplosser in Excel kan een bedrijf helpen bij deze beslissingen.
Hoe kan een bedrijf Oplosser gebruiken om te bepalen welke projecten moeten worden uitgevoerd?
De meeste bedrijven willen projecten uitvoeren die de grootste netto contante waarde (NPV) bijdragen, afhankelijk van beperkte middelen (meestal kapitaal en arbeid). Stel dat een softwareontwikkelingsbedrijf probeert te bepalen welk van de 20 softwareprojecten het moet uitvoeren. De NHW (in miljoenen dollars) die door elk project wordt bijgedragen, evenals het kapitaal (in miljoenen dollars) en het aantal programmeurs dat nodig is gedurende elk van de volgende drie jaar wordt weergegeven op het werkblad van het basismodel in het bestand Capbudget.xlsx, dat wordt weergegeven in figuur 30-1 op de volgende pagina. Project 2 levert bijvoorbeeld $ 908 miljoen op. Het vereist $ 151 miljoen tijdens jaar 1, $ 269 miljoen tijdens jaar 2 en $ 248 miljoen tijdens jaar 3. Project 2 vereist 139 programmeurs tijdens jaar 1, 86 programmeurs tijdens jaar 2 en 83 programmeurs tijdens jaar 3. In de cellen E4:G4 wordt het beschikbare kapitaal (in miljoenen euro's) weergegeven gedurende elk van de drie jaren en in de cellen H4:J4 wordt aangegeven hoeveel programmeurs er beschikbaar zijn. Tijdens jaar 1 zijn er bijvoorbeeld tot $ 2.5 miljard aan kapitaal en 900 programmeurs beschikbaar.
Het bedrijf moet beslissen of het elk project moet uitvoeren. Laten we aannemen dat we geen fractie van een softwareproject kunnen uitvoeren; Als we bijvoorbeeld 0,5 van de benodigde resources toewijzen, hebben we een niet-werkend programma dat ons $ 0 omzet oplevert!
De truc bij het modelleren van situaties waarin je iets wel of niet doet, is om binaire cellen te wijzigen. Een binaire cel die verandert, is altijd gelijk aan 0 of 1. Wanneer een binaire veranderende cel die overeenkomt met een project gelijk is aan 1, voeren we het project uit. Als een binaire veranderende cel die overeenkomt met een project gelijk is aan 0, voeren we het project niet uit. U kunt Oplosser instellen op het gebruik van een bereik van binaire veranderende cellen door een beperking toe te voegen: selecteer de veranderende cellen die u wilt gebruiken en kies Bin in de lijst in het dialoogvenster Beperking toevoegen.
Met deze achtergrond zijn we klaar om het softwareproject selectieprobleem op te lossen. Zoals altijd bij een Oplosser-model beginnen we met het identificeren van de doelcel, de veranderende cellen en de beperkingen.
- Doelcel. We maximaliseren de NHW gegenereerd door geselecteerde projecten.
- Cellen wijzigen. We zoeken voor elk project naar een veranderende cel met 0 of 1 binair. Ik heb deze cellen gevonden 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), het gebruikte jaar t kapitaal kleiner is dan of gelijk is aan het beschikbare jaar t kapitaal, en het gebruikte jaar t arbeid kleiner is dan of gelijk is aan de beschikbare arbeid in het jaar t .
Zoals u kunt zien, 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 is gegenereerd. (De naam NHW verwijst naar het bereik C6:C25.) Voor elk project met een 1 in kolom A wordt met deze formule de NHW van het project gebruikt en voor elk project met een 0 in kolom A wordt met deze formule niet de NHW van het project overgenomen. Daarom kunnen we de NHW van alle projecten berekenen en onze doelcel is lineair omdat deze wordt berekend door termen op te tellen die volgen op de vorm (veranderende cel)*(constante). 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 van Oplosser in, zoals in afbeelding 30-2.
Ons doel is de NHW van geselecteerde projecten te maximaliseren (cel B2). Onze veranderende cellen (het bereik genaamd Doit) zijn de binaire veranderende cellen voor elk project. De beperking E2:J2<=E4:J4 zorgt ervoor dat gedurende elk jaar het gebruikte kapitaal en de gebruikte arbeid kleiner zijn dan of gelijk zijn aan het beschikbare kapitaal en de beschikbare arbeid. Om de beperking toe te voegen waardoor de veranderende cellen binair worden, klik ik op Toevoegen in het dialoogvenster Parameters van Oplosser en selecteer ik Bin in de lijst in het midden van het dialoogvenster. Het dialoogvenster Beperking toevoegen moet worden weergegeven zoals in afbeelding 30-3.
Ons model is lineair omdat de doelcel wordt berekend als de som van termen die de vorm hebben (veranderende cel)*(constante) en omdat de beperkingen voor het gebruik van hulpbronnen worden berekend door de som van (veranderende cellen)*(constanten) te vergelijken met een constante.
Als het dialoogvenster Parameters van Oplosser is ingevuld, klikt u op Oplossen en hebben we de resultaten die eerder in afbeelding 30-1 zijn weergegeven. Het bedrijf kan een maximale NHW van $ 9,293 miljoen ($ 9.293 miljard) behalen door te kiezen voor projecten 2, 3, 6-10, 14-16, 19 en 20.
Omgaan met andere beperkingen
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 lost dit probleem op door simpelweg de beperking toe te voegen dat de binaire veranderende cel voor Project 3 kleiner is dan of gelijk is aan de binaire veranderende cel voor Project 4.
U vindt dit voorbeeld in 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 van project 3 en cel L12 naar de binaire waarde van project 4. Als we de beperking L9<=L12 toevoegen en Project 3 kiezen, is L9 gelijk aan 1 en dwingt onze beperking L12 (het binaire systeem van Project 4) af op 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 onze beperking staat toe dat het binaire Project 4-bestand gelijk is aan 0 of 1, en dat is wat we willen. De nieuwe optimale oplossing wordt getoond in figuur 30-4.
Er wordt een nieuwe optimale oplossing berekend als Project 3 betekent dat we ook Project 4 moeten selecteren. Stel nu dat we slechts vier projecten van project 1 tot en met 10 kunnen uitvoeren. (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<=L10 toe, die ervoor zorgt dat maximaal 4 van de eerste 10 projecten worden geselecteerd. De nieuwe optimale oplossing wordt weergegeven in figuur 30-5. De NPV is gedaald tot $ 9.014 miljard.
Binaire en integer programmeerproblemen oplossen
Lineaire oplosser-modellen waarin sommige of alle veranderende cellen binair of geheel getal moeten hebben, zijn meestal moeilijker op te lossen dan lineaire modellen waarin alle veranderende cellen breuken mogen hebben. Om deze reden zijn we vaak tevreden met een bijna optimale oplossing voor een binair of integer programmeerprobleem. Als het Oplosser-model lange tijd wordt uitgevoerd, kunt u overwegen de instelling Tolerantie in het dialoogvenster Oplosser-opties aan te passen. (Zie figuur 30-6.) Een tolerantieinstelling van 0,5% betekent bijvoorbeeld dat Oplosser stopt wanneer er een eerste haalbare oplossing wordt gevonden die zich binnen 0,5% van de theoretisch optimale doelcelwaarde bevindt (de theoretisch optimale doelcelwaarde is de optimale doelwaarde die wordt gevonden als de beperkingen voor binaire getallen en gehele getallen worden weggelaten). Vaak staan we voor de 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 standaardtolerantiewaarde is 0,05%, wat betekent dat Oplosser stopt wanneer er een waarde voor een doelcel wordt gevonden die zich binnen 0,05 procent van de theoretisch optimale waarde van de doelcel bevindt.
Problemen
- Een bedrijf heeft negen projecten in overweging. De NHW die voor elk project wordt toegevoegd en het kapitaal dat voor elk project nodig is voor de komende twee jaar worden weergegeven in de volgende tabel. (Alle getallen zijn in miljoenen.) Zo voegt Project 1 $ 14 miljoen aan NHW toe en zijn uitgaven van $ 12 miljoen in jaar 1 en $ 3 miljoen in jaar 2 nodig. In jaar 1 is $ 50 miljoen aan kapitaal beschikbaar voor projecten en in jaar 2 is $ 20 miljoen beschikbaar.
| NHW | Uitgaven voor jaar 1 | Uitgaven voor 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 |
- Als we geen fractie van een project kunnen uitvoeren, maar wel het hele project of geen van een project, hoe kunnen we dan de NHW maximaliseren?
- 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 geeft Pressdata.xlsx de volgende informatie over elk boek:
- Geraamde omzet en ontwikkelingskosten (in duizenden euro's)
- Pagina's in elk boek
- Of het boek is gericht op een publiek van softwareontwikkelaars (aangegeven met een 1 in kolom E)
Een uitgeverij kan dit jaar boeken publiceren van in totaal 8500 pagina's en moet ten minste vier boeken publiceren die zijn gericht op softwareontwikkelaars. Hoe kan het bedrijf zijn winst maximaliseren?
Over het artikel
Dit artikel is een bewerking van Microsoft Office Excel 2007 Data Analysis and Business Modeling door Wayne L. Winston.
Dit boek in klasstijl is ontwikkeld op basis van een reeks presentaties van Wayne Winston, een bekende statisticus en hoogleraar bedrijfskunde die is gespecialiseerd in creatieve, praktische toepassingen van Excel.