Hur du använder Problemlösaren för att fastställa optimal produkt mix

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.

Den här artikeln beskrivs hur du använder Problemlösaren, en Microsoft Excel-tilläggsprogram du kan använda för konsekvensanalys, att ta reda på ett optimalt produkt blandning.

Hur kan jag avgöra vilken produktkombination som maximerar lönsamheten?

Företagen behöver ofta mängd av varje produkt att producera på månadsbasis. I sin enklaste form handlar problemet produkt blanda så avgör hur mycket varje produkt som ska produceras per månad att maximera vinsten. Produkt blandning måste vanligtvis uppfylla följande villkor:

  • Produkt blandning kan inte använda fler resurser än vad som är tillgängliga.

  • Det finns en begränsad begäran för varje produkt. Vi kan inte producera fler av en produkt under en månad än begäran avgör eftersom överskottsproduktionen kasseras (till exempel en ömtåliga läkemedel).

Låt oss lösa följande exempel på produktkombinationsproblemet. Du kan hitta lösning på problemet i filen Prodmix.xlsx, som visas i bild 27-1.

Bild av bok
Figur 27-1 produkt mix

Anta att vi arbetar på ett läkemedelsföretag som ger sex olika produkter på sin anläggning. Tillverkning av varje produkt kräver arbete och råmaterial. Rad 4 i bild 27-1 visar antalet arbetstimmar som behövs för att producera en fyrkant för varje produkt och rad 5 pund råmaterial som behövs för att producera en fyrkant för varje produkt. Till exempel kräver producerar en fyrkant av produkt 1 sex arbetstimmar och 3,2 pund råmaterial. Pris per pund anges på rad 6 för respektive läkemedel, enhetskostnad per pund anges på rad 7 och vinsten per pund anges på rad 9. Till exempel produkt 2 säljer för $11,00 per pund, har en enhetskostnad av $5,70 per pund och bidrar $5.30 vinst per pund. Den månadens begäran för respektive läkemedel anges på rad 8. Begäran för produkt 3 är exempelvis 1 041 kilo. Den här månaden, är 4500 arbetstimmar och 1 600 pund råmaterial som tillgängliga. Hur kan företaget maximera månadsvinsten?

Om vi kände till verktyget Problemlösaren i Excel, skulle vi attackerar problemet genom att skapa ett kalkylblad om du vill spåra vinst- och Resursanvändning som är associerad med produkt blandning. Sedan använder vi pröva varierar produkt blandning om du vill optimera vinst utan att använda mer arbete eller råmaterial än vad som är tillgängliga och utan att tillverka något läkemedel utöver begäran. Vi använder Problemlösaren i den här processen endast på scenen utvärderingsversion och fel. Problemlösaren kan en optimering motor som felfritt utför sökningen utvärderingsversion och fel.

En tangent för att lösa produktkombinationsproblemet är att effektivt beräkna Resursanvändning och vinst som är kopplad till en viss produkt blandning. Ett viktigt verktyg som vi kan använda för den här beräkningen är funktionen PRODUKTSUMMA. Funktionen PRODUKTSUMMA Multiplicerar motsvarande värden i cellområden och returnerar summan av dessa värden. Varje cellområdet som används i en PRODUKTSUMMA-beräkning måste ha samma dimensioner, vilket innebär att du kan använda PRODUKTSUMMA med två rader och två kolumner, men inte med en kolumn och en rad.

Blanda som ett exempel på hur vi kan använda funktionen PRODUKTSUMMA i vårt exempel ska vi prova att beräkna vår resursanvändning. Vår arbete användningen beräknas genom

(Arbete används per pund av läkemedel 1) *(Drug 1 pounds produced) +
(arbete används per pund av läkemedel 2) * (antal 2 pund tillverkats) +...
(Arbete används per pund av läkemedel 6) * (antal 6 pund tillverkats)

Vi kan beräkna användningen av arbete i en längre tid att utföra sätt som D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. På samma sätt användningen av råmaterial kan beräknas som D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. Ange följande formler på ett kalkylblad för sex produkter är dock mer tidskrävande. Förstå hur lång tid det tar om du arbetade med ett företag som tillverkat, till exempel 50 produkter på sin anläggning. Ett mycket enklare sätt att beräkna arbete och användningen av råmaterial är att kopiera från D14 till D15 formeln SUMPRODUCT($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årt arbete användningen), men är mycket enklare att ange! Observera att $-tecknet med cellområdet D2: I2 så att när jag kopierar formeln jag ändå spela in produkt blandning från rad 2. Formeln i cell D15 beräknar användningen av råmaterial.

På liknande sätt bestäms vår vinst av

(Antal 1 TB per pund) * (läkemedel 1 pund tillverkas) +
(läkemedel 2 TB per pund) * (antal 2 pund tillverkats) +...
(Läkemedel 6 vinst per pund) * (antal 6 pund tillverkats)

Vinsten kan enkelt beräknas i cell D12 med formeln SUMPRODUCT(D9:I9,$D$2:$I$2).

Vi kan nu identifiera tre komponenterna i vår produkt blandning Problemlösaren modellen.

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

  • Justerbara celler. Antal pund produceras av varje produkt (som visas i cellområdet D2: I2)

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

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

    • Ge inte av läkemedel än i begäran. Det vill säga måste värdena i cellerna D2: I2 (pund produceras av respektive läkemedel) vara mindre än eller lika med begäran för respektive läkemedel (som visas i cellerna D8: I8).

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

Jag lära dig hur du anger målcellen, justerbara celler och begränsningarna till Problemlösaren. Allt du behöver göra är sedan klicka på Lös om du vill söka efter en produkt blandning att maximera vinsten!

Om du vill starta, klicka på fliken Data och klicka på Problemlösaren i gruppen analys.

Obs!: Enligt anvisningarna i kapitel 26, ”en introduktion till optimering med Problemlösaren i Excel”, installerat Problemlösaren genom att klicka på Microsoft Office-knappen, sedan Excel-alternativ, följt av tillägg. Klicka på Excel-tillägg i listan hantera, markerar du rutan tillägget Problemlösaren och klicka sedan på OK.

Dialogrutan Parametrar för Problemlösaren visas som visas i bild 27-2.

Bild av bok
Bild 2-27 i parametrar för Problemlösaren dialogrutan.

Klicka på rutan Målcell och markera sedan vår vinst cell (cell D12). Klicka på rutan justerbara celler och peka på cellområdet D2: I2 som innehåller pund produceras av varje läkemedel. Dialogrutan bör nu se bild 27-3.

Bild av bok
Bild 27-3 i Scenariohanteraren med målcell och justerbara celler definierade

Nu är det dags att lägga till villkor i modellen. Klicka på knappen Lägg till. Du ser dialogrutan Lägg till begränsning visas i bild 27-4.

Bild av bok
Dialogrutan för bild 27-4 i Lägg till begränsning

Om du vill lägga till begränsningar för Resursanvändning, klicka på rutan cellreferens och markera sedan området D14: D15. Välj < = i mitten listan. Klicka på rutan begränsning och markera cellområdet F14: F15. Dialogrutan Lägg till begränsning bör nu se ut som bild 27-5.

Bild av bok
Figur 5-27 i Lägg till begränsning dialogrutan med de angivna begränsningar för Resursanvändning

Vi har nu försäkrat att när Problemlösaren provar olika värden för den ändra celler, endast kombinationer som uppfyller båda D14 < = F14 (arbete som används är mindre än eller lika med arbete som är tillgängliga) och D15 < = F15 (råvarutypen är mindre än eller lika med tillgängligt råmaterial) betraktas. Klicka på Lägg till om du vill ange begränsningar för begäran. Fyll i dialogrutan Lägg till begränsning som du ser i bild 27-6.

Bild av bok
Dialogrutan bild 27-6 fältet Lägg till begränsning med angivna begränsningar för begäran

Lägga till de här begränsningarna säkerställer att när Problemlösaren provar olika kombinationer för justerbara cellvärden, kombinationer som uppfyller följande parametrar betraktas:

  • D2 < = D8 (mängd produceras av läkemedel 1 är mindre än eller lika med begäran för läkemedel 1)

  • E2 < = E8 (den produceras av läkemedel 2 uppgår mindre än eller lika med begäran för läkemedel 2)

  • F2 < = F8 (mängd produceras av läkemedel 3 gjort är mindre än eller lika med begäran om läkemedel 3)

  • G2 < = G8 (mängd produceras av läkemedel 4 gjort är mindre än eller lika med begäran om läkemedel 4)

  • H2 < = H8 (mängd produceras av läkemedel 5 gjort är mindre än eller lika med behovet av läkemedel 5)

  • I2 < = I8 (mängd produceras av läkemedel 6 gjort är mindre än eller lika med begäran för läkemedel 6)

Klicka på OK i dialogrutan Lägg till begränsning. Fönstret Problemlösaren bör se ut som bild 27-7.

Bild av bok
Bild 27-7 slutliga dialogrutan Parametrar för Problemlösaren för produktkombinationsproblemet

Vi anger du den begränsning som justerbara celler måste vara negativt i dialogrutan Alternativ för Problemlösaren. Klicka på knappen Alternativ i dialogrutan Parametrar för Problemlösaren. Kontrollera rutan linjär modell och rutan anta icke-negativ som visas i bild 27-8 på nästa sida. Klicka på OK.

Bild av bok
Figur 27-8 Problemlösaren alternativinställningar

Markera rutan anta icke-negativ säkerställer att Problemlösaren finner endast kombinationer av justerbara celler där samtliga justerbara celler innehåller ett negativt värde. Vi har checkats rutan linjär modell eftersom produkten blanda problemet är en särskild typ av problem med Problemlösaren kallas en linjär modell. I princip är en linjär följande villkor:

  • Målcellen beräknas genom att lägga till termer i formuläret (ändra cell)*(konstant).

  • Varje begränsning uppfyller ”linjär modell kravet”. Detta innebär att varje begränsning utvärderas genom att lägga till termer i formuläret (ändra cell)*(konstant) och jämföra summor med en konstant.

Varför är vårt problem linjärt? Vår Målcell (vinst) beräknas som

(Antal 1 TB per pund) * (läkemedel 1 pund tillverkas) +
(läkemedel 2 TB per pund) * (antal 2 pund tillverkats) +...
(Läkemedel 6 vinst per pund) * (antal 6 pund tillverkats)

Den här beräkningen följer ett mönster som mål cellvärdet härleds genom att lägga till termer i formuläret (ändra cell)*(konstant).

Vårt exempel utvärderas begränsningen genom att jämföra värdet från (arbete används per pund av läkemedel 1) * (läkemedel 1 pund tillverkas) + (arbete används per pund av läkemedel 2) *(Drug 2 pounds produced) +... (Arbete ossErik per pund av läkemedel 6) * (antal 6 pund tillverkats) till arbete som är tillgängliga.

Därför begränsningen utvärderas genom att lägga till termer i formuläret (ändra cell)*(konstant) och jämföra summor med en konstant. Begränsningen och villkoret råmaterial uppfyller kravet för linjära modeller.

Vår begäran villkor ta formuläret

(Läkemedel 1 tillverkats) < = (läkemedel 1 begäran)
(läkemedel 2 tillverkats) < = (läkemedel 2 begäran)
§
(läkemedel 6 tillverkats) < = (läkemedel 6 begäran)

Varje begäran begränsning uppfyller också kravet för linjära modeller eftersom varje utvärderas genom att lägga till termer i formuläret (ändra cell)*(konstant) och jämföra summor med en konstant.

Har visat att produktkombinationsmodellen är en linjär modell, därför ska vi bry dig?

  • Om en är linjär och vi väljer linjär modell är Problemlösaren säkert att hitta optimala lösning i Problemlösaren modellen. Om en problemlösarmodell inte linjär Problemlösaren kan eller kan inte hitta den bästa lösningen.

  • Om en är linjär och vi väljer linjär modell används Problemlösaren en väldigt effektiv algoritm (simplex-metoden) i modellen optimal lösning. Om en är linjär och vi inte väljer linjär modell Problemlösaren använder en mycket ineffektiv algoritm (GRG2-metoden) och kanske har svårt att hitta i modellen optimal lösning.

Klicka på OK i dialogrutan Alternativ, återgå till den huvudsakliga Problemlösaren i dialogrutan visas tidigare i bild 27-7. När du klickar på Lös beräknar Problemlösaren en optimal lösning (om det finns) för produktkombinationsmodellen. När jag anges i kapitel 26 är en optimal lösning i produkten blandning modellen en uppsättning värden för justerbara celler (pund produceras av respektive läkemedel) som maximerar vinst över alla möjliga lösningar. En möjlig lösning är igen, en uppsättning värden för justerbara celler uppfyller samtliga villkor. Justerbara cellvärden som visas i bild 27-9 är en möjlig lösning eftersom alla produktionsnivåer är negativt, skarp nivåer inte överskrider begäran och Resursanvändning inte överstiga tillgängliga resurser.

Bild av bok
Figur 27-9 A möjlig lösning på blanda där alla villkor uppfylls.

Justerbara cellvärden som visas i bild 27-10 på nästa sida representerar en ogenomförbar lösning av följande anledningar:

  • Vi spannmål av läkemedel 5 än behovet.

  • 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
Figur 10-27 passar ogenomförbar lösning på produktkombinationsproblemet inte inom definierade villkor.

Vilket Problemlösaren finner snabbt den bästa lösningen visas i bild 27-11 när jag klickar på Lös. Du måste markera Behåll Problemlösarens lösning för att bevara optimal lösning värden i kalkylbladet.

Bild av bok
Figur 27-11 den optimala lösningen på produktkombinationsproblemet

Vårt läkemedelsföretag kan maximera månadsvinsten på en nivå i $6,625.20 genom producerar 596,67 pund läkemedel 4, 1 084 pund av läkemedel 5 och inga andra läkemedel! Vi kan inte avgöra om vi uppnår maximal vinst för $6,625.20 på andra sätt. Allt vi kan vara säker på är att med våra begränsade resurser och begäran, det finns inget sätt att få mer än $6,627.20 den här månaden.

Anta att begäran för varje produkt måste uppfyllas. (Finns Ingen möjlig lösning kalkylblad i filen Prodmix.xlsx.) Vi sedan behöver ändra vår begäran villkor från D2: I2 < = D8: I8 till D2: I2 > = D8: I8. Så här: öppna Problemlösaren, Välj den D2: I2 < = D8: I8 villkoret och klicka sedan på Ändra. Dialogrutan Ändra begränsning visas i bild 27-12, visas.

Bild av bok
Dialogrutan för figur 27-12-fältet Ändra begränsning

Markera > = och klicka sedan på OK. Nu har vi försäkrat när Problemlösaren följa ändra endast cellvärden som uppfyller alla behov. När du klickar på Lös ser du meddelandet ”Problemlösaren kan inte hitta en möjlig lösning”. Det här meddelandet innebär inte att vi har gjort ett misstag i vår modellen, men som med våra begränsade resurser, vi kan inte uppfyller behovet av alla produkter. Problemlösaren informerar oss bara om att vi behöver lägga till mer arbete, mer råmaterial eller både om vi vill gärna uppfyller begäran för varje produkt.

Nu ska vi se vad som händer om vi tillåter obegränsad begäran för varje produkt och vi tillåta negativa quantities ska produceras av varje läkemedel. (Du kan visa problemet Problemlösaren i kalkylbladet Ange konvergerar inte värden i filen Prodmix.xlsx.) Öppna Problemlösaren för att hitta den bästa lösningen för det här fallet, klickar du på knappen Alternativ och avmarkera rutan anta icke-negativ. I dialogrutan Parametrar för Problemlösaren Markera begäran begränsning D2: I2 < = D8: I8 och klicka sedan på Ta bort för att ta bort villkoret. När du klickar på Lös returnerar Problemlösaren meddelandet ”ange cellvärden konvergerar inte”. Det här meddelandet innebär att om målcellen ska maximeras (som i vårt exempel) det är möjligt lösningar med stora godtyckliga målcellvärden cellvärden. (Om målcellen ska minimeras meddelandet ”ange Cell värden konvergerar inte” betyder att det är möjligt lösningar med små godtyckliga målcellvärden cellvärden.) I vårt fall, skapar genom att låta negativa tillverkning av ett läkemedel, gäller ”vi” resurser som kan användas för att producera stora godtyckliga mängder av andra läkemedel. Möjlighet att vår obegränsad begäran ser gör det här att vi kan obegränsad vinst. Vi kan inte skapa obegränsade pengar i en verklig situation. Kortfattat om du ser ”ange värden konvergerar inte” har modellen ett fel.

  1. Anta att vårt läkemedelsföretag kan köpa upp till 500 arbetstimmar på $1 mer än den aktuella arbetskostnader per timme. Hur kan vi maximera vinsten?

  2. Vid en krets tillverkning växt spannmål fyra tekniker (A, B, C och D) tre produkter (produkterna 1, 2 och 3). Den här månaden, kan Chiptillverkaren sälja 80 enheter av produkt 1, 50 enheter av produkt 2 och högst 50 enheter av produkt 3. Tekniker A kan göra produkter 1 och 3. Tekniker B kan göra produkter 1 och 2. Tekniker C kan göra endast produkt 3. Tekniker D kan göra endast produkt 2. För varje enhet tillverkats produkterna bidra följande vinst: produkt 1, $6. Produkt 2, $7. och produkt 3, $10. Tid (i timmar) varje tekniker som behövs att tillverka en produkt är följande:

    Produkt

    Tekniker A

    Tekniker B

    Tekniker C

    Tekniker D

    1

    2

    2,5

    Kan jag inte göra

    Kan jag inte göra

    2

    Kan jag inte göra

    3

    Kan jag inte göra

    3,5

    3

    3

    Kan jag inte göra

    4

    Kan jag inte göra

  3. Varje tekniker kan arbeta upp till 120 timmar per månad. Hur kan Chiptillverkaren maximera månadsvinsten? Anta ett bråktal antal enheter kan produceras.

  4. En dator som tillverkning växt ger mus, tangentbord och video spelet styrspak. Vinst/per enhet, per enhet arbete användningen, månatliga begäran och per enhet time machine användningen anges i tabellen nedan:

    Mus

    Tangentbord

    Styrspak

    Vinst/enhet

    $8

    $11

    $9

    Arbete/enhet

    .2 timme

    .3 timme

    .24 timme

    Machine/tidsenhet

    .04 timme

    .055 timme

    .04 timme

    Månatliga begäran

    15 000

    27 000

    11 000

  5. Varje månad totalt 13 000 arbetstimmar och 3 000 timmar machine tidsperiod är tillgängliga. Hur kan tillverkaren maximera dess månatliga vinst bidrag från växten?

  6. Lösa vår läkemedel exempel under antagandet att en minsta begäran av 200 enheter för respektive läkemedel måste uppfyllas.

  7. Jason gör romben armband, halsband och diamantörhängen. Han vill arbeta högst 160 timmar per månad. Han har 800 ounces romber. Vinst, arbete tid och ounces av romber som krävs för att producera varje produkt anges nedan. Om begäran för varje produkt är obegränsad, hur kan Jason maximera vinsten?

    Produkt

    Vinst/enhet

    Arbetstimmar per enhet

    Ounces av romber per enhet

    Bracelet

    300 kr

    35 kronor

    1,2

    Necklace

    200 USD

    .15

    tjocklek på.75

    Diamantörhängen

    100 kr

    .05

    .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.

×