Excel för bokföring: Viktiga mallar och formler

Även med framväxten av dedikerade, molnbaserade bokföringsprogram förblir Microsoft Excel den obestridda arbetshästen inom finans- och redovisningsbranschen. Från att förbereda månadsavstämningar till att bygga komplexa finansiella modeller erbjuder Excel den flexibilitet och råa beräkningskraft som stela bokföringssystem ofta saknar.

Oavsett om du är en småföretagare som hanterar din egen bokföring eller en företagsredovisningsekonom som hanterar tusentals rader med transaktionsdata, är det ett ovärderligt krav att bemästra Excel. I den här guiden går vi igenom de viktiga Excel-mallar och formler som varje ekonom behöver, komplett med praktiska genomgångar och konkreta exempel.

Sätta upp mallen för huvudboken (GL)

Huvudboken är huvudförvaret för alla dina finansiella transaktioner. Om du använder Excel för att sköta bokföringen för en mindre verksamhet är det avgörande att strukturera huvudboken rätt från dag ett. En dåligt strukturerad huvudbok kommer att göra det omöjligt att generera automatiserade rapporter senare.

En standardhuvudbok i Excel bör ställas in som ett kontinuerligt tabellformat. Undvik att hoppa över rader eller infoga tomma kolumner mellan data. Här är ett exempel på den ideala kolumnstrukturen:

Date	Transaction ID	Account Code	Description	Debit	Credit	Running Balance
2023-10-01	TRX-001	1010 (Cash)	Ägarinsättning	$10,000		$10,000
2023-10-03	TRX-002	6010 (Rent)	Hyresinbetalning oktober		$2,000	$8,000
2023-10-05	TRX-003	4010 (Sales)	Faktura Kund A	$1,500		$9,500

Skapa ett dynamiskt löpande saldo

För att beräkna ett löpande saldo som uppdateras dynamiskt när du lägger till rader behöver du en formel som adderar Debet och subtraherar Kredit från föregående rads saldo. Förutsatt att rad 1 är din rubrikrad och rad 2 innehåller din första transaktion, placera ditt startsaldo i G2. I cell G3, ange:

=G2 + E3 - F3

Dra ner den här formeln. För att förhindra att formeln visar upprepade summor på tomma rader under dina data, omslut den i en OM-sats (IF) som kontrollerar om datumkolumnen (A) är tom:

=IF(A3="", "", G2 + E3 - F3)

Proffstips: För att säkerställa konsekvens och förhindra stavfel i din kontokodskolumn, ställ in en kontoplan på en separat flik och använd datavalidering för att kontrollera inmatning via en rullgardinsmeny. Detta kommer att spara dig timmar av felsökning när det är dags att bygga dina finansiella rapporter.

Bygga dynamiska finansiella rapporter (Resultat- & Balansräkning)

När din huvudbok är korrekt strukturerad blir det en fråga om att aggregera data baserat på kontokoder för att generera en resultaträkning och balansräkning. Den mest kraftfulla funktionen för denna uppgift är SUMIFS.

SUMIFS låter dig summera värden i ett intervall endast om de uppfyller flera kriterier (t.ex. matchar en specifik kontokod OCH infaller inom ett specifikt datumintervall). Att bemästra villkorsstyrd summering med SUMIF och SUMIFS är avgörande för automatiserad finansiell rapportering.

Steg-för-steg: Automatisera en resultaträkning

1. På ett nytt blad, lista dina intäkts- och kostnadskonton i kolumn A, och deras motsvarande kontokoder i kolumn B.
2. I cellerna C1 och D1 anger du datumen för din rapporteringsperiod (t.ex. Startdatum: 2023-10-01, Slutdatum: 2023-10-31).
3. I kolumn E (Total-kolumnen), använd SUMIFS för att hämta de totala debet- eller kreditbeloppen för den specifika kontokoden inom datumintervallet.

Här är syntaxen för att summera Kredit-kolumnen (Intäkter) från ett blad som heter "GL" för kontokod "4010" i oktober:

=SUMIFS(GL!$F:$F, GL!$C:$C, 4010, GL!$A:$A, ">="&$C$1, GL!$A:$A, "<="&$D$1)

Låt oss bryta ner vad den här formeln gör:

• GL!$F:$F: Intervallet vi vill summera (Kredit-kolumnen).
• GL!$C:$C: Det första kriterieintervallet (Kontokod-kolumnen).
• 4010: Den specifika kontokoden vi letar efter.
• GL!$A:$A: Det andra kriterieintervallet (Datum-kolumnen).
• ">="&$C$1: Talar om för Excel att datumet måste vara större än eller lika med vårt startdatum.
• "<="&$D$1: Talar om för Excel att datumet måste vara mindre än eller lika med vårt slutdatum.

Effektivisera bankavstämningar

Bankavstämning är processen att matcha saldona i ditt företags bokföring mot motsvarande information på ett kontoutdrag. Excel är ovärderligt för att upptäcka avvikelser, saknade betalningar eller dubbla bankavgifter.

Det snabbaste sättet att stämma av stora listor med transaktioner är att exportera ditt kontoutdrag till Excel och placera det sida vid sida med din interna huvudbok. Använd sedan sökfunktioner för att hitta matchande belopp eller referensnummer.

Även om VLOOKUP ofta används av många redovisningsekonomer, ger övergången till INDEX MATCH-sökmetoden mycket mer flexibilitet, särskilt när ditt sökvärde (som ett checknummer) inte finns i den första kolumnen i din tabell.

Använda EXAKT och villkorsstyrd formatering

Om du har sorterat båda listorna efter datum och belopp kan du helt enkelt subtrahera bankbeloppet från bokfört belopp. Resultatet 0 betyder att de matchar.

=Book_Amount - Bank_Amount

Du kan sedan tillämpa Villkorsstyrd formatering (Regler för markering av celler > Lika med > 0) för att göra alla matchande rader gröna, vilket gör att de återstående omarkerade objekten (avstämningsposterna) sticker ut omedelbart.

Hantera kund- och leverantörsreskontra (Förfallolistor)

Kassaflödet är livsnerven i varje företag. Att spåra kundreskontra (vem som är skyldig dig pengar) och leverantörsreskontra (vem du är skyldig pengar) är en daglig uppgift. Att skapa en förfallolista (åldersanalys) i Excel hjälper dig att identifiera vilka fakturor som är aktuella, förfallna eller kraftigt försenade.

För att bygga en förfallolista behöver du beräkna skillnaden mellan dagens datum och fakturans förfallodatum, och sedan placera det numret i kategorier (t.ex. 0-30 dagar, 31-60 dagar, 61-90 dagar, 90+ dagar).

Genomgång: Skapa en förfalloformel

Anta att kolumn A har fakturanumret, kolumn B har kundnamnet, kolumn C har förfallodatumet och kolumn D har det utestående saldot. I kolumn E vill vi beräkna antalet förfallna dagar.

=TODAY() - C2

Funktionen TODAY() returnerar alltid dagens datum. Om resultatet är ett negativt tal har fakturan inte förfallit ännu. Därefter kategoriserar vi de förfallna dagarna i kolumn F. Du kan använda logiska tester och nästlade IF-satser för att kategorisera dessa förfallna fakturor perfekt:

=IF(E2<0, "Not Due", IF(E2<=30, "1-30 Days", IF(E2<=60, "31-60 Days", IF(E2<=90, "61-90 Days", "Over 90 Days"))))

När dina data har kategoriserats kan du infoga en pivottabell för att summera de utestående saldona per kund och förfallokategori, vilket ger ledningen en tydlig bild av inkassoprioriteringar.

5 viktiga Excel-formler som varje ekonom behöver

Utöver grundläggande aritmetik kräver modern bokföring en handfull specialiserade formler för att hantera avskrivningar, periodiseringar och prognoser.

1. EOMONTH (End of Month): Avgörande för att projicera periodiseringar och skapa standardiserade månadsrubriker. =EOMONTH(A2, 0) returnerar den sista dagen i månaden för datumet i A2. Om du ändrar 0 till 1 får du sista dagen i nästa månad.
2. EDATE: Perfekt för att beräkna förfallodatum för lån eller investeringar. Den lägger till ett specifikt antal månader till ett datum. =EDATE(Start_Date, 12) lägger till exakt 12 månader.
3. PMT (Payment): Används för att beräkna betalningen för ett lån baserat på konstanta betalningar och en konstant ränta. Avgörande för att amortera skulder. Syntax: =PMT(rate, nper, pv).
4. SLN (Straight-Line Depreciation): Returnerar den linjära avskrivningen för en tillgång under en period. Syntax: =SLN(cost, salvage, life).
5. XLOOKUP: Den moderna ersättaren till VLOOKUP och INDEX MATCH. Den kan söka åt vänster, returnera ett specifikt meddelande om ett fel hittas och söka nerifrån och upp. Rekommenderas starkt om du använder Microsoft 365.

Automatisera grovjobbet med Power Query

Att kopiera och klistra in data från bokföringsprogram till Excel-mallar varje månad är tråkigt och ökar risken för mänskliga fel. Om du märker att du manuellt formaterar CSV-exporter från QuickBooks, Xero eller din bank varje månad är det dags att uppgradera ditt arbetsflöde.

Du kan använda Power Query för att importera och transformera data som ett proffs. Power Query låter dig bygga en anslutning till en rådatafil (som en månatlig CSV-dump). Du kan ställa in regler för att automatiskt radera onödiga översta rader, ändra text till datum, fylla ner tomma kontonummer och ta bort pivotering av kolumner. Nästa månad lägger du helt enkelt den nya CSV-filen i mappen, klickar på "Uppdatera" i Excel, och alla dina formateringssteg tillämpas omedelbart.

Skriv bokföringsformler snabbare med AI

Att memorera komplexa, djupt nästlade formler kan vara skrämmande, även för erfarna finansexperter. Om du någonsin kämpar med att komma ihåg den exakta syntaxen för en invecklad sökning, en IF-sats för åldersanalys, eller en komplex avskrivningsberäkning, kan verktyg som ExcelGPT hjälpa till. Beskriv bara ditt behov på vanligt språk – som "beräkna den linjära avskrivningen för en tillgång över 5 år och ignorera restvärdet" – och få den exakta, fungerande formeln direkt.

Genom att kombinera en stark grundläggande kunskap om Excel-strukturen med modern AI-hjälp kan du bygga pålitliga, felfria bokföringsmallar på en bråkdel av tiden.

Vanliga frågor

Hur skyddar jag mina bokföringsformler från att raderas av misstag?

Du kan skydda dina mallar genom att använda Excels funktion "Skydda blad". Först markerar du de celler där datainmatning är tillåten (som transaktionsdetaljerna), högerklickar, väljer Formatera celler, går till fliken Skydd och avmarkerar "Låst". Gå sedan till fliken Granska i menyfliksområdet och klicka på "Skydda blad". Dina formler kommer att låsas, men användarna kan fortfarande mata in data.

Kan Excel helt ersätta dedikerade bokföringsprogram som QuickBooks?

Även om ett mycket litet eller helt nystartat företag kan använda Excel för att hålla koll på grundläggande intäkter och utgifter, rekommenderas det inte som en permanent ersättning för dedikerade bokföringsprogram. Dedikerad programvara säkerställer att reglerna för dubbel bokföring följs strikt, upprätthåller rigorösa revisionsspår och hanterar komplex skatterapportering inbyggt. Excel används bäst som ett analytiskt och rapporterande komplement till ditt huvudsakliga bokföringssystem.

Vad är det bästa sättet att sammanfatta stora volymer data från huvudboken?

Pivottabeller är det mest effektiva sättet att sammanfatta tusentals rader med huvudboksdata. Genom att infoga en pivottabell kan du dra "Kontonamn" till fältet Rader, "Datum" (grupperat per månad) till fältet Kolumner och "Belopp" till fältet Värden för att omedelbart generera en korstabulerad ekonomisk sammanställning utan att skriva en enda formel.

Hur hittar jag dubbla transaktionsposter i min huvudbok?

Det snabbaste sättet är att använda Villkorsstyrd formatering. Markera kolumnen som innehåller dina transaktionsreferenser (som checknummer eller faktura-ID), gå till fliken Start, klicka på Villkorsstyrd formatering, markera Regler för markering av celler och välj "Dubblettvärden". Excel kommer omedelbart att markera alla transaktioner som har matats in mer än en gång.