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

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

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

Χρήση επικυρώσεων δεδομένων για τη μοντελοποίηση σεναρίων κερδοφορίας

Λήψη αρχείου 0801.xlsx . Ανοίξτε το και επιλέξτε την καρτέλα με την ένδειξη 8-1-start.

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

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

Πάρτε το μοντέλο που έχετε λάβει και κόψτε και επικολλήστε τις περιγραφές από τη στήλη C στη στήλη F. Μπορείτε να το κάνετε επισημαίνοντας τα κελιά C6:C8, πατώντας Ctrl+X, επιλέγοντας το κελί F6 και πατώντας Enter.

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

Αντιγράψτε το εύρος της στήλης Β στις στήλες C, D και E.

Μπορείτε να το κάνετε αυτό επισημαίνοντας B3:B8, πατώντας Ctrl+C, επιλέγοντας κελιά C3:E3 και πατώντας Enter. Αυτά τα ποσά θα είναι τα ίδια για κάθε σενάριο μέχρι να τα αλλάξετε.

Στη σειρά 2 πληκτρολογήστε τους τίτλους καλύτερη περίπτωση , βασική περίπτωση , και χειρότερη περίπτωση.

Ρύθμιση του μοντέλου για ανάλυση σεναρίων.

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

Επεξεργαστείτε τις εισόδους κάτω από κάθε σενάριο.

Μπορείτε να βάλετε ό,τι πιστεύετε ότι είναι πιθανό, αλλά για να ταιριάξετε τους αριθμούς με αυτούς σε αυτό το παράδειγμα, εισαγάγετε τις τιμές. Αγνοήστε τη στήλη Β προς το παρόν.

Είσοδοι για ανάλυση σεναρίου.

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

Στο κελί E1, εισαγάγετε τον τίτλο Σενάριο .

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

Ο ευκολότερος τρόπος για να το κάνετε αυτό είναι να ακολουθήσετε αυτά τα βήματα:

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

Πατήστε το εικονίδιο Format Painter στην ενότητα Πρόχειρο στην αριστερή πλευρά της καρτέλας Home. Ο κέρσορας σας θα αλλάξει σε πινέλο.

Επιλέξτε το κελί F1 για να επικολλήσετε τη μορφοποίηση.

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

Τώρα, στο κελί F1, επιλέξτε Επικύρωση δεδομένων από την ενότητα Εργαλεία δεδομένων της καρτέλας Δεδομένα.

Εμφανίζεται το πλαίσιο διαλόγου Επικύρωση δεδομένων.

Στην καρτέλα Ρυθμίσεις, αλλάξτε το αναπτυσσόμενο μενού Να επιτρέπεται σε Λίστα, χρησιμοποιήστε το ποντίκι για να επιλέξετε την περιοχή =$C$2:$E$2 και κάντε κλικ στο OK.

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

Κάντε κλικ στο αναπτυσσόμενο πλαίσιο, το οποίο εμφανίζεται τώρα δίπλα στο κελί F1, και επιλέξτε ένα από τα σενάρια (για παράδειγμα, Base Case).

Εφαρμογή τύπων σε σενάρια

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

Επιλέξτε τα κελιά B3:B8 και επιλέξτε το Χρώμα γεμίσματος από την ομάδα Γραμματοσειρά στην καρτέλα Αρχική σελίδα.

Αλλάξτε το χρώμα γεμίσματος σε λευκό φόντο.

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

Τώρα πρέπει να αντικαταστήσετε τις κωδικοποιημένες τιμές στη στήλη Β με τύπους που θα αλλάξουν καθώς αλλάζει το αναπτυσσόμενο πλαίσιο. Μπορείτε να το κάνετε αυτό χρησιμοποιώντας μια σειρά από διαφορετικές λειτουργίες. Ένα HLOOKUP, μια ένθετη εντολή IF, ένα IFS και ένα SUMIF θα κάνουν το κόλπο. Προσθέστε τους τύπους ακολουθώντας αυτά τα βήματα:

Επιλέξτε το κελί B3 και προσθέστε έναν τύπο που θα αλλάξει την τιμή ανάλογα με το κελί F1.

Εδώ είναι ποιος θα είναι ο τύπος στις διάφορες επιλογές:

• =HLOOKUP($F$1,$C$2:$E$8,2,0)

  Σημειώστε ότι με αυτήν τη λύση, πρέπει να αλλάξετε τον αριθμό ευρετηρίου σειράς από 2 σε 3 και ούτω καθεξής καθώς αντιγράφετε τον τύπο προς τα κάτω. Αντίθετα, θα μπορούσατε να χρησιμοποιήσετε μια συνάρτηση ROW στο τρίτο πεδίο ως εξής: =HLOOKUP($F$1,$C$2:$E$8,ROW(A3)-1,0)

• =IF($F$1=$C$2,C3,IF($F$1=$D$2,D3,E3))
• =IFS($F$1=$C$2,C3,$F$1=$D$2,D3,$F$1=$E$2,E3)
• =SUMIF($C$2:$E$2,$F$1,C3:E3)

  Όπως πάντα, υπάρχουν πολλές διαφορετικές επιλογές για να διαλέξετε και η καλύτερη λύση είναι αυτή που είναι η πιο απλή και κατανοητή. Οποιαδήποτε από αυτές τις λειτουργίες θα παράγει ακριβώς το ίδιο αποτέλεσμα, αλλά η αλλαγή του αριθμού ευρετηρίου σειράς στο HLOOKUP δεν είναι ισχυρή και η προσθήκη της ROW μπορεί να προκαλέσει σύγχυση για έναν χρήστη. Η ένθετη εντολή IF είναι δύσκολο να δημιουργηθεί και να ακολουθηθεί, και παρόλο που η νέα συνάρτηση IFS έχει σχεδιαστεί για να κάνει μια ένθετη συνάρτηση IF απλούστερη, εξακολουθεί να είναι μάλλον δυσκίνητη. Το SUMIF είναι αρκετά απλό στην κατασκευή και την παρακολούθηση, και είναι εύκολο να επεκταθεί εάν χρειαστεί να προσθέσετε επιπλέον σενάρια στο μέλλον.

  Λάβετε υπόψη ότι το IFS είναι μια νέα συνάρτηση που είναι διαθέσιμη μόνο με εγκατεστημένο το Office 365 και το Excel 2016 ή νεότερη έκδοση. Εάν χρησιμοποιείτε αυτήν τη συνάρτηση και κάποιος ανοίξει αυτό το μοντέλο σε προηγούμενη έκδοση του Excel, μπορεί να δει τον τύπο, αλλά δεν θα μπορεί να τον επεξεργαστεί.

Αντιγράψτε τον τύπο στο κελί B3 κάτω από τη στήλη.

Η ολοκληρωμένη ανάλυση σεναρίου.

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

Τώρα για το διασκεδαστικό κομμάτι! Ήρθε η ώρα να δοκιμάσετε τη λειτουργικότητα του σεναρίου στο μοντέλο.

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