Lineaire Programmering [Excel] *Issues*

[eL_Jay]

Aangezien er geen OFF meer is voor office gerelateerde issues dump ik mijn probleem hier. (dit lijkt mij namelijk de meest geschikte)

Voor een opdrachtgever dien ik een model te ontwerpen dat hem in staat stelt om het kapitaalbeslag door voorraad (halffabrikaten en eindproducten, NIET grondstoffen) tot een minimum te beperken.

Dit wil ik doen middels een Excel sheets waar de maandelijkse vereiste productie van een artikel (+/- 150 artikelen) de input vormt en waarbij middels de Solver (Simplex methode) add-in lineaire programmering wordt toegepast en a.h.w. de optimale productie, waarbij minimaal kapitaalbeslag plaatsvind maar die toch voldoet aan de vraag van de markt, er komt uitgerold.

Bij het ontwerp van dit model loop ik tegen een aantal problemen aan.

1. Het is een vereiste dat de maandelijks vereiste productie (verwachte afzet in die maand) geproduceerd wordt terwijl de lineaire programmering techniek de minder rendabele producten op 0 zet waardoor deze niet geproduceerd zouden moeten worden. Dit kan niet. Mijn vraag is; hoe maak ik de koppeling tussen lineaire programmering en de minimaal vereiste productie (de eerder genoemde input)?
2. Vervolgens wil ik een bedrijfseconomische optimale productie (vastgesteld met de formule van Camp) als richtpunt implementeren waarop de lineaire programmering zich dient te richten. M.a.w. De lineaire programmering rekent enkel uit wat de optimale productie is op het gebied van kapitaalbeslag. Dit wil ik echter ook koppelen aan een ijkpunt van minimale optimale productiehoeveelheid die op de totale kosten is gebasseerd(met name omsteltijden, opwarmtijen en af- en uitval). Dus wanneer voldaan wordt aan de restricties dan dient de optimale productiehoeveelheid (vanuit kapitaalbeslag oogpunt) zo veel mogelijk in de buurt te liggen van deze meer bedrijfseconomische optimale productiehoeveelheid.

Graag hoor ik jullie mening en eventuele tips hierover.

Mochten jullie vragen hebben (ik kan me voorstellen dat het lastig te begrijpen/lezen is) dan kun je me altijd via DM of dit topic bereiken.
Wanneer iemand de serieuze intentie heeft om mij daadwerkelijk te helpen aan oplossing van mijn problemen dan stuur ik eventueel de excel sheets (ontwerp met fictieve cijfers) toe zodat deze persoon beeld krijgt bij wat ik bedoel.

Alvast bedankt voor de input

MVG

eL_Jay

[siddler]

Voor vraag 1; als ik het goed heb kun je in de solver van Excel ook constraints instellen. Je kunt dan gewoon iets instellen van bijv. X1 <25 of x1 -25,<= 0

[LiquidT_NL]

Volgens mij geldt dat ook voor de tweede vraag. Het zijn beide standaard optimalisatie vragen, met bepaalde randvoorwaarden. Als je even googled op "excel solver constraints" kom je het vast wel tegen.

[RobIII]

eL_Jay schreef op woensdag 14 november 2012 @ 12:56:
Mochten jullie vragen hebben (ik kan me voorstellen dat het lastig te begrijpen/lezen is) dan kun je me altijd via DM of dit topic bereiken.
Wanneer iemand de serieuze intentie heeft om mij daadwerkelijk te helpen aan oplossing van mijn problemen dan stuur ik eventueel de excel sheets (ontwerp met fictieve cijfers) toe zodat deze persoon beeld krijgt bij wat ik bedoel.

Euh; zullen we dat gewoon allemaal op 't forum houden? Allereerst is dit niets minder dan een Kan iemand even...? en daarbij: het forum heeft natuurlijk 0 nut als iedereen achter de schermen met elkaar gaat lopen communiceren (over dit soort zaken althans). Mensen die in de toekomst op je topic stuiten middels de search / google hebben dan niets aan je topic omdat alles "behind de scenes" heeft plaatsgevonden. Je topic is dan niet meer dan een lege huls. (Zie ook "mail me" is ongewenst).

eL_Jay schreef op woensdag 14 november 2012 @ 12:56:
Aangezien er geen OFF meer is voor office gerelateerde issues dump ik mijn probleem hier. (dit lijkt mij namelijk de meest geschikte)

Ik denk dat CSA beter is; en dus: PRG » CSA

[ Voor 15% gewijzigd door RobIII op 14-11-2012 13:11 ]

[krijn1985]

Inderdaad, een minimum voor deze producten instellen bij de restricties/constraints . Zoals je aangeeft vul je maandelijks vereiste productie van producten in. Dan moet je dus in de constraints wel aangeven dat je dus minimaal die vereiste aantal wilt/moet produceren.

[eL_Jay]

@siddler dat klopt, zover was ik al
@liquidT, dank, ik ga ermee aan de slag.
@RoBIII Excuses was niet bedoelt als Kan iemand even.., de mail me heb ik aangepast in TS.
@Krijn. Dat begrijpt ik, de feitelijke uitvoering die lukt me dus niet (niet excel-vaardig genoeg en te weinig inzicht in wiskunde)
Helaas lukt de daadwerkelijke uitvoering me niet (het totale plaatje kloppend krijgen formule technisch).
Dus mocht iemand me kunnen doorverwijzen naar literatuur o.i.d. die mij hierbij kan helpen dan zou ik jullie eeuwig dankbaar zijn
Ik ga in ieder geval aan de slag met google en hopelijk kom ik daarmee verder.

Bedankt voor de input

[krijn1985]

Heb je al eens met solver gespeeld met maar 2 of 3 producten? Lukt dit wel? Opzich moet 150 producten niet heel veel verschillen dan. Grootste nadeel is dat je hierbij wel heel duidelijk voor jezelf alle constraints moet neerzetten. En als je er 1 vergeet het natuurlijk niet meer klopt. Kan je het bestand of voorbeeld anders even delen?

De basic uitleg is hier te vinden.

[ Voor 18% gewijzigd door krijn1985 op 14-11-2012 13:50 ]

[eL_Jay]

http://speedy.sh/Y7VYG/Op...hoeveelheden-blender.xlsx

Dit is wat ik er tot dusver van heb weten te brouwen, het is echter niet volledig en helaas werkt het niet (solver zegt infeasable)
Sheet 4 is waar de laatste versie staat waarin ik aan het klooien ben.

Disclaimer, zijn fictieve cijfers (m.u.v. max prod. cap.)

Ik denk zelf nog het een ander te kunnen schaven in de komende dagen, (dus wil je jezelf moeite besparen dan wacht tot het weekend en dan upload ik de meest recente versie) een volledig werkend model verwacht ik echter niet in mijn eentje te kunnen maken (vandaar dit topic )
Ik wil enkel van jullie vragen er eens een kritische blik op te werpen en aan mij terugkoppelen wat beter/goed kan en DUS NIET OM EEN WERKEND MODEL TE MAKEN EN DIT AAN MIJ TE RETOURNEREN.

Nogmaals bedankt voor de medewerking

[krijn1985]

Even snel gekeken. Maar wat gebeurd er nu als je dus alle randvoorwaarden voor de producten instelt? Wat dus het minimum en evt. maximum moet zijn. Op dit moment zijn de constraints nog niet goed ingesteld dus.

[LiquidT_NL]

Ik ken de situatie niet, maar even wat dingen die ECHT niet kloppen:
1. Bij geen feasible oplossingen: check altijd wat er gebeurt zonder capaciteitsbeperkingen, om maar te kijken of het uberhaupt mogelijk is
2. Ik weet nogmaals niets van wat jij hier wil doen, maar ik zie dat de kostencalculatie uit gaat van de 4e en 7e rij, maar de veranderende variabelen in de solver staan op rij 5?

edit: sowieso is het allemaal nogal onduidelijk omdat je nogal met termen smijt zonder ze duidelijk uit te leggen.

[ Voor 13% gewijzigd door LiquidT_NL op 14-11-2012 15:00 ]

[krijn1985]

Wat Liquid inderdaad zegt. Je wilt B7 minimaliseren die dus een berekening is van 4e en 7de rij. Maar je laat solver dus op rij 5 waardes veranderen. Solver kan veranderen wat hij wilt op rij 5, maar B7 (wat je dus wilt minimaliseren) zal niet hierdoor niet veranderen.

Toen ik met solver heb leren werken (was voor een vakje in mijn master) heb ik aangeleerd om dus alle variabele cellen 1 bepaalde kleur te geven, je constraints een andere kleur en de cell die je wilt maximaliseren/minimaliseren nog een andere kleur. HIerdoor kan je dus altijd snel zien wat, wat is en je dus in solver mee moet nemen.

edit: ik vond dit zelf wel prettig werken. Vooral naarmate je met 150 producten gaat werken zal het een stuk complexer worden en dan is het wel handig om te weten, wat nou ook alweer waar staat;)

[ Voor 14% gewijzigd door krijn1985 op 14-11-2012 15:07 ]

[LiquidT_NL]

Nou ja, dit is gewoon slordigheid om eerlijk te zijn! Ik wil niet beschuldigen, maar dit is volgens mij gewoon copy-pasta werk geweest

[ Voor 12% gewijzigd door LiquidT_NL op 14-11-2012 15:17 ]

[eL_Jay]

Excuses, voor eventuele onduidelijkheid.
Mocht er terminologie onbekend zijn dan wil ik die graag toelichten, maar of beestje nou X of Y heet maakt formuletechnisch niet zoveel uit volgens mij.

@ LiquidT Helaas geen knip een plak werk. (hoewel sheet 4 is geknipt uit sheet 2 en aangepast)
Op basis van een boek heb ik een voorbeeld uitgewerkt met mijn eigen gegevens. In dat boek hebben ze het echter enkel over maximaliserings vraagstukken en dus geen minimalisering. Geld voor maximalisering en minimaliseren hetzelfde, maar is het enige verschil dat je i.p.v. max, min selecteerd in het Solver-menu?

@ krijn, volgens mij begrijp je me verkeerd (of heb ik het formuletechnisch wel heel slecht aangepakt) Wat ik wil Solven zijn de minimale kosten (b5) en de productiehoeveelheden die hiervoor nodig zijn (rij 4) met de cijfers in rij vier mag Excel spelen, de rest zijn gegeven cijfers waaraan niet getornd mag worden (deze cijfers zijn dus niet variabel). De opdrachtgever wil in rij 11 elke maand de afzetprognoses invullen. Vervolgens drukt hij op solver en poept excel de optimale productiehoeveelheden uit in rij 4. Deze cijfers in rij 4 is wat de opdrachtgever wil weten. B7 wil ik niet minimaliseren, maar B5, B7 moet onder C7 blijven (de maximale productie capaciteit, je kunt immers niet meer produceren dan je machines/mankracht toelaten.)
Kan je je nog herinneren welke literatuur je toen voor je mastervak gebruikte?


Nogmaals bedankt voor de input, zoals ik gewent ben van GoT

P.s. je hebt gelijk dat het slordigheid is, nogmaals excuses hiervoor.
(p.p.s. ik ben nauwelijks bekend met excel en heb een ongezonde aversie van wiskunde en formules)

[krijn1985]

Dan zou ik beginnen met B5 als doelfunctie (weet niet waarom hij bij mij op nederlands staat) in te stellen. Dat staat dus al verkeerd.
Daarnaast moet je dus dan ook de cellen van rij4 al variabele cellen toevoegen, dat is dus wat solver mag aanpassen.
Dan blijft nog over de constraints dus. Oftewel oa B7 <= C7, D4:K4 => D11:K11 etc etc. Kijk dit even zelf na.

het is dus vaak handig om eerst voor je zelf op te schrijven wat je wilt, oftewel, welke formule je wilt maximaliseren/minimaliseren, het veranderen van welke variabelen en wat dus alle constraints zijn (inclusief 0 grens etc etc).

[LiquidT_NL]

eL_Jay schreef op woensdag 14 november 2012 @ 16:26:
Excuses, voor eventuele onduidelijkheid.
Mocht er terminologie onbekend zijn dan wil ik die graag toelichten, maar of beestje nou X of Y heet maakt formuletechnisch niet zoveel uit volgens mij.

@ LiquidT Helaas geen knip een plak werk. (hoewel sheet 4 is geknipt uit sheet 2 en aangepast)
Op basis van een boek heb ik een voorbeeld uitgewerkt met mijn eigen gegevens. In dat boek hebben ze het echter enkel over maximaliserings vraagstukken en dus geen minimalisering. Geld voor maximalisering en minimaliseren hetzelfde, maar is het enige verschil dat je i.p.v. max, min selecteerd in het Solver-menu?

Met alle respect, maar volgens mij snap je niet veel van lineair programmeren en optimalisatie vraagstukken. Geen punt natuurlijk, maar daar zou ik mij eens in verdiepen.

Wat je in feite doet is een maximalisatie of minimalisatie functie, dus het hoogste/laagste getal uitrekenen vanuit je kosten/winstfunctie (b5) afhankelijk van bepaalde invoer (rij 4) en constraints. Kort door de bocht: hij plot een n-dimensionale grafiek en rekent daar het dal van. Plat gezegd gaat hij dus gewoon alle getallen-combinaties proberen en daar de hoogste van nemen (plat gezegd dan: in het echt worden hier bepaalde algoritmes tegenaan gesmeten).

@ krijn, volgens mij begrijp je me verkeerd (of heb ik het formuletechnisch wel heel slecht aangepakt) Wat ik wil Solven zijn de minimale kosten (b5) en de productiehoeveelheden die hiervoor nodig zijn (rij 4) met de cijfers in rij vier mag Excel spelen

Waarom geef je dan aan dat de variabelen rij 5 staan?

(p.p.s. ik ben nauwelijks bekend met excel en heb een ongezonde aversie van wiskunde en formules)

Dan is het lekker onhandig dat jij dit loopt te doen zeg

edit: het is denk ik handig een simpel voorbeeldje eerst te doen, zodat je wat makkelijker overziet wat er gebeurt.

Probeer eens zo'n voorbeeldje eerst uit te werken:
http://www.weka-financiee...t-de-oplosser.18982.lynkx


edit: overigens, ik snap het probleem wat je wil oplossen helemaal niet.

Als het zo is dat:
- waarde in rij 4 moet minimaal de waarde in 11 zijn (dus D4 >= D11, E4 >= E11 etc)
- doelstelling is een lineaire vergelijking met daarin de veranderende variabelen (rij 4)

Dan is de oplossing dus alles in rij 4 zetten op de waarde in rij 11 (dus D4=D11, E4=E11). Dat lijkt mij niet de bedoeling. M.a.w. wat jij nu doet: jij hebt 100 dubbeltjes, jij wil deze aan 10 kinderen geven. Je wil minimaal geld kwijt zijn ,maar wel elk kind 1 dubbeltje geven minimaal. De oplossing is dus ieder kind 1 dubbeltje

Geef nu eens aan wat je EXACT wil berekenen. Ik ben erg geïnteresseerd omdat ik dit soort problemen al jaren niet meer heb gehad, en dit wel leuk vind stiekem.

[ Voor 22% gewijzigd door LiquidT_NL op 14-11-2012 16:59 ]

[eL_Jay]

Bedankt voor alle feedback.

helaas ben ik tot de conclusie gekomen dat het model nooit zal werken. De optimale productiehoeveelheden (output) zal in 9 v.d. 10 (zo niet 10 v.d. 10) gelijk zijn aan de input. Dit is namelijk de belangrijkste restrictie. (optimale productie moet namelijk = of > afzet) .Daarnaast is de enige andere restrictie dat de productiehoeveelheden ten minste groter zijn dan wat de restrictie minimale productierun aangeeft (de minimale hoeveelheid waar je de kosten voor omstellen en opstarten eruit hebt, soort van break-even hoeveelheid)

Ik gooi het nu over een totaal andere boeg en ga middels de op basis van de verwachte afzet van de EindProducten de behoefte aan halffabrikaten(INTermediates) vaststellen, vervolgens stel ik hierop de grondstoffen(Raw Materials) behoefte vast en ken ik aan de EP, RM en INT een ABC-classificatie toe en vervolgens ga ik een Master Production Schedule met Material Requirements Planning trekjes produceren waarin de minimaal te produceren hoeveelheden uitrollen (en in welke maand deze productie plaatsvind). De opdrachtgever is hiermee akkoord gegaan dus dit is hoe we het gaan aanpakken.

Nogmaals bedankt voor alle input en excuses dat ik niet/nauwelijks gebruik ga maken van jullie feedback.