Använda Problemlösaren för att fastställa den optimala produktmixen

Hur kan jag fastställa den månatliga produktmixen som maximerar lönsamheten?

Företag behöver ofta bestämma hur mycket varje produkt som ska tillverkas på månadsbasis. I sin enklaste form innebär produktmixproblemet hur man bestämmer beloppet för varje produkt som ska produceras under en månad för att maximera vinsten. Produktmix måste vanligtvis följa följande villkor:

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

• Efterfrågan på varje produkt är begränsad. Vi kan inte producera mer av en produkt under en månad än efterfrågan dikterar, eftersom överskottsproduktionen är bortkastad (till exempel ett förgängligt läkemedel).

Nu ska vi lösa följande exempel på problemet med produktmixen. Du kan hitta lösningen på det här problemet i filen Prodmix.xlsx, som visas i bild 27-1.

Låt oss säga att vi arbetar för ett läkemedelsföretag som producerar sex olika produkter på sin anläggning. Produktionen av varje produkt kräver arbete och råmaterial. Rad 4 i figur 27-1 visar antalet timmars arbete som behövs för att producera ett kilo av varje produkt, och rad 5 visar de pund av råmaterial som behövs för att producera ett pund av varje produkt. Att producera ett kilo produkt 1 kräver till exempel sex timmars arbete och 3,2 pund råmaterial. För varje läkemedel ges priset per pund på rad 6, enhetskostnaden per pund ges på rad 7 och vinstbidraget per pund ges på rad 9. Till exempel, Produkt 2 säljer för $ 11,00 per pund, ådrar sig en enhetskostnad på $ 5,70 per pund och bidrar $ 5.30 vinst per pund. Månadens efterfrågan på varje läkemedel ges på rad 8. Efterfrågan på Produkt 3 är till exempel 1 041 pund. Denna månad, 4500 timmars arbete och 1600 pounds av råmaterial finns tillgängliga. Hur kan det här företaget maximera sin månadsvinst?

Om vi inte visste något om Excel-problemlösaren angrep vi det här problemet genom att skapa ett kalkylblad för att spåra vinst- och resursanvändning som är kopplad till produktmixen. Då skulle vi använda försök och fel för att variera produktblandningen för att optimera vinsten utan att använda mer arbetskraft eller råmaterial än vad som är tillgängligt, och utan att producera något läkemedel över efterfrågan. Vi använder Problemlösaren i den här processen endast i steget försök och fel. I grund och botten är Problemlösaren en optimeringsmotor som felfritt utför test- och felsökningen.

En nyckel till att lösa problemet med produktmixen är att effektivt beräkna resursanvändningen och vinsten i samband med en viss produktmix. Ett viktigt verktyg som vi kan använda för att göra 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åde som används i en PRODUKTSUMMA utvärdering måste ha samma dimensioner, vilket innebär att du kan använda PRODUKTSUMMA med två rader eller två kolumner, men inte med en kolumn och en rad.

Som exempel på hur vi kan använda funktionen PRODUKTSUMMA i vårt exempel på produktmix ska vi försöka beräkna vår resursanvändning. Vår arbetsanvändning beräknas av

(Arbete som används per pund av drogen 1)*(Drog 1 pounds produceras)+
(Arbete som används per pund av läkemedel 2)*(Drug 2 pounds produceras) + ...
(Arbete som används per pund av drogen 6)*(Drog 6 pounds produceras)

Vi kan beräkna arbetsanvändningen 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 användningen av råmaterial beräknas som D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Det är dock tidskrävande att ange de här formlerna i ett kalkylblad för sex produkter. Tänk dig hur lång tid det skulle ta om du arbetade med ett företag som till exempel producerade 50 produkter vid sin anläggning. Ett mycket enklare sätt att beräkna användningen av arbete och råmaterial är att kopiera formeln från D14 till D15 PRODUKTSUMMA($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 arbetsanvändning) men är mycket enklare att komma in i! Observera att jag använder $-tecknet med området D2:I2 så att när jag kopierar formeln fångar jag fortfarande produktblandningen från rad 2. Formeln i cell D15 beräknar råmaterialanvändningen.

På liknande sätt avgörs vår vinst av

(Drug 1 vinst per pund)*(Drug 1 pounds producerad) +
(Drug 2 vinst per pund)*(Drug 2 pounds produceras) + ...
(Drug 6 vinst per pund)*(Drug 6 pounds producerad)

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

Nu kan vi identifiera de tre komponenterna i vår produktmix Problemlösaren.

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

• Ändra celler. Antalet pund som produceras av varje produkt (anges i cellområdet D2:I2)

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

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

  • Producera inte mer av ett läkemedel än vad som efterfrågas. Det vill: värdena i cellerna D2:I2 (pounds produceras av varje läkemedel) måste vara mindre än eller lika med efterfrågan på varje läkemedel (anges i cellerna D8:I8).

  • Vi kan inte producera en negativ mängd av något läkemedel.

Jag ska visa dig hur du anger målcellen, ändrar celler och begränsningar i Problemlösaren. Då allt du behöver göra är att klicka på knappen Lös för att hitta en vinstmaximerande produktmix!

Börja genom att klicka på fliken Data och sedan på Problemlösaren i gruppen Analys.

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

Klicka på rutan Ange målcell och markera sedan vår vinstcell (cell D12). Klicka på rutan Genom att ändra celler och peka sedan på området D2:I2, som innehåller de pund som produceras av varje läkemedel. Dialogrutan ska nu se ut som Bild 27-3.

Vi är nu redo att lägga till villkor i modellen. Klicka på knappen Lägg till. Dialogrutan Lägg till villkor visas i Bild 27-4.

Om du vill lägga till begränsningar för resursanvändning klickar du på rutan Cellreferens och markerar sedan området D14:D15. Välj <= i listan i mitten. Klicka på rutan Villkor och markera sedan cellområdet F14:F15. Dialogrutan Lägg till villkor ska nu se ut som Bild 27-5.

Vi har nu säkerställt att när Problemlösaren provar olika värden för justerbara celler kommer endast kombinationer som uppfyller både D14<=F14 (arbete som används är mindre än eller lika med tillgängligt arbete) och D15<=F15 (råmaterial som används är mindre än eller lika med tillgängligt råmaterial) att beaktas. Klicka på Lägg till för att ange villkor för efterfrågan. Fyll i dialogrutan Lägg till villkor enligt bild 27-6.

Genom att lägga till dessa villkor ser du till att när Problemlösaren provar olika kombinationer för de justerbara cellvärdena beaktas endast kombinationer som uppfyller följande parametrar:

• D2<=D8 (mängden som produceras av Drug 1 är mindre än eller lika med efterfrågan på Drug 1)

• E2<=E8 (mängden producerad av narkotika 2 är mindre än eller lika med efterfrågan på narkotika 2)

• F2<=F8 (mängden som produceras av läkemedel 3 är mindre än eller lika med efterfrågan på narkotika 3)

• G2<=G8 (mängden som produceras av läkemedel 4 är mindre än eller lika med efterfrågan på läkemedel 4)

• H2<=H8 (mängden som produceras av läkemedel 5 är mindre än eller lika med efterfrågan på narkotika 5)

• I2<=I8 (mängden producerad av läkemedel 6 är mindre än eller lika med efterfrågan på Drug 6)

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

Vi anger villkoret att justerbara celler måste vara icke-negativa i dialogrutan Alternativ för Problemlösaren. Klicka på knappen Alternativ i dialogrutan Parametrar för Problemlösaren. Markera kryssrutan Anta linjär modell och rutan Anta att den inte är negativ, så som visas i Bild 27-8 på nästa sida. Klicka på OK.

Om du markerar rutan Antag icke-negativ ser du till att Problemlösaren endast tar hänsyn till kombinationer av justerbara celler där varje justerbar cell har ett icke-negativt värde. Vi har kontrollerat rutan Anta linjär modell eftersom problemet med produktmix är en särskild typ av problem med Problemlösaren som kallas linjär modell. I grund och botten är en Problemlösaren-modell linjär under följande förhållanden:

• Målcellen beräknas genom att villkoren i formuläret läggs ihop (ändra cell)*(konstant).

• Varje villkor uppfyller det "linjära modellkravet". Det innebär att varje villkor utvärderas genom att villkoren i formuläret (justerbar cell)*(konstant) adderas och summorna jämförs med en konstant.

Varför är problemlösningen linjär? Vår målcell (vinst) beräknas som

(Drug 1 vinst per pund)*(Drug 1 pounds producerad) +
(Drug 2 vinst per pund)*(Drug 2 pounds produceras) + ...
(Drug 6 vinst per pund)*(Drug 6 pounds producerad)

Den här beräkningen följer ett mönster där målcellens värde härleds genom att termer för formuläret (justerbar cell)*(konstant) adderats.

Vår arbetskraftsbegränsning utvärderas genom att jämföra värdet som härleds från (Arbete som används per pund av Drug 1)*(Drug 1 pounds producerad) + (Arbete som används per pund av Drug 2)*(Drug 2 pounds producerad)+ ... (Labor ossed per pund av Drug 6)*(Drug 6 pounds producerad) till det tillgängliga arbetet.

Därför utvärderas villkoret arbete genom att addera villkoren i formuläret (ändra cell)*(konstant) och jämföra summorna med en konstant. Både villkoret för arbete och råmaterial uppfyller det linjära modellkravet.

Våra behovsbegränsningar är i

(Drog 1 producerad)<=(Drug 1 Demand)
(Drog 2 producerad)<=(Drug 2 Demand)
§
(Drog 6 producerad)<=(Drug 6 Demand)

Varje behovsvillkor uppfyller också det linjära modellkravet, eftersom var och en utvärderas genom att summera villkoren i formuläret (ändra cell)*(konstant) och jämföra summorna med en konstant.

Efter att ha visat att vår produktmixmodell är en linjär modell, varför ska vi bry oss?

• Om en problemlösarmodell är linjär och vi väljer Anta linjär modell, är Problemlösaren garanterat att hitta den optimala lösningen på Problemlösaren. Om en problemlösarmodell inte är linjär kanske Problemlösaren hittar den optimala lösningen eller inte.

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

När du har klickat på OK i dialogrutan Alternativ för Problemlösaren återgår vi till huvuddialogrutan för Problemlösaren, som visas tidigare i bild 27-7. När vi klickar på Lösa beräknar Problemlösaren en optimal lösning (om det finns någon) för vår produktmixmodell. Som jag sade i kapitel 26, en optimal lösning på produktmix modellen skulle vara en uppsättning justerbara cellvärden (pounds produceras av varje läkemedel) som maximerar vinsten över uppsättningen av alla genomförbara lösningar. Återigen är en genomförbar lösning en uppsättning justerbara cellvärden som uppfyller alla begränsningar. De justerbara cellvärdena som visas i bild 27-9 är en genomförbar lösning eftersom alla produktionsnivåer inte är negativa, produktionsnivåerna inte överskrider efterfrågan och resursanvändningen inte överskrider tillgängliga resurser.

De justerbara cellvärdena som visas i Bild 27-10 på nästa sida utgör en ogenomförbar lösning av följande orsaker:

• Vi producerar mer av Drug 5 än efterfrågan på det.

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

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

När du har klickat på Problemlösaren hittar problemlösaren snabbt den optimala lösningen i bild 27-11. Du måste välja Behåll problemlösarens lösning för att bevara optimala lösningsvärden i kalkylbladet.

Vårt läkemedelsföretag kan maximera sin månatliga vinst på en nivå av $ 6,625.20 genom att producera 596.67 pounds av Drug 4, 1084 pounds av Drug 5, och ingen av de andra drogerna! Vi kan inte avgöra om vi kan uppnå den maximala vinsten på $ 6,625.20 på andra sätt. Allt vi kan vara säkra på är att med våra begränsade resurser och efterfrågan finns det inget sätt att tjäna mer än $ 6,627.20 den här månaden.