Använda problemlösa ren för att ta reda på optimal produkt kombination

Obs!: Vi vill kunna erbjuda dig bästa möjliga supportinnehåll så fort som möjligt och på ditt språk. Den här sidan har översatts med hjälp av automatiserad översättning och kan innehålla grammatiska fel eller andra felaktigheter. Vår avsikt är att den här informationen ska vara användbar för dig. Vill du berätta för oss om informationen är till hjälp längst ned på sidan? Här är artikeln på engelska som referens.

I den här artikeln beskrivs hur du använder problemlösa ren, ett tilläggsprogram för Microsoft Excel som du kan använda för konsekvens analyser för att fastställa en optimal produkt kombination.

Hur kan jag ta reda på produkt kombinationen per månad för att maximera lönsamheten?

Företagen måste ofta ta reda på hur mycket av varje produkt som kan framställas per månad. I sin enklaste form inkluderar produkt kombinations problemet hur mycket av varje produkt som ska produceras under en månad för att maximera vinsterna. Produkt kombinationen måste vanligt vis följa följande villkor:

  • Produkt kombination kan inte använda fler resurser än vad som är tillgängligt.

  • Det finns ett begränsat behov för varje produkt. Vi kan inte producera mer av en produkt under en månad än efter frågan, eftersom överskotts produktionen slösas (till exempel ett ömtåligt läkemedel).

Vi löser nu det här exemplet med produkt kombinations problemet. Du hittar lösningen på problemet i filen prodmix. xlsx, som visas i Figur 27-1.

Bild av bok

Låt oss säga att vi jobbar för ett läkemedel som tillverkar sex olika produkter i sin anläggning. Produktionen av varje produkt kräver arbete och råmaterial. Rad 4 i bild 27-1 visar de timmars arbete som behövs för att producera en kilo av varje produkt och rad 5 visar den mängd råmaterial som behövs för att tillverka en kilo av varje produkt. Om du till exempel producerar en kilo produkt 1 måste sex timmars arbete och 3,2 kilo råmaterial. För varje läkemedel ges priset per pund på rad 6, styckkostnads priset per kilo i rad 7, och vinst avgiften per pund ges i rad 9. Produkt 2 säljer till exempel $11,00 per pund och ger en Styckkostnad på $5,70 per pund och bidrar till $5,30 vinst per pund. Månadens efter frågan för varje läkemedel ges på rad 8. Till exempel är efter frågan för produkt 3 1041 pund. Den här månaden finns 4500 timmars arbete och 1600 kilo råmaterial. Hur kan det här företaget maximera månads vinsten?

Om vi inte visste något om Excel problemlösa ren kan vi attackera det här problemet genom att skapa ett kalkyl blad för att spåra vinst och Resursanvändning som är kopplade till produkt kombinationen. Då kommer vi att använda test och fel för att variera produkt kombinationen för att optimera vinsten utan att använda mer arbets kraft eller råmaterial än vad som är tillgängligt, och utan att producera något läkemedel. Vi använder problemlösa ren i den här processen endast i test-och fel skedet. Problemlösa ren är en optimerings motor som gör det perfekt att söka efter en prov period.

En viktig lösning på produkt kombinations problemet är att effektivt beräkna resursanvändningen och vinsten som är kopplad till en given produkt kombination. Ett viktigt verktyg som vi kan använda för att göra beräkningen är funktionen produkt summa. Funktionen produkt Summa multiplicerar motsvarande värden i cell områden och Returnerar summan av värdena. Varje cell område som används i en produkt Summa-utvärdering måste ha samma dimensioner, vilket betyder att du kan använda produkt summa med två rader eller två kolumner, men inte med en kolumn och en rad.

Som ett exempel på hur vi kan använda funktionen produkt summa i vårt produkt kombinations exempel försöker vi att beräkna vår resursanvändning. Vår arbets förbrukning beräknas av

(Arbets kraft per kilo för läkemedel 1) * (läkemedel 1 kg producerad) +
(arbets kraft per kilo för läkemedel 2) * (läkemedel 2 kg producerad) +...
(Arbets kraft som används per kilo av läkemedel 6) * (läkemedel 6 pund producerade)

Vi kan beräkna arbets belastningen på ett mer omständligt sätt som D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + i2 * I4. På samma sätt kan råmaterial användning beräknas som D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + i2 * i5. Det är dock dags att ange dessa formler i ett kalkyl blad för sex produkter. Föreställ dig hur lång tid det tar om du arbetar med ett företag som produceras, till exempel 50-produkter hos anläggningen. Ett mycket enklare sätt att beräkna arbete och råmaterial användning är att kopiera från D14 till D15 formeln produkt Summa ($D $2: $I $2, D4: i4). Den här formeln beräknar D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + i2 * I4 (vilket är vår arbets krafts användning) men är mycket lättare att ange! Observera att jag använder $-tecknet med området D2: i2 så när jag kopierar formeln jag fortfarande sparar produkt kombinationen från rad 2. Formeln i cell D15 beräknar användning av råmaterial.

På liknande sätt bestäms vinsten av

(Läkemedel 1 vinst per pund) * (läkemedel 1 kg producerad) +
(vinst per kilo för läkemedel 2) * (läkemedel 2 kg producerad) +...
(För läkemedel 6 vinst per kilo) * (läkemedel 6 pund producerade)

Vinst beräknas enkelt i cell D12 med formeln produkt Summa (D9: i9, $D $2: $I $2).

Nu kan vi identifiera de tre komponenterna i vår produkt kombination problemlösa ren-modell.

  • Målcell.Vårt mål är att maximera vinsten (beräknas i cell D12).

  • Justerbara celler.Antalet producerade kilo för varje produkt (som listas i cell området D2: i2)

  • Villkor. Vi har följande begränsningar:

    • Använd inte mer arbete eller råmaterial än vad som är tillgängligt. Det betyder att värdena i cellerna D14: D15 (de använda resurserna) måste vara mindre än eller lika med värdena i cellerna F14: F15 (tillgängliga resurser).

    • Tillverka inte mer av ett läkemedel än vad som är i behov. D.v.s. värdena i cellerna D2: i2 (antal kilo producerade av varje läkemedel) måste vara mindre än eller lika med behovet för varje läkemedel (visas i cellerna D8: i8).

    • Vi kan inte producera ett negativt belopp av något läkemedel.

Jag visar hur du anger mål cellen, de justerbara cellerna och begränsningarna i problemlösa ren. Allt du behöver göra är att klicka på knappen Lös för att hitta en vinst-maximera produkt kombination!

Börja med att klicka på fliken data och klicka sedan på problemlösa ren i gruppen analys.

Obs!:  Som vi förklarat i kapitel 26 "en introduktion till optimering med Excel problemlösa ren", installeras problemlösa ren genom att klicka på Microsoft Office-knappen och sedan på Excel-alternativ följt av tillägg. Klicka på Excel-tillägg i listan hantera och markera kryss rutan problemlösa ren och klicka sedan på OK.

Dialog rutan parametrar för problemlösa ren visas, som du ser i Figur 27-2.

Bild av bok

Klicka i rutan Ange målcell och välj sedan vinst-cellen (cell D12). Klicka i rutan justerbara celler och peka sedan på området D2: i2, som innehåller mängden producerad kg för varje läkemedel. Dialog rutan bör nu se ut i 27-3.

Bild av bok

Nu är det dags att lägga till begränsningar för modellen. Klicka på knappen Lägg till. Dialog rutan Lägg till begränsning visas i Figur 27-4.

Bild av bok

Om du vill lägga till villkor för resursanvändning klickar du på rutan cell referens och väljer sedan området D14: D15. Välj < = i listan mellan. Klicka på rutan begränsning och markera sedan cell området F14: F15. Dialog rutan Lägg till begränsning bör nu se ut som figur 27-5.

Bild av bok

Vi har nu säkerställt att när problemlösa ren försöker använda olika värden för de justerbara cellerna är det bara kombinationer som uppfyller både D14< = F14 (arbets kraft som används är mindre än eller lika med arbete) och D15< = F15 (råmaterial som används är mindre än eller lika med tillgängligt för råmaterial) beaktas. Klicka på Lägg till för att ange villkor för efter frågan. Fyll i dialog rutan Lägg till begränsning enligt figur 27-6.

Bild av bok

Om du lägger till de här begränsningarna ser du till att när problemlösa ren försöker skilja olika kombinationer efter de värden som ändras av en cell, tas bara kombinationer som uppfyller följande parametrar:

  • D2< = D8 (den mängd som produceras av läkemedel 1 är mindre än eller lika med efter frågan på läkemedel 1)

  • E2< = E8 (mängden producerad för läkemedel 2 är mindre än eller lika med behovet för läkemedel 2)

  • F2< = F8 (den mängd som produceras av läkemedel 3 är mindre än eller lika med efter frågan för läkemedel 3)

  • G2< = G8 (det genererade beloppet för läkemedel 4 är mindre än eller lika med efter frågan på läkemedel 4)

  • H2< = H8 (den mängd som produceras av läkemedel 5 är mindre än eller lika med efter frågan på läkemedel 5)

  • I2< = i8 (den mängd som produceras av läkemedel 6 är mindre än eller lika med behovet för läkemedel 6)

Klicka på OK i dialog rutan Lägg till begränsning. Fönstret problemlösa ren bör se ut som figur 27-7.

Bild av bok

Vi anger villkoret att justerbara celler inte får vara negativ i dialog rutan alternativ för problemlösa ren. Klicka på knappen Alternativ i dialog rutan parametrar för problemlösa ren. Markera rutan anta linjär modell och anta att den inte är negativ, som visas i Figur 27-8 på nästa sida. Klicka på OK.

Bild av bok

Om du markerar kryss rutan anta att den inte är negativ ser problemlösa ren bara en kombination av justerbara celler där varje cell får ett icke-negativt värde. Nu har vi kontrollerat den linjära modell rutan eftersom produkt kombinations problemet är en särskild typ av problemlösning som kallas en linjär modell. En problemlösa ren-modell är linjär under följande förhållanden:

  • Målcell beräknas genom att addera villkoren i formuläret (justerbar cell) * (konstant).

  • Varje begränsning uppfyller det "linjära modell kravet". Det innebär att varje begränsning utvärderas genom att addera villkoren i formuläret (justerbar cell) * (konstant) och jämföra summan med en konstant.

Varför är detta problem linjärt med problemlösning? Vår Målcell (vinst) beräknas som

(Läkemedel 1 vinst per pund) * (läkemedel 1 kg producerad) +
(vinst per kilo för läkemedel 2) * (läkemedel 2 kg producerad) +...
(För läkemedel 6 vinst per kilo) * (läkemedel 6 pund producerade)

Den här beräkningen följer ett mönster där mål cellens värde härleds genom addition av villkoren i formuläret (justerbar cell) * (konstant).

Vårt arbets villkor utvärderas genom att jämföra det värde som härleds från (arbets kraft som används per kilo av läkemedel 1) * (läkemedel 1 kg producerad) + (arbets kraft som används per kilo av läkemedel 2) * (läkemedel 2 kg producerad) +... (Arbets tid Ed per kilo för läkemedel 6) * (läkemedel 6 kg producerad) till det tillgängliga arbetet.

Därför beräknas arbets villkoret genom att addera villkoren i formuläret (justerbar cell) * (konstant) och jämföra summan med en konstant. Både begränsningen för arbets tid och råmaterial uppfyller kravet på linjära modeller.

Våra efter fråge begränsningar har formen

(Läkemedel 1 produceras) < = (läkemedel 1 efter frågan)
(läkemedel 2 producerad) < = (läkemedel 2 efter frågan)
§
(läkemedel 6 producerad) < = (läkemedel 6 efter frågan)

Varje demand-villkor uppfyller även kravet på linjär modell, eftersom varje beräknas genom att adderas till villkoren i formuläret (justerbar cell) * (konstant) och jämförelse av summan till en konstant.

Har vi visat att vår produkt mix modell är en linjär modell, varför bör vi vara försiktig?

  • Om en modell för problemlösa ren är linjär och vi väljer anta linjär modell är problemlösa ren garanterat med att hitta den optimala lösningen för problemlösa ren-modellen. Om en modell för problemlösa ren inte är linjär kanske problemlösa ren kan hitta den optimala lösningen.

  • Om en problemlösa ren-modell är linjär och vi väljer anta linjär modell använder problemlösa ren en mycket effektiv algoritm (simplex-metoden) för att hitta modellens optimala lösning. Om en modell för problemlösa ren är linjär och vi inte väljer anta linjär modell använder problemlösa ren en väldigt ineffektiv algoritm (metoden GRG2) och kan ha svårt att hitta modellens optimala lösning.

När du har klickat på OK i dialog rutan alternativ för problemlösa ren återgår vi till den centrala dialog rutan problemlösa ren, som visas tidigare i Figur 27-7. När vi klickar på Lös returnerar problemlösa ren en optimal lösning (om en sådan finns) för vår produkt kombination. Som jag angav i kapitel 26 är en optimal lösning för produktens mix modell en uppsättning med justerbara cell värden (antal kilo producerad av varje läkemedel) som maximerar vinsten på alla genomförbara lösningar. En genomförbar lösning är en uppsättning celler som uppfyller alla villkor. Värdena för justerbara celler som visas i Figur 27-9 är en genomförbar lösning eftersom alla produktions nivåer är icke-negativa, att produktions nivåerna inte överskrider efter frågan och att resursanvändningen inte överskrider de tillgängliga resurserna.

Bild av bok

Värdena för justerbara celler som visas i Figur 27-10 på nästa sida representerar en ogenomförbar lösning av följande skäl:

  • Vi producerar mer av läkemedel 5 än efter frågan för det.

  • Vi använder mer arbete än vad som är tillgängligt.

  • Vi använder mer råmaterial än vad som är tillgängligt.

Bild av bok

När du har klickat på Lös kan problemlösa ren hitta den optimala lösningen som visas i Figur 27-11. Du måste välja Behåll problemlösning för att behålla de optimala värdena i kalkyl bladet.

Bild av bok

Vårt läkemedel företag kan maximera sin månatliga vinst till en nivå på $6 625,20 genom att producera 596,67 kilo av läkemedel 4, 1084 kilo av läkemedel 5 och inget av de andra narkotikaerna! Det går inte att avgöra om vi kan uppnå maximal vinst på $6 625,20 på annat sätt. Allt vi kan vara säkra på är att det inte går att göra mer än $6 627,20 den här månaden.

Antag att behovet för varje produkt måste uppfyllas. (Se kalkyl bladet inga genomförbarheten i filen prodmix. xlsx.) Vi måste sedan ändra våra begränsningar för efter frågan från D2: I2< = D8: i8 till D2: I2> = D8: i8. Det gör du genom att öppna problemlösa ren, välja D2: I2< = D8: i8-begränsningen och klicka sedan på Ändra. Dialog rutan Ändra begränsning, som visas i Figur 27-12, visas.

Bild av bok

Välj > = och klicka sedan på OK. Nu är vi säkra på att problemlösa ren bara kan ändra cell värden som uppfyller alla krav. När du klickar på Lös visas meddelandet "problemlösa ren hittade ingen genomförbar lösning". Det här meddelandet innebär inte att vi har gjort ett misstag i vår modell, men i stället för att det inte uppfyller alla produkter. Problemlösa ren berättar bara för oss att om vi vill uppfylla behovet för varje produkt måste vi lägga till mer arbete, mer råmaterial eller fler av båda.

Låt oss se vad som händer om vi tillåter obegränsad efter frågan för varje produkt och vi tillåter att negativa kvantiteter produceras för varje läkemedel. (Du kan se det här problemlösnings problemet i filen prodmix. xlsx.) Om du vill hitta den optimala lösningen för den här situationen öppnar du problemlösa ren, klickar på knappen Alternativ och avmarkerar rutan låt icke-negativ. I dialog rutan parametrar för problemlösa ren väljer du villkoret D2: I2< = D8: i8 och klickar sedan på ta bort för att ta bort villkoret. När du klickar på Lös returnerar problemlösa ren meddelandet "Ange cell värden konvergerar inte". Det här meddelandet innebär att om mål cellen ska maximeras (som i vårt exempel) finns det genomförbara lösningar med ovanligt stora Målcell. (Om mål cellen ska minimeras innebär meddelandet "Ange cell värden konvergerar inte" betyder det att det finns genomförbara lösningar med små mål cell värden.) I vår situation är det möjligt att med negativ produktion av ett läkemedel skapa resurser som kan användas för att producera stora mängder andra läkemedel. Med det här obegränsade behovet av detta kan vi göra obegränsade vinster. I en verklig situation kan vi inte ringa till oändligt. Om "Ange värden inte konvergerar" visas i korthet.

  1. Anta att vårt läkemedel företag kan köpa upp till 500 timmars arbete med $1 per timme jämfört med nuvarande arbets kostnad. Hur kan vi maximera vinst?

  2. Med fyra tekniker (A, B, C och D) producerar de tre produkterna (produkter 1, 2 och 3). Denna månad kan kretskorts tillverkaren sälja 80 enheter av produkt 1, 50 enheter av produkt 2 och högst 50 enheter av produkt 3. Tekniker A kan bara göra produkter 1 och 3. Tekniker B kan bara ringa produkter 1 och 2. Tekniker C kan bara göra produkt 3. Tekniker D kan bara göra produkt 2. För varje tillverkad enhet bidrar produkterna med följande vinst: produkt 1, $6; Produkt 2, $7; och produkt 3, $10. Tiden (i timmar) som varje tekniker behöver för att tillverka en produkt är följande:

    Produkt

    Tekniker A

    Tekniker B

    Tekniker C

    Tekniker D

    1

    2

    2,5

    Kan inte göra

    Kan inte göra

    2

    Kan inte göra

    3

    Kan inte göra

    3,5

    3

    3

    Kan inte göra

    4

    Kan inte göra

  3. Varje tekniker kan arbeta upp till 120 timmar per månad. Hur kan man maximera månads vinsten? Antag att ett bråktal med enheter kan produceras.

  4. En dator tillverknings anläggning tillverkar möss, tangent bord och styrspakar för video spel. Täckning per enhet, arbets tid per enhet, månatlig efter frågan och per enhet anges i följande tabell:

    Mus

    Keyboard

    Styrspakar

    TB

    $8

    $11

    $9

    Arbets förbrukning/enhet

    .2 timme

    .3 timmar

    .24-timmarsformat

    Maskin tid/enhet

    .04 timme

    .055 timme

    .04 timme

    Månatlig efter frågan

    150 000

    27 000

    11 000

  5. Varje månad är totalt 13 000 arbets tid och 3000 timmar med maskin tid tillgängliga. Hur kan tillverkaren maximera månads vinsten från anläggningen?

  6. Lös vårt narkotika exempel förutsatt att en minsta efter frågan på 200 enheter för varje läkemedel måste uppfyllas.

  7. Jason gör Diamond Bracelets, Necklaces och örhängen. Han vill arbeta högst 160 timmar per månad. Han har 800 gram diamanter. Vinst, arbets tid och gram som behövs för att tillverka varje produkt visas nedan. Hur kan Jason maximera sin vinst om behovet av varje produkt är obegränsat?

    Produkt

    Enhets vinst

    Arbets tid per enhet

    Gram diamanter per enhet

    Bracelet

    300 kr

    .35

    1,2

    Necklace

    200 kr

    .15

    .75

    Örhängen

    100 kr

    .05

    0,5

Utöka dina Office-kunskaper
Utforska utbildning
Få nya funktioner först
Anslut till Office Insiders

Hade du nytta av den här informationen?

Tack för din feedback!

Tack för din feedback! Det låter som att det kan vara bra att koppla dig till en av våra Office-supportrepresentanter.

×