Cum se construiesc scenarii de scadere în modelul dvs. financiar - dummies

Metoda cea mai frecvent utilizată de construire a scenariilor este folosirea unei combinații de formule și casete drop-down. În modelul financiar, creați un tabel de scenarii posibile și intrările acestora și conectați numele scenariilor la o casetă verticală de celule de intrare. Intrările modelului sunt legate de tabelul de scenarii. Dacă modelul a fost construit corespunzător cu toate intrările care trec prin ieșiri, atunci rezultatele modelului se vor schimba pe măsură ce utilizatorul selectează diferite opțiuni din caseta drop-down.

Cutiile drop-down de validare a datelor sunt utilizate pentru o serie de scopuri diferite în modelarea financiară, inclusiv analiza scenariilor.

Utilizarea validărilor de date pentru a modela scenariile de profitabilitate

Descărcați fișierul 0801. xlsx. Deschideți-l și selectați fila cu eticheta 8-1-start.

Modul în care a fost modelat, intrările sunt aliniate în coloana B. Ați putea efectua analiza de sensibilitate pur și simplu schimbând una dintre intrările - de exemplu, modificați numărul clienților per operator de apel în celula B3 de la 40 la 45 și veți vedea că toate numerele dependente se schimbă. Aceasta ar fi o analiză a sensibilității, deoarece schimbați doar o singură variabilă. În schimb, veți schimba simultan mai multe variabile în acest exercițiu complet de analiză a scenariilor, astfel încât va trebui să faceți mai mult decât să faceți mai multe modificări manual.

Luați modelul descărcat și tăiați și inserați descrierile din coloana C în coloana F. Puteți face acest lucru prin evidențierea celulelor C6: C8, apăsând Ctrl + X, selectând celula F6 și apăsând Enter.

Intrările din celulele B3 până la B8 sunt domeniile active care conduc modelul și rămân astfel. Cu toate acestea, ei trebuie să devină formule care se schimbă în funcție de caseta drop-down pe care o veți crea.
Copiați intervalul în coloana B peste coloanele C, D și E.
Puteți face acest lucru evidențiind B3: B8, apăsând pe Ctrl + C, selectând celule C3: E3 și apăsând Enter. Aceste sume vor fi aceleași pentru fiecare scenariu până când le veți schimba.
În rândul 2, introduceți titlurile Cel mai bun caz
, Caz de bază , și Cel mai rău caz. Configurarea modelului pentru analiza scenariilor.
Rețineți că formulele încă se leagă la intrările din coloana B, după cum puteți vedea selectând celula C12 și apăsând tasta de comandă rapidă F2.
Editați intrările sub fiecare scenariu.
Puteți pune ceea ce credeți că este posibil, dar pentru a potrivi numerele cu cele din acest exemplu, introduceți valorile. Ignora coloana B pentru moment.

Intrări pentru analiza scenariilor.
Acum trebuie să adăugați caseta drop-down în partea de sus, care va conduce scenariile. Nu contează exact exact unde ați pus caseta drop-down, dar ar trebui să fie într-o locație ușor de găsit, de obicei în partea de sus a paginii.
În celula E1, introduceți titlul Scenariu
. Selectați celula F1 și schimbați formatul pentru introducere astfel încât utilizatorul să poată vedea că această celulă poate fi editată.
Cea mai ușoară cale de a face acest lucru este să urmați acești pași:
Faceți clic pe una dintre celulele deja formatate ca intrări, cum ar fi celula E3.
1. Apăsați pictograma Format Painter din secțiunea Clipboard din partea stângă a filei Home. Cursorul dvs. se va schimba într-o pensulă.
2. Selectați celula F1 pentru a lipi formatul.
3. Formatul Painter este în mod normal pentru o singură utilizare. După ce ați selectat celula, pensula va dispărea de pe cursor. Dacă doriți ca Format Painter să devină "lipicios" și să se aplice la mai multe celule, faceți dublu clic pe pictograma atunci când o selectați din fila Pagina principală.
  Acum, în celula F1, selectați Validare date din secțiunea Instrumente date din fila Date.
Se afișează caseta de dialog Validare date.
În fila Setări, schimbați meniul derulant Allow din listă, utilizați mouse-ul pentru a selecta intervalul = $ C $ 2: $ E $ 2 și faceți clic pe OK.
Crearea scenariilor drop-down pentru validarea datelor.
Faceți clic pe caseta derulantă, care apare acum lângă celula F1, și selectați unul dintre scenarii (de exemplu, Cazul de bază).
Aplicarea formulelor la scenarii

Celulele din coloana B conduc încă modelul, iar acestea trebuie înlocuite cu formule. Înainte de a adăuga formule, totuși, ar trebui să modificați formatarea celulelor din interval, pentru a arăta că acestea conțin formule, în loc de numere codate greu. Urmați acești pași:

Selectați celulele B3: B8 și selectați opțiunea Fill Color din grupul Font din fila Home.

Schimbați Culoarea de umplere pe un fond alb.
Este foarte important să se facă distincția între formule și celulele de intrare într-un model. Trebuie să clarificați pentru orice utilizator care deschide modelul că celulele din acest interval conțin formule și nu ar trebui să fie suprascrise.
Acum trebuie să înlocuiți valorile codate greu în coloana B cu formule care se vor schimba odată cu modificarea casetei drop-down. Puteți face acest lucru folosind o serie de funcții diferite; un HLOOKUP, o declarație IF imbricată, un IFS și un SUMIF vor face toate trucurile. Adăugați formulele urmând acești pași:

Selectați celula B3 și adăugați o formulă care va schimba valoarea în funcție de ce se află în celula F1.

Iată care va fi formula sub diferitele opțiuni:
= HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8, 2, 0)
- Rețineți că, numărul indexului de rând de la 2 la 3 și așa mai departe pe măsură ce copiați formula în jos. În schimb, ați putea folosi o funcție ROW în al treilea câmp ca acesta: = 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, $ E $ 2, E3)
- = SUMIF ($ C $ 2: $ E $ 2, $ F $ 1, C3: E3)
- Ca întotdeauna există mai multe opțiuni diferite și cea mai bună soluție este cea care este cel mai simplu și mai ușor de înțeles.Oricare dintre aceste funcții va produce exact același rezultat, dar trebuie să schimbați numărul indexului rândului din HLOOKUP nu este robust și adăugarea rândului poate fi confuză pentru un utilizator. Instrucțiunea IF imbricată este dificil de construit și urmărită și, deși noua funcție IFS este proiectată pentru a face o funcție IF imbricată mai simplă, este încă destul de greu. SUMIF este destul de simplu de construit și de urmat și este ușor să se extindă dacă aveți nevoie să adăugați scenarii suplimentare în viitor.
  Rețineți că IFS este o funcție nouă disponibilă numai pentru Office 365 și Excel 2016 sau mai recent instalat. Dacă utilizați această funcție și cineva deschide acest model într-o versiune anterioară a Excel, poate să vizualizeze formula, dar nu va putea să o editeze.
  
  Copiați formula în celula B3 în coloană.
Analiza scenariilor finalizate.
Folosind o copie obișnuită și o lipiți, veți pierde toate formatele. Este important să păstrați formatarea modelului astfel încât să puteți vedea dintr-o privire care intrări sunt în valori, procente sau numere de clienți în dolari. Utilizați formulele de inserare pentru a păstra formatul. Puteți accesa această opțiune prin copierea celulei în clipboard, evidențiind intervalul de destinație, faceți clic dreapta și selectând pictograma Lipire formule pentru a lipi doar formulele și lăsa formatarea intactă.
Acum, pentru distracție! Este timpul să testați funcționalitatea scenariului din model.

Faceți clic pe celula F1, schimbați caseta drop-down și urmăriți modificările ieșirilor modelului pe măsură ce comutați între diferitele scenarii.