12. Oefeningen

Hierna volgen een aantal oefeningen. In de opgave wordt bijna nooit vermeld hoe de vormgeving moet gebeuren. Bij elk werkblad heb je daarin de vrije keuze. Ook kan je meestal naar eigen keuze charts maken. Wel leggen wij er de nadruk op dat zowel de vormgeving als het gebruik van charts als bedoeling hebben het werkblad te verduidelijken. Het verdient aanbeveling met charts te experimenteren en regelmatig de werkbladen te bewaren. Je kan uiteraard ook zelf varianten op de oefeningen bedenken of vraagstukken uit andere vakken met een spreadsheet oplossen. Na elke oefening of elk onderdeel staat het nummer van het hoofdstuk waarbij deze oefening hoort. Dit betekent dat de leerstof uit hoofdstukken tot en met het opgegeven hoofdstuk hierin kan worden toegepast. Bij sommige oefeningen komen nieuwe functies of verbanden aan bod, die dan bij de opgaven worden uitgelegd. Onderdelen van sommige oefeningen kunnen met macro's worden geautomatiseerd.

Bij sommige oefeningen is een figuur van de volledige of een deel van een oplossing gevoegd.

12.1 Oefening 1

Maak een werkblad waarin ontvangsten en uitgaven op je bankrekening worden bijgehouden. Voor elke transactie moet de datum worden vermeld, het bedrag (met teken: positief voor inkomsten, negatief voor uitgaven), het nummer van de andere rekening die betrokken is, en het nieuwe saldo. Voorzie ruimte voor de stand van de rekening waarmee wordt gestart. Breng een formaat aan dat steeds het teken voor de bedragen vermeldt. Maak hierbij gebruik van de mogelijkheid om een afzonderlijk formaat op te geven voor positieve en negatieve getallen. (3)

Aanvullingen:

1. Voeg onderaan nieuwe verrichtingen toe en zorg dat de vormgeving wordt aangepast. Wegens de chronologische ordening moeten nieuwe rijen achteraan worden toegevoegd. (3)
2. Voeg nieuwe verrichtingen toe met behulp van de database commando's. Om er voor te zorgen dat het niet mogelijk is bepaalde waarden te wijzigen met de database commando's kunnen deze waarden als formule worden aangebracht, ook als dit om andere redenen niet strikt noodzakelijk is. Een alternatieve mogelijkheid hiertoe maakt gebruik van de commando's om het werkblad en cellen te beveiligen. (7)
3. Bereken de hoogste waarden voor de inkomsten en uitgaven en de hoogste, laagste en gemiddelde waarde van de stand van de rekening. Plaats deze waarden niet onder maar rechts van de tabel, zodat met de database commando's op eenvoudige wijze nieuwe verrichtingen kunnen worden toegevoegd. Zorg er voor dat deze resultaten bij het toevoegen van nieuwe verrichtingen correct blijven. (4)
4. Maak een chart van de evolutie van de stand van de rekening in de tijd. Dit is een XY chart met één lijn. Je hoeft geen legende aan te brengen. Het verdient aanbeveling de plaats van de markeringen en labels van de X-as en het tonen van de rasters aan te passen. (5)
5. Een rekening bestaat uit 3+7+2 cijfers, gescheiden door mintekens. Geef de twaalf cijfers als getal op en breng de mintekens aan via het formaat 000-0000000-00. (4)
6. De laatste twee cijfers in het rekeningnummer worden gevormd door de eerste tien door 97 te delen en de rest te nemen. De functie MOD(getal,97) realiseert dit. Je hoeft dan enkel de eerste tien cijfers in te geven en kan de laatste twee berekenen en via de & operator toevoegen. Dit gebeurt met de formule: =getal&MOD(getal,97). Om een numeriek formaat te verkrijgen moet je de resulterende tekst omzetten naar een getal via de functie VALUE(text). De formule wordt dan =VALUE(getal&MOD(getal,97)). Deze formule blijkt echter enkel een correct resultaat op te leveren wanneer de laatste 2 cijfers niet met nullen beginnen.
   
   figuur 1a: Werkblad bij oefening 1

   Een andere formule, die hetzelfde niet steeds correcte resultaat oplevert, maakt gebruik van de functie TEXT(getal,formaat). Deze functie formatteert een getal en kan worden gebruikt om een getal in een tekst in een bepaald formaat te plaatsen. Het formaat hierin moet tussen " " staan. De formule wordt dan =TEXT(getal&MOD(getal,97),"000-0000000-00"). Om een formule te verkrijgen die altijd het juiste resultaat geeft moet je de twee delen eerst formatteren en daarna aan elkaar plakken met de formule =TEXT(getal,"000-0000000")&TEXT(MOD(getal,97),"-00"). Probeer deze zaken uit. (4)

12.2 Oefening 2

Zoek in de krant de koersen van tien aandelen op gedurende een maand. Houd deze bij in een werkblad. Breng de datums aan met het commando Data, Series... en kies voor enkel weekdagen. (4, 7)

Aanvullingen:

1. Bereken voor elk aandeel de gemiddelde koers, de hoogste en laagste koers, de stijging of daling tegenover de koers van de eerste bijgehouden datum.
2. Bereken voor elk aandeel en elke dag de procentuele stijging of daling tegenover de vorige koers van het aandeel. Bereken ook hier het gemiddelde, de grootste en kleinste stijging over de verschillende dagen en de totale procentuele stijging van de eerste tot de laatste dag.
3. Bereken voor elke dag de gemiddelde, grootste en kleinste procentuele stijging over alle aandelen. Bepaal ook welk aandeel de grootste en kleinste procentuele stijging oplevert. Hiervoor wordt gebruik gemaakt van de INDEX en MATCH functies.
4. Maak een chart van relevante zaken. (5)
   
   figuur 1b: Werkblad bij oefening 2

12.3 Oefening 3

De Bel-20 beursindex van de termijnmarkt wordt bepaald als de gewogen som van de koersen van een aantal representatieve aandelen. Op 18 januari 1999 werd de Bel-20 index bepaald aan de hand van de koersen van de 20 hierna vermelde aandelen met hun gewicht:

(Sindsdien werden CBR, Cobepa, Petrofina en Tractebel vervangen door Agfa-Gevaert, IBA, Interbrew en Telindus)

1. Stel een werkblad op met daarin voor een bepaalde datum en voor elk of sommige van de 20 aandelen: de naam, (slot)koers, het aantal aandelen, het gewicht (aantal * koers) en hun procentuele bijdrage tot de Bel-20 index. (4)
2. Stel een werkblad op met de volgende koersen van de sommige of alle van de bovenstaande 20 aandelen voor een bepaalde dag: omzet, openingskoers, hoogste, laagste en slotkoers en maak een Volume-Open-High-Low-Close chart op een bepaalde dag. (5)
3. Stel een werkblad op met voor vijf opeenvolgende weekdagen of voor de vier of vijf maandagen in een maand de slotkoersen en omzet van sommige aandelen en de evolutie van de Bel-20 index. Maak een passende bijbehorende chart in de tijd. (5)
4. Onderzoek grafisch in welke mate de koersen van de aandelen uit de vorige vraag representatief zijn voor de Bel-20 index. Dit gebeurt door een XY chart te maken van enerzijds de Bel-20 index (X) en anderzijds de aandeelkoersen (Y) op de overeenkomstige dag. Naarmate de bekomen punten meer op een stijgende rechte lijn liggen is de evolutie van de koers van het aandeel representatief voor de Bel-20 index. De gebruikte schaal beïnvloedt de interpretatie. In feite kan je enkel iets zinvol zeggen indien de koersen over een groot aantal (gelijkmatig verspreide) dagen worden in rekening gebracht. Lineaire regressietechnieken kunnen leiden tot een kwantitatieve interpretatie. Hiervoor gebruik je de lineaire regressietechnieken in de Analysis Tools. Zie ook de cursus Statistiek. Breng trendlijnen, de vergelijking van de regressielijn en de R² waarden aan. (5)
   
   figuur 2a: Werkblad bij oefening 3

12.4 Oefening 4

Een bedrijf maakt een product waarvoor het twee grondstoffen nodig heeft. Van grondstof A zijn drie eenheden nodig per product en van grondstof B vijf eenheden. Een eenheid A kost 150 BF en een eenheid B kost 95 BF. De verkoopsprijs van het product bedraagt 1300 BF. De hele productie wordt steeds verkocht.

Maak een werkblad met door jou te kiezen verkoophoeveelheden voor vier kwartalen van een jaar. In elk kwartaal is er een vaste kost van 30000 BF. Bepaal in het werkblad hoe groot de totale kost (vaste kost + variabele kost per eenheid * verkochte hoeveelheid; de variabele kost per eenheid is de totale grondstofkost voor één eenheid), omzet en winst zijn. Geef een jaaroverzicht en maak een zinvolle chart. Bereken (buiten de tabel) het Break Even Point op jaarbasis (winst = 0). (4, 5, 11)

figuur 2b: Werkblad bij oefening 4

12.5 Oefening 5

Een bedrijf koopt twee producten aan en verkoopt ze opnieuw. Product één kost bij leverancier A 500 BF en bij leverancier B 550 BF. Product twee kost bij leverancier A 800 BF en bij leverancier B 750 BF. Leverancier A kan maximaal 1000 exemplaren van elk product leveren per maand. Leverancier B kan onbeperkt leveren. Er wordt aangekocht zodat de vraag juist kan worden voldaan. Er worden geen voorraden aangelegd. De vraagcijfers van de producten bedragen voor zes maanden:

product één: 1800, 2000, 1500, 900, 1100, 1000

product twee: 1500, 1300, 1200, 1600, 1000, 800

Stel een werkblad op waarin de aangekochte hoeveelheden per product bij leveranciers A en B, de totaal te betalen bedragen aan A en B en het totale te betalen bedrag voor beide producten samen worden weergegeven voor elke maand afzonderlijk en als overzicht voor het ganse jaar. Verdeel de aankopen over de leveranciers zodat de te betalen bedragen zo klein mogelijk zijn. Maak een zinvolle chart. Bereken de verkoopprijs (geheel getal) om voor beide producten een gemiddelde halfjaarlijkse winst van minstens 10 % te maken. (4, 5)

figuur 3a: Werkblad bij oefening 5

12.6 Oefening 6

Een firma produceert drie producten A, B en C. Hierbij treden vaste kosten op van 100 000 F, 80 000 F en 70 000 F en variabele grondstofkosten en loonkosten. De loonkosten bedragen per eenheid van elk product 800, 1000 en 1200 F. De grondstofkosten bedragen 250 F per eenheid van grondstof 1 en 300 F per eenheid van grondstof 2. Voor 1 exemplaar van product A zijn drie grondstofeenheden 1 en acht grondstofeenheden 2 nodig. Voor 1 exemplaar van product B zijn zeven grondstofeenheden 1 en vier grondstofeenheden 2 nodig. Voor 1 exemplaar van product C zijn enkel 10 grondstofeenheden 1 nodig. Er moeten 1000 eenheden A, 750 eenheden B en 800 eenheden C worden geproduceerd

1. Bepaal de variabele productiekost voor één eenheid A, B en C
2. Bepaal de totale productiekost van A, B en C afzonderlijk en van de drie producten samen.
3. Bepaal de verkoopprijs (geheel getal) voor A, B en C opdat er op elk product een winst van minstens 15% zou worden gemaakt.
4. Bepaal de behoeften aan grondstoffen 1 en 2 en de totale loonkost.
5. Onderzoek de invloed van een stijging van de kost van grondstof 1 met 4%.
6. Hoeveel eenheden A, B en C moeten wij verkopen om voor elk product het Break Even Point te bereiken (aan de verkoopprijzen uit de 3de vraag).
7. Stel dat de productie voor producten A en B met 10 % kan stijgt en dat je deze extra productie kan verkopen. Wat is de invloed op de winst?

Gebruik een werkblad om de gegevens te verwerken en de antwoorden op deze vragen te vinden. (4, 5)

figuur 3b: Werkblad bij oefening 6

12.7 Oefening 7

Stel een werkblad op waarin het voetbalklassement uit de eerste afdeling wordt voorgesteld. Hierin komen voor: Nr. in de rangschikking, naam van de ploeg, aantal gespeelde wedstrijden (= aantal gewonnen + verloren + gelijk gespeelde wedstrijden), aantal gewonnen, gelijk gespeelde en verloren wedstrijden, aantal doelpunten vóór en tegen (deze vijf getallen zijn inputgegevens), aantal punten. Het aantal punten wordt berekend door het aantal gewonnen wedstrijden met drie te vermenigvuldigen en daar het aantal gelijk gespeelde wedstrijden bij op te tellen. De rangschikking wordt opgemaakt volgens behaalde punten en vervolgens het aantal gewonnen wedstrijden en het doelsaldo (doelpunten vóór min doelpunten tegen). (4,5)

Varianten:

1. Bereken het aantal punten door het aantal gewonnen wedstrijden met twee in plaats van met drie te vermenigvuldigen (vroegere puntentelling).
2. Bereken de rangschikking op basis van het aantal punten en vervolgens het aantal gescoorde doelpunten en het doelsaldo (dit systeem wordt in Engeland toegepast).
3. Maak een High-Low Line chart van het aantal doelpunten vóór en tegen. Combineer deze daarna met een chart met de behaalde punten.

12.8 Oefening 8

Iemand leent een bedrag van 2 000 000 BF en zal de helft terugbetalen in 10 jaarlijkse gelijke termijnen aan een werkelijke rentevoet van 10 %. De rest betaalt hij terug in 5 daaropvolgende jaarlijkse gelijke termijnen van 10.5 %. Op deze rest moet uiteraard 10% rente betaald worden gedurende de eerste 10 jaar. Stel het aflossingsplan op en bereken de werkelijke rentevoet. (8)

Varianten:

1. Zelfde vraag wanneer de eerste helft terugbetaald wordt in 20 semestriële gelijke termijnen aan een werkelijke rentevoet i=10 %.
2. Zelfde vraag wanneer de eerste helft terugbetaald wordt in 20 semestriële gelijke termijnen aan een schijnbare jaarlijks j(2)=10 %.
   
   figuur 4a: Werkblad bij oefening 8

12.9 Oefening 9

Iemand spaart door in het begin van elke maand 10 000 BF te storten, waarbij de jaarlijkse rentevoet 7.5 % bedraagt. Na hoeveel maanden zal hij minstens 500 000 BF hebben gespaard? Hoeveel maanden later zal dit bedrag (minstens) zijn verdubbeld? Hoeveel maanden zal hij minder lang moeten sparen om 500 000 BF te hebben wanneer de jaarlijkse rentevoet na één jaar verhoogd wordt tot 9 %? (8)

figuur 4b: Werkblad bij oefening 9

12.10 Oefening 10

Een bedrijf koopt een aantal producten aan op afbetaling. Bij de aankoop wordt steeds 15% van de aankoopsom onmiddellijk betaald, terwijl de rest wordt betaald via constante termijnen te betalen na 1, 2, 3... jaar, die berekend worden op het niet onmiddellijk bij de aankoop betaalde deel van het aankoopbedrag. Het aantal termijnen en de jaarlijkse rentevoet kunnen verschillen van product tot product. Hiervan worden (minstens) de volgende zaken geregistreerd: productomschrijving, aankoopbedrag, aankoopdatum, bedrag betaald bij aankoop, rentevoet (percentage), aantal termijnen, bedrag van één termijn, totaal betaald bedrag voor dit product. Tevens wordt voor de gegevens waar dit zinvol is een totaal over alle producten berekend.

Stel deze zaken voor in een werkblad en maak een zinvolle chart (8)

12.11 Oefening 11

Een firma verkoopt een aantal producten op afbetaling. Wanneer de verkoopprijs minder dan 100 000 BEF bedraagt, wordt bij aankoop 20% betaald en wordt de rest van de som in drie gelijke schijven afbetaald via een annuïteit met constante termijn aan een rentevoet van 8%. Wanneer de verkoopprijs 100 000 BEF of meer bedraagt, wordt bij aankoop 15 % betaald en wordt de rest van de som in 10 gelijke schijven afbetaald via een annuïteit met constante termijn aan een rentevoet van 9 %. Stel een werkblad op waarin zich deze gegevens bevinden voor een aantal producten (bijvoorbeeld 7). Hierin moeten voor elk product de omschrijving, de prijs, het direct betaalde bedrag, het overschot, de rentevoet, het aantal termijnen en het bedrag van een termijn vermeld staan en de som van alle betaalde bedragen. Bereken ook zinvolle totalen. Maak een zinvolle chart. (8)

12.2 Oefening 12

Een stadsbestuur schrijft een a pari terugbetaalbare obligatielening uit van 120 miljoen frank tegen een werkelijke rentevoet van 7.5%. De dienst van de lening wordt verzekerd door een constante annuïteit met vijf jaarlijkse vervaldagen. De nominale waarde van een obligatie is 10 000 frank. Stel het aflossingsplan op.

Dit aflossingsplan gelijkt zeer sterk op dat voor een annuïteitslening met constante termijn. Ook hier wordt eerst de (theoretische) termijn van de annuïteit berekend. Deze wordt dan opgesplitst in een rente- en aflossingsbestanddeel. Het aflossingsbestanddeel bestaat uit een geheel aantal obligaties ter grootte van de nominale waarde. Dit heeft als gevolg dat de werkelijke termijnen van de annuïteit jaarlijks nagenoeg constant zijn. De financiële functies worden gebruikt om de aantallen obligaties te bepalen. In het aflossingsplan worden kolommen opgenomen voor het jaarnummer, het aantal obligaties in omloop in het begin van het jaar, het aantal terugbetaalbare obligaties aan het slot van het jaar, het totaal aantal obligaties terugbetaald tot en met het slot van het jaar, en de totale (bijna constante) termijn, die wordt opgesplitst in een rente- en aflossingsdeel. (8)

figuur 4c: Werkblad bij oefening 12

12.13 Oefening 13

Stel een werkblad op dat de evolutie van voorraden bijhoudt zoals in hoofdstuk 10. Produceer echter niet zelf maar kopen de producten aan. Hierbij is er een variabele inkoopprijs per eenheid en een vaste kost per bestelling. De gemiddelde voorraad in een periode wordt nu gegeven door de som van beginvoorraad, eindvoorraad en de grootte van één bestelling te delen door twee. (11)

Onderzoek de volgende gevallen:

1. Er is één bestelling per periode.
2. Een vaste bestelgrootte van bijvoorbeeld 200 eenheden per bestelling. De totaal te bestellen hoeveelheid in een periode wordt verkregen door de verwachte vraag te verminderen met de beginvoorraad en deze hoeveelheid op te trekken tot het eerstvolgende veelvoud van de bestelgrootte (200). Het aantal bestellingen is gelijk aan de totaal te bestellen hoeveelheid gedeeld door de bestelgrootte (200). De totale bestelkost per periode is het aantal bestellingen vermenigvuldigd met de vaste kost per bestelling. Reken deze aan als een periodekost.
3. Er bestaat een theoretische formule voor de optimale bestelgrootte (EOQ: Economic Order Quantity). Deze wordt berekend door de totale kost per periode te minimaliseren, wanneer er geen begin- en eindvoorraad is en de vraag precies wordt voldaan. Dan moet de volgende kostenfunctie worden geminimaliseerd:

   K = c_b * Q_d / Q + c_i * Q_d + c_s * Q / 2

   waarbij c_b: vaste kost per bestelling

   c_i: variabele inkoopprijs per aangekochte eenheid

   c_s: bewaarkost per eenheid en per periode

   Q_d: vraag, theoretisch gelijk een de aangekochte hoeveelheid voor de hele periode

   Q: bestelgrootte van één bestelling

   n = Q_d / Q: aantal bestellingen in één periode

   Q / 2: gemiddelde voorraad in één periode wanneer er geen begin- of eindvoorraad is.

   Afleiden naar Q, de eerste afgeleide gelijk stellen aan 0 en de oplossing kiezen waarbij de tweede afgeleide positief is (minimum) geeft:

   EOQ = Q^opt = sqrt(2 * c_b * Q_d / c_s)

   In de praktijk zullen de niet-gehele resultaten uit deze formule moeten worden afgerond, en treden afwijkingen af, onder meer als gevolg daarvan maar ook als gevolg van de gedeeltelijke onvoorspelbaarheid van de vraag.

   Onderzoek de correctheid van deze formule wanneer geen afrondingen gebeuren.

4. Breng de beginvoorraad in rekening door in de formule voor Q^opt de vraag Qd te vervangen door Q_dv-Q_b+dQ (verwachte vraag min beginvoorraad plus toeslag). Bereken het aantal bestellingen uit de formule

   n = round(((Q_dv - Q_b + dQ ) / Q^opt),0)

   (afgerond tot een geheel getal) en de bestelgrootte uit de formule

   Q = round((Q_dv - Q_b + dQ) / n, -2 )

   (afgerond naar het dichtstbijzijnde veelvoud van 100).

   Onderzoek in hoeverre deze formule een optimale winst benadert.

5. Vergelijk de resultaten van de verschillende alternatieven!
   
   figuur 5a: Werkblad bij oefening 13

12.14 Oefening 14

In het aanslagjaar 1995 wordt de basisbelasting als volgt berekend:

Stel een werkblad op waarin je voor een aantal belastbare inkomens de totale basisbelasting en het gemiddelde (basis)belastingspercentage uitrekent. Maak een chart, die de curve van de basisbelasting (gebroken lijn) aangeeft. Gebruik hierbij de VLOOKUP functie. Het resultaat correspondeert met de hierin voorkomende waarde die minstens gelijk is aan de lookup_value. (4)

figuur 5b: Werkblad bij oefening 14