Lineair programmeren met de Oplosser

In het rekenblad Excel kun je lineair programmeren met behulp van de invoegtoepassing 'Oplosser'. Dit hulpprogramma noem je een macro. Zo'n macro moet je eerst oproepen. Dat gaat zo:

  1. Excel openen, er komt een werkmap te voorschijn.
  2. Kies bij Extra het Invoegtoepassing, je krijgt dan een lijst te zien van macro's die je kunt oproepen (de Oplosser moet daar bij staan).
  3. Selecteer nu in de lijst Beschikbare invoegmacro's de macro Oplosser en sluit af met .
  4. Onder Extra is nu de Oplosser toegevoegd.
In dit practicum los je het volgende probleem op:

Een handelaar in fietsen, brommers en kinderzitjes kan deze artikelen aanschaffen bij een groothandel. Hij moet dan rekening houden met de inkoopprijs, maar ook met de opslagruimte. Verder is voor hem de winst per artikel van belang. Er gelden de volgende gegevens:


inkoopprijs (in €) opslagruimte (m2) winst per stuk (in €)
fietsen 300 0,5 100
brommers 1200 1,0 300
kinderzitjes 36 0,1 20

Hij besluit hoogstens 100 fietsen en hoogstens 50 kinderzitjes te kopen. Hij beschikt over maximaal 101 m2 opslagruimte en over maximaal € 93.000,= om de artikelen aan te schaffen. Het gaat deze ondernemer om een zo groot mogelijke winst. Hoeveel moet hij van elk van deze artikelen aanschaffen?

Voor het oplossen van dit probleem kies je eerst de beslissingsvariabelen. Hier:
x = aantal fietsen
y = aantal brommers
z = aantal kinderzitjes

Je vindt dan in de tekst deze randvoorwaarden of restricties:

300x + 1200y + 36z < 93000
0,5x + 1y + 0,1z < 101
0 < x < 100
0 < y
0 < z < 50

Verder geldt als doelfunctie de totale winst: W = 100x + 300y + 20z.

Gezocht wordt een maximum van deze doelfunctie onder de beschreven restricties.

Nu naar Excel met het bij dit practicum horende bestand:

LPfietsen.xls.

Open dit bestand met de rechtermuisknop en sla het op jouw computer op.
Je ziet hoe de gegevens hierboven zijn ingevoerd.
Er is een kolom 'aantal' gemaakt. De cellen C4, C5 en C6 stellen de variabelen x, y en z voor. Vooralsnog zijn daar de waarden 0, 0 en 0 ingevoerd.
In cel D7 zie je hoe de totale inkoopprijs wordt berekend met de formule: 300x + 1200y + 36z.
Controleer zo ook de berekeningen van de cellen E7 en F7.
De restricties gelden voor de cellen C4, C6, D7 en E7.
De doelfunctie zit in cel F7.
Ga dat na!

Roep nu de macro Oplosser op en ga als volgt te werk:

  1. Vul de doelfunctie in bij Cel bepalen: $F$7
  2. Geef aan bij Gelijk aan: MAX
  3. Geef de beslissingsvariabelen aan bij Door verandering cel: $C$4 : $C$6 (dit betekent: C4 t/m C6)
  4. Klik op Toevoegen, je krijgt dan een hulpvenster waarin je telkens een restrictie kunt invoeren. De eerste is bijvoorbeeld: $C$4 < 100.
    (dit betekent: x < 100)
  5. Door telkens weer op Toevoegen te klikken, kun je meerdere (hier dus vier) restricties invoeren.
  6. Als alle restricties zijn ingevoerd klik je op Oplossen.
Na eventjes rekenen geeft de computer aan dat hij een oplossing heeft kunnen vinden. De resultaten staan dan in de oorspronkelijke werkmap.
Ga na, dat je vindt:
x = 80
y = 56
z = 50
W = 25800 als maximale winst

Probeer het lineair programmeren met de Oplosser in Excel goed onder de knie te krijgen met behulp van lineair programmeringsproblemen uit je wiskundeboek.