Gebruik Oplosser om te bepalen de optimale productmix

Opmerking:  We willen u graag zo snel mogelijk de meest recente Help-inhoud in uw eigen taal bieden. Deze pagina is automatisch vertaald en kan grammaticale fouten of onnauwkeurigheden bevatten. Wij hopen dat deze inhoud nuttig voor u is. Kunt u ons onder aan deze pagina laten weten of de informatie nuttig voor u was? Hier is het Engelstalige artikel ter referentie.

In dit artikel wordt beschreven hoe met Oplosser, een Microsoft Excel-invoegtoepassing kunt u gebruiken voor what-if-analyses, om te bepalen de combinatie van een optimale product.

Hoe kan ik de maandelijkse productmix die winstgevendheid maximaal zien?

Er moeten vaak bedrijven om te bepalen de hoeveelheid van elk product en op maandbasis te retourneren. In het eenvoudigste het probleem mengt u product te maken hoe bepaalt u de hoeveelheid van elk product dat moet worden geproduceerd tijdens een maand-tot-winst maximaliseren. Productmix moet meestal voldoen aan de volgende beperkingen:

  • Productmix niet meer informatiebronnen dan er beschikbaar zijn gebruiken.

  • Er is een beperkte vraag voor elk product. We kunnen niet meer van een product produceren in een maand dan aanvraag bepaalt, omdat de overproductie verloren (bijvoorbeeld medicijnen die beperkt houdbaar gaat).

We gaan nu het volgende voorbeeld van het product mix probleem proberen. U vindt de oplossing voor dit probleem in het bestand Prodmix.xlsx, weergegeven in afbeelding 27-1.

Afbeelding van boek
Afbeelding 27-1 de productmix

Stel dat we werken bij een medicijnen bedrijf die zes verschillende producten in zijn fabriek oplevert. Hoeveelheid van elk product dat is vereist voor standaardwerkprocessen en onbewerkte materiaal. Rij 4 in afbeelding 27-1 ziet u de uren van standaardwerkprocessen die nodig zijn voor het maken van een pond van elk product en rij 5 ziet u hoeveel pond grondstoffen nodig zijn voor het maken van een pond van elk product. Een pond van Product 1 produceren vereist bijvoorbeeld zes uren van standaardwerkprocessen en 3,2 pond onbewerkte materiaal. Voor elke medicijnen die de prijs per pond wordt uitgedrukt in rij 6, de kostprijs per pond wordt uitgedrukt in rij 7 en de winstbijdrage van de per pond wordt uitgedrukt in rij 9. Bijvoorbeeld Product 2 verkocht voor $11,00 per pond, een prijs per eenheid van $5,70 per pond bijhoudt en $5,30 winst per pond bijdraagt. Aanvraag van de maand voor elke medicijnen wordt uitgedrukt in rij 8. Aanvraag voor Product 3 is bijvoorbeeld 1041 pond. Deze maand, zijn 4500 standaardwerkprocessen en 1600 pond van onbewerkte materiaal beschikbaar. Hoe kan dit bedrijf de maandelijkse winst maximaliseren?

Als u niets over Excel Oplosser, aanvallen we dit probleem op door het bouwen van een werkblad om bij te houden winst- en resourcekalenders gebruik die is gekoppeld aan de combinatie van het product. Vervolgens zou gebruiken we proefabonnement en fout variëren van het productmix winst wordt geoptimaliseerd zonder meer standaardwerkprocessen of onbewerkte materiaal dan beschikbaar is en zonder dat er een medicijnen die groter zijn dan aanvraag. We gebruiken Oplosser in deze procedure alleen in de proefversie-en-fout-fase. Oplosser is in feite een optimalisatie-engine waarmee probleemloos werkt de zoekopdracht proefversie-en-fout.

Een sleutel voor het oplossen van het product mix probleem is voor het efficiënt berekenen van de weergave Resourcegebruik en winst die is gekoppeld aan de combinatie van een bepaald product. Een belangrijk hulpmiddel kunt u deze berekening gebruiken is de functie SOMPRODUCT. De functie SOMPRODUCT vermenigvuldigt corresponderende waarden in celbereiken en geeft als resultaat de som van deze waarden. Elk celbereik gebruikt in een evaluatie met SOMPRODUCT moet hebben dezelfde dimensies die houdt in dat u SOMPRODUCT met twee rijen of twee kolommen, maar niet met één kolom en één rij gebruiken kunt.

Als een voorbeeld van hoe we de functie SOMPRODUCT in ons product gebruiken kunt mengen voorbeeld, laten we eens proberen te berekenen van onze Resourcegebruik. Het gebruik van onze standaardwerkprocessen wordt berekend door

*(Drug 1 pounds produced) (Standaardwerkprocessen gebruikt per pond van medicijnen 1) +
(Standaardwerkprocessen per pond van medicijnen 2 gebruikt) * (productie medicijn 2 pond geproduceerd) +...
(Standaardwerkprocessen gebruikt per pond van medicijnen 6) * (productie medicijn 6 pond geproduceerd)

We standaardwerkprocessen gebruik op meer vervelende wijze als kan berekenen D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. Daarnaast grondstoffenverbruik kan worden berekend als D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. Deze formules in een werkblad invoeren voor zes producten is echter tijdrovende. Stel hoelang nodig zou hebben als u met een bedrijf dat geproduceerd werkte, bijvoorbeeld 50 producten in hun bedrijf. Er is een makkelijker manier te berekenen standaardwerkprocessen en het gebruik van de onbewerkte materiaal kopiëren van D14 naar D15 de formule SUMPRODUCT($D$2:$I$2,D4:I4). Deze formule berekent D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (is het gebruik van onze standaardwerkprocessen), maar is veel gemakkelijker om in te voeren! Zoals u ziet dat ik het dollarteken met het bereik D2: I2, gebruiken zodat wanneer ik de formule kopieert ik nog steeds de combinatie van het product van rij 2 vastleggen. De formule in cel D15 berekent grondstoffenverbruik.

Op soortgelijke wijze, wordt onze winst bepaald door

(Productie medicijn 1 winst per pond) * (1 medicijnen pond geproduceerd) +
(medicijnen 2 winst per pond) * (productie medicijn 2 pond geproduceerd) +...
(Medicijnen 6 winst per pond) * (productie medicijn 6 pond geproduceerd)

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

We kunnen nu de drie onderdelen van de combinatie van onze product Oplosser-model identificeren.

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

  • Veranderende cellen. Het aantal pond geproduceerd van elk product (weergegeven in het cellenbereik D2: I2)

  • Beperkingen. We hebben de volgende beperkingen:

    • Gebruik niet meer standaardwerkprocessen of onbewerkte materiaal dan beschikbaar is. Dat wil zeggen de waarden in cellen D14: D15 (de resources die worden gebruikt) moeten kleiner dan of gelijk is aan de waarden in cellen F14: F15 (beschikbare resources).

    • Niet meer worden geproduceerd van een medicijnen dan is van de vraag. Dat wil zeggen de waarden in de cellen D2: I2 (aantal pond dat van elk medicijnen) moeten kleiner dan of gelijk is aan de aanvraag voor elke medicijnen (weergegeven in cellen D8: I8).

    • We kunnen niet een negatieve hoeveelheid eventuele medicijnen produceren.

Ik ziet u hoe u de doelcel Voer veranderende cellen en de beperkingen in Oplosser. U hoeft te is vervolgens klikt u op de knop oplossen om te zoeken van een product winst maximaliseren combinatie!

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

Opmerking: Zoals wordt uitgelegd in hoofdstuk 26, "Een inleiding naar optimalisatie met Excel Oplosser," is door te klikken op de Microsoft Office-knop, klikt u vervolgens Excel-opties, gevolgd door invoegtoepassingen voor Oplosser geïnstalleerd. Klik op Excel-invoegtoepassingen in de lijst beheren, schakel het selectievakje Oplosser-invoegtoepassing in en klik vervolgens op OK.

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

Afbeelding van boek
Dialoogvenster van de afbeelding 27-2-de Parameters van Oplosser

Klik in het vak doelcel instellen en selecteer de winstcel van onze (cel D12). Klik in het vak door verandering cel en wijst u het bereik D2: I2, waarin de pond geproduceerd van elke medicijnen. Het dialoogvenster ziet er nu afbeelding 27-3.

Afbeelding van boek
Dialoogvenster van de afbeelding 27-3-de Parameters van Oplosser met de doelcel en veranderende cellen zijn opgegeven

Nu we gaan beperkingen toevoegen aan het model. Klik op de knop toevoegen. Hier ziet u het dialoogvenster toevoegen wordt weergegeven in afbeelding 27-4.

Afbeelding van boek
Afbeelding 27-4 het dialoogvenster toevoegen

Als u wilt de resourcebeperkingen gebruik toevoegen, klik in het vak celverwijzing en selecteer het bereik D14: D15. Selecteer < = in de middelste lijst. Klik in het vak restrictie, en selecteer het celbereik F14: F15. Het dialoogvenster toevoegen ziet er nu zoals figuur 27-5.

Afbeelding van boek
Afbeelding 27 tot en met 5 het dialoogvenster toevoegen met de beperkingen voor het gebruik van resource ingevoerd

We hebt ervoor gezorgd dat wanneer Oplosser verschillende waarden voor het wijzigen van cellen opheffen, alleen combinaties die voldoen aan beide D14 < = F14 (standaardwerkprocessen gebruikt is kleiner is dan of gelijk is aan standaardwerkprocessen beschikbaar) en D15 < = F15 (grondstoffen kleiner is dan of gelijk aan beschikbare grondstoffen) wordt beschouwd. Klik op toevoegen om de aanvraag restricties op te geven. Vul in het dialoogvenster toevoegen zoals weergegeven in figuur 27-6.

Afbeelding van boek
Afbeelding 27-6 het dialoogvenster toevoegen met de aanvraag beperkingen ingevoerd

Het toevoegen van deze beperkingen zorgt ervoor dat Oplosser bij verschillende combinaties voor de veranderende celwaarden, alleen combinaties die voldoen aan de volgende parameters wordt beschouwd als:

  • D2 < = D8 (het schuifblok wordt verplaatst van medicijnen 1 geproduceerd is kleiner dan of gelijk is aan de aanvraag voor medicijnen 1)

  • E2 < = E8 (de hoeveelheid geproduceerd van medicijnen 2 is kleiner dan of gelijk aan de aanvraag voor medicijnen 2)

  • F2 < = F8 (het schuifblok wordt verplaatst van medicijnen 3 aangebracht geproduceerd is kleiner dan of gelijk is aan de aanvraag voor medicijnen 3)

  • G2 < = G8 (het schuifblok wordt verplaatst van medicijnen 4 aangebracht geproduceerd is kleiner dan of gelijk is aan de aanvraag voor medicijnen 4)

  • H2 < = H8 (het schuifblok wordt verplaatst van medicijnen 5 aangebracht geproduceerd is kleiner dan of gelijk is aan de aanvraag voor medicijnen 5)

  • I2 < = I8 (het schuifblok wordt verplaatst van medicijnen 6 aangebracht geproduceerd is kleiner dan of gelijk is aan de aanvraag voor medicijnen 6)

Klik op OK in het dialoogvenster toevoegen. Het venster Oplosser moet er uitzien zoals afbeelding 27 tot en met 7.

Afbeelding van boek
Afbeelding 27 tot en met 7 het uiteindelijke Parameters Oplosser-dialoogvenster voor het product mix probleem

Beperking dat veranderende cellen moet worden niet-negatieve in het dialoogvenster Oplosser-opties. Klik op de knop Opties in het dialoogvenster Parameters Oplosser. Controleer het lineaire Model wordt ervan uitgegaan dat en het vak wordt ervan uitgegaan dat er niet-negatieve zoals wordt weergegeven in figuur 27-8 op de volgende pagina. Klik op OK.

Afbeelding van boek
Afbeelding 27-8 Oplosser options instellingen

Niet-negatief wordt ervan uitgegaan dat dit vakje zorgt ervoor dat Oplosser alleen combinaties van veranderende cellen waarin deze cellen wordt ervan uitgegaan een niet-negatieve waarde acht. We ingeschakeld in dat het vak lineaire Model wordt ervan uitgegaan dat omdat het product meng een probleem is een speciaal type van het probleem met Oplosser een lineaire modelgenoemd. In feite is een oplossingsmodel lineair zijn de volgende voorwaarden:

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

  • Elke beperking voldoet aan de 'lineaire model vereiste." Dit betekent dat elke beperking wordt geëvalueerd door de termen van het formulier (cel)*(constante) wijzigen en de totalen naar een constante vergelijken.

Waarom wordt dit probleem Oplosser lineaire? De doelcel (winst) wordt berekend als

(Productie medicijn 1 winst per pond) * (1 medicijnen pond geproduceerd) +
(medicijnen 2 winst per pond) * (productie medicijn 2 pond geproduceerd) +...
(Medicijnen 6 winst per pond) * (productie medicijn 6 pond geproduceerd)

Deze berekening verloopt volgens een patroon waarin de doelcel waarde wordt afgeleid door termen van het formulier (cel)*(constante) wijzigen.

De beperking van onze standaardwerkprocessen wordt geëvalueerd door de waarde die wordt afgeleid van (Standaardwerkprocessen gebruikt per pond van medicijnen 1) te vergelijken * (1 medicijnen pond geproduceerd) + (Standaardwerkprocessen per pond van medicijnen 2 gebruikt) *(Drug 2 pounds produced) +... (Geleverde arbeid onsed per pond van medicijnen 6) * (productie medicijn 6 pond geproduceerd) naar de standaardwerkprocessen beschikbaar.

Daarom de restrictie standaardwerkprocessen wordt geëvalueerd door de termen van het formulier (cel)*(constante) wijzigen en de totalen naar een constante vergelijken. Voldoen aan de vereiste lineaire model zowel de beperkingen van de standaardwerkprocessen en de onbewerkte materiaal.

Onze beperkingen bellen nemen het formulier

(Medicijnen 1 geproduceerd) < = (vraag medicijnen 1)
(medicijnen 2 geproduceerd) < = (vraag medicijnen 2)
artikel
(medicijnen 6 geproduceerd) < = (vraag medicijnen 6)

Elke beperking aanvraag ook de vereiste lineaire model voldoet aan omdat elk wordt geëvalueerd door de termen van het formulier (cel)*(constante) wijzigen en de totalen naar een constante vergelijken.

Hebben getoond dat productmixmodel een lineaire model is, waarom we delen?

  • Als een oplossingsmodel lineair is en lineaire Model wordt ervan uitgegaan dat u selecteert, zeker dat Oplosser de optimale oplossing aan het model Oplosser vindt. Als een Oplosser-model niet lineaire is, of Oplosser de optimale oplossing niet kan vinden.

  • Als een oplossingsmodel lineair is en lineaire Model wordt ervan uitgegaan dat u selecteert, wordt een uiterst efficiënte algoritme (de simplex methode) in Oplosser gebruikt om van het model optimale oplossing te vinden. Als een Oplosser-model lineair is en we lineaire Model wordt ervan uitgegaan dat niet inschakelt, Oplosser gebruikmaakt van een zeer niet efficiënt algoritme (de methode GRG2) en mogelijk niet kunt vinden van het model optimale oplossing.

Wanneer u op OK in het dialoogvenster Opties voor Oplosser, terug we naar het belangrijkste Oplosser dialoogvenster eerder in figuur 27 tot en met 7 weergegeven. Wanneer we op oplossen klikt, berekent Oplosser een optimale oplossing (indien aanwezig) voor het productmixmodel. Zoals ik vermeld in hoofdstuk 26, zou een optimale oplossing voor het product mix model een set veranderende celwaarden (aantal pond dat van elk medicijnen) die de winst gemaximaliseerd over het instellen van alle bruikbare oplossingen. Klik nogmaals is bruikbare oplossing een set van het wijzigen van celwaarden die voldoet aan alle voorwaarden. De veranderende celwaarden in figuur 27-9 hebben een bruikbare oplossing omdat alle productieniveaus niet-negatieve zijn en Resourcegebruik geen beschikbare bronnen overschrijdt productieniveaus aanvraag niet overschrijden.

Afbeelding van boek
Afbeelding 27-9 A bruikbare oplossing van het product combineren voldoet aan alle beperkingen.

De veranderende celwaarden in figuur 27-10 op de volgende pagina vertegenwoordigen een onbruikbare oplossing voor de volgende oorzaken hebben:

  • We produceren meer van medicijnen 5 dan de vraag.

  • We gebruiken meer standaardwerkprocessen dan beschikbaar is.

  • We gebruiken meer onbewerkte materiaal dan beschikbaar is.

Afbeelding van boek
Afbeelding 27-10 past een onbruikbare oplossing van het product niet binnen de gedefinieerde beperkingen.

Nadat u hebt geklikt oplossen, vindt u Oplosser snel de optimale oplossing in figuur 27-11 weergegeven. U moet Oplosser-oplossing behouden voor het behoud van de waarden van de beste oplossing in het werkblad selecteren.

Afbeelding van boek
Afbeelding 27-11 de optimale oplossing van het product

Ons medicijnen bedrijf kan de maandelijkse winst op een niveau van $6.625,20 maximaliseren door te produceren 596,67 pond van medicijnen 4, 1084 pond van medicijnen 5 en geen van de overige medicijnen! Kan niet worden bepaald als we de maximale winst van $6.625,20 op andere manieren kunt bereiken. Alle we kunt Zorg ervoor dat u van is dat met onze beperkte resources en de vraag, er geen manier om te laten meer dan $6,627.20 deze maand.

Stel dat de aanvraag voor elk product moet worden voldaan. (Zie het werkblad Geen bruikbare oplossing in het bestand Prodmix.xlsx.) Wat u vervolgens hoeft te wijzigen van de beperkingen van onze aanvraag van D2: I2 < = D8: I8 naar D2: I2 > = D8: I8. Klik hiertoe Oplosser openen, selecteert u de D2: I2 < = D8: I8 beperking en klik vervolgens op wijzigen. Het dialoogvenster wijzigen, weergegeven in figuur 27-12, wordt weergegeven.

Afbeelding van boek
Figuur 27-12 het dialoogvenster wijzigen

Selecteer > = en klik vervolgens op OK. Ervoor dat in aanmerking alleen celwaarden die voldoen aan alle vereisten wijzigen. Wanneer u op oplossen klikt, ziet u het bericht "Oplosser heeft een bruikbare oplossing niet gevonden." Dit bericht betekent niet dat we een fout in het model, maar die hebt gemaakt met onze beperkte resources, we niet voldoen aan de aanvraag voor alle producten. Oplosser is ons gewoon vertellen dat als u wilt vergaderen aanvraag voor elk product, moeten we meer standaardwerkprocessen, meer onbewerkte materialen of meer van beide toevoegen.

Laten we eens kijken wat er gebeurt als we onbeperkte aanvraag voor elk product toestaan en we negatieve hoeveelheden worden geproduceerd voor elke medicijnen toestaan. (U kunt dit probleem Oplosser Zie in het werkblad Instellen waarden convergeren niet in het bestand Prodmix.xlsx.) Als u zoekt de optimale oplossing voor deze situatie, open Oplosser, klikt u op de knop Opties en schakel het selectievakje niet-negatief wordt ervan uitgegaan dat. Selecteer in het dialoogvenster Parameters Oplosser de aanvraag restrictie D2: I2 < = D8: I8 en klik vervolgens op verwijderen om te verwijderen van de beperking. Wanneer u op oplossen klikt, Oplosser geeft als resultaat het bericht "Set celwaarden geen resultaat oplevert." Dit bericht betekent dat als de doelcel is gemaximaliseerd (zoals in dit voorbeeld), zijn er bruikbare oplossingen met waarden voor de willekeurig grote doelcel. (Als de doelcel is geminimaliseerd, het bericht "Instellen cel waarden convergeren niet" betekent dat er bruikbare oplossingen met celwaarden willekeurig kleine doel.) In dit geval maken doordat negatieve hoeveelheid een medicijnen die we van kracht"" resources die kunnen worden gebruikt om willekeurig grote hoeveelheden van andere medicijnen produceren. Gezien de onbeperkte vraag, deze manier kunnen wij onbeperkt winst maken. In een werkelijke situatie aanbrengen geen we een onbeperkte hoeveelheid geld. Kortom, als u "Instellen waarden convergeren niet" wordt weergegeven, heeft uw model een fout.

  1. Stel dat u dat ons bedrijf medicijnen maximaal 500 uren van standaardwerkprocessen bij $1 meer per uur dan de huidige standaardwerkprocessen kosten kunt kopen. Hoe kunnen we winst maximaliseren?

  2. Bij een chip fabriek, produceren vier technici (A, B, C en D) drie producten (producten 1, 2 en 3). Deze maand, die door de chipfabrikant kunt verkopen 80 eenheden van Product 1, 50 eenheden van Product 2 en maximaal 50 eenheden van Product 3. Technicus A kunt u alleen producten 1 en 3. Technicus B kan alleen producten 1 en 2 maken. Technicus C kan alleen Product 3 maken. Technicus D kan alleen Product 2 maken. Voor elke eenheid geproduceerd, de producten de volgende winst bijdragen: Product-1, $6; Product 2: $7; en Product 3: $10. De tijd (in uren) elke technicus nodig heeft voor het bouwen van een product is als volgt:

    Product

    Technicus A

    Technicus B

    Technicus C

    Technicus D

    1

    2

    2,5

    Is niet mogelijk

    Is niet mogelijk

    2

    Is niet mogelijk

    3

    Is niet mogelijk

    3,5

    3

    3

    Is niet mogelijk

    4

    Is niet mogelijk

  3. Elke technicus kan maximaal 120 uren per maand werken. Hoe kan de chipfabrikant de maandelijkse winst maximaliseren? Wordt ervan uitgegaan dat een gebroken aantal eenheden kan worden geproduceerd.

  4. Een computerfabrikant produceert muis, toetsenborden en video spel joysticks. De winst per eenheid, per eenheid standaardwerkprocessen gebruik, maandelijkse vraag en per eenheid systeemtijd gebruik worden vermeld in de volgende tabel:

    Muis

    Toetsenborden

    Joysticks

    Winst-eenheid

    $8

    $11

    $9

    Arbeidsverbruik/eenheid

    .2 uur

    .3 uur

    .24 uur

    Machine/tijdseenheid

    .04 uur

    .055 uur

    .04 uur

    Maandelijkse vraag

    15.000

    27.000

    11.000

  5. Elke maand, een totaal van 13.000 werkuren en 3000 uren van machinetijd zijn beschikbaar. Hoe kan de fabrikant van de winstbijdrage van de maandelijkse van het bedrijf maximaliseren?

  6. Oplossen van onze medicijnen voorbeeld ervan uitgaande dat een minimale vraag van 200 eenheden voor elke medicijnen moet worden voldaan.

  7. Jason zorgt ervoor dat ruitje armbanden, halssnoeren en oorringen. Hij wil maximaal 160 uur per maand werken. Hij heeft 800 ounce van ruiten. De winst, standaardwerkprocessen tijd en ounce van ruiten die zijn vereist voor elk product produceren worden hieronder. Als de aanvraag voor elk product wordt gemaakt en hoe kan Jason zijn winst maximaliseren?

    Product

    Winst per eenheid

    Arbeidsuren per eenheid

    Ounce van ruiten per eenheid

    Armband

    € 300

    .35

    1,2

    Ketting

    $ 200

    .15

    .75

    Oorbellen

    € 100

    0,05

    0,5

Uw Office-vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Office Insiders

Was deze informatie nuttig?

Bedankt voor uw feedback.

Hartelijk dank voor uw feedback! Het lijkt ons een goed idee om u in contact te brengen met een van onze Office-ondersteuningsagents.

×