Traceerbaarheid van grondstoffen met Ms Excel

.

Traceerbaarheid van grondstoffen bij voorraadbeheer en kostprijsberekening met behulp van Ms Excel zelfs toepasbaar tot in Uw recepten.

Op deze webpagina geef ik U uitleg hoe je dat zou kunnen maken. Als uitgangspunt voor deze pagina heb ik een aantal door mij ontwikkelde rekenmodellen gebruikt. i.v.m de complexiteit van deze materie heb ik deze pagina niet even in een namiddagje klaar.  start op 27-11-2017

uitgangspunt is deze lijst met grondstoffen/ingrediënten:

Op deze lijst komen alle te gebruiken grondstoffen te staan. Inclusief de inkoopprijs en de leverancier. Even er van uitgaan dat ook een kostprijsberekening belangrijk is wordt ook de leverancier op deze lijst geplaatst. Zodra een grondstof in prijs veranderd of U veranderd van leverancier krijgt de grondstof een ander artikelnummer. Even een prijs aanpassen gaat niet lukken omdat een nieuwe prijs direct invloed heeft op alle recepten die U reeds heeft gemaakt

klik op de afbeelding voor een vergrote weergave

hoofdbestand:

Zodra een grondstof binnenkomt plaatst U die in het hoofdbestand. De boekingsdatum geeft in de 2e kolom direct het weeknummer en zodra U het grondstofnummer heeft ingevuld wordt dit nummer gekoppeld aan de week van binnenkomst en ziet U in kolom D het batchnummer. Het grondstofnummer in kolom E zorgt ook voor het invullen van de kolommen F en G. Handmatig vult U de uiterste houdbaarheidsdatum in. Bovenin kolom I ziet U de huidige datum. In kolom H ziet U het aantal dagen dat de houdbaarheidsdatum nog heeft te gaan. In dit voorbeeld is hij 212 dagen verlopen

klik op de afbeelding voor een vergrote weergave

Grondstoffen lijst kolom nummers:

Omdat er veel gewerkt wordt met formules die naar kolomadressen verwijzen heb ik hieronder de kolommen genummerd. Op het volgende plaatje wordt dat wat duidelijker

klik op de afbeelding voor een vergrote weergave

hoofdbestand formule uitleg:

KOLOM C: kolom C is gekoppeld aan kolom B. Als(B7=””;””;  hier wordt bedoeld Als in B7 niets staat, dan niets, anders  WEEKNUMMER(B7) het weeknummer behorende bij de datum uit cel B7

KOLOM D: kolom D is gekoppeld aan kolom C  Als(B7=””;””;  hier wordt bedoeld Als in B7 niets staat, dan niets, anders, moet de inhoud van Cel E7 (grondstofnummer)  samengevoegd worden met de inhoud van C7 (weeknummer)

KOLOM F: kolom F is gekoppeld aan kolom E  Als(E7=””;””;  hier wordt bedoeld Als in E7 niets staat, dan niets, anders moet er verticaal gezocht worden in de lijst grondstoffen vanaf cel $B$3 t/m cel $G$716. in de 2e kolom. De inhoud van E7 staat altijd in de 1e kolom. In de 2e kolom staan zie boven de grondstofnaam.

1 laatste opmerking bij de laatste formule: cel $B$3 t/m cel $G$716 dit adressen staat tussen Dollartekens het is een zgn absoluut cel adres. Heeft U later meer regels nodig op de lijst grondstoffen, voeg ze dan ergens tussen en nooit helemaal onderaan erbij. Alles wat U onderaan bijvoegt wordt in de formule niet meegenomen en U krijgt dan foutmeldingen omdat er iets niet is terug te vinden. Als U ze tussen voegt veranderen dit soort formules altijd mee

klik op de afbeelding voor een vergrote weergave

Hoofdbestand voorraadbeheer:

Voor de duidelijkheid bij de t.h.t heb ik de datums aangepast. Op regel 8 ziet U de cel roodgekleurd. Boven de rekenkolom staat 23 door mij opgegeven, zodra de berekening in kolom ! onder de 23 dagen staat wordt de cel rood. U heeft dan nog 23 dagen om de grondstof te verwerken. In kolom P geeft U het aantal KG of Liters op die binnen zijn gekomen. In kolom S ziet U dat er 83 KG uitgegaan is en dat er nog 17 KG over is

klik op de afbeelding voor een vergrote weergave

hoofdbestand berekening eindvoorraad:

De formule in kolom S luidt: .=SOM.ALS(‘MUTATIES – GRONDSTOFFEN’!$F$4:$F$1641;$D7;’MUTATIES – GRONDSTOFFEN’!$U$4:$U$1641). Deze formule wijst naar de rekenblad Mutaties Grondstoffen.

Op dit tabblad staat het badge nummer in kolom F regels 4 t/m 1641. de aantallen die verwerkt zijn staan daar in kolom U 4 t/m 1641. Het badge nummer dat moet worden getotaliseerd staat in het hoofdbestand in kolom D op regel 7

klik op de afbeelding voor een vergrote weergave

Aan het hoofdbestand en de grondstoffenlijst wil ik nog een leverancierslijst toevoegen en eventueel een bestelbon.

 

Mutatie bestand.

In kolom D /E/F kopieert U de gebruikte grondstoffen van het eindproduct. Kolom G is een vaste mutatie welke gebruikt wordt om de gebruikte aantallen in kolom U te berekenen waardoor deze van de juiste voorraadregel worden afgeschreven. Zie batchnummer van het ingrediënt in kolom D. Heeft U voor Uw recept 7 grondstoffen nodig  dan plaatst U in de kantlijn kolom a een X. Alle grondstoffen boven de X krijgen hetzelfde volgnummer. dit wordt automatsich ingevuld zodra U een nummer in kolom D plaatst. In dit voorbeeld hebben de eerste 7 regels volgnummer 501700001. Zodra U een grondstof op regel 12D invult wordt nummer 501700002 gebruikt totdat U weer een X plaatst.

Het volgnummer kun je zelf in de verborgen kolom B instellen instellen

klik op de afbeelding voor een vergrote weergave

Mutatieblad gebruikte formules:

In B2 geeft U het nummer op waarmee U wilt starten. op regel 6 staat: Als in A5 een X staat dan met er bij het nummer hierboven 1 bijgeteld worden. Onder volgnummer staat Als in kolom D niets staat dan moet er niets gebeuren anders moet het nummer hiernaast uit kolom B worden gegeven. Met verticaal zoeken (Hierboven al behandeld) wordt de omschrijving en de inkoopprijs opgehaald

klik op de afbeelding voor een vergrote weergave

Tabblad Recepten/kostprijs:

Dit tabblad bestaat uit ongeveer 1000 regels, In kolom C zit een automatische nummering. Het startnummer geeft U op in cel C2. De nummering verhoogt automatisch op het moment dat U een X plaatst in de kantlijn in kolom B.

klik op de afbeelding voor een vergrote weergave

Voorbeeld van de automatische nummering: Per recept gebruikt U steeds voldoende regels, houdt ook wat regels over als U meerdere batchnummers van een zelfde grondstof gaat gebruiken

klik op de afbeelding voor een vergrote weergave

Voorbeeld recept:

Op het moment dat U de grondstofnummers invult, krijgt U in de 2 kolommen erna de totale voorraad (kolom F) en het aantal batchnummers (kolom G) te zien. In het voorbeeld ziet U B100 daar is 120 Kg van in voorraad verdeeld over 2 goederen ontvangsten. u kunt nu het voorraadbestand raadplegen welke batchnummers U in het recept wil opnemen.

klik op de afbeelding voor een vergrote weergave

Voorbeeld compleet recept:

Het recept is nu ingedeeld in 3 hoofdgroepen

  • D t/m G grondstoffen/voorraden en aantal batchnummers
  • H t/m O Recept gegevens
  • P t/m R Eindgebruik grondstoffen

klik op de afbeelding voor een vergrote weergave

D t/m G grondstoffen/voorraden en aantal batchnummers:

Zodra een grondstofnummer in kolom E wordt opgegeven krijgt U daarnaast direct de voorraad te zien, ook ziet hoeveel partijen van dezelfde grondstof aanwezig zijn. Als er meerdere partijen zijn kunt U de oudste direct opmaken door bij het eindgebruik de partijen te splitsen. U heeft dan meer regels nodig. gebruikte formules: kolom F, G, I e worden automatisch opgezocht door Verticaal zoeken waarbij kolom E  het zoeknummer is. Voor kolom M is het zoeknummer de inhoud van kolom H de inkoopprijs kan nl verschillende zijn per batchnummer

H t/m O Vaste Recept gegevens

Als U de percentages van de gebruikte grondstoffen invult, geeft U ook het aantal KG/Ltrs op die u wilt gaan maken hierdoor worden de benodigde gegevens in kolom N uitgerekend. De aantallen KG/Ltr kunt U per keer aanpassen de % niet, dan dient U een nieuw recept aan te maken. Op het moment dat U een batchnummer invult wordt de bijbehorende inkoopprijs opgezocht. Wordt van een grondstof verschillende batchnummers gebruikt dan moet U die invullen in kolom H (zie onderste regel) de percentages laat U staan en past U niet aan. U kunt nu de definitieve lijst invullen

P t/m R Definitief Eindgebruik grondstoffen

Van alle grondstoffen die U gaat gebruiken geeft U op de regel van het bij behorende batchnummer de gebruikte aantallen KG/Ltrs op in kolom P, Het percentage in kolom Q wordt automatisch uitgerekend. Door kolom P in te vullen worden automatisch de laatste 2 kolommen ingevuld. Deze gegevens kopieert U naar het Mutatiebestand waardoor ze van de juiste voorraadnummer (batchnummers) worden afgeschreven. Hier krijgt ieder compleet eindproduct zijn eigen volgnummer en boekingsdatum, waarmee alle grondstoffen tot in het eindproduct traceerbaar zijn.

Mutaties uitgaande grondstoffen:

d.m.v. kopieren  zijn de batchnummers met de bijbehorende Kg op het mutatieblad geplaatst. Zolang U geen X zet in de kantlijn krijgen alle gebruikte grondstoffen het zelfde volgnummer mee. Met het volgnummer plaatst U het eindproduct op de voorraadlijst eindproducten. Met behulp van het volgnummer zijn alle gebruikte grondstoffen traceerbaar tot op het hoofdbestand waar ook een koppeling te vinden is naar de leverancier

Voorraad op het hoofdbestand na invullen voorraadmutaties

 

1-12-17 21.40 uur wordt vervolgt

Wat nog volgt:

Eindproducten.

Mutaties eindproducten.

klantenbestand.

klant factuur.