Analisi dati di simulazione

     

In questa pagina vengono presi in considerazione gli strumenti Excel usati per l'analisi di simulazione. Essi sono quattro:
↓ tabelle dati a una variabile
↓ tabelle dati a due variabili
↓ gestione scenari
↓ ricerca obiettivo
↓ risolutore
Tramite le analisi di simulazione possiamo simulare il comportamento di una formula al variare dei dati variabili sui quali essa opera.

Tabelle dati a una variabile

     

Una tabella dati è costituita da un intervallo di celle che, in relazione ad alcuni parametri di input presentano risultati diversi.
Questo strumento offre un modo rapido per calcolare più versioni di risultati con una sola operazione, nonché per visualizzare e confrontare nello stesso foglio di lavoro i risultati di tutte le variazioni.
Prendiamo in considerazione il file tab1.xls che descrive un mutuo di 120.000€ con tasso di interesse al 10% per un periodo di 48 mesi.

In cella B4 viene inserita la formula per il calcolo della rata mensile.
=RATA(B1/12;B2;B3)

Nel nostro prospetto si tratta solo di una ipotesi di prima istanza: in realtà vogliamo controllare l'entità della rata al variare del numero di mesi, per cui sotto la cella A4 inseriamo una seriazione rappresentativa degli eventuali numero di mesi su cui si potrebbe spalmare il mutuo; questo sviluppo si ha nell'intervallo A5:A12.

Selezioniamo allora l'intervallo di celle A4:B12 quindi cliccando sul pulsante Analisi di simulazione della scheda Dati attiviamo il comando Tabella dati.

Dobbiamo sempre tener presente che la selezione di celle deve comprendere anche la riga e la colonna di intestazione della tabella dati.
Inoltre, deve sempre essere inclusa la cella contenente la formula. Apparirà la seguente finestra di dialogo:

Il valore della cella di riferimento deve essere inserito in corrispondenza della voce Cella di input per colonna visto che la seriazione si sviluppa in verticale cioè nel senso di una colonna. Il risultato dell'elaborazione sarà il seguente:

dove si nota il variare della rata mensile a secondo dei mesi indicati. Come si intuisce, nelle tabelle dati ad una variabile, assume una certa rilevanza l'orientamento della tabella. Ipotizziamo che l'orientamento delle etichette del numero dei mesi fosse stato orizzontale, come si vede nel file allegato tab2.xls.

In questo caso la seriazione dei dati di test si sviluppa in orizzontale. cioè per riga.

attivando il comando Tabella dati, nella finestra di dialogo seguente:

sceglieremo Cella di input per riga per inserire il valore di riferimento del numero di mesi del mutuo, coerentemente con lo sviluppo in orizzontale (su una riga) della seriazione.

Tabelle dati a due variabili

     

La procedura che segue mostra come eseguire una analisi di simulazione, tramite una tabella dati a due variabili sul file allegato tab3.xls.

Dove la cella B7 contiene la formula

=((B1-B2)*B4*12)-B3

che ci permette di ottenere l'utile annuo. In questo computo sono da considerare costanti:
la cella B2 del costo unitario mensile e
la cella B3 delle spese fisse annuali.
Nella tabella sottostante si cerca il valore dell'utile annuo al variare delle due variabili ricavo unitario e clienti.
Di fatto si tratta di studiare una funzione matematica a due variabili.

Ora selezioniamo l'area B7:F11.

Anche in questo caso è importante includere la formula e le intestazioni delle due variabili, notiamo che le intestazioni della variabile clienti sono disposte su righe mentre le intestazioni della variabile ricavo unitario sono disposte su colonne diverse.

Dopo aver attivato il comando Dati>Tabella nella casella scegliamo
Cella di input per riga inseriamo $B$4 che è il valore attuale dei clienti (le intestazioni della var. clienti sono disposte su righe diverse) mentre per
Cella di input per colonna inseriamo $B$1 che è il valore attuale del ricavo unitario (le intestazioni della var. ricavo unitario sono disposte su colonne diverse).

Gestione scenari

     

Le tabelle dati consentono di ottenere una simulazione di risultati in base ad una sola formula e al massimo due variabili di input, una per riga e una per colonna.
Gli scenari di Excel permettono invece di analizzare i risultati di una formula in base ad un numero maggiore di variabili.

Il seguente procedimento basato sul file bilancio.xls mostra come usare uno scenario per analizzare come un cambio di abitazione possa influire sul bilancio economico di un soggetto.

Mentre le entrate annuali si prevede rimangano invariate, il cambio di abitazione implica un aumento delle spese condominiali, un aumento delle spese di carburante perché la nuova abitazione è più lontana dal luogo di lavoro, e un aumento delle spese di riscaldamento, perché la nuova casa è più grande di quella vecchia.

Lo scenario rappresenta l'insieme dei valori di input che sostituiscono i parametri originari di una tabella Excel. I dati variabili in input consentono di eseguire previsioni per l'ottenimento di nuovi valori. Possiamo creare più scenari all'interno dello stesso foglio di lavoro, passando da uno all'altro per controllare i dati ottenuti.

Analizziamo tramite la funzionalità Gestione scenari la situazione economica del soggetto in relazione alle nuove spese.
Dopo aver cliccato sul pulsante Analisi di simulazione della scheda Dati clicchiamo sulla voce Gestione scenari.

Cliccando sul pulsante Aggiungi scegliamo di creare un nuovo scenario cui daremo il nome scenario attuale, in quanto rappresentativo lo stato attuale delle cose.

Le celle variabili sono:
cella $D$2 relativa alle spese condominiali;
cella $D$4 relativa al carburante;
cella $D$10 relativa alle spese di riscaldamento.
Dopo aver cliccato su OK apparirà la seguente finestra che chiede di confermare i valori suggeriti per ciascuna delle celle variabili.
In questo caso confermiamo i valori attuali facendo nuovamente clic su OK.

Passiamo ora alla realizzazione dello stato futuro facendo clic su Aggiungi nella finestra di Gestione scenari.

Appare la finestra Aggiungi scenario nella quale digitiamo il nome del nuovo scenario, che in questo caso chiamiamo stato futuro.
Le celle variabili suggerite sono le stesse dello scenario precedente, quindi clicchiamo su OK.

Nella finestra Valori scenario digitiamo i nuovi valori:
3200 per la cella $D$2 relativa alle spese condominiali;
2800 per la cella $D$4 relativa al carburante;
2000 per cella $D$10 relativa alle spese di riscaldamento.

Clicchiamo su OK e si ritorna sulla finestra Gestione scenari.

Realizziamo ora un prospetto riepilogativo dello scenario facendo clic sul pulsante Riepilogo.

La finestra di dialogo propone come cella risultato la D11 che rappresenta la somma delle spese sulle quali vanno ad influenzare le celle variabili.

Scegliendo la prima voce : Riepilogo, otteniamo un nuovo foglio di lavoro con la tabella riepilogativa.
Scegliendo, invece Scenario rapporto tabella pivot otteniamo una tabella pivot che contiene i dati dello scenario appena creato.

Ricerca obiettivo

     

La funzione Ricerca obiettivo consente di impostare il valore derivante da una formula ad un valore predefinito, modificando i valori delle celle da cui la formula dipende.

Se conosciamo il risultato desiderato di una formula, ma non il valore di input necessario per ottenerlo, possiamo usare il comando Ricerca obiettivo.

Il seguente procedimento mostra come Ricerca obiettivo per ricavare l'interesse bancario utile a determinare un dato profitto.
Supponiamo di poter disporre di un dato capitale C=3000€ e di aver trovato una banca che ci permette di avere un interesse dell'1% qualora fossimo disposti ad immobilizzare il capitale per 5 anni. La formula che ci restituisce il montante (capitale + interesse) è la seguente:

implementiamo questa formula ne foglio di calcolo capitale.xls.

La cella B1 viene rinominata Cap perché contiene il capitale di 3000€.

La cella B2 viene rinominata i perché contiene l'interesse dell'1%.

La cella B3 viene rinominata n perché contiene il numero di anni.

La cella B4 viene rinominata M perche contiene il montante.

La formula del montante in cella B4 viene ovviamente implementata con la sintassi Excel.

=Cap*(1+i)^n

Ci proponiamo di conoscere l'interesse bancario che ci servirebbe per riuscire ad ottenere nei 5 anni di immobilizzo dei 3000€ un montante finale di 3300€.

Selezioniamo la cella obiettivo B4 quindi dal pulsante Analisi di simulazione della scheda Dati attiviamo il comando Ricerca obiettivo.

Compiliamo la finestra di dialogo con i valori mostrati cliccando poi sulla cella B2 dell'interesse per indicare la cella di cui cambiare il valore.

Si ottiene, allora, un risultato finale di un interesse dell'1,92%, ora, non ci resta che andare a cercare un istituto di credito disposto ad accordarci queste condizioni.

 

Risolutore

     

Il Risolutore è un componente aggiuntivo di Excel che può essere usato per determinare il valore massimo o minimo di una cella, denominata cella obiettivo, soggetta ai vincoli, o limiti, modificando simultaneamente un certo numero di altre celle ritenute variabili.

Di fatto, il risolutore si comporta come lo strumento di ricerca obiettivo, tranne per il fatto che più di una cella è variabile.

Il seguente esempio si riferisce alla pianificazione di una campagna pubblicitaria nella quale intendiamo investire 12.000€ per l’acquisto di spazi pubblicitari in alcune testate giornalistiche di portata nazionale ed europea.

I vincoli sono evidenziati nella parte bassa:
A non possiamo spendere più di 12.000€;
B sulle testate Espresso+Gente non dobbiamo investire più di 7500€ in totale;
C dobbiamo raggiungere un valore nominale di 800 milioni di lettori;
D l'investimento percentuale su ogni singola rivista non deve superare il 33%;
E su ciascuna rivista bisogna acquistare almeno 6 pagine.

Attenzione alla zona evidenziata che deve essere costituita dalle seguenti formule:

Quando viene chiamato il comando del risolutore appare una finestra di dialogo dove deve essere inserita la cella obiettivo e i vincoli come si vede nella figura seguente.

Se manteniamo la soluzione del risolutore, il risultato dell'operazione potrebbe essere il seguente.

In questo caso (per avere senso) le pagine pubblicate devono essere rappresentate da un numero intero.

Quest'ultimo aggiustamento può essere apportato manualmente sempre tenendo conto dell'obiettivo e dei vincoli.

In allegato il file di partenza che ci permette di eseguire l'esercizio.