Analisi di sensibilità in Excel (Sample Datasheet)

I prucessi in u campu di a finanza sò sempre interconnessi - un fattore depende di l'altru è cambia cun ellu. Segui questi cambiamenti è capisce ciò chì aspetta in u futuru, forsi utilizendu funzioni Excel è metudi di spreadsheet.

Ottene parechji risultati cù una tabella di dati

E capacità di datasheet sò elementi di l'analisi di ciò chì-se - spessu fattu per Microsoft Excel. Questu hè u sicondu nome per l'analisi di sensibilità.

Overview

Una tavola di dati hè un tipu di gamma di cellule chì ponu esse aduprate per risolve i prublemi cambiendu i valori in alcune cellule. Hè creatu quandu hè necessariu di seguità i cambiamenti in i cumpunenti di a formula è riceve l'aghjurnamenti di i risultati, secondu questi cambiamenti. Scupritemu cumu utilizà e tabelle di dati in a ricerca, è chì tipi sò.

Fundamenti nantu à e tavule di dati

Ci sò dui tipi di tavule di dati, sò diffirenti in u numeru di cumpunenti. Avete bisognu di cumpilà una tabella cun un focusu nantu à u numeru di valori uXNUMXbuXNUMXbchì avete bisognu di verificà cun ellu.

I statistichi utilizanu una sola tavola variabile quandu ci hè solu una variabile in una o più espressioni chì ponu cambià u so risultatu. Per esempiu, hè spessu usatu in cunghjunzione cù a funzione PMT. A formula hè pensata per calculà a quantità di pagamentu regulare è piglia in contu a tarifa d'interessu specificata in l'accordu. In tali calculi, i variàbili sò scritti in una colonna, è i risultati di i calculi in un altru. Un esempiu di una piastra di dati cù 1 variabile:

In seguitu, cunzidira i platti cù 2 variàbili. Sò usati in i casi induve dui fattori influenzanu u cambiamentu in ogni indicatore. I dui variàbili ponu finisce in un altru tavulu assuciatu cù u prestitu, chì pò esse usatu per determinà u periodu di rimborsu ottimali è a quantità di u pagamentu mensuale. In questu calculu, avete ancu bisognu di utilizà a funzione PMT. Un esempiu di una tavula cù 2 variàbili:

Crea una tabella di dati cù una variabile

Cunsiderate u metudu di analisi cù l'esempiu di una piccula libreria cù solu 100 libri in stock. Unipochi di elli ponu esse venduti più caru ($ 50), u restu costarà à i cumpratori menu ($ 20). L'ingudu tutale da a vendita di tutti i beni hè calculatu - u pruprietariu hà decisu chì vende u 60% di i libri à un prezzu altu. Avete bisognu di sapè cumu l'inguernu aumenterà si aumenta u prezzu di un voluminu più grande di merchenzie - 70%, è cusì.

Prestu attenzione! L'inguernu tutale deve esse calculatu cù una formula, altri ùn serà micca pussibule di cumpilà una tabella di dati.

1. Selezziunate una cellula libera luntanu da u bordu di u fogliu è scrivite a formula in questu: = Cella di i rivenuti totali. Per esempiu, se u redditu hè scrittu in a cellula C14 (a designazione aleatoria hè indicata), avete bisognu di scrive questu: = S14.
2. Scrivemu u percentuale di u voluminu di merchenzie in a colonna à a manca di sta cellula - micca sottu, questu hè assai impurtante.
3. Selezziunamu a gamma di cellule induve si trovanu a colonna di percentuale è u ligame à l'ingudu tutale.

4. Truvemu nantu à a tabulazione "Dati" l'elementu "E se l'analisi" è cliccate nantu à questu - in u menù chì apre, selezziunate l'opzione "Tavola di dati".

5. Una piccula finestra s'aprirà induve avete bisognu di specificà una cellula cù u percentualità di libri inizialmente venduti à un prezzu altu in a colonna "Valori sustituti per fila in ...". Stu passu hè fattu per ricalculate l'ingudu tutale tenendu in contu u percentualità crescente.

Dopu à cliccà u buttone "OK" in a finestra induve i dati sò stati inseriti per a compilazione di a tavula, i risultati di i calculi appariscenu in e linee.

Aghjunghjendu una Formula à una Tavola di Dati Variabile Unicu

Da una tavula chì hà aiutatu à calculà una azzione cù una sola variàbile, pudete fà un strumentu d'analisi sofisticatu aghjunghjendu una formula addiziale. Deve esse inseritu vicinu à una formula esistente - per esempiu, se a tavula hè orientata à fila, entremu l'espressione in a cellula à a diritta di quella esistente. Quandu l'orientazione di a colonna hè stabilita, scrivemu a nova formula sottu a vechja. Dopu, seguitate l'algoritmu:

1. Selezziunate a gamma di cellule di novu, ma avà deve include a nova formula.
2. Aprite u menù di analisi "e se" è selezziunate "Datasheet".
3. Aghjunghjemu una nova formula à u campu currispundenti in fila o culonni, secondu l'orientazione di u pianu.

Crea una tabella di dati cù dui variàbili

L'iniziu di una tale tavula hè un pocu sfarente - avete bisognu di mette un ligame à l'ingudu tutale sopra i valori percentuali. In seguitu, facemu questi passi:

1. Scrivite l'opzioni di prezzu in una linea cù un ligame à l'ingressu - una cellula per ogni prezzu.
2. Selezziunate una gamma di cellule.

3. Apertura a finestra di a tabella di dati, cum'è quandu compilate una tabella cù una variabile - attraversu a tabulazione "Dati" in a barra di strumenti.
4. Sustituite in a colonna "Sustituite i valori per colonne in ..." una cellula cù un prezzu iniziale altu.
5. Aghjunghjite una cellula cù u percentuale iniziale di vendite di libri caru à a colonna "Sustituite i valori per fila in ..." è cliccate "OK".

In u risultatu, tutta a tavola hè piena di quantità di ingressu pussibule cù diverse cundizioni per a vendita di merchenzie.

Accelerate i calculi per i fogli di travagliu chì cuntenenu tabelle di dati

Sè avete bisognu di calculi rapidi in una tabella di dati chì ùn attivanu micca un recalculamentu di tuttu u libru di travagliu, ci sò parechje cose chì pudete fà per accelerà u prucessu.

1. Aprite a finestra di l'opzioni, selezziunate l'elementu "Formule" in u menù à destra.
2. Selezziunate l'elementu "Automaticu, eccettu per i tavule di dati" in a sezione "Calculazioni in u libru di travagliu".

3. Calculemu i risultati in a tavula manualmente. Per fà questu, selezziunate e formule è appughjà a chjave F.

Altri Strumenti per Eseguisce Analisi di Sensibilità

Ci sò altre strumenti in u prugramma per aiutà à realizà analisi di sensibilità. Automatizzanu alcune azzioni chì altrimenti avissiru esse fattu manualmente.

1. A funzione "Selezzione di paràmetri" hè adattata se u risultatu desideratu hè cunnisciutu, è avete bisognu di cunnosce u valore di input di a variabile per ottene un tali risultatu..
2. "Ricercà una suluzione" hè un add-on per risolve i prublemi. Hè necessariu di stabilisce limiti è puntà à elli, dopu chì u sistema hà da truvà a risposta. A suluzione hè determinata da cambià i valori.
3. L'analisi di sensibilità pò esse fatta cù u Manager di Scenariu. Stu strumentu si trova in u menù di analisi chì-se sottu a tabulazione Dati. Sustituisci i valori in parechje cellule - u numeru pò ghjunghje à 32. U dispatcher compara sti valori per chì l'utilizatore ùn hà micca bisognu di cambià manualmente. Un esempiu di usu di u script manager:

Analisi di sensibilità di un prughjettu d'investimentu in Excel

L'analisi di ciò chì si hè particularmente utile in situazioni induve a previsione hè necessaria, cum'è l'investimentu. L'analisti utilizanu stu metudu per sapè cumu u valore di l'azzioni di una cumpagnia cambiassi in u risultatu di cambiamenti in certi fatturi.

Metudu di analisi di sensibilità di l'investimentu

Quandu analizà "e se" utilizate l'enumerazione - manuale o automatica. A gamma di valori hè cunnisciutu, è sò sustituiti in a formula unu per unu. U risultatu hè un inseme di valori. Sceglie u numeru apprupriatu da elli. Cunsideremu quattru indicatori per i quali l'analisi di sensibilità hè realizata in u campu di a finanza:

1. Valore Presente Nettu - Calculatu sottraendu a quantità di l'investimentu da a quantità di l'ingudu.
2. Tassi internu di ritornu / prufittu - indica quantu prufittu hè necessariu per esse ricevutu da un investimentu in un annu.
3. U rapportu di pagamentu hè u rapportu di tutti i prufitti à l'investimentu iniziale.
4. Indici di prufittu scontu - indica l'efficacità di l'investimentu.

Formula

A sensibilità di l'incrustazione pò esse calculata cù sta formula: Cambia in u paràmetru di output in % / Cambia in u paràmetru di input in %.

I paràmetri di output è input ponu esse i valori descritti prima.

1. Avete bisognu di sapè u risultatu in cundizioni standard.
2. Sustituemu una di e variàbili è monitoremu i cambiamenti in u risultatu.
3. Calculemu a variazione percentuale di i dui paràmetri relative à e cundizioni stabilite.
4. Inseremu i percentuali ottenuti in a formula è determina a sensibilità.

Un esempiu di analisi di sensibilità di un prughjettu d'investimentu in Excel

Per una megliu comprensione di a metodulugia di l'analisi, hè necessariu un esempiu. Analizemu u prughjettu cù e seguenti dati cunnisciuti:

1. Riempite a tavula per analizà u prughjettu nantu à questu.

2. Calculemu u flussu di cassa utilizendu a funzione OFFSET. In u stadiu iniziale, u flussu hè uguali à l'investimenti. Dopu, applichemu a formula: =IF(OFFSET(Number,1;)=2;SUM(Afflux 1:Flux 1); SUM(Afflux 1:Flux 1)+$B$ 5)
   I designazioni di cellula in a formula pò esse diverse, secondu u layout di a tavula. À a fine, u valore da i dati iniziali hè aghjuntu - u valore di salvezza.

3. Determinemu u periodu per quale u prugettu hà da pagà. Per u periodu iniziale, usemu sta formula: = SUMMESLY (G7: G17;»<0″). A gamma di cellula hè a colonna di flussu di cassa. Per più periodi, applichemu sta formula: =Periodu iniziale+IF(Primu e.stream>0; Primu e.stream;0). U prugettu hè à u puntu di break-even in 4 anni.

4. Creemu una colonna per i numeri di quelli periodi quandu u prugettu paga.

5. Calculemu u ritornu di l'investimentu. Hè necessariu di fà una espressione induve u prufittu in un periudu particulare di tempu hè divisu da l'investimentu iniziale.

6. Determinemu u fattore di sconto utilizendu sta formula: =1/(1+Disc.%) ^ Numeru.

7. Calculemu u valore attuale cù a multiplicazione - u flussu di cassa hè multiplicatu da u fattore di scontu.

8. Calculemu PI (indice di prufittuità). U valore attuale à u tempu hè divisu da l'investimentu à l'iniziu di u prugettu.

9. Definimu a tarifa interna di u ritornu cù a funzione IRR: = IRR (Range di flussu di cassa).

Analisi di Sensibilità di l'Investment Using Datasheet

Per l'analisi di prughjetti in u campu di l'investimentu, altri metudi sò megliu adattati da una tabella di dati. Parechji utilizatori sperimentanu cunfusione quandu compilanu una formula. Per sapè a dependenza di un fattore nantu à i cambiamenti in l'altri, avete bisognu di selezziunà e cellule currette per inserisce calculi è per leghje dati.

Analisi di fattori è dispersioni in Excel cù automatizazione di calculu

Un'altra tipologia di l'analisi di sensibilità hè l'analisi di fatturi è l'analisi di varianza. U primu tipu definisce a relazione trà i numeri, u sicondu palesa a dependenza di una variabile à l'altri.

ANOVA in Excel

U scopu di un tali analisi hè di dividisce a variabilità di u valore in trè cumpunenti:

1. Variabilità cum'è u risultatu di l'influenza di altri valori.
2. Cambiamenti per via di a relazione di i valori chì l'affettanu.
3. Cambiamenti casuali.

Facemu l'analisi di a varianza attraversu l'add-in Excel "Analisi di dati". Se ùn hè micca attivatu, pò esse attivatu in i paràmetri.

A tavula iniziale deve seguità duie regule: ci hè una colonna per ogni valore, è i dati in questu sò disposti in ordine ascendente o descendente. Hè necessariu di verificà l'influenza di u livellu di l'educazione nantu à u cumpurtamentu in cunflittu.

1. Truvate u strumentu Analysis di Dati in a tabulazione Dati è apre a so finestra. In a lista, avete bisognu di selezziunà l'analisi unidirezionale di a varianza.

2. Riempite e linee di u dialogu. L'intervallu di input hè tutte e cellule, senza l'intestazione è i numeri. Gruppu per colonne. Fighjemu i risultati nantu à una nova foglia.

Siccomu u valore in a cellula gialla hè più grande di una, l'assunzione pò esse cunsiderata incorrecta - ùn ci hè micca una relazione trà l'educazione è u cumpurtamentu in cunflittu.

Analisi di fattori in Excel: un esempiu

Analizemu a relazione di dati in u campu di vendita - hè necessariu identificà i prudutti populari è impopulari. infurmazione iniziale:

1. Avemu bisognu di sapè per quale a dumanda di merchenzie hà aumentatu u più durante u secondu mese. Cumpilemu una nova tavola per determinà a crescita è a diminuzione di a dumanda. A crescita hè calculata cù sta formula: =IF((Demand 2-Demand 1)>0; Demand 2- Demand 1;0). Formula di diminuzione: =IF(Crescita=0; Demand 1- Demand 2;0).

2. Calculate a crescita di a dumanda di merchenzie in percentuale: =IF(Crescita/Risultatu 2 = 0; Diminuzione/Risultatu 2; Crescita/Risultatu 2).

3. Facemu un graficu per a chiarezza - selezziunate una gamma di cellule è creanu un histogramma attraversu a tabulazione "Inserisci". In i paràmetri, avete bisognu di caccià u riempimentu, questu pò esse fattu per mezu di u strumentu Format Data Series.

Analisi bidirezionale di a varianza in Excel

L'analisi di a varianza hè fatta cù parechje variàbili. Cunsiderate questu cun un esempiu: avete bisognu di sapè quantu rapidamente a reazione à un sonu di un volumu diversu si manifesta in l'omi è e donne.

1. Avemu apertu "Analisi di Dati", in a lista avete bisognu di truvà un analisi bidirezionale di varianza senza ripetizioni.
2. Intervallu di input - cellule chì cuntenenu dati (senza un header). Fighjemu i risultati nantu à una nova foglia è cliccate "OK".

U valore F hè più grande di u F-criticu, chì significa chì u pianu affetta a rapidità di reazione à u sonu.

cunchiusioni

In questu articulu, l'analisi di sensibilità in un fogliu di calculu Excel hè statu discutitu in detail, per chì ogni utilizatore puderà capisce i metudi di a so applicazione.