Metodi e formule di previsione in Excel
Questa guida spiega i metodi di previsione elementari che possono essere facilmente applicati ai fogli di calcolo di Microsoft Excel. Questa guida si rivolge a manager e dirigenti che hanno bisogno di anticipare la domanda dei clienti. La teoria è illustrata con Microsoft Excel. Note avanzate sono disponibili per gli sviluppatori di software che desiderano riprodurre la teoria in un’applicazione personalizzata.
Vantaggi della previsione
La previsione può aiutarti a prendere le decisioni giuste e a guadagnare / risparmiare denaro. Ecco un esempio.
- Dimensiona ottimamente i tuoi inventari
Il tempo è denaro. Lo spazio è denaro. Quindi ciò che vuoi fare è utilizzare tutti i mezzi a tua disposizione per ridurre le tue scorte, senza incorrere in mancanze di magazzino, ovviamente.
Come? Prevedendo!
Come semplificare le cose: etichette, commenti, nomi dei file
Nel tempo, man mano che i tuoi dati si accumulano, sarai sempre più propenso a confonderti e a commettere errori. La soluzione? Non essere disordinato: fare un buon uso delle etichette, dei commenti e dare nomi corretti ai tuoi file può risparmiarti molti problemi.
- Etichetta sempre le tue colonne. Utilizza la prima riga di ogni colonna per descrivere i dati che contiene.
- Dati diversi, colonne diverse. Non mettere numeri diversi (ad esempio, i costi e le vendite) nella stessa colonna. È estremamente probabile che ti confonderai e rende più difficile i calcoli e la gestione dei dati.
- Dai a ogni file un nome chiaramente comprensibile. Richiede poco sforzo e velocizza le cose. Li rende facili da identificare visivamente e più facili da trovare utilizzando 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 ai dati che hai creato molto tempo prima. Excel ha una grande soluzione da offrire: commenti.
Basta fare clic destro sulla cella su cui si desidera commentare e quindi selezionare “inserisci commento”.
Puoi usarli:
- per spiegare il contenuto di una cella (ad esempio, costo unitario secondo le stime del signor Doe)
- per lasciare avvertimenti ai futuri utenti del foglio (ad esempio, ho un dubbio su questo calcolo… )
Per iniziare: un semplice esempio di previsione utilizzando le linee di tendenza
Visualizzazione dei dati
Ora facciamo la nostra prima previsione. In questa parte, useremo questo file: Example1.xls. Per ripetere i passaggi da soli, puoi scaricare il file. Questi dati servono solo come esempio.
I nostri dati: Nella prima colonna, dati sui costi unitari di prodotti simili (il costo unitario riflette la qualità del prodotto). Nella seconda colonna, dati su quanto è stato venduto.
Cosa vogliamo sapere: Se vendiamo un altro prodotto, con una qualità corrispondente a un costo di $150/unità, quante unità possiamo aspettarci di vendere?
Come arriviamo a questo punto: Qui è abbastanza semplice. Vogliamo trovare una semplice relazione matematica tra il costo unitario e le vendite, e poi utilizzare questa relazione per fare la nostra 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, quindi utilizziamo Inserisci > Grafico e scegliamo l’opzione XY (Scatter). Vogliamo stimare le vendite come funzione della qualità, quindi mettiamo il costo unitario sull’asse orizzontale e le vendite sull’asse verticale.
Ora, fermiamoci qualche secondo e diamo un’occhiata a ciò che vediamo: la relazione sembra essere crescente e lineare.
Per avere un’idea della forma esatta della relazione, facciamo clic destro sul grafico e selezioniamo l’opzione “Linea di tendenza”.
Ora, dobbiamo selezionare la relazione che sembra “adattarsi” (cioè descrivere al meglio) ai nostri dati. Anche qui, usiamo i nostri occhi: in questo caso, i punti sono quasi su una linea retta, quindi usiamo l’impostazione “lineare”. In seguito, useremo 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.
Capisci: Numero di unità vendute = 102.4 volte il costo unitario - 191.64.
Quindi, se decidiamo di produrre a 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, attenzione: il software è sempre in grado di trovare una relazione tra le due colonne, anche se questa relazione è in realtà molto debole! Pertanto è necessario effettuare un controllo di robustezza. Ecco come fare rapidamente:
- Prima di tutto, dai sempre un’occhiata al grafico. Se trovi i punti vicini alla linea di tendenza, come nel nostro esempio sopra, c’è una buona probabilità che la relazione sia robusta. Tuttavia, se i punti sembrano essere posizionati quasi casualmente e sono in generale abbastanza lontani dalla linea di tendenza, allora devi fare attenzione: la correlazione è debole e la relazione stimata non dovrebbe essere accettata acriticamente.
- Dopo aver dato un’occhiata al 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 è: non c’è semplicemente una vera tendenza. Se è vicino a 1, allora la correlazione è forte. Quest’ultima è utile, poiché aumenta il potere esplicativo della relazione trovata.
Ci sono modi più sottili per assicurarsi che la correlazione sia alta; torneremo su questo argomento più avanti.
Naturalmente, questi ultimi passaggi possono essere automatizzati: non è necessario annotare la relazione e utilizzare la calcolatrice tascabile per effettuare il calcolo. Hai bisogno di Analysis Toolpak!
Previsione utilizzando Analysis Toolpak
Prima di procedere, verifica se Excel ATP (Analysis Toolpak) è installato. Consulta la sezione Installazione di Analysis Toolpak per ulteriori informazioni.
Sfortunatamente, dati di vendita così perfetti con una relazione lineare così bella e semplice sono piuttosto rari nella vita reale. Diamo un’occhiata a cosa Excel offre per situazioni più complesse, con dati più complessi.
Approfondimento: l’esempio dell’adattamento esponenziale
Come puoi immaginare, un modello lineare dei tuoi dati non è sempre probabile. Infatti, ci sono molte ragioni per credere che dovrebbe seguire un modello esponenziale. Molti comportamenti nell’economia sono guidati da equazioni esponenziali (ad esempio, i calcoli di interesse composti sono un esempio classico).
Ecco come eseguire un adattamento esponenziale:
- Dai un’occhiata ai tuoi dati. Disegna un grafico semplice e guardalo. Se seguono un’evoluzione esponenziale, dovrebbero apparire così:
Questo è il caso perfetto. Ovviamente, i dati non assomiglieranno mai esattamente a questo. Ma se i punti sembrano seguire approssimativamente questa distribuzione, dovrebbe incoraggiarti a considerare l’adattamento esponenziale.
Come nell’esempio precedente, puoi sempre disegnare un grafico dei tuoi dati, richiedere una linea di tendenza e scegliere “esponenziale” invece di lineare. Quindi, raccogli l’equazione visualizzata, come al solito.
- Fortunatamente, puoi fare tutto questo direttamente, utilizzando Analysis Toolpak: metti tutti i tuoi dati in un foglio di calcolo vuoto di Excel e vai su Strumenti => Analisi dati
Installazione di Analysis Toolpak (ATP)
ATP è un componente aggiuntivo fornito con Microsoft Excel, ma non è sempre installato per impostazione predefinita. Per installarlo, è possibile procedere come segue:
- Assicurati di avere il tuo CD di Office con te. Excel potrebbe richiederti di inserire il CD per installare i file ATP.
- Apri un foglio di calcolo di Excel, vai al menu Strumenti e seleziona Componenti aggiuntivi. Seleziona la prima casella della finestra, denominata “2.Analysis Toolpak”.
- Inserisci il tuo CD di Office se richiesto dal software.
- Fatto! Nota che il menu “Strumenti” ora include molte altre funzioni, tra cui l’opzione “Analisi dati”. Questa è quella che useremo di più.
Utilizzo di Analysis Toolpak (ATP)
… in un contesto lineare
Ora, torniamo al nostro esempio lineare. Se i tuoi dati “sembrano” buoni (vedi illustrazione sopra), puoi utilizzare ATP per ottenere una stima diretta della forma funzionale, senza passare attraverso il processo di “linea di tendenza”.
Apri il foglio dei dati, quindi apri il menu “strumenti” e seleziona “Analisi dati”. Compare una finestra che ti chiede che tipo di analisi desideri eseguire. Seleziona “regressione” per impostazioni lineari.
Ora devi fornire a Excel due argomenti: un “intervallo Y” e un “intervallo X”. L’intervallo Y indica ciò che desideri stimare (ad esempio le tue vendite), mentre l’intervallo X contiene i dati che pensi possano spiegare le tue vendite (ad esempio il costo unitario). Nel nostro esempio (vedi example1.xls), i dati delle vendite si trovano nella colonna B, dalla riga 3 alla riga 90, quindi devi inserire “$B$3:$B$90” come intervallo Y e “$A$3:$A$90” come intervallo X. Quando hai finito, clicca su “ok”.
Compare un nuovo foglio, contenente i “risultati della regressione”.
Il risultato più importante si trova nella colonna “Coefficients” in fondo al foglio. L’intercetta è la costante e il coefficiente della “variabile X” è il coefficiente di X (in questo caso, il costo unitario). Pertanto, troviamo la stessa equazione che abbiamo trovato utilizzando la funzione “linea di tendenza”. Vendite = Intercetta + Coefficiente X * costo unitarioVendite = -126 + 100 * costo unitario
Questo foglio contiene anche un numero utile che 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 che hai trovato è una rappresentazione abbastanza buona dei tuoi dati. Se è vicino a 0, allora la stima non è buona e dovresti probabilmente provare un altro tipo di adattamento (vedi adattamento esponenziale di seguito).
Questo metodo è probabilmente più veloce delle tecniche di “linea di tendenza”. Tuttavia, è un po’ più tecnico e molto meno visivo. Quindi, se non vuoi fare la fatica di tracciare e valutare visivamente i tuoi dati, assicurati almeno di controllare il valore del “R square”.
… utilizzando l’adattamento esponenziale
Se l’adattamento lineare non va bene (ad esempio se ottieni un basso R-Squared, cioè 0,1), potresti voler utilizzare l’Adattamento Esponenziale.
Avvia Analysis Toolpak, come al solito: apri il foglio dei dati, quindi apri il menu “strumenti” e seleziona “Analisi dati”. Compare una finestra che ti chiede che tipo di analisi desideri eseguire.
Nel nostro contesto esponenziale, ciò che desideriamo selezionare è “esponenziale”.
Nota che Excel ti chiede solo un intervallo di input. Seleziona la colonna che contiene i dati che desideri prevedere (ad esempio il costo unitario) e scegli un “fattore di smorzamento”.
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 attraverso l’automazione, poiché esistono numerosi metodi disponibili. Se desideri che tutti i modelli vengano confrontati con i tuoi dati, puoi considerare di inviarli a Lokad. Abbiamo un potente sistema informatico che “testa” tutti i modelli e seleziona solo quelli che funzionano meglio con i dati della tua azienda.