Metodi e formule di previsione in Excel
This guide explains elementary forecasting methods that can be readily applied into Microsoft Excel spreadsheets. This guide applies to managers and executive who need to anticipate customer demand. The theory is illustrated with Microsoft Excel. Advanced notes are available for software developer who would like to reproduce the theory into a custom application.
Vantaggi della previsione
La previsione può aiutarti a prendere le decisioni giuste e a guadagnare/salvare denaro. Ecco un esempio.
- Dimensiona i tuoi inventari in modo ottimale
Il tempo è denaro. Lo spazio è denaro. Quindi, ciò che vuoi fare è utilizzare tutti i mezzi a tua disposizione per ridurre i tuoi stock – senza, ovviamente, incorrere in rotture di stock.
Come? Con la previsione!
Come semplificare le cose: etichette, commenti, nomi dei file
Col tempo, man mano che i tuoi dati si accumulano, sarà sempre più probabile che tu ti confonda e commetta errori. La soluzione? Non essere disordinato: fare un buon uso delle etichette, dei commenti e nominare correttamente i tuoi file può evitarti molti problemi.
- Etichetta sempre le tue colonne. Utilizza la prima riga di ogni colonna per descrivere i dati che contiene.
- Dati differenti, colonne differenti. Non mettere numeri diversi (ad esempio, i tuoi costi e le tue vendite) nella stessa colonna. È estremamente probabile che ti confonda e renda i calcoli e la gestione dei dati più difficili.
- Dai a ogni file un nome chiaramente comprensibile. Richiede poco sforzo e velocizza il processo. Li rende facili da identificare visivamente e più semplici da trovare tramite la funzione di ricerca di Windows.
- Usa i commenti.
Anche se di solito non lavori con una grande quantità di dati, è comunque molto facile confondersi. Questo vale soprattutto se torni a consultare dati creati molto tempo fa. Excel offre una soluzione eccellente: i commenti.
Basta fare clic destro sulla cella che si desidera commentare, e poi selezionare « inserisci commento ».
Puoi usarli:
- per spiegare il contenuto di una cella (es. costo unitario secondo le stime del Sig. Doe)
- per lasciare avvertimenti agli utenti futuri del foglio (es. Ho dei dubbi su questo calcolo… )
Iniziare: un semplice esempio di previsione usando le linee di tendenza
Visualizzare i tuoi dati
Procediamo ora con la nostra prima previsione. In questa sezione useremo questo file: Example1.xls. Per ripetere i passaggi tu stesso, puoi scaricare il file. Questi dati servono solo da esempio.
I nostri dati: Nella prima colonna, dati sui costi unitari di prodotti simili (il costo unitario riflette la qualità del prodotto). Nella seconda, dati sulla quantità venduta.
Cosa vogliamo sapere: Se vendessimo un altro prodotto, con una qualità corrispondente a un costo di $150 per unità, quante unità possiamo aspettarci di vendere?
Come ci arriviamo: Qui, è abbastanza semplice. Vogliamo trovare una relazione matematica semplice tra il costo unitario e le vendite, e poi utilizzare questa relazione per fare la previsione.
Prima di tutto, è sempre utile creare un grafico in Excel, per dare un’occhiata ai dati. I tuoi occhi sono strumenti eccellenti che possono aiutarti a identificare le tendenze in pochi secondi.
Per fare ciò, selezioniamo i nostri dati, poi usiamo Inserisci > Grafico, e scegliamo l’opzione XY (dispersione). Vogliamo stimare le vendite in funzione della qualità, quindi poniamo il costo unitario sull’asse orizzontale e le vendite su quello verticale.
Ora, ci fermiamo per qualche secondo e osserviamo attentamente ciò che vediamo: la relazione sembra essere crescente e lineare.
Per avere un’idea della forma esatta della relazione, clicchiamo con il pulsante destro sul grafico e selezioniamo l’opzione «Linea di tendenza».
Ora, dobbiamo selezionare la relazione che sembra «adatta» (cioè che descrive al meglio) i nostri dati. Anche qui, usiamo i nostri occhi: in questo caso, i punti sono quasi allineati, quindi usiamo l’impostazione «lineare». Successivamente, utilizzeremo altre impostazioni - più complesse, ma spesso più realistiche - come «esponenziale».
La nostra linea di tendenza è ora visualizzata sul grafico. Un altro clic destro ci consente di visualizzare la forma esatta della relazione: y = 102.4x - 191.64.
In altre parole: Numero di unità vendute = 102.4 volte il costo unitario - 191.64.
Quindi, se decidiamo di produrre ad un costo unitario di $150, possiamo aspettarci di vendere 102.4*150 - 191.64 = 15168 unità.
Abbiamo appena completato con successo la nostra prima previsione.
Tuttavia, fai attenzione: il software è sempre in grado di trovare una relazione tra le due colonne, anche se nella realtà tale relazione è molto debole! Pertanto, è necessario verificare la robustezza. Ecco come fare rapidamente:
- Innanzitutto, dai sempre un’occhiata al grafico. Se noti che i punti sono strettamente allineati alla linea di tendenza, come nel nostro esempio sopra, c’è una buona probabilità che la relazione sia robusta. Tuttavia, se i punti sembrano distribuiti in modo quasi casuale e generalmente abbastanza distanti dalla linea di tendenza, allora devi fare attenzione: la correlazione è debole, e la relazione stimata non dovrebbe essere fidata ciecamente.
- Dopo aver osservato il grafico, puoi utilizzare la funzione CORREL. Nel nostro esempio, la funzione sarebbe: CORREL(A2:A83,B2:B83). Se il risultato è vicino a 0, allora la correlazione è bassa, e la conclusione è: semplicemente non esiste una vera tendenza. Se è vicino a 1, allora la correlazione è forte. Quest’ultima è utile, poiché aumenta la capacità esplicativa della relazione trovata.
Esistono modi più sottili per assicurarsi che la correlazione sia elevata; ne parleremo in seguito.
Naturalmente, questi ultimi passaggi possono essere automatizzati: non devi annotare la relazione e usare la tua calcolatrice tascabile per effettuare i calcoli. Serve l’Analysis Toolpak!
Previsione utilizzando l’Analysis Toolpak
Prima di procedere, dovresti verificare se il Excel ATP (Analysis Toolpak) è installato. Consulta la sezione ‘Installazione dell’Analysis Toolpak’ per ulteriori informazioni.
Purtroppo, dati di vendita così perfetti con una relazione lineare così semplice sono abbastanza rari nella realtà. Diamo un’occhiata a ciò che Excel ha da offrire per situazioni più complicate, con dati più complessi.
Approfondimenti: l’esempio dell’adattamento esponenziale
Come puoi immaginare, un modello lineare dei tuoi dati non è sempre probabile. Infatti, ci sono molti motivi per credere che debba seguire un modello esponenziale. Molti comportamenti in economia sono guidati da equazioni esponenziali (ad esempio, i calcoli dell’interesse composto sono un classico esempio).
Ecco come eseguire un adattamento esponenziale:
- Dai un’occhiata ai tuoi dati. Disegna un grafico semplice e osserva. Se seguono un’andamento esponenziale, dovrebbero apparire così:
Questo è il caso perfetto. Ovviamente, i dati non appariranno mai esattamente così. Ma se i punti sembrano seguire approssimativamente questa distribuzione, dovrebbe incoraggiarti a considerare l’adattamento esponenziale.
Come nel precedente esempio, puoi sempre disegnare un grafico dei tuoi dati, richiedere una linea di tendenza e scegliere « esponenziale » al posto della lineare. Quindi, prendi nota dell’equazione visualizzata, come al solito.
- Per fortuna, puoi fare tutto questo direttamente utilizzando l’Analysis Toolpak: inserisci tutti i tuoi dati in un foglio Excel vuoto e vai su Strumenti => Analisi dei dati
Installazione dell’Analysis Toolpak (ATP)
L’ATP è un componente aggiuntivo che viene con Microsoft Excel, ma non è sempre installato per impostazione predefinita. Per installarlo, si può procedere come segue:
- Assicurati di avere con te il CD di Office. Excel potrebbe richiederti di inserire il CD per installare i file dell’ATP.
- Apri un foglio Excel, vai al menu Strumenti, quindi seleziona Componenti aggiuntivi. Seleziona la prima casella della finestra, etichettata « 2.Analysis Toolpak ».
- Inserisci il CD di Office se richiesto dal software.
- Fatto! Nota che il menu « Strumenti » ora include molte più funzionalità, compresa l’opzione « Analisi dei dati ». Questa è quella che utilizzeremo maggiormente.
Utilizzo dell’Analysis Toolpak (ATP)
… in un contesto lineare
Ora, torniamo al nostro esempio lineare. Se i tuoi dati « sembrano » buoni (vedi l’illustrazione sopra), puoi usare l’ATP per ottenere una stima diretta della forma funzionale, senza passare attraverso il processo della « linea di tendenza ».
Apri il tuo foglio di dati, poi apri il menu « Strumenti » e seleziona « Analisi dei dati ». Si aprirà una finestra che ti chiederà che tipo di analisi desideri eseguire. Seleziona « regressione » per un contesto lineare.
Ora devi fornire a Excel due argomenti: un intervallo « Y » e un intervallo « X ». L’intervallo Y indica ciò che desideri stimare (cioè le tue vendite), e l’intervallo X contiene i dati che ritieni possano spiegare le tue vendite (qui, il tuo costo unitario). Nel nostro esempio (vedi example1.xls), i dati delle vendite sono nella colonna B, dalla riga 3 alla riga 90, quindi devi impostare « $B$3:$B$90 » come intervallo Y, e « $A$3:$A$90 » come intervallo X. Quando hai finito, clicca « ok ».
Appare un nuovo foglio contenente i « risultati della regressione ».
Il risultato più importante è contenuto nella colonna « Coefficients » in fondo al foglio. L’intercetta è la costante, e il coefficiente della « X variable » è il coefficiente di X (qui, il tuo costo unitario). Dunque, troviamo la stessa equazione ottenuta utilizzando la funzione « linea di tendenza ». Vendite = Intercetta + coefficiente X * costo unitario, ovvero Vendite = -126 + 100 * costo unitario.
Questo foglio contiene anche un numero utile che ti fornisce informazioni sulla bontà della tua stima: il « R Square ». Se è vicino a 1, allora la tua stima è buona, il che significa che l’equazione trovata rappresenta abbastanza bene i tuoi dati. Se è vicino a 0, allora la stima non è buona, e probabilmente dovresti provare un altro tipo di adattamento (vedi l’adattamento esponenziale qui sotto).
Questo metodo è probabilmente più veloce rispetto alle tecniche della « linea di tendenza ». Tuttavia, è un po’ più tecnico e molto meno visivo. Quindi, se non vuoi fare lo sforzo di tracciare un grafico e valutarlo a occhio, assicurati almeno di controllare il valore del « R Square ».
… utilizzando l’adattamento esponenziale
Se la stima lineare non va bene (per esempio se ottieni un valore di R-Squared basso, ad es. 0.1), potresti voler utilizzare l’adattamento esponenziale.
Avvia l’Analysis Toolpak, come al solito: apri il tuo foglio di dati, poi apri il menu « Strumenti » e seleziona « Analisi dei dati ». Si aprirà una finestra che ti chiederà che tipo di analisi desideri eseguire.
Nel nostro contesto esponenziale, ciò che vogliamo selezionare è « esponenziale ».
Nota che Excel ti chiede solo un intervallo di input. Seleziona la colonna che contiene i dati che desideri prevedere (cioè il costo unitario) e scegli un « fattore di lisciamento ».
Come faccio a sapere quale modello scegliere?
Nota che non è necessario provare ogni metodo di stima per trovare quello che funziona meglio per te. Questo può essere ottenuto solo tramite automazione, dato l’elevato numero di metodi disponibili. Se desideri che tutti i modelli siano confrontati con i tuoi dati, puoi considerarne l’invio a Lokad. Abbiamo un potente sistema informatico che « testa » tutti i modelli e ne seleziona solo quelli che funzionano meglio con i dati della tua azienda.