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

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

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

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

Λήψη αρχείου 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 εισάγετε τους τίτλους Best Case
, Θήκη βάσης , και Χειρότερη περίπτωση. Ρύθμιση του μοντέλου για ανάλυση σεναρίων.
Σημειώστε ότι οι τύποι εξακολουθούν να συνδέονται με τις εισόδους στη στήλη B, όπως μπορείτε να δείτε επιλέγοντας το κελί C12 και πατώντας το πλήκτρο συντόμευσης F2.
Επεξεργαστείτε τις εισόδους κάτω από κάθε σενάριο.
Μπορείτε να τοποθετήσετε ό, τι νομίζετε ότι είναι πιθανό, αλλά για να αντιστοιχίσετε τους αριθμούς με εκείνους του παραδείγματος αυτού, εισαγάγετε τις τιμές. Αγνοήστε τη στήλη Β για τώρα.

Είσοδοι για ανάλυση σεναρίων.
Τώρα πρέπει να προσθέσετε το αναπτυσσόμενο πλαίσιο στην κορυφή, το οποίο θα οδηγήσει τα σενάρια σας. Δεν έχει σημασία πού ακριβώς βάζετε το αναπτυσσόμενο πλαίσιο, αλλά θα πρέπει να βρίσκεται σε μια τοποθεσία που είναι εύκολο να βρεθεί, συνήθως στο πάνω μέρος της σελίδας.
Στο κελί E1, εισάγετε τον τίτλο Σενάριο
. Επιλέξτε το κελί F1 και αλλάξτε τη μορφοποίηση στην είσοδο, ώστε ο χρήστης να μπορεί να δει ότι αυτό το κελί είναι επεξεργάσιμο.
Ο πιο εύκολος τρόπος για να γίνει αυτό είναι να ακολουθήσετε αυτά τα βήματα:
Κάντε κλικ σε ένα από τα κελιά που είναι ήδη μορφοποιημένα ως είσοδος, όπως το κελί E3.
1. Πατήστε το εικονίδιο Format Painter στο τμήμα Πρόχειρο στην αριστερή πλευρά της καρτέλας Αρχική σελίδα. Ο δρομέας σας θα αλλάξει σε ένα πινέλο.
2. Επιλέξτε το κελί F1 για να επικολλήσετε τη μορφοποίηση.
3. Η μορφοποίηση του Ζωγράφου είναι κανονικά για μία μόνο χρήση. Αφού επιλέξετε το κελί, το πινέλο θα εξαφανιστεί από τον κέρσορα. Αν θέλετε ο Ζωγράφος μορφοποίησης να γίνει "κολλητικός" και να εφαρμοστεί σε πολλά κελιά, κάντε διπλό κλικ στο εικονίδιο όταν το επιλέξετε από την καρτέλα Αρχική σελίδα.
  Τώρα, στο κελί F1, επιλέξτε Επικύρωση δεδομένων από την ενότητα Εργαλεία δεδομένων στην καρτέλα Δεδομένα.
Εμφανίζεται το πλαίσιο διαλόγου Validation Data.
Στην καρτέλα Ρυθμίσεις, αλλάξτε το αναπτυσσόμενο μενού "Allow" στη λίστα, χρησιμοποιήστε το ποντίκι για να επιλέξετε το εύρος = $ C $ 2: $ E $ 2 και κάντε κλικ στο OK.
Δημιουργία των αναπτυσσόμενων σεναρίων επικύρωσης δεδομένων.
Κάντε κλικ στο αναπτυσσόμενο πλαίσιο, το οποίο εμφανίζεται τώρα δίπλα στο κελί F1, και επιλέξτε ένα από τα σενάρια (για παράδειγμα, υπόθεση βάσης).
Εφαρμογή τύπων σε σενάρια

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

Επιλέξτε κελιά 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) ($ 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, αλλάξτε το αναπτυσσόμενο πλαίσιο και παρακολουθήστε την αλλαγή των εξόδων του μοντέλου καθώς αλλάζετε μεταξύ των διαφορετικών σεναρίων.