Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Problemi e soluzioni nel Reporting delle vendite
Vendere è importante, ma sapere cosa si sta vendendo è senz’altro meglio.
Il Reporting delle vendite è la chiave del successo, soprattutto per quelle aziende che si
trovano in una fase critica della loro esistenza quale, per esempio:
• il lancio di un nuovo prodotto;
• la comparsa di un nuovo competitor nel mercato;
• l’entrata in un nuovo paese/mercato;
• l’inizio di attività di importazione parallela tra un paese e l’altro.
In tutte queste fasi occorre tenere sotto stretto controllo le vendite in modo da evidenziare al
Management i trend, le configurazioni e gli effetti che prendono lentamente forma nel mercato.
Fare un buon Reporting, pertanto, diventa cruciale nella misura in cui il Management è in
grado di interpretare i segnali e intraprendere tempestivamente delle azioni di Marketing
mirate a promuovere un certo andamento o arginare un determinato effetto.
La realizzazione di molti obiettivi aziendali definiti, per esempio, in termini di profitto, quota di
mercato o numero di clienti sono intrinsecamente legati al raggiungimento di alcuni obiettivi di
vendita.
In questo capitolo prenderemo in considerazione alcuni aspetti fondamentali riguardanti il
Reporting delle vendite, sia sotto il profilo concettuale, sia sotto quello pratico che si manifesta
con la creazione di un modello attraverso Excel che risponda alle esigenze contingenti.
I temi che tratteremo sono riassumibili come segue:
• misurare la performance in rapporto agli obiettivi
• individuare trend e anomalie nell’andamento dei ricavi
• tecniche di budgeting e forecasting
• adozione di un modello rolling quale strumento di controllo
• gestione dei dati di sell-in e sell-out in realtà multinazionali e multiprodotto
Sotto il profilo tecnico della realizzazione dei modelli Excel, invece, evidenzieremo alcuni dei
problemi tipici di si trova a dover gestire i dati di vendita confezionando Report da presentare
al Top Management.
Tra questi elementi troviamo i seguenti:
• creare un Reporting settimanale partendo dalla data di vendita
• trovare i dati a prescindere dalla loro disposizione nei fogli (orizzontale o verticale)
• unire dati provenienti da fonti diverse facendoli confluire in un Report esteticamente
presentabile
• calcolare una media mobile automaticamente, anche quando i dati sono a cavallo tra un
anno e l’altro
Si è deciso di presentare tutti questi temi attraverso l’introduzione di un Case History che farà
da base di partenza per sviluppare tutte aree introdotte nella spiegazione del caso. Nel
presentare i diversi modelli, pertanto, faremo riferimento al modello di Reporting adottato nel
Case History “Le pietre di SreeBeD”.
Per leggere e comprendere correttamente questo capitolo è necessario possedere una buona
conoscenza delle funzioni spiegate nel capitolo sugli strumenti di lavoro di questo libro.
Case History: le pietre di SreeBeD
Sreebed Plc è una società americana che vende pietre preziose in tutto il mondo attraverso
diverse modalità a seconda del paese.
Le pietre di Sreebed hanno un alto profilo e sono conosciute per l’eccellenza qualitativa
raggiunta grazie a un severo monitoraggio di tutte le fasi di estrazione, produzione e
raffinazione.
La società è riuscita negli anni a costruire una forte brand awareness attraverso campagne
pubblicitarie e scelte di marketing estremamente selettive e accurate.
Le pietre che la società vende sono sostanzialmente cinque:
• Ametiste (Amethyst)
• Smeraldi (Emeralds)
• Rubini (Ruby)
1/24
Excel in azienda – Analisi delle vendite
•
•
Edizioni FAG Milano
Topazi (Topaz)
dal 2005 la società inizierà anche la produzione e distribuzione di Perle (Pearls)
Per quanto riguarda la presenza nei diversi paesi, non esiste una regola precisa, ma è possibile
definire tre distinte modalità:
• in alcune nazioni la società ha una creato filiale o un ufficio di rappresentanza facente
capo legalmente alla Holding
• in quasi tutti i paesi extraeuropei si è deciso di esportare direttamente a grossisti locali
che provvedono alla distribuzione fisica nelle gioiellerie
• in alcuni paesi europei la società ha stretto partnership/joint ventures con alcune
società che si occupano della lavorazione delle pietre che vengono inserite in gioielli più
complessi ad alto margine. Questi partner non si occupano solo della lavorazione, ma
anche degli aspetti relativi alla promozione e distribuzione dei gioielli nei loro paesi. I
paesi con partnership in atto sono:
o Paesi Scandinavi (Svezia, Norvegia, Finlandia, Islanda)
o Olanda
o Francia
o Svizzera
o Germania
o Russia
o Polonia
o Slovenia
o Ungheria
o Repubblica Ceca
o Repubblica Slovacca
o Paesi Baltici (Lettonia, Latvia, Lituania)
Questo significa che tutte le possibili combinazioni Prodotto/Paese sono le seguenti:
Emerald
Country/Produc Amethys
t
Opals
s
Ruby
Topaz
Pearls
# t
1 Czech Republic
2 Denmark
3 Estonia
4 Finland
5 France
6 Germany
7 Hungary
8 Iceland
9 Latvia
10 Lithuania
11 Netherlands
12 Norway
13
14
15
16
17
18
Poland
Russia
Slovak Republic
Slovenia
Sweden
Switzerland
Quindi 18 x 5 = 90 combinazioni. A questi livelli, il rischio di errore nell’elaborazione dei dati e
la complessità fanno sì che l’Office Automation risulti praticamente imprescindibile.
Mr Sreebed, è convinto che il business delle partnership sia quello più profittevole e ha deciso
di creare un team che segua da vicino le dinamiche di questi mercati analizzando
costantemente le vendite.
2/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
La società Partner sono tenute a fornire i dati di Sell-Out, cioè i dati di vendita dal Partner alle
gioiellerie, con cadenza mensile. Il formato dei dati è molto semplice: si tratta di un Report
disposto su dodici colonne che rappresentano i mesi dell’anno.
Dalla contabilità e il Controllo di Gestione arrivano invece i dati di Sell-In, cioè le vendite dalla
società ai diversi Partner. Il formato di questi dati è molto più dettagliato rispetto ai dati forniti
dai partner e la logica è completamente diversa, poiché i dati si sviluppano in verticale e per
ogni singola transazione viene riportata data, prezzo e altre informazioni.
Il Direttore Commerciale, Mr Kaiser Soze, ha ricevuto l’incarico di indicare i primi 5 paesi nei
quali vale la pena aumentare gli investimenti per sollecitare il mercato. Per fare questo, dovrà
mettere insieme i dati di Sell-In, Sell-Out e Stock di prodotto creando un Report che evidenzi
l’andamento settimanale, mensile e confronti i dati con l’anno precedente.
Al fine di ridurre al minimo il margine del Partner e aumentare le quantità vendute ai tempi
della sigla del contratto si era deciso di includere delle Free of Charge (FOC – quantità gratuite)
in misura variabile dal 2% al 5% su ogni ordine di alcuni Partner. Ora, però, il business è
cresciuto e sarà necessario trovare una soluzione alternativa.
Mr Sreebed vuole vedere solamente i dati settimanali, ma, dal momento che la società è
americana, essi devono essere riportati sia in Euro che in Dollari.
Nelle pagine che seguono esporremo i modelli utilizzati per tenere sotto controllo il business.
I fogli di input
In questo testo abbiamo più volte insistito sull’importanza di separare i fogli di input da quelli
di output. Anche in questo caso abbiamo seguito questa filosofia che porta ad avere maggior
ordine e minori possibilità di errore.
Come assunto di base, i fogli di input contengono dati manuali che possono essere inseriti –
quasi – da chiunque, mentre i fogli di output sono fondamentalmente automatici e possono
essere gestiti solo da persone che conoscono a fondo il programma.
Spesso, inoltre, abbiamo usato un colore diverso per i dati da inserire manualmente (anche se
in una stampa in B/N può non essere così evidente). Questo serve al fine di enfatizzare
maggiormente le differenze tra celle a input manuale e celle contenenti formule, quindi
automatiche.
Purtroppo non è sempre possibile mantenere una netta distinzione tra dati manuali e formule:
come vedremo spesso è utile fondere alcuni dati per creare delle stringhe di ricerca univoche
(per esempio prodotto/paese/periodo). In tal senso, assume un ruolo determinante l’abitudine
a evidenziare (per esempio attraverso i colori) i dati manuali da quelli derivanti da formule.
I fogli di input che presenteremo per questo modello sono:
• il foglio dei dati provenienti dalla contabilità sulle vendite SELL-OUT (cioè dall’azienda
all’intermediario)
• i dati di mercato provenienti mensilmente dai diversi partner (chiamati SELL-IN cioè
vendite dall’intermediario al mercato)
• il foglio del budget che ricalca il modello dei dati di mercato
• il foglio DB che contiene tutte le tabelle di relazione tra i dati presenti nel modello
3/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
GESTIRE LA COMPLESSITÀ: IL FOGLIO “DB”
Uno dei problemi nella gestione di un business multiprodotto in più paesi con N varianti è che
possono verificarsi mutamenti che rischiano di minare la stabilità del modello Excel che
abbiamo
creato
(Figura
4.1).
Figura 4.1 Per creare una cartella realmente efficiente è necessario pensare a un foglio dove
vengano raccolte informazioni e parametri comuni a tutta la cartella di lavoro
Per esempio, potremmo vendere un prodotto in più, oppure una certa area, da un certo
momento in poi, viene considerata in un modo piuttosto che in un altro (per esempio, dal 2004
non ha più senso parlare di Est Europa quando si parla di paesi come la Repubblica Ceca o la
Lettonia).
Per tutti i mutamenti che siamo in grado di prevedere o, comunque, come norma di massima,
è necessario creare un foglio che abbia tutte le tabelle di abbinamento possibili. Per citarne
solo alcune:
• abbinamento codice prodotto / famiglia di prodotto
• abbinamento paese / area di appartenenza
• abbinamento area / account
La lista potrebbe essere decisamente molto più lunga.
Il senso di creare un foglio del genere è che tutte le formule del foglio Excel dovranno fare
riferimento alle tabelle create nel foglio informativo. Per accogliere una modifica o un’aggiunta
basterà sistemare il foglio informativo e il cambiamento sarà effettivo in ogni singolo foglio
della cartella.
Il foglio informativo è, di fatto, la consolle dalla quale impostare le informazioni principali o
modificare i parametri rilevanti del file a cui stiamo lavorando. Facendo un parallelismo con
Access, questo foglio corrisponde alle diverse tabelle che vengono create per fare le cosiddette
correlazioni.
Abbiamo creato un foglio che si chiama DB tiene conto delle seguenti informazioni:
• tabella nome prodotto / codice prodotto (range A2:C7): qui sono contenuti gli
abbinamenti tra il codice sintetico usato nella cartella e il significato di questo codice.
Nel nostro esempio abbiamo solo 5 prodotti, ma l’elenco può essere anche molto lungo
• impostazione del mese (cella B11) e dell’anno (cella B11): i calcoli di ogni singola
cella della cartella partono dall’ipotesi che ci troviamo alla fine del mese e nell’anno
specificato da queste due celle. In altre parole per cambiare mese e anno, bisogna per
forza agire su queste due celle. Nel nostro esempio, per cambiare mese, abbiamo usato
la funzionalità Convalida…, disponibile dal menu Dati (Figura 4.2)
4/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Figura 4.2 Attraverso la funzione Convalida è possibile lasciare che l’utente scelga un dato
all’interno di un limitato numero di opzioni
•
•
•
•
tabella numero settimana nel mese / range calendariale / tasso di cambio vs $
(A15:C19): un mese può avere da un minimo di 4 a un massimo di 5 settimane: la
prima settimana del mese, pertanto, corrisponderà a una data di inizio e una data di
fine; la seconda avrà una data di inizio e fine successive alla prima e così via. In
quest’area del foglio, a ogni numero progressivo viene abbinato un testo che riporta la
data di inizio e fine della settimana e il tasso di cambio applicato in quel periodo
temporale
data di partenza e di fine per il calcolo della media mobile (celle B27e B29): una
volta impostato il mese di riferimento queste celle calcolano il range di date sul quale
calcolare la media mobile
data di partenza / range settimanale (colonne F:I): per ogni data di partenza
settimana viene abbinato un certo range. In altre parole, partendo da una data con
questa tabella siamo in grado di individuare a quale settimana appartiene e, dalla
settimana, deriviamo la data che stabilisce il limite inferiore e superiore della settimana
trasposizioni mensili e riferimenti (da L2 a P13): è utile avere la possibilità di
passare da un numero che indica un mese a un riferimento testuale e viceversa. Come
vedremo più oltre questa possibilità sarà sfruttata ampiamente nella cartella. Una
tabella come quella in L2:P13 è estremamente utile quando si devono convertire
riferimenti testuali in date e viceversa (Figura 4.3)
5/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Figura 4.3 Il problema della gestione e interpretazione delle date non è banale e occorre
prevedere la possibilità di convertire una data testuale in un numero e viceversa
•
•
stock per paese e prodotto (colonne Q:U): come è noto a chi conosce un po’ di
contabilità, il magazzino è un valore patrimoniale e cambia da un periodo contabile
all’altro. Per calcolare lo stock a una certa data dobbiamo conoscere i movimenti di
acquisto e di vendita e lo stock all’inizio del periodo. Qui vengono riportati i dati di stock
per anno, paese e prodotto
abbinamenti country / area (colonne V:W): nel nostro esempio i paesi dell’area
scandinava sono stati raggruppati in un’unica area (Nordic), ma il concetto di
raccogliere dei singoli paesi o regioni in aree è un tema sempre molto importante
quando si analizzano le vendite. Questa semplice tabella di conversione permette di
mantenere sempre aggiornate le informazioni relative ai raggruppamenti ovunque si
trovino nel foglio
Nell’impostazione del mese (cella A11) e dell’anno (cella B11) si è scelto di usare
l’abbreviazione inglese del mese. Inoltre si è usata la funzione Convalida Dati. Per utilizzare
questa funzionalità basta seguire questi passi:
1) selezionare una cella o un Range di celle
2) selezionare Convalida Dati dal menu Dati
3) scegliere Elenco dal menu a tendina Consenti
4) nel campo Origine digitare i soli valori ammessi per quella cella o quell’intervallo
oppure un Range dove sono contenuti i dati da mostrare (Figura 4.4)
Figura 4.4 Dalla finestra di dialogo Convalida Dati è possibile determinare con esattezza quali
sono i valori ammissibili per una cella o per un intervallo di celle
Con Convalida Dati gli utenti dovranno scegliere solo uno degli item proposti e non potranno
inserire dati non riconosciuti dalle nostre formule.
Nella cella C11 troviamo un’applicazione di quanto dicevamo a proposito delle trasposizioni
mensili. La formula:
=CERCA.VERT(A11; $M$2:$N$13; 2; FALSO)
cerca il valore inserito come mese e riporta il valore numerico corrispondente. Questo valore
esprime il numero progressivo relativo a un certo mese (per esempio in un calendario gennaiodicembre, febbraio è uguale a 2; in un calendario aprile-marzo febbraio sarebbe uguale a 11).
Il contenuto della cella C11, come vedremo, serve per calcolare la data di partenza e di fine su
cui calcolare il MAT (moving average total), cioè la media mobile (Figura 4.5).
6/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Figura 4.5 Attraverso l’uso della funzione CERCA.VERT() è possibile convertire
istantaneamente una data in formato testuale in un numero che indica il mese e può essere
usato per i calcoli nel resto della cartella
Nel range A15:C19 vengono gli intervalli di giorni all’interno di una settimana. In molte aziende
le analisi settimanali si fanno semplicemente riportando i dati riferiti alla “settimana 27” o alla
“settimana 45”. È ovvio che, sapendo che un anno ha 52 settimane, siamo in grado di sapere a
quali date fanno riferimento, ma dire la settimana “dal 15 al 22 settembre” è decisamente
meglio.
Ogni mese ha, al massimo, 5 settimane incomplete (se fossero complete avremmo 5 x 7 = 35
giorni in un mese). Nel range A15:A19 vengono riportati i dati relativi al numero della
settimana all’interno di un certo mese (cioè la prima settimana del mese sarà 1, la seconda 2 e
così via). La combinazione concatenata del numero di settimana, mese e anno viene cercata
nel range F:I con la funzione cerca vert e, nelle celle B15:B19 viene riportato il risultato. In
B15 troviamo:
=CERCA.VERT ($A15 & $A$11 & ($B$11 - 2000); $F:$I; 3; FALSO)
La combinazione di Numero della settimana (A15) Mese (A11) e l’anno dopo il 2000 (B11 –
2000) restituisce una stringa che viene cercata nel range F:I. Per esempio la seconda
settimana di settembre 2005 sarà codificata come: “2Sep5”.
In realtà la formula fa un doppio check per vedere se la formula CERCA.VERT() produce un
errore: se ricorre questo caso, infatti, la formula restituisce un “N/A”, se invece non ci sono
errori parte la ricerca. La formula completa (Figura 4.6) è quindi:
=SE (VAL.ERRORE (CERCA.VERT ($A15 & $A$11 & ($B$11 - 2000); $F:$I; 3; FALSO)); "N/A";
CERCA.VERT($A15 & $A$11 & ($B$11 - 2000); $F:$I; 3; FALSO))
7/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Figura 4.6 La funzione CERCA.VERT() si rivela preziosa anche per creare una tabella che
suddivide il mese in intervalli di giorni che costituiscono le settimane
Nel range C15:C19 viene invece riportato il tasso di cambio che, sempre attraverso l’utilizzo
della funzione CERCA.VERT(), viene pescato dal range F:I. In questo range, dicevamo, sono
presenti anche le date di inizio e fine settimana per tutto l’anno. Rimandiamo al paragrafo “Il
Reporting settimanale” per i dettagli sulla costruzione delle date qui riportate.
Il calcolo della data di partenza e di fine per il MAT è un po’ complesso, pertanto spezziamo la
formula in due pezzi e usiamo i Nomi per comprendere meglio le formule. I nomi che definiamo
nel foglio sono:
• mese = cella C11
• anno = cella B11
• mat = cella B24
Il nome mat definisce il numero di mesi sui quali si vuole calcolare la media. A seconda del
business dell’azienda ha senso considerare 3, 6 o 12 mesi di dati. Nell’ultimo caso il MAT può
assumere il significato di Moving Annual Total, cioè il totale di un anno mobile (in effetti in
letteratura l’acronimo MAT è usato sia come Moving Average Trend che come Moving Annual
Total).
Immaginiamo di usare pertanto 3 mesi come valore su cui calcolare la media e immaginiamo di
essere alla al 28 febbraio 2004. Nella cella B24, quindi scriveremo “3”.
Utilizzando la formula FINE.MESE() (Figura 4.7) è possibile calcolare la data di arrivo del
calcolo della media. In B29 troviamo:
=FINE.MESE("01/"&mese&"/"&anno;0)
Figura 4.7 La funzione FINE.MESE() è disponibile solo attivando il componente Strumenti di
Analisi, selezionando Componenti Aggiuntivi dal menu Strumenti
La formula calcola la fine del mese determinato dalla fusione di 01/ con il valore del mese
determinato nella cella C1 (2) e con il valore dell’anno determinato dalla cella B11 (2004).
Otteniamo 29/02/2004, perché il 2004 era bisestile.
Ottenuta la data di arrivo, possiamo calcolare il limite inferiore in modo semplice usando,
anche in questo caso, la formula FINE.MESE(). In B27 troviamo:
=FINE.MESE(B29; -mat)
8/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
In B29 abbiamo appena calcolato una data. Da questa data sottraiamo un numero di mesi pari
al valore assunto dal nome “mat”, cioè della cella B24 (Figura 4.8), quindi “3”. Togliendo 3
mesi dalla data che abbiamo determinato nella cella B29, pertanto, otteniamo la data di
partenza per il calcolo della media mobile.
Figura 4.8 L’uso dei Nomi al posto dei riferimenti agevola la scrittura di funzioni e consente
agli utenti che non hanno contribuito alla costruzione del foglio di comprendere meglio come
funzionano le formule
Il range di colonne da F a I non presenta particolari problemi di interpretazione e viene trattato
nel paragrafo dal titolo “Il Reporting settimanale”.
Nell’intervallo L:P contiene una tabella di conversione tra numeri e mesi. In altre parole, se
l’anno fiscale della società va da marzo ad aprile, il mese 1 sarà marzo, il mese 3 maggio e
così via. Per esigenze di traduzione in questo caso abbiamo preferito inserire un testo con
l’abbreviazione del mese in inglese, ma avremmo potuto inserire qualunque altro testo utile a
identificare un certo mese all’interno di un anno.
L’elemento interessante di questo intervallo è l’indicazione dei riferimenti mensili rispetto a
Stock calcolato (etichette INV Pos, colonna O) e dato di Sell Out (etichetta S-OUT, colonna
P). Come vedremo in seguito, è possibile utilizzare un elemento testuale di una cella come
indirizzo nel quale cercare i dati (Figura 4.9). Per esempio, nella cella P2 troviamo la stringa
“D:D”. Questa stringa di testo verrà in seguito utilizzata per “prelevare” dei dati dal foglio
InMKT precisamente nella colonna “D:D”. Per fare questo utilizzeremo la funzione
INDIRIZZO().
Figura 4.9 È possibile utilizzare alcune complesse funzioni di Ricerca e Riferimento che
convertono un testo scritto in una cella in un valido riferimento utilizzabile da un’altra formula.
9/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Per questo vale la pena di indicare dove si trovano alcune informazioni nel foglio in una tabella
come quella in figura
I range Q:T e V:W, che contengono rispettivamente i dati di stock di fine anno per ogni
chiusura d’anno e il raggruppamento paesi/aree geografiche di interessi, non presentano
particolari problemi di interpretazione. Essi vengono usati come riferimento in diversi fogli della
cartella di lavoro.
I DATI DI VENDITA CONTABILI (SELL-OUT): IL FOGLIO “INVOICES”
In moltissime aziende capita che i dati in possesso della funzione commerciale siano diversi da
quelli registrati in contabilità.
Questo capita poiché le regole contabili che ciascuna azienda adotta e segue sono diverse dalle
logiche che le persone senza background contabile ritengono accettabili. Ecco alcuni esempi di
possibili discrepanze tra dati di vendita contabili e commerciali:
• in alcune aziende americane o inglesi vengono considerate come vendite del mese gli
ordini pervenuti e inseriti nel sistema entro un certo numero di giorni dopo la data di
chiusura del mese. Secondo questa regola un ordine inserito, per esempio, il 3 marzo è
considerato una vendita del mese di febbraio. Questa è una regola molto aggressiva di
aziende di paesi dove il bilancio può essere redatto in modo molto meno restrittivo
rispetto alle regole italiane. Per chi non ne cogliesse la – dubbia – logica, diremo che la
sua razionale è che gli ordini pervenuti qualche giorno dopo la chiusura del mese sono
stati “provocati” nel mese precedente. Tipicamente questa regola vale per le aziende
che hanno un processo di produzione molto veloce oppure che dispongono di uno stock
sufficiente a evadere gli ordini rapidamente
• in altre aziende il concetto di vendita è legato alla spedizione. In molti casi i criteri sono
restrittivi e le vendite sono considerate tali se la spedizione avviene entro il penultimo
giorno del mese
• nelle aziende dove esistono problemi di competenza contabile, la vendita contabile
viene spalmata lungo il periodo temporale a cui fa riferimento il contratto stipulato con
il cliente. Può quindi capitare che la quota mensile di un servizio reso a un cliente su
due mesi venga considerato dalla contabilità solo al 50% e dall’ufficio commerciale al
100%
Esistono numerose casistiche di scontro dialettico tra “commerciali” e “contabili”. Sta di fatto
che, purtroppo o per fortuna, il dato di vendita contabile è il dato ufficiale. Qualunque altro
dato di vendita diverso da questo non verrà riportato nel bilancio ufficiale e non sarà oggetto di
valutazione da parte di soggetti esterni o interni all’azienda.
Per questo motivo il modello che costruiamo prende le mosse dai dati provenienti dalla
contabilità.
I dati presenti in questo foglio sono frutto di un’estrazione dal sistema informativo aziendale,
che, nel caso specifico di Sreebed è JDEdwards. Più che concentrarci sull’Erp usato, però, è
bene capire quali dati devono essere presenti e vedere a quale colonna del modello che
abbiamo creato fanno riferimento. Questi sono:
• data di spedizione (colonna A): determina il periodo temporale di una singola
transazione
• codice cliente (colonna B): è il codice univoco che viene attribuito al cliente dal
sistema informativo. Assieme al numero di partita IVA costituisce il sistema più sicuro
per evitare di confondere un cliente con l’altro. Rispetto al codice IVA, tuttavia,
presenta il vantaggio che, a prescindere da eventuali cambi di ragione sociali o di
Partita IVA, esso rimane sempre lo stesso (Figura 4.10)
10/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Figura 4.10 Nei sistemi informativi complessi il nome di un cliente corrisponde a un codice che
spessissimo è diverso dalla Partita Iva del Cliente. Capita sovente, infatti, che una società operi
un cambio nella figura giuridica e vi sia una modifica della Partita Iva
•
•
•
•
•
•
•
numero d’ordine (colonna C): è un codice del cliente che permette di identificare
l’ordine. Nei dialoghi con il cliente, questi farà sempre riferimento al suo numero
d’ordine, mai al numero che gli attribuiremo noi
numero fattura (colonna D): è un numero progressivo attribuito alla fattura e
permette di identificare internamente l’intera transazione
nome del cliente (colonna E): per non dover risalire al cliente partendo dal numero,
è utile riportare anche il nome del cliente
paese (colonna F): è il campo che riporta l’area geografica. Eventuali raggruppamenti
geografici vengono aggiunti successivamente con una funzione di tipo CERCA.VERT()
note (colonna G): campo descrittivo che riporta eventuali istruzioni speciali relative a
un certo ordine
codice di prodotto (colonna H): è un codice sintetico che indica la famiglia di
prodotto o il prodotto (in caso di business con prodotti semplici). Nel nostro esempio il
codice prodotto è costituito da una sigla di due lettere, ma la codificazione può essere
anche molto complessa. Nel capitolo sulle funzioni abbiamo fornito qualche esempio a
riguardo parlando delle funzioni di testo
prezzo di vendita (colonna I): identifica il prezzo al quale è avvenuta una singola
transazione. Per le transazioni in cui vi sono delle merci vendute a scopo promozionale
(FOC) il prezzo viene riportato uguale a zero (Figura 4.11)
Figura 4.11 Se una transazione avviene a titolo gratuito, dovremo poter essere in grado di
aver traccia delle quantità cedute e di vedere che si è trattato di un omaggio osservando il
prezzo che deve essere, in questo caso, uguale a zero
11/24
Excel in azienda – Analisi delle vendite
•
•
•
Edizioni FAG Milano
tasso di cambio vs € (colonna J): è il tasso di cambio usato in caso di transazione in
valuta fuori UE. In questo cambio è sempre l’euro che quota le altre valute (per
esempio, se in questa colonna troviamo un valore di 1,3, significa che 1,3 monete della
valuta quotata valgono 1 euro). Quando troviamo 1, invece, significa che la valuta è
l’euro
quantità (colonna K): determina il volume di ogni singola transazione
valore (colonna L): è dato dalla moltiplicazione delle quantità per il prezzo
trasformato in euro
Il foglio che abbiamo predisposto si chiama Invoices e contiene tutti questi campi
esattamente nell’ordine in cui sono stati presentati poche righe fa.
In aggiunta a questi campi vi sono dei calcoli automatici propedeutici ai fogli di analisi che
incontreremo nei prossimi paragrafi. Vediamo questi campi uno per uno assieme allo scopo di
capire il motivo per il quale sono stati creati.
Country Group (colonna M): per alcuni paesi è possibile che esista un unico partner. Per
questo motivo abbiamo creato una colonna che raggruppa i paesi per una singola area. Questa
situazione si può verificare anche quando il Management decide che un certo gruppo di
paesi/regioni sia gestito da un capoarea: in questo caso si dovrà utilizzare una tabella che
associ ogni singolo paese a un certo owner. Nel nostro caso, la combinazione, paesi/partner si
trova nel foglio DB, colonne V e W. Il foglio DB, come abbiamo visto, è fondamentale e
contiene tutte le informazioni di base e le associazioni che permettono all’intera cartella di
lavorare correttamente. La formula (Figura 4.12) che utilizziamo in M2 per ricreare
l’associazione è una funzione CERCA.VERT():
=CERCA.VERT(F2; DB!$V:$W; 2; FALSO)
Figura 4.12 L’associazione tra un paese/regione e una macro area è un tipico problema di
natura commerciale. Con CERCA.VERT() il problema è facilmente risolvibile
Nella colonna F sono contenute le informazioni relative alla country. Queste vengono cercate
nel range V:W del foglio DB. L’approssimazione di CERCA.VERT() in questo caso non è
ammessa: se c’è un errore di tipo #N/D, dobbiamo essere in grado di rilevarlo. Quando si usa
la funzione CERCA.VERT() è importante ordinare i dati, specie se l’approssimazione è su
FALSO.
Wk_All (colonna N): è il campo che serve per rilevare le vendite settimanali a prescindere
dal paese. Per costruire un Reporting settimanale è necessario che nel database vi sia un
campo
che,
per
ogni
singolo
record
o
riga,
riporti
la
combinazione
prodotto/settimana/mese/anno. Come dicevamo in apertura, occorre tenere conto anche delle
unità spedite gratuitamente (foc). Per questo la formula ne tiene conto (Figura 4.13) rilevando
il prezzo di vendita: se questo è uguale a zero, la merce è stata venduta gratuitamente.
Analizziamo la formula in dettaglio che troviamo in N2 del foglio Wk_All:
=H2 & CERCA.VERT(A2;DB!G:H;2;VERO) & SE(I2=0; "foc"; "") & ANNO(A2)
12/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Figura 4.13 Nella colonna N viene creata una stringa di ricerca utile ai fini del reporting che
costruiremo nelle pagine seguenti. L’uso della funzione CONCATENA() nella versione ridotta
(“&”) è fondamentale
Questa funzione crea delle stringhe univoche formate da prodotto (colonna H), range
settimanale corrispondente alla data specificata nella colonna A, testo “foc” se il prezzo
(colonna I) è uguale a zero e anno riferito alla colonna A. Da notare che la funzione
CERCA.VERT() utilizza l’approssimazione come VERO (fare riferimento al capitolo sulle
funzioni).
Per esempio, la cella N2 produce come risultato la stringa “AM1 - 3 Jan2003” che significa
prodotto “AM”, quindi Ametiste, range settimanale 1-3 gennaio del 2003. Non specifica la
country, ma questo è un passo successivo.
Wk_ByC (colonna O): questa colonna contiene la fusione tra la colonna N e la colonna della
country (M). Molto semplicemente in O2 troviamo la formula:
=M2 & N2
ByMonth (colonna P): in questo campo troviamo i dati necessari a creare una stringa che
identifichi in modo univoco il mese, l’anno, il prodotto e il paese. In P2 troviamo:
=CERCA.VERT (MESE(A2); DB!$L$2:$M$13; 2; FALSO) & ANNO (A2) & H2 & M2
Il mese della data inserita nella colonna A viene cercato nel range L2:M13 del foglio DB e
viene restituito il mese abbreviato in inglese (per esempio, per 1 viene restituito jan, cioè
january). Questa soluzione può essere molto comoda per utilizzare un Reporting in lingua
inglese, ma non solo: pensiamo, per esempio, a tutte quelle situazioni in cui il primo mese
dell’anno fiscale non inizia con gennaio. Al mese abbreviato in inglese viene concatenato l’anno
(sempre nella colonna A), il prodotto (colonna H) e il paese (colonna M).
MAT (colonna Q): questa colonna non viene utilizzata nel resto della cartella, ma costituisce
un buon esempio di un modo alternativo per consentire il calcolo del MAT, cioè del Moving
Average Trend o Media Mobile (Figura 4.14). Nella cella Q2 troviamo:
=SE (E (A2<=DB!$B$29; A2>DB!$B$27); P2; "")
13/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Figura 4.14 La colonna Q contiene una formula che esemplifica in modo chiaro come utilizzare
la funzione SE() combinata con la funzione E()
In pratica se la data (colonna A) si trova tra la data specificata in B29 e quella specificata in
B27 del foglio DB, allora la formula restituisce il contenuto della cella P2, cioè della stringa
utile per cercare i dati per mese. In questo modo è possibile sommare solo i dati che si trovano
all’interno del range di date specificato nel doglio DB. L’intelligenza nel calcolo della date
risiede nel foglio DB, pertanto invitiamo a leggere attentamente il paragrafo “
14/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Gestire la complessità: il foglio “DB””.
IL FOGLIO DEI DATI DI SELL-IN E IL FOGLIO DEL BUDGET
Nel foglio che abbiamo chiamato InMKT vi sono riportati i dati che i partner dei diversi paesi
riportano.
Questi dati sono divisi per area geografica, anno, prodotto e mese. È interessante notare che i
mesi sono determinati da una specifica colonna, mentre nel foglio “Invoices”, il periodo
temporale era determinato dalla data contenuta in una specifica colonna (e sempre quella).
Vediamo il contenuto di ogni campo:
• anno (colonna A): in questo campo viene riportato l’anno a cui fa riferimento la vendita
• area (colonna B): è il raggruppamento di paesi o area geografica
• prodotto (colonna C): è riportato per intero il nome del prodotto
• mese 1, 2, 3… (colonne D-O): in queste colonne vengono riportati i dati in unità per
ogni mese
• merge (colonna P): in questa colonna si fondono i dati della colonna A, B e C
Il prodotto viene cercato nel range B2:C6 del foglio DB e, al posto del nome prodotto esteso,
viene riportato il codice (Figura 4.15). Nella colonna P, pertanto, troviamo la seguente
formula:
=A2 & CERCA.VERT($C2; DB!$B$2:$C$6; 2; FALSO) & B2
Figura 4.15 La formula della colonna P del foglio InMKT contiene un altro buon esempio
dell’uso della funzione CERCA.VERT()
Il foglio del Budget è relativo al budget di Sell-IN e si chiama BDG. In questo foglio troviamo la
medesima struttura del foglio InMKT, pertanto non è necessario scendere in ulteriori dettagli.
I fogli di output
In questa sezione parliamo di Reporting vero e proprio.
fogli che verranno presentati sono
quelli che possiamo presentare al Management.
La caratteristica che li accomuna è che in questi fogli non vi sono dati di input da parte
dell’utente. I fogli, infatti, sono quasi totalmente automatici e l’utente è chiamato solo in pochi
casi a specificare quale country desidera analizzare.
Questa differenza rispetto ai fogli di input rende il Report quasi “a prova di bomba”, poiché è
possibile impedire agli utenti di creare problemi bloccando le celle.
I Report che proporremo in questa sede sono:
• il Report delle vendite settimanali totali per linea di prodotto
• il Report delle vendite settimanali per linea di prodotto e per paese
• il Report sui dati di vendita Sell-In e Sell-Out nell’anno in corso
• il Report sui dati di vendite Sell-In e Sell-Out visti in prospettiva dinamica (o Rolling)
• il Report sull’analisi degli scostamenti rispetto al Budget
Iniziamo con il Reporting settimanale.
15/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
IL REPORTING SETTIMANALE
Nelle colonne F:I del foglio DB contengono la tabella che associa un certo codice (per esempio
2Sep5) a una data e a un testo che rappresenta un intervallo di date corrispondente a una
settimana lavorativa.
Esistono diverse scuole di pensiero rispetto all’analisi dei dati settimanali. Nel nostro esempio
calcoliamo le date considerando che:
• la settimana inizia di lunedì e si chiude di venerdì tranne i casi che seguono
• la settimana finisce comunque con la data di chiusura del mese (che è sempre l’ultimo
giorno di calendario del mese)
• la settimana inizia comunque con il primo giorno del mese
• il sabato e le domeniche non rilevano ai fini dell’analisi
Per creare un calendario con la data di inizio e fine settimana coerenti con i punti esposti, può
essere utile sapere che le date possono essere espresse anche nel formato che esprime il
giorno della settimana. Per fare questo basta fare un clic destro nella cella (o premere Ctrl+1)
e, dalla Scheda Numero selezionare Personalizzato. Nella casella Tipo scriveremo “ggg”
affinché un lunedì venga rappresentato come “lun” oppure “gggg” se desideriamo che venga
rappresentato come “lunedì”. Naturalmente la cella conterrà sempre un numero, quindi
qualunque operazione che fa riferimento a questa cella sarà fatta usando il numero seriale
Microsoft che rappresenta la data e non la rappresentazione testuale del giorno della
settimana.
A titolo di esempio abbiamo ricreato le date a intervalli settimanali dal 1/1/2003
coerentemente con il modello esposto. Il risultato lo si può vedere nella Figura 4.16.
Figura 4.16 Per calcolare quali date rientrano in una settimana lavorativa è necessario creare
un foglio simile a quello rappresentato qui a fianco
Nelle colonne A e B sono contenute rispettivamente la data di inizio e fine settimana. La cella
A1 contiene la data di inizio anno 01/01/2003 che, nel 2003, era un mercoledì. Dal momento
che la settimana finisce di venerdì la cella B1 conterrà la formula:
=A1 + 2
La cella A2, cioè, sarà data dal venerdì + 3 giorni, quindi in A2 troveremo:
=B1 + 3
A questo punto, la cella B2 sarà il lunedì più 4 giorni.
Nelle colonne C e D abbiamo le stesse date contenute nelle colonna A e B, ma questa volta
vengono rappresentate come giorni della settimana e non come date. Questo permette di
verificare se, per errore, vi è un giorno diverso da lunedì nella colonna A o diverso da venerdì
nella colonna B. Le uniche eccezioni ammesse a questa regola sono, ovviamente, i cambi di
mese.
Nella colonna E riportiamo una formula che permette di costruire una stringa di testo
rappresentativa dell’intervallo settimanale. Per esempio in E1 troviamo “1 – 3 Jan” (Figura
4.17) e la formula che genera questo dato è la seguente:
16/24
Excel in azienda – Analisi delle vendite
=GIORNO(A1) & " - "
DB!$L$2:$M$13;2;FALSO)
&
GIORNO(B1)
Edizioni FAG Milano
&
"
"
&
CERCA.VERT
(MESE
(B1);
Figura 4.17 La figura qui rappresentata costituisce un modo molto creativo di usare le funzioni
di data GIORNO() e MESE()
In pratica la formula estrae il giorno dalle date delle colonne A e B e cerca il mese espresso
dalla data della colonna B (oppure della colonna A, non cambia nulla visto che il mese deve
essere lo stesso) nel range L2:M13 del foglio DB in modo da ottenere l’abbreviazione inglese
del mese. Il tutto viene concatenato attraverso il simbolo “&”.
Veniamo ora al Reporting settimanale vero e proprio tenendo presente che lavoreremo usando
le stringhe di testo che abbiamo descritto poche righe fa.
Il foglio Rep_Wk_ALL è il foglio contiene il resoconto settimanale di tutti i prodotti per tutti i
paesi e i prodotti e riepiloga l’andamento settimanale del Sell-Out. In questo foglio vengono
evidenziate le quantità vendute, le quantità FOC (free of charge – omaggio), il prezzo medio
applicato prima e dopo le eventuali FOC, il valore delle vendite, il cambio verso il dollaro e il
valore delle vendite in dollari.
Le date che costituiscono il range settimanale sono collegate al foglio DB dove - nel range
B15:B19 - è presente una sezione che, in base al mese e l’anno impostati nelle celle A11 e
B11, calcola quali sono gli intervalli di giorni che costituiscono le settimane di un certo mese. I
dati contenuti nella colonna B del foglio Rep_Wk_ALL, quindi, riportano fedelmente i dati
contenuti nel range B15:B19 del foglio DB (si veda a tale riguardo il paragrafo “
17/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Gestire la complessità: il foglio “DB””).
Per esempio, la prima settimana del mese è contenuta nelle celle B7, B15, B23, B31, B39,
B47. Tutte le celle faranno riferimento al primo set di date che viene riportato nella cella B7
(Figura 4.18). In questa cella, infatti, troveremo il riferimento:
=DB!B15
Figura 4.18 Uno dei concetti cardine della cartella di lavoro presentata è che la data viene
cambiata solo ed esclusivamente dalla cella B15 del Foglio DB. Tutte gli altri fogli usano come
parametro di calcolo un riferimento a questa cella
Al cambiare del range B15:B19, pertanto, il Report mostrerà range diversi. Questo è un
accorgimento puramente estetico che consente di mostrare nel Report la data sempre corretta
evitando l’inserimento dei dati per ogni prodotto ogni volta che cambia il mese.
I nomi dei prodotti per esteso che troviamo nelle celle A6, A14, A22, A30 e A38 sono presi
come riferimento per cercare il codice nel foglio DB attraverso una funzione di tipo
CERCA.VERT(). Quest’operazione viene inglobata in una più complessa funzione di tipo
SOMMA.SE() che ricerca una stringa univoca formata dal codice di prodotto, il codice della
settimana (per esempio “1 - 4 Feb”) e l’anno.
Questa funzione la troviamo nelle formule delle colonne C, D e F (Figura 4.19). Iniziamo ad
analizzare la formula della cella C7:
=SOMMA.SE(Invoices!$N:$N; CERCA.VERT($A$6; DB!$B:$C; 2; FALSO) & $B7 & $D$2;
Invoices!$K:$K)
18/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Figura 4.19 SOMMA.SE() è una formula che fa da regina in tutta la cartella qui esemplificata
La funzione cerca nella colonna N nel foglio Invoices una stringa formata da:
• codice prodotto che, nella fattispecie, è dato dalla ricerca del contenuto della cella A6
(cioè il nome del prodotto) nelle colonne B:C foglio DB
• codice settimana (B7, riferimento assoluto alla colonna) formato dal giorno di partenza
della settimana, il giorno di arrivo e il mese (per esempio “1 - 4 Feb”). Lo stesso
identico codice è presente nella codificazione del foglio Invoices
• anno (D2, riferimento assoluto)
Quando la stringa viene trovata la funzione somma i valori corrispondenti nella colonna K,
contenente i valori in unità.
Nella colonna D la formula è la medesima, ma in questo caso viene cercata una stringa che
nell’ultima parte contiene il testo “foc”. Nella colonna N del foglio DB, infatti, quando il prezzo
è uguale a zero le formule restituiscono un “foc”, come si può notare nella Figura 4.20 (si veda
il
paragrafo
“
19/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Gestire la complessità: il foglio “DB””).
La formula che troviamo in D7, pertanto, è:
=SOMMA.SE(Invoices!$N:$N; CERCA.VERT($A$6; DB!$B:$C; 2; FALSO) & $B7 & $D$2 &
“foc”; Invoices!$K:$K)
Figura 4.20 La distinzione tra unità gratuite viene gestita attraverso la ricerca di una stringa di
testo che contiene la parola “foc” alla fine. Questo è coerente con la formula creata nella
colonna N del foglio Invoices
Come anticipavamo, la formula è del tutto simile a quella della cella C7 se non per la stringa di
ricerca che contiene “foc”.
Nella cella E7 viene riepilogato il totale delle unità vendute che sarà semplicemente dato dalla
somma delle unità a pagamento più le unità vendute Free of Charge:
=SOMMA(C7:D7)
Nella colonna F viene calcolato il prezzo delle transazioni con un sistema simile a quello usato
per calcolare le unità vendute. A volte, quando si commette un errore, è possibile agire sulle
formule semplicemente usando la funzione Trova e Sostituisci dal menu Modifica (oppure
premendo i tasti Ctrl + Shift + S). Per esempio, possiamo cercare “Invoices!$K:$K” e
sostituire con “Invoices!$L:$L” (Figura 4.21).
Ecco la parte centrale della formula contenuta in F7:
= SOMMA.SE(Invoices!$N:$N; CERCA.VERT($A$6; DB!$B:$C; 2; FALSO) & $B7 & $D$2;
Invoices!$L:$L)
Figura 4.21 La funzione Trova e Sostituisci è utile anche per cercare una stringa, non solo
all’interno di elementi testuali, ma anche all’interno di una formula. In questo esempio,
sostituiamo un riferimento a una colonna con un altro riferimento
Come potete notare, la funzione è sostanzialmente identica a quella contenuta in C7, ma in
questo caso la colonna della somma nel foglio Invoices è la L, cioè quella che contiene i valori
di vendita (Q x prezzo). Abbiamo pertanto ottenuto il fatturato, ma per giungere al prezzo
occorre dividere per le quantità. Prima di procedere, però, è necessario verificare se la colonna
del divisore è uguale a zero, perché se così fosse otterremmo il fastidioso errore #DIV/0! La
formula pertanto diventa:
20/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
=SE(C7=0; 0; SOMMA.SE(Invoices!$N:$N; CERCA.VERT($A$6; DB!$B:$C; 2; FALSO) & $B7&
$D$2; Invoices!$L:$L)/ C7)
In questo modo se C7 è uguale a zero, la formula restituisce 0. In caso contrario, estrae il
fatturato e lo divide per C7, cioè per le quantità senza Free of Charge. In questo modo
otteniamo un prezzo medio che ha una valenza diversa da quello che otterremmo dividendo
per le quantità totali; con questo prezzo è possibile verificare eventuali scostamenti dal prezzo
stabilito contrattualmente con il Partner. Trattandosi di un foglio generale, il prezzo può
risentire di variazioni dovute al mix tra un paese e l’altro in cui vi possono essere condizioni
contrattuali diverse.
Nella colonna G viene calcolato il fatturato settimanale moltiplicando il prezzo ottenuto nella
colonna F per le quantità vendute a pagamento (colonna C). La formula, molto semplice, è:
=C7 * F7
Nella colonna H, invece, viene riportato il prezzo che tiene conto delle FOC. La formula divide il
fatturato per le quantità al lordo delle Free of Charge:
=SE(E7 = 0; 0; G7/E7)
Figura 4.22 Per evitare di visualizzare un errore di tipo #DIV/0! è necessario verificare che il
divisore non sia uguale a zero
Anche in questo caso occorre verificare che il divisore non sia uguale a zero (Figura 4.22). Il
prezzo che otteniamo in questo caso risente di condizioni particolari applicate a un certo
partner. Per verificare esattamente a quale partner siano state concesse FOC tali da abbassare
significativamente il prezzo della transazione, occorre cercare nel foglio Rep_Wk_ByC che
vedremo tra breve.
La colonna I riporta il cambio della settimana verso il dollaro, ma, come sappiamo, altro non è
uno specchio del Range C15:C19 del foglio DB (fare riferimento al paragrafo “
21/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Gestire la complessità: il foglio “DB””).
Nella colonna J viene calcolato il valore in Dollari. Molto banalmente la formula è:
=G7 * I7
Cioè il valore delle vendite in Euro moltiplicato per il tasso di cambio.
Il foglio Rep_Wk_ALL contiene informazioni importanti ma generiche perché non scende a un
dettaglio sufficiente a stabilire perché una certa settimana è andata così bene o male. Per
scoprire maggiori dettagli dobbiamo vedere il foglio Rep_Wk_ByC.
Il foglio Rep_Wk_ByC ha le stesse caratteristiche del foglio Rep_Wk_ALL ma, in questo
caso, tutte le formule includono il dettaglio del paese che viene riportato nella cella C1. In
tutte le formule che vedremo questa cella sarà un riferimento assoluto. Un’altra differenza
rispetto al foglio precedente è costituita dal fatto che le formule della funzione SOMMA.SE()
eseguono la ricerca nella colonna O del foglio Invoices e non nella colonna N come accadeva
in precedenza. Nella colonna O, infatti, si trovano delle stringhe di testo nello stesso formato
che stiamo cercando.
Vediamo, in dettaglio come si modificano le formule di SOMMA.SE() che abbiamo visto prima.
La cella C7 diventa:
=SOMMA.SE(Invoices!$O:$O; $C$1 & CERCA.VERT($A$6; DB!$B:$C; 2; FALSO) & $B7 &
$D$2; Invoices!$K:$K)
La cella D7 si trasforma in:
=SOMMA.SE(Invoices!$O:$O; $C$1 & CERCA.VERT($A$6; DB!$B:$C; 2; FALSO) & $B7 & "foc"
& $D$2; Invoices!$K:$K)
Nella cella F7 troviamo:
=SE(C7=0; 0; SOMMA.SE(Invoices!$O:$O; $C$1 & CERCA.VERT($A$6; DB!$B:$C; 2; FALSO)
& $B7 & $D$2; Invoices!$L:$L) / C7)
Come possiamo notare, in tutte le formule, il riferimento alla cella C1 è assoluto ed è al centro
della stringa di ricerca della formula SOMMA.SE().
Una funzionalità interessante che è stata adottata in questo foglio è il Convalida Dati (un
accenno
su
come
usare
questa
funzione
è
contenuto
nel
paragrafo
“
22/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Gestire la complessità: il foglio “DB””). Attraverso questo strumento è possibile lasciare che
l’utente scelga solo un limitato set di dati evitando così i classici errori provocati da utenti
“poco brillanti”.
In questo foglio la funzionalità Convalida Dati permette che l’utente scelga un paese
compreso tra (Figura 4.23): Nordic; Netherlands; France; Switzerland; Germany; Russia;
Poland; Slovenia; Hungary; Czech Republic; Slovak Republic; Baltics.
Figura 4.23 La funzione Convalida Dati si rivela un toccasana contro gli utenti combinaguai
ANALISI DELLE VENDITE PER PERIODO CONTABILE
Il foglio Rep_Year contiene un report che mette a confronto le vendite Sell-In e Sell-Out
nell’anno e nel mese specificati nella celle A11 e B11 del foglio DB (che come già accennato
“comanda” tutti i fogli della cartella).
In questo foglio, in particolare, troviamo:
• una sezione dedicata alle vendite dal Distributore al Mercato (colonne A:N)
• una sezione dedicata alle nostre vendite al Distributore (colonne P:AC)
• lo stock alla fine dell’anno 2004 (colonna AE)
• la valutazione dello stock all’ultimo mese (colonne AG:AS)
Nella colonna AT (tenuta nascosta per motivi estetici) è contenuta la fusione del testo relativo
al codice prodotto e al paese. Questa stringa sarà, come abbiamo visto in altri esempi, la
chiave di ricerca delle funzioni SOMMA.SE() usate in questo foglio di lavoro.
In questo foglio il riferimento temporale ai mesi è fisso dal primo mese all’ultimo mese
dell’anno di riferimento (cella B1, collegata al foglio DB) ed essi vanno da gennaio a dicembre.
Iniziamo a esaminare le vendite dal Distributore al Mercato (Sell-Out).
Come sappiamo, il foglio di input è strutturato in modo che per raccogliere il dato dal primo
all’ultimo mese dell’anno basta spostarsi dalla colonna D alla colonna O (Figura 4.24). Nella
cella B6 del foglio Rep_Year, infatti, troviamo la seguente funzione:
=SOMMA.SE(InMKT!$P:$P; $B$1 & $AT6; InMKT!D:D)
23/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Figura 4.24 Per creare formule facili da copiare nel foglio è importante parametrizzare
correttamente i riferimenti assoluto e relativi
La formula cerca nella colonna P del foglio InMKT la stringa di testo formata dalla fusione delle
celle B1 (anno) e AT6 (fusione prodotto e paese) e quando la trova restituisce il risultato sulla
stessa riga, ma nella colonna D. È importante notare le impostazioni in termini di riferimenti
assoluti e relativi usati:
• il riferimento alla colonna P del foglio InMKT è assoluto poiché quando copieremo la
formula verso destra il riferimento non deve cambiare
• il riferimento all’anno (cella B1) è assoluto sia nella riga che nella colonna, mentre
quello alla cella AT6 è assoluto nella colonna e relativo nella riga: in questo modo
quando copieremo la formula verso il basso il dato di prodotto/paese punterà sempre
alla riga giusta, mentre copiando la formula verso destra la colonna di riferimento non
deve cambiare
• la colonna D del foglio InMKT, invece, è un riferimento relativo: in questo modo sarà
possibile assecondare la struttura del foglio che dispone i dati temporalmente da
sinistra a destra
I dati di Sell-In, cioè le vendite dal nostro stabilimento al Distributore sono evidenziate nelle
colonne P:AC.
In questo caso, la logica di pescaggio dei dati è diversa, poiché il database in cui questi dati
sono inseriti è diverso rispetto a quello dove troviamo i dati di Sell-Out (Figura 4.25).
Nella cella Q6 troviamo:
=SOMMA.SE(Invoices!$P:$P; Q$5 & $B$1 & $AT6; Invoices!$K:$K)
Figura 4.25 La formula della colonna Q è diversa da quella della colonna B non solo per il
secondo argomento, ma anche – e soprattutto – perché la colonna K del foglio Invoices è un
riferimento assoluto
Traducendo la formula in algoritmo di ricerca, Excel cerca nella colonna P del foglio Invoices
la stringa derivante dalla concatenazione del nome inglese del mese (cella Q5) con l’anno
(cella B1) e la fusione prodotto/paese. Una volta trovato il dato, esegue la somma nella
colonna K che contiene i dati in unità totali (a pagamento e FOC).
Anche in questo caso riteniamo interessante analizzare i riferimenti assoluti e relativi:
• la colonna P del foglio Invoices è un riferimento assoluto poiché quando ci spostiamo a
destra, il riferimento deve restare sempre fisso a quella colonna
• il riferimento alla cella Q5 è assoluto nella riga poiché se copiamo la formula verso il
basso, il mese non deve cambiare
• l’anno rimane il medesimo per tutti i dati dell’analisi quindi la cella B1 ha un riferimento
assoluto nella riga e nella colonna
• il riferimento alla cella AT6 è fisso nella colonna poiché copiando la formula a destra
essa deve puntare sempre alla combinazione prodotto/paese
• la colonna K del foglio Invoices contiene i dati di vendita in unità e come abbiamo
precisato, questi ultimi si trovano solo in questa colonna in quanto la struttura di questo
24/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
foglio è diversa rispetto al foglio dei dati di Sell-Out. Per questo motivo, i riferimenti a
questa colonna devono essere assoluti
La colonna AE richiama i dati relativi allo Stock della fine dell’anno precedente. Il riferimento
all’anno precedente è anch’esso dinamico, cioè cambia quando viene modificato l’anno dalla
cella B11 del foglio DB (Figura 4.26). Questo è possibile grazie a questa semplicissima
funzione contenuta nella cella AE5:
=$B$1-1
Figura 4.26 È importante notare che un foglio realmente automatico modifica dinamicamente
anche le etichette e i titoli dei dati, come fa la cella AE5
Banalissimamente, basta togliere uno dal valore della cella B1, cioè quella che contiene l’anno
in corso. Le formule della colonna AE faranno tutte riferimento fisso alla cella AE5 che contiene
questo calcolo.
La struttura di queste formule è piuttosto semplice e l’abbiamo appena incontrata. Nella cella
A6 troviamo:
=SOMMA.SE(DB!U:U; AT6 & AE$5; DB!T:T)
Nel foglio DB, la colonna U contiene i riferimenti di prodotto/paese/anno e la colonna T i dati di
stock a fine periodo espressi in unità. La formula pertanto cerca nella colonna U la
concatenazione delle stringhe formate dalla cella AT6 e AE5 (quest’ultima con un riferimento
fisso alla riga) e quando trova una corrispondenza riporta i dati della colonna T. Attraverso
questa formula, quindi, otteniamo lo stock alla fine dell’anno precedente.
A questo punto è possibile calcolare qual è il livello di scorte presente nel magazzino del nostro
distributore in un certo mese.
Da un punto di vista concettuale la formula per calcolare questo dato è la seguente:
Stock al mese(T) =
Stock al 31/12 anno precedente
+ Sell-In dal primo mese dell’anno T al mese T (cioè YTD, Year To Date, cumulativo)
- Sell-Out dal primo mese dell’anno T al mese T (cioè YTD, Year To Date, cumulativo)
Questo calcolo è rappresentato dal grafico della figura 4.27.
25/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Figura 4.27 Il Flusso di Magazzino è un tipico esempio di movimento rappresentabile con un
grafico di tipo Waterfall Chart
Il problema che abbiamo con i dati a disposizione è che dobbiamo calcolare le vendite Sell-In e
Sell-Out cumulative. Se, per esempio, dovessimo sommare nelle cella AA1, AB1 e AC1 i valori
cumulativi delle celle A1, B1 e C1, la soluzione di chi non sa usare i riferimenti assoluti e
relativi, potrebbe essere:
• Cella AA1: “=A1”
• Cella AB1: “=A1+B1”
• Cella AC1: “=A1+B1+C1”
Questo ovviamente non è il modo più efficace e nemmeno quello più elegante! La soluzione
consiste nell’usare la banalissima funzione SOMMA() indicando come valori da sommare un
range che - inizialmente - ha come limiti la stessa cella. Per esempio:
=SOMMA(A1:A1)
Il risultato di questa formula sarà equivalente al contenuto della cella A1 e, di fatto, non vi
sarà alcuna somma. Cosa succede però se però blocchiamo la prima cella con un riferimento
assoluto alla colonna e copiamo a destra la formula? Semplice, la formula avrà un range che
ingigantisce man mano che copiamo verso destra. In altre parole la formula si trasforma come
segue:
=SOMMA($A1:A1)
Se questa formula fosse contenuta nella cella AA1 e la copiassimo in AB1, in questa cella
avremmo la formula “=SOMMA($A1:B1)”, mentre in AC1 la formula sarebbe
“=SOMMA($A1:C1)”. In altre parole quando copiamo verso destra la formula modifica solo il
secondo membro del range e non il primo. Se invece copiamo verso il basso, la formula si
modifica di conseguenza perché le righe sono impostate come riferimenti relativi.
Questa è senz’altro la soluzione ottimale per ovviare al problema sopra esposto che ci
apprestiamo ad affrontare nel nostro esempio del caso SreeBeD.
Il range AG:AS, dicevamo, contiene i dati dello Stock aggiornati rispetto alla dinamica SellIn/Sell-Out. Nella cella AH6 troviamo le formule (Figura 4.28):
=$AE6 + SOMMA($Q6:Q6) - SOMMA($B6:B6)
26/24
Excel in azienda – Analisi delle vendite
Edizioni FAG Milano
Figura 4.28 Il calcolo matematico dello stock a una certa data è semplice, ma nella costruzione
della formula occorre fare attenzione a bloccare alcuni riferimenti come assoluti. In questo
modo non incontreremo problemi copiando la formula verso destra o verso il basso
Dove AE6 (riferimento assoluto alla colonna) rappresenta lo Stock Iniziale, Q6 (riferimento
misto) corrisponde alle vendite Sell-In e B6 (riferimento misto) sono le vendite Sell-Out.
Vediamo come si trasformano le formule se copiamo questa cella in AI6:
=$AE6 + SOMMA($Q6:R6) - SOMMA($B6:C6)
Come annunciato, i riferimenti delle funzioni SOMMA() sono semi-relativi e questo permette di
avere sempre il range di somma corretto.
27/24