Nelle ultime settimane, abbiamo esaminato il aumento dell'uso e della popolarità di Excel e ho notato che Excel ha fatto incursioni nell'analisi. Oggi esamineremo un tipo di analisi: pianificazione e ottimizzazione basata su scenari.
Utilizzando i componenti aggiuntivi di Excel, è possibile creare analisi what-if e pianificazione basata su scenari utilizzando la simulazione Monte Carlo. Ma creare questo tipo di analisi è noioso, soprattutto perché Excel di base non ha funzionalità così integrate. Inoltre, il display è ancora in formato foglio di calcolo piuttosto che un display grafico. Essere in grado di mostrare graficamente gli input e i loro effetti sull'output, insieme alle loro interazioni tra loro, è estremamente prezioso per lo spettatore.
Alcune versioni fa, abbiamo deciso di aggiungere l'analisi JMP ai fogli di calcolo Excel tramite il nostro componente aggiuntivo di Excel. Questo estende il valore di un foglio di calcolo Excel incorporando grafica JMP e simulazione Monte Carlo.
Quando posso andare in pensione - un esempio
Diamo un'occhiata alla differenza tra i due approcci utilizzando un argomento che è vicino e caro a tutti noi: Quando avrò abbastanza soldi per andare in pensione? Di seguito è riportato un foglio di calcolo che ho creato per mostrare potenziali risultati.
Comincia con i miei risparmi fino ad oggi (Bilancio di apertura), cosa ho intenzione di risparmiare ogni anno, quanto interesse posso guadagnare dai miei investimenti e quanto tempo ancora ho intenzione di lavorare. Calcolo quindi il valore futuro del mio bilancio di apertura e il valore futuro dei flussi di risparmio. Sto solo mostrando le celle di input / output, ma noterai che ho nascosto alcune colonne.
Per mostrare come sono stati calcolati i numeri, ho nascosto le colonne (a proposito, è possibile nascondere elementi in un foglio di calcolo una buona idea?). Ora possiamo vedere come ho creato il modello.
Avevo bisogno di fare un calcolo basato sul numero di anni lasciati alla pensione. Se quel numero fosse 10, vorrei che le mie formule di valore futuro sul valore di apertura e sul flusso di risparmio venissero impostate a 10 anni. L'ho realizzato creando 30 celle (supponendo che abbia 30 o meno anni di lavoro) ciascuna con la seguente formula:
Le celle nella colonna D contengono = + IF ($ C $ 9> D5, D5 + 1,0)
Le celle nella colonna E contengono = IF (D6 <> 0, E5,0)
Entrambi sono in termini relativi o assoluti, se necessario. Questo mi ha permesso di creare una serie del numero necessario di anni per il mio calcolo.
La cella F10 contiene = FVSCHEDULE (F6, $ E $ 5: $ E $ 54) e
La cella F11 contiene = FV (C8, C9, F7) * - 1
Questo potrebbe non essere il modo migliore per creare questo modello, ma sembra calcolare correttamente i risultati - quindi cosa importa se c'è un modo migliore? L'ho fatto funzionare, giusto? (Se non sei d'accordo, non sei un tipico utente di Excel.)
Posso regolare gli ingressi e vedere le uscite cambiare. Bellissimo. Eppure ci sono potenziali intuizioni che non sono evidenti all'interno del foglio di calcolo. C'è una rappresentazione grafica che fornirebbe una migliore comprensione del modello.
Non entrerò nei dettagli di come Excel eseguirà l'analisi basata su scenari qui. Sei libero di apprenderlo da Excel. Ma voglio mostrare come JMP può fornire nuove informazioni che Excel non è in grado di fornire.
Si apre il foglio di calcolo con il componente aggiuntivo JMP richiamato, fare clic sulla scheda JMP e selezionare Crea / Modifica modello:
Quindi definisci il modello usando la finestra di dialogo Crea / Modifica:
Durante la fase di definizione, fai clic sul pulsante Scegli per selezionare le etichette di input e la cella contenente la quantità di input. È possibile impostare i valori minimi, iniziali e massimi per ogni cella di input e quindi identificare le celle di Output nella stessa modalità point-and-click.
Dopo aver definito il modello, selezionare Esegui modello dalla scheda del componente aggiuntivo JMP. JMP mostra quanto segue:
JMP mostrerà graficamente le variabili che contribuiscono al risultato (risparmi futuri). Questi grafici trasmettono molte più informazioni rispetto alle celle. Poiché JMP sta graficando i valori minimi / massimi per ciascuna variabile, quello che ha la pendenza più ripida è quello che influirà di più sulla variabile target (risparmi futuri). In questo caso, è il numero di anni in cui posso lavorare per consentire ai miei risparmi di crescere.
Ma il Profiler mostrerà anche come ogni variabile può influenzare gli altri. Posso usare il mouse per selezionare la linea rossa verticale al centro di ciascun grafico e trascinarla verso sinistra o destra, in effetti alzando o abbassando il valore di quella variabile. Quando aumento il numero di anni fino al pensionamento, guarda cosa succede al grafico del ROI annuale:
La pendenza di tale linea aumenta drammaticamente, indicando che questa variabile ora ha un impatto maggiore sul risultato. Questo ha senso: più tempo permetti ai tuoi soldi di guadagnare interesse e crescere, meglio è.
Ma l'analisi non si ferma qui. Dopo aver impostato ogni variabile su un valore realistico, è possibile testare la probabilità di raggiungere tali valori, ovvero, quale è la probabilità che io risparmi in media $ 10.000 all'anno o guadagni un rendimento del 7% sul mio investimenti? È qui che entra in gioco la simulazione.
Con JMP, puoi definire le variabili su cui eseguire la simulazione Monte Carlo:
L'immagine sopra mostra la probabilità di raggiungere un obiettivo di $ 1 milione in risparmi futuri variando casualmente l'importo risparmiato all'anno, il tasso di rendimento e il numero di anni di pensionamento. Una probabilità del 5% di raggiungere il mio obiettivo non mi farebbe sentire bene se fossero numeri reali.
Ma l'obiettivo di questa serie di blog non è quello di mostrare come sfruttare un foglio di calcolo esistente. Il fatto è che se ci sono stati errori nel foglio di calcolo (e per favore non li trovi), l'analisi della JMP sarebbe di scarso valore e potrebbe portare a conclusioni e decisioni sbagliate.
La prossima settimana vedremo come è possibile creare lo stesso modello in JMP.
Nota: Questo post del blog è parte di a serie su errori nei fogli di calcolo . I nuovi post di questa serie vengono pubblicati ogni settimana il mercoledì.
Mike Clayton ha scritto:
Gli esempi di risparmio presentano sempre ciò che ritengo sia un ROI molto ottimista dato i soliti sistemi di risparmio del tipo IRA. Il mio ROI sui risparmi dal 1994 al 2014, un periodo di 20 anni di grande volatilità, ha mostrato il ROI delle tariffe dopo le tasse e di circa il 4 percento. Tuttavia per ottenere quei soldi, devo pagare le imposte sul reddito ... che sono d'accordo che ho evitato durante quel periodo di 20 anni di risparmi costanti.
Quindi cosa è reale? Come includere i problemi fiscali in un'analisi del piano di risparmio dato l'uso comune di IRA. Questo potrebbe essere un modello interessante per aggiungere gli sforzi Monte Carlo PRIMA di firmare per un ROTH o un IRA REGOLARE. Farebbe un bel webinar.
