Hvordan bygge inn feilkontroller i Excel-finansmodellen

Selv om du nettopp har begynt å modellere, er du sannsynligvis godt klar over hvor lett det er å gjøre en feil i en økonomisk modell! Det er tre måter å forhindre feil i en økonomisk modell:

• Unngå å gjøre feilene i utgangspunktet. I denne boken beskriver jeg flere teknikker du kan bruke for å unngå å gjøre feil i utgangspunktet, for eksempel å være i samsvar med formlene dine.
• Sjekk modellen for feil. Til tross for din beste innsats, kan feil uunngåelig slippe gjennom, så sjekk, dobbeltsjekk, og få noen andre til å sjekke modellen din etter at den er ferdig.
• Inkluder feilkontroller. Mens du bygger modellen, ta med feilkontroller som forhindrer at utilsiktede feil sklir inn i modellen på grunn av feil oppføringer eller brukerfeil.

Informasjonen her fokuserer på de to første punktene: teknikker for modellbygging for å redusere feil, samt måter å sjekke modellen for feil.

Feilsjekker er en kritisk del av en godt bygget finansiell modell slik at brukeren eller modellbyggeren med et øyeblikk kan se om formlene beregner riktig. For eksempel, når du oppretter ledelsesrapporter, sjekk at summen av hver enkelt avdelings rapport legger seg til den bedriftsomfattende totalen. Dette kan gjøres ved å sette inn en enkel IF-funksjon, blant andre metoder.

I eksemplet som vises her, er det bygget et kapitalbudsjett med estimerte forbruksdatoer i kolonne E. I kapitalforbruksplanen vist i kolonnene F til Q, blir forbruket spredt over hele året. Modellbyggeren vet at det totale investeringsbeløpet på $124 700 vist i celle D17 bør være det samme som det totale kapitalbeløpet vist i celle R17, og hvis de to beløpene ikke er lik hverandre, beregner ikke modellen riktig. Så feilkontrollcellen E1 inneholder den veldig enkle formelen =R17-D17.

En enkel feilsjekk.

Nedenfor kan du se at en bruker har skrevet inn feil verdi i celle E4. 20. februar er ikke en gyldig oppføring fordi kapitalforbruksplanen kun tillater datoer i løpet av 2019. Dette betyr at den økonomiske modellen nedenfor er feil - brukeren har lagt inn anleggsutgifter verdt 124 700 USD i modellen, men bare 115 700 USD har blitt allokert over året. Tallet som vises i celle E1 (9 000) varsler brukeren om at det er et problem. Last ned fil 0401.xlsx og velg fanene merket 4-9 og 4-10 for å prøve å utløse denne feilkontrollen selv.

En feilsjekk utløst.

Denne feilkontrollen er veldig enkel og ganske diskret. For å gjøre det mer åpenbart, kan du foretrekke å inkludere beskrivelsen "feilsjekk" ved siden av feilkontrollcellen i celle D1, noe som vil gjøre det mer åpenbart for brukeren hva som har skjedd når feilkontrollen utløses.

Feilkontrollen vist ovenfor er en foretrukket metode for feilkontroll, fordi den er så enkel og rask å bygge. Fordi den returnerer en verdi i tilfelle en feil, kan den være litt for diskret for din smak – den varsler ikke nødvendigvis brukeren umiddelbart om at en feil har blitt gjort. Imidlertid er det absolutt raskt og enkelt å følge, og av denne grunn er det en ganske vanlig feilsjekk som favoriseres av mange modellbyggere.

Hvis du bruker denne typen feilkontroll, formater den ved å bruke kommastilen (finnes på Hjem-fanen i Numbers-gruppen) og fjern desimalen og formater den med en rød skrift. På denne måten vil ikke nullen vise hvis det ikke er noen feil, og et rødt tall vil vise hvis det er en feil.

Alternativt kan du foretrekke den andre feilkontrollformelen som =D17=R17, som vil returnere verdien TRUE hvis de er like eller FALSE hvis de ikke er det. Denne metoden er imidlertid også gjenstand for en falsk feil.

Tillater toleranse for feil

=IF(D17<>R17,”error”,0) er en overlegen feilsjekk, men nå og da kan den returnere et falskt feilresultat, selv om verdiene er de samme. Denne "feilen" i en feilsjekk er forårsaket av det faktum at Excel utfører beregninger med 14 desimaler. Etter det avkorter den verdien og kan forårsake en liten avvik, som vil rapportere en feil når den bare er 0,00000000000001 av.

For å unngå dette potensielle problemet kan du bruke en absoluttverdiformel, som vil tillate en toleranse for feil. =HVIS(ABS(D17-R17)>1,"feil",0) vil tillate at verdiene er av $1 før den rapporterer en feil. Hvis du bruker ABS-funksjonen i Excel, vil denne ta den absolutte verdien av resultatet, så det spiller ingen rolle om det er et positivt eller negativt tall.

Det er mange varianter av denne formelen. Noen modellbyggere foretrekker å vise ordet OK hvis tallene er riktige, og sjekke om de ikke er det.

Bruker betinget formatering på en feilsjekk

For å gjøre feilkontrollen enda mer fremtredende for brukeren, bør du vurdere å bruke betinget formatering for å legge til en regel som får hele cellen til å bli rød hvis feilkontrollen har blitt utløst. På Hjem-fanen på båndet, i Stiler-gruppen, klikk på knappen Betinget formatering. Hold så musepekeren over Marker Cells Rule og velg Lik.

Bruker betinget formatering på en feilsjekk.

Når dialogboksen Lik med vises, skriver du inn ordfeilen i boksen Formater celler som er LIK MED og klikker OK. Som standard vil den slå cellen til Lys rød fyll med mørk rød tekst, men du kan endre dette i rullegardinboksen.

Redigere den betingede formateringsfargen.

Betinget formatering er populært i feilkontroller fordi det gjør at feilkontrollen skiller seg mer ut når den har blitt utløst. Og med litt flaks vil brukeren innse at det er en feil i den økonomiske modellen. Betinget formatering er ikke begrenset til feilkontroller – det kan være nyttig for andre modeller der du ønsker å trekke brukerens oppmerksomhet til unormale eller høydepunkter i beregningene.