Hur man bygger felkontroll i din Excel Financial Model - dummies

Även om du bara har börjat modellera är du noga 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 misstag i första hand. I denna bok beskriver jag flera tekniker som du kan använda för att undvika att göra misstag i första hand, till exempel att vara förenlig med dina formler.
• Kontrollera modellen för fel. Trots dina bästa ansträngningar kan fel oundvikligen gå igenom, så kolla, dubbelkontroll och få någon annan att kontrollera din modell när den är klar.
• Inkludera felkontroll. När du bygger modellen, inkludera felkontroll som hindrar oavsiktliga fel från att glida in i modellen på grund av felaktiga poster eller användarfel.

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

Felkontroll är en kritisk del av en välbyggd finansiell modell så att användaren eller modelleraren kan se en överblick om formlerna beräknar sig korrekt. När du till exempel skapar hanteringsrapporter kontrollerar du att summan av varje enskild avdelningens rapport lägger till hela företagets totala. Detta kan göras genom att infoga en enkel IF-funktion, bland andra metoder.

I det exempel som visas här har en kapitalbudget byggts med beräknade spenderingsdatum i kolumn E. I kapitaltilläggsschemat som visas i kolumnerna F till Q spridas utgifterna över hela året. Modelleren vet att den totala kapitaltillskottet på $ 124, 700 som visas i cell D17 ska vara densamma som den totala kapitalplaneringsmängden som visas i cell R17, och om de två mängderna inte motsvarar varandra, beräknar modellen inte korrekt. Således innehåller felkontrollen E1 den mycket enkla formeln = R17-D17.

En enkel felkontroll.

Nedan ser du att en användare har angett ett felaktigt värde i cell E4. 20 februari är inte en giltig post eftersom kapitaltilläggsschemat endast tillåter datum under 2019. Det betyder att den finansiella modellen nedan är inkorrekt - användaren har angett $ 124 700 på investeringar i modellen, men endast $ 115, 700 har fördelats över året. Numret som visas i cell E1 (9, 000) varnar användaren för att det finns ett problem. Hämta fil 0401. xlsx och välj flikarna märkta 4-9 och 4-10 för att försöka utlösa denna felkontroll själv.

En felkontroll utlöst.

Denna felkontroll är väldigt enkel och ganska diskret.För att göra det mer uppenbart kan du helst inkludera beskrivningen "felkontroll" bredvid felkontrollkällan i cell D1, vilket skulle göra det mer uppenbart för användaren vad som 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 behöver inte nödvändigtvis varna användaren omedelbart att ett fel har gjorts. Det är dock säkert snabbt och enkelt att följa, och därför är en ganska vanlig felkontroll som favoriseras av många modeller.

Om du använder denna typ av felkontroll, formatera den med kommandostilen (finns på fliken Hem i gruppen Numbers) och ta bort decimaltalet och formatera det med en röd typsnitt. På så sätt visas inte noll 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 felkontrollmetoden som = D17 = R17, som returnerar värdet SANT om de är samma eller FALSE om de inte är. Emellertid är denna metod också föremål för ett falskt fel.

Tillåten tolerans för fel

= IF (D17R17, "error", 0) är en överlägsen felkontroll, men då och då kan det returnera ett felaktigt felresultat, trots att värdena är desamma. Denna "bugg" i en felkontroll beror på att Excel kör beräkningar med 14 decimaler. Därefter avkortas värdet och kan orsaka en minutskillnad, som 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 absolutvärdesformel, vilket skulle möjliggöra en tolerans för fel. = IF (ABS (D17-R17)> 1, "error", 0) tillåter värdena att vara av med $ 1 innan det rapporterar ett fel. Om du använder ABS-funktionen i Excel, kommer det 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 variationer av denna formel. Vissa modeller föredrar att visa ordet OK om siffrorna är rätt och Kontrollera om de inte är.

Användning av villkorlig formatering till en felkontroll

Om du vill göra felkontrollen ännu mer framträdande för användaren, överväg att använda villkorad 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 Startsida i fliken, i gruppen Stilar, klickar du på knappen Villkorlig formatering. Håll sedan musen över Highlight Cells Rule och välj Equal To.

Användning av villkorlig formatering till en felkontroll.

När dialogrutan Lika till visas, skriv ordet error i rutan Format celler som är likvärdiga och klicka på OK. Som standard kommer det att vända cellen till Light Red Fill med Dark Red Text, men du kan ändra det i rullgardinsmenyn.

Redigering av den villkorliga formateringsfärgen.

Villkorlig formatering är populär vid felkontroll eftersom det gör att felkontrollen utmärker sig mer när den har blivit utlöst. Och med någon tur kommer användaren att inse att det finns ett fel i den finansiella modellen.Villkorlig formatering är inte begränsad till felkontroller. Det kan vara användbart för andra modeller där du vill dra användarens uppmärksamhet på avvikelser eller höjdpunkter i beräkningarna.