Brug af Problemløser til at bestemme den optimale produkt kombination

Hvordan kan jeg bestemme den månedlige produkt kombination, der maksimerer rentabiliteten?

Virksomheder har ofte brug for at fastlægge antallet af hvert produkt, der skal produceres på månedsbasis. I sin mest enkle form er der problemer med produkt kombinationen , der omfatter, hvordan du kan bestemme mængden af hvert produkt, der skal produceres i løbet af en måned for at maksimere overskuddet. Produkt mix skal normalt overholde følgende begrænsninger:

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

• Der er et begrænset behov for hvert produkt. Vi kan ikke oprette mere af et produkt i en måned end behov, fordi den overskydende produktion spildes (f. eks. et letfordærvelige stof).

Lad os nu løse følgende eksempel på problemet med produkt kombinationen. 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 stof firma, der producerer seks forskellige produkter på deres fabrik. Produktion af hvert produkt kræver arbejdskraft og råmateriale. Række 4 i figur 27-1 viser de arbejdstimer, der er nødvendige for at producere et pund af hvert produkt, og række 5 viser de pund pund, der kræves for at producere et pund af hvert produkt. F. eks. kræver en mængde af produkt 1 seks timers arbejde og 3,2 pund af råmaterialer. For hver af de stoffer, der er angivet i række 6, er prisen for hver pund angivet i række 7, og avance bidraget pr. pund er angivet i række 9. F. eks. sælger Product 2 til $11,00 pr. pund, og der optages en enhedspris på $5,70 pr. pund, og der optages $5,30 overskud pr. pund. Månedens behov for hvert enkelt stof er angivet i række 8. For eksempel er efterspørgslen efter produkt 3 1041 pund. I denne måned er 4500 timers arbejde og 1600 pund af råmateriale tilgængelige. Hvordan kan dette firma maksimere sin månedlige fortjeneste?

Hvis vi vidste ingenting om Excel-Problemløser, vil vi gerne angribe dette problem ved at opbygge et regneark til at registrere overskud og Ressourceforbrug, der er knyttet til produkt kombinationen. Derefter ville vi bruge prøveversionen og fejlen til at variere produkt kombinationen for at optimere overskuddet uden at bruge mere arbejde eller råmateriale, end det er tilgængeligt, og uden at der fremstilles nogen stoffer, der er større end behov. Vi bruger Problemløser i denne proces kun ved prøveversionen-and-fejl-fasen. I det væsentlige er Problemløser et optimerings program, der gennemførte fejl i forsøget på at udføre prøveversionen og fejlsøgning.

En nøgle til at løse problemet med produkt mix er at beregne det ressourceforbrug og den avance, der er knyttet til en bestemt produkt kombination. Et vigtigt værktøj, som vi kan bruge til at udføre 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 én kolonne og én række.

Som et eksempel på, hvordan vi kan bruge funktionen SUMPRODUKT i vores produkt mix-eksempel, lad os prøve at beregne vores Ressourceforbrug. Vores forbrug af arbejdskraft beregnes af

(Anvendt arbejde pr. pund af narkotika 1) * (narkotika 1 pund produceret) +
(arbejdskraft pr. pund af narkotika 2) * (2 pund producerede) +...
(Anvendt arbejde pr. pund af narkotika 6) * (stof 6 pund produceret)

Vi kan også beregne arbejds forbrug på en mere besværlig 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 til seks produkter. ForeStil dig, hvor lang tid det ville tage, hvis du arbejdede med en virksomhed, der produceres, f. eks 50 produkter på deres fabrik. En meget nemmere måde at beregne forbrug af arbejds-og råmateriale på er at kopiere fra D14 til D15 formel SUMPRODUKT ($D $2: $I $2, d4: i4). Denne formel beregner D2 * d4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + i2 * i4 (som er vores arbejds forbrug), men det er meget nemmere at skrive! Bemærk, at jeg bruger $-tegnet med området D2: i2, så når jeg kopierer formlen, kan jeg stadig hente produkt mix fra række 2. Formlen i celle D15 beregner råvareforbruget.

På samme måde bestemmes vores avance af

(Stof 1-avance pr. pund) * (narkotika 1 pund produceret) +
(narkotika 2-resultat) * (2 pund) +.. .
(Narkotika 6-avance pr. pund) * (stoffer 6 pund produceret)

Det er nemt at beregne overskud i celle D12 med formlen SUMPRODUKT (I9:, $D $2: $I $2).

Vi kan nu identificere de tre komponenter i vores problem løsnings model til produkt mix.

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

• Ændre celler.Antallet af pund, der produceres af hvert produkt (angivet i celleområdet D2: i2)

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

  • Du må ikke bruge flere arbejdsopgaver eller råmaterialer, end der er tilgængelig. Det betyder, at 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).

  • Der produceres ikke flere af de stoffer, der ikke er i efterspørgsel. Det betyder, at værdierne i cellerne D2: i2 (pund, der er produceret af hvert enkelt stof), skal være mindre end eller lig med efterspørgslen for hvert enkelt stof (angivet i cellerne D8: i8).

  • Vi kan ikke give en negativ mængde af nogen stoffer.

Jeg viser, hvordan du angiver målcellen, ændrer celler og begrænsninger til Problemløser. Det eneste, du skal gøre, er at klikke på knappen løs for at finde et produkt, der opfører sig over et profit-maksimering!

Start ved at 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 avance celle (celle D12). Klik på feltet ved at ændre cellerne, og peg derefter på området D2: i2, der indeholder det pund, der er produceret af hver enkelt stof. Dialogboksen bør nu se 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 betingelse, der vises i figur 27-4.

Hvis du vil tilføje begrænsninger for ressourceforbrug, skal du klikke på feltet celle reference og derefter vælge området D14: D15. Vælg < = fra den midterste liste. Klik på feltet betingelse, og markér derefter celleområdet F14: F15. Dialogboksen Tilføj begrænsning ser nu ud som figur 27-5.

Vi har nu sikret dig, at når Problemløser har forskellige værdier for de justerbare celler, er det kun kombinationer, der opfylder både D14< = F14 (anvendt arbejde er mindre end eller lig med arbejdsløn, der er tilgængelig), og D15< = F15 (anvendt råmateriale er mindre end eller lig med der vil blive taget hensyn til den disponible råvare. Klik på Tilføj for at angive efterspørgsels begrænsningerne. Udfyld dialogboksen Tilføj betingelse som vist i figur 27-6.

Hvis du tilføjer disse begrænsninger, sikrer det, at når Problemløser forsøger forskellige kombinationer for de ændrede celleværdier, overvejes kun de kombinationer, der opfylder følgende parametre:

• D2< = D8 (det beløb, der produceres af narkotika 1, er mindre end eller lig med efterspørgslen efter stoffer 1)

• E2< = E8 (mængden af produceret stof 2 er mindre end eller lig med behovet for stoffer 2)

• F2< = F8 (det beløb, der fremstilles af narkotika 3, er mindre end eller lig med efterspørgslen efter stoffer 3)

• G2< = G8 (det beløb, der er fremstillet af de pågældende stoffer, er mindre end eller lig med efterspørgslen efter stoffer 4)

• H2< = H8 (det beløb, der fremstilles af narkotika 5, er mindre end eller lig med efterspørgslen efter narkotika 5)

• I2< = i8 (det beløb, der fremstilles af narkotika 6, er mindre end eller lig med efterspørgslen efter stoffer 6)

Klik på OK i dialogboksen Tilføj betingelse. Vinduet Problemløser ser ud som figur 27-7.

Vi angiver den betingelse, der ændrer celler, skal være ikke-negativ i dialogboksen Problemløserindstillinger. Klik på knappen Indstillinger i dialogboksen parametre til Problemløser. Markér afkrydsningsfeltet 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 anTager ikke-negativ, sikrer det, at Problemløser kun finder kombinationer af ændrede celler, hvor hver ændring af cellen forudsætter en værdi, der ikke er negativ. Vi har markeret afkrydsningsfeltet Antag lineær model, fordi produktets kombinations problem er en særlig type problemLøser-problem, der kaldes en lineær model. En Problemløser-model er som standard lineær under følgende betingelser:

• Målcellen beregnes ved at tilføje sammen betingelserne for formularen (ændring af celle) * (konstant).

• Hver betingelse opfylder "lineært model krav". Det betyder, at hver betingelse evalueres ved at tilføje sammen sammen betingelserne i formularen (ændring af celle) * (konstant) og sammenligne summerne med en konstant.

Hvorfor er dette problem med problemløseren lineær? Vores målcelle (fortjeneste) beregnes som

(Stof 1-avance pr. pund) * (narkotika 1 pund produceret) +
(narkotika 2-resultat) * (2 pund) +.. .
(Narkotika 6-avance pr. pund) * (stoffer 6 pund produceret)

Denne beregning følger et mønster, hvor målcellens værdi er afledt ved at tilføje sammen vilkår i formularen (ændring af celle) * (konstant).

Vores arbejdsbelastning evalueres ved at sammenligne den værdi, der er afledt af (anvendt arbejde pr. pund af narkotika 1) * (stof 1 pund produceret) + (forbrug af arbejde pr. pund af narkotika 2) * (stof 2 pund produceret) +... (Arbejds-dked pr. pund af narkotika 6) * (stof 6 pund produceret) til den disponible arbejdstid.

Derfor evalueres arbejds begrænsningen ved at tilføje sammen vilkår i formularen (ændring af celle) * (konstant) og sammenligne summerne med en konstant. Både arbejds begrænsningen og råvare begrænsningen opfylder det lineære model behov.

Vores efterspørgsels begrænsninger tager form af

(Narkotika 1 produceret) < = (narkotika 1-behov)
(narkotika 2-produceret) < = (stof 2-demand)
§
(narkotika 6 produceret) < = (narkotika 6-behov)

Hver efterspørgsels begrænsning opfylder også kravene til lineær model, fordi hver evaluering er samlet ved at tilføje sammen betingelserne i formularen (ændring af celle) * (konstant) og sammenligning af summerne med en konstant.

Da vi har vist, at vores produkt mix-model er en lineær model, hvorfor bør vi tage højde for det?

• Hvis en Problemløser model er lineær, og vi vælger Antag lineær model, garanteres Problemløser at finde den optimale løsning til Problemløser-modellen. Hvis en Problemløser model ikke er lineær, kan Problemløser muligvis ikke finde 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 (den simpleks metode) 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 (metoden GRG2) og kan have svært ved at finde modellens optimale løsning.

Når du har klikket på OK i dialogboksen Problemløserindstillinger, vender vi tilbage til Hoveddialogboksen Problemløser, som vist tidligere i figur 27-7. Når vi klikker på løsning, beregner Problemløser en optimal løsning (hvis der findes en) til vores produkt mix-model. Som det er angivet i kapitel 26, er en optimal løsning på modellen til produkt blandet en række ændringer af celleværdier (pund, der produceres af hvert enkelt stof), som maksimerer overskuddet over sættet af alle mulige løsninger. En gennemførlig løsning er et sæt ændring af celleværdier, der opfylder alle begrænsninger. De ændrede celleværdier, der vises i figur 27-9, er en gennemførlig løsning, da alle produktionsniveauer ikke er negative, produktionsniveauer ikke overskrider efterspørgslen, og ressourceforbrug ikke overstiger de tilgængelige ressourcer.

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

• Vi producerer mere af narkotika 5, end det har behov for det.

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

• Vi bruger mere råmateriale end det, der er tilgængeligt.

Når du har klikket på løs, finder Problemløser hurtigt den optimale løsning, der vises i figur 27-11. Du skal vælge behold Problemløser-løsning for at bevare de optimale løsnings værdier i regnearket.

Vores stof virksomhed kan maksimere sin månedlige fortjeneste på et niveau på $6.625,20 ved at producere 596,67 pund af narkotika 4, 1084 pund af narkotika 5 og ingen af de andre stoffer! Vi kan ikke afgøre, om vi kan opnå den maksimale fortjeneste på $6.625,20 på andre måder. Det kan være, at vi er sikre på, at du har vores begrænsede ressourcer og krav, at du ikke kan få mere end $6.627,20 i denne måned.