Un foglio di calcolo di solito parte dai dati e arriva a un risultato: dato un prezzo e un costo, quanto margine ottengo? Spesso però la domanda reale è inversa: a che prezzo devo vendere per ottenere un certo margine? Oppure: come cambia il risultato se varia il costo? Gli strumenti di analisi di simulazione servono a questo. In Excel si trovano in Dati, Analisi di simulazione; in LibreOffice Calc sotto il menu Strumenti e in parte nel menu Dati.
Il modello di partenza
Tutti gli strumenti lavorano su un modello in cui le celle di input sono separate e le formule dipendono da esse. Usiamo un esempio semplice:
| Cella | Contenuto | Valore |
|---|---|---|
| B1 | Prezzo unitario | 25 |
| B2 | Costo variabile unitario | 14 |
| B3 | Costi fissi mensili | 6.000 |
| B4 | Quantità venduta | 800 |
| B5 | Utile | =(B1-B2)*B4-B3 |
Con questi valori l'utile è 2.800. Ora poniamo qualche domanda.
Ricerca obiettivo: trovare l'input per un risultato
Domanda: quante unità devo vendere per arrivare al pareggio, cioè a utile zero?
Apri Ricerca obiettivo (in Calc: Strumenti, Ricerca valore destinazione) e compila:
- Imposta la cella: B5;
- Al valore: 0;
- Cambiando la cella: B4.
Il programma prova valori diversi per B4 finché B5 raggiunge l'obiettivo e propone il risultato, in questo caso circa 545,45 unità. Puoi accettarlo o annullare.
Qualche osservazione pratica:
- la cella da cambiare deve contenere un valore, non una formula;
- Ricerca obiettivo modifica un solo input alla volta;
- il risultato è approssimato: con modelli non lineari può fermarsi su una soluzione e non su un'altra, oppure non trovarne. Conviene partire da un valore iniziale ragionevole.
Per un modello così semplice il pareggio si potrebbe calcolare anche con una formula (costi fissi diviso margine unitario). La forza della Ricerca obiettivo emerge con modelli più complessi, in cui invertire il calcolo a mano sarebbe lungo o impossibile.
Tabelle dati: vedere molti scenari insieme
Ricerca obiettivo dà una risposta puntuale. Una tabella dati mostra invece come cambia un risultato al variare di uno o due input, tutto in una griglia.
Tabella a una variabile
Vuoi vedere l'utile per prezzi da 20 a 30 euro.
- In D2:D12 scrivi i prezzi da provare: 20, 21, …, 30.
- In E1, una riga sopra il primo prezzo e una colonna a destra, scrivi
=B5. - Seleziona D1:E12 e apri Tabella dati (in Calc: Dati, Statistiche, Operazioni multiple).
- Lascia vuota la cella di input riga e indica B1 come cella di input colonna.
Il programma riempie E2:E12 con l'utile corrispondente a ogni prezzo, ricalcolando il modello per ciascun valore.
Tabella a due variabili
Per vedere l'effetto combinato di prezzo e quantità:
- In D2:D12 i prezzi, in E1:K1 le quantità (500, 600, …, 1.100).
- Nella cella d'angolo D1 scrivi
=B5. - Seleziona D1:K12, apri Tabella dati, indica B4 come cella di input riga e B1 come cella di input colonna.
Ottieni una griglia con l'utile per ogni combinazione. Con una formattazione condizionale a scala di colori, le zone di perdita e di guadagno saltano subito all'occhio.
Le tabelle dati sono formule a matrice: non puoi modificare una singola cella del risultato, solo l'intera tabella. Su modelli pesanti possono rallentare il ricalcolo; in Excel puoi impostare il calcolo automatico «tranne le tabelle dati» dalle opzioni delle formule.
Gestione scenari
Quando vuoi confrontare alcune ipotesi complete, per esempio «prudente», «atteso» e «ottimistico», ciascuna con valori diversi per prezzo, costo e quantità, Gestione scenari permette di salvare ogni combinazione con un nome e passare dall'una all'altra. Può anche generare un rapporto di riepilogo che affianca i risultati. In Calc la funzione si trova in Strumenti, Scenari.
In alternativa, molti preferiscono costruire gli scenari come colonne di un foglio di parametri e scegliere quello attivo con un elenco a discesa: è più trasparente e funziona allo stesso modo in tutti i programmi.
Risolutore: ottimizzare con vincoli
Il Risolutore è lo strumento più potente. Non cerca solo un valore obiettivo, ma il massimo o il minimo di un risultato modificando più celle insieme, nel rispetto di vincoli. In Excel è un componente aggiuntivo da attivare nelle opzioni; in Calc si trova in Strumenti, Risolutore.
Un esempio tipico è il mix di produzione: due prodotti con margini diversi, che usano ore macchina e materiali in quantità diverse. Vuoi sapere quante unità di ciascuno produrre per massimizzare il margine totale, senza superare le ore macchina disponibili né le scorte di materiale, e con quantità non negative.
Nel Risolutore indichi:
- la cella obiettivo (margine totale) e se massimizzarla o minimizzarla;
- le celle variabili (quantità dei due prodotti);
- i vincoli (ore usate minori o uguali alle ore disponibili, materiale usato minore o uguale alle scorte, quantità maggiori o uguali a zero, eventualmente intere).
Per problemi lineari come questo il metodo Simplex trova la soluzione ottimale. Il risultato va sempre controllato con buon senso: un modello mal impostato produce soluzioni matematicamente corrette ma praticamente assurde.
Nel mio lavoro uso le tabelle dati per mostrare a chi deve decidere quanto un risultato è sensibile alle ipotesi: spesso è un'informazione più utile del numero singolo.
In sintesi
- Gli strumenti di simulazione richiedono un modello con input separati dalle formule.
- Ricerca obiettivo trova il valore di un input che produce il risultato desiderato.
- Le tabelle dati mostrano l'effetto di una o due variabili in una griglia.
- Gli scenari salvano combinazioni complete di ipotesi da confrontare.
- Il Risolutore cerca massimi o minimi modificando più celle nel rispetto di vincoli.
