Costruire applicazioni con Excel 2016 e 2019

Togliti ogni dubbio con una analisi “what if” su Excel 2019

di

thumbnail

10

dic

2018

Nelle imprese si raccolgono in continuazione dati che un uso sapiente di Excel può trasformare in una miniera d’oro.

In generale le analisi si effettuano per capire come stanno le cose e prendere decisioni valutando ipotesi alternative: che cosa succederebbe se le premesse fossero diverse, se per ipotesi quel vincolo non esistesse?

Presumendo che una riduzione dei prezzi di vendita ci faccia vendere di più, quali volumi dovremmo realizzare per arrivare agli stessi utili? Quali conseguenze sul risultato operativo si avrebbero da una diminuzione del 4 per cento del costo dei materiali? E se riuscissimo a finanziare il circolante a un tasso inferiore di due punti rispetto a quello che ora stiamo pagando, di quanto migliorerebbe l’utile lordo? E se fosse solo un punto e non due?

Per trovare risposte a questo genere di domande, che nel gergo aziendale americano si chiamano what if, bisogna prima costruire un modello logico della situazione da analizzare. I modelli logici possono essere molto semplici o molto complessi; il più diffuso, noto e utilizzato è lo schema del conto economico, che si presta egregiamente anche a questo scopo, oltre che a presentare il risultato contabile di un periodo.

Ridotto alla sua struttura più elementare, un conto economico o conto profitti e perdite, come si chiama in contabilità, è formato da tre numeri, legati fra loro da una semplice relazione aritmetica:

Utile = Ricavi – Costi

Se i ricavi sono 1.000 e i costi 900, l’utile è 1.000 – 900, cioè 100.

L’utilità di questo semplice schema sta nel fatto che le due voci di base, costi e ricavi, nascono dalla somma di molteplici fattori, ognuno dei quali ha una sua dinamica distinta da quella degli altri: i costi delle materie prime si muovono diversamente da quelli delle risorse umane; i ricavi originati dall’attività di base dell’impresa hanno un andamento diverso dai ricavi occasionali che possono nascere da dismissioni di cespiti o da opportunità commerciali non ripetitive. Stando così le cose, è molto facile provare a modificare il valore di un componente di costo o di ricavo per vedere l’effetto che potrebbe provocare sul risultato finale, cioè l’utile.

Analisi di questo tipo sono le più adatte a essere sviluppate con Excel, dal momento che tutta la logica di base dei fogli di lavoro è pensata proprio per eseguire calcoli di questo genere.

Ai fini delle analisi what if, oltre alle funzionalità intrinseche di Excel basate sul meccanismo del ricalcolo automatico abbiamo a disposizione anche alcuni strumenti specializzati, che andiamo a vedere con l’aiuto di qualche esempio.

Come sappiamo, quando in alcune celle di un foglio di lavoro vi sono formule che fanno riferimento ad altre celle che contengono soltanto dati, una modifica in queste ultime provoca il ricalcolo automatico dell’intero foglio e quindi le celle con le formule visualizzano l’effetto della modifica. Per cui, se in A1 abbiamo 15 e in A2 c’è un 5, la formula in A3 che dice:

=A1+A2

 

fa vedere 20. Cambiando il contenuto di A2, da 5 a 10, automaticamente in A3 compare 25, cioè la somma di 15 e 10. È questa correlazione fra celle con valori calcolati e celle con valori di riferimento a fare di Excel un formidabile strumento di analisi what if: in qualunque momento possiamo cambiare un valore di riferimento per osservare le conseguenze di questa modifica sui valori calcolati.

Per eseguire un’analisi what if è opportuno disporre i valori nelle celle del foglio di lavoro in modo che sia facile intervenire sui dati da variare. Vediamo un caso specifico, per capire meglio come si possa fare.

In Excel sono disponibili molte funzioni predefinite per i più svariati calcoli finanziari. Supponiamo di aver bisogno di accendere un mutuo per l’importo di 100.000 euro per finanziare la ristrutturazione di un appartamento. Una telefonata in banca ci fa sapere che i mutui di questo genere stanno su un tasso del 5% annuo e il rimborso è previsto in dieci anni.

In una cella del foglio di lavoro scriviamo la formula che calcola l’importo della rata annuale a queste condizioni:

Capitale:	€100.000,00
Tasso: 5%
Durata: 10 anni

 

In questo caso dovremmo scrivere:

=RATA(0,05;10;100000,00)

 

ottenendo il risultato di -€ 12.950,46, che è la somma da restituire alla banca ogni anno per dieci anni.

Scritta in questo modo, la formula non ci aiuta molto a fare qualche ipotesi alternativa, perché se volessimo valutare la rata annuale per una durata inferiore o per un prestito diverso, dovremmo riscrivere la formula per intero.

Per sfruttare le funzionalità di ricalcolo automatico, conviene preparare il foglio di lavoro in modo che la formula non contenga valori, ma riferimenti alle celle dove sono registrati i tre parametri che interessano: in questo modo è più agevole modificare uno, due o tutti e tre i valori di riferimento per vedere l’effetto che si otterrebbe (what if).

Nella figura seguente vediamo come si potrebbe impostare il foglio di lavoro che ci interessa.

Impostazione analisi what if su Excel 2019

Uno schema agevole per valutare ipotesi in alternativa.

 

Come si può vedere, la formula principale (quella in B9, che esegue il calcolo finanziario) è scritta utilizzando riferimenti a celle che hanno un nome, e non è necessario riscriverla ogni volta per vedere che cosa succederebbe se si trovasse un tasso più favorevole, o si aumentasse il capitale da mutuare o si allungasse la durata del rimborso: basta immettere i nuovi valori nelle celle predisposte per contenerli.

Tabelle di sensibilità

Ora, immaginiamo di aver bisogno di esaminare più ipotesi in riferimento a un determinato valore di partenza: cambiamo quel valore e osserviamo il nuovo risultato nella cella calcolata. Annotiamo il risultato in una cella vuota del foglio di lavoro, poi cambiamo di nuovo il valore di riferimento, rileviamo l’effetto trascrivendolo in un’altra cella vuota e andiamo avanti così, magari una decina di volte. È un modo un po’ primitivo di operare: c’è forse un sistema migliore? C’è e si chiama tabella di sensibilità. Vediamo come funziona, usando un esempio concreto.

Tabelle dati

La scheda Dati della Barra multifunzione contiene una sezione chiamata Strumenti dati, al cui interno si trovano cinque pulsanti di comando; l’ultimo, Analisi di simulazione, quando viene premuto presenta un menu con tre comandi: Gestione scenari, Ricerca obiettivo, Tabella dati. Quest’ultimo comando permette di creare una tabella con una serie di valori possibili da utilizzare in un’analisi what if. Tabelle di questo genere servono per analizzare la sensibilità di un risultato al variare di uno o più valori che lo determinano e vengono dette tabelle di sensibilità nel gergo degli analisti di gestione.

L’uso del comando Tabella dati non è affatto intuitivo e per capirne il funzionamento è necessario esaminare un esempio pratico, che vediamo illustrato in questa figura.

Tabelle di sensibilità in Excel 2019

Una tabella organizzata in modo da rappresentare il contributo di vari prodotti al rendimento economico di un reparto.

 

Nel foglio di lavoro sono riepilogati alcuni dati relativi a un reparto di produzione, nel quale si assemblano tre prodotti, chiamati Alfa, Gamma e Delta. L’assemblaggio dei tre prodotti assorbe lo stesso materiale in quantità diverse e ciascun prodotto richiede un numero diverso di ore di lavorazione. Il costo unitario di manodopera e materiali è indicato nelle celle B2 e B3, alle quali sono stati attribuiti rispettivamente i nomi CostoOra e CostoMateriali.

Immediatamente sotto questi dati è stata impostata una tabella che riepiloga i dati essenziali sull’attività produttiva del reparto al termine di un certo periodo. Le colonne Ore per pezzo, Materiali per pezzo, Prezzo di vendita e Pezzi prodotti contengono valori numerici in euro e in unità mentre la colonna Costo industriale contiene il risultato della formula:

=(CostoOra*B8)+(CostoMateriali*C8)

 

che viene ripetuta per ciascuno dei tre prodotti, cambiando soltanto il riferimento alla riga. I valori nella colonna Margine unitario risultano da una sottrazione fra quelli contenuti nella colonna Prezzo di vendita e quelli della colonna Costo industriale. Nell’ultima colonna, Margine totale, una semplice moltiplicazione fra Margine unitario e Pezzi prodotti fa vedere quanto contribuiscono i singoli prodotti nelle quantità fabbricate al rendimento economico del reparto.

Il capo del reparto si chiede che cosa succederebbe se cambiasse il costo dei materiali, perché si compra meglio, o perché il fornitore aumenta i prezzi o per un qualunque altro fattore. Se avesse in mente un solo numero, gli basterebbe sostituire il valore che sta nella cella B3 con un altro e il foglio di lavoro verrebbe ricalcolato istantaneamente. Siccome, però, le ipotesi possibili sono più di una, può ricorrere alla funzionalità chiamata sinteticamente Tabella dati, che gli permette di vedere contemporaneamente in forma tabellare gli effetti di svariate modifiche nella cella di riferimento CostoMateriali.

Per ottenere questa indicazione bisogna stabilire quali sono i valori risultato che si vogliono osservare e qual è la variabile che darà l’input per generare questi valori. Abbiamo già visto che la variabile input sarà il costo dei materiali. Stabiliamo che l’output sarà il margine per ciascun prodotto e per il loro totale.

Nella riga 13 inseriamo questi valori per le rispettive celle:

C13:	=H8
D13: =H9
E13: =H10
F13: =H11

 

mentre nell’intervallo B14:B19 creiamo una colonna di possibili valori di input:

B14:	60
B15: 65
B16: 70
B17: 75
B18: 80
B19: 85

 

Selezioniamo col mouse l’intera area della tabella che vogliamo creare, l’intervallo B13:F19, e scegliamo il comando Tabella dati da Dati/Strumenti dati/Analisi di simulazione. Nella finestra di dialogo Tabella dati, riportata nella figura seguente, scegliamo la casella Cella di input per colonna e vi scriviamo $B$3. Possiamo ottenere lo stesso effetto facendo clic direttamente sulla cella B3.

Finestra di dialogo Tabella dati in Excel 2019

La finestra di dialogo Tabella dati chiede l’indicazione della cella di input.

 

Un clic su OK ed ecco (nella figura successiva) la tabella di sensibilità generata da Excel (il formato con i bordi è stato aggiunto dopo, per evidenziare meglio la tabella).

Excel 2019 genera una tabella di sensibilità

La tabella di sensibilità con una sola cella di input.

 

Con questa semplice operazione siamo in grado di esaminare contemporaneamente l’effetto di più variazioni del contenuto di una stessa cella (B3) su quattro risultati (la colonna Margine totale). Non solo, ma se il nostro capo reparto volesse raffinare ulteriormente le sue ipotesi, potrebbe modificare a piacere uno o più dei sei valori di input registrati nelle celle B14:B19, osservando il contestuale modificarsi dei margini di utile sui prodotti.

Se, per avventura, volesse modificare il contenuto della nuova tabella (l’area compresa fra la cella C14 e la cella F19), riceverebbe un secco avvertimento da Excel: Impossibile modificare parte di una tabella dati.
In tutte queste celle, infatti, si trova una formula che ha questo aspetto inconsueto:

{=TABELLA(;B3)}

 

nella quale le parentesi graffe di apertura e chiusura indicano che si tratta di una formula matrice, cioè di una formula che agisce su una matrice di celle (l’intervallo C14:F19, appunto). Nel prossimo paragrafo chiariamo la natura di questo tipo di formule.

Soddisfatto per il risultato che ha ottenuto, il nostro capo reparto si chiede se non sarebbe possibile esaminare l’effetto congiunto di una modifica nei prezzi dei materiali e nel costo delle ore lavorate: ci sono novità in arrivo sulla fiscalizzazione degli oneri sociali (il carico dei contributi potrebbe diminuire) e si prevede un accordo a breve per il rinnovo del contratto integrativo. Excel, sempre servizievole, consente di esaminare l’effetto di due variabili di input, limitando però l’output a una sola variabile dipendente. È sempre meglio che niente, per cui procediamo nel modo seguente.

  1. Nella colonna B lasciamo i valori per i costi dei materiali così come stanno.
  2. Nella riga 13, da C13 a G13, inseriamo una serie di possibili valori per il costo orario.
  3. Nella prima cella dell’intervallo in cui si creerà la tabella, cioè in B13, inseriamo il riferimento =H11, che sarà la variabile dipendente da osservare.
  4. Selezioniamo l’intervallo che va da B13 a G19.
  5. Diamo il comando Tabella dati come prima e immettiamo il riferimento $B$2 nella casella Cella di input per riga e $B$3 nella casella Cella di input per colonna.

Un clic su OK e la tabella si presenterà all’incirca come nella prossima figura.

Tabella di sensiblità con due valori di input in Excel 2019

La tabella di sensibilità costruita con due valori di input
(nella riga 13 e nella colonna B).

 

Come si può notare, nella cella D15 – che si trova nel punto di intersezione della colonna con il costo orario 30 e della riga col costo per i materiali 65 – compare il valore 7.080, cioè quello che risulta dall’impostazione attuale della tabella dalla quale siamo partiti. Tutte le altre combinazioni di incroci presentano il margine complessivo che si può ottenere al variare contemporaneo dei valori per le due celle di input.

Anche in questa nuova disposizione, le celle che formano la parte centrale della tabella sono un’unica formula matrice e non possono essere modificate singolarmente, mentre si possono cambiare a piacere i valori nelle celle che contengono l’input, per osservare il comportamento della variabile dipendente.

Si può cambiare anche il contenuto della cella B13, per esaminare l’effetto delle variabili di input su un singolo prodotto, invece che sul totale dei prodotti: immettendo, per esempio, =H9, si può valutare l’impatto delle variazioni sul margine del Prodotto Beta.

Dalle tabelle che abbiamo appena descritto se ne possono derivare altre, particolarmente ricche di funzionalità.

Le tabelle pivot

Molte volte si estraggono informazioni da un confronto fra dati omogenei riferiti a momenti o a contesti diversi: è prassi comune, per esempio, degli addetti al marketing paragonare le vendite realizzate in un periodo con quelle dello stesso periodo dell’anno precedente, oppure mettere a confronto i risultati commerciali ottenuti in aree geografiche diverse nello stesso periodo.

Quando è opportuno fare confronti di questo genere, si dimostra comoda una tabella pivot, che rende estremamente agevole il confronto fra gruppi di dati organizzati in tabelle. Come il suo nome lascia supporre, con una tabella pivot si possono organizzare i dati in modo da poterli confrontare fra loro a gruppi, ruotando di volta in volta un gruppo di riferimento, che funge da perno e può essere scelto a piacere fra i dati a disposizione.

Supponiamo di avere una serie di dati come quella nella Tabella qui sotto: sono alcuni dati sulle vendite (espresse in numero di pezzi e in ricavi) di quattro prodotti, venduti in quattro province diverse, raggruppate in due aree di vendita; con questi stessi dati abbiamo visto (nel paragrafo precedente) i vantaggi offerti dalle funzionalità di filtro e totalizzazione delle tabelle strutturate.

Settore Prov Articolo Pezzi Ricavi
I BG A-10 123 € 12.300,00
I BG B-90 295 € 44.250,00
I BG C-50 414 € 16.560,00
I BG D-70 278 € 20.850,00
I CO A-10 217 € 21.700,00
I CO B-90 164 € 24.600,00
I CO C-50 451 € 18.040,00
I CO D-70 208 € 15.600,00
II MI A-10 447 € 44.700,00
II MI B-90 103 € 15.450,00
II MI C-50 129 € 5.160,00
II MI D-70 506 € 37.950,00
II PV A-10 479 € 47.900,00
II PV B-90 140 € 21.000,00
II PV C-50 247 € 9.880,00
II PV D-70 272 € 20.400,00

I dati sui quali si basa la tabella pivot.

 

Se inseriamo questi dati in un foglio di lavoro Excel e li organizziamo in una tabella strutturata, otteniamo un risultato simile a quello che vediamo nella parte superiore della figura seguente. Crearla richiede pochi clic e la riga Somma in fondo presenta la somma dei pezzi venduti e dei ricavi.

Volendo isolare l’andamento della sola provincia di Bergamo, nell’ambito del Settore I, ci basta agire sui pulsanti filtro delle prime due colonne per ottenere il risultato che si vede nella parte destra della figura.

Tabella strutturata

Una tabella strutturata aiuta a mettere a fuoco una situazione.

 

Se, però, volessimo organizzare i dati diversamente, per capire come sta andando l’attività commerciale nel suo complesso, senza distinguere fra settori e province, sarebbe meglio disporli nel modo che vediamo qui sotto.

Tutti i settori
Tutte le province
Articolo A-10 B-90 C-50 D-70 Totale
Pezzi 1.266 702 1.241 1.264 4.473
Ricavi € 126.600 € 105.300 € 49.640 € 94.800 € 376.340

Un altro modo di guardare ai dati, per avere nuove risposte.

 

Ottenere questo risultato con i vari comandi Copia/Incolla speciale/Trasponi richiederebbe parecchio lavoro e le pur ottime funzionalità di filtro della tabella strutturata non ci aiuterebbero più di tanto in questo caso.

Le tabelle pivot servono proprio per riorganizzare i dati in questo e in molti altri modi, con estrema semplicità e con poche mosse del mouse. La loro utilità deriva dal fatto che nelle imprese i sistemi informatici che si usano per la gestione sono in grado di produrre facilmente serie anche molto grandi di dati grezzi come quelli esemplificati qui sopra, che possono ricavare dalle loro basi di dati e mettere a disposizione degli uffici che si occupano di controllo della gestione e di marketing: per chi le riceve, queste serie di dati sono autentiche miniere potenziali di informazioni, che è possibile estrarre e cogliere soltanto con l’aiuto delle tabelle pivot.

Questo articolo riprende contenuti presenti nel capitolo 2 di Costruire applicazioni con Excel – per le versioni 2016 e 2019.

Costruire applicazioni con Excel 2019

Per fare il salto di qualità dal foglio all’applicazione

 




Mike Davis, diplomato alla Harvard Business School, è un consulente aziendale specializzato nell’uso di PC nella gestione operativa e nello sviluppo applicativo enterprise. Da questa esperienza ormai trentennale è nato un forte impegno nella formazione sui prodotti Microsoft a cui ha dedicato numerose pubblicazioni. Per Apogeo è autore di Costruire applicazioni con Access 2010, Excel 2010, Windows 10, tutti editi nella collana Guida completa. Insieme ai numerosi riconoscimenti professionali, tra le cose di cui va più orgoglioso, ricorda i suoi cinque nipoti.

Letto 2.248 volte | Tag: , ,

Lascia il tuo commento