Ανάλυση ευαισθησίας στο Excel (Δείγμα φύλλου δεδομένων)

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

Λήψη πολλαπλών αποτελεσμάτων με έναν πίνακα δεδομένων

Οι δυνατότητες των φύλλων δεδομένων είναι στοιχεία της ανάλυσης what-if—που συχνά γίνεται μέσω του Microsoft Excel. Αυτό είναι το δεύτερο όνομα για την ανάλυση ευαισθησίας.

Επισκόπηση

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

Βασικά στοιχεία σχετικά με τους πίνακες δεδομένων

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

Οι στατιστικολόγοι χρησιμοποιούν έναν ενιαίο πίνακα μεταβλητών όταν υπάρχει μόνο μία μεταβλητή σε μία ή περισσότερες εκφράσεις που μπορεί να αλλάξει το αποτέλεσμά τους. Για παράδειγμα, χρησιμοποιείται συχνά σε συνδυασμό με τη συνάρτηση PMT. Ο τύπος έχει σχεδιαστεί για τον υπολογισμό του ποσού της τακτικής πληρωμής και λαμβάνει υπόψη το επιτόκιο που καθορίζεται στη συμφωνία. Σε τέτοιους υπολογισμούς, οι μεταβλητές γράφονται σε μια στήλη και τα αποτελέσματα των υπολογισμών σε μια άλλη. Ένα παράδειγμα πινακίδας δεδομένων με 1 μεταβλητή:

Στη συνέχεια, εξετάστε τις πλάκες με 2 μεταβλητές. Χρησιμοποιούνται σε περιπτώσεις όπου δύο παράγοντες επηρεάζουν την αλλαγή σε οποιονδήποτε δείκτη. Οι δύο μεταβλητές μπορεί να καταλήξουν σε έναν άλλο πίνακα που σχετίζεται με το δάνειο, ο οποίος μπορεί να χρησιμοποιηθεί για τον προσδιορισμό της βέλτιστης περιόδου αποπληρωμής και του ποσού της μηνιαίας πληρωμής. Σε αυτόν τον υπολογισμό, πρέπει επίσης να χρησιμοποιήσετε τη συνάρτηση PMT. Παράδειγμα πίνακα με 2 μεταβλητές:

Δημιουργία πίνακα δεδομένων με μία μεταβλητή

Εξετάστε τη μέθοδο ανάλυσης χρησιμοποιώντας το παράδειγμα ενός μικρού βιβλιοπωλείου με μόνο 100 βιβλία σε απόθεμα. Μερικά από αυτά μπορούν να πωληθούν πιο ακριβά (50 $), τα υπόλοιπα θα κοστίσουν λιγότερο στους αγοραστές (20 $). Υπολογίζεται το συνολικό εισόδημα από την πώληση όλων των αγαθών – ο ιδιοκτήτης αποφάσισε ότι θα πουλούσε το 60% των βιβλίων σε υψηλή τιμή. Πρέπει να μάθετε πώς θα αυξηθούν τα έσοδα εάν αυξήσετε την τιμή ενός μεγαλύτερου όγκου αγαθών – 70% και ούτω καθεξής.

Δώσε προσοχή! Τα συνολικά έσοδα πρέπει να υπολογίζονται χρησιμοποιώντας έναν τύπο, διαφορετικά δεν θα είναι δυνατή η σύνταξη πίνακα δεδομένων.

1. Επιλέξτε ένα ελεύθερο κελί μακριά από την άκρη του φύλλου και γράψτε τον τύπο σε αυτό: =Κυψέλη συνολικών εσόδων. Για παράδειγμα, εάν το εισόδημα είναι γραμμένο στο κελί C14 (υποδεικνύεται η τυχαία ονομασία), πρέπει να γράψετε αυτό: =S14.
2. Γράφουμε το ποσοστό του όγκου των αγαθών στη στήλη στα αριστερά αυτού του κελιού – όχι κάτω από αυτό, αυτό είναι πολύ σημαντικό.
3. Επιλέγουμε το εύρος των κελιών όπου βρίσκεται η στήλη ποσοστού και ο σύνδεσμος με το συνολικό εισόδημα.

4. Βρίσκουμε στην καρτέλα «Δεδομένα» το στοιχείο «Τι γίνεται αν ανάλυση» και κάνουμε κλικ σε αυτό – στο μενού που ανοίγει, επιλέγουμε την επιλογή «Πίνακας δεδομένων».

5. Θα ανοίξει ένα μικρό παράθυρο όπου πρέπει να καθορίσετε ένα κελί με το ποσοστό των βιβλίων που πουλήθηκαν αρχικά σε υψηλή τιμή στη στήλη "Αντικατάσταση τιμών ανά γραμμές σε ...". Αυτό το βήμα γίνεται για να επανυπολογιστεί το σύνολο των εσόδων λαμβάνοντας υπόψη το αυξανόμενο ποσοστό.

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

Προσθήκη τύπου σε πίνακα δεδομένων μεμονωμένης μεταβλητής

Από έναν πίνακα που βοήθησε στον υπολογισμό μιας ενέργειας με μία μόνο μεταβλητή, μπορείτε να δημιουργήσετε ένα εξελιγμένο εργαλείο ανάλυσης προσθέτοντας έναν επιπλέον τύπο. Πρέπει να εισαχθεί δίπλα σε έναν ήδη υπάρχοντα τύπο – για παράδειγμα, εάν ο πίνακας είναι προσανατολισμένος στη γραμμή, εισάγουμε την έκφραση στο κελί στα δεξιά του υπάρχοντος. Όταν οριστεί ο προσανατολισμός της στήλης, γράφουμε τον νέο τύπο κάτω από τον παλιό. Στη συνέχεια, ακολουθήστε τον αλγόριθμο:

1. Επιλέξτε ξανά το εύρος των κελιών, αλλά τώρα θα πρέπει να περιλαμβάνει τον νέο τύπο.
2. Ανοίξτε το μενού ανάλυσης "what if" και επιλέξτε "Datasheet".
3. Προσθέτουμε έναν νέο τύπο στο αντίστοιχο πεδίο σε γραμμές ή στήλες, ανάλογα με τον προσανατολισμό της πλάκας.

Δημιουργήστε έναν πίνακα δεδομένων με δύο μεταβλητές

Η αρχή ενός τέτοιου πίνακα είναι ελαφρώς διαφορετική – πρέπει να βάλετε έναν σύνδεσμο με τα συνολικά έσοδα πάνω από τις ποσοστιαίες τιμές. Στη συνέχεια, εκτελούμε τα εξής βήματα:

1. Γράψτε τις επιλογές τιμών σε μία γραμμή με έναν σύνδεσμο προς το εισόδημα – ένα κελί για κάθε τιμή.
2. Επιλέξτε μια περιοχή κελιών.

3. Ανοίξτε το παράθυρο του πίνακα δεδομένων, όπως όταν συντάσσετε έναν πίνακα με μία μεταβλητή – μέσω της καρτέλας «Δεδομένα» στη γραμμή εργαλείων.
4. Αντικαταστήστε στη στήλη "Αντικατάσταση τιμών κατά στήλες σε ..." ένα κελί με αρχική υψηλή τιμή.
5. Προσθέστε ένα κελί με το αρχικό ποσοστό πωλήσεων ακριβών βιβλίων στη στήλη "Αντικατάσταση τιμών ανά γραμμές σε ..." και κάντε κλικ στο "OK".

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

Επιταχύνετε τους υπολογισμούς για φύλλα εργασίας που περιέχουν πίνακες δεδομένων

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

1. Ανοίξτε το παράθυρο επιλογών, επιλέξτε το στοιχείο "Τύπες" στο μενού στα δεξιά.
2. Επιλέξτε το στοιχείο "Αυτόματα, εκτός από πίνακες δεδομένων" στην ενότητα "Υπολογισμοί στο βιβλίο εργασίας".

3. Ας υπολογίσουμε ξανά τα αποτελέσματα στον πίνακα χειροκίνητα. Για να το κάνετε αυτό, επιλέξτε τους τύπους και πατήστε το πλήκτρο F.

Άλλα εργαλεία για την εκτέλεση ανάλυσης ευαισθησίας

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

1. Η συνάρτηση "Επιλογή παραμέτρων" είναι κατάλληλη εάν είναι γνωστό το επιθυμητό αποτέλεσμα και πρέπει να γνωρίζετε την τιμή εισόδου της μεταβλητής για να λάβετε ένα τέτοιο αποτέλεσμα.
2. Η "Αναζήτηση λύσης" είναι ένα πρόσθετο για την επίλυση προβλημάτων. Είναι απαραίτητο να θέσετε όρια και να τα υποδείξετε, μετά από τα οποία το σύστημα θα βρει την απάντηση. Η λύση προσδιορίζεται αλλάζοντας τις τιμές.
3. Η ανάλυση ευαισθησίας μπορεί να γίνει χρησιμοποιώντας το Scenario Manager. Αυτό το εργαλείο βρίσκεται στο μενού ανάλυσης what-if στην καρτέλα Δεδομένα. Αντικαθιστά τις τιμές σε πολλά κελιά – ο αριθμός μπορεί να φτάσει τα 32. Ο διεκπεραιωτής συγκρίνει αυτές τις τιμές έτσι ώστε ο χρήστης να μην χρειάζεται να τις αλλάξει χειροκίνητα. Ένα παράδειγμα χρήσης του διαχειριστή σεναρίων:

Ανάλυση ευαισθησίας επενδυτικού έργου στο Excel

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

Μέθοδος Ανάλυσης Επενδυτικής Ευαισθησίας

Όταν αναλύετε το «τι θα γινόταν αν» χρησιμοποιήστε απαρίθμηση - χειροκίνητη ή αυτόματη. Το εύρος των τιμών είναι γνωστό και αντικαθίστανται στον τύπο μία προς μία. Το αποτέλεσμα είναι ένα σύνολο τιμών. Επιλέξτε τον κατάλληλο αριθμό από αυτά. Ας εξετάσουμε τέσσερις δείκτες για τους οποίους πραγματοποιείται ανάλυση ευαισθησίας στον τομέα των οικονομικών:

1. Καθαρή παρούσα αξία – Υπολογίζεται αφαιρώντας το ποσό της επένδυσης από το ποσό των εσόδων.
2. Εσωτερικός συντελεστής απόδοσης / κέρδους – υποδεικνύει πόσο κέρδος απαιτείται να ληφθεί από μια επένδυση σε ένα έτος.
3. Ο λόγος απόσβεσης είναι ο λόγος όλων των κερδών προς την αρχική επένδυση.
4. Δείκτης προεξοφλημένου κέρδους – δείχνει την αποτελεσματικότητα της επένδυσης.

Τύπος

Η ευαισθησία ενσωμάτωσης μπορεί να υπολογιστεί χρησιμοποιώντας αυτόν τον τύπο: Αλλαγή παραμέτρου εξόδου σε % / Αλλαγή παραμέτρου εισόδου σε %.

Οι παράμετροι εξόδου και εισόδου μπορεί να είναι οι τιμές που περιγράφηκαν προηγουμένως.

1. Πρέπει να γνωρίζετε το αποτέλεσμα υπό τυπικές συνθήκες.
2. Αντικαθιστούμε μία από τις μεταβλητές και παρακολουθούμε τις αλλαγές στο αποτέλεσμα.
3. Υπολογίζουμε την ποσοστιαία μεταβολή και των δύο παραμέτρων σε σχέση με τις καθορισμένες συνθήκες.
4. Εισάγουμε τα λαμβανόμενα ποσοστά στον τύπο και προσδιορίζουμε την ευαισθησία.

Ένα παράδειγμα ανάλυσης ευαισθησίας ενός επενδυτικού έργου στο Excel

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

1. Συμπληρώστε τον πίνακα για να αναλύσετε το έργο σε αυτόν.

2. Υπολογίζουμε τις ταμειακές ροές χρησιμοποιώντας τη συνάρτηση OFFSET. Στο αρχικό στάδιο, η ροή είναι ίση με τις επενδύσεις. Στη συνέχεια, εφαρμόζουμε τον τύπο: =IF(OFFSET(Αριθμός,1;)=2;SUM(Εισαγωγή 1:Εξαγωγή 1); SUM(Εισαγωγή 1:Εκροή 1)+$B$ 5)
   Οι ονομασίες κελιών στον τύπο μπορεί να διαφέρουν, ανάλογα με τη διάταξη του πίνακα. Στο τέλος, προστίθεται η τιμή από τα αρχικά δεδομένα – η τιμή διάσωσης.

3. Καθορίζουμε την περίοδο για την οποία θα αποδώσει το έργο. Για την αρχική περίοδο, χρησιμοποιούμε αυτόν τον τύπο: =ΣΥΝΟΠΤΙΚΑ(G7: Γ17;»<0″). Το εύρος κελιών είναι η στήλη ταμειακών ροών. Για περαιτέρω περιόδους, εφαρμόζουμε αυτόν τον τύπο: =Αρχική περίοδος+IF(First e.stream>0; First e.stream;0). Το έργο βρίσκεται στο νεκρό σημείο σε 4 χρόνια.

4. Δημιουργούμε μια στήλη για τους αριθμούς εκείνων των περιόδων που το έργο αποδίδει.

5. Υπολογίζουμε την απόδοση της επένδυσης. Είναι απαραίτητο να γίνει μια έκφραση όπου το κέρδος σε μια συγκεκριμένη χρονική περίοδο διαιρείται με την αρχική επένδυση.

6. Καθορίζουμε τον παράγοντα έκπτωσης χρησιμοποιώντας αυτόν τον τύπο: =1/(1+Δίσκος.%) ^ Αριθμός.

7. Υπολογίζουμε την παρούσα αξία χρησιμοποιώντας πολλαπλασιασμό – η ταμειακή ροή πολλαπλασιάζεται με τον συντελεστή έκπτωσης.

8. Ας υπολογίσουμε το PI (δείκτης κερδοφορίας). Η παρούσα αξία διαχρονικά διαιρείται με την επένδυση κατά την έναρξη του έργου.

9. Ας ορίσουμε τον εσωτερικό ρυθμό απόδοσης χρησιμοποιώντας τη συνάρτηση IRR: =IRR(Εύρος ταμειακών ροών).

Ανάλυση ευαισθησίας επένδυσης με χρήση φύλλου δεδομένων

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

Ανάλυση παραγόντων και διασποράς στο Excel με αυτοματισμό υπολογισμών

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

ANOVA στο Excel

Ο σκοπός μιας τέτοιας ανάλυσης είναι να χωρίσει τη μεταβλητότητα της τιμής σε τρία στοιχεία:

1. Μεταβλητότητα ως αποτέλεσμα της επιρροής άλλων τιμών.
2. Αλλαγές λόγω της σχέσης των τιμών που το επηρεάζουν.
3. Τυχαίες αλλαγές.

Ας εκτελέσουμε ανάλυση διακύμανσης μέσω του πρόσθετου Excel "Ανάλυση δεδομένων". Εάν δεν είναι ενεργοποιημένο, μπορεί να ενεργοποιηθεί στις ρυθμίσεις.

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

1. Βρείτε το εργαλείο ανάλυσης δεδομένων στην καρτέλα Δεδομένα και ανοίξτε το παράθυρό του. Στη λίστα, πρέπει να επιλέξετε μονόδρομη ανάλυση διακύμανσης.

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

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

Ανάλυση παραγόντων στο Excel: ένα παράδειγμα

Ας αναλύσουμε τη σχέση των δεδομένων στον τομέα των πωλήσεων – είναι απαραίτητο να εντοπίσουμε δημοφιλή και μη δημοφιλή προϊόντα. Αρχικές πληροφορίες:

1. Πρέπει να μάθουμε για ποια αγαθά η ζήτηση αυξήθηκε περισσότερο τον δεύτερο μήνα. Καταρτίζουμε έναν νέο πίνακα για να προσδιορίσουμε την ανάπτυξη και τη μείωση της ζήτησης. Η ανάπτυξη υπολογίζεται με αυτόν τον τύπο: =IF((Demand 2-Demand 1)>0; Demand 2- Demand 1;0). Τύπος μείωσης: =IF(Ανάπτυξη=0; Ζήτηση 1- Ζήτηση 2;0).

2. Υπολογίστε την αύξηση της ζήτησης για αγαθά ως ποσοστό: =IF(Ανάπτυξη/Αποτέλεσμα 2 =0; Μείωση/Αποτέλεσμα 2; Ανάπτυξη/Αποτέλεσμα 2).

3. Ας φτιάξουμε ένα γράφημα για λόγους σαφήνειας – επιλέξτε μια περιοχή κελιών και δημιουργήστε ένα ιστόγραμμα μέσω της καρτέλας «Εισαγωγή». Στις ρυθμίσεις, πρέπει να αφαιρέσετε το γέμισμα, αυτό μπορεί να γίνει μέσω του εργαλείου Format Data Series.

Αμφίδρομη ανάλυση διασποράς στο Excel

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

1. Ανοίγουμε την "Ανάλυση δεδομένων", στη λίστα πρέπει να βρείτε μια αμφίδρομη ανάλυση διακύμανσης χωρίς επαναλήψεις.
2. Διάστημα εισαγωγής – κελιά που περιέχουν δεδομένα (χωρίς κεφαλίδα). Εμφανίζουμε τα αποτελέσματα σε ένα νέο φύλλο και κάνουμε κλικ στο "OK".

Η τιμή F είναι μεγαλύτερη από την F-κρίσιμη, πράγμα που σημαίνει ότι το πάτωμα επηρεάζει την ταχύτητα αντίδρασης στον ήχο.

Συμπέρασμα

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