I rapporti fra imprese e rappresentanti richiedono spesso l’utilizzo di un software che tenga traccia delle vendite concluse dagli agenti, del loro fatturato e delle rispettive provvigioni. Con un foglio elettronico Excel l’azienda può provvedere alla la gestione del lavoro degli agenti di vendita e creare report personalizzati per dettagliare l’attività di ciascuno.
Database Agenti
Per prima cosa occorre creare le tabelle con le informazioni di base: Rappresentanti e Clienti. La tabella Rappresentanti dovrà contenere i seguenti campi: Ragione Sociale, Indirizzo, Città, Partita Iva, Telefono e Provvigioni (quest’ultimo dovrà indicare la percentuale dei compensi da riconoscere al rappresentante al momento della vendita).
=>Vai agli altri Tutorial di PMI.it su Excel
=>Scarica l’esempio
Alla stessa maniera dovrà essere creata la tabella Clienti avendo cura di sostituire il campo Provvigioni con il campo Rappresentante. In questo modo, la tabella Rappresentanti potrà essere relazionata alla tabella Clienti: la relazione sarà “uno ad uno” ossia “ad ogni cliente corrisponde un rappresentante”. Questi dovrà seguire i clienti affidatigli promuovendo e vendendo i prodotti dell’azienda. Per questo lavoro l’azienda gli corrisponderà le provvigioni contrattualizzate.
=> Leggi come creare un software gestionale con Microsoft Office
Per permettere la relazione fra le tabelle si dovranno selezionare le celle del foglio Rappresentanti contenenti la ragione sociale, e associare ad essere il nome “Rappresentanti”. Per fare questo è necessario richiamare la funzione “Nome” nel menu “Inserisci”. Nel campo “Riferito a” inserire la formula: =RAPPRESENTANTI!$A$2:$A$65536
Confermare con il pulsante Aggiungi, quindi Ok.
L’utilizzo di un nome descrittivo in una cella consente non solo di comprenderne l ‘ utilità del contenuto ma anche di raggruppare più celle contenenti dati omogenei. In questo modo è stato creato un insieme di dati, chiamato Rappresentanti, che potrà facilmente essere utilizzato nelle formule. I nomi sono visibili da qualunque foglio della cartella di lavoro. Il nome appena creato, pertanto, potrà essere utilizzato in altri fogli di lavoro avendo la certezza di far riferimento sempre allo stesso insieme di dati. Le celle della colonna Rappresentante della tabella Clienti dovranno, pertanto, collegarsi all’insieme appena creato. Per fare questo occorre richiamare la funzione “Convalida”dal menu Dati. I criteri di convalida dovranno consentire la visualizzazione di un Elenco la cui origine è indicata dalla formula =Rappresentanti. È importante che siano presenti i flag sui campi “Ignora celle vuote” e “Elenco nella cella”. Confermare con il pulsante Ok.
Il campo Ragione Sociale della tabella Clienti dovrà essere relazionato con la tabella delle vendite. Anche in questo caso occorre definire un nome alle celle della colonna A richiamando il comando Nome all’interno del menu Inserisci. Il nome da definire sarà Clienti e come riferimento avrà la formula: =CLIENTI!$A$2:$A$65536
=> Scopri come tenere sotto controllo budget e costi con Excel
Dettaglio Vendite
Si dovrà adesso creare la tabella Vendite, in cui indicare il dettaglio delle vendite effettuate, con importo e provvigioni maturate dal rappresentante. Per non appesantire la cartella Excel, limiteremo l’inserimento dei dati a 600 record. La tabella dovrà contenere i seguenti campi:
- Data: quella della vendita effettuata;
- Mese: è il campo utilizzato per elaborare il report finale (permette di raggruppare le vendite in base al mese)e contenerrà la formula =SE(A2<>””;MESE(A2);””)per contrassegnare il mese di riferimento (gennaio = 1, febbraio = 2, marzo = 3 e così via);
- Numero: quello del documento emesso al momento della vendita;
- Cliente: relazionato con il campo Ragione Sociale della tabella Clienti, richiamando il comando “Convalida” dal menu Dati. Nel campo Consenti si dovrà selezionare Elenco mentre nel campo Origine si dovrà indicare la formula =Clienti. Accertarsi che i campi “Ignora celle vuote” e “Elenco nella cella” siano selezionati;
- Importo: il valore della vendita;
- Rappresentante: relazionato con il campo Ragione Sociale della tabella Rappresentanti, richiamando il comando Convalida dal menu Dati. Nel campo Consenti si dovrà selezionare Elenco mentre nel campo Origine si dovrà indicare la formula =Rappresentanti. Accertarsi che i campi “Ignora celle vuote” e “Elenco nella cella” siano selezionati. Per permettere di richiamare in automatico il rappresentante affidato al cliente selezionato inserire la funzione: =SE(D2<>””;CERCA.VERT(D2;CLIENTI!$A$2:$F$65536;6;FALSO);””);
- Percentuale: per calcolare la provvigione inserire la formula: =SE(F2<>””;CERCA.VERT(F2;RAPPRESENTANTI!$A$2:$F$65536;6;FALSO);””)
- Provvigione: dovrà contenere la formula =SE(E2<>””;SE(G2<>””;E2*G2;0);0)
Report vendite
L’ultimo foglio, chiamato Report, dovrà presentare un prospetto delle vendite effettuate e delle provvigioni maturate dai rappresentanti in ogni mese dell ‘ anno. Per questo dovrà avere una colonna con l’elenco dei rappresentanti, due colonne per ciascun mese dell’anno (vendite e provvigioni) e due colonne finali relative ai totali.
=> Leggi come calcolare le provvigioni sulle vendite con EXCEL
Nella colonna A inserire la formula:
=SE(RAPPRESENTANTI!A2<>””;RAPPRESENTANTI!A2;””)
Affinché i valori inseriti nel foglio Vendite siano visibili nel foglio Report riepilogati per mese e per rappresentante occorre utilizzare la funzione =MATR.SOMMA.PRODOTTO(). Questa funzione permette di moltiplicare gli elementi corrispondenti nelle matrici specificate e di ottenere la somma dei relativi prodotti. Pertanto, per estrarre le vendite di uno specifico mese e di un determinato rappresentante occorre utilizzare la formula:
=SE(A3=””;0;MATR.SOMMA.PRODOTTO((VENDITE!$B$2:$B$600=i_num_mese)
∗(VENDITE!$F$2:$F$600=A3)∗VENDITE!$E$2:$E$600)).
Analogamente, per estrarre le provvigioni la formula sarà:
=SE(A3=””;0;MATR.SOMMA.PRODOTTO((VENDITE!$B$2:$B$600=i_num_mese)
∗ (VENDITE!$F$2:$F$600=A3)*VENDITE!$H$2:$H$600)).
Resta inteso che al valore i_num_mese dovrà essere sostituito il numero del mese di riferimento (1= gennaio, 2=febbraio e così via). Le formule appena create dovranno, quindi, essere adattate alle celle che compongono la tabella. Infine, le ultime due colonne sono destinate a contenere il totale delle vendite e delle provvigioni di ciascun rappresentante. Per il calcolo del totale delle vendite occorre inserire la formula: =B3+D3+F3+H3+J3+L3+N3+P3+R3+T3+V3+X3.
Per il calcolo delle provvigioni la formula sarà: =C3+E3+G3+I3+K3+M3+O3+Q3+S3+U3+W3+Y3.
