Hur man bygger ett mini-ESP i Excel steg för steg

Om du har ett litet företag eller startar ett projekt och kämpar varje månad med lösa Excel-ark, konton som inte balanserar och spridd data, är det viktigt att skapa en Ett mini-ERP-system i Excel kan vara precis vad du behöver.Du behöver inte spendera en förmögenhet på komplex programvara för att börja kontrollera försäljning, utgifter, kunder, lager och betalningar med lite ordning och en global överblick.

Tanken är att bygga en lätt men väldesignat system i Excel (o Google Ark) som centraliserar viktig information och låter dig jämföra budget med faktiska resultat, granska grundläggande indikatorer och fatta beslut varje vecka, utan att vänta 10 eller 15 dagar på bokslutet. Nedan ser du hur du konfigurerar det, vilka tabeller och ark du behöver, vilka bästa metoder du bör följa och hur du tar det till en nästan professionell nivå med hjälp av verktyg som Power Query och användarformulär.

Vad är ett mini-ERP i Excel och vad används det till?

När vi pratar om ett mini-ERP i Excel syftar vi på en strukturerad arbetsbok som centraliserar de grundläggande hanteringsprocessernaDet inkluderar försäljning, inköp, lager, treasury, försäljningsmål, kundreskontrauppföljning och en liten dashboard. Det är inte ett fullfjädrat ERP-system som de som används av stora företag, men det är ett system som eliminerar behovet av otaliga lösa filer och tvingar dig att arbeta med datalogik.

Huvudmålet är minska repetitivt arbete och koppla samman information som normalt är spriddFakturor på ett ställe, kundlistor på ett annat, lager på ett annat, bankkonton på ett annat, etc. Med en enda fil, eller med flera väl sammankopplade reskontra, kan du generera automatiska rapporter, upptäcka avvikelser och reagera i tid om försäljningen minskar, om vissa kunder ackumulerar för mycket skuld eller om lagret av en nyckelprodukt börjar ta slut.

Den här typen av lösning passar särskilt bra i Små och medelstora företag och mikroföretag som behöver organisation men inte har råd med ett stort ERP-system eller de som vill validera sina processer innan de tar steget. Det är också mycket användbart för entreprenörer som vill förstå, på ett mycket visuellt sätt, hur pengar rör sig i deras projekt: intäkter, direkta kostnader, fasta kostnader, marginal och kassaflöde.

Innan du öppnar Excel: vad ska du mäta och med vilken disciplin?

Det första är inte att skapa kalkylblad eller formler, utan bestäm vilka indikatorer du ska följa och hur oftaOm du försöker kontrollera allt från dag ett kommer du att bli utmattad och överge verktyget. Det är bättre att börja med några väl valda nyckeltal som är i linje med din budget.

Några mycket praktiska grundläggande indikatorer för ett mini-ERP är försäljning (enheter och belopp), antal biljetter eller beställningar, produktiva timmar, viktiga direkta kostnader (råvaror, direkt personaltid, daglig eller veckovis reklam) och en tydlig åtskillnad mellan rörliga och fasta kostnader. Allt eftersom du blir mer bekant med systemet kan du lägga till fler dimensioner, såsom produktkategorier, försäljningskanaler eller marginaler per produkt.

För varje KPI du ska följa upp, definiera tydligt detaljeringsnivån (dagligen, veckovis eller månadsvis), måttenheten och vem som ansvarar för att mata in dataTill exempel kanske du vill se försäljning per dag, produktiva timmar per vecka och fasta utgifter per månad. Det viktiga är att inte blanda ihop nivåer och att personen som matar in informationen vet exakt vad, var och när den ska matas in.

Dessutom är det viktigt att man från början är överens om en datainsamlingsrutinEn specifik tid varje dag eller vecka är avsatt för att registrera POS-försäljning, bankbetalningar, utfärdade fakturor och utgifter. Till exempel i slutet av varje dag för försäljning och produktion, och på måndagsmorgnar för att registrera föregående veckas banktransaktioner. Utan denna disciplin ser mini-ERP-systemet bra ut men är i princip värdelöst.

Grundläggande filstruktur: budget och verklighet

Ett robust och enkelt sätt att konfigurera ditt system är att tydligt separera budgetinformation från faktisk informationPå så sätt kan du enkelt jämföra vad du planerade att göra med vad som faktiskt hände och analysera avvikelser med hjälp av specifika kriterier.

Å ena sidan, skapa ett ark eller en fil som är din budgetbasDet är vanligtvis en tabell med kolumner som: datum eller vecka, koncept/konto/KPI, budgeterat belopp och, om du är intresserad av mer detaljer, någon klassificering efter typ av utgift eller inkomst. Du kan börja med en veckomall för "budget kontra faktiska utfall" och utöka den med dina egna kategorier.

Å andra sidan, utforma en bas av faktisk driftHär representerar varje rad en specifik händelse: en försäljning, en utgift, ett köp, en produktionskörning, en betalning osv. Typiska kolumner skulle innehålla: datum, transaktionstyp (försäljning, utgift, köp), beskrivning eller konto, belopp, kund eller leverantör, produkt, betalningsmetod osv. Senare kan du använda den här tabellen som en källa för pivottabeller eller Power Query-frågor som aggregerar per dag, vecka eller månad.

Tricket är att Se till att kategorierna i din faktiska databas matchar de i budgeten.Om din budget inkluderar en radpost som heter "Onlineannonsering" kan du inte slumpmässigt lista den utgiften i din verklighetstabell som "Annonser", "Försäljning" eller "Digital marknadsföring". Definiera en katalog över radposter och håll dig till den, annars blir din budget jämfört med faktiska jämförelser felaktiga. tillförlitlighet.

Instrumentpanel och navigeringsblad

För att göra mini-ERP-systemet enkelt för hela teamet att använda är det mycket bra att skapa ett första sidan som instrumentpanel och navigeringsmenyHärifrån kan du komma åt de andra avsnitten – inställningar, datatabeller, rapporter etc. – med hjälp av knappar eller hyperlänkar, och till och med exportera panelen till PowerPoint. dynamiska länkar mellan Excel och PowerPoint.

Det är en bra idé att inkludera några saker i den här kontrollpanelen Allmänna justeringar såsom månaden då räkenskapsåret börjar, huvudvalutan och betalningsintervallet som du vill använda för att flagga varningar om sena betalningar. Du kan till exempel bestämma att om en kund är mer än 15 dagar sen kommer det att markeras i betalningsvillkorsrapporten.

Förutom knapparna för att gå till varje sektion (produkter, kunder, försäljning, inköp, lager, treasury, lönsamhetsrapporter etc.) kan du reservera ett område för att visa Nyckelindikatorer i ett mycket visuellt formatTotala intäkter för perioden, bruttomarginal, nettomarginal, utestående kundsaldon och en kort kassaflödesöversikt för 7, 14, 30, 90 och 180 dagar. Detta förvandlar mini-ERP-systemet till en slags instrumentpanel som ger en snabb överblick över ditt företags hälsa.

Konfigurationsavsnitt: kategorier, användare och parametrar

Innan du börjar mata in data är det en bra idé att förbereda ett avsnitt av konfiguration där du definierar huvudlistorna och globala parametrarDen här delen är oftast lite mer "tråkig", men det är det som gör att allt annat flyter smidigt och sammanhängande.

I konfigurationsbladet kan du definiera produktkategorier, säljare, betalningsmetoder, utgiftstyper, sekundära valutor och alla andra kataloger som du kan tänkas vilja använda som en rullgardinsmeny i dina tabeller. Att använda rullgardinslistor (datavalidering) minskar fel drastiskt och säkerställer att koncepten alltid är konsekventa.

Det är också ett bra ställe att slå sig ner på kritiska tröskelvärdenTill exempel betalningsvillkoret vi nämnde tidigare, eller ett standardvärde för den kritiska lagernivån, som du sedan kan ange för varje produkt om det behövs. På samma sätt, om du arbetar med flera bankkonton, kan du lagra de kortnamn du kommer att använda när du registrerar kvitton och betalningar här.

Produktdefinition och grundläggande lagerhantering

En central del av varje mini-ERP är produktbladHär definierar du allt du köper, lagrar eller säljer: fysiska varor, tjänster, prenumerationer etc. Varje produkt måste ha minst ett namn och en unik kod.

I produkttabellen rekommenderas att inkludera fält som Produktnamn, kod, kategori, måttenhet, kritisk lagernivå (i dagar) Och om det tillför värde, varumärket eller din vanliga leverantör. Koden och kategorin kommer att göra ditt liv mycket enklare när du grupperar och analyserar senare, och enheten (styck, kg, meter, paket, etc.) förhindrar förvirring vid blandning av referenser.

Begreppet kritisk lagernivå i dagar Det är väldigt kraftfullt: det handlar inte bara om hur många enheter du har, utan hur många försäljningsdagar lagret täcker, givet din genomsnittliga dagliga försäljning. Senare, i lagertabellen, kan du beräkna en daglig förbrukningshastighet baserad på historisk försäljning och använda den för att uppskatta hur många dagar det nuvarande lagret räcker. När det värdet faller under en kritisk nivå kan kalkylbladet visa en röd varning.

Om din aktivitet kräver det kan du också lämna detaljer lagerplats, utgångsdatum, partinummer eller artikelnummer att hantera FIFO-metoder (först in, först ut) och ha bättre spårbarhet för varje produktbatch.

Kunddatabas och riskhantering

En annan grundpelare i ett effektivt mini-ERP-system i Excel är en väl underhållen kundlista Det fungerar nästan som ett mini-CRM. Här behöver du inte bara kundens namn, utan även kontaktuppgifter och kriterier för ekonomisk uppföljning.

En typisk kundarkstruktur kan innefatta Kundkod, namn, adress, telefonnummer, e-postadress, tilldelad säljare Och, mycket intressant, två viktiga parametrar: betalningsgräns och saldoriskgräns.

El betalningsfrist Ange hur många dagar du anser vara acceptabelt för kunden att betala dig. Om de överskrider det antalet på en faktura kan systemet flagga det i en betalningsvillkorsrapport. balansriskgräns Detta är det maximala beloppet du är villig att ackumulera som skuld till en klient; om det överstiger detta belopp utan betalning och dessutom överskrider dess löptid, bör du se det tydligt markerat i dina rapporter.

Att arbeta med unika kundkoder är en bra vana, även om du inte använder dem just nu. Koderna underlättar filtrering, gruppering och dataunderhåll (Om du till exempel ändrar kundens företagsnamn förblir koden densamma och ingenting går sönder.) Det är också lämpligt att undvika att ändra tabellstrukturen när den väl är igång; det är bättre att använda knappar eller makron för att sortera och rensa databasen.

Försäljningsmål, faktisk försäljning och prestationsanalys

Utöver att bara dokumentera vad som har hänt, får ett mini-ERP mycket styrka när du införlivar försäljningsmål och jämförelser med faktisk försäljningDetta gör att du kan utvärdera säljteamets prestationer och se om du närmar dig dina månatliga mål.

På ett målblad kan du för varje produkt och för varje månad ange försäljningsbelopp du strävar efter att uppnåDet behöver inte vara perfekt; även en rimlig uppskattning ger dig en bra referenspunkt. Om du arbetar efter kategorier kan du också definiera aggregerade mål per produktfamilj eller kanal.

På ett separat blad anger du faktisk månatlig eller daglig försäljning (Vi ska nu fördjupa oss i strukturen hos försäljningsfakturor.) Sedan kan du med hjälp av pivottabeller eller formler generera en "försäljningsprestanda"-rapport som visuellt visar hur stor procentandel av målet du har uppnått per produkt, per kategori eller per säljare, och markera de områden som ligger under eller över prognosen med färg.

Om du kombinerar denna metod med information om enhetskostnad och försäljningspris kommer du också att kunna analysera inte bara hur mycket du säljer, utan också hur lönsam varje produktlinje är.Detta avslöjar ibland överraskningar: bästsäljande produkter som knappt bidrar till marginalen, eller minoritetsreferenser som upprätthåller verksamhetens totala lönsamhet.

Produktkostnader, försäljningspriser och fasta kostnader

För att ditt mini-ERP-system ska ha en solid finansiell dimension behöver du ange tre typer av ekonomiska data: enhetskostnader för produkter, försäljningspriser och fasta kostnaderMed denna information blir lönsamhetsrapporter meningsfulla.

På produktkostnadslistan kan du registrera enhetskostnaden per produkt och per månadDetta är användbart eftersom inköpskostnaden kan variera beroende på el tiempoOch det är lämpligt att återspegla dessa variationer i marginalberäkningar. Om du kan uppskatta framtida kostnader kan du till och med prognostisera lönsamhetsscenarier flera månader i förväg.

I försäljningsprislistan definierar du det pris du säljer varje produkt eller tjänst förOm du säljer till samma pris under långa perioder behöver du inte röra den här delen särskilt ofta; om du hanterar många kampanjer eller priser per kund behöver du ett något mer sofistikerat system, men för ett mini-ESP räcker standardpriset vanligtvis.

mycket Fasta kostnader (Hyra, administrativ lön, licenser, försäkringar etc.) matas vanligtvis in varje månad och anses vara relativt stabila. Genom att mata in dessa data i systemet kommer dina finansiella rapporter att kunna visa brytpunkten, nettomarginaltrender och andelen fasta kostnader i förhållande till försäljning. Det är viktigt att undvika att skriva över grå celler eller celler som innehåller formler; generellt sett bör du bara fylla i vita celler, som är avsedda för datainmatning.

Fakturaregister för försäljning och inköp

Kärnan i mini-ERP:et ligger vanligtvis i tabell för försäljningsfakturapostHär anger du information om all din försäljning, antingen manuellt, genom att importera den med Power Query från en CSV-fil från kassasystemet, eller från ditt faktureringsprogram, om du har ett sådant.

En standardstruktur för den här tabellen skulle innehålla fält som fakturadatum, utgångsdatum, fakturanummer, produktkod och namn, kundkod och namn, kvantitet, enhet, pris, moms Och om du arbetar med batcher, ett artikelnummer att länka till lagret. Många av dessa kolumner kan fyllas i automatiskt från huvudtabellerna (till exempel hämtas enhet och produkttyp från produktbladet, kundnamnet hämtas från kundbladet, etc.).

Parallellt kan du ha ett ark med inköpsrekord Använd den här tabellen för att registrera inköp av varor eller tjänster. Även om vissa viktiga beräkningar kanske inte förlitar sig på den, är den mycket användbar för spårbarhet och lagerkontroll. Du kan lägga till kolumner som tullkod (vid behov), utgångsdatum, lagerplats, leverantör och annan information som hjälper till att fysiskt lokalisera varorna.

Kombinationen av både tabeller, försäljning och inköp, flöden lagermodulen och förbrukningsrapporternaOch, om man kopplar det väl till kostnader och priser, bruttomarginalen och lönsamhetsdelen per produkt eller per familj.

Lagerhantering och lageraviseringar

Med de registrerade köpen och försäljningarna kan du skapa ett kalkylblad över aktieregister som håller lagerstatusen för varje produkt uppdaterad. Även om du kan beräkna det med formler är det oftast mer praktiskt att centralisera det i en tabell som visar det tydligt.

På det här bladet, efter att produkten och, om tillämpligt, artikel- eller batchnummer har valts, kan resten av informationen hämtas automatiskt: enheter i lager, datum för första registrerade försäljning, datum för senaste försäljning, beräknad daglig konsumtion och uppskattade lagerdagar baserat på den konsumtionstakten. Allt detta beräknas genom att jämföra historiska försäljningsdata med lagerinleveranser.

Kolumn av beräknade lagerdagar Detta är särskilt användbart eftersom det låter dig se hur många försäljningsdagar det aktuella lagret täcker. Om detta värde faller under den kritiska nivån som definierats i produktinformationen kan systemet visa en röd varning för att påminna dig om att lägga en beställning. Detta hjälper dig att undvika både lagerbrist och överlagring av långsamt rörliga varor.

Övervakning av inkasso, deadlines och likviditet

Att sälja är bra, men om du inte får betalt i tid blir det jobbigt för verksamheten. Därför är det viktigt att inkludera [saknad information – troligen en funktion eller ett system] i ditt mini-ERP-system. modul för spårning av samlingar som registrerar de faktiska betalningar du får från kunder och jämför dem med vad de borde ha betalat enligt de utfärdade fakturorna.

Du kan registrera på fakturablanketten inkassodatum, kundkod, betalningsnummer eller referens, betalningsmetod, belopp, förväntat datum för insättning på konto, bank- och valfria växelDet förväntade insättningsdatumet är viktigt eftersom vissa betalningsmetoder (som kassaterminaler eller onlineplattformar) har en fördröjning mellan den tidpunkt då kunden debiteras och den faktiska krediteringen till ditt bankkonto.

Med dessa data, och genom att jämföra dem med försäljningsfakturatabellen, kan du generera löpande kontorapporter, betalningsvillkor och riskaviseringarTill exempel en rapport som visar dig, för varje kund, hur mycket pengar de är skyldiga dig, hur mycket de har betalat hittills, vilka fakturor som är försenade och hur många dagar sena de är. Du kan också filtrera efter säljare för att se kvaliteten på den portfölj som hanteras av var och en.

Om du också matar ett litet blad av statskassan vid olika tidshorisonter (7, 14, 30, 90 och 180 dagar)Detta gör att du kan förutse kassaflödesproblem och förhandla mer effektivt med banker och leverantörer. Detta perspektiv kompletteras av information om operativt rörelsekapital: kundfordringar, leverantörsskulder, lager och förutbetalda kostnader, samt genom att spåra kapitalutgifter (CAPEX), särskilt om dessa tillgångar är leasade och inte längre betraktas som rena driftskostnader.

Viktiga rapporter: ekonomi, lönsamhet och betalningsvillkor

När du väl har organiserat informationen väl börjar den mest givande delen: rapporternaI ett välbyggt Excel mini ERP-system är det normalt att ha minst fyra rapportblock: finansiell sammanfattning, försäljningsanalys, produktlönsamhet samt betalnings- och riskrapporter.

El sammanfattande finansiell rapport Den här rapporten samlar dina intäkter, bruttomarginal (försäljning minus kostnad för sålda varor) och nettomarginal (efter fasta kostnader och andra utgifter) i en enda vy. Allt eftersom du lägger till data månad för månad expanderar rapporten, vilket gör att du kan identifiera trender: starka och svaga månader, effekten av prisförändringar, vikten av rörliga kostnader med mera.

El analys av försäljningsfakturaregister Den bygger på försäljningstabellen och byggs vanligtvis med hjälp av pivottabeller. Du kan gruppera efter produkt, kategori, kund, säljare, geografiskt område eller kanal och snabbt se var din försäljning är koncentrerad. Med även en grundläggande förståelse för pivottabeller kan du skapa mycket flexibla vyer utan att ändra den underliggande strukturen.

El rapport om uppnående av försäljningsmål Den fokuserar på att jämföra mål med faktisk försäljning. Den presenteras vanligtvis som en grafisk instrumentpanel som visar procentandelen uppnådd per produkt eller kategori. Den är särskilt användbar för månatliga uppföljningar med säljteamet, eftersom den ger en snabb översikt över vem som når målen och vem som behöver stöd.

El produktlönsamhetsrapport Det är förmodligen ett av de mest avslöjande. Genom att jämföra försäljningspriser, enhetskostnader och fasta kostnader visar det vilka produkter eller affärsområden som faktiskt genererar vinst och vilka som kan sänka dina resultat. Ibland upptäcker man att en produkt med låg volym är en riktig pärla vad gäller marginal, eller att ett helt produktsortiment bör ses över eller ompositioneras.

Slutligen, den betalningsvillkorsrapport Den använder de risk- och betalningsgränser som du definierat i kundprofilen för att utvärdera deras betalningsbeteende. Systemet kan svara på frågor som: "Hur ofta betalar den här kunden?", "När var deras första och sista betalning?", "Liggar de inom den överenskomna betalningstiden?" och, baserat på detta, utfärda varningar till de som överskrider sin riskgräns och ackumulerar betydande sena betalningar.

Automatisering med Power Query, UserForms och makron

Om du vill ta ett steg framåt i kvalitet och minska manuella uppgifter är det väl värt att använda Power Query för strukturering och relatering av tabeller och användarformulär (UserForms i VBA) för datainmatning. Det är inte obligatoriskt, men det är en naturlig utveckling i takt med att mini-ERP-systemet börjar växa.

Med Power Query kan du Importera data från CSV-filer, kassasystem, kontoutdrag eller till och med andra Excel-arbetsböckerTransformera dem (rena kolumner, standardisera koncept, kombinera tabeller) och ladda in dem i din modell utan att behöva kopiera och klistra in varje gång, och för presentationer kan du också dra nytta av Dynamiska OLE- och DDE-länkar mellan Excel och PowerPointDessutom kan du definiera relationer mellan tabeller (till exempel mellan fakturor och kunder, eller mellan försäljning och produkter) på ett mycket liknande sätt som du skulle göra det i en lättviktsdatabas.

Med användarformulär och makron kan du bygga mer användarvänliga och guidade datainmatningsskärmarIstället för att användaren direkt redigerar tabellen fyller de i ett formulär med obligatoriska fält, rullgardinsmenyer och valideringar, och systemet fyller i motsvarande tabell med informationen. Detta minskar fel, förhindrar strukturella fel och möjliggör bättre kontroll över vem som har åtkomst till vad.

Naturligtvis är det viktigt att komma ihåg att Det är inte lämpligt att ändra tabellstrukturer som redan används av formler eller frågor.Om du behöver ändra design är det bäst att göra det noggrant, dokumentera befintliga beroenden eller arbeta med kopior tills du är säker. Du bör också överväga makro- och VBA-kompatibilitet, särskilt om du ska dela filen med användare av Mac eller äldre versioner av Excel.

Gå igenom rutinen och samarbeta med revisorn

Ett mini-ESP i Excel ger bara värde om det används konsekvent och granskas ofta. Därför är det viktigt att etablera en kort veckomöte att granska instrumentpanelen och fatta konkreta beslut.

I det mötet kan du istället för att gå vilse i detaljer fokusera på Tre huvudpunkter: största avvikelser mellan budget och verklighet, grundorsak och korrigerande åtgärderOm till exempel försäljningen ligger 20 % under prognoserna i en kategori analyserar du om problemet är relaterat till prissättning, marknadsföring, lager eller konkurrens, och definierar specifika åtgärder. Det är mycket användbart att dokumentera dessa åtgärder i en kolumn för "åtgärd" och en kolumn för "ansvarig part" i själva kalkylbladet, så att mätningen är direkt kopplad till ledningen.

Det är också en bra idé att kontrollera med din revisor eller rådgivare hur förkorta tidsfristerna för att lämna finansiell informationMånga företag arbetar med en fördröjning på 10 dagar eller mer i sina bokslut, vilket är praktiskt taget värdelöst för att fatta operativa beslut. Du kan implementera automatiseringsprocesser och förfiltrering av nyckelkonton ("före justeringar") för att få aktuell data om försäljning, direkta, rörliga och fasta kostnader, kassaflöde och rörelsekapital.

Om du integrerar informationen din revisor ger dig med ditt mini-ESP, får du en en mycket mer komplett överblick: redovisning, operativt arbete och kassaflödeDetta gör att du kan kontrollera både kortsiktigt (kassa och lager) och medellångsiktigt (lönsamhet och tillväxt) med ett verktyg som i huvudsak fortfarande bara är ett välstrukturerat Excel-ark.

Att konfigurera ett mini-ERP-system i Excel handlar inte om att bygga ett monstruöst makro eller skapa spektakulära diagram, utan om Tänk noga igenom vilka data som är avgörande för din verksamhet, organisera dem i väl utformade tabeller, automatisera det som är nödvändigt och granska dem disciplinerat.Om du strukturerar produkter, kunder, försäljning, inköp, kostnader, lager, inkasso och rapporter med hjälp av den logik vi har sett, använder verktyg som Power Query och formulär för att minska fel, och tar för vana att granska avvikelser och kassaflöde varje vecka över olika perioder, kommer du att ha ett förvånansvärt kraftfullt system för ditt företags storlek utan att behöva investera i ett traditionellt ERP-system.