Oplosser gebruiken om de optimale productmix te bepalen

Hoe kan ik de maandelijkse productmix bepalen die de winstgevendheid maximaliseert?

Bedrijven moeten vaak de hoeveelheid van elk product per maand bepalen. In zijn eenvoudigste vorm omvat het probleem van de productmix het bepalen van de hoeveelheid van elk product dat gedurende een maand moet worden geproduceerd om de winst te maximaliseren. Productmix moet meestal voldoen aan de volgende beperkingen:

• De productmix kan niet meer resources gebruiken dan beschikbaar is.

• Er is een beperkte vraag naar elk product. We kunnen gedurende een maand niet meer van een product produceren dan de vraag dicteert, omdat de overtollige productie verspild wordt (bijvoorbeeld een bederfelijk medicijn).

Laten we nu het volgende voorbeeld van het probleem met de productmix oplossen. U vindt de oplossing voor dit probleem in het bestand Prodmix.xlsx, weergegeven in afbeelding 27-1.

Stel dat we werken voor een farmaceutisch bedrijf dat zes verschillende producten produceert in hun fabriek. De productie van elk product vereist arbeid en grondstoffen. Rij 4 in afbeelding 27-1 toont de uren arbeid die nodig zijn om een pond van elk product te produceren, en rij 5 toont de ponden grondstoffen die nodig zijn om een pond van elk product te produceren. Voor het produceren van een pond Product 1 is bijvoorbeeld zes uur arbeid en 3,2 pond grondstoffen vereist. Voor elk geneesmiddel wordt de prijs per pond vermeld in rij 6, de eenheidskosten per pond in rij 7 en de winstbijdrage per pond wordt vermeld in rij 9. Product 2 verkoopt bijvoorbeeld voor $ 11,00 per pond, brengt een eenheidskosten van $ 5,70 per pond in rekening en draagt $ 5,30 winst per pond bij. De vraag van de maand voor elk medicijn wordt vermeld in rij 8. De vraag naar Product 3 is bijvoorbeeld 1041 pond. Deze maand zijn 4500 uur arbeid en 1600 pond grondstoffen beschikbaar. Hoe kan dit bedrijf de maandelijkse winst maximaliseren?

Als we niets wisten over Excel Oplosser, zouden we dit probleem aanpakken door een werkblad te maken om de winst en het resourcegebruik van de productmix bij te houden. Dan zouden we vallen en opstaan gebruiken om de productmix te variëren om de winst te optimaliseren zonder meer arbeid of grondstoffen te gebruiken dan beschikbaar is, en zonder een medicijn te produceren dat de vraag te boven gaat. We gebruiken Oplosser in dit proces alleen in de trial-and-error-fase. In wezen is Oplosser een optimalisatie-engine die de trial-and-error-zoekopdracht feilloos uitvoert.

Een sleutel voor het oplossen van het probleem met de productmix is het efficiënt berekenen van het resourcegebruik en de winst die zijn gekoppeld aan een bepaalde productmix. Een belangrijk hulpmiddel dat we kunnen gebruiken om deze berekening te maken, is de functie SOMPRODUCT. De functie SOMPRODUCT vermenigvuldigt overeenkomende waarden in celbereiken en retourneert de som van deze waarden. Elk celbereik dat in een SOMPRODUCT evaluatie wordt gebruikt, moet dezelfde afmetingen hebben. Dit betekent dat u SOMPRODUCT met twee rijen of twee kolommen kunt gebruiken, maar niet met één kolom en één rij.

Als voorbeeld van hoe we de functie SOMPRODUCT kunnen gebruiken in ons voorbeeld van de productmix, gaan we proberen ons resourcegebruik te berekenen. Ons arbeidsgebruik wordt berekend door

(Arbeid gebruikt per pond drug 1)*(Drug 1 pond geproduceerd)+
(Arbeid gebruikt per pond drug 2)*(Drug 2 pond geproduceerd) + ...
(Arbeid gebruikt per pond drug 6)*(Drug 6 pond geproduceerd)

We kunnen het arbeidsgebruik op een saaiere manier berekenen als D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. Op dezelfde manier kan het gebruik van grondstoffen worden berekend als D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Het invoeren van deze formules in een werkblad voor zes producten kost echter veel tijd. Stel u voor hoe lang het zou duren als u werkt met een bedrijf dat bijvoorbeeld 50 producten in hun fabriek produceerde. Een veel eenvoudigere manier om arbeid en het gebruik van grondstoffen te berekenen, is door de formule SOMPRODUCT($D$2:$I$2,D4:I4) te kopiëren van D14 naar D15. Deze formule berekent D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (wat ons arbeidsgebruik is) maar is veel gemakkelijker in te voeren! U ziet dat ik het $-teken met het bereik D2:I2 gebruik, zodat ik bij het kopiëren van de formule nog steeds de productmix van rij 2 vastleg. De formule in cel D15 berekent het gebruik van grondstoffen.

Op een vergelijkbare manier wordt onze winst bepaald door

(Drug 1 winst per pond)*(Drug 1 pond geproduceerd) +
(Drug 2 winst per pond)*(Drug 2 pond geproduceerd) + ...
(Drug 6 winst per pond)*(Drug 6 pond geproduceerd)

Winst kan eenvoudig worden berekend in cel D12 met de formule SOMPRODUCT(D9:I9,$D$2:$I$2).

We kunnen nu de drie onderdelen van ons productmix oplossermodel identificeren.

• Doelcel. Ons doel is om de winst te maximaliseren (berekend in cel D12).

• Cellen wijzigen. Het aantal kilo's dat van elk product wordt geproduceerd (vermeld in het celbereik D2:I2)

• Beperkingen. We hebben de volgende beperkingen:

  • Gebruik niet meer arbeid of grondstoffen dan beschikbaar is. Dat wil dus dat de waarden in cellen D14:D15 (de gebruikte resources) kleiner moeten zijn dan of gelijk moeten zijn aan de waarden in de cellen F14:F15 (de beschikbare resources).

  • Maak niet meer van een medicijn dan er gevraagd is. Dat wil gezegd, de waarden in de cellen D2:I2 (pond geproduceerd van elk geneesmiddel) moeten kleiner zijn dan of gelijk zijn aan de vraag voor elk geneesmiddel (vermeld in cellen D8:I8).

  • We kunnen geen negatieve hoeveelheid drugs produceren.

Ik laat u zien hoe u de doelcel invoert, cellen wijzigt en beperkingen in Oplosser. Vervolgens hoeft u alleen maar op de knop Oplossen te klikken om een productmix te vinden die de winst maximaliseren!

Klik eerst op het tabblad Gegevens en klik in de groep Analyse op Oplosser.

Het dialoogvenster Parameters voor oplosser wordt weergegeven, zoals wordt weergegeven in afbeelding 27-2.

Klik op het vak Doelcel instellen en selecteer vervolgens onze winstcel (cel D12). Klik op het vak Door cellen te wijzigen en wijs vervolgens het bereik D2:I2 aan, dat de ponden bevat die van elk medicijn worden geproduceerd. Het dialoogvenster ziet er nu uit in Afbeelding 27-3.

We zijn nu klaar om beperkingen aan het model toe te voegen. Klik op de knop Toevoegen. Het dialoogvenster Beperking toevoegen wordt weergegeven in afbeelding 27-4.

Als u de beperkingen voor het resourcegebruik wilt toevoegen, klikt u op het vak Celverwijzing en selecteert u vervolgens het bereik D14:D15. Selecteer <= in de middelste lijst. Klik op het vak Beperking en selecteer vervolgens het celbereik F14:F15. Het dialoogvenster Beperking toevoegen moet er nu uitzien als afbeelding 27-5.

We hebben er nu voor gezorgd dat wanneer Oplosser verschillende waarden voor de veranderende cellen probeert, alleen combinaties worden overwogen die voldoen aan zowel D14<=F14 (gebruikte arbeid is kleiner dan of gelijk aan beschikbare arbeid) als D15<=F15 (gebruikte grondstoffen zijn kleiner dan of gelijk aan de beschikbare grondstof). Klik op Toevoegen om de vraagbeperkingen in te voeren. Vul het dialoogvenster Beperking toevoegen in, zoals wordt weergegeven in afbeelding 27-6.

Het toevoegen van deze beperkingen zorgt ervoor dat wanneer Oplosser verschillende combinaties probeert voor de veranderende celwaarden, alleen combinaties worden overwogen die voldoen aan de volgende parameters:

• D2<=D8 (de hoeveelheid geproduceerd geneesmiddel 1 is kleiner dan of gelijk aan de vraag naar drug 1)

• E2<=E8 (de hoeveelheid geproduceerd geneesmiddel 2 is kleiner dan of gelijk aan de vraag naar drug 2)

• F2<=F8 (de hoeveelheid geproduceerd geneesmiddel 3 is kleiner dan of gelijk aan de vraag naar Drug 3)

• G2<=G8 (de hoeveelheid geproduceerd geneesmiddel 4 is kleiner dan of gelijk aan de vraag naar drug 4)

• H2<=H8 (de hoeveelheid geproduceerd medicijn 5 is kleiner dan of gelijk aan de vraag naar Drug 5)

• I2<=I8 (de hoeveelheid geproduceerd geneesmiddel 6 is kleiner dan of gelijk aan de vraag naar Drug 6)

Klik op OK in het dialoogvenster Beperking toevoegen. Het venster Oplosser moet eruitzien als afbeelding 27-7.

In het dialoogvenster Oplosseropties wordt de beperking ingevoerd dat het wijzigen van cellen niet-negatief moet zijn. Klik op de knop Opties in het dialoogvenster Parameters voor oplosser. Schakel het selectievakje Lineair model aannemen en niet-negatief aannemen in, zoals wordt weergegeven in afbeelding 27-8 op de volgende pagina. Klik op OK.

Als u het vak Niet-negatief aannemen selecteert, zorgt u ervoor dat Oplosser alleen rekening houdt met combinaties van veranderende cellen waarin elke veranderende cel een niet-negatieve waarde veronderstelt. We hebben het vak Lineair model aannemen gecontroleerd omdat het probleem met de productmix een speciaal type oplosserprobleem is dat een lineair model wordt genoemd. In wezen is een Oplosser-model lineair onder de volgende omstandigheden:

• De doelcel wordt berekend door de termen van het formulier op te tellen (cel wijzigen)*(constant).

• Elke beperking voldoet aan de 'lineaire modelvereiste'. Dit betekent dat elke beperking wordt geëvalueerd door de termen van het formulier op te tellen (cel wijzigen)*(constant) en de som te vergelijken met een constante.

Waarom is dit oplosser-probleem lineair? Onze doelcel (winst) wordt berekend als

(Drug 1 winst per pond)*(Drug 1 pond geproduceerd) +
(Drug 2 winst per pond)*(Drug 2 pond geproduceerd) + ...
(Drug 6 winst per pond)*(Drug 6 pond geproduceerd)

Deze berekening volgt een patroon waarin de waarde van de doelcel wordt afgeleid door termen van de vorm op te tellen (cel wijzigen)*(constant).

Onze arbeidsbeperking wordt geëvalueerd door vergelijking van de waarde afgeleid van (Arbeid gebruikt per pond van Drug 1)*(Drug 1 pond geproduceerd) + (Arbeid gebruikt per pond van Drug 2)*(Drug 2 pond geproduceerd)+ ... (Werk onsed per pond Drug 6)*(Drug 6 pond geproduceerd) aan de beschikbare arbeid.

Daarom wordt de arbeidsbeperking geëvalueerd door de termen van de vorm op te tellen (cel wijzigen)*(constant) en de som te vergelijken met een constante. Zowel de arbeidsbeperking als de grondstofbeperking voldoen aan de lineaire modelvereiste.

Onze vraagbeperkingen nemen de vorm aan

(Drug 1 geproduceerd)<=(Drug 1 Demand)
(Drug 2 geproduceerd)<=(Drug 2 Demand)
§
(Drug 6 geproduceerd)<=(Drug 6 Demand)

Elke vraagbeperking voldoet ook aan de vereiste van het lineaire model, omdat elk wordt geëvalueerd door de termen van de vorm op te tellen (cel wijzigen)*(constant) en de som te vergelijken met een constante.

Nu we hebben laten zien dat ons productmixmodel een lineair model is, waarom zou het ons dan schelen?

• Als een oplossermodel lineair is en we Aannemen lineair model selecteren, vindt Oplosser gegarandeerd de optimale oplossing voor het oplossermodel. Als een Oplosser-model niet lineair is, kan Oplosser de optimale oplossing al dan niet vinden.

• Als een oplossermodel lineair is en we Aannemen lineair model selecteren, gebruikt Oplosser een zeer efficiënt algoritme (de simplex-methode) om de optimale oplossing van het model te vinden. Als een oplossermodel lineair is en we niet Lineair model aannemen selecteren, gebruikt Oplosser een zeer inefficiënt algoritme (de GRG2-methode) en kan het moeilijk zijn om de optimale oplossing van het model te vinden.

Nadat u in het dialoogvenster Opties voor oplosser op OK hebt geklikt, keren we terug naar het hoofddialoogvenster oplosser, dat eerder in afbeelding 27-7 wordt weergegeven. Wanneer we op Oplossen klikken, berekent Oplosser een optimale oplossing (indien aanwezig) voor ons productmixmodel. Zoals ik in hoofdstuk 26 heb vermeld, zou een optimale oplossing voor het productmixmodel een set veranderende celwaarden (ponden geproduceerd van elk medicijn) zijn die de winst ten opzichte van de set van alle haalbare oplossingen maximaliseren. Nogmaals, een haalbare oplossing is een set veranderende celwaarden die voldoen aan alle beperkingen. De veranderende celwaarden in afbeelding 27-9 zijn een haalbare oplossing, omdat alle productieniveaus niet-negatief zijn, de productieniveaus de vraag niet overschrijden en het resourcegebruik de beschikbare resources niet overschrijdt.

De veranderende celwaarden die worden weergegeven in afbeelding 27-10 op de volgende pagina, vertegenwoordigen een onuitvoerbare oplossing om de volgende redenen:

• We produceren meer drugs 5 dan de vraag ernaar.

• We gebruiken meer arbeid dan wat er beschikbaar is.

• We gebruiken meer grondstoffen dan wat er beschikbaar is.

Nadat u op Oplossen hebt geklikt, vindt Oplosser snel de optimale oplossing die wordt weergegeven in afbeelding 27-11. U moet Oplossing voor oplosser behouden selecteren om de optimale oplossingswaarden in het werkblad te behouden.

Ons medicijnbedrijf kan zijn maandelijkse winst maximaliseren op een niveau van $ 6,625,20 door 596,67 pond van Drug 4, 1084 pond van Drug 5 te produceren, en geen van de andere drugs! We kunnen niet bepalen of we de maximale winst van $ 6.625,20 op andere manieren kunnen bereiken. We kunnen er alleen zeker van zijn dat er met onze beperkte middelen en vraag geen manier is om meer dan $ 6.627,20 te verdienen deze maand.