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

Bemærk!: Vi vil gerne give dig den mest opdaterede hjælp, så hurtigt vi kan, på dit eget sprog. Denne side er oversat ved hjælp af automatisering og kan indeholde grammatiske fejl og unøjagtigheder. Det er vores hensigt, at dette indhold skal være nyttigt for dig. Vil du fortælle os, om oplysningerne var nyttige for dig, nederst på denne side? Her er artiklen på engelsk så du kan sammenligne.

I denne artikel beskrives brugen af Problemløser, et Microsoft Excel-tilføjelsesprogram, du kan bruge til what if-analyse til at bestemme en optimal 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.

Billede af bog

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.

Bemærk!:  Som beskrevet i kapitel 26 "Problemløser med Excel Problemløser" er Problemløser installeret ved at klikke på Microsoft Office-knappen og derefter Excel-indstillinger efterfulgt af TilføjelsesProgrammer. Klik på Excel-tilføjelsesprogrammer på listen Administrer, Markér afkrydsningsfeltet tilføjelsesprogrammet Problemløser, og klik derefter på OK.

Dialogboksen parametre til Problemløser vises, som vist i figur 27-2.

Billede af bog

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.

Billede af bog

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.

Billede af bog

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.

Billede af bog

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.

Billede af bog

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.

Billede af bog

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.

Billede af bog

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.

Billede af bog

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.

Billede af bog

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.

Billede af bog

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.

Lad os antage, at efterspørgslen efter hvert produkt skal være opfyldt. Se regnearket intet gennemførligt løsning i filen prodmix. xlsx. Vi er nødt til at ændre vores efterspørgsels begrænsninger fra D2: I2< = D8: i8 til D2: I2> = D8: i8. Det gør du ved at åbne Problemløser, vælge D2: I2< = D8: i8-begrænsningen og derefter klikke på Rediger. Dialogboksen ændrings begrænsning, der vises i figur 27-12, vises.

Billede af bog

Vælg > =, og klik derefter på OK. Vi har nu sikret sig, at Problemløser vil overveje kun at ændre celleværdier, der opfylder alle krav. Når du klikker på løs, får du vist meddelelsen "Problemløser kunne ikke finde en gennemførlig løsning". Denne meddelelse betyder ikke, at vi har lavet en fejl i vores model, men i stedet for vores begrænsede ressourcer opfylder vi ikke efterspørgslen efter alle produkter. Problemløser fortæller os, at vi er nødt til at tilføje mere arbejde, flere råvarer eller flere af begge dele, hvis vi vil overholde efterspørgslen for hvert produkt.

Lad os se, hvad der sker, hvis vi tillader ubegrænset behov for hvert produkt, og vi tillader, at der produceres negative antal for hver enkelt stof. (Du kan se dette problem med problemLøser på set-værdierne konvergerer ikke i regnearket i filen prodmix. xlsx). Hvis du vil finde den optimale løsning for denne situation, skal du åbne Problemløser, klikke på knappen Indstillinger og fjerne markeringen i afkrydsningsfeltet Antag ikke-negativ. I dialogboksen parametre til Problemløser skal du vælge den ønskede 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 "Angiv celleværdier konvergerer ikke". Denne meddelelse betyder, at hvis målcellen skal være maksimeret (som i vores eksempel), er der en gennemførlig løsning med vilkårligt store celleværdier. Hvis målcellen skal være minimeret, betyder det, at meddelelsen "Angiv celleværdier konvergerer ikke" betyder, at der er gennemførlige løsninger med vilkårligt små målcelle værdier. I vores situation, hvor vi har en negativ produktion af et stof, har vi i kraft "Opret"-ressourcer, der kan bruges til at producere vilkårligt store mængder af andre stoffer. Det giver os ubegrænset behov, så vi kan få ubegrænset fortjeneste. I virkelighedens faktiske situation kan vi ikke lave et ubegrænset antal penge. Hvis du ser "set-værdierne ikke konvergerer" i en kort, har din model en fejl.

  1. ForeStil dig, at vores stof virksomhed kan købe op til 500 timers arbejde i op til pr. time end de aktuelle omkostninger til arbejdsomkostninger. Hvordan kan vi maksimere overskuddet?

  2. På en chip fremstillings plan producerer fire teknikere (A, B, C og D) tre produkter (Product 1, 2 og 3). Denne måned kan chip producenten sælge 80 enheder af produkt 1, 50 enheder af produkt 2 og højst 50 enheder af produkt 3. Tekniker A kan kun lave produkter 1 og 3. Teknikere B kan kun lave produkter 1 og 2. Tekniker C kan kun lave produkt 3. Teknikeren D kan kun lave produkt 2. For hver fremstillet enhed bidrager produkter til følgende: produkt 1, $6; Produkt 2, $7 og produkt 3, $10. Den tid (i timer), hver tekniker skal fremstille et produkt, er som følger:

    Produkt

    Tekniker A

    Tekniker B

    Tekniker C

    Tekniker D

    1

    2

    2,5

    Kan ikke gøre

    Kan ikke gøre

    2

    Kan ikke gøre

    3

    Kan ikke gøre

    3,5

    3

    3

    Kan ikke gøre

    4

    Kan ikke gøre

  3. Hver tekniker kan arbejde op til 120 timer om måneden. Hvordan kan chip fabrikanten maksimere sin månedlige fortjeneste? Antag, at der kan produceres et brøkdels antal enheder.

  4. Et computer produktionsanlæg giver mus, tastaturer og videospil-joysticks. Forbrug, månedlig efterspørgsel og per-Unit-brug af arbejdstiden, månedlig efterspørgsel og per-Unit-brug af forbrug er angivet i den følgende tabel.

    Mus

    Tastaturer

    Joysticks

    Avance/enhed

    $8

    $11

    $9

    Arbejds forbrug/enhed

    .2 timer

    .3 timer

    .24 timer

    Maskin klokkeslæt/enhed

    .04 time

    .055 time

    .04 time

    Månedlig efterspørgsel

    15.000

    27.000

    11.000

  5. Hver måned er der en total på 13.000 arbejdstimer og 3000 timer fra maskin tiden. Hvordan kan producenten maksimere sin månedlige indtjenings bidrag fra producenten?

  6. Du kan løse vores eksempler på, at et minimum behov på 200-enheder for hvert enkelt stof skal opfyldes.

  7. Jason gør rombe bracelets, necklaces og øreringe. Han vil gerne arbejde op til 160 timer om måneden. Han har 800 ounce-diamanter. Den avance, arbejdstid og de ounce-ruder, der kræves for at producere hvert produkt, er anført nedenfor. Hvis behovet for hvert produkt er ubegrænset, hvordan kan Jason maksimere sin indtjening?

    Produkt

    Enheds avance

    Arbejdstimer pr. enhed

    Ounce af diamanter pr. enhed

    Bracelet

    DKK 300

    .35

    1,2

    NECKLACE

    DKK 200

    .15

    .75

    Øreringe

    DKK 100

    ,05

    5

Udvid dine Office-færdigheder
Gå på opdagelse i kurser
Få nye funktioner først
Bliv Office Insider

Var disse oplysninger nyttige?

Tak for din feedback!

Tak for din feedback! Det lyder, som om det vil kunne hjælpe, hvis du bliver sat i forbindelse med en af vores Office-supportteknikere.

×