Hur man bygger in felkontroller i din Excel-ekonomimodell

Även om du precis har börjat modellera, är du förmodligen väl medveten om hur lätt det är att göra ett misstag i en finansiell modell! Det finns tre sätt att förhindra fel i en finansiell modell:

• Undvik att göra misstagen i första hand. I den här boken beskriver jag flera tekniker som du kan använda för att undvika att göra misstag i första hand, som att vara konsekvent med dina formler.
• Kontrollera modellen för fel. Trots dina bästa ansträngningar kan fel oundvikligen glida igenom, så kontrollera, dubbelkolla och låt någon annan kontrollera din modell när den är klar.
• Inkludera felkontroller. När du bygger modellen, inkludera felkontroller som förhindrar att oavsiktliga fel glider in i modellen på grund av felaktiga inmatningar eller användarfel.

Informationen här fokuserar på de två första punkterna: tekniker för modellbyggande för att minska fel, samt sätt att kontrollera modellen för fel.

Felkontroller är en kritisk del av en välbyggd finansiell modell så att användaren eller modelleraren med ett ögonkast kan se om formlerna beräknar korrekt. Till exempel, när du skapar ledningsrapporter, kontrollera att summan av varje enskild avdelnings rapport läggs till den företagsomfattande summan. Detta kan göras genom att bland annat infoga en enkel IF-funktion.

I exemplet som visas här har en kapitalbudget byggts upp med beräknade utgiftsdatum i kolumn E. I investeringsschemat som visas i kolumnerna F till Q sprids utgifterna ut över hela året. Modellören vet att det totala investeringsbeloppet på 124 700 USD som visas i cell D17 bör vara detsamma som det totala kapitalbeloppet som visas i cell R17, och om de två beloppen inte är lika med varandra, beräknas inte modellen korrekt. Så felkontrollcellen E1 innehåller den mycket enkla formeln =R17-D17.

En enkel felkontroll.

Nedan kan du se att en användare har angett ett felaktigt värde i cell E4. 20 februari är inte en giltig post eftersom investeringsschemat endast tillåter datum under 2019. Detta betyder att den finansiella modellen nedan är felaktig – användaren har angett 124 700 USD i kapitalutgifter i modellen, men endast 115 700 USD har allokerats över året. Siffran som visas i cell E1 (9 000) uppmärksammar användaren på att det finns ett problem. Ladda ner filen 0401.xlsx och välj flikarna märkta 4-9 och 4-10 för att försöka utlösa den här felkontrollen själv.

En felkontroll utlöstes.

Denna felkontroll är mycket enkel och ganska diskret. För att göra det mer uppenbart kanske du föredrar att inkludera beskrivningen "felkontroll" bredvid felkontrollcellen i cell D1, vilket skulle göra det mer uppenbart för användaren vad som har hänt när felkontrollen utlöses.

Felkontrollen som visas ovan är en föredragen metod för felkontroll, eftersom den är så enkel och snabb att bygga. Eftersom det returnerar ett värde vid ett fel, kan det vara lite för diskret för din smak – det varnar inte nödvändigtvis användaren omedelbart om att ett fel har begåtts. Men det är verkligen snabbt och enkelt att följa och av denna anledning en ganska vanlig felkontroll som gynnas av många modellbyggare.

Om du använder den här typen av felkontroll, formatera den med kommastilen (finns på fliken Hem i gruppen Tal) och ta bort decimalen och formatera den med ett rött teckensnitt. På så sätt visas inte nollan om det inte finns något fel, och ett rött nummer kommer att visa om det finns ett fel.

Alternativt kan du föredra den andra felkontrollformeln som =D17=R17, som returnerar värdet TRUE om de är samma eller FALSE om de inte är det. Men denna metod är också föremål för ett falskt fel.

Tillåter tolerans för fel

=OM(D17<>R17,”error”,0) är en överlägsen felkontroll, men då och då kan den returnera ett falskt felresultat, trots att värdena är desamma. Denna "bugg" i en felkontroll orsakas av att Excel utför beräkningar med 14 decimaler. Efter det trunkerar den värdet och kan orsaka en liten avvikelse, vilket kommer att rapportera ett fel när det bara är 0,00000000000001 av.

För att undvika detta potentiella problem kan du använda en formel för absolut värde, som tillåter en tolerans för fel. =OM(ABS(D17-R17)>1,"fel",0) kommer att tillåta värdena att vara avstängda med $1 innan det rapporterar ett fel. Om du använder ABS-funktionen i Excel kommer detta att ta det absoluta värdet av resultatet, så det spelar ingen roll om det är ett positivt eller negativt tal.

Det finns många varianter av denna formel. Vissa modellerare föredrar att visa ordet OK om siffrorna stämmer, och kontrollera om de inte är det.

Använder villkorlig formatering för en felkontroll

För att göra felkontrollen ännu mer framträdande för användaren, överväg att använda villkorlig formatering för att lägga till en regel som gör att hela cellen blir röd om felkontrollen har utlösts. På fliken Hem på menyfliksområdet, i gruppen Stilar, klicka på knappen Villkorlig formatering. Håll sedan musen över Markera cellers regel och välj Lika med.

Använder villkorlig formatering för en felkontroll.

När dialogrutan Lika med visas skriver du ordet fel i rutan Formatera celler som är LIKA MED och klickar på OK. Som standard ändras cellen till ljusröd fyllning med mörkröd text, men du kan ändra detta i rullgardinsmenyn.

Redigera den villkorliga formateringsfärgen.

Villkorlig formatering är populärt vid felkontroller eftersom det gör att felkontrollen sticker ut mer när den har utlösts. Och med lite tur kommer användaren att inse att det finns ett fel i den finansiella modellen. Villkorlig formatering är inte begränsad till felkontroller – den kan vara användbar för andra modeller där du vill uppmärksamma användaren på avvikelser eller höjdpunkter i beräkningarna.