Analisi di simulazione in Microsoft Excel

Domante What if per programmare il futuro

Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 20 aprile 2020
Categoria: Microsoft Office Excel

Excel come monitorare il Coronavirusl
Analisi di simulazione in Microsoft Excel

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, per esempio, creare due diversi bilanci, con una differente previsione di entrate, per quantificare i valori che porteranno a un determinato risultato.

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.

Cominciare a dare alcune definizioni relative ai quattro strumenti:

Vediamo ora un esempio per ognuno di questi strumenti.

Ricerca Obiettivo

Ricerca Obiettivo, come anche gli altri tre strumenti, deve sempre appoggiarsi ad 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.

Analisi di simulazione. Ricerca Obiettivo
Analisi di simulazione. Ricerca Obiettivo

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).

Analisi di simulazione. Risolutore

Analisi di simulazione. Risolutore

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 specificare caratteristiche avanzate del Risolutore.
È possibile visualizzare dei rapporti finali da Risolvi / Rapporti valori / OK

Rapporti
Se il Risolutore trova una soluzione, posso 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 la finestra di dialogo Salva scenario che consente di salvare valori di celle da utilizzare con Gestione scenari di 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 ad un motivo qualsiasi. Desidero creare una serie di scenari variando alcune spese percentuali.

Analisi di simulazione. Gestione Scenari

Analisi di simulazione. Gestione Scenari

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 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.

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.

Analisi di simulazione. Tabella Dati

Analisi di simulazione. Tabella Dati

In C10 necessita una formula o il riferimento ad 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%.

Conclusione

Nelle imprese si raccolgono 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 prendere decisioni valutando ipotesi alternative, approfittane anche tu.


Per saperne di più su Microsoft Excel potrebbero interessarti questi due libri:


Se vuoi approfondire alcuni dei temi trattati, visita la pagina con le mie pubblicazioni cartacee e online.

Se l'articolo ti è piaciuto, condividilo!

Segui l'hashtag #AndreaPacchiarotti