Creare proiezioni Excel

Creare proiezioni su Excel può essere frustrante, ad esempio quando vogliamo creare delle proiezioni per un business plan. Nelle versioni precedenti a 365, questi processi erano spesso complessi perchè richiedevano molta attenzione, trascinando le formule nel foglio fino al mese interessato.

Questo processo crea però file poco scalabili e con grande possibilità di sbagliare, motivo per il quale conviene indubbiamente utilizzare la versione 365, disponibile anche online (gratuitamente).

Come creare proiezioni con Excel 365

Vediamo come creare proiezioni con Excel 365, creando un file scalabile e facile da modificare. Stiamo ipotizzando di avere un certo numero di utenti iscritti al nostro sito. Solo una percentuale di tutti questi utenti pagherà una quota mensile, mentre gli altri saranno iscritti con il piano gratuito. Vogliamo inserire sia un valore di spesa media per utente pagante al mese, una crescita percentuale mensile e una percentuale di churn mensile (utenti che abbandonano la piattaforma). Vogliamo anche avere una certa flessibilità inserendo un numero di mesi a piacere, che comprenda la nostra proiezione.

Mettiamo i dati in un formato più chiaro:

• Utenti iniziali: gli utenti iniziali del nostro modello
• Utenti paganti: la percentuale di utenti paganti sul totale degli utenti
• Crescita mensile: la crescita costante mensile dei nostri utenti
• € / Mese / Utente pagante: la cifra che ogni utente spende mensilmente sulla nostra piattaforma
• Numero di mesi: i mesi interessati dalla nostra proiezione
• Churn rate: la percentuale di utenti che, mensilmente, abbandona la piattaforma
• Crescita - Churn: questo dato percentuale non è altro che la differenza fra la crescita e il churn

I dati sono tutti inseriti in una tabella, ma utilizzeremo i singoli dati con il loro riferimento di cella (ad esempio C9, C7 e così via).

A questo punto dobbiamo immaginarci un flusso come questo:

• Ci serve una sequenza di mesi, da 1 a X, a seconda del dato che abbiamo inserito
• Per ognuno dei mesi, dobbiamo calcolare gli utenti in piattaforma, gli utenti paganti e il fatturato
• Ci servirà poi aggregare i dati per creare un grafico, inserendo il fatturato cumulato

Cominciamo allora a vedere la nostra proiezione.

Vediamo la prima formula:

=MATR.TRASPOSTA(SEQUENZA(C7))

Prendiamo i numeri da 1 al valore in C7 per creare una sequenza tipo {1;2;3...} a seconda della scelta dell'utente. I numeri saranno poi inseriti in diverse colonne, anzichè righe, grazie a MATR.TRASPOSTA.

Vediamo la seconda formula:

=ARROTONDA(PERCOL(F11#;LAMBDA(x;SE(x=1;C3;SCARTO(x;1;-1)*(1+C9))));0)

F11# è la sequenza di numeri precedente. PERCOL ci dice che per ognuno degli elementi di questo array vogliamo effettuare una operazione, tramite una funzione LAMBDA. La funzione LAMBDA dice che se il valore è il primo della sequenza, allora inseriremo C3, che è il nostro valore degli utenti iniziale. Se non lo è, allora deve muoversi sul foglio per andare in giù, da F11#, di una riga e di una colonna. Il concetto è che, così facendo, il dato che prenderemo sarà quello del mese precedente. A questo punto il valore sarà moltiplicato per 1+C9, ovvero 1+(crescita-churn) che è il nostro tasso di crescita reale. Alla fine, ARROTONDA non farà altro che arrotondare il totale all'intero più vicino.

Vediamo la terza formula:

=ARROTONDA(F12#*C4;0)

In questo caso non stiamo facendo altro che recuperare gli utenti totali per poi moltiplicarli per la percentuale di conversione, in modo da trovare i nostri utenti paganti. L'arrotondamento porta il risultato a essere un intero.

Vediamo la quarta formula, necessaria per trovare il fatturato mensile:

=F13#*C6

In questo caso stiamo moltiplicando gli utenti paganti per il prezzo mensile.

Le prime 3 formule del RECAP, utilizzato per avere un riassunto dei dati della proiezione, sono praticamente uguali - vediamo la prima:

=INDICE(F12#;C7)

In sostanza stiamo prendendo il valore C7, cioè i mesi totali, del nostro array in F12#. Questa formula ci restituirà gli utenti finali della nostra progressione

Per avere un totale di quanto fatturato, ci è sufficiente sommare tutti i valori di fatturato mensili:

=SOMMA(F14#)

Per creare un grafico dinamico come quello che vedi sopra dobbiamo creare un array dedicato:

=LET(fatturato;F14#;
fatturato_cumulato;SCAN(0;fatturato;LAMBDA(a;b;a+b));
mesi_progressione;F11#;
mesi_ipotesi;C7;

STACK.VERT(fatturato;SE(mesi_progressione=C7;fatturato;NON.DISP());fatturato_cumulato;SE(mesi_progressione=C7;fatturato_cumulato;NON.DISP())))

Vediamola nel dettaglio:

• La funzione LET dichiara che F14# è il nostro fatturato mensile, chiamato fatturato
• Il fatturato_cumulato deve sommare i singoli valori del fatturato mensile. In questo caso possiamo utilizzare la funzione SCAN che è perfetta per operazioni ricorsive - in sostanza, SCAN sommerà, per tutto l'intervallo del fatturato, il fatturato in maniera progressiva
• I mesi di progressione sono quelli inseriti in F11#
• I mesi di ipotesi sono invece in C7

Vediamo che cosa fa STACK.VERT, che unisce 4 array diversi:

• Il primo è il fatturato mensile
• Il secondo è il fatturato mensile, dove però prendiamo solo il valore finale. Inserendo SE(mesi_progressione=C7 ci assicuriamo che sia così, altrimenti apparirà un errore con la funzione NON.DISP
• Il terzo è il fatturato cumulato
• Il quarto è il fatturato cumulato, dove però, pure in questo caso, prendiamo solamente il valore finale

Il motivo per una formula così arzigogolata è che ci serve un array completo per poi evidenziare l'ultimo mese sul grafico. In questo modo riusciremo a evidenziare solamente i valori dell'ultimo mese.

Come creare proiezioni con Excel (non versione 365)

Vediamo adesso come creare proiezioni con Excel, non nella versione 365.

Ci servono solo 3 dati, supponendo che restino costanti per il periodo di analisi:

1. Numero di clienti iniziali - 120
2. % di clienti che si uniscono all'azienda in un determinato anno (crescita) - 10%
3. % di clienti che abbandonano l'azienda in un determinato anno (churn rate) - 4,5%

L'immagine segnala sia la tabella con i dati, sia le formule presenti.

I clienti iniziali vengono sommati ai clienti acquisiti -> vengono eliminati i clienti persi -> otteniamo i clienti finali, che diventano i clienti iniziali dell'anno successivo. Teniamo bloccati i riferimenti per B3 e B4 perchè sono uguali per tutto il nostro periodo di analisi. Quando poi faremo modelli più complessi, avremo sicuramente valori differenziati a seconda dell'anno da noi considerato.

C'è però un errore che riscontriamo spesso in modelli di questo tipo. Partiamo infatti da 120 clienti e arriviamo a 156,84 nell'ultimo anno. Se presentiamo a chiunque una proiezione come questa, la domanda sarebbe: "Ma perchè ci sono delle frazioni di cliente?"

Risolviamo con una formula molto semplice tramite la funzione ARROTONDA. Attenzione anche a risolvere temporaneamente questo problema cambiando la visualizzazione e modificando il numero di decimali che si vedono sul file. Se moltiplicati, questi numeri potrebbero portare a risultati difficili da interpretare.

La regola generale è di ragionare su quali siano i valori che possono essere con decimali. Non ha senso arrotondare il numero di clienti iniziali perchè è un numero finito. Ha senso farlo se utilizziamo moltiplicazioni o divisioni, come in questo caso.

Formula generale:

ARROTONDA([valore];[numero_decimali])

Esempio: nel nostro caso, vediamo i clienti persi nel primo anno (cella G13). Consideriamo quindi:

- [valore] = E6*$B$4

- [numero_decimali] = 0

Formula pratica:

ARROTONDA(E6*$B$4;0)

Abbiamo quindi creato un piccolo sistema per iniziare a valutare delle proiezioni future sul nostro business. Possiamo adattarlo a qualsiasi nostra esigenza dato che le regole rimangono molto semplici. Un suggerimento sarebbe quello di inserire i dati in INPUT (clienti iniziali, crescita, churn rate) in un foglio differenziato per esigenze di layout e di comodità.