Brug af Problemløser til at bestemme det optimale produktmix

Hvordan kan jeg bestemme det månedlige produktmix, der maksimerer rentabiliteten?

Virksomheder har ofte brug for at bestemme mængden af hvert produkt til at producere på månedsbasis. I sin enkleste form indebærer produktblandingsproblemet , hvordan man bestemmer mængden af hvert produkt, der skal produceres i løbet af en måned for at maksimere overskuddet. Produktblandingen skal som regel overholde følgende begrænsninger:

• Produktblandingen kan ikke bruge flere ressourcer, end der er tilgængelige.

• Der er en begrænset efterspørgsel efter hvert produkt. Vi kan ikke producere mere af et produkt i løbet af en måned, end efterspørgslen dikterer, fordi den overskydende produktion er spildt (for eksempel et letfordærveligt stof).

Lad os nu løse følgende eksempel på problemet med produktblandingen. Du kan finde løsningen på dette problem i filen Prodmix.xlsx, der er vist i figur 27-1.

Lad os sige, at vi arbejder for et lægemiddelfirma, der producerer seks forskellige produkter på deres fabrik. Produktion af hvert produkt kræver arbejdskraft og råvarer. Række 4 i figur 27-1 viser de timers arbejde, der er nødvendig for at producere et pund af hvert produkt, og række 5 viser de pounds af råvarer, der er nødvendige for at producere et pund af hvert produkt. For eksempel kræver produktion af et pund produkt 1 seks timers arbejdskraft og 3,2 pounds af råmateriale. For hvert stof er prisen pr. pund givet i række 6, enhedsomkostningerne pr. pund er givet i række 7, og profitbidraget pr. pund er givet i række 9. Produkt 2 sælger f.eks. for $ 11,00 pr. pund, pådrager sig en enhedsomkostninger på $ 5,70 pr. pund og bidrager med $ 5,30 overskud pr. pund. Månedens efterspørgsel efter hvert stof er givet i række 8. For eksempel er efterspørgslen efter produkt 3 1041 pounds. Denne måned er 4500 timers arbejde og 1600 pounds af råmateriale til rådighed. Hvordan kan dette firma maksimere sit månedlige overskud?

Hvis vi ikke vidste noget om Excel Problemløser, ville vi angribe dette problem ved at opbygge et regneark for at registrere overskud og ressourceforbrug, der er knyttet til produktblandingen. Så ville vi bruge prøveversion og fejl til at variere produktmikset for at optimere profit uden at bruge mere arbejdskraft eller råmateriale end det er tilgængeligt, og uden at producere noget stof, der overstiger efterspørgslen. Vi bruger kun Problemløser i denne proces i prøve- og fejlfasen. I bund og grund er Problemløser et optimeringsprogram, der fejlfrit udfører en prøve- og fejlsøgning.

En nøgle til at løse produktblandingsproblemet er effektivt at beregne ressourceforbruget og fortjeneste, der er knyttet til et givent produktmix. Et vigtigt værktøj, som vi kan bruge til at foretage denne beregning, er den SUMPRODUKT funktion. 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 én kolonne og én række.

Som et eksempel på, hvordan vi kan bruge funktionen SUMPRODUKT i vores produktmixeksempel, så lad os prøve at beregne vores ressourceforbrug. Vores forbrug af arbejdskraft beregnes af

(Arbejdskraft brugt pr. pund af stof 1)*(Drug 1 pounds produceret)+
(Arbejdskraft brugt pr pund af stof 2)*(Drug 2 pounds produceret) + ...
(Arbejdskraft brugt pr pund af stof 6)*(Drug 6 pounds produceret)

Vi kan beregne brugen af arbejdskraft på en mere kedelig måde som D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. På samme måde kan råvareforbruget beregnes som D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Det er dog tidskrævende at angive disse formler i et regneark for seks produkter. Forestil dig, hvor lang tid det ville tage, hvis du arbejdede med en virksomhed, der producerer f.eks. 50 produkter på deres fabrik. En meget nemmere måde at beregne forbrug af arbejdskraft og råvarer på er at kopiere formlen SUMPRODUKT($D$2:$I$2,D4:I4) fra D14 til D15. Denne formel beregner D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (som er vores forbrug af arbejdskraft), men det er meget nemmere at angive! Bemærk, at jeg bruger $ tegn med området D2:I2, så når jeg kopierer formlen jeg stadig fange produktet mix fra række 2. Formlen i celle D15 beregner forbruget af råvarer.

På samme måde bestemmes vores overskud af

(Drug 1 profit per pund)*(Drug 1 pounds produceret) +
(Drug 2 profit per pund)*(Drug 2 pounds produceret) + ...
(Drug 6 profit per pund)*(Drug 6 pounds produceret)

Profit beregnes nemt i celle D12 med formlen SUMPRODUKT(D9:I9,$D$2:$I$2).

Vi kan nu identificere de tre komponenter i vores problemløsermodel for produktblanding.

• Målcelle. Vores mål er at maksimere profitten (beregnet i celle D12).

• Ændring af celler. Antallet af pund produceret af hvert produkt (angivet i celleområdet D2:I2)

• Begrænsninger. Vi har følgende begrænsninger:

  • Brug ikke mere arbejdskraft eller råmateriale, end der er tilgængeligt. Dvs. værdierne i cellerne D14:D15 (de anvendte ressourcer) skal være mindre end eller lig med værdierne i cellerne F14:F15 (de tilgængelige ressourcer).

  • Må ikke producere mere af et lægemiddel, end der er i efterspørgslen. Dvs. at værdierne i cellerne D2:I2 (pounds produceret af hvert stof) skal være mindre end eller lig med efterspørgslen efter hvert stof (angivet i cellerne D8:I8).

  • Vi kan ikke producere en negativ mængde af noget stof.

Jeg viser dig, hvordan du angiver målcellen, ændrer celler og begrænsninger i Problemløser. Derefter skal du blot klikke på knappen Løs for at finde et produktmiks, der maksimerer profitten!

For at begynde skal du klikke på fanen Data og klikke på Problemløser i gruppen Analyse.

Dialogboksen Parametre til Problemløser vises som vist i Figur 27-2.

Klik på feltet Angiv målcelle, og vælg derefter vores overskudscelle (celle D12). Klik på feltet Ved ændring af celler, og peg derefter på området D2:I2, som indeholder de pounds, der er produceret af hvert stof. Dialogboksen bør nu se ud som Figur 27-3.

Vi er nu klar til at føje begrænsninger til modellen. Klik på knappen Tilføj. Du får vist dialogboksen Tilføj begrænsning, der vises i Figur 27-4.

Hvis du vil tilføje begrænsningerne for ressourceforbrug, skal du klikke på feltet Cellereference og derefter markere området D14:D15. Vælg <= på den midterste liste. Klik på feltet Begrænsning, og markér derefter celleområdet F14:F15. Dialogboksen Tilføj begrænsning bør nu se ud som Figur 27-5.

Vi har nu sikret, at når Problemløser afprøver forskellige værdier for de skiftende celler, er det kun kombinationer, der opfylder både D14<=F14 (arbejdskraft, der anvendes, er mindre end eller lig med tilgængelig arbejdskraft) og D15<=F15 (anvendt råmateriale er mindre end eller lig med tilgængeligt råmateriale) vil blive taget i betragtning. Klik på Tilføj for at angive efterspørgselsbegrænsningerne. Udfyld dialogboksen Tilføj begrænsning som vist i Figur 27-6.

Tilføjelse af disse begrænsninger sikrer, at når Problemløser afprøver forskellige kombinationer for de ændrede celleværdier, tages der kun hensyn til kombinationer, der opfylder følgende parametre:

• D2<=D8 (mængden produceret af Drug 1 er mindre end eller lig med efterspørgslen efter Stof 1)

• E2<=E8 (mængden af produceret af Drug 2 er mindre end eller lig med efterspørgslen efter Stof 2)

• F2<=F8 (mængden produceret af Drug 3 er mindre end eller lig med efterspørgslen efter Stof 3)

• G2<=G8 (mængden produceret af Drug 4 lavet er mindre end eller lig med efterspørgslen efter Drug 4)

• H2<=H8 (mængden produceret af Drug 5 er mindre end eller lig med efterspørgslen efter Drug 5)

• I2<=I8 (mængden produceret af Drug 6 lavet er mindre end eller lig med efterspørgslen efter Drug 6)

Klik på OK i dialogboksen Tilføj begrænsning. Vinduet Problemløser skal se ud som Figur 27-7.

Vi angiver den begrænsning, at ændring af celler skal være ikke-negativ i dialogboksen Indstillinger for Problemløser. Klik på knappen Indstillinger i dialogboksen Parametre til Problemløser. Markér feltet Antag lineær model og feltet Antag ikke-negativ, som vist i figur 27-8 på den næste side. Klik på OK.

Hvis du markerer afkrydsningsfeltet Antag ikke-negativ, sikrer du, at Problemløser kun medtager kombinationer af skiftende celler, hvor hver ændringscelle antager en ikke-negativ værdi. Vi har markeret feltet Antag lineær model, fordi produktblandingsproblemet er en særlig type problemløser, der kaldes en lineær model. I bund og grund er en Problemløser-model lineær under følgende betingelser:

• Målcellen beregnes ved at sammenlægge betingelserne i formularen (ændring af celle)*(konstant).

• Hver begrænsning opfylder "krav til lineær model". Det betyder, at hver begrænsning evalueres ved at addere betingelserne i formularen (ændringscelle)*(konstant) og sammenligne summerne med en konstant.

Hvorfor er problemet i Problemløser lineært? Vores målcelle (overskud) beregnes som

(Drug 1 profit per pund)*(Drug 1 pounds produceret) +
(Drug 2 profit per pund)*(Drug 2 pounds produceret) + ...
(Drug 6 profit per pund)*(Drug 6 pounds produceret)

Denne beregning følger et mønster, hvor målcellens værdi er afledt ved at sammenlægge betingelserne i formularen (ændrende celle)*(konstant).

Vores arbejdskraft begrænsning evalueres ved at sammenligne den værdi, der er afledt af (Labor anvendes pr pund af Drug 1)*(Drug 1 pounds produceret) + (Labor anvendes pr pund af Drug 2)*(Drug 2 pounds produceret)+ ... (Arbejd for osed per pund af Drug 6)*(Drug 6 pounds produceret) til arbejdskraft til rådighed.

Derfor evalueres arbejdsbetingelsen ved at sammenlægge betingelserne i formularen (skiftende celle)*(konstant) og sammenligne summerne med en konstant. Både arbejdsbegrænsningen og råvarebegrænsningen opfylder det lineære modelkrav.

Vores efterspørgselsbegrænsninger har form som

(Stof 1 produceret)<=(Drug 1 Demand)
(Stof 2 produceret)<=(Drug 2 Demand)
§
(Drug 6 produceret)<=(Drug 6 Demand)

Hver efterspørgselsbegrænsning opfylder også det lineære modelkrav, fordi hver enkelt evalueres ved at sammenlægge betingelserne i formularen (ændringscelle)*(konstant) og sammenligne summerne med en konstant.

Efter at have vist, at vores produkt mix model er en lineær model, hvorfor skal vi pleje?

• Hvis en Problemløser-model er lineær, og vi vælger Antag lineær model, er Problemløser garanteret til at finde den optimale løsning på Problemløser-modellen. Hvis en Problemløser-model ikke er lineær, kan det være, at Problemløser finder den optimale løsning.

• Hvis en Problemløser-model er lineær, og vi vælger Antag lineær model, bruger Problemløser en meget effektiv algoritme (simpleksmetoden) til at finde modellens optimale løsning. Hvis en Problemløser-model er lineær, og vi ikke vælger Antag lineær model, 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å OK i dialogboksen Indstillinger for Problemløser, vender vi tilbage til den primære problemløserdialogboks, der er vist tidligere i Figur 27-7. Når vi klikker på Problemløser, beregner Problemløser en optimal løsning (hvis der findes en) til vores produktmixmodel. Som jeg sagde i kapitel 26, en optimal løsning på produktet mix model ville være et sæt af skiftende celleværdier (pounds produceret af hvert stof), der maksimerer profit over sættet af alle mulige løsninger. Igen er en mulig løsning et sæt ændrede celleværdier, der opfylder alle begrænsninger. De ændrede celleværdier, der vises i figur 27-9, er en mulig løsning, fordi alle produktionsniveauer er ikke-negative, produktionsniveauer overstiger ikke efterspørgslen, og ressourceforbruget ikke overstiger de tilgængelige ressourcer.

De ændrede celleværdier, der vises i figur 27-10 på næste side, repræsenterer en uigennemførlig løsning af følgende årsager:

• Vi producerer mere af Drug 5 end efterspørgslen efter det.

• Vi bruger mere arbejdskraft end det, der er tilgængeligt.

• Vi bruger mere råmateriale end det, der er til rådighed.

Når du har klikket på Problemløser, finder Problemløser hurtigt den optimale løsning, der er vist i Figur 27-11. Du skal vælge Behold problemløserløsning for at bevare de optimale løsningsværdier i regnearket.

Vores lægemiddelvirksomhed kan maksimere sit månedlige overskud på et niveau på $ 6.625,20 ved at producere 596,67 pounds af Drug 4, 1084 pounds af Drug 5, og ingen af de andre stoffer! Vi kan ikke afgøre, om vi kan opnå det maksimale overskud på $ 6.625,20 på andre måder. Alt, hvad vi kan være sikker på, er, at med vores begrænsede ressourcer og efterspørgsel, er der ingen måde at gøre mere end $ 6.627,20 denne måned.