Πώς να δημιουργήσετε ελέγχους σφαλμάτων στο οικονομικό μοντέλο του Excel

Ακόμα κι αν μόλις ξεκίνησες να ασχολείσαι με το μόντελινγκ, μάλλον ξέρεις καλά πόσο εύκολο είναι να κάνεις λάθος σε ένα οικονομικό μοντέλο! Υπάρχουν τρεις τρόποι για την αποφυγή σφαλμάτων σε ένα οικονομικό μοντέλο:

• Αποφύγετε να κάνετε τα λάθη εξαρχής. Σε αυτό το βιβλίο, περιγράφω πολλές τεχνικές που μπορείτε να χρησιμοποιήσετε για να αποφύγετε να κάνετε λάθη εξαρχής, όπως το να είστε συνεπείς με τους τύπους σας.
• Ελέγξτε το μοντέλο για σφάλματα. Παρά τις καλύτερες προσπάθειές σας, τα σφάλματα μπορεί αναπόφευκτα να ξεφύγουν, γι' αυτό ελέγξτε, ελέγξτε ξανά και ζητήστε από κάποιον άλλο να ελέγξει το μοντέλο σας αφού ολοκληρωθεί.
• Συμπεριλάβετε ελέγχους σφαλμάτων. Καθώς δημιουργείτε το μοντέλο, συμπεριλάβετε ελέγχους σφαλμάτων που αποτρέπουν την ολίσθηση τυχαίων σφαλμάτων στο μοντέλο λόγω εσφαλμένων καταχωρίσεων ή σφαλμάτων χρήστη.

Οι πληροφορίες εδώ επικεντρώνονται στα δύο πρώτα σημεία: τεχνικές για τη δημιουργία μοντέλων για τη μείωση των σφαλμάτων, καθώς και τρόπους ελέγχου του μοντέλου για σφάλματα.

Οι έλεγχοι σφαλμάτων είναι ένα κρίσιμο μέρος ενός καλά δομημένου οικονομικού μοντέλου, έτσι ώστε ο χρήστης ή ο μοντελιστής να μπορεί να δει με μια ματιά εάν οι τύποι υπολογίζονται σωστά. Για παράδειγμα, όταν δημιουργείτε αναφορές διαχείρισης, ελέγξτε ότι το άθροισμα της αναφοράς κάθε μεμονωμένου τμήματος προστίθεται στο σύνολο της εταιρείας. Αυτό μπορεί να γίνει με την εισαγωγή μιας απλής συνάρτησης IF, μεταξύ άλλων μεθόδων.

Στο παράδειγμα που φαίνεται εδώ, έχει δημιουργηθεί ένας προϋπολογισμός κεφαλαίου με εκτιμώμενες ημερομηνίες δαπανών στη στήλη Ε. Στο πρόγραμμα δαπανών κεφαλαίου που εμφανίζεται στις στήλες F έως Q, η δαπάνη κατανέμεται σε ολόκληρο το έτος. Ο διαμορφωτής γνωρίζει ότι το συνολικό ποσό κεφαλαιακής δαπάνης των 124.700 $ που εμφανίζεται στο κελί D17 θα πρέπει να είναι το ίδιο με το συνολικό ποσό του προγράμματος κεφαλαίου που εμφανίζεται στο κελί R17 και εάν τα δύο ποσά δεν είναι ίσα μεταξύ τους, τότε το μοντέλο δεν υπολογίζει σωστά. Έτσι, το κελί Ε1 ελέγχου σφαλμάτων περιέχει τον πολύ απλό τύπο =R17-D17.

Ένας απλός έλεγχος σφαλμάτων.

Παρακάτω, μπορείτε να δείτε ότι ένας χρήστης έχει εισαγάγει μια εσφαλμένη τιμή στο κελί E4. Η 20η Φεβρουαρίου δεν είναι έγκυρη καταχώριση επειδή το πρόγραμμα δαπανών κεφαλαίου επιτρέπει μόνο ημερομηνίες κατά τη διάρκεια του 2019. Αυτό σημαίνει ότι το παρακάτω οικονομικό μοντέλο είναι λανθασμένο — ο χρήστης έχει εισαγάγει κεφαλαιουχικές δαπάνες αξίας 124.700 $ στο μοντέλο, αλλά μόνο 115.700 $ έχουν κατανεμηθεί σε το έτος. Ο αριθμός που εμφανίζεται στο κελί E1 (9.000) ειδοποιεί τον χρήστη για το γεγονός ότι υπάρχει πρόβλημα. Κάντε λήψη του αρχείου 0401.xlsx και επιλέξτε τις καρτέλες με τις ετικέτες 4-9 και 4-10 για να δοκιμάσετε να ενεργοποιήσετε αυτόν τον έλεγχο σφάλματος μόνοι σας.

Ενεργοποιήθηκε ένας έλεγχος σφάλματος.

Αυτός ο έλεγχος σφαλμάτων είναι πολύ απλός και αρκετά διακριτικός. Για να γίνει πιο προφανές, μπορείτε να προτιμήσετε να συμπεριλάβετε την περιγραφή "έλεγχος σφάλματος" δίπλα στο κελί ελέγχου σφάλματος στο κελί D1, κάτι που θα καθιστούσε πιο προφανές στον χρήστη τι συνέβη όταν ενεργοποιείται ο έλεγχος σφάλματος.

Ο έλεγχος σφαλμάτων που εμφανίζεται παραπάνω είναι μια προτιμώμενη μέθοδος ελέγχου σφαλμάτων, επειδή είναι τόσο απλή και γρήγορη στην κατασκευή. Επειδή επιστρέφει μια τιμή σε περίπτωση σφάλματος, μπορεί να είναι λίγο πολύ διακριτικό για τα γούστα σας — δεν ειδοποιεί απαραίτητα τον χρήστη αμέσως ότι είχε γίνει ένα σφάλμα. Ωστόσο, είναι σίγουρα γρήγορο και εύκολο να ακολουθηθεί και, για αυτόν τον λόγο, είναι ένας αρκετά κοινός έλεγχος σφαλμάτων που προτιμάται από πολλούς μοντελιστές.

Εάν χρησιμοποιείτε αυτό το είδος ελέγχου σφάλματος, μορφοποιήστε το χρησιμοποιώντας το στυλ κόμμα (βρίσκεται στην καρτέλα Αρχική σελίδα στην ομάδα Αριθμοί) και αφαιρέστε το δεκαδικό ψηφίο και μορφοποιήστε το με μια κόκκινη γραμματοσειρά. Με αυτόν τον τρόπο, το μηδέν δεν θα εμφανίζεται εάν δεν υπάρχει σφάλμα και ένας κόκκινος αριθμός θα δείχνει εάν υπάρχει σφάλμα.

Εναλλακτικά, μπορείτε να προτιμήσετε τον άλλο τύπο ελέγχου σφαλμάτων, όπως =D17=R17, ο οποίος θα επιστρέψει την τιμή TRUE εάν είναι ίδιοι ή FALSE εάν δεν είναι. Ωστόσο, αυτή η μέθοδος υπόκειται επίσης σε ψευδές σφάλμα.

Επιτρέποντας την ανοχή στο λάθος

=IF(D17<>R17,”error”,0) είναι ένας ανώτερος έλεγχος σφάλματος, αλλά κάθε τόσο μπορεί να εμφανίζει ένα ψευδές αποτέλεσμα σφάλματος, παρόλο που οι τιμές είναι οι ίδιες. Αυτό το "σφάλμα" στον έλεγχο σφαλμάτων προκαλείται από το γεγονός ότι το Excel μεταφέρει τους υπολογισμούς σε 14 δεκαδικά ψηφία. Μετά από αυτό, περικόπτει την τιμή και μπορεί να προκαλέσει μια μικρή απόκλιση, η οποία θα αναφέρει ένα σφάλμα όταν είναι μόνο 0,00000000000001.

Για να αποφύγετε αυτό το πιθανό πρόβλημα, θα μπορούσατε να χρησιμοποιήσετε έναν τύπο απόλυτης τιμής, ο οποίος θα επέτρεπε μια ανοχή για σφάλμα. =IF(ABS(D17-R17)>1,”σφάλμα”,0) θα επιτρέψει στις τιμές να είναι απενεργοποιημένες κατά 1$ πριν αναφέρει ένα σφάλμα. Εάν χρησιμοποιείτε τη συνάρτηση ABS στο Excel, θα λάβει την απόλυτη τιμή του αποτελέσματος, επομένως δεν έχει σημασία αν πρόκειται για θετικό ή αρνητικό αριθμό.

Υπάρχουν πολλές παραλλαγές αυτού του τύπου. Ορισμένοι μοντελιστές προτιμούν να εμφανίζουν τη λέξη ΟΚ εάν οι αριθμοί είναι σωστοί και Ελέγξτε εάν δεν είναι.

Εφαρμογή μορφοποίησης υπό όρους σε έλεγχο σφάλματος

Για να κάνετε τον έλεγχο σφάλματος ακόμη πιο εμφανή στον χρήστη, εξετάστε το ενδεχόμενο να χρησιμοποιήσετε μορφοποίηση υπό όρους για να προσθέσετε έναν κανόνα που κάνει ολόκληρο το κελί να γίνεται κόκκινο εάν έχει ενεργοποιηθεί ο έλεγχος σφάλματος. Στην αρχική καρτέλα της κορδέλας, στην ομάδα Στυλ, κάντε κλικ στο κουμπί Μορφοποίηση υπό όρους. Στη συνέχεια, τοποθετήστε το δείκτη του ποντικιού πάνω από τον κανόνα επισήμανσης κελιών και επιλέξτε Equal to.

Εφαρμογή μορφοποίησης υπό όρους σε έλεγχο σφάλματος.

Όταν εμφανιστεί το πλαίσιο διαλόγου Equal To, πληκτρολογήστε τη λέξη error στο πλαίσιο Format Cells that is EQUAL TO και κάντε κλικ στο OK. Από προεπιλογή, θα μετατρέψει το κελί σε Ανοιχτό κόκκινο γέμισμα με σκούρο κόκκινο κείμενο, αλλά μπορείτε να το αλλάξετε στο αναπτυσσόμενο πλαίσιο.

Επεξεργασία του χρώματος μορφοποίησης υπό όρους.

Η μορφοποίηση υπό όρους είναι δημοφιλής στους ελέγχους σφαλμάτων επειδή κάνει τον έλεγχο σφάλματος να ξεχωρίζει περισσότερο όταν έχει ενεργοποιηθεί. Και, με κάθε τύχη, ο χρήστης θα αντιληφθεί ότι υπάρχει σφάλμα στο οικονομικό μοντέλο. Η μορφοποίηση υπό όρους δεν περιορίζεται σε ελέγχους σφαλμάτων — μπορεί να είναι χρήσιμη για άλλα μοντέλα όπου θέλετε να επιστήσετε την προσοχή του χρήστη σε ανωμαλίες ή επισημάνσεις στους υπολογισμούς.