Analisi di simulazione in Microsoft Excel
Excel What if per programmare il futuro
Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 03 Febbraio 2023
Categoria: Microsoft Office Excel Analisi di simulazione
I tool di Excel per l'analisi di simulazione permettono di usare un insieme di valori differenti in una o più formule per visionare tutti i possibili risultati. Tramite questi strumenti è possibile, solo per fare un esempio, creare due diversi bilanci, con una differente previsione di entrate, per quantificare i valori che porteranno a un determinato risultato. In generale, dunque, le analisi di simulazione in Excel consentono di utilizzare il foglio di calcolo per creare modelli probabilistici e analizzare i dati. Inoltre, è possibile utilizzare grafici e tabelle pivot per visualizzare i risultati della simulazione e prendere decisioni informate.
Vediamo dunque come effettuare analisi di simulazione in Excel attraverso Ricerca obiettivo, Risolutore, Gestione scenari e Tabella dati.
Sommario Analisi di simulazione in Microsoft Excel
Microsoft Excel mette a disposizione quattro strumenti per determinare la tipologia di analisi che meglio si adatta alle proprie esigenze. Questi tool sono: Ricerca Obiettivo, Risolutore, Gestione Scenari, Tabella Dati. Tutti e quattro devono prevedere delle formule per funzionare, così facendo aiutano a programmare il futuro rispondendo a domande tipo: "Cosa succede se..." anche chiamate domande "What if".
Essi sono raggiungibili dalla scheda Dati. Tre di questi Ricerca Obiettivo, Gestione Scenari, Tabella Dati sono presenti di default nel gruppo Previsione, mentre per usare il Risolutore bisogna, se non è mai stato fatto prima, attivarlo dalla scheda File Opzioni Componenti aggiuntivi e, da Vai..., scegliere Componente aggiuntivo Risolutore; l’icona Risolutore comparirà, sempre nella scheda Dati, nel gruppo Analisi.
Cominciamo a dare alcune definizioni relative ai quattro strumenti:
- Ricerca Obiettivo consente di trovare l’input adatto a un determinato valore.
Lavora su una variabile. Trova il valore che produce un risultato prefissato:
per es. “Quanti prodotti x devo vendere per incassare un y noto?”
- Risolutore consente di trovare il valore ottimale di una cella di destinazione mediante l’inserimento di valori diversi all’interno delle celle utilizzate per il calcolo della cella di destinazione stessa.
Lavora su più variabili per calcolare un valore ottimale:
per es. “Quale sarà l’utile se abbasso il prezzo di x e alzo il prezzo di y?”
Può impostare dei vincoli e, se trova una soluzione, stampare dei rapporti (Valori, Sensibilità, Limiti).
- Gestione Scenari consente di creare gruppi di valori differenti e di alternarli
Lavora su più variabili.
Può stampare dei rapporti (Riepilogo che mostra le celle variabili e il loro valore assunto nei vari scenari o una Tabella pivot) con i valori delle celle di input e di output e presentare, attraverso il pulsante Mostra, diversi scenari in sequenza così da poter rapidamente confrontare i risultati.
Permette di variare fino a 32 celle di input.
- Tabella Dati consente di visualizzare i risultati di input multipli contemporaneamente
Lavora su 2 variabili.
Permette dunque di vedere allo stesso tempo i risultati di vari possibili input diversi
Vediamo ora un esempio per ognuno di questi strumenti.
Excel Ricerca Obiettivo
Ricerca Obiettivo, come anche gli altri tre strumenti, deve sempre appoggiarsi a una formula. Il tool prova ripetutamente nuovi valori nella cella variabile (Cambiando la cella:) per trovare una soluzione.
Tale iterazione continua per 100 volte o finché Excel trova la soluzione per l’obiettivo specificato con scarto pari a 0,001. Il numero massimo di iterazioni (32.767) e lo scarto (tra 0 e 1) sono configurabili dalla scheda File Opzioni Formule.
Supponiamo di porci la seguente domanda:
pagando un importo rateale diverso da 500 euro, che prestito posso ottenere?
Considerando un interesse annuo del 12% (che diventa mensile dividendo la cella C5 per 12), un periodo di 24 rate e un prestito di 10.000 euro, applicando Ricerca Obiettivo come da immagine e dando Ok, si otterrà un valore di prestito superiore ai 10.000 euro suddetti.
Excel Risolutore
Immaginando delle previsioni di vendita trimestrali (Gennaio, Febbraio e Marzo) per tre differenti agenti e calcolando dei totali per agente e un totale globale, si desidera prevedere come dovrebbero variare le vendite di ciascun agente per ottenere un totale globale di 31.000 euro. L’obiettivo deve però essere raggiunto impostando dei vincoli (Pasquale deve vendere fino a raggiungere almeno un totale di 7.000 euro, mentre Marco di almeno 6.000 euro).
Imposta obiettivo: in Valore di: va specificata la cella obiettivo da impostare a un certo valore (o da massimizzare o minimizzare: per trovare il valore MAX o MIN per quella cella). È necessario che la cella contenga una formula. Affinché la cella obiettivo assuma il massimo minimo valore possibile, selezionare Max Min.
Modificando le celle variabili: vanno specificate le celle da modificare per raggiungere l'obiettivo.
Aggiungi Cambia Elimina: Aggiunge, Modifica, Rimuove un vincolo.
Risolvi: Avvia il processo risolutivo per il problema definito.
Reimposta Tutto: Annulla le impostazioni correnti.
Opzioni: Visualizza una finestra per specificare caratteristiche avanzate del Risolutore.
È possibile visualizzare dei rapporti finali da Risolvi Rapporti valori OK
Rapporti
Se il Risolutore trova una soluzione, è possibile scegliere un rapporto nella casella Rapporti OK, ogni rapporto va in un diverso foglio della cartella.
Valori: elenca la cella obiettivo e le celle variabili con valori originali e finali, vincoli e informazioni sui vincoli.
Sensibilità: fornisce informazioni sul grado di sensibilità della soluzione a piccole variazioni della formula nella casella Imposta obiettivo. Il rapporto non viene generato per modelli contenenti vincoli interi. Per modelli non lineari, il rapporto fornisce i valori per i gradienti ridotti e i moltiplicatori di Lagrange. Per modelli lineari, il rapporto include costi ridotti, prezzi ombra, coefficienti oggettivi (con incrementi e decrementi consentiti) e vincoli a destra.
Limiti: elenca cella obiettivo e celle variabili con valori, limiti inferiore e superiore e valori obiettivo. Il rapporto non viene generato per modelli contenenti vincoli interi. Il limite inferiore (o superiore) è il più piccolo (o grande) valore che la cella variabile può assumere fissati i valori di tutte le altre celle variabili e nel rispetto dei vincoli.
Salva scenario: apre l'omonima finestra di dialogo che consente di salvare valori di celle da utilizzare con Gestione scenari di Excel.
Excel Gestione Scenari
Supponiamo di avere dei costi annuali per Affitto, Spese del personale, Energia elettrica e Varie e delle previsioni di costi addizionali dovuti a un motivo qualsiasi. Si desidera creare una serie di scenari variando alcune spese percentuali.
Ipotesi 1° scenario: incremento dal 2,5% al 10% dell'Affitto e decremento dal 8% all'5% di Varie (Nome scenario: Affitto alto - Varie basso)
Seleziono C4:C7 Analisi di simulazione Gestione Scenari... Aggiungi Ok, sostituisco a 2,5 e 8 le nuove % 10 e 5 Ok
Ipotesi 2° scenario: incremento dal 10% al 20% delle Spese del personale e decremento dal 20% al 10% dell'Energia elettrica (Nome scenario: SpesePersonale alto - EnergiaElettrica basso)
Seleziono C4:C7 Analisi di simulazione Gestione Scenari... Aggiungi Ok, sostituisco a 10 e 20 le nuove % 20 e 10 Ok
Una volta creato almeno uno scenario, compariranno due pulsanti, Unisci e Riepilogo (con due opzioni Riepilogo e Scenario rapporto di tabella pivot)
Per riepilogare gli Scenari in un nuovo foglio di lavoro:
.../Riepilogo: mostra le celle variabili e il loro valore assunto nei vari scenari (la colonna intestata con i valori correnti indica lo scenario presente al momento della creazione del riepilogo). È modificabile.
.../Scenario rapporto di tabella pivot: crea una tabella pivot con l'elenco campi nominati con i riferimenti di cella
Per unire scenari da altri fogli di lavoro:
...Unisci e poi selezionare il foglio da cui prendere gli scenari
Da notare che non vengono mantenuti i valori originali quindi, se si vogliono conservare, vanno salvati come scenario da Aggiungi.
Excel Tabella Dati
Immaginiamo di avere una lista di nozze con dei valori di riferimento (Prezzo in C3, Quantità in C4, il prodotto =C3*C4 in C5, percentuale di Sconto in C6 e la formula =C5-(C5*C6) in C7 per calcolare il Totale).
Si desidera calcolare il prezzo per ogni prodotto (prezzo per quantità) e sottrarre lo sconto in una sola volta per tutti i prodotti che hanno dei Prezzi unitari e delle Quantità già definiti.
In C10 necessita una formula o il riferimento a essa: =C7
Seleziono C10:F18
Dalla scheda Dati Analisi di simulazione Tabella dati inserisco in
Cella di input per riga: C4 (le quantità)
Cella di input per colonna: C3 (il prezzo)
Cliccando Ok tutte le celle nell’intervallo D10:F18 si valorizzeranno con il prodotto tra Quantità e Prezzo, incluso lo Sconto del 10%.
Se desideri vedere un altro esempio puoi leggerlo nell'articolo dedicato alla funzione RATA
Conclusione
Nelle imprese si raccolgono grandi mole di dati continuamente (a proposito sai cosa sono i Big data?) che un uso sapiente di Microsoft Excel può trasformare in una miniera d’oro. Ora che sai che le analisi di simulazione si effettuano per capire come stanno le cose e per prendere decisioni valutando ipotesi alternative, approfittane anche tu.
Se ti è piaciuto Analisi di simulazione in Excel, condividilo!
Per saperne di più su Microsoft Excel potrebbero interessarti i video corsi Excel o questi libri: