Metodi e formule per le previsioni con Excel - Software di ottimizzazione delle scorte

Metodi e formule per le previsioni con Excel


Home » Knowledgebase » Qui
di Guillaume Saint-Jacques, 2008-06-18 (ultima modifica: 2010-02-22)

Image
In questa guida, dedicata a manager e a chiunque voglia anticipare la domanda della clientela, passeremo al vaglio metodi di previsione elementari che possono essere applicati direttamente ai fogli di calcolo Microsoft Excel. Illustreremo la teoria con Microsoft Excel. Sono disponibili note aggiuntive per gli sviluppatori software che volessero riprodurre la teoria in un'applicazione personalizzata.


Previsioni: perché convengono

Produrre delle previsioni ti aiuta a prendere le decisioni più corrette e più redditizie dal punto di vista economico. Ecco un esempio.

  • Calcola la quantità ottimale di scorte

Il tempo è denaro. Lo spazio è denaro. Quindi, dovrai usare tutti i mezzi a tua disposizione per ridurre i livelli di stock (senza per questo incorrere in ammanchi, ovviamente).

Come? Con le previsioni!

Come facilitare le cose: etichette, commenti, nomi dei file

Man mano che i dati si accumulano nel tempo, le possibilità di confondersi o commettere errori aumentano. La soluzione? Mantenere l'ordine: usare etichette e commenti, o dare nomi corretti ai file, sono operazioni che possono risparmiarti parecchi problemi.

  • Dài sempre un'etichetta alle tue colonne. Usa la prima riga di ogni colonna per descrivere i dati che contiene.
  • Dati diversi, colonne diverse. Non inserire valori diversi (ad esempio, valori relativi ai costi e valori relativi alle vendite) nella stessa colonna: questo può confondere le acque, rendendo i calcoli e la gestione dei dati molto più difficili.
  • Dài a ogni file un nome chiaro e comprensibile. Si tratta di un'operazione semplice, che può rendere le cose molto più rapide: potrai individuare i file visivamente e trovarli facilmente attraverso la funzione di ricerca.
  • Usa i commenti.

Anche se non devi lavorare con grandi quantità di dati, entrare in confusione è facile, soprattutto con i dati meno recenti. Excel ti offre una soluzione vincente: i commenti.

Utilità dei commenti

Utilità dei commenti

Fai clic col tasto destro sulla cella che vuoi commentare, poi seleziona « inserisci commento ».

Puoi usare i commenti per:

  • spiegare il contenuto di una cella (es. costo unitario secondo le stime di Rossi)
  • lasciare un avviso a chi userà il file in futuro (es. Ho qualche dubbio su questo calcolo...)

Ottieni previsioni vendite avanzate con la nostra applicazione web di previsione delle scorte. Lokad è specializzata nell'ottimizzazione delle scorte attraverso la previsione della domanda. Il contenuto di questo tutorial è solo una delle tante funzionalità del nostro motore di previsione.

Per cominciare: un esempio semplice con i tracciati

I tuoi dati

I tuoi dati

Vediamo ora una prima previsione. Useremo questo file: Example1.xls. Puoi anche scaricare il file e ripetere il procedimento per conto tuo (i dati servono solo da esempio).

I nostri dati: Nella prima colonna, vediamo i dati sul costo unitario di prodotti simili (il costo unitario riflette la qualità del prodotto). Nella seconda colonna, vediamo i dati sulle vendite.

Cosa vogliamo sapere: Se vendiamo un altro prodotto, la cui qualità corrisponde a un costo unitario di €150, quante unità possiamo aspettarci di vendere?

Come troviamo la risposta: In modo abbastanza semplice. Vogliamo infatti trovare il rapporto matematico tra costo unitario e vendite, sulla base del quale elaboreremo la nostra previsione.

Per prima cosa, è sempre utile creare un grafico Excel, per dare uno sguardo ai dati. Vedere con i tuoi occhi è il modo migliore per identificare rapidamente i trend.

Per farlo, selezioniamo i dati, usiamo Inserisci > Grafico, poi scegliamo l'opzione XY (Grafico a dispersione). Vogliamo stimare le vendite come una funzione della qualità, quindi mettiamo il costo unitario sull'asse orizzontale e le vendite sull'asse verticale.

Fermiamoci un secondo a guardare cosa abbiamo ottenuto: il rapporto sembra crescente e lineare.

Per avere un'idea della forma esatta del rapporto, clicchiamo col tasto destro sul grafico e selezioniamo l'opzione "Tracciato".

Creare un tracciato

Creare un tracciato


Dobbiamo ora selezionare il rapporto che sembra descrivere meglio i nostri dati. Ancora una volta, ci affidiamo semplicemente ai nostri occhi: in questo caso, i punti sono quasi allineati, quindi scegliamo l'opzione "lineare". Più tardi potremo utilizzare altre impostazioni, più complesse ma anche più realistiche, come "esponenziale".

Abbiamo quindi un tracciato sul grafico. Se clicchiamo col tasto destro, appare il rapporto matematico esatto: y = 102,4x - 191,64.

Che vuol dire: Numero di unità vendute = 102,4 volte il costo unitario - 191,64.

Quindi, se decidiamo di produrre al costo unitario di €150, possiamo aspettarci di vendere 102,4*150 - 191,64 = 15.168 unità.

Tracciato lineare

Tracciato lineare


Abbiamo appena completato la nostra prima previsione.

Bisogna, però, fare attenzione: il software è comunque in grado di trovare un rapporto tra le due colonne, anche se il rapporto è in realtà molto debole. Di conseguenza, è necessario verificare che il rapporto sia solido:

  • Per prima cosa, guarda il grafico. Se i punti ti sembrano vicini al tracciato, come nell'esempio sopra, allora ci sono buone probabilità che il rapporto sia solido. Se invece i punti ti sembrano quasi messi a caso, o comunque sono lontani dal tracciato, allora dovrai fare attenzione, poiché la correlazione è debole e il rapporto stimato non è completamente affidabile.

Puntini sparsi: nessun rapporto evidente, nessuna previsione affidabile

Puntini sparsi: nessun rapporto evidente, nessuna previsione affidabile

I puntini

I puntini "hanno senso": le previsioni sono affidabili

  • Dopo aver guardato il grafico, puoi usare la funzione CORREL. Nel nostro caso, la funzione sarà: CORREL(A2:A83,B2:B83). Se il risultato si avvicina a 0, la correlazione è debole: possiamo dunque concludere che non esista un vero e proprio rapporto. Se il risultato si avvicina a 1, la correlazione è forte e il rapporto può essere considerato affidabile.

Esistono anche altri modi per verificare quanto sia forte la correlazione, ma su questi torneremo più tardi.

Ovviamente, le ultime fasi possono essere automatizzate: non devi segnarti il rapporto su un pezzo di carta e usare la calcolatrice. Ti basta Analysis Toolpak.

Previsioni con Analysis Toolpak

Prima di andare avanti, verifica che Excel ATP (Analysis Toolpak) sia installato. Per ulteriori informazioni, consulta la sezione Installare Analysis Toolpak.

Purtroppo, dati sulle vendite così perfettamente semplici, con un rapporto lineare, sono piuttosto rari nella vita reale. Vediamo cosa ci offre Excel per gestire situazioni e dati più complicati.

Spingersi più lontano: il tracciato esponenziale

Come potrai immaginare, un modello lineare non è sempre il più probabile. Anzi, i dati seguono spesso un modello esponenziale. Gran parte delle questioni economiche si basano su equazioni esponenziali (es. calcolo dell'interesse composto).

Per ottenere un tracciato esponenziale:

1) Guarda i tuoi dati. Traccia un grafico semplice e guardalo. Se seguono un'evoluzione esponenziale, dovrebbero avere questo aspetto:

Forma esponenziale perfetta

Forma esponenziale perfetta

Questo è il caso da manuale. Ovviamente, i dati non avranno mai un aspetto identico a questo. Se, però, i punti seguono più o meno questa distribuzione, dovresti considerare il tracciato esponenziale.

Usare i tracciati

Usare i tracciati

Come nell'esempio precedente, puoi sempre disegnare un grafico con i tuoi dati e scegliere un tracciato « esponenziale » invece che lineare. Poi usa l'equazione che vedi sullo schermo.

2) Fortunatamente, puoi fare tutto questo in modo diretto, usando Analysis Toolpak: inserisci tutti i tuoi dati in un nuovo foglio Excel, poi vai su Strumenti => Analisi Dati.

Installare Analysis Toolpak (ATP)

Analysis Toolpak (ATP) è uno strumento in aggiunta a Microsoft Excel, che non sempre è installato di default. Per installarlo, procedi in questo modo:

  1. Assicurati di avere con te il CD di Office. Excel potrebbe richiederti di inserire il CD per installare i file ATP.
  2. Apri un foglio Excel, vai sul menù Strumenti, poi seleziona Aggiunte. Spunta la prima casella della finestra, « Analysis Toolpak ».
  3. Inserisci il CD (se richiesto dal software).
  4. Fatto! Il menù « Strumenti » contiene molte funzionalità, compresa un'opzione « Analisi Dati », una di quelle che useremo più spesso.

Usare Analysis Toolpak (ATP)

... con la regressione lineare

Torniamo al nostro esempio lineare. Se i tuoi dati « sembrano » giusti (vedi illustrazione sopra), puoi usare ATP per avere una stima diretta della forma funzionale, senza passare per il « tracciato ».

Apri il foglio con i dati, apri il menù « Strumenti » e seleziona « Analisi Dati ». Si aprirà una finestra, in cui ti verrà chiesto quale tipo di analisi vuoi eseguire. Seleziona « Regressione lineare ».

A questo punto, devi indicare un « campo Y » e un « campo X ». Il campo Y indica cosa vuoi stimare (es. le vendite), mentre il campo X contiene i dati che pensi possano spiegare le vendite (es. il costo unitario). Nel nostro esempio (vedi example1.xls), le vendite sono nella colonna B, dalla riga 3 alla riga 90: dovrai quindi indicare « $B$3:$B$90 » come campo Y, e «$A$3:$A$90 » come campo X. Quando hai finito, clicca « ok ».

Apparirà un nuovo foglio, contenente « i risultati della regressione ».
Risultati di Analysis Toolpak, nel caso di regressione dei minimi quadrati ordinari

Risultati di Analysis Toolpak, nel caso di regressione dei minimi quadrati ordinari


I risultati più importanti sono quelli contenuti nella colonna « Coefficienti » in fondo al foglio. Il punto di intersezione è la costante, mentre la « variabile X » è il coefficiente di X (qui, il costo unitario). Quindi, troviamo la stessa equazione che avevamo trovato prima usando la funzione « tracciato ». Vendite = Punto di intersezione + coefficienteX * costo unitario. Vendite = -126 + 100 * costo unitario

Il foglio contiene anche un numero importantissimo, che ti dà la misura di quanto sia affidabile la stima: è la « R al quadrato ». Se si avvicina a 1, allora la stima è buona e l'equazione che hai trovato rappresenta in modo sufficientemente adeguato i tuoi dati. Se si avvicina a 0, allora la stima non è delle migliori; in questo caso, dovresti provare un altro tipo di regressione (vedi la regressione esponenziale più avanti).

Questo metodo è forse più rapido della tecnica con il « tracciato », ma è un po' più tecnico e meno immediato a livello visivo. Se non vuoi perdere tempo a osservare i tuoi dati, assicurati almeno di controllare il valore « R al quadrato ».

... con l'andamento esponenziale

Se la regressione lineare non è la più indicata (ad esempio se ottieni un valore basso di R al quadrato, come 0,1), potresti provare con l'andamento esponenziale.

Avvia Analysis Toolpak e procedi come al solito: apri il foglio con i tuoi dati, vai sul menù « strumenti » e seleziona « Analisi Dati ». Si aprirà una finestra, in cui ti verrà chiesto che tipo di analisi vuoi condurre.

Per ottenere un andamento esponenziale, scegli « esponenziale ».

Ricorda che Excel stavolta ti chiederà un solo campo di input. Seleziona la colonna che contiene i dati per cui vuoi eseguire una previsione (es. costo unitario), quindi scegli “fattore di smorzamento”.

Come faccio a sapere quale modello devo scegliere?

Non devi provare tutti i metodi di previsione per sapere quello che fa più al caso tuo. I metodi a disposizione sono tanti, quindi conviene affidarsi a una selezione automatica. Se vuoi applicare tutti i modelli ai tuoi dati, puoi anche inviarli a Lokad. Abbiamo un potente sistema informatico che “testa” tutti i modelli e seleziona solo quelli più adatti ai tuoi dati (scopri tutto quello che Lokad ha da offrire).