11. Een balans opstellen

In dit hoofdstuk zal je een balans opstellen. Hierin zal je voor een firma de Activa en Passiva voor 5 jaren aangeven.

De volgende zaken worden behandeld:

• De balans opstellen.
• Outlines aanbrengen en ermee werken.
• Een verticale analyse maken.
• Een horizontale analyse maken.
• Balansen consolideren.

Er wordt gebruik gemaakt van de werkboeken Balans.xls, Peugeot.xls en Ford.xls. Deze laatste twee bevatten resultaten van de jaarrekeningen van de firma's Peugeot en Ford in de jaren 1989-1993.

Je kan de werkboeken opvragen in P:\Excel 97 en bewaren op je I: schijf.

Het opstellen van de balans verloopt in de volgende stappen:

1. Breng een hoofd aan (firmanaam, titel), gevolgd door een lege rij.
2. Breng de jaartallen aan als kolomhoofd.
3. Breng de items van de balans aan, waarbij elk niveau inspringt.
4. Vul de invoergegevens in.
5. Bereken de totalen met de SUM functie, de SUBTOTAL functie of als een gewone som.

De tekstgegevens voor de balans van de firma Peugeot zijn (en):

Opmerkingen:

• Het is aangewezen nu reeds enige aandacht aan de vormgeving te besteden. Laat de niveaus inspringen met de Increase Indent knop. Verbreed kolom A tot bijvoorbeeld 45 tekens zodat de meeste teksten op één regel passen. Stel voor deze kolommen de Wrap text optie in. Je kan eventueel zelf controleren wanneer een tweede regel moet worden genomen binnen een cel met alt+enter.

• Zorg ervoor dat de tabel der ACTIVA door lege rijen en kolommen is omgeven om indien nodig de commando's uit het Data menu te kunnen toepassen.

• Voor de PASSIVA moet je analoge bewerkingen uitvoeren. Deze worden hier niet vermeld.
• Bij de PASSIVA zal V. Overgedragen Winst en Overgedragen Verlies (-) over twee cellen worden gespreid (A62 en A63). Het Romeinse cijfer V. wordt enkel bij Overgedragen Winst vermeld. Om het begin van beide teksten op elkaar af te stemmen, kan je ook Overgedragen Verlies (-) door V. laten voorafgaan, en later dit Romeins getal de (onzichtbare) witte Font Color geven.

Bij het invoeren van de getalgegevens moet je er goed op letten een onderscheid te maken tussen cellen met getallen en cellen met formules. De cellen van het laagste niveau zullen getallen bevatten. Bevindt zich onder een cel een andere cel van een lager niveau (de titel springt verder in), dan zal hij formules moeten bevatten.

Je verkrijgt uiteindelijk het werkblad Balans 1, waarin nog geen formules werden berekend (figuur 1).

Opmerking:

• Om de nu reeds ingevulde cellen met getallen en de later in te vullen cellen met formules te onderscheiden kan je de kleur van de cellen met getallen in donkerblauw veranderen. Dit doe je het eenvoudigst op de volgende wijze:

Selecteer hierbij vooraf de cellen die getallen of formules zullen bevatten (B5:F46). De Constants optie in het Go To Special dialoogvenster beperkt de geselecteerde cellen tot cellen die geen formules bevatten en niet leeg zijn, dus enkel tot de cellen met constante getallen.

Je zou nu zeer zorgvuldig voor elke rij in de tabel die geen getallen bevat de som kunnen berekenen met de SUM functie of door gewoon + tekens te gebruiken.

In sommige SUM formules van het laagste niveau moet een range cellen worden opgeteld, zoals in:

In andere SUM formules van een hoger niveau moeten uit elkaar staande cellen worden opgeteld, zoals in:

Hierdoor kunnen gemakkelijk fouten ontstaan. Je kan deze fouten vermijden door de SUM functie te vervangen door de SUBTOTAL functie, die ook bij het commando Data, Subtotals... wordt gebruikt.

Deze functie SUBTOTAL(type,range) bevindt zich in de Math & Trig Category in het Paste Function dialoogvenster en heeft twee argumenten.

• Het eerste argument geeft het type subtotaal aan. Het kan de volgende waarden hebben:

Breng de SUBTOTAL formule aan voor elke cel uit het eerste jaar (1989) en kopieer de bekomen formule naar rechts met de fill handle. Het berekenen gebeurt het eenvoudigst eerst voor de laagste niveaus, vervolgens voor de cellen van één niveau hoger enz.:

Je verkrijgt nu het werkblad met de totalen (figuur 2).

Opmerkingen:

• Zelfs bij het gebruik van de SUBTOTAL functie vergt het nog bijzonder veel oplettendheid om de juiste cellen op te tellen. Je kan het inspringen in kolom A kan hierbij als leidraad nemen.
• In bepaalde gevallen kan je met de AutoSum knop dezelfde resultaten verkrijgen als met de SUBTOTAL functie. Indien de som moet worden gemaakt van getallen in cellen onder én rechts van de getallentabel, selecteer je de getallentabel met de rij eronder en de kolom rechts ervan. Met de AutoSum knop worden de totalen automatisch in de lege rij en kolom geplaatst. Indien in de tabel reeds cellen met de SUM functie staan worden deze subtotalen in plaats van de basisgegevens opgeteld. Bij de SUBTOTAL functie is de werkwijze juist omgekeerd (steeds worden de basisgegevens opgeteld en nooit de subtotalen), maar het resultaat is hetzelfde. Wanneer de totalen echter niet rechts én onder de tabel moeten staan kan je de AutoSum knop niet gebruiken.

Je kan nu op een erg eenvoudige wijze een outline aanbrengen.

Dit gebeurt met het commando

Rechts van het werkblad (figuur 3) bevinden zich nu in vier kolommen knoppen met – tekens met daaronder verticale lijnen. Bovenaan bevinden zich de outline niveau knoppen. Deze bevatten de cijfers 1 tot 4.

Elke rij bevat een punt op het niveau van zijn groep. Dit kan samenvallen met de – of + knop. Niveau 1 bevat de hoofdrubrieken (VASTE ACTIVA en VLOTTENDE ACTIVA). Niveau 2 bevat de 10 rubrieken aangeduid met Romeinse cijfers. Niveau 3 bevat de subrubrieken aangeduid met hoofdletters. Niveau 4 bevat de detailrubrieken aangeduid met cijfers.

Wanneer je met outlines werkt is de PivotTable toolbar erg nuttig. Je kan deze tonen met het commando

Je kan nu de outline knoppen of knoppen in de PivotTable toolbar klikken en de volgende zaken uitvoeren:

• Klik op een knop met een – teken of selecteer de corresponderende rij en klik de Hide Detail knop in de PivotTable Toolbar of kies het commando

• Klik op een knop met een + teken of selecteer de corresponderende rij en klik de Show Detail knop in de PivotTable Toolbar of kies het commando

• Klik op een knop met een cijfer om alle rijen van dit niveau te tonen en alle rijen van lager niveaus te verbergen. Door de knop van het hoogste niveau (1) te klikken beperk je de getoonde informatie maximaal. Door de knop van het laagste niveau (4) te klikken toon je alle niveaus en wordt geen enkele rij nog verborgen.

Opmerkingen:

• De outline structuur werd automatisch gemaakt doordat Excel de formuleverbanden bestudeerde. Het is mogelijk dat Excel hieruit geen conclusies kan trekken. Het is ook mogelijk dat een verkeerde outline structuur werd gevormd.
• De betekenis en het gebruik van de + en – knoppen is dezelfde als bij de soortgelijke tekens in de All Folders lijst van Explorer bij Windows 95.
• Het is mogelijk outline niveaus te hebben voor rijen en kolommen. Omdat hier echter geen bewerkingen met cellen van verschillende kolommen zijn gebeurd zijn er geen outline niveaus voor de kolommen. Wanneer je een kolom met gemiddelden toevoegt kan je wel outline niveaus voor kolommen verkrijgen.
• Je kan slechts één outline per werkblad maken.

• Het commando Data, Subtotals... brengt ook een outline structuur aan.

• Je kan de outline structuur verwijderen met het commando

• Je kan de outline structuur wijzigen door rijen te selecteren en andere commando's van het Data, Group and Outline submenu of knoppen uit de PivotTable Toolbar te gebruiken.

• Wanneer je één of meer rijen (of kolommen) selecteert kan je deze op een lager niveau (hoger nummer) brengen met de Group knop of met het commando

• Wanneer je één of meer rijen (of kolommen) selecteert kan je deze op een hoger niveau (lager nummer) brengen met de Ungroup knop of met het commando

• Indien geen rijen of kolommen maar ranges geselecteerd zijn bij het uitvoeren van de Group of Ungroup actie verschijnt een Group of Ungroup dialoogvenster waarin je kan aangeven of je rijen of kolommen wilt groeperen.

• Je kan bepaalde instellingen van outlines wijzigen met het commando

Data, Group and Outline, Settings...

• Het is niet mogelijk een automatische outline aan te brengen wanneer de formules nog niet zijn aangebracht. Deze automatische outline is namelijk gebaseerd op de aard van de formules.
• Je kan wel manueel een outline aanbrengen met de Group en Ungroup knoppen vóór je de formules aanbrengt. Daarna kan je trachten met het commando Data, Subtotals... de formules op een automatische wijze aan te brengen. Bij complexe outlines zoals hier zullen echter vele resultaten niet op de correcte plaats staan. Vaak zijn er ook te veel subtotalen.
• Je kan zelfs het commando Data, Subtotals... toepassen zonder vooraf outlines aan te brengen. In dit geval kan zelfs de aard van de subtotalen verkeerd zijn.

Je kan de verdere vormgeving op drie wijzen aanpassen:

• Selecteer een cel in de outline, breid de selectie uit tot de hele region met het commando

• Selecteer een cel in de outline, breid de selectie uit tot de hele region met het commando

Format, AutoFormat..., Classic 2 of Accounting 3

• Breng manueel formaten aan. Dit verloopt in een aantal stappen:

Nu verkrijg je een afgewerkt werkblad (figuur 5).

Opmerkingen:

• Bij complexe outlines vertoont de automatische formattering met de commando's Data, Grouping and Outline, Settings... en Format, AutoFormat... onvolkomenheden.
• Het aanbrengen van formaten per niveau is niet strikt noodzakelijk maar bevordert de overzichtelijkheid.
• De Format Painter knop wordt gebruikt om op een snelle wijze formaten te kopiëren:

1. Selecteer en formatteer de cellen uit een rij.
2. Dubbelklik de Format Painter knop, terwijl de geformatteerde cellen geselecteerd zijn. De Format Painter knop wordt nu ingedrukt getoond.
3. Klik of sleep achtereenvolgens over de ranges waarheen het formaat moet worden gekopieerd. Zolang de Format Painter knop ingedrukt getoond wordt, wordt na het loslaten van de muis het formaat geplakt.
4. Klik nogmaals de Format Painter knop om het plakken van formaten te beëindigen.

• Wanneer je de Format Painter knop slechts eenmaal klikt in plaats van hem te dubbelklikken, wordt het formaat enkel bij de eerstvolgende sleepbewerking gekopieerd en daarna niet meer.
• Bij het kopiëren van formules of formaten met de Format Painter knop wordt ook over geselecteerde verborgen cellen gekopieerd! Wanneer je dus de rijen waarheen je formules of formaten niet wilt kopiëren verbergt, moet je de erboven en eronder staande cellen afzonderlijk selecteren om te vermijden dat de verborgen rijen worden gewijzigd. Dit is de reden waarom de lijnen rond de rijen van niveau 1 afzonderlijk werden aangebracht. Het gebruik van de Format Painter had hier niet enkel de lijnen maar ook de numerieke formaten, lettertypes en lettergroottes gekopieerd. Eventueel kan je wel aangeven enkel de zichtbare cellen te selecteren met het commando

• Het aanbrengen van lijnen gebeurt vaak best achteraf. De halfdikke rand om het geheel brengen je best als allerlaatste aan.
• Het is niet aangewezen de getallen in verschillende lettertypes of lettergroottes aan te brengen. Dit maakt het overzicht minder leesbaar. je kan eventueel wel een onderscheid in de getallen aanbrengen met kleuren, maar ook hier moet overdaad vermeden worden.

Stel vervolgens een verticale en horizontale analyse van de activa op. Hetzelfde kan voor de passiva gebeuren.

• Een verticale analyse geeft voor elk jaar (elke kolom) de procentuele waarde t.o.v. het totaal. Dit houdt in dat alle cellen in de onderste rij de waarde 100% krijgen en de andere cellen een percentage in verhouding tot hun aandeel t.o.v. het totaal. Op deze wijze kan je de belangrijkste rubrieken afzonderen.
• Een horizontale analyse geeft voor elke rubriek (elke rij) aan hoe de waarde evolueert t.o.v. een basisjaar (basiskolom). Er worden dus indices aangebracht. Dit houdt in dat alle cellen van de eerste kolom (1989) de waarde 100% krijgen en de andere cellen een percentage in verhouding tot hun procentueel aandeel t.o.v. deze op 100 genormeerde waarde. Op deze wijze kan je groeiende (>100%) en afnemende rubrieken (<100%) aangeven.

Het opstellen van het werkblad Verticale analyse verloopt zeer eenvoudig:

Hiermee is het werkblad Verticale analyse (figuur 6) volledig afgewerkt!

Opmerkingen:

• Dit werkblad vormt een voorbeeld van de eerder zeldzame gevallen waarbij je gemengde referenties moet gebruiken. De reden hiervoor is dat de formule zowel naar rechts als naar onder moet worden gekopieerd. Wilde je ze slechts in één richting kopiëren, dan had je in de noemer een absolute of relatieve referentie kunnen gebruiken.
• Het is uiteraard mogelijk de verticale analyse in hetzelfde werkblad te plaatsen als de gegevens waarop ze gebaseerd is. De hier gebruikte methode is echter overzichtelijker.

• Het is daarnaast eveneens mogelijk de verticale analyse in een afzonderlijk werkboek te plaatsen. In dit geval moet in de formules de bestandsnaam van het werkboek gevolgd door een ! teken aan de eigenlijke referentie voorafgaan.

Het opstellen van het werkblad Horizontale analyse verloopt zeer eenvoudig:

Hiermee is het werkblad Horizontale analyse (figuur 7) volledig afgewerkt!

Het is uiteraard mogelijk de som of het gemiddelde van de gegevens van de verschillende jaren in de balans te maken. Je moet daarvoor rechts kolommen aanbrengen met de gepaste functies.

Het commando

Data, Group and Outline, Auto Outline

zal dan ook outline niveaus in de kolommen maken.

Het is echter ook mogelijk balansen voor dezelfde jaren van verschillende ondernemingen in verschillende werkboeken of werkbladen te maken en een samenvatting daarvan te maken.

Telkens zal de lay-out van de verschillende werkbladen gelijk moeten zijn. Dit betekent dat dezelfde gegevens zich in corresponderende cellen bevinden.

Consolideer als voorbeeld de balansen van twee firma's, Peugeot en Ford.

• Eerst wordt het geval behandeld waarbij de verschillende balansen (Balans 1, Balans 2) in twee opeenvolgende werkbladen van hetzelfde werkblad staan. In dit geval bevinden de sheet tabs van de twee werkbladen zich naast elkaar.
• Vervolgens wordt het geval behandeld waarbij de verschillende balansen in gelijknamige werkbladen van verschillende werkboeken staan.

Het aanbrengen van een tweede balans is zeer eenvoudig:

1. Maak een kopie van de reeds bestaande balans Balans 1 en noem die Balans 2.
2. Selecteer de numerieke gegevens, maar niet de formules. Dit gebeurt het gemakkelijkste door de range met getallen te selecteren (B5:F46 in het werkblad Balans 2), en vervolgens de selectie te beperken met het commando

Edit, Go To..., Special..., Constants

3. Maak de inhoud van de geselecteerde cellen leeg, maar niet de formaten, met het commando

Edit, Clear, Contents

4. Vul nu de naam van de tweede firma en de numerieke gegevens in. Wanneer je de betreffende cellen vooraf selecteert kan dit vrij snel gebeuren.

Opmerkingen:

• Omdat deze selectie uit meerdere ranges bestaat worden de ranges één na één ingevuld. Dit heeft als gevolg dat je de gegevens het beste per rij invult en dus telkens de tab toets in plaats van de enter toets gebruikt om naar de volgende cel te gaan.
• Het is nog eenvoudiger door (bij voorkeur in een template – zie infra) de cellen van het werkblad Balans 1 te beveiligen. Standaard zijn alle cellen beveiligd, maar is deze beveiliging slechts effectief wanneer ook het werkblad beveiligd is. Ga als volgt te werk om er voor te zorgen dat je enkel de gewenste cellen kan wijzigen:

• Wanneer het werkblad beveiligd is, is het enkel mogelijk cellen te wijzigen waarvan Locked niet aangekruist is.
• Wanneer het werkblad beveiligd is, kan je met de tab toets van de ene niet-beveiligde cel naar de volgende springen, zodat je probleemloos de getalwaarden kan intypen en met tab naar de volgende cel kan gaan.
• Wanneer je cellen willen formatteren (beveiligde of niet-beveiligde), blijkt dat het commando Format, Cells... (naast vele andere) niet geactiveerd kan worden, tot je de beveiliging van het werkblad hebt uitgezet. Dit laatste gebeurt met het commando

Tools, Protection, Unprotect Sheet...

• Een groot nadeel van het aanzetten van de beveiliging van een werkblad is dat het tonen en verbergen van rijen met de outline knoppen eveneens niet meer gaat! Daarom is het aangewezen de beveiliging van het werkblad enkel aan te zetten tijdens het invoeren van gegevens!
• Je kan eventueel een template voor het balans werkboek maken. Deze bestaat uit het werkboek met 'lege' balans werkbladen (Balans 1, Verticale analyse, Horizontale analyse). Deze 'lege' werkbladen bevatten tekstgegevens, formules en formaten maar geen numerieke gegevens. Ook de naam van de firma in Balans 1 is geschrapt. Bewaar daarna dit model werkboek met het commando

Ga uit van de volgende situatie:

• In het werkboek Balans bevinden zich de werkbladen Balans1 en Balans2. Deze bevatten de balansen van de ACTIVA en de PASSIVA van twee firma's. De cellen met te consolideren getallen zijn B5:F46 (Activa) en B49:F100 (Passiva) in beide werkbladen.

• In hetzelfde werkboek is reeds een werkblad Geconsolideerde balans gemaakt. Dit bevat dezelfde lay-out als de werkbladen Balans 1 en Balans 2.

• De som van de getallen in de werkbladen Balans 1 en Balans 2 zal worden gemaakt in de cellen B5:F46 (Activa) en B49:F100 (Passiva) van het werkblad Geconsolideerde balans. Deze cellen mogen vóór het consolideren leeg zijn of getallen en/of formules bevatten.

Voer de consolidatie uit door de range 'Geconsolideerde balans'!B5:F46, waarin de geconsolideerde activa moeten worden geplaatst, te selecteren en het commando

Data, Consolidate...

te kiezen. Nu verschijnt het Consolidate dialoogvenster (figuur 8). Hierin kan je de volgende zaken invullen:

• Kies de uit te voeren bewerking (Function - standaard: Sum).

• Geef aan welke range geconsolideerd moet worden (Reference). Dit kan door een sheet tab te klikken en daarin een range te selecteren. Kies één range tegelijk. Voeg deze met de Add knop toe aan de All references lijst. Kies met de Browse... knop een range uit een niet-geopend bestand.

• Kies een te consolideren range uit de All references lijst en wijzig deze (Add) of verwijder ze (Delete).

• Duid aan dat de eerste rij of kolom (Use labels in Top row / Left column) van een range de veldnamen bevat van de te consolideren gegevens (vooral wanneer de te consolideren ranges verschillende afmetingen hebben).

• Leg een verband tussen de te consolideren gegevens en het resultaat (Create links to source data), zodat het resultaat zal worden bijgewerkt wanneer de gegevens worden gewijzigd.

Maak de volgende keuzen:

Vervang de naam van de firma door totaal:

Je verkrijgt het werkblad Geconsolideerde balans (figuur 9).

Opmerkingen:

• Ten gevolge van het aankruisen van Create link to source data duurt het consolideren vrij lang. Er worden namelijk voor elke rij in de range met resultaten twee rijen toegevoegd met daarin referenties naar de gegevens uit de te consolideren werkbladen (='Balans 1'!cel en ='Balans 2'!cel). In de oorspronkelijke cel, die erboven staat, wordt de som van deze twee getallen gemaakt (=SUM(cel1:cel2)). De toegevoegde rijen vormen een vijfde outline niveau. Wijzigingen in de oorspronkelijke gegevens worden hierdoor automatisch bijgewerkt.
• Indien je Create link to source data niet aankruist, wordt een kopie gemaakt van de inhoud (formules) van de cellen uit de te consolideren werkbladen, waarbij waarden van getalconstanten worden opgeteld.

• Op een analoge manier kan je de Passiva consolideren. Het is niet mogelijk selecties bestaande uit meer dan één range in één keer te consolideren, zodat je Activa en Passiva afzonderlijk moet consolideren. Bij het consolideren van de Passiva moet je de ranges van de Activa met de Delete knop uit de All references lijst in het Consolidate dialoogvenster verwijderen.

Wanneer je de ranges sheet-level namen geeft (B5:F46: Activa, B49:F100: Passiva) worden de formules veel eenvoudiger.

In bepaalde gevallen is het te verkiezen de balansen van verschillende firma's niet in één werkboek onder te brengen, maar per firma een werkboek te gebruiken. Dit wordt hierna geïllustreerd.

Ga uit van de volgende situatie:

• In de werkboeken Peugeot.xls en Ford.xls bevindt zich het werkblad Balans 1. Dit bevat de balans van de ACTIVA en de PASSIVA. De cellen met te consolideren getallen zijn B5:F46 (Activa) en B49:F100 (Passiva) in beide werkbladen.

• In het werkboek Consolidatie.xls is reeds een werkblad Balans 1 gemaakt. Dit bevat dezelfde lay-out als in de werkboeken Peugeot.xls en Ford.xls. De som van de getallen in de werkbladen balans1 van de werkboeken Peugoet.xls en Ford.xls zal gemaakt worden in de cellen B5:F46 (Activa) en B49:F100 (Passiva). Deze cellen mogen vóór het consolideren leeg zijn, getallen en/of formules bevatten.

• Er worden geen sommen maar gemiddelden berekend.

Voer de consolidatie uit door de range 'Balans 1'!B5:F46 te selecteren in Consolidatie.xls, waarin de geconsolideerde activa moeten worden geplaatst, en het commando

te kiezen. In het Consolidate dialoogvenster (figuur 10) maak je de volgende keuzen:

Je verkrijgt het geconsolideerde werkblad Balans 1 (figuur 11).

Opmerkingen:

• Wanneer de gegevens niet uit gelijknamige werkbladen in andere werkboeken komen, moeten zowel de bestandsnaam van het werkboek als de naam van het werkblad vóór de referentie naar de cellen worden vermeld. Een dergelijke referentie heeft de vorm:

='[WORKBOOK.XLS]worksheet '!range

• Je kan de bestanden Peugeot.xls en Ford.xls opzoeken met de Browse... knop.
• Kruis Create link to source data niet aan, omdat het consolideren van gegevens uit verschillende bestanden erg veel tijd vraagt. Er worden nu geen rijen toegevoegd.
• Juist zoals bij de verticale en horizontale analyse is het mogelijk alle balansen in hetzelfde werkblad van hetzelfde werkboek onder te brengen. Dit is echter af te raden omdat het de overzichtelijkheid schaadt.