inhoud vorige volgende

10. Productie, voorraad en verkoop

In dit hoofdstuk wordt een werkblad opgesteld waarbij gedurende een periode (een jaar) voor elke maand gegevens of prognoses worden bijgehouden over de voorraadevolutie van een bepaald product, de kosten die daarbij optreden en omzet- en winstgegevens.

Achtereenvolgens worden de volgende zaken behandeld:

Een model met constante productie opstellen.
De vraag simuleren.
Speciale waarden opzoeken.
Een tabel maken van de winst in functie van de productie.
Een model met variabele productie opstellen.

De in dit hoofdstuk opgestelde werkbladen bevinden zich in het werkboek Voorraad.xls. Je kan dit openen vanuit P:\Excel 97 en bewaren op je I: schijf. De werkbladen zijn:

Voorraad 1: bevat het model van de voorraden.

Voorraad 2: bevat het uitgebreide model met kosten, omzet en winst.

Voorraad 3: laat de vraagcijfers via een macro wijzigen en berekent speciale gevallen.

Voorraad 4: bevat het model met daaraan toegevoegd een data tabel en chart.

Voorraad 5: laat de productie maandelijks variëren in functie van de maandelijkse verwachte vraag en de beginvoorraad.

De wijze van het in rekening brengen van de kosten is voor discussie vatbaar. De modellen worden met opzet eenvoudig gehouden.

Het model van de voorraden (*)

Beschouw in eerste instantie het volgende model:

In het begin van elke maand is er een beginvoorraad van het product, genoteerd als Q_b.

Elke maand wordt een zekere hoeveelheid van dit product geproduceerd, genoteerd als Q_p.

De maximale hoeveelheid die in een maand kan verkocht worden is gelijk aan de som van deze twee. Deze maximale hoeveelheid vormt het aanbod en wordt genoteerd als Q_a. Tussen Q_b, Q_p en Q_a bestaat het verband:

Q_a = Q_b + Q_p

De vraag is een grootheid die je niet (volledig) in de hand hebt. Deze wordt genoteerd als Q_d (d van demand).

De verkochte hoeveelheid wordt genoteerd als Q_v. Er wordt verkocht tot uitputting van de voorraad. Dit betekent het volgende:

Indien de vraag groter is dan het aanbod, wordt het volledige aanbod verkocht. Je kan dan niet helemaal aan de vraag voldoen.

Indien het aanbod groter is dan de vraag, kan je aan de vraag voldoen, maar houd je op het einde van de maand nog een niet-verkochte hoeveelheid over, de eindvoorraad, die wordt genoteerd als Q_e.

De bovenstaande beschouwingen leiden tot de volgende verbanden:

Q_v = min(Q_a,Q_d)

Q_e = Q_a – Q_v (en is gegarandeerd 0 of groter)

De (niet-verkochte) eindvoorraad Q_e wordt overgeheveld naar de volgende maand, waar hij de beginvoorraad vormt:

Q_b(maand k+1) = Q_e(maand k)

Je kan ook de gemiddelde voorraad in een bepaalde periode registreren. Wanneer je aanneemt dat productie en verkoop gelijkmatig gespreid over de hele periode verlopen, kan je benaderd stellen dat deze gemiddelde voorraad Q_s (s van stock) gegeven wordt door de formule:

Q_s = (Q_b + Q_e) / 2

De grootheden vallen in drie groepen uiteen:

Stuurgegevens (inputgegevens die je zelf kan controleren door ze te kiezen): Q_p.

Stoorgegevens (gegevens die je eventueel ook zelf kan invoeren, maar die je niet kan kiezen en controleren): Q_d(wordt door de markt bepaald).

Afhankelijke (berekende) grootheden: Q_b, Q_a, Q_v, Q_e, Q_s.

Je kan opteren om de productie Q_p voor alle perioden gelijk te kiezen of in elke periode een andere productie te voorzien. Je kan je ook laten leiden door een verwachte vraag en de productie zo kiezen dat het aanbod juist aan deze verwachte vraag voldoet, of zo dat er een licht overschot is. Niet aan de vraag kunnen voldoen is immers nadelig voor je imago, en kan een toekomstige vraag in negatieve zin beïnvloeden.

Kies er in dit model voor om in elke maand evenveel te produceren en noteer deze vaste maandelijkse productie als Q.

figuur 1: Het werkblad 'Voorraad 1' met het model van de voorraden

Geef in eerste instantie zelf een aantal waarden voor de vraag in. Later zal je de vraagcijfers door Excel laten genereren, waarbij deze normaal wordt verdeeld rond een bepaalde (verwachte) vraag.

Het model wordt weergegeven in het werkblad Voorraad 1 (figuur 1). De commando's en formules zijn:

A1:

naam, Italic knop

B1:

Productie, voorraad en verkoop

B1:G1:

Bold knop, Font Size knop: 14, Merge and Center knop

B2:

maandelijkse productie

E2:

Q, Font Size knop: 14, Center knop

F2:

1000, Format, Cells..., Number tabblad, Category: Custom, Type: 0" e.", Font Color knop: Dark Blue, Bold knop, Center knop

B4:

begin voorraad

B5:

Q_b

C4:

productie

C5:

Q_p

D4:

aanbod

D5:

Q_a

E4:

vraag

E5:

Q_d

F4:

verkocht

F5:

Q_v

G4:

eind voorraad

G5:

Q_e

H4:

gem. Voorraad

H5:

Q_s

A5:

maand

B4:H5:

Format, Cells..., Alignment tabblad, Horizontal Center, Vertical Center, Wrap text

B5:H5:

Font Size knop: 14

A6:

januari, sleep fill handle naar A6:A17

A18:

jaartotaal

A19:

gem. maand

A:A:

Format, Column, AutoFit Selection

B6:

0, Font Color knop: Dark Blue

(Q_b)

B7:

=G6, sleep fill handle naar B7:B17

C6:

=$F$2, sleep fill handle naar C6:C17

(Q_p; =Q)

D6:

=B6+C6, sleep fill handle naar D6:D17

(Q_a; =Q_b+Q_p)

E6:E17:

950, 1200, 1000, 900, 950, 1000, 1100, 1050, 950, 1000, 900, 950, Font Color knop: Dark Blue

(Q_d)

F6:

=MIN(D6,E6), sleep fill handle naar F6:F17

(Q_v)

G6:

=D6–F6, sleep fill handle naar G6:G17

(Q_e; =Q_a–Q_v)

H6:

=(B6+G6)/2, sleep fill handle naar H6:H17

(Q_s; =(Q_b+Q_e)/2)

B18:

=B6

C18:

=SUM(C6:C17)

D18:

=B18+C18

E18:

=SUM(E6:E17)

F18:

=SUM(F6:F17)

G18:

=D18–F18 (of: =G17)

H18:

=AVERAGE(H6:H17)

B19:

=AVERAGE(B6:B17), sleep fill handle naar B19:H19

B6:H19:

Format, Cells..., Number tabblad, Category Custom, Type: 0" e."

A4:H5:

Borders knop: knop 2 (dunne lijn onderaan)

A18:H19:

Borders knop: knop 9 (dunne lijn boven en halfdikke lijn onder range)

A4:H19:

Format, Cells, Border tabblad, klik halfdikke lijn, Presets: Outline knop, klik dunne lijn, Border: verticaal lijn binnenin

Tools, Options..., View tabblad, verwijder de aankruising van Gridlines

Opmerkingen:

Verklaar de formules in rij 18!

Alle in te voeren grootheden werden beklemtoond door ze in een donker blauwe kleur weer te geven.
Je kan de symbolen in E2 en A5:H5 als namen geven aan cellen F2 en de twaalf cellen in de kolommen A6:H17. Omdat je in de verschillende werkbladen dezelfde namen zal gebruiken, moet je van de namen sheet-level namen van de vorm 'Voorraad 1'!Q enz. definiëren met het commando Insert, Name, Define.... Je kan dan de namen gebruiken bij het opstellen van de formules of achteraf in de plaats van de referenties aanbrengen met het commando Insert, Name, Apply.... Hierbij moet je erop letten dat je vóór je dit laatste commando toepassen ofwel één cel ofwel de range waarin de namen gebruikt moeten worden selecteert. Ook moet je in de Apply names lijst alle gewenste namen selecteren.
Al alternatief kan je de symbolen in A5:H5 niet als namen maar als labels definiëren met het commando Insert, Name, Label…. Een probleem is hierbij dat het symbool Qb in cel B5 als label de 14 opeenvolgende niet-lege cellen in B6:B19 zal aanduiden, terwijl ze enkel B6:B17 mogen aanduiden. Je kan dit probleem verhelpen door in rij 18 een lege rij toe te voegen en die daarna te verbergen (rijhoogte tot 0 reduceren, of met het commando Format, Row, Hide). Dan zal het symbool Qb de twaalf opeenvolgende niet-lege cellen in B6:B17 aanduiden zoals gewenst. Je kan het symbool dan gebruiken in de formules in de laatste twee rijen (nu rijen 19 en 20). Om verwarring te vermijden wordt deze werkwijze hier niet toegepast.
Het gebruik van namen heeft ook nadelen. Zo kan de formule voor de gemiddelden in cel B19 enkel gekopieerd worden naar C19:H19 wanneer de referenties niet door namen zijn vervangen! Wanneer de referenties door labels zijn vervangen, die door Excel correct worden geïnterpreteerd, kan je ze daarentegen wel kopiëren!
In de werkbladen van het werkboek Voorraad.xls zullen geen labels worden gebruikt en enkel namen wanneer ze in macro procedures voorkomen.

Het model uitbreiden om kosten, omzet en winst aan te geven (*)

Juist zoals er een evolutie is in de voorraden, is er ook een evolutie in de kosten, omzet en winst. Je kan deze op een analoge wijze in een afzonderlijke tabel plaatsen. In dit werkboek daarentegen wordt de bestaande tabel naar rechts uitgebreid om grootheden die verband houden met geldbedragen aan te geven.

Bekijk vooreerst de in de tabel op te nemen grootheden en de formules die de verbanden aangeven:

Met de beginvoorraad Q_b kan je een kost associëren, die met K_b wordt aangeduid. Deze kost kan, juist zoals de beginvoorraad zelf, worden beschouwd als een overgedragen kost K_e van de eindvoorraad Q_e van de vorige periode:

K_b(maand k+1) = K_e(maand k)

In de eerste periode (januari) moet je de waarde van K_b intypen, juist zoals die van Q_b.

Met de productie Q_p kan je een productiekost associëren, die bestaat uit een vaste periodekost F (F van fixed) en een variabele kost per geproduceerde eenheid c_p. De totale productiekost is dus gelijk aan:

F + c_p * Q_p

In dit werkblad wordt echter de vaste periodekost F en de variabele productiekost c_p*Q_p van de periode afzonderlijk verrekend, omdat je enkel deze laatste met de eindvoorraad zal overdragen naar een volgende periode. De variabele productiekost wordt dan gegeven door:

K_p = c_p * Q_p

De (variabele) kost van de aangeboden hoeveelheid K_a wordt gegeven door een formule die analoog is aan die voor het aanbod Q_a:

K_a = K_b + K_p

Deze variabele kost van de aangeboden hoeveelheid moet je proportioneel verdelen over het verkochte deel Q_v en de eindvoorraad Q_e, wat resulteert in een (variabele) kost van de verkochte hoeveelheid K_v en een (variabele) kost van de eindvoorraad K_e:

K_v = K_a * (Q_v / Q_a)

K_e = K_a * (Q_e / Q_a) = K_a – K_v

Deze laatste formule is analoog aan de formule voor Q_e.

Tenslotte is er nog een kost voor het bewaren van de producten. Deze is evenredig met het (gemiddeld) aantal eenheden die in voorraad worden gehouden gedurende een hele periode, en dus afhankelijk van de duur van de periode. Hij wordt voorgesteld door c_s (s van stock). Over een hele periode correspondeert hiermee een kost die gegeven wordt door:

c_s * Q_s

Reken deze kost juist zoals de vaste productiekost als een periodekost aan. Deze totale periodekost wordt voorgesteld door K_f (f van fixed - de belangrijkste bijdrage tot de periodekost):

K_f = F + c_s * Q_s

Bij het verkopen realiseer je een omzet die gegeven wordt door

O = P * Q_v

waarbij P de verkoopprijs per eenheid is. Deze grootheid kan je volledig controleren, maar uiteraard zal een verhoging hiervan gewoonlijk een negatieve weerslag hebben op de vraag.

De winst in een bepaalde periode W kan je formuleren als de omzet, verminderd met de kosten, waarbij je de periodekosten volledig aanrekent (vaste en voorraadkosten) en verder de variabele productiekosten van de verkochte hoeveelheden. De variabele kosten van de niet verkochte hoeveelheden worden als kost van de eindvoorraad naar de volgende periode overgedragen. De formule voor de winst is dan:

W = O – K_v – K_f

Het is zeker interessant de winst uit te drukken per (verkochte) eenheid (w_e). Deze verkrijg je via de formule:

w_e = W / Q_v

Met deze redeneringen is het vrij eenvoudig het werkblad uit te breiden door boven de tabel enkele rijen toe te voegen en vier extra gegevens te voorzien (P, F, c_p en c_s). Stel ook de belangrijkste resultaten (W_j: de winst op jaarbasis en w_je: de jaarlijkse winst per verkochte eenheid) bovenaan in het werkblad voor. Dit resulteert in het werkblad Voorraad 2 (figuur 2).

Om de maandnamen steeds in beeld te hebben moet je de linkerkolom bevriezen. Zo blijft kolom A steeds in beeld. De commando's en formules zijn:

B1:

Window, Freeze Panes

Zoom knop: 75%

3,5:

Insert, Rows

I2:

verkoopprijs:

L2:

P

I3:

vaste productiekost:

L3:

F

I4:

variabele productiekost:

L4:

c_p

I5:

bewaarkost:

L5:

C_s

L2:L5:

Font Size knop: 14, Center knop

M2:

45, Format, Cells..., Number tabblad, Category Custom,
Type: # ##0 BF"/e."

M3:

10000, Format, Cells..., Number tabblad, Category Custom,
Type: # ##0 BF'/mnd"

M4:

30, Format, Cells..., Number tabblad, Category Custom,
Type: # ##0 BF"/e."

M5:

5, Format, Cells..., Number tabblad, Category Custom,
Type: # ##0 BF"/e./mnd"

M2:N2:

Bold knop, Font Color knop: Dark Blue, Merge and Center knop

M3:N3:

Bold knop, Font Color knop: Dark Blue, Merge and Center knop

M4:N4:

Bold knop, Font Color knop: Dark Blue, Merge and Center knop

M5:N5:

Bold knop, Font Color knop: Dark Blue, Merge and Center knop

I7:

var. kost begin voorraad

I8:

K_b

J7:

var. kost productie

J8:

K_p

K7:

var. kost aanbod

K8:

K_a

L7:

var. kost verkochte hoev.

L8:

K_v

M7

var. kost eind voorraad

M8:

K_e

N7:

periode kost

N8:

K_f

O7:

omzet

O8:

O

P7:

winst

P8:

W

Q7

winst per eenheid

Q8:

w_e

I7:Q8:

Format, Cells..., Alignment tabblad, Horizontal Center, Vertical Center, Wrap Text

I8:Q8:

Font Size lijst: 14

I9:

0, Font Color knop: Dark Blue

(K_b)

I10:

=M9, sleep fill handle naar I10:I20

J9:

=$M$4*C9

(K_p; =c_p*Q_p)

K9:

=I9+J9

(K_a; =K_b+K_p)

L9:

=ROUND(K9*F9/D9,0)

(K_v; = K_a*Q_v/Q_a)

M9:

=K9–L9

(K_e; =K_a–K_v)

N9:

=$M$3+$M$5*H9

(K_f; =F+c_s*Q_s)

O9:

=$M$2*F9

(O; =P*Q_v)

P9:

=O9–L9–N9

(W; =O–K_v–K_f)

Q9:

=P9/F9

(w_e; =W/Q_v)

J9:Q9:

sleep fill handle naar J9:Q20

I21:

=I9

J21:

=SUM(J9:J20)

K21:

=I21+J21

L21:

=SUM(L9:L20)

M21:

=K21-L21

(of: =M20)

N21:

=SUM(N9:N20)

O21:

=SUM(O9:O20)

P21:

=SUM(P9:P20)

Q21:

=P21/F21

I22:

=AVERAGE(I9:I20), sleep fill handle naar I22:P22

Q22:

=P22/F22

I9:P22:

Format, Cells..., Number tabblad, Category Currency,
Decimal Places: 0, Symbol: BF, Negative Numbers: - 1 234 BF

Q9:Q22:

Format, Cells..., Number tabblad, Category Custom,
Type: # ##0.00 BF"/e."

I:Q:

sleep kolomhoofd naar rechts tot alle getallen zichtbaar zijn
(Width: 9.14)

I7:Q8:

Borders knop: knop 2 (dunne lijn onder range)

I21:Q22:

Borders knop: knop 9 (dunne lijn boven en halfdikke lijn onder range)

I7:Q22:

Format, Cells, Border tabblad, Line: halfdik, Presets: Outline, Line: dun, Border: verticaal binnenin

B4:

Jaarlijkse winst:

E4:

W_j

B5:

Gemiddelde winst per eenheid:

E5:

w_je

E4:E5:

Font Size knop: 14, Center knop

F4:

=P21, Format, Cells..., Number tabblad, Category Currency,
Decimal Places: 0, Symbol: BF, Negative Numbers: - 1 234 BF

F5:

=Q21, Format, Cells..., Number tabblad, Category Custom,
Type: # ##0 BF"/e."

F4:G5:

Font Color knop: Green, Bold knop, Merge and Center knop

Opmerkingen:

De hier gebruikte methode om de kosten van de verkochte hoeveelheid te berekenen maakt gebruik van de gemiddelde kost van een verkochte eenheid. Je kan ook de FIFO (eerst worden de oudste producten - beginvoorraad - verkocht) of LIFO (eerst worden de jongste producten - productie - verkocht) methoden gebruiken. In deze gevallen worden de formules in L9 (en de cellen daaronder):

FIFO: =ROUND(IF(F9<=B9, I9*F9/B9, I9+J9*(F9-B9)/C9),0)
(als Q_v<=Q_b: K_b*Q_v/Q_b; als Q_v>=Q_b: K_b+K_p*(Q_v-Q_b)/Q_p)

LIFO: =ROUND(IF(F9<=C9, J9*F9/C9, J9+I9*(F9-C9)/B9),0)
(als Q_v<=Q_p: K_p*Q_v/Q_p; als Q_v>=Q_p: K_p+K_b*(Q_v-Q_p)/Q_b)

De drie methodes leveren hetzelfde resultaat omdat de variabele kosten in elke maand gelijk zijn en er oorspronkelijk geen beginvoorraad is (B9=0, I9=0). Indien er echter een beginvoorraad is en de verhouding tussen de kosten (I9) en hoeveelheid (B9) is niet gelijk aan de variabele kost per eenheid (L4), dan zullen de verschillende methodes verschillende resultaten opleveren. Je kan dit nagaan door in B9 en I9 respectievelijk de waarden 50 en 1400 in te vullen (variabele kost per eenheid van de beginvoorraad: 28 BF).

De hierboven vermelde formules zijn nog niet helemaal correct omdat de beginvoorraad zelf kan bestaan uit delen vervaardigd aan een verschillende variabele kost in verschillende maanden, terwijl in de formules voor elke eenheid in de beginvoorraad dezelfde kost wordt aangerekend. Vooral bij grote beginvoorraden, regelmatig variërende kosten en de LIFO methode, waarbij de oudste voorraden het langst blijven liggen, is de kans op deze fout reëel.

De vraag simuleren (*)

Je kan nu dit model op twee wijzen gebruiken:

om een beschrijving te maken van een actuele situatie.
om een simulatie te maken.

In het eerste geval zijn alle gegevens, inclusief de vraag, bekend tot op een bepaald tijdstip. In het laatste geval maak je voorspellingen en moet je de vraag inschatten.

In het model werden zelfgekozen waarden ingevuld voor de vraag in kolom E. Bij andere waarden zullen de resultaten uiteraard verschillen. Een meer realistische situatie is de volgende:

Maak een schatting van de vraag, waarbij zowel de mogelijkheid bestaat om de vraag als constant in elke periode in te schatten, als om voor elke periode andere waarden voor deze verwachte vraag Q_dv te schatten. Daarbij moet je een onderscheid maken tussen de verwachte vraag Q_dv en de gesimuleerde vraag Q_d, waarbij je het toeval een rol laat spelen door gebruik te maken van statistische functies.

Neem aan dat de vraag een toevalsgrootheid is. Deze toevalsgrootheid is normaal verdeeld met als gemiddelde waarde de verwachte vraag en een zekere spreiding. De verdelingsfunctie heeft dus een klokvorm. Voor meer informatie over de eigenschappen van de normale verdeling wordt verwezen wij naar de cursus "Kansrekening en Statistiek".

Excel beschikt over de functie RAND(), die een toevalsgetal oplevert dat uniform verdeeld is tussen 0 en 1. Dit betekent dus dat alle tussenliggende waarden met evenveel waarschijnlijkheid kunnen voorkomen. Je kan een toevalsgetal verkrijgen dat normaal verdeeld is met gemiddelde waarde m en standaardafwijking s door op dit uniform verdeeld toevalsgetal de inverse functie van de (cumulatieve) normale verdeling toe te passen. Dit gebeurt in Excel met de functie NORMINV(RAND(),m,s).

Het resultaat wordt gevormd door het werkblad Voorraad 3 (figuur 3).

Om hiervan gebruik te maken moet je het vorige werkblad als volgt aanpassen:

Schuif alle cellen in de tabel vanaf kolom E naar rechts op:

E7:E22:

Insert, Cells..., Shift cells right

Hierdoor ontstaat in de tabel een nieuwe lege kolom E. De vormgeving (randen van de cellen) wordt automatisch aangepast zoals gewenst.

Wijzig de titels van kolommen E en F zodat ze de verwachte vraag (Q_dv) en de werkelijke (gesimuleerde) vraag (Q_d) aangeven:

E7:

verwachte vraag

F7:

werkelijke vraag

E8:

Q_dv

Kopieer de vraagcijfers en de formules met totalen van kolom F naar kolom E:

F9:F22:

ctrl+sleep naar E9:E22

E9:E22:

Font Color knop: Automatic

Plaats de formule voor de werkelijke vraag in de cellen van kolom F, waarbij je de verwachte vraag als gemiddelde en 1/20 ervan als standaardafwijking neemt. Dit betekent dat je met een kans van 68% hoogstens één maal de standaardafwijking afwijkt van de gemiddelde waarde, en met een kans van 95% hoogstens 1.96 maal de standaardafwijking afwijkt van de gemiddelde waarde. Wanneer m = 1000, zal met de gekozen waarde s = 1000/20 = 50 overeenkomen.

F9:

=ROUND(NORMINV(RAND(),E9,E9/20),0), sleep fill handle naar F9:F20

Met de ROUND functie verkrijg je gehele waarden voor de werkelijke vraag.

Nu krijg je normaal verdeelde waarden in de kolom F. Deze zullen afhankelijk van het tijdstip verschillen, en bij elke nieuwe actie waarbij een berekening optreedt wijzigen. Dit laatste is storend. Daarom moet je gepaste maatregelen nemen om de zaak te stabiliseren (zie infra).

Het model houdt nu rekening met de werkelijke vraag, maar de getalwaarden veranderen bij nagenoeg elke actie. Zet daarom de formules in kolom F om in hun getalwaarden:

F9:F20:

Copy knop, Edit, Paste Special..., Paste Values

Opmerkingen:

Door de oude waarden van de vraag uit kolom F te kopiëren naar de nieuw toegevoegde kolom E zullen formules die naar de vraag verwijzen naar kolom F blijven verwijzen, zoals gewenst. Wanneer je de waarden van de vraag had verplaatst in plaats van gekopieerd, zouden de formules naar de verwachte vraagcijfers in kolom E verwijzen en had je alle formules opnieuw moeten corrigeren.
Doordat de som en het gemiddelde werden gemaakt van twaalf getallen met gemiddelde waarde in de kolom links ervan, moeten de resultaten in F21 en F22 dicht bij die in E21 en E22 liggen.
Buiten beeld moeten de buitenranden van het werkblad nog worden aangepast. Er werd namelijk slechts een deel van kolom Q naar rechts opgeschoven.

Nieuwe vraagcijfers genereren met een macro

Je zal nu een macro en een Macro knop met naam Wijzig Vraagcijfers maken om nieuwe normaal verdeelde waarden voor de verwachte vraag op te stellen:

In deze macro zal je gebruik maken van de ranges van de verwachte en werkelijke vraag, E9:E20 en F9:F20. Geef deze vooraf de sheet-level namen Qdv en Qd:

Insert, Name, Define...,
Names in Workbook: 'Voorraad 3'!Qdv, Refers to: ='Voorraad 3'!$E$9:$E$20, Add,Names in Workbook: 'Voorraad 3'!Qd, Refers to: ='Voorraad 3'!$F$9:$F$20, Add, Close

Definieer vervolgens de macro:

Tools, Macro, Record New Macro..., WijzigVraagcijfers

Voer nu de volgende acties uit, die in de macro worden geregistreerd:

Selecteer de range met naam Qd in de name list van de formulebalk

Qd:

=ROUND(NORMINV(RAND(),Qdv,Qdv/20),0) ctrl+enter

Copy knop, Edit, Paste Special..., Paste Values

druk op esc om de mogelijkheid tot plakken te beëindigen

Beëindig het registreren van de macro door de Stop Recording knop in te drukken in de getoonde Stop Recording toolbar.

De vraagcijfers zijn nu vervangen door andere.

Bekijk de inhoud van de macro met het commando

Tools, Macro, Macros…, WijzigVraagcijfers, Edit:

Sub WijzigVraagcijfers()

' WijzigVraagcijfers Macro

' Macro recorded 9/11/97 by Marc Andries

Application.Goto Reference:="Qd"

Selection.FormulaR1C1 = "=ROUND(NORMINV(RAND(),Qdv,Qdv/20),0)"

Selection.Copy

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

End Sub

De inhoud van de macro spreekt voor zichzelf:

Application.Goto Reference:="Qd"

selecteert de range Qd

Selection.FormulaR1C1 = "=ROUND(NORMINV(RAND(),Qdv,Qdv/20),0)"

brengt de formule aan in alle cellen van de range.

Selection.Copy

kopieert de nog steeds geselecteerde range Qd

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

plakt de inhoud als getallen (argument Paste:=xlValues). Je kan de argumenten Operation, SkipBlanks en Transpose, die standaard waarden hebben, schrappen.

Application.CutCopyMode = False

beëindigt de mogelijkheid tot plakken (het kader in stippellijn verdwijnt).

Je kan de macro uittesten in Step mode met het commando

Tools, Macro, Macro..., WijzigVraagcijfers, Step knop

Creëer nu de Macro knop met behulp van de Forms toolbar:

Rechtsklik in een werkbalk en klik Forms in het snelmenu om de Forms toolbar te tonen.
Klik de Button knop in de Forms toolbar.
Sleep over de plaats waar de knop moet worden aangebracht (G2:H3). Nu wordt in het werkblad een knop met voorlopige naam Button 1 geplaatst.
Selecteer de naam WijzigVraagcijfers van de macro in het Assign Macro dialoogvenster, dat automatisch verschijnt.
Sleep over de tekst in de knop en vervang deze door de in te typen tekst Wijzig Vraagcijfers.
Wijzig, indien nodig de afmetingen van de knop door zijn handles te verplaatsen of wijzig de plaats door de rand te verslepen.
Klik buiten de knop. De handles verdwijnen.
Sluit de Forms toolbar.

Je kan nu de Macro knop klikken om de macro uit te voeren en nieuwe vraagcijfers te produceren. Uiteraard zullen alle afhankelijke formules eveneens nieuwe resultaten vertonen.

Opmerkingen:

Wanneer je een Macro knop wilt wijzigen, moet je hem ctrl+klikken. De handles verschijnen opnieuw, zodat je de afmetingen van de knop opnieuw kunnen wijzigen en de tekst erin kan wijzigen. Je kan de knop dan eveneens verwijderen met de del toets.
Je kan de Macro knop ook klikken met de rechtermuisknop. Dan verschijnt het Button snelmenu met de commando's Cut, Copy, Paste, Edit Text, Grouping (submenu met de commando's Group, Ungroup en Regroup), Order (submenu met de commando's Bring to Front, Send to Back, Bring Forward en Send Backward), Assign Macro... en Format Control…, die je op de knop kan uitvoeren.

Een aantal speciale gevallen onderzoeken met de Solver

Je kan je nu de vraag stellen voor welke waarde van Q en P de winst per eenheid het grootste zal zijn.

Door de waarde van de verkoopprijs P te verhogen zal uiteraard de winst toenemen, maar het model wordt dan onrealistisch: Normaal moeten de vraagcijfers negatief reageren op een prijstoename en dit is niet in het model voorzien. Je kan uiteraard de constante waarden voor de verwachte vraag vervangen door een dalende functie van de prijs. De vorm en coëfficiënten van de functie moeten dan echter realistisch worden gekozen, en zijn bij voorkeur het resultaat van waargenomen feiten.

Je kan echter in dit model wel de invloed van een verhoging of verlaging van de productie Q onderzoeken en deze optimaliseren zodat de winst per eenheid zo groot mogelijk wordt. Dit kan op drie manieren gebeuren:

Door andere waarden voor Q in te vullen en via "trial and error" naar een maximale waarde voor de winst (in cel F4) te zoeken.

Door beroep te doen op de Solver add-in.

Door met het commando Data, Table... een tabel met waarden voor Q en W_j op te stellen.

De beide laatste gevallen worden in de rest van dit hoofdstuk geïllustreerd.

Eerst wordt met de Solver de waarde voor Q bepaald die een maximale winst oplevert. Vervolgens wordt het Break Even Point opgespoord (de productie die juist geen winst of verlies oplevert). Ten gevolge van de bewaarkosten zal er echter een tweede, hoge waarde voor Q optreden waarbij er geen winst of verlies is. Ook deze waarde wordt met de Solver opgespoord.

Opmerkingen:

De bekomen resultaten zullen afhankelijk zijn van de vraagcijfers. In het voorbeeld in deze tekst bedragen de twaalf vraagcijfers: 915, 1169, 1022, 826, 908, 975, 1087, 1089, 987, 982, 965, 986.
Alle bewerkingen worden uitgevoerd in het werkblad Voorraad 3.

De productie bepalen die een maximale winst oplevert

Bepaal eerst de waarde voor Q die de winst zo groot mogelijk maakt. Roep de Solver op met het commando

Tools, Solver...

Nu verschijnt het Solver Parameters dialoogvenster (figuur 4), waarin je de volgende zaken invult:

Set Target Cell:

$F$4 (W_j)

Equal to:

Max (maak de inhoud van deze cel maximaal).

By Changing Cells:

$F$2 (Q)

Add...

Plaats de restrictie $F$2 = integer in het Add Constraint dialoogvenster (figuur 5) en klik OK. De restrictie wordt in de Subject to the Constraints lijst geplaatst.

Solve

Voer de optimalisatie uit.

Je kan optimalisatiecriteria kiezen en restricties (Constraints) toevoegen (Add...), wijzigen (Change...) of verwijderen (Delete). Het is mogelijk een celinhoud te minimaliseren, maximaliseren of gelijk te maken aan een bepaalde waarde. Je kan één of meerdere cellen of ranges opgeven (By Changing Cells; door ze met komma's te scheiden), waarvan je de waarde kan veranderen. Je kan ook alle te wijzigen cellen en restricties verwijderen (Reset All).

Solver werkt een aantal gevallen uit. Dit wordt weergegeven in de statusbalk onderaan. Dit kan enige tijd in beslag nemen. Indien er een resultaat wordt verkregen verschijnt dit bericht in het Solver Results dialoogvenster (figuur 6), waarin je de oplossing kan handhaven (invullen; Keep Solver Solution) of terugkeren naar de originele waarden (Restore Original Values). Je kan eveneens met de Save Scenario... knop het opgestelde scenario een naam geven in het Save Scenario dialoogvenster (figuur 7), waardoor het bewaard wordt.

De gevonden oplossing levert (ogenschijnlijk) de grootste winst W_j=51605 BF bij een maandelijkse productie van Q=974 eenheden.

Dit is echter niet de echte grootste winst! Je kan dit nagaan door de Solver nogmaals de maximale winst te laten berekenen met als startwaarde de zojuist gevonden Q=974 eenheden. Nu wordt een grotere grootste winst W_j=52023 BF bij een maandelijkse productie van Q=979 eenheden gevonden. Wanneer je de Solver een derde maal aan het werk zet verandert de gevonden oplossing niet meer.

Je kan nu een rapport opstellen (klik de gewenste rapporten in de Reports lijst in het Solver Results dialoogvenster), dat in een afzonderlijk werkblad zal worden geplaatst. Er zijn drie soorten rapporten, maar bij restricties tot gehele waarden is enkel het Answer Report (figuur 8) mogelijk, dat in een afzonderlijk werkblad, links van het geactiveerde wordt geplaatst en de standaard naam Answer Report 1 krijgt. Je kan de afdruk van dit rapport beheren met het commando

View, Report Manager…

Opmerkingen:

Vooral bij sterk niet-lineaire problemen (zoals hier met o.a. de MIN functie) die enkel gehele resultaten aanvaarden is het gevonden resultaat niet steeds van de eerste keer correct.
Je kan met de Options knop in het Solver Parameters de berekeningswijze instellen via het Solver Options dialoogvenster (figuur 9).

Wanneer je in het Solver Options dialoogvenster Show Iteration Results aankruist, wordt de wijziging van elke cel in het werkblad getoond, waardoor je een beter zicht krijgt op de evolutie naar de oplossing. Bij elke tussentijdse oplossing wordt gepauzeerd en een Show Trial Solution dialoogvenster getoond, waarin je de berekeningen kan verder zetten (Continue) of afbreken (Stop). Wanneer je de berekeningen overdoet vanaf een beginwaarde 1000, treden de volgende tussenresultaten op:

Q

W_j

1000

48638 BF

975

51725 BF

978

51973 BF

974

51605 BF

974

51605 BF

980

51888 BF

979

52023 BF

Je kan met de esc toets de iteraties eveneens afbreken.

In bepaalde gevallen functioneren de restricties van het type Integer niet. In dit geval zullen de resulterende Changing Cells geen gehele waarden bevatten, al is dit opgegeven. Het is dan best de berekening af te breken wanneer er geen of weinig wijziging meer is in de te optimaliseren waarde bij opeenvolgende iteraties. Vervolgens kan je manueel de twee dichtstbij gelegen gehele waarden invullen en kijken welke het beste resultaat oplevert. Dit is dan de gezochte optimale waarde.

De berekening kan eventueel lang duren, vooral bij restricties van het type Integer en wanneer er charts of tabellen zijn, die op de wijzigende cellen gebaseerd zijn. Het is mogelijk dat na verloop van de maximale tijd, ingesteld in het Solver Options dialoogvenster, een Show Trial Solution dialoogvenster verschijnt, waarin je kan beslissen of je verder wilt gaan (Continue) of het zoeken afbreken (Stop). Wanneer dergelijke berichten regelmatig verschijnen kan je een aantal parameters in het Solver Options dialoogvenster wijzigen (Max Time, Iterations, Precision, Tolerance en Convergence).

De Tolerance parameter in het Solver Options dialoogvenster is enkel relevant bij restricties van het type Integer, en wordt bij trage vooruitgang best verhoogd. De Convergence parameter is enkel relevant bij niet-lineaire problemen. Algemeen zullen grotere waarden voor Max Time en Iterations en kleinere waarden voor Precision, Tolerance en Convergence een nauwkeuriger resultaat opleveren ten koste van meer en langduriger rekenwerk.

De nulpunten van de winst bepalen

Interessant is een tweede geval waarbij je op zoek gaat naar het Break Even Point (BEP). Dit is de productiewaarde waarbij er geen winst of verlies is. Theoretisch wordt deze waarde bekomen uit de formule:

Productiekosten = Omzet

F + c_p * Q = P * Q

waaruit volgt:

Q^BEP = F / ( P - c_p) = 10000 / (45 - 30) = 667 (afgerond)

In de praktijk zal deze waarde slechts bij benadering gelden, omdat er in deze formule wordt van uitgegaan dat er geen begin- en eindvoorraden zijn en steeds exact aan de vraag wordt voldaan. De bewaarkosten zullen daarenboven verantwoordelijk zijn voor een tweede nulpunt van de winst. Volgens de theoretische formule stijgt de winst immers steeds met de productie (in de onrealistische veronderstelling dat steeds de ganse productie wordt verkocht). In de praktijk daarentegen zal je bij een zeer grote productie met aanzienlijke voorraden blijven zitten, die bewaarkosten met zich meebrengen. Daardoor zal de winst vanaf een zekere (maximale) waarde gaan dalen en uiteindelijk weer een nulwaarde bereiken. Ga nu op zoek gaan naar deze twee nulwaarden.

Tools, Solver…

Vul nu in het Solver Parameters dialoogvenster in:

Set Target Cell:

$F$4 (W_j)

Equal to:

Value of 0

By Changing Cells:

$F$2 (Q)

Subject to the Constraints:

$F$2 = integer

Solve

Nu wordt gemeld dat er geen exacte oplossing kan worden gevonden: Solver could not find a feasable solution. Dit is uiteraard te verwachten met de beperking dat enkel gehele waarden voor Q zijn toegelaten. Nochtans wordt de benaderde waarde getoond. Wanneer je hiermee niet tevreden bent bestaat er een eenvoudige truc om dit op te lossen:

Vervang de formule in cel F4 (W_j; =Q21) door

F4:

=ABS(Q21)

Aangezien je toch niet geïnteresseerd bent in verliezen, maakt dit niets uit in normale situaties. Je kan nu echter trachten de kleinste waarde (dichtst bij 0 liggende waarde) voor Q21 te zoeken, door in het Solver Parameters dialoogvenster in te vullen:

Set Target Cell:

$F$4 (ABS(W_j))

Equal to:

Min

By Changing Cells:

$F$2 (Q)

Subject to the Constraints:

$F$2 = integer

Solve

Je zal hierdoor steeds één oplossing krijgen. Om beide oplossingen te krijgen kan je een extra restrictie toevoegen:

Subject to the Constraints:

$F$2 <= 900 (het lage nulpunt - Break Even Point)

of:

Subject to the Constraints:

$F$2>= 1100 (het hoge nulpunt)

Je kan een restrictie wijzigen of verwijderen door ze te selecteren en de Change... of Delete knop in te drukken. Bij het zoeken naar de nulpunten vervang je in het werkblad de startwaarde voor Q door een waarde die reeds aan de restrictie voldoet.

De aldus bereikte oplossingen zijn:

Winst (of verlies) 60 BF bij 667 eenheden. In cel Q21 zie je dat dit een winst is (+60 BF).
Winst (of verlies) 118 BF bij 1155 eenheden. Dit is een verlies (Q21: –118 BF).

Uiteraard zullen de gevonden waarden anders zijn bij andere vraagcijfers. Je kan dit uittesten.

Wanneer je alle scenario's hebt bewaard kan je een scenario opvragen met het commando

Tools, Scenarios…

In het Scenario Manager dialoogvenster (figuur 10) kan je de scenario's tonen en beheren.

De winst bepalen in functie van de productie in een data table en een chart (*)

Je hebt met de Solver als het ware een tekenstudie van de functie W_j(Q) gemaakt: Je hebt de nulpunten en het maximum gezocht met behulp van het idee dat je over deze functie kon verkrijgen door redenering. Nu ga je een stap verder door voor een groot aantal waarden van de productie de winst te berekenen in een data table en deze daarna grafisch uit te zetten. Kies deze waarden tussen 600 en 1200, zodat de twee nulpunten in het interval vallen.

Plaats de data table en chart onderaan in het werkblad Voorraad 4.

De data table van de winst opstellen (*)

Excel heeft een commando, Data, Table..., waarmee je een één- of tweedimensionale tabel kan opstellen.

Een ééndimensionale tabel bevat twee of meer kolommen (of rijen). In de linkerkolom worden de waarden van de onafhankelijke grootheid (Q) vermeld, in de overige kolommen zullen de functiewaarden (jaarlijkse winst W_j en theoretische jaarlijkse winst W_th) worden berekend. De eerste rij van de tabel bevat in de rechterkolommen de formules.

Stel de data table van de winst als volgt op:

J24:

Q

K24:

W_j

L24:

W_th

J24:L24:

Font Size knop: 14, Center knop

J25:

=F2

(Q)

K25:

= Q21

(W_j)

L25:

=((M2-M4)*F2-M3)*12

((P–c_p)*Q–F)*12

J24:L25:

Borders lijst, knop 10 (overal dunne lijn)

J26:J27:

600, 610, sleep fill handle naar J26:J86

J25:J86:

Format, Cells..., Number tabblad, Category Custom: 0" e."

K25:L86:

Format, Cells..., Number tabblad, Category Currency:
Decimal Places: 0, Symbol: BF, Negative Numbers: - 1 234 BF

J26:L86:

Format, Cells, Border tabblad, Line: dun, Presets: Outline, Border: verticaal binnenin

J25:L86:

Data, Table..., Column Input Cell: $F$2

De data table wordt gegenereerd in de range K26:L86 (figuur 12). In elke cel wordt de array formule {=TABLE(,F2)} geplaatst.

Opmerkingen:

Bij het opstellen van de formules moet je er rekening mee houden dat de winst wordt berekend op jaarbasis. Daarom moeten zowel de maandelijkse productie Q als de maandelijkse vaste kost F met 12 worden vermenigvuldigd in de formule voor de theoretische winst op jaarbasis (L25).

In het Table dialoogvenster (figuur 11) kan je opgeven welke cel de inputgrootheid is voor de rij (Row input cell) en kolom (Column input cell). Bij een ééndimensionale data table wordt slechts één van de twee waarden opgegeven (gewoonlijk de Column input cell); bij een tweedimensionale data table worden beide waarden opgegeven (zie infra).

De bovenste rij (J24:L24) behoort niet bij de data table en wordt dus niet geselecteerd. De cellen erin verduidelijken de betekenis van de waarden.

De formule in cel J25 is niet nodig voor het commando Data, Table…. Juist zoals de titels in cellen J24:L24 verduidelijkt ze de tabel. Met de inputwaarde in J25 corresponderen namelijk de outputwaarden in K25:L25. Je mag nochtans niet naar deze cel verwijzen in het Table dialoogvenster, aangezien de outputgrootheden afhangen van cel F2 (juist zoals J25 zelf). Een wijziging in de waarde van J25 zal de waarde in F2 niet wijzigen en de outputwaarden dus evenmin.

Je kan zoveel outputkolommen opgeven als je wilt.

De tabel bevat vele formules, die bij elke wijziging van een gegeven zullen worden herberekend. Dit blijkt uit de vertraagde werking van het spreadsheet programma, de boodschap Calculating in de statusbalk en de tekst Table 1 in de formulebalk tijdens het herberekenen. Je kan deze vertraging teniet doen door de automatische herberekening van de tabel uit te zetten. Dit gebeurt met het commando

Tools, Options..., Calculation tabblad

In het Calculation tabblad van het Options dialoogvenster kies je de optie Automatic except tables. Om bij deze instelling ook de tabellen te herberekenen zal je nu de Calc Sheet of Calc Now knoppen moeten indrukken. Wanneer zelfs dan het herberekenen nog te lang duurt, kan je de automatische herberekening volledig uitzetten door de Manual optie te kiezen.

Zodra een gegeven gewijzigd wordt zal nu Calculate in de statusbalk verschijnen wat aangeeft dat de resultaten niet consistent zijn zolang de Calc Sheet of Calc Now knop niet werden ingedrukt. Met Calc Sheet wordt het hele werkblad opnieuw herberekend; met Calc Now of functietoets F9 worden alle geopende documenten herberekend. Bij het bewaren worden alle te bewaren documenten eveneens herberekend wanneer Recalculate before save is aangekruist en de berekening manueel gebeurt.
Je kan de drie met de Solver berekende gevallen aan de hand van de tabel verifiëren.
De aanwezigheid van data tables zal de Solver enorm vertragen, tenzij de Automatic except tables of Manual opties gekozen zijn in het Calculation tabblad van het Options dialoogvenster.
Het is niet mogelijk naar individuele cellen uit de range L26:M86 met de array formule te verwijzen of deze cellen afzonderlijk te wijzigen of leeg te maken.

De data table van de winst in een chart voorstellen (*)

Stel nu de data table in een chart voor. Bij het plaatsen van de theoretische formules heb je er reeds voor gezorgd dat deze mee in de chart kunnen worden opgenomen. Je stelt best een XY chart op, omdat dit de enige is die je de mogelijkheid biedt de schaal van de X-as te wijzigen. Neem de hele tabel in de chart op, behalve rij 25:

Selecteer de in de chart op te nemen ranges (J24:L24,J26:L86)
Stel de chart op met de Chart Wizard en plaats hem links van de data table. Kies voor een XY (Scatter) chart, formaat 3 (vloeiende lijnen), Series in Columns, en vullen de titels in.

De gedaante van de chart is echter nog niet zoals gewenst. Het belangrijkste tekort is dat op de X-as waarden van 0 tot 1500 i.p.v. van 600 tot 1200 worden getoond. Dubbelklik daarom de ingebedde chart en brengen de volgende wijzigingen aan:

dubbelklik X-as, Scale..., Minimum: 600 (i.p.v. 0), Maximum: 1200 (i.p.v. 1500),
Major unit: 200 (i.p.v. 500)

Verplaats daarna de chart en wijzig zijn afmetingen (figuur 12).

Een tweedimensionale data table opstellen

Je kan naast een ééndimensionale data table, waarin slecht één inputgrootheid verandert, ook een tweedimensionale data table opstellen waarin je zowel voor de Row input cell als voor de Column input cell een waarde moet invullen. In dit geval zal slechts één output grootheid in de tabel kunnen worden opgenomen. Maak ter illustratie een data table waarin je de jaarlijkse winst W_j berekent voor variërende waarden van de maandelijkse productie Q en de variabele kost c_p (figuur 13):

O24:

W_j

P24:

c_p

N26:

Q

O25:

=Q21

(formule voor de winst)

O26:O46:

1000, 1001, ..., 1020

(waarden voor Q)

P25:T25:

28, 29, 30, 31, 32

(waarden voor c_p)

O25:T46:

Data, Table..., Row Input Cell: $M$4, Column Input Cell: $F$2

Opmerkingen:

Wanneer de automatische herberekening uitstaat en alle cellen in de tabel hetzelfde resultaat vertonen, moet je herberekenen met Tools, Options..., Calculation tabblad, Calc Sheet.
Met het commando Data, Table... kan je onderzoeken wat er zou gebeuren wanneer je één of twee inputwaarden wijzigt. Men noemt dit een What if analyse. Met het commando Tools, Goal Seek... kan je het omgekeerde probleem aanpakken: Je kan je afvragen hoe één inputwaarde moet veranderen om het resultaat een bepaalde waarde te doen aannemen. Het commando Tools, Solver... is hier een uitbreiding van. Je kan nu meerdere inputwaarden laten wijzigen om een bepaald resultaat te verkrijgen of te optimaliseren.

Een model met variabele productie (*)

Je zal nu een tweede model opstellen waarin je de maandelijkse productie bepaalt in functie van de verwachte vraag en de beginvoorraad voor die maand.

Stel dit model op in het werkblad Voorraad 5. Ga hierbij uit van een kopie van het werkblad Voorraad 3, omdat de data tables en charts zullen afwijken van die in Voorraad 4. Daarenboven zouden bij het kopiëren van een werkblad met ingebedde charts, de datareeksen in de chart op de originele data en niet op de gekopieerde gebaseerd zijn. Om al deze redenen is het veiliger de chart opnieuw op te stellen.

Het werkblad aanpassen (*)

Neem aan dat je in dit nieuwe werkblad elke maand een andere productie kan kiezen. De vraag is nu:

Welke productie is optimaal?

Theoretisch zal het antwoord zijn dat die productie de hoogste winst oplevert, die ervoor zorgt dat juist aan de vraag kan worden voldaan. Aangezien je echter de werkelijke vraagcijfers niet kent, maar enkel kan simuleren, moet je een productie kiezen die er voor zorgt dat het aanbod juist voldoet aan de verwachte vraag:

Q_a = Q_b + Q_p = Q_d

waaruit:

Q_p = Q_d – Q_b

Het is wellicht aangewezen een zekere veiligheidsmarge in acht te nemen. Stel deze voor door dQ en gebruik ze als stuurgrootheid in cel F2. Pas de formule voor de productie aan zodat ze wordt:

Q_p = Q_dv – Q_b + dQ

De wijzigingen aan het werkblad zijn gering:

E2:

dQ

F2:

0

(dQ; initiële waarde:
geen veiligheidsmarge)

C9:

=E9–B9+$F$2, sleep fill handle naar C9:C20

(Q_p; =Q_dv–Q_b+dQ)

Het werkblad zou nu een begin- en eindvoorraad 0 tonen in elke maand, indien de werkelijke en verwachte vraagcijfers gelijk waren. Waarschijnlijk is het echter beter een veiligheidsmarge te nemen. Kies daarom 20 als waarde voor dQ (figuur 14):

F2:

20

(dQ)

Met de Solver kan je nu de waarde van dQ berekenen die een maximale winst oplevert.

De data table en chart van de winst opstellen (*)

Stel de data table van de winst op, waarbij je voor de inputgrootheid dQ waarden van –100 tot +100 en een extra kolom voor de jaarlijkse productie (Q_pj) voorziet:

J24:

dQ

K24:

Q_pj

L24:

W_j

M24:

W_th

J24:M25:

Font Size knop: 14, Center knop

J25:

=F2

(dQ)

K25:

=C21

(Q_pj)

L25:

=Q21

(W_j)

M25:

=((M2-M4)*C21-M3*12

(P–c_p)*Q_pj-F*12

J24:M25:

Borders knop, knop 10 (overal dunne lijn)

J26:J27:

–100,–95, sleep fill handle naar J26:J66

J25:K66:

Format, Cells..., Number tabblad, Category Custom: 0" e."

L25:M66:

Format, Cells..., Number tabblad, Category Currency:
Decimal Places: 0, Symbol: BF, Negative Numbers: - 1 234 BF

J26:M66:

Format, Cells, Border tabblad, Line: dun, Presets: Outline, Border: verticaal binnenin

J25:M66:

Data, Table..., Column input cell: F2

Bij het opstellen van de chart met de Chart Wizard moet je ook nu achteraf aanpassingen doen:

dubbelklik X-as, Scale..., Minimum: -100 (i.p.v. -150), Maximum: 100 (i.p.v. 150)

Je verkrijgt nu een goede chart links van de data table (figuur 15).

Andere modellen (*)

Op een analoge manier kan je het model aanpassen zodat je simulaties krijgt over

de reactie van de werkelijke vraag op een prijsverhoging of -verlaging.
de (negatieve) reactie van de vraag op een stock breuk (niet kunnen voldoen aan de vraag - sommige klanten zullen hun vertrouwen verliezen en elders gaan).
het bederven van bewaarde producten (waardoor de eindvoorraad geringer is dan het niet verkochte deel, en de daaraan verbonden productiekosten als verlies moeten worden opgevat).
de invloed van een staking (geen productie in een bepaalde maand).
een verhoging van bepaalde kosten.
begrenzingen op maximale productie of voorraadhoeveelheden enz.

Het Tools menu

Tot slot volgt een overzicht van het Tools menu. Vele commando's hieruit werden in dit en de vorige hoofdstukken behandeld. Het Tools menu bevat de volgende commando's:

Tools menu

Spelling…

Controleer de spelling. Je kan binnen Excel de taal niet veranderen!

AutoCorrect…

Beheer het automatisch corrigeren van spellingfouten. In vele gevallen zet je deze opties beter uit, om het hardnekkig automatisch "verbeteren" van woorden die juist zijn te voorkomen.

LookUp Reference…

Zoek een sleutelwoord op in Microsoft Bookshelf Basics of een andere referentiebibliotheek.

Share Workbook…

Beheer op welke wijze (delen van) een werkboek door verschillende gebruikers tegelijk kunnen worden gebruikt.

Track Changes

Dit is een submenu met de commando's Highlight Changes…, Accept or Reject Changes…. Je kan opgeven wijzigingen te markeren, en eventueel door te voeren. Dit is vooral nuttig wanneer meerdere gebruikers een zelfde werkboek bewerken. Het wordt dan ook met Share Workbook… gecombineerd.

Merge Workbooks…

Beheer het samenvoegen van meerdere werkboeken.

Protection…

Dit is een submenu met de commando's Protect Sheet…, Protect Workbook…, Protect and Share Workbook…. Je kan er de beveiliging op allerlei niveaus mee aanzetten en beheren.

Goal Seek…

Ga op zoek naar één te wijzigen cel om in een afhankelijke cel een vooropgegeven waarde te verkrijgen.

Scenarios…

Beheer scenario's, die je bijvoorbeeld met de Solver hebt opgesteld.

Auditing

Dit is een submenu met de commando's Trace precedents, Trace Dependents, Trace Errors, Remove All Arrows, Show Auditing Toolbar. Je kan er afhankelijkheden tussen cellen en fouten mee beheren. Je kan hierbij gebruik maken van de Auditing toolbar.

Solver…

Zoek waarden voor cellen om in een afhankelijke cel een vooropgegeven waarde te verkrijgen (of een minimale of maximale waarde).

Macro

Dit is een submenu met de commando's Macros…, Record New Macro…, Visual Basic Editor.

Add-Ins…

Beheer add-ins. Dit zijn extra mogelijkheden, die vaak niet automatisch worden geïnstalleerd of in de menu's opgenomen. Hierbij worden .xla bestanden gebruikt, die uiteraard op je schijf moeten zijn gekopieerd. Nadat een add-in is geïnstalleerd is er vaak een commando (meestal in het Tools menu) toegevoegd, zoals Solver… of Data Analysis….

Je kan de volgende add-ins beheren: Analysis Toolpak, Analysis Toolpak – VBA, AutoSave, Conditional Sum Wizard, File Conversion Wizard, Internet Assistant Wizard, Lookup Wizard, Microsoft AccessLinks Add-In, Microsoft Bookshelf Integration, MS Query Add-in for Excel 5 Compatibility, ODBC Add-In, Report Manager, Solver Add-In, Template Utilities, Template Wizard with Data Tracking, Update Add-in Links, Web Form Wizard.

Customize…

Beheer de menubalken en werkbalken. Dit commando is equivalent aan View, Toolbars, Customize….

Options…

Bepaal allerlei instellingen die algemeen gelden of voor een bepaald object (zoals een werkboek of een venster).

Wizard

Dit is een submenu met de commando's Lookup…, File Conversion…, Conditional Sum…, Web Form…. Je kan hiermee de aangeduide Wizards oproepen.

inhoud vorige volgende

A1:	naam, Italic knop
B1:	Productie, voorraad en verkoop
B1:G1:	Bold knop, Font Size knop: 14, Merge and Center knop
B2:	maandelijkse productie
E2:	Q, Font Size knop: 14, Center knop
F2:	1000, Format, Cells..., Number tabblad, Category: Custom, Type: 0" e.", Font Color knop: Dark Blue, Bold knop, Center knop
B4:	begin voorraad	B5:	Q_b
C4:	productie	C5:	Q_p
D4:	aanbod	D5:	Q_a
E4:	vraag	E5:	Q_d
F4:	verkocht	F5:	Q_v
G4:	eind voorraad	G5:	Q_e
H4:	gem. Voorraad	H5:	Q_s
A5:	maand
B4:H5:	Format, Cells..., Alignment tabblad, Horizontal Center, Vertical Center, Wrap text
B5:H5:	Font Size knop: 14
A6:	januari, sleep fill handle naar A6:A17
A18:	jaartotaal
A19:	gem. maand
A:A:	Format, Column, AutoFit Selection
B6:	0, Font Color knop: Dark Blue		(Q_b)
B7:	=G6, sleep fill handle naar B7:B17
C6:	=$F$2, sleep fill handle naar C6:C17		(Q_p; =Q)
D6:	=B6+C6, sleep fill handle naar D6:D17		(Q_a; =Q_b+Q_p)
E6:E17:	950, 1200, 1000, 900, 950, 1000, 1100, 1050, 950, 1000, 900, 950, Font Color knop: Dark Blue		(Q_d)
F6:	=MIN(D6,E6), sleep fill handle naar F6:F17		(Q_v)
G6:	=D6–F6, sleep fill handle naar G6:G17		(Q_e; =Q_a–Q_v)
H6:	=(B6+G6)/2, sleep fill handle naar H6:H17		(Q_s; =(Q_b+Q_e)/2)
B18:	=B6
C18:	=SUM(C6:C17)
D18:	=B18+C18
E18:	=SUM(E6:E17)
F18:	=SUM(F6:F17)
G18:	=D18–F18 (of: =G17)
H18:	=AVERAGE(H6:H17)
B19:	=AVERAGE(B6:B17), sleep fill handle naar B19:H19
B6:H19:	Format, Cells..., Number tabblad, Category Custom, Type: 0" e."
A4:H5:	Borders knop: knop 2 (dunne lijn onderaan)
A18:H19:	Borders knop: knop 9 (dunne lijn boven en halfdikke lijn onder range)
A4:H19:	Format, Cells, Border tabblad, klik halfdikke lijn, Presets: Outline knop, klik dunne lijn, Border: verticaal lijn binnenin
	Tools, Options..., View tabblad, verwijder de aankruising van Gridlines

B1:	Window, Freeze Panes
	Zoom knop: 75%
3,5:	Insert, Rows
I2:	verkoopprijs:	L2:	P
I3:	vaste productiekost:	L3:	F
I4:	variabele productiekost:	L4:	c_p
I5:	bewaarkost:	L5:	C_s
L2:L5:	Font Size knop: 14, Center knop
M2:	45, Format, Cells..., Number tabblad, Category Custom, Type: # ##0 BF"/e."
M3:	10000, Format, Cells..., Number tabblad, Category Custom, Type: # ##0 BF'/mnd"
M4:	30, Format, Cells..., Number tabblad, Category Custom, Type: # ##0 BF"/e."
M5:	5, Format, Cells..., Number tabblad, Category Custom, Type: # ##0 BF"/e./mnd"
M2:N2:	Bold knop, Font Color knop: Dark Blue, Merge and Center knop
M3:N3:	Bold knop, Font Color knop: Dark Blue, Merge and Center knop
M4:N4:	Bold knop, Font Color knop: Dark Blue, Merge and Center knop
M5:N5:	Bold knop, Font Color knop: Dark Blue, Merge and Center knop
I7:	var. kost begin voorraad	I8:	K_b
J7:	var. kost productie	J8:	K_p
K7:	var. kost aanbod	K8:	K_a
L7:	var. kost verkochte hoev.	L8:	K_v
M7	var. kost eind voorraad	M8:	K_e
N7:	periode kost	N8:	K_f
O7:	omzet	O8:	O
P7:	winst	P8:	W
Q7	winst per eenheid	Q8:	w_e
I7:Q8:	Format, Cells..., Alignment tabblad, Horizontal Center, Vertical Center, Wrap Text
I8:Q8:	Font Size lijst: 14
I9:	0, Font Color knop: Dark Blue		(K_b)
I10:	=M9, sleep fill handle naar I10:I20
J9:	=$M$4*C9		(K_p; =c_p*Q_p)
K9:	=I9+J9		(K_a; =K_b+K_p)
L9:	=ROUND(K9*F9/D9,0)		(K_v; = K_a*Q_v/Q_a)
M9:	=K9–L9		(K_e; =K_a–K_v)
N9:	=$M$3+$M$5*H9		(K_f; =F+c_s*Q_s)
O9:	=$M$2*F9		(O; =P*Q_v)
P9:	=O9–L9–N9		(W; =O–K_v–K_f)
Q9:	=P9/F9		(w_e; =W/Q_v)
J9:Q9:	sleep fill handle naar J9:Q20
I21:	=I9
J21:	=SUM(J9:J20)
K21:	=I21+J21
L21:	=SUM(L9:L20)
M21:	=K21-L21		(of: =M20)
N21:	=SUM(N9:N20)
O21:	=SUM(O9:O20)
P21:	=SUM(P9:P20)
Q21:	=P21/F21
I22:	=AVERAGE(I9:I20), sleep fill handle naar I22:P22
Q22:	=P22/F22
I9:P22:	Format, Cells..., Number tabblad, Category Currency, Decimal Places: 0, Symbol: BF, Negative Numbers: - 1 234 BF
Q9:Q22:	Format, Cells..., Number tabblad, Category Custom, Type: # ##0.00 BF"/e."
I:Q:	sleep kolomhoofd naar rechts tot alle getallen zichtbaar zijn (Width: 9.14)
I7:Q8:	Borders knop: knop 2 (dunne lijn onder range)
I21:Q22:	Borders knop: knop 9 (dunne lijn boven en halfdikke lijn onder range)
I7:Q22:	Format, Cells, Border tabblad, Line: halfdik, Presets: Outline, Line: dun, Border: verticaal binnenin
B4:	Jaarlijkse winst:
E4:	W_j
B5:	Gemiddelde winst per eenheid:
E5:	w_je
E4:E5:	Font Size knop: 14, Center knop
F4:	=P21, Format, Cells..., Number tabblad, Category Currency, Decimal Places: 0, Symbol: BF, Negative Numbers: - 1 234 BF
F5:	=Q21, Format, Cells..., Number tabblad, Category Custom, Type: # ##0 BF"/e."
F4:G5:	Font Color knop: Green, Bold knop, Merge and Center knop

F9:F22:	ctrl+sleep naar E9:E22
E9:E22:	Font Color knop: Automatic

Selecteer de range met naam Qd in de name list van de formulebalk
Qd:	=ROUND(NORMINV(RAND(),Qdv,Qdv/20),0) ctrl+enter
	Copy knop, Edit, Paste Special..., Paste Values
druk op esc om de mogelijkheid tot plakken te beëindigen

Set Target Cell:	$F$4 (W_j)
Equal to:	Max (maak de inhoud van deze cel maximaal).
By Changing Cells:	$F$2 (Q)
Add...	Plaats de restrictie $F$2 = integer in het Add Constraint dialoogvenster (figuur 5) en klik OK. De restrictie wordt in de Subject to the Constraints lijst geplaatst.
Solve	Voer de optimalisatie uit.

Q	W_j
1000	48638 BF
975	51725 BF
978	51973 BF
974	51605 BF
974	51605 BF
980	51888 BF
979	52023 BF

Set Target Cell:	$F$4 (ABS(W_j))
Equal to:	Min
By Changing Cells:	$F$2 (Q)
Subject to the Constraints:	$F$2 = integer
Solve

J24:	Q
K24:	W_j
L24:	W_th
J24:L24:	Font Size knop: 14, Center knop
J25:	=F2	(Q)
K25:	= Q21	(W_j)
L25:	=((M2-M4)F2-M3)12	((P–c_p)Q–F)12
J24:L25:	Borders lijst, knop 10 (overal dunne lijn)
J26:J27:	600, 610, sleep fill handle naar J26:J86
J25:J86:	Format, Cells..., Number tabblad, Category Custom: 0" e."
K25:L86:	Format, Cells..., Number tabblad, Category Currency: Decimal Places: 0, Symbol: BF, Negative Numbers: - 1 234 BF
J26:L86:	Format, Cells, Border tabblad, Line: dun, Presets: Outline, Border: verticaal binnenin
J25:L86:	Data, Table..., Column Input Cell: $F$2

O24:	W_j
P24:	c_p
N26:	Q
O25:	=Q21	(formule voor de winst)
O26:O46:	1000, 1001, ..., 1020	(waarden voor Q)
P25:T25:	28, 29, 30, 31, 32	(waarden voor c_p)
O25:T46:	Data, Table..., Row Input Cell: $M$4, Column Input Cell: $F$2

E2:	dQ
F2:	0	(dQ; initiële waarde: geen veiligheidsmarge)
C9:	=E9–B9+$F$2, sleep fill handle naar C9:C20	(Q_p; =Q_dv–Q_b+dQ)

J24:	dQ
K24:	Q_pj
L24:	W_j
M24:	W_th
J24:M25:	Font Size knop: 14, Center knop
J25:	=F2	(dQ)
K25:	=C21	(Q_pj)
L25:	=Q21	(W_j)
M25:	=((M2-M4)C21-M312	(P–c_p)Q_pj-F12
J24:M25:	Borders knop, knop 10 (overal dunne lijn)
J26:J27:	–100,–95, sleep fill handle naar J26:J66
J25:K66:	Format, Cells..., Number tabblad, Category Custom: 0" e."
L25:M66:	Format, Cells..., Number tabblad, Category Currency: Decimal Places: 0, Symbol: BF, Negative Numbers: - 1 234 BF
J26:M66:	Format, Cells, Border tabblad, Line: dun, Presets: Outline, Border: verticaal binnenin
J25:M66:	Data, Table..., Column input cell: F2

Tools menu
Spelling…	Controleer de spelling. Je kan binnen Excel de taal niet veranderen!
AutoCorrect…	Beheer het automatisch corrigeren van spellingfouten. In vele gevallen zet je deze opties beter uit, om het hardnekkig automatisch "verbeteren" van woorden die juist zijn te voorkomen.
LookUp Reference…	Zoek een sleutelwoord op in Microsoft Bookshelf Basics of een andere referentiebibliotheek.
Share Workbook…	Beheer op welke wijze (delen van) een werkboek door verschillende gebruikers tegelijk kunnen worden gebruikt.
Track Changes	Dit is een submenu met de commando's Highlight Changes…, Accept or Reject Changes…. Je kan opgeven wijzigingen te markeren, en eventueel door te voeren. Dit is vooral nuttig wanneer meerdere gebruikers een zelfde werkboek bewerken. Het wordt dan ook met Share Workbook… gecombineerd.
Merge Workbooks…	Beheer het samenvoegen van meerdere werkboeken.
Protection…	Dit is een submenu met de commando's Protect Sheet…, Protect Workbook…, Protect and Share Workbook…. Je kan er de beveiliging op allerlei niveaus mee aanzetten en beheren.
Goal Seek…	Ga op zoek naar één te wijzigen cel om in een afhankelijke cel een vooropgegeven waarde te verkrijgen.
Scenarios…	Beheer scenario's, die je bijvoorbeeld met de Solver hebt opgesteld.
Auditing	Dit is een submenu met de commando's Trace precedents, Trace Dependents, Trace Errors, Remove All Arrows, Show Auditing Toolbar. Je kan er afhankelijkheden tussen cellen en fouten mee beheren. Je kan hierbij gebruik maken van de Auditing toolbar.
Solver…	Zoek waarden voor cellen om in een afhankelijke cel een vooropgegeven waarde te verkrijgen (of een minimale of maximale waarde).
Macro	Dit is een submenu met de commando's Macros…, Record New Macro…, Visual Basic Editor.
Add-Ins…	Beheer add-ins. Dit zijn extra mogelijkheden, die vaak niet automatisch worden geïnstalleerd of in de menu's opgenomen. Hierbij worden .xla bestanden gebruikt, die uiteraard op je schijf moeten zijn gekopieerd. Nadat een add-in is geïnstalleerd is er vaak een commando (meestal in het Tools menu) toegevoegd, zoals Solver… of Data Analysis…. Je kan de volgende add-ins beheren: Analysis Toolpak, Analysis Toolpak – VBA, AutoSave, Conditional Sum Wizard, File Conversion Wizard, Internet Assistant Wizard, Lookup Wizard, Microsoft AccessLinks Add-In, Microsoft Bookshelf Integration, MS Query Add-in for Excel 5 Compatibility, ODBC Add-In, Report Manager, Solver Add-In, Template Utilities, Template Wizard with Data Tracking, Update Add-in Links, Web Form Wizard.
Customize…	Beheer de menubalken en werkbalken. Dit commando is equivalent aan View, Toolbars, Customize….
Options…	Bepaal allerlei instellingen die algemeen gelden of voor een bepaald object (zoals een werkboek of een venster).
Wizard	Dit is een submenu met de commando's Lookup…, File Conversion…, Conditional Sum…, Web Form…. Je kan hiermee de aangeduide Wizards oproepen.