Använda problemlösa ren för att ta reda på 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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.