Brug Problemløser til at bestemme den optimale produktblanding

Spørgsmål besvaret i denne artikel:

• Hvordan kan jeg bestemme den månedlige produktblanding, der maksimerer overskuddet?
• Har en Problemløser-modellen altid en løsning?
• Hvad betyder det, hvis en Problemløser-model returnerer resultatet Værdier i målsætningen konvergerer ikke?

Øvelsesfil

Svar på spørgsmålene i denne artikel

Hvordan kan jeg bestemme den månedlige produktblanding, der maksimerer overskuddet?

Virksomheder har ofte brug for at bestemme mængden af hvert produkt, der skal produceres månedligt. I sin enkleste form involverer produktblandingsproblemet, hvordan man bestemmer mængden af hvert produkt, der skal produceres i løbet af en måned for at maksimere overskuddet. Produktbladingen skal normalt overholde følgende begrænsninger

• Produktblandingen kan ikke bruge flere ressourcer, end der er tilgængelige.
• Der er en begrænset efterspørgsel på hvert produkt. Du ønsker ikke at producere mere af et produkt i løbet af en måned, end efterspørgslen dikterer, fordi den overskydende produktion er spildt (som i tilfældet med et letfordærveligt vare).

Lad os nu løse følgende produktblandingsproblem. Du kan finde en mulig løsning på dette problem i regnearket Mulig løsning i filen Produktblanding.xlsx, vist i figuren. Prøveværdier for den producerede mængde af hvert lægemiddel er angivet i række 2.

Lad os sige, at du arbejder for et lægemiddelfirma, der producerer seks forskellige produkter. For hvert produkt har du brug for arbejdskraft og råmateriale. Række 4 i figuren viser de arbejdstimer, der er nødvendige for at producere et kilo af hvert produkt, og række 5 viser de kilo råmateriale, der er nødvendige for at producere et kilo af hvert produkt. For eksempel kræver produktion af et kilo af produkt 1 seks timers arbejdskraft og 3,2 kilo råmateriale. For hvert lægemiddel er prisen pr. kilo angivet i række 6, enhedsomkostningen pr. kilo er angivet i række 7, og fortjenesten pr. kilo angives i række 9. F.eks. sælges produkt 2 for kr. 11,00 pr. kilo, har en enhedsomkostning på kr. 5,70 pr. kilo og bidrager med en fortjeneste på kr. 5,30 pr. kilo. Månedens efterspørgsel efter hvert lægemiddel er angivet i række 8. Efterspørgsel efter produkt 3 er for eksempel 1.041 kilo. Denne måned er der 4.500 timers arbejdskraft og 1.600 kilo råmateriale tilgængelig. Hvordan kan dette firma maksimere sit månedlige overskud?

Hvis du ikke vidste noget om Problemløser, kan du angribe dette problem ved at konstruere et regneark til at spore fortjeneste og ressourceforbrug forbundet med produktblandingen. Derefter kan du bruge tid og sikkert også fejl mens du varierer produktblandingen for at optimere overskuddet uden at bruge mere arbejdskraft eller råmateriale end der er tilgængeligt og uden at producere lægemiddeler, der overstiger efterspørgslen. Du bruger kun Problemløser i denne proces på trial-and-error stadiet. I det væsentlige er Problemløser en optimeringsmotor, der fejlfrit udfører trial-and-error søgninger.

En nøgle til løsning af produktblandingsproblemet er effektivt at beregne ressourceforbruget og fortjenesten forbundet med en givet produktblanding. Et vigtigt værktøj, som du kan bruge til at lave denne beregning, er funktionen SUMPRODUKT. Funktionen SUMPRODUKT multiplicerer tilsvarende værdier i celleområder og returnerer summen af disse værdier. Hvert celleområde, der bruges i en SUMPRODUKT evaluering, skal have de samme dimensioner, hvilket betyder, at du kan bruge SUMPRODUKT med to rækker eller to kolonner, men ikke med en kolonne og en række.

Som et eksempel på, hvordan du kan bruge funktionen SUMPRODUKT i produktblandingseksemplet, lad os prøve at beregne ressourceforbrug. Arbejdsforbrug beregnes ved hjælp af følgende formel:

(Brugt arbejdskraft pr. kilo lægemiddel 1) * (Produceret lægemiddel 1 kilo) + (Brugt arbejdskraft pr. kilo lægemiddel 2) * (Produceret lægemiddel 2 kilo) + ... (Brugt arbejdskraft pr. kilo lægemiddel 6) * (Produceret lægemiddel 6 kilo)

Du kunne beregne arbejdskraften på en mere kedelig måde, som D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. Tilsvarende kunne råvareforbruget beregnes som D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Det er dog tidskrævende at indtaste disse formler i et regneark for de seks produkter. Forestil dig, hvordan det ville være, hvis du arbejdede med et firma, der for eksempel producerede 50 produkter. En meget lettere måde at beregne arbejdskraft og råvareforbrug er at kopiere formlen fra D14 til D15 =SUMPRODUKT($D$2:$I$2;D4:I4). Denne formel beregner D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (som er forbruget af arbejdskraft), men er meget lettere at indtaste. Bemærk, at jeg bruger $-tegnet i området D2:I2, så når jeg kopierer formlen, fanger jeg stadig produktblandingen fra række 2. Formlen i celle D15 beregner råvareforbrug.

På lignende måde bestemmes fortjenesten ved hjælp af følgende formel

(Lægemiddel 1 fortjeneste pr. kilo)*(Produceret lægemiddel 1 kilo)+(Lægemiddel 2 fortjeneste pr. kilo)*(Produceret lægemiddel 2 kilo) + ... (Lægemiddel 6 fortjeneste pr. kilo)*(Produceret lægemiddel 6 kilo)

Overskuddet beregnes let i celle D12 med formlen =SUMPRODUKT(D9:I9;$D$2:$I$2).

Du kan nu identificere de tre komponenter i løsningsmodellen for produktblandingen:

• Målcelle: Maksimal fortjeneste (beregnet i celle D12).
• Variable celler: Antal producerede kilo for hvert produkt (anført i celleområdet D2:I2)
• Begrænsninger: Du har følgende begrænsninger:
  • Brug ikke mere arbejdskraft eller råmateriale, end der er tilgængeligt. Det er værdierne i cellerne D14:D15 (de anvendte ressourcer) der skal være mindre end eller lig med værdierne i cellerne F14:F15 (de tilgængelige ressourcer).
  • Producer ikke mere af et lægemiddel, end det er efterspurgt. Det vil sige værdierne i cellerne D2:I2 (kilo produceret af hvert lægemiddel) skal være mindre end eller lig med efterspørgslen efter hvert lægemiddel (angivet i cellerne D8:I8).
  • Du kan ikke producere en negativ mængde af noget lægemiddel.

Jeg vil vise dig hvordan du indtaster målcellen, variable celler og begrænsninger i Problemløser. Så alt hvad du skal gøre er at klikke på knappen Løs for at finde en produktblanding, der maksimerer overskuddet.

Bemærk

Du installerer Problemløser ved at klikke på fanen Filer, derefter Indstillinger efterfulgt af tilføjelsesprogrammer. Klik på Excel-tilføjelsesprogrammer på listen Administrer, og klik derefter på Gå. Marker afkrydsningsfeltet Problemløser i dialogboksen Tilføjelsesprogrammer, og klik derefter på OK

Til at begynde med skal du klikke på fanen Data og derefter klikke på Problemløser i gruppen Analyse. Dialogboksen Parametre i Problemløser vises som vist i figuren.

Klik i feltet Angiv målsætning, og vælg derefter Overskudscellen (celle D12). Klik i feltet Ved at ændring af variabelceller, og marker derefter på området D2:I2, som indeholder de producerede kilo for hvert lægemiddel. Dialogboksen skal nu se ud som i figuren.

Du er nu klar til at tilføje begrænsninger til modellen. Klik på knappen Tilføj. Du får vist dialogboksen Tilføj begrænsning som vist i figuren.

For at tilføje ressourceforbrugs begrænsninger skal du klikke i feltet Cellereference og derefter vælge området D14:D15. Vælg derefter <= i den midterste liste. Klik i feltet Begrænsning, og vælg derefter celleområdet F14:F15. Dialogboksen Tilføj begrænsning skal nu se ud som figuren.

Du har nu sikret, at når Problemløseren prøver forskellige værdier for de variable celler, er kun de kombinationer, der opfylder både D14<=F14 (anvendt arbejdskraft er mindre end eller lig med disponibel arbejdskraft) og D15<=F15 (anvendt råmateriale er mindre end eller lig med det tilgængelige råmateriale) vil blive overvejet. Klik på Tilføj for at indsætte efterspørgslens begrænsninger. Udfyld derefter dialogboksen Tilføj begrænsning som vist i figuren: I feltet Cellereference skal du vælge D2:I2, og i feltet Begrænsning skal du vælge D8:I8.

Tilføjelse af disse begrænsninger sikrer, at når Problemløser prøver forskellige kombinationer for de variable celleværdier, overvejes kun kombinationer, der opfylder følgende parametre:

• D2<=D8 (den producerede mængde af Lægemiddel 1 er mindre end eller lig med efterspørgslen efter Lægemiddel 1)
• E2<=E8 (den producerede mængde af Lægemiddel 2 er mindre end eller lig med efterspørgslen efter Lægemiddel)
• F2<=F8 (den producerede mængde af Lægemiddel 3 er mindre end eller lig med efterspørgslen efter Lægemiddel 3)
• G2<=G8 (den producerede mængde af Lægemiddel 4 er mindre end eller lig med efterspørgslen efter Lægemiddel 4)
• H2<=H8 (den producerede mængde af Lægemiddel 5 er mindre end eller lig med efterspørgslen efter Lægemiddel 5)
• I2<=I8 (den producerede mængde af Lægemiddel 6 er mindre end eller lig med efterspørgslen efter Lægemiddel 6)

Klik på OK i dialogboksen Tilføj begrænsning. Dialogboksen Løsningsparametre skal se ud som figuren.

Markering af afkrydsningsfeltet Gør variabler uden begrænsninger ikke-negative, bevirker at alle de celler, der ændres, tvinges til at være større end eller lig med 0. Vælg derefter Simplex LP i listen Vælg en løsningsmetode. Du vælger løsningsmetoden Simplex LP fordi produktblandingsproblemet er en speciel type Problemløser problem kaldet en lineær model. I det væsentlige er Problemløser modeller lineære under følgende betingelser:

• Målcellen beregnes ved at tilføje udtryk i form af (variabel celle) * (konstant).
• Hver begrænsning opfylder kravene til en lineær model. Det betyder, at hver begrænsning evalueres ved at tilføje udtryk i form af (den variable celle) * (konstant) og sammenligne summen med en konstant.

Hvorfor er dette Problemløser problem lineært? Vores målcelle (fortjeneste) beregnes som følger:

(Lægemiddel 1 fortjeneste pr. kilo) * (Lægemiddel 1 kilo produceret) + (Lægemiddel 2 fortjeneste pr. kilo) * (Lægemiddel 2 kilo produceret) +… (Lægemiddel 6 fortjeneste pr. kilo) * (Lægemiddel 6 kilo produceret)

Denne beregning følger et mønster, hvor målcellens værdi er fundet ved at summere udtryk af formen (variabel celle) * (konstant).

Vores begrænsning af arbejdskraft evalueres ved at sammenligne værdien udledt af (arbejdskraft brugt pr. kilo Lægemiddel 1) * (produceret Lægemiddel 1 kilo) + (arbejdskraft pr. kilo Lægemiddel 2) “(produceret Lægemiddel 2 kilo) +… (arbejdskraft brugt pr. kilo Lægemiddel 6) * (produceret Lægemiddel 6 kilo) til den tilgængelige arbejdskraft.

Derfor vurderes arbejdskraftens begrænsning ved at addere udtryk af formen (variabel celle) * (konstant) og sammenligne summerne med en konstant. Både arbejdskraftbegrænsning og råmateriale begrænsninger opfylder kravene til lineær model.

Efterspørgsel begrænsningerne har denne form:

(Lægemiddel 1 produceret) <= (Lægemiddel 1 efterspørgsel)

(Lægemiddel 2 produceret) <= (Lægemiddel 2 efterspørgsel)

…

(Lægemiddel 6 produceret) <= (Lægemiddel 6 efterspørgsel)

Hver efterspørgselsbegrænsning opfylder også kravene til en lineær model, fordi hver vurderes ved at summere udtrykkene i form af (variabel celle) * (konstant) og sammenligne summerne med en konstant.

Når du ved, at produktblandingsmodellen er en lineær model, hvorfor så tænke mere over det?

• Hvis en Problemløser-model er lineær, og du vælger Simplex LP som løsningsmetode, garanterer Problemløser, at du finder den optimale løsning på Problemløser-modellen. Hvis en Problemløser-model ikke er lineær, kan Problemløser måske eller måske ikke finde den optimale løsning
• Hvis en Problemløser-model er lineær, og du vælger Simplex LP, bruger Problemløser en meget effektiv algoritme (simplex-metoden) til at finde modellens optimale løsning. Hvis en Problemløser-model er lineær, og du ikke vælger Simplex LP, bruger Problemløser en meget ineffektiv algoritme (GRG2-metoden) og kan have svært ved at finde modellens optimale løsning.

Når du har klikket på Løs, beregner Problemløser en optimal løsning (hvis der findes en) til produktblandingsmodellen. Som jeg sagde i kapitel 1, ville en optimal løsning på produktblandingsmodellen være et sæt variable celleværdier (kilo produceret af hvert lægemiddel), der maksimerer profitten over sættet med alle mulige løsninger. Igen er en mulig løsning et sæt variable celleværdier, der opfylder alle begrænsninger. Sættet med variable celleværdier vist i figuren er en brugbar løsning, fordi alle produktionsniveauer ikke er negative produktionsniveauer, ikke overstiger efterspørgslen, og ressourceforbrug ikke overstiger de tilgængelige ressourcer. Se regnearket Mulig løsning i filen Produktblanding.xlsx.

De variable celleværdier vist i figuren (og i regnearket Umulig Løsninger) repræsenterer en umulig løsning af følgende grunde:

• Du producerer mere af Lægemiddel 5 end der er efterspørgslen på det.
• Du bruger mere arbejdskraft, end der er tilgængeligt.
• Du bruger mere råmateriale, end der er tilgængeligt.

Når du har klikket på Løs, finder Problemløseren hurtigt den optimale løsning vist i figuren. Du skal vælge Behold løsning for at bevare de optimale løsningsværdier i regnearket.

Vores lægemiddelfirma kan maksimere sit månedlige overskud ved et niveau på kr. 6.625,20 ved at producere 596,67 kilo af Lægemiddel 4, 1.084 kilo af Lægemiddel 5 og intet af de andre lægemidler. Du kan ikke afgøre, om du kan opnå den maksimale fortjeneste på kr. 6.625,20 på andre måder. Det eneste du kan være sikker på er, at der med dine begrænsede ressourcer og efterspørgsel er ikke andre måder hvorpå der kan tjenes mere end kr. 6.625 20 denne måned. Det er muligt, at der er mere end en optimal løsning på en Problemløser-model, men det er ikke let at afgøre, om dette er tilfældet.

Har en Problemløser-model altid en løsning?

Antag, at efterspørgslen efter hvert produkt skal imødekommes. (Se regnearket Ingen mulig løsning i filen Produktblanding.xlsx) Du skal derefter ændre dine kravs begrænsninger fra D2:I2 <= D8:I8 til D2:I2 >= D8:I8. For at gøre dette skal du åbne Problemløser (under fanen Data i gruppen Analyse), vælg begrænsningen D2:I2 <= D8:I8 og derefter klikke på Ret. Dialogboksen Ret begrænsning vist i figuren vises.

Vælg >=, og klik derefter på OK. Du har nu sikret, at Problemløser kun overvejer at ændre celleværdier, der opfylder alle kravene. Når du klikker på Løs, vises meddelelsen, at Problemløser ikke kunne finde en mulig løsning. Denne meddelelse betyder ikke, at du lavede en fejl i din model, men snarere at du med de begrænsede ressourcer, ikke kan imødekomme efterspørgslen efter alle produkter. Problemløser fortæller dig simpelthen, at hvis du vil imødekomme efterspørgslen efter hvert produkt, skal du tilføje mere arbejdskraft, flere råvarer eller mere af begge.

Hvad betyder det, hvis en Problemløser-model viser resultatet Værdier i målsætningen konvergerer ikke?

Lad os se, hvad der sker, hvis du tillader ubegrænset efterspørgsel efter hvert produkt, og du tillader, at der produceres negative mængder af hvert lægemiddel. (Du kan se dette Problemløser-problem i regnearket Angiv værdier Konverger ikke i filen Produktblanding.xlsx.) For at finde den optimale løsning til denne situation skal du åbne Problemløser og fjerne markeringen i afkrydsningsfeltet Gør ubegrænsede variabler til ikke-negative. I feltet Underlagt begrænsninger skal du vælge begrænsning D2:I2 <= D8:I8 og derefter klikke på Slet for at fjerne begrænsningen. Når du klikker på Løs, returnerer Problemløser meddelelsen Værdier i målsætningen konvergerer ikke. Denne meddelelse betyder, at hvis målcellen skal maksimeres (som i dette eksempel), er der mulige løsninger med vilkårligt store målcelleværdier. (Hvis målcellen skal minimeres, betyder meddelelsen Værdier i målsætningen konvergerer ikke, at der er gennemførlige løsninger med vilkårligt små målcelle værdier.) I denne situation ved at tillade den negative produktion af et lægemiddel skaber du faktisk ressourcer, der kan bruges til at producere vilkårligt store mængder af andre lægemiddeler. I betragtning af din ubegrænsede efterspørgsel, vil dette giver dig mulighed for at skabe en ubegrænset fortjeneste. I den virkelige verden kan du ikke tjene et uendeligt beløb. Kort sagt, hvis du ser Værdier i målsætningen konvergerer ikke, har din model en fejl.