Oplosser gebruiken voor het bepalen van de optimale product mix

Hoe kan ik bepalen welke maandelijkse product mix winstgevend is?

Bedrijven dienen vaak de hoeveelheid van elk product te bepalen dat maandelijks dient te worden verlangd. In zijn eenvoudigste vorm dient het product mix -probleem te bepalen hoe u de hoeveelheid van elk product bepaalt dat binnen een maand moet worden geproduceerd om de winst te maximaliseren. De product mix moet doorgaans voldoen aan de volgende beperkingen:

• Product mix mag niet meer resources gebruiken dan beschikbaar is.

• Er geldt een beperkte vraag voor elk product. We kunnen niet meer producten produceren gedurende een maand dan de vraag, omdat de overschot van de overbelasting wordt verspild (zoals een bederfelijke medicijn).

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

Stel dat we voor een farmaceutisch bedrijf werken met zes verschillende producten op hun eigen bedrijf. Voor de productie van elk product is arbeid en grond materiaal vereist. In rij 4 in afbeelding 27-1 wordt het tijdstip van de arbeid aangegeven die nodig is voor het maken van een pond van elk product, en de rij 5 toont de kilo grams producten die nodig zijn voor het maken van een pond van elk product. Als u bijvoorbeeld een pond van product 1 wilt produceren, moet u zes uur arbeids arbeid en 3,2 lbs van grondstof produceren. Voor elk medicijn wordt de prijs per pond gegeven in rij 6, de kostprijs per pond opgegeven in rij 7 en de winstbijdrage per pond wordt uitgedrukt in rij 9. Product 2 wordt bijvoorbeeld verkocht voor $11,00 per pond, de kostprijs van $5,70 per pond, en draagt een bijdrage aan $5,30 winst per pond. De vraag van de maand voor elk medicijn wordt gegeven in rij 8. Zo is vraag voor product 3 1041 pond. Deze maand zijn 4500 uur arbeid en 1600 lbs van grondstof vrij. Hoe kan dit bedrijf de maandelijkse winst maximaliseren?

Als we op de hoogte zijn van Excel Oplosser, kunnen we dit probleem verhelpen door een werkblad te construeren, zodat het winst-en Resourcegebruik van de product mix kan worden bijgehouden. Vervolgens gebruiken we de proefversie en-fout om de winst te bekrachtigen zonder dat u meer arbeid of grondstof gebruik hoeft te maken dan is beschikbaar, en zonder dat er een medicijn voor de vraag wordt geproduceerd. In deze procedure wordt Oplosser alleen in de fase proefversie en de fout fase gebruikt. In essentie is Oplosser een optimaliserings engine waarmee het zoeken naar een proefversie en fouten kan worden uitgevoerd.

Een sleutel voor het oplossen van het probleem met het productmix is het efficiënt te berekenen van het Resourcegebruik en de winst die is gekoppeld aan een bepaald product mix. Een belangrijk hulpmiddel voor het maken van deze berekening is de functie SOMPRODUCT. Met de functie SOMPRODUCT worden corresponderende waarden in celbereiken vermenigvuldigd en wordt de som van deze waarden als resultaat gegeven. Elk celbereik dat wordt gebruikt in een SOMPRODUCT-evaluatie, moet dezelfde afmetingen hebben, wat betekent dat u SOMPRODUCT met twee rijen of twee kolommen kunt gebruiken, maar niet één kolom en één rij.

Als voorbeeld van het gebruik van de functie SOMPRODUCT in het voorbeeld van de productmix, gaan we proberen het Resourcegebruik te berekenen. Onze arbeid wordt berekend door

(Arbeid gebruikt per pond van medicijn 1) * (medicijn 1 pond voortgebracht) +
(Arbeid gebruikt per pond van medicijn 2) * (medicijn 2 pond voortgebracht) +...
(Arbeid gebruikt per pond van medicijn 6) * (medicijn 6 pond geproduceerd)

We kunnen het werk voor arbeids gebruik op een nog veelere manier berekenen als D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. Ook kan het gebruik van onbewerkte materialen worden berekend als D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * i5. Het invoeren van deze formules in een werkblad duurt echter niet voor zes producten. U kunt zien hoe lang het duurt voordat u met een bedrijf werkte dat, bijvoorbeeld 50-producten, op hun eigen bedrijf werd vervaardigd. Een veel eenvoudige manier om arbeid en grondstofverbruik te berekenen is het kopiëren van D14 naar D15 de formule SOMPRODUCT ($D $2: $I $2, D4: I4). Met deze formule wordt de D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (wat is onze arbeidsverbruik), maar u kunt nog veel makkelijker invoeren. Let op dat ik het $-teken met het bereik D2: I2, zodat wanneer ik de formule Kopieer, zodat ik de product mix van rij 2 nog moet vastleggen. De formule in cel D15 rekent grondstofverbruik.

Onze winst wordt op een soortgelijke manier bepaald door

(Winst per pond voor medicijn 1) * (medicijn 1 pond geproduceerd) +
(Medicijn 2 winst per pond) * (medicijn 2 pond geproduceerd) +...
(Medicijn 6 winst per pond) * (medicijn 6 pond geproduceerd)

Winst in cel D10 met de formule SOMPRODUCT (D9: i9, $D $2: $I $2)is eenvoudig te berekenen.

We kunnen nu de drie onderdelen van ons product mix Oplosser-model identificeren.

• Doelcel. Ons doel is de winstgevendheid te maximaliseren (berekend in cel D10).

• Cellen wijzigen. Het aantal kilo kilos van elk product (vermeld in het cellenbereik D2: I2)

• Beperkingen. We hebben de volgende beperkingen:

  • Gebruik niet meer werk of onkosten grondstoffen. Dat wil zeggen: de waarden in de cellen D14: D15 (de gebruikte resources) moeten kleiner zijn dan of gelijk zijn aan de waarden in de cellen F14: F15 (de beschikbare bronnen).

  • Produceert geen meer van een drugs dan met de vraag. Dat wil zeggen: de waarden in de cellen D2: I2 (Euro's gemaakt van elk medicijn) moeten kleiner zijn dan of gelijk zijn aan de vraag voor elk medicijn (vermeld in cel D8: I8).

  • Er kan geen sprake zijn van een negatieve hoeveelheid drugs.

Ik laat u zien hoe u de doelcel, het wijzigen van cellen en beperkingen in Oplosser invoert. Vervolgens hoeft u niet te klikken op de knop oplossen om een product mix met winst te zoeken.

Als u wilt beginnen, klikt u op het tabblad gegevens en klikt u in de groep analyse op Oplosser.

Het dialoogvenster parameters van Oplosser wordt weergegeven, zoals wordt weergegeven in afbeelding 27-2.

Klik in het vak doelcel instellen en selecteer vervolgens onze winst cel (cel D10). Klik op het vak door veranderende cellen en wijs het bereik D2: I2 aan, dat de Euro's bevat van elk medicijn. In het dialoogvenster ziet u nu afbeelding 27-3.

U kunt nu beperkingen toevoegen aan het model. Klik op de knop toevoegen. U ziet het dialoogvenster beperking toevoegen, dat wordt weergegeven in afbeelding 27-4.

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

Wanneer Oplosser verschillende waarden voor de veranderende cellen probeert te gebruiken, worden alleen combinaties weergegeven die voldoen aan beide D14<= F14 (arbeid verbruikt minder dan of gelijk aan de beschikbare arbeid) en D15<= F15 (grondstofverbruik is kleiner dan of gelijk aan het onbewerkte materiaal) wordt beschouwd. Klik op toevoegen om de vraag beperkingen in te voeren. Vul het dialoogvenster beperking toevoegen in, zoals wordt weergegeven in afbeelding 27-6.

Door deze beperkingen toe te voegen, zorgt u ervoor dat alleen combinaties die voldoen aan de volgende parameters worden gebruikt:

• D2<= D8 (het bedrag dat is gemaakt van medicijn 1 is kleiner dan of gelijk aan de vraag voor medicijn 1)

• E2<= E8 (de hoeveelheid vervaardigd van medicijn 2 is kleiner dan of gelijk aan de vraag voor medicijn 2)

• F2<= F8 (het bedrag dat is gemaakt van medicijn 3 is kleiner dan of gelijk aan de vraag voor medicijn 3)

• G2<= G8 (het gemaakte bedrag van medicijn 4 is kleiner dan of gelijk aan de vraag voor medicijn 4)

• H2<= H8 (het gemaakte bedrag van medicijn 5 is kleiner dan of gelijk aan de vraag voor medicijn 5)

• I2<= i8 (het gemaakte bedrag van medicijn 6 is kleiner dan of gelijk aan de vraag voor medicijn 6)

Klik op OK in het dialoogvenster beperking toevoegen. Het Oplosser-venster moet er nu zo uitzien als in afbeelding 27-7.

In het dialoogvenster Opties voor Oplosser wordt de beperking ingevoerd waarmee cellen wijzigen in niet-negatief moeten zijn. Klik in het dialoogvenster Parameters Oplosser op de knop Opties. Selecteer het selectievakje lineair model aannemen en het selectievakje niet-negatief, zoals weergegeven in afbeelding 27-8, op de volgende pagina. Klik op OK.

Als u wilt controleren of het selectievakje niet-negatieve waarden opneemt, wordt u aangeraden alleen combinaties van veranderende cellen te bezorgen, waarbij elke veranderende cel een niet-negatieve waarde in beoordeelt. We hebben het selectievakje lineair model aannemen uitgeschakeld, omdat het product mix van een speciaal type probleem met Oplosser een lineair modelwordt genoemd. In feite is een Solver-model lineair onder de volgende voorwaarden:

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

• Elke beperking voldoet aan de vereisten voor lineair model. Dit betekent dat elke beperking wordt geëvalueerd door samen de termen van het formulier (veranderende cel) * (constante) en de totalen te vergelijken met een constante.

Waarom is dit Oplosser-probleem lineair? De doelcel (winst) wordt berekend als

(Winst per pond voor medicijn 1) * (medicijn 1 pond geproduceerd) +
(Medicijn 2 winst per pond) * (medicijn 2 pond geproduceerd) +...
(Medicijn 6 winst per pond) * (medicijn 6 pond geproduceerd)

Bij deze berekening wordt een patroon gevolgd waarbij de waarde van de doelcel wordt afgeleid door samen termen van het formulier (veranderende cel) * (constante)toe te voegen.

Onze arbeid-beperking wordt geëvalueerd door de waarde te vergelijken die is afgeleid van (arbeidsverbruik per pond van medicijn 1) * (medicijn 1 pond geproduceerd) + (arbeid verbruik per pond van medicijn 2) +... (Arbeid USEd per pond van medicijn 6) * (medicijn 6 pond voortgebracht) tot de beschikbare arbeid.

Daarom wordt de beperking voor de arbeid geëvalueerd door de termen van het formulier (cel wijzigen) * (constante) te combineren en de totalen te vergelijken met een constante. De beperking arbeid en de grondstof beperking voldoen aan de vereisten voor lineair model.

Onze Aanvraagbeperkingen hebben de vorm

(Medicijn 1 vervaardigd) <= (vraag voor medicijn 1)
(Medicijn 2 geproduceerd) <= (medicijn 2 vraag)
§
(Medicijn 6 vervaardigd) <= (medicijn 6 vraag)

Elke aanvraagbeperking voldoet ook aan de vereisten voor lineair model, omdat elk wordt geëvalueerd door het toevoegen van de termen van het formulier (veranderende cel) * (constante) en de vergelijking van de totalen met een constante.

Hoe kan ik zien dat ons product mix een lineair model is, waarom we het willen doen?

• Als een Oplosser-model lineair is en we selecteren als lineair model, is Oplosser gegarandeerd de optimale oplossing voor het Oplosser-model. Als een Solver-model niet lineair is, kan Oplosser de optimale oplossing mogelijk niet vinden.

• Als een Solver-model lineair is en we selecteren als lineair model, moet Oplosser een zeer efficiënt algoritme (de simplex-methode) gebruiken om de optimale oplossing van het model te zoeken. Als een Solver-model lineair is en we niet selecteren om het voor lineair model te selecteren, gebruikt Oplosser een zeer ondoelmatig algoritme (de GRG2-methode) en kan de optimale oplossing van het model niet worden gevonden.

Nadat u op OK hebt geklikt in het dialoogvenster Opties voor Oplosser, gaat u terug naar het hoofdvenster van Oplosser, dat eerder werd weergegeven in afbeelding 27-7. Wanneer we op oplossen klikken, wordt er een optimale oplossing (indien aanwezig) voor ons productmixmodel berekend. Zoals u in hoofdstuk 26 hebt uiteengezet, is een optimale oplossing voor het product mix-model een set gewijzigde celwaarden (kilo ponden van elk medicijn) die de winst in de reeks van alle bruikbare oplossingen optimaliseren. Een bruikbare oplossing is een reeks veranderende celwaarden die voldoen aan alle beperkingen. De veranderende celwaarden in afbeelding 27-9 vormen een beschikbare oplossing omdat alle productieniveaus niet-negatief zijn, de productieniveaus niet groter zijn dan de vraag en het Resourcegebruik niet meer dan de beschikbare bronnen.

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

• We produceren meer van medicijn 5 dan de vraag daarvoor.

• We gebruiken meer arbeid dan de beschikbare functies.

• U gebruikt meer onbewerkte materialen dan wat er beschikbaar is.

Nadat u op oplossen hebt geklikt, vindt u met Oplosser snel de optimale oplossing die wordt weergegeven in afbeelding 27-11. U moet Oplosser-oplossing behouden selecteren om de optimale oplossings waarden in het werkblad te behouden.

Onze drugs maatschappij kan zijn maand winst op een niveau van $6.625,20 maximaliseren door 596,67 pond van medicijn 4, 1084 lbs van medicijn 5 en geen andere drugs te produceren. We kunnen niet bepalen of we de maximum winst met $6.625,20 op andere manieren kunnen bereiken. We kunnen er ook voor zorgen dat u deze maand niet meer dan $6.627,20 kunt maken, met onze beperkte informatiebronnen en aanvraag.