Sebbene la lunga lista di funzioni di Excel sia una delle caratteristiche più allettanti dell'applicazione di foglio di calcolo di Microsoft, ci sono alcune gemme sottoutilizzate che migliorano queste funzioni. Uno strumento spesso trascurato è l'analisi What-If.
Lo strumento di analisi what-if di Excel è suddiviso in tre componenti principali. La parte qui discussa è la potente funzione Goal Seek che consente di lavorare all'indietro da una funzione e determinare gli input necessari per ottenere l'output desiderato da una formula in una cella. Continua a leggere per sapere come utilizzare lo strumento Goal Seek di Excel What-If Analysis.
Esempio di strumento di ricerca degli obiettivi di Excel
Supponiamo che tu desideri sottoscrivere un mutuo ipotecario per acquistare una casa e sei preoccupato di come il tasso di interesse sul prestito influenzerà i pagamenti annuali. L'importo del mutuo è di $ 100.000 e si ripagherà il prestito nel corso di 30 anni.
Utilizzando la funzione PMT di Excel, si può facilmente capire quali sarebbero i pagamenti annuali se il tasso di interesse fosse 0 %. Il foglio di calcolo sarebbe probabilmente simile a questo:
La cella in A2 rappresenta il tasso di interesse annuale, la cella in B2 è la lunghezza del prestito in anni, e la cella in C2 è l'importo del mutuo ipotecario. La formula in D2 è:
= PMT (A2, B2, C2)
e rappresenta i pagamenti annuali di un mutuo trentennale di $ 100.000 a 0% di interesse. Si noti che la cifra in D2 è negativa poiché Excel presuppone che i pagamenti siano un flusso di cassa negativo dalla posizione finanziaria.
Sfortunatamente, nessun creditore ipotecario ti presterà $ 100.000 con interessi dello 0%. Supponiamo che tu faccia un po 'di calcoli e scopri che puoi permetterti di rimborsare $ 6.000 all'anno in rate del mutuo. Ora ti stai chiedendo quale sia il tasso di interesse più alto che puoi assumere per il prestito per assicurarti di non pagare più di $ 6.000 all'anno.
Molte persone in questa situazione inizieranno semplicemente a digitare numeri nella cella A2 fino a quando la cifra in D2 ha raggiunto circa $ 6.000. Tuttavia, puoi fare in modo che Excel lavori per te utilizzando lo strumento Obiettivo ricerca analisi What-If. Essenzialmente, farai funzionare Excel dal risultato in D4 fino a quando non raggiungerà un tasso di interesse che soddisfi il tuo pagamento massimo di $ 6.000.
Inizia facendo clic sulla scheda Datisu la barra multifunzione e individua il pulsante What-If Analysisnella sezione Strumenti dati. Fai clic sul pulsante Analisi ipoteticae seleziona Ricerca obiettivodal menu.
Excel apre una piccola finestra e ti chiede di inserire solo tre variabili. La variabile Imposta celladeve essere una cella che contiene una formula. Nel nostro esempio qui, è D2. La variabile Al valoreè la quantità che desideri che la cella D2sia alla fine dell'analisi.
Per noi, è -6.000. Ricorda che Excel considera i pagamenti come un flusso di cassa negativo. La variabile Cambiando cellaè il tasso di interesse che Excel deve trovare per te in modo che l'ipoteca da $ 100.000 ti costerà solo $ 6.000 all'anno. Quindi, utilizza la cella A2.
Fai clic sul pulsante OKe potresti notare che Excel lampeggia un mucchio di numeri nelle rispettive celle fino a quando le iterazioni finalmente convergono un numero finale. Nel nostro caso, la cella di A2 dovrebbe ora leggere circa il 4,31%.
Questa analisi ci dice che per non spendere più di $ 6.000 all'anno su un mutuo trentennale di $ 100.000, è necessario garantire il prestito a non più del 4,31%. Se vuoi continuare a fare analisi what-if, puoi provare diverse combinazioni di numeri e variabili per esplorare le opzioni che hai quando cerchi di ottenere un buon tasso di interesse su un mutuo.
Analisi What-If di Excel Lo strumento Goal Seek è un potente complemento alle varie funzioni e formule presenti nel foglio di calcolo tipico. Lavorando all'indietro rispetto ai risultati di una formula in una cella, puoi esplorare le diverse variabili nei tuoi calcoli in modo più chiaro.