un esempio pratico di una piccola procedura in vba excel per ottenere un elaborato comparativo dei fatturati giornalieri e mensili per le aziende commerciali

Il foglio elettronico è uno degli strumenti principe per il controllo di gestione nelle aziende in quanto permette di raccogliere dati, sintetizzarli e rap presentarli graficamente con uno sforzo limitato. La sua diffusione negli uffici è enorme, ma è spesso sottoutilizzato in quanto la scarsità di tempo e l’urgenza nel realizzare i lavori non consentono quasi mai di studiare o approfondire le immense potenzialità dei fogli elettronici. Esistono funzioni di tutti i tipi: matematiche, statistiche, finanziarie, attuariali, logaritmiche e menu che forniscono strumenti quali totalizzatori con raggruppamenti, filtri, ordinamenti, link a dati esterni, eccetera. Ma quanti sono gli utenti che sanno identificare ed utilizzare lo strumento più idoneo per fare svolgere un determinato compito? Sono pochi e vengono definiti utenti evoluti: all’interno del caleidoscopico mondo dello spreadsheet riuscire ad identificare correttamente quale sia il modo più corretto di risolvere un problema richiede necessariamente la conoscenza di tutte o quasi tutte le possibilità offerte.
Di conseguenza si vedono situazioni che oramai possono essere definite classiche, come per esempio, copiare il foglio del mese precedente rinominandolo e cancellandovi tutti i vecchi dati per poi digitare i nuovi. Qualcuno più accorto degli altri ha creato un foglio di base da copiare e compilare di volta in volta. Ma altri ancora sono andati oltre affrontando la situazione di petto e scrivendo una microprocedura in Visual Basic for Excel che consenta di caricare i dati in un foglio che viene rielaborato per produrre un secondo foglio contenente quanto desiderato, rispettando la struttura richiesta arricchita da grafici.

Un esempio reale

Faremo un esempio pratico prendendo spunto da un’ azienda che operi nel settore del commercio. Mensilmente deve essere compilata una statistica sul fatturato realizzato giornalmente raffrontando i risultati conseguiti con il budget previsto e i valori di vendita nei due anni precedenti per il medesimo periodo. Considerando l’importanza di poter disporre delle necessarie informazioni sull’andamento delle vendite non solo a consuntivo ma anche durante il mese al fine di poter verificare il trend e l’allineamento con le previsioni, il documento deve poter essere stampato quotidianamente.
Di conseguenza nasce anche la ri chiesta di vedere l’andamento nei singoli giorni del mese sempre comparati sui due anni precedenti.
Il prospetto richiesto dovrà quindi riportare i seguenti dati:

• il giorno del mese
• giorno della settimana
• fatturato del giorno;
• totale progressivo del fatturato conseguito;
• scostamento dell’anno corrente rispetto al precedente (anno n su anno n-1) e scostamento di quest’ultimo sull’anno ad esso precedente (anno n-l su anno n-2);
• totale progressivo previsionale del fatturato;
• percentuale di conseguimento del budget per i tre anni.

Il documento richiesto sarà quindi composto da uno schema riportante 31 righe che accolgono i vari giorni del mese contenenti i dati sopra descritti. Il tutto deve essere arricchito dalla stampa di un grafico di comparazione dei dati del documento.

L'approccio al problema

Vediamo quali dati sono necessari per ottenere quanto richiesto. Occorrono i seguenti:

• il fatturato di ogni giorno;
• il budget mensile;

inoltre è necessario conoscere il giorno della settimana relativamente ad ogni giorno del mese preso in esame, perciò avremo 3 valori, ovvero quello riferito all’anno n-l, all’anno n-2 e n. Altra informazione importante è il tipo di giorno, ovvero se il giorno è feriale o festivo; vedremo la sua necessità parlando del fatturato previsto.
Passiamo all’organizzazione dei dati. Vi consiglio di prendere l’Excel e aprire il file Fattx.xls che contiene quanto ora de scritto. Le informazione possono essere organizzate in vari modi, ma quello scelto ritengo sia il più semplice e funzionale: viene creato un foglio di nome Fatt in cui ogni colonna contiene un mese, estendendosi da riga 1 a riga 31. A riga 40 e 41 appare in numero del mese (1-12) e l’anno. Le colonne devono contenere i mesi nella corretta successione. Gli eventuali mesi in cui non ci sono dati (chiusura dell’azienda o indisponibilità dei dati) devono occupare la relativa colonna; per comodità potrebbe essere utile fare comunque apparire il mese e l’ anno nelle righe 40 e 41.
Un altro foglio di nome GSett contiene i giorni della settimana indicati con i valori da 1 (la Domenica) a 7 (il Sabato), mentre il foglio Lav contiene un flag in dicante se il giorno era lavoravo.
La correlazione dei dati attraverso i diversi fogli avviene nella maniera più semplice possibile: le informazioni relative ad un giorno occupano la stessa cella su fogli diversi, quindi se si vogliono ac quisire i dati relativi al 1 marzo 1996, il fatturato è disponibile nel foglio Fatt alla cella ARi, mentre il flag giorno lavorativo è riportato in Lav alla cella AR1 e il suo giorno della settimana appare in GSett sempre alla cella AR1.
Come vedete, questa struttura permette, dopo aver calcolato la posizione della cella, di disporre di tutte le informazioni necessarie semplicemente selezionando il foglio che contiene il dato desiderato.
Il foglio Fatt contiene in fondo ad ogni colonna anche altre informazioni:

• totale del fatturato del mese;
• la fattura media giornaliera;
• il budget del fatturato;
• il fatturato medio giornaliero da conseguire quotidianamente per raggiungere il budget;
• la percentuale di conseguimento del budget (valore significativo solo alla conclusione del mese).

Ora possiamo studiare come deve apparire graficamente il documento richiesto.

Il layout del documento

Il documento che prenderà il nome di 3anni costituirà il prodotto finale dell’elaborazione e non necessiterà di modifiche da parte dell’utente. Sarà composto da quattro gruppi di dati: il calendario, il fatturato giomaliero, il fatturato progressivo del mese e il conseguimento budget.
I dati relativi ai vari anni in esame appa iono in ordine cronologico, quindi per primo sarà presente l’anno n-2, seguirà l’anno n-1 e l’anno n.
Il gruppo calendario si compone di sette colonne:

• nella prima appare il giorno del mese del calendario;
• nelle successive appare il giorno del la settimana relativo ai singoli anni considerati
• nelle ultime tre è riportato per i tre anni il flag indicante se il giorno è lavorativo o festivo.

Il gruppo fatturato giornaliero è com posto da tre colonne che contengono il fatturato dei giorni nei vari anni. Il gruppo fatturato progressivo è strutturato nella stessa maniera del gruppo fatturato giornaliero.
Il gruppo conseguimen to budget è composto da sei colonne: le prime tre contengono il fatturato previsto giorno per giorno nei tre anni, mentre le successive tre colonne sono la percentuale di conseguimento del budget.

Algoritmo di calcolo dei dati

Vediamo ora un aspetto importante: come sono stati calcolati i valori. Per quanto attiene al gruppo calendario, i dati sono stati presi dal foglio GSett e Lav, quindi gli unici calcoli presenti sono il numero dei giorni di calendario ed il numero totale dei giorni lavorativi, Il gruppo fatturato giornaliero prende i propri dati dal foglio Fatt e presenta in fondo alle colonne le somma per i tre anni dei fatturati realizzati.
Il discorso per i gruppi successivi è diverso: fatturato progressivo è costituito da calcoli.
Ogni cella della singola giornata contiene il valore progressivo del giorno precedente a cui si somma il fattu rato del giorno prendendolo dalla relativa colonna del gruppo fatturato giornaliero.
Le ultime due colonne che rappresentano la crescita del fatturato rapportato anno su anno, sono ottenute calcolando la percentuale fra l’anno n- i e l’anno n-2 e la percentuale fra l’anno n e n- 1.
Il gruppo, che abbiamo detto essere composto da sei colonne, vede nelle prime tre il valore progressivo di fatturato previsto in ogni anno per raggiungere il budget. In altre parole è stato preso il budget del mese e diviso per i giorni lavorativi. Il valore così ottenuto rappre senta il budget giornaliero.
Questo sistema di calcolo, decisamente più concreto ed esatto rispetto alla suddivisione del budget per il numero di gior ni di calendario, consente di generare un progressivo del budget che consideri an che la variabile del tipo di giorno: feriale o festivo. Di conseguenza il budget del fatturato all’ m-esimo giorno è calcolabile come il budget da consegui re il giorno precedente (m- 1) a cui va sommato del valore del budget giornaliero solo nel caso in cui il giorno m sia lavorativo.
Le ultime tre colonne del gruppo conseguimento budget sono composte dalla percentuale che rappresenta il conseguimento realizzato giorno per giorno. Nel caso il foglio Fatt sia privo di alcuni mesi e che conseguentemente venga visualizzato un errore nella cella delle percentuali, è stato inserito un controllo che verifica se le celle utilizzate nei calcoli contengono un valore, altrimenti la cella della percentuale viene lasciata vuota.

Altri dati

Il foglio, a differenza dell’impostazione classica che prevede in alto l’intestazione delle colonne, inizia subito con i dati e ne pone i titoli a pie di pagina. Il motivo è dato dalla maggior semplicità nella realizzazione della macro, la quale ac cede alla riga di numero uguale a quella del giorno a cui si riferisce, nonché una maggiore semplicità per l’utente nell’in dividuare dove deve immettere i dati.
Nel caso preferiste migliorarne l’estetica, potete modificare la macro affinché, dopo aver eseguito le elaborazioni già codificate, inserisca le righe che ritenete necessarie per far spazio all’in testazione, magari copiando direttamente quella già esistente in fondo.
Ma ri cordatevi di eseguire il lavoro inverso per ripristinare la situazione originale prima di rilanciare la stessa macro per ché essa opera su indirizzi fissi e conseguentemente non viene influenzata che marginalmente dalle modifiche apportate al foglio.
Potreste procedere come segue: attivare il registratore di macro ed eseguire tutte le modifiche del caso; così facendo crea te la macro senza sforzi e potete inserirne le istruzioni all’interno della macro principale.
Rimane solo il lavoro di ripristino per consentire alla macro di lavorare sugli indirizzi corretti. Potete procedere ancora col registratore di macro.
La situazione migliore è data dal foglio impostato come lo desiderate e dalla macro che proceda compiendo le seguen ti azioni:

• provvede al ripristino del Iayout come essa se lo attende eseguendo i comandi che avete ottenuto median te la seconda registrazione;
• esegue le istruzioni originarie per aggiornare il foglio;
• esegue la prima macro che sposta l’intestazione dal fondo fino in cima al documento.

Ma se voi o i potenziali utenti non avete problemi ad utilizzare i fogli con le intestazioni sotto anziché sopra, potete lasciare tutto immodificato.
Passiamo ad osservare i totali del foglio 3anni che appaiono dopo le 31 righe dei dati giornalieri. Essi rappresentano:

• il numero dei giorni di calendario del mese, ovvero di quanti giorni il mese è composto. Si tratta di un dato di scarsa utilità.
• Il numero dei giorni lavorativi per dei tre mesi in esame, calcolati come semplice somma dei flag del tipo giorno. Serve per vedere se un mese ha più o meno giorni lavorativi ri spetto gli altri mesi. Questo dato di viene utile per eseguire altri calcoli;
• l’ammontare del fatturato consegui to per ogni mese;
• l’incremento percentuale del fattura to nei tre anni;
• si ripete per omogeneità il totale del fatturato del mese e le relative per centuali.

Sotto a questi dati compare uno specchietto che evidenzia i seguenti valori suddivisi per i tre anni in esame:

• L’ammontare della fattura media del giorno;
• Il budget mensile;
• La fattura media giornaliera prevista;
• La percentuale di conseguimento del budget.

Adesso che abbiamo visto il foglio 3anni nei suoi vari aspetti, affrontiamo le macro per poi vedere un suo curioso ed utilissimo dettaglio di questo foglio.

Le macro in VBA

Il foglio Progr del file Fattx.xls contiene le funzioni necessarie più tutte le macro: creaNuovoMese e stat3anni. Incominciamo analizzando la prima.
Essa si occupa di creare tutte le strutture necessarie per poter consentire al l’utente la digitazione dei dati nei relativi fogli. Richiede come parametro il mese ed anno che si vuole preparare e determina attraverso la funzione calcolaColonna() la colonna in cui i dati del periodo in oggetto dovranno essere sistemati. Questa funzione è molto semplice in quanto considera l’anno ed il mese come un offset rispetto la prima colonna calcolato come prodotto dell’anno per 12 e sommato al numero del mese. In particolare, questa funzione sottrae 93 all’anno per evitare di dover gestire fogli inutilmente troppo larghi ed aggiunge 7 per avere libere le prime sette colonne. Ma questo è ininfluente per l’utente che vedrà la colonna in cui deve mettere i dati indicata mediante il mese ed anno inserito nelle righe 40 e 41.
Quando creaNuovoMese viene eseguita, richiede il mese e l’anno da creare, proponendo come valori di default il mese successivo a quello corrente, ed il budget del fatturato.
Definiamo meglio il compito di questa macro. Essa opera sui fogli Fatt, Lav, Gsett, ovvero quelli contenenti i dati, inserendo il mese e l’anno in fondo dalla colonna relativa al periodo da creare; in GSett riempie le celle da 1 a 31 il codice del giorno della settimana con i valori precedentemente descritti e mette in riga 32 il numero dei giorni calendario di cui il mese è composto.
In Lav viene messo il valore i per ogni giorno feriale e zero per i festivi.
L’algoritmo di calcolo verifica il giorno della settimana e l’eventuale festività a data fissa come il Natale. L’utilizzo di un foglio per questo tipo di informazioni rende semplicissimo inserire delle festività come, per esempio, il Patrono o la Pasqua che cadono in date variabili, oppure toglierle come nel caso di certi giorni festivi in cui l’attività commerciale non viene sospesa.
Nel foglio Fatt viene svolto un lavoro più intenso. Nelle celle da 1 a 31 che dovranno ospitare il fatturato giornaliero viene immesso il valore zero per quei giorni che la funzione chkFestivita() considera non lavorativi. Questo consente all’utente di avere un riscontro soprat tutto quando deve caricare i dati dei mesi precedenti prendendoli dai tabulati o da terminale in quanto se si dimenticasse di avanzare di una cella per scavalcare la festività, la vedrebbe già avvalorata a zero. Inoltre, per quei mesi che hanno meno di 31 giorni, nelle celle inutili viene immesso lo zero.
Lo stesso accade anche negli altri due fogli.
Sempre sul foglio Fatt, nelle righe successive a quelle contenti i fatturati giornalieri si trova:

• a colonna 32 il totale del fatturato del mese;
• a colonna 33 la fattura media gior naliera calcolata sul totale del fattu rato diviso i giorni lavorativi;
• a colonna 34 il budget che è stato inserito nella finestra di dialogo che la stessa macro ha aperto all’inizio;
• a colonna 35 il valore della fattura media necessaria per raggiungere il budget;
• a colonna 36 la percentuale di con seguimento del budget.

Ovviamente tutti i valori calcolati acquisiscono significato solo quando tutti i fatturati del mese sono stati inseriti in quanto i calcoli avvengono sulla base dell’intero mese. Inizialmente era stata espressa la necessità di disporre di un foglio i cui valori fossero significativi anche a metà mese: analizziamo quindi la seconda macro.
stat3anni opera sul foglio 3anni costruendo in esso dei riferimenti ai fogli contenenti i dati.
Il suo scopo è di correlare i dati dei tre anni in un unico foglio che l’utente possa poi analizzare e stampare. Essa necessita di conoscere il mese e l’anno su cui lavorare come base; questi dati vengono forniti attraverso due finestre gestite dalla macro. Un aspetto interessante di questa macro è che costruisce le formule e le immette nel foglio. Soprattutto è da notare che nel foglio 3anni in basso a sinistra compare una piccola scritta recante un’informazione: fino a quale giorno i dati sono aggiornati. E' di particolare interesse perché esso è gestito automaticamente, infatti l’utente non deve digitare questa informazione. Essa viene dedotta percorrendo tutte le celle del mese in esame dalla 3lesima alla prima ricercandone una che contenga un fatturato. Fin qua tutto semplice.
Le cose si complicano quando l’ultimo giorno del mese è un festivo e quindi la 31esima cella non contiene il valore del fatturato. Di conseguenza viene effettuata una scansione al contrario rispetto la precedente, cercando il primo giorno lavorativo: se si supera la cella 31 significa che i dati della colonna comprendono tutto il mese, altrimenti si utilizza il numero di riga come ultimo giorno compilato.
Ultimo compito della macro consiste nel mettere il mese e l’anno elaborato nell’intestazione delle colonne, dopo di che la macro termina.

Il Grafico

Il foglio 3anni è composto da due parti: un foglio che viene compilato dalla macro stat3anni e un grafico sulla destra. Esso viene tenuto aggiornato rispetto ai dati della tabella dallo stesso Excel senza far ne carico alla macro o all’utente in quanto le serie di dati da rappresentare sono il progressivo del fatturato e del budget.
E stato scelto di rappresentare il valore progressivo perché è più significativo rispetto al dato del singolo giorno che è comunque facilmente visibile nel foglio.
Questa scelta permette di vedere l’andamento delle vendite raffrontate sul budget consentendo di analizzare chiaramente il trend.
Il confronto con i due anni precedenti è reso possibile dalla presenza nel grafico di altre due coppie di linee.

Conclusioni e saluti

Siccome quanto contenuto nel foglio 3anni è costituito non dai dati ma dai riferimenti ai dati, è possibile eseguire simulazioni inserendo i valori del fatturato nel foglio Fatt e vedere quali con seguenze porterebbero, per esempio, la chiusura dell’azienda per uno o più giorni. Anche se non presenta le caratteristi che di alcuni costosi programmi di controllo di gestione e non vuole neppure mettersi in competizione con essi, il file Fattx.xls rappresenta un buon punto di inizio per costruire una piccola procedura a riguardo.
D’altro canto, sotto l’aspetto della programmazione sarebbe stato estremamente più semplice creare una macro che copiasse i dati da Fatt a 3anni, ma il sistema utilizzato rappresenta un buon esempio di programmazione con il Visual Basic for Excel.
Ci sarebbero ancora tantissimi aspetti di cui parlare, ma temo che andrebbero oltre il soggetto in esame, ovvero la presentazione della macro e delle possibilità offerte dall’Excel per costruire piccoli ma efficacissimi sistemi per l’ufficio Controllo di Gestione. A questo punto non mi resta che salutarvi.

Tratto da:
Paolo Guccini
Rivista DEV Computer Programming
Edizioni Infomedia
Aprile 1996