Slik bygger du et mini-ERP-system i Excel trinn for trinn

Hvis du har en liten bedrift eller starter et prosjekt og sliter hver måned med løse Excel-regneark, kontoer som ikke balanserer og spredte data, er det viktig å sette opp en Et mini-ERP-system i Excel kan være akkurat det du trenger.Du trenger ikke å bruke en formue på kompleks programvare for å begynne å kontrollere salg, utgifter, kunder, lagerbeholdning og betalinger med litt orden og et globalt overblikk.

Tanken er å bygge en lett, men godt designet system i Excel (o Google Regneark) som sentraliserer viktig informasjon og lar deg sammenligne budsjett med faktiske resultater, gjennomgå grunnleggende indikatorer og ta avgjørelser hver uke, uten å vente 10 eller 15 dager på regnskapsavslutningen. Nedenfor ser du hvordan du konfigurerer det, hvilke tabeller og ark du trenger, hvilke beste fremgangsmåter du bør følge, og hvordan du tar det til et nesten profesjonelt nivå ved hjelp av verktøy som Power Query og brukerskjemaer.

Hva er et mini-ERP-system i Excel, og hva brukes det til?

Når vi snakker om et mini-ERP-system i Excel, refererer vi til et en strukturert arbeidsbok som sentraliserer de grunnleggende administrasjonsprosesseneDet inkluderer salg, kjøp, lager, treasury, salgsmål, sporing av kundefordringer og et lite dashbord. Det er ikke et fullverdig ERP-system som de som brukes av store selskaper, men det er et system som eliminerer behovet for utallige løse filer og tvinger deg til å jobbe med datalogikk.

Hovedmålet er redusere repeterende arbeid og koble sammen informasjon som vanligvis er spredtFakturaer på ett sted, kundelister på et annet, varelager på et annet, bankkontoer på et annet, osv. Med én enkelt fil, eller med flere godt sammenkoblede reskontroer, kan du generere automatiske rapporter, oppdage avvik og reagere i tide hvis salget avtar, hvis visse kunder akkumulerer for mye gjeld, eller hvis du går tom for lager av et nøkkelprodukt.

Denne typen løsning passer spesielt godt i SMB-er og mikrobedrifter som trenger organisering, men ikke har råd til et stort ERP-system eller de som ønsker å validere prosessene sine før de tar spranget. Det er også veldig nyttig for gründere som ønsker å forstå, på en svært visuell måte, hvordan penger beveger seg i prosjektet sitt: inntekter, direkte utgifter, faste utgifter, margin og kontantstrøm.

Før du åpner Excel: hva skal du måle, og med hvilken disiplin?

Det første er ikke å lage regneark eller formler, men bestem hvilke indikatorer du vil spore og hvor ofteHvis du prøver å kontrollere alt fra dag én, vil du ende opp med å bli utslitt og gi opp verktøyet. Det er bedre å starte med noen velvalgte KPI-er som er i tråd med budsjettet ditt.

Noen svært praktiske grunnleggende indikatorer for et mini-ERP er salg (enheter og beløp), antall billetter eller bestillinger, produktive timer, viktige direkte kostnader (råvarer, direkte personaltimer, daglig eller ukentlig annonsering) og et klart skille mellom variable og faste kostnader. Etter hvert som du blir mer kjent med systemet, kan du legge til flere dimensjoner, for eksempel produktkategorier, salgskanaler eller marginer per produkt.

For hver KPI du skal spore, definer tydelig detaljnivået (daglig, ukentlig eller månedlig), måleenheten og hvem som er ansvarlig for å legge inn dataeneFor eksempel kan du se salg per dag, produktive timer per uke og faste utgifter per måned. Det viktigste er å ikke blande nivåer, og at personen som legger inn dataene vet nøyaktig hva, hvor og når de skal legges inn.

Videre er det viktig å bli enige fra starten av om en rutine for datafangstEn bestemt tid hver dag eller uke er dedikert til å registrere POS-salg, bankbetalinger, utstedte fakturaer og utgifter. For eksempel på slutten av hver dag for salg og produksjon, og på mandag morgen for å registrere forrige ukes banktransaksjoner. Uten denne disiplinen ser mini-ERP-systemet bra ut, men er i hovedsak ubrukelig.

Grunnleggende filstruktur: budsjett og realitet

En robust og enkel måte å sette opp systemet ditt på er å tydelig skille budsjettinformasjon fra faktisk informasjonPå denne måten kan du enkelt sammenligne hva du planla å gjøre med hva som faktisk skjedde, og analysere avvik ved hjelp av spesifikke kriterier.

På den ene siden lager du et ark eller en fil som er din budsjettgrunnlagDet er vanligvis en tabell med kolonner som: dato eller uke, konsept/konto/KPI, budsjettert beløp, og hvis du er interessert i flere detaljer, en klassifisering etter type utgift eller inntekt. Du kan starte med en ukentlig mal for «budsjett vs. faktisk» og utvide den med dine egne kategorier.

På den annen side, design en base av faktisk driftHer representerer hver rad en spesifikk hendelse: et salg, en utgift, et kjøp, en produksjonskjøring, en betaling osv. Typiske kolonner vil inkludere: dato, transaksjonstype (salg, utgift, kjøp), beskrivelse eller konto, beløp, kunde eller leverandør, produkt, betalingsmåte osv. Senere kan du bruke denne tabellen som en kilde for pivottabeller eller Power Query-spørringer som aggregerer etter dag, uke eller måned.

Trikset er at Sørg for at kategoriene i den faktiske databasen samsvarer med de i budsjettet.Hvis budsjettet ditt inkluderer en linjepost kalt «Nettannonsering», kan du ikke tilfeldig føre opp den utgiften i realitetstabellen din som «Annonser», «Salg» eller «Digital markedsføring». Definer en katalog over linjeposter og hold deg til den, ellers vil budsjettet ditt mot faktiske sammenligninger være unøyaktige. pålitelighet.

Dashbord og navigasjonsark

For å gjøre mini-ERP-en enkel for hele teamet å bruke, er det veldig nyttig å lage en startside som dashbord og navigasjonsmenyHerfra kan du få tilgang til de andre seksjonene – innstillinger, datatabeller, rapporter osv. – ved hjelp av knapper eller hyperlenker, og til og med eksportere panelet til PowerPoint. dynamiske koblinger mellom Excel og PowerPoint.

Det er lurt å inkludere noen ting i dette kontrollpanelet Generelle justeringer, som for eksempel måneden regnskapsåret begynner, hovedvalutaen og betalingsperioden som du vil bruke til å flagge varsler om forsinket betaling. Du kan for eksempel bestemme at hvis en kunde er mer enn 15 dager forsinket, skal det fremheves i betalingsbetingelsesrapporten.

I tillegg til knappene for å gå til hver seksjon (produkter, kunder, salg, kjøp, lager, treasury, lønnsomhetsrapporter osv.), kan du reservere et område for visning. Nøkkelindikatorer i et svært visuelt formatTotalinntekter for perioden, bruttomargin, nettomargin, utestående kundesaldoer og en kort kontantstrømoppsummering for 7, 14, 30, 90 og 180 dager. Dette forvandler mini-ERP-systemet til et slags dashbord som gir en rask oversikt over bedriftens helsetilstand.

Konfigurasjonsseksjon: kategorier, brukere og parametere

Før du begynner å legge inn data, er det lurt å forberede en del av konfigurasjon der du definerer hovedlistene og globale parametereDenne delen er vanligvis litt mer «kjedelig», men det er det som gjør at alt annet flyter jevnt og sammenhengende.

I konfigurasjonsarket kan du definere produktkategorier, selgere, betalingsmetoder, utgiftstyper, sekundære valutaer og enhver annen katalog du måtte ønske å bruke som en rullegardinmeny i tabellene dine. Bruk av rullegardinlister (datavalidering) reduserer feil drastisk og sikrer at konseptene alltid er konsistente.

Det er også et bra sted å slå seg ned kritiske tersklerFor eksempel betalingsbetingelsen vi nevnte tidligere, eller en standardverdi for det kritiske lagernivået, som du deretter kan angi for hvert produkt om nødvendig. På samme måte, hvis du jobber med flere bankkontoer, kan du lagre de korte navnene du vil bruke når du registrerer mottak og betalinger her.

Produktdefinisjon og grunnleggende lagerstyring

En sentral del av enhver mini-ERP er produktarkHer definerer du alt du kjøper, lagrer eller selger: fysiske varer, tjenester, abonnementer osv. Hvert produkt må ha minst ett navn og en unik kode.

I produkttabellen anbefales det å inkludere felt som Produktnavn, kode, kategori, måleenhet, kritisk lagernivå (i dager) Og hvis det tilfører verdi, merkevaren eller din vanlige leverandør. Koden og kategorien vil gjøre livet ditt mye enklere når du grupperer og analyserer senere, og enheten (stykker, kg, meter, pakker osv.) forhindrer forvirring når du blander referanser.

Konseptet av kritisk lagernivå i dager Det er veldig kraftig: det handler ikke bare om hvor mange enheter du har, men hvor mange salgsdager den lagerbeholdningen vil dekke, gitt ditt gjennomsnittlige daglige salg. Senere, i lagertabellen, kan du beregne en daglig forbruksrate basert på historisk salg og bruke den til å anslå hvor mange dager den nåværende lagerbeholdningen vil vare. Når den verdien faller under et kritisk nivå, kan regnearket vise et rødt varsel.

Hvis aktiviteten din krever det, kan du også oppgi detaljer lagerlokasjon, utløpsdato, lotnummer eller delenummer å håndtere FIFO-metoder (først inn, først ut) og ha bedre sporbarhet for hvert produktparti.

Kundedatabase og risikostyring

En annen grunnpilar i et effektivt mini-ERP-system i Excel er en en godt vedlikeholdt kundeliste Det fungerer nesten som et mini-CRM. Her trenger du ikke bare kundens navn, men også kontaktinformasjon og kriterier for økonomisk sporing.

En typisk struktur for kundeark kan inkludere Kundekode, navn, adresse, telefonnummer, e-postadresse, tildelt salgsrepresentant Og, veldig interessant, to nøkkelparametere: betalingstermingrense og saldorisikogrense.

El betalingsfrist Angi antall dager du anser som akseptabelt for kunden å betale deg. Hvis de overskrider dette antallet på en faktura, kan systemet flagge det i en betalingsbetingelsesrapport. balanserisikogrense Dette er det maksimale beløpet du er villig til å akkumulere som gjeld hos en klient. Hvis det overstiger dette beløpet uten betaling, og i tillegg går utover lånefristen, bør du se det tydelig markert i rapportene dine.

Det er en god vane å jobbe med unike kundekoder, selv om du ikke bruker dem akkurat nå. Kodene forenkler filtrering, gruppering og datavedlikehold (Hvis du for eksempel endrer kundens firmanavn, forblir koden den samme, og ingenting brytes.) Det er også lurt å unngå å endre tabellstrukturen når den kjører. Det er bedre å bruke knapper eller makroer for å sortere og rense databasen.

Salgsmål, faktisk salg og resultatanalyse

Utover å bare registrere hva som har skjedd, får et mini-ERP mye styrke når du integrerer Salgsmål og sammenligninger med faktisk salgDette lar deg evaluere salgsteamets ytelse og se om du kommer nærmere dine månedlige mål.

På et målskjema kan du for hvert produkt og for hver måned føre inn salgsbeløpet du ønsker å oppnåDet trenger ikke å være perfekt; selv et rimelig estimat vil gi deg et godt referansepunkt. Hvis du jobber etter kategorier, kan du også definere samlede mål etter produktfamilie eller kanal.

På et eget ark skriver du inn faktisk månedlig eller daglig salg (Vi skal nå gå dypere inn i strukturen til salgsfakturaer). Deretter kan du ved hjelp av pivottabeller eller formler generere en «salgsresultat»-rapport som visuelt viser deg hvor stor prosentandel av målet du har nådd per produkt, per kategori eller per selger, og markere områdene som er under eller over prognosen med farge.

Hvis du kombinerer denne tilnærmingen med enhetskostnad og salgsprisinformasjon, vil du også kunne analyser ikke bare hvor mye du selger, men også hvor lønnsom hver produktlinje er.Dette avdekker noen ganger overraskelser: bestselgende produkter som knapt bidrar til marginen, eller minoritetsreferanser som opprettholder den samlede lønnsomheten til virksomheten.

Produktkostnader, salgspriser og faste kostnader

For at mini-ERP-systemet ditt skal ha en solid økonomisk dimensjon, må du legge inn tre typer økonomiske data: enhetsproduktkostnader, salgspriser og faste kostnaderMed denne informasjonen gir lønnsomhetsrapporter mening.

På produktkostnadslisten kan du registrere enhetskostnaden per produkt og per månedDette er nyttig fordi kjøpsprisen kan variere med tidenOg det er lurt å gjenspeile disse variasjonene i marginberegninger. Hvis du kan estimere fremtidige kostnader, kan du til og med projisere lønnsomhetsscenarioer flere måneder frem i tid.

I salgsprislisten definerer du prisen du selger hvert produkt eller hver tjeneste forHvis du selger til samme pris over lengre perioder, trenger du ikke å røre denne delen så ofte. Hvis du håndterer mange kampanjer eller priser per kunde, trenger du et noe mer sofistikert system, men for et mini-ERP-system er standardprisen vanligvis tilstrekkelig.

den faste kostnader (Husleie, administrativ lønn, lisenser, forsikring osv.) legges vanligvis inn månedlig og anses som relativt stabile. Ved å legge inn disse dataene i systemet, vil de økonomiske rapportene dine kunne vise break-even-punktet, trender i nettofortjeneste og andelen faste kostnader i forhold til salg. Det er viktig å unngå å overskrive grå celler eller celler som inneholder formler. Generelt bør du bare fylle ut hvite celler, som er beregnet for dataregistrering.

Fakturaoversikt for salg og kjøp

Kjernen i mini-ERP-systemet ligger vanligvis i tabell for salgsfakturapostHer legger du inn informasjon om alt salget ditt, enten manuelt, ved å importere det med Power Query fra en CSV-fil fra POS-systemet, eller fra faktureringsprogrammet ditt, hvis du har et.

En standardstruktur for denne tabellen ville inneholde felt som fakturadato, utløpsdato, fakturanummer, produktkode og navn, kundekode og navn, antall, enhet, pris, mva. Og hvis du jobber med batcher, et delenummer som skal kobles til lagerbeholdningen. Mange av disse kolonnene kan fylles ut automatisk fra hovedtabellene (for eksempel hentes enhet og produkttype fra produktarket, kundenavnet hentes fra kundearket osv.).

Parallelt kan du ha et ark med kjøpsrekord Bruk denne tabellen til å registrere kjøp av varer eller tjenester. Selv om noen viktige beregninger kanskje ikke er avhengige av den, er den svært nyttig for sporbarhet og lagerstyring. Du kan legge til kolonner som tariffkode (om nødvendig), utløpsdato, lagerlokasjon, leverandør og annen informasjon som hjelper med å fysisk finne varene.

Kombinasjonen av både tabeller, salg og kjøp, feeder lagermodulen og forbruksrapporteneOg hvis du kobler det godt sammen med kostnader og priser, bruttomarginen og lønnsomhetsdelen per produkt eller per familie.

Lagerstyring og lagervarsler

Med de registrerte kjøpene og salgene kan du lage et regneark over aksjeopptegnelser som holder lagerstatusen for hvert produkt oppdatert. Selv om du kan beregne det med formler, er det vanligvis mer praktisk å sentralisere det i en tabell som viser det tydelig.

På dette arket, etter at du har valgt produkt og, hvis aktuelt, dele- eller batchnummer, kan resten av dataene hentes automatisk: enheter på lager, dato for første registrerte salg, dato for siste salg, estimert daglig forbruksrate og estimerte lagerdager basert på denne forbruksraten. Alt dette beregnes ved å kryssreferere historiske salgsdata med lagerkvitteringer.

Kolonnen til estimerte lagerdager Dette er spesielt nyttig fordi det lar deg se hvor mange salgsdager den nåværende varelageret dekker. Hvis denne verdien faller under det kritiske nivået som er definert i produktdetaljene, kan systemet vise en rød advarsel for å minne deg på å legge inn en bestilling. Dette hjelper deg med å unngå både lagerbrist og overfylling av lageret med varer med lav omsetning.

Overvåking av inkasso, tidsfrister og likviditet

Det er flott å selge, men hvis du ikke får betalt i tide, lider bedriften. Derfor er det viktig å inkludere [manglende informasjon - sannsynligvis en funksjon eller et system] i mini-ERP-systemet ditt. modul for sporing av samlinger som registrerer de faktiske betalingene du mottar fra kunder og sammenligner dem med det de skulle ha betalt i henhold til de utstedte fakturaene.

Du kan registrere på fakturabladet innkrevingsdato, kundekode, betalingsnummer eller referanse, betalingsmåte, beløp, forventet dato for innskudd på konto, bank og valgfrie vekselerForventet innskuddsdato er viktig fordi noen betalingsmetoder (som POS-terminaler eller nettplattformer) har en forsinkelse mellom tidspunktet kunden blir belastet og den faktiske krediteringen til bankkontoen din.

Med disse dataene, og ved å kryssreferere dem med salgsfakturatabellen, kan du generere rapporter om brukskontoer, betalingsbetingelser og risikovarslerFor eksempel en rapport som viser deg, for hver klient, hvor mye penger de skylder deg, hvor mye de har betalt til dags dato, hvilke fakturaer som er forfalte og hvor mange dager forsinket de er. Du kan også filtrere etter selger for å se kvaliteten på porteføljen som administreres av hver enkelt.

Hvis du også gir et lite blad med statskassen ved ulike horisonter (7, 14, 30, 90 og 180 dager)Dette vil gjøre det mulig for deg å forutse kontantstrømproblemer og forhandle mer effektivt med banker og leverandører. Dette perspektivet suppleres av informasjon om driftskapital: kundefordringer, leverandørgjeld, varelager og forhåndsbetalte kostnader, samt ved å spore kapitalutgifter (CAPEX), spesielt hvis disse eiendelene er leaset og ikke lenger anses som rene driftskostnader.

Viktige rapporter: økonomi, lønnsomhet og betalingsbetingelser

Når du har dataene godt organisert, begynner den mest givende delen: rapporteneI et godt bygget Excel mini ERP-system er det normalt å ha minst fire blokker med rapporter: økonomisk sammendrag, salgsanalyse, produktlønnsomhet og betalings- og risikorapporter.

El sammendrag av finansrapporten Denne rapporten samler inntekter, bruttomargin (salg minus varekostnad) og nettomargin (etter faste kostnader og andre utgifter) i én enkelt visning. Etter hvert som du legger til data måned for måned, utvides denne rapporten, slik at du kan identifisere trender: sterke og svake måneder, effekten av prisendringer, vekten av variable utgifter og mer.

El analyse av salgsfakturaer Den er basert på salgstabellen og er vanligvis bygget opp ved hjelp av pivottabeller. Du kan gruppere etter produkt, kategori, kunde, selger, geografisk område eller kanal, og raskt se hvor salget ditt er konsentrert. Med selv en grunnleggende forståelse av pivottabeller kan du lage svært fleksible visninger uten å endre den underliggende strukturen.

El rapport om oppnåelse av salgsmål Den fokuserer på å sammenligne mål med faktisk salg. Den presenteres vanligvis som et grafisk dashbord som viser prosentandelen av oppnåelse per produkt eller kategori. Den er spesielt nyttig for månedlige gjennomganger med salgsteamet, da den gir en rask oversikt over hvem som når målene og hvem som trenger støtte.

El produktlønnsomhetsrapport Det er sannsynligvis et av de mest avslørende. Ved å kryssreferere salgspriser, enhetskostnader og faste kostnader, viser det deg hvilke produkter eller forretningsområder som faktisk genererer fortjeneste, og hvilke som kan trekke ned resultatene dine. Noen ganger oppdager du at et lavvolumsprodukt er en virkelig perle når det gjelder margin, eller at et helt produktsortiment bør gjennomgås eller omposisjoneres.

Til slutt, betalingsbetingelsesrapport Den bruker risiko- og betalingsgrensene du har definert i kundeprofilen for å evaluere betalingsatferden deres. Systemet kan svare på spørsmål som: «Hvor ofte betaler denne kunden?», «Når var deres første og siste betaling?», «Er de innenfor den avtalte betalingsfristen?» og, basert på dette, utstede advarsler til de som overskrider risikogrensen og akkumulerer betydelige forsinkede betalinger.

Automatisering med Power Query, UserForms og makroer

Hvis du vil ta et kvalitetssprang og redusere manuelle oppgaver, er det vel verdt å bruke det. Power Query for strukturering og relasjon av tabeller og brukerskjemaer (UserForms i VBA) for dataregistrering. Det er ikke obligatorisk, men det er en naturlig utvikling etter hvert som mini-ERP-systemet begynner å vokse.

Med Power Query kan du Importer data fra CSV-filer, salgssystemer, kontoutskrifter eller til og med andre Excel-arbeidsbøkerTransformer dem (rydd kolonner, standardiser konsepter, kombiner tabeller) og last dem inn i modellen din uten å måtte kopiere og lime inn hver gang, og for presentasjoner kan du også dra nytte av Dynamiske OLE- og DDE-koblinger mellom Excel og PowerPointI tillegg kan du definere forhold mellom tabeller (for eksempel mellom fakturaer og kunder, eller mellom salg og produkter) på en veldig lik måte som du ville gjort det i en lettvektsdatabase.

Brukerskjemaer og makroer lar deg bygge mer brukervennlige og veiledede skjermbilder for dataregistreringI stedet for at brukeren redigerer tabellen direkte, fyller de ut et skjema med obligatoriske felt, rullegardinmenyer og valideringer, og systemet fyller ut den tilhørende tabellen med informasjonen. Dette reduserer feil, forhindrer strukturelle feil og gir bedre kontroll over hvem som har tilgang til hva.

Selvfølgelig er det viktig å huske at Det er ikke tilrådelig å endre tabellstrukturer som allerede brukes av formler eller spørringer.Hvis du trenger å endre design, er det best å gjøre det nøye, dokumentere eksisterende avhengigheter eller jobbe med kopier til du er sikker. Du bør også vurdere makro- og VBA-kompatibilitet, spesielt hvis du skal dele filen med brukere av Mac eller eldre versjoner av Excel.

Gjennomgå rutinene og samarbeid med regnskapsføreren

Et mini-ERP-system i Excel gir bare verdi hvis det brukes konsekvent og gjennomgås ofte. Derfor er det viktig å etablere et kort ukentlig møte å gjennomgå dashbordet og ta konkrete beslutninger.

I det møtet kan du fokusere på detaljene, i stedet for å gå deg vill. Tre hovedpunkter: største avvik mellom budsjett og virkelighet, underliggende årsak og korrigerende tiltakHvis for eksempel salget er 20 % under anslagene i en kategori, analyserer du om problemet er relatert til prising, kampanjer, lagerbeholdning eller konkurranse, og definerer spesifikke tiltak. Det er svært nyttig å dokumentere disse handlingene i en «handling»-kolonne og en «ansvarlig part»-kolonne i selve regnearket, slik at målingen er direkte knyttet til ledelsen.

Det er også lurt å sjekke med regnskapsføreren eller rådgiveren din hvordan forkorte fristene for å levere finansiell informasjonMange firmaer opererer med en forsinkelse på 10 dager eller mer i avslutningen av regnskapet, noe som er praktisk talt ubrukelig for å ta driftsbeslutninger. Du kan implementere automatiseringsprosesser og forhåndsfiltrering av nøkkelkontoer ("før justeringer") for å ha oppdaterte data om salg, direkte, variable og faste kostnader, kontantstrøm og arbeidskapital.

Hvis du integrerer informasjonen regnskapsføreren din gir deg med mini-ERP-en din, vil du ha en en mye mer komplett oversikt: regnskap, drift og kontantstrømDette lar deg kontrollere både kortsiktig (treasury og varelager) og mellomlang sikt (lønnsomhet og vekst) med et verktøy som i hovedsak fortsatt bare er et velstrukturert Excel-regneark.

Å sette opp et mini-ERP-system i Excel handler ikke om å bygge en uhyrlig makro eller lage spektakulære diagrammer, men om Tenk nøye gjennom hvilke data som er kritiske for virksomheten din, organiser dem i veldesignede tabeller, automatiser det som er nødvendig, og gjennomgå dem med disiplin.Hvis du strukturerer produkter, kunder, salg, kjøp, kostnader, lager, inkasso og rapporter ved hjelp av logikken vi har sett, bruker verktøy som Power Query og skjemaer for å redusere feil, og gjør det til en vane å gjennomgå avvik og kontantstrøm ukentlig over ulike perioder, vil du ha et overraskende kraftig system for størrelsen på bedriften din uten å ha investert i et tradisjonelt ERP-system.