Quando un’azienda si serve di agenti per la vendita dei propri prodotti spesso ricorre ad un sistema di calcolo provvigioni basato su “scaglioni” di fatturato, ad ognuno dei quali è associato un tasso di provvigione via via crescente. Il calcolo non presenta difficoltà da un punto di vista matematico: è sufficiente, difatti, suddividere il fatturato realizzato dall’agente fino alla capienza massima di ogni scaglione e, successivamente, applicare il tasso stabilito contrattualmente.
=> Calcolo IVA online con il tool gratuito di PMI.it
Supponiamo, per semplicità di esposizione, che l’azienda A utilizzi soltanto due scaglioni: fino a 10.000 € viene applicata una provvigione del 10% e, per la parte eccedente, una provvigione del 20%; se un agente fattura 16.000 € dovrà percepire una provvigione lorda pari a 2.200 €: sui primi 10.000 € di fatturato percepirà 1.000 € di provvigione (10.000 * 10% = 1.000) mentre, sui restanti 6.000 € percepirà una provvigione pari a 1.200 € ((16.000-10.000)*20%).
=> Scarica il modello
Da un punto di vista operativo, se intendiamo realizzare un modello “flessibile” per il calcolo delle provvigioni, ovvero che possa essere utilizzato con differenti scaglioni a seconda del tipo di azienda o di vendite, dobbiamo ragionare in termini più complessi. Il modello proposto in questo tutorial consente di calcolare in automatico, dopo la compilazione di una parte tabellare relativa agli scaglioni utilizzati in azienda e l’inserimento delle vendite di ogni singolo agente, la provvigione lorda spettante ad ognuno e il tasso di provvigione media corrisposto dall’azienda.
=> Ritenute e provvigioni: regole per agenti di commercio
Come costruire il modello
Il modello prevede la possibilità di utilizzare fino ad un massimo di quattro scaglioni (normalmente, in azienda si utilizzano mediamente tre) e l’inserimento di dieci agenti ma, come vedremo nel seguito della trattazione, può essere personalizzato a seconda delle esigenze. Nella prima tabella costruita sull’intervallo A3:C7 devono essere indicati gli scaglioni e le relative percentuali di provvigione; l’unico accorgimento da adottare è quello di utilizzare un valore superiore a qualunque previsione di vendita come ultimo scaglione: nell’esempio riproposto in figura è stato utilizzato come valore massimo un milione di Euro (nella cella B6) considerato che il fatturato massimo realizzato da un singolo agente è ottantamila Euro (valore inserito nella cella C10); inoltre, i dati da inserire nell’intervallo A4:A7 servono solo per una maggiore chiarezza del prospetto e non vengono utilizzati nelle formule del modello.
Figura 1. Il documento finale
Nella seconda tabella, costruita sull’intervallo A9:D21, vanno indicati i nominativi degli agenti, la zona di vendita e il fatturato realizzato; l’intervallo D10:D21 e la cella C20 contengono, invece, le formule del modello.
La funzione “SE” nidificata
Prima di analizzare queste ultime, soffermiamo brevemente la nostra attenzione sulla funzione “SE nidificata” che è alla base del modello proposto. La funzione SE è costituita da tre argomenti: il test (o condizione), ciò che Excel deve restituire se la condizione si verifica e ciò che la funzione deve restituire se la condizione è falsa; questi ultimi due argomenti possono essere numeri, formule, funzioni, riferimenti di cella o etichette. Ad esempio, attraverso la funzione =SE(A1<20;300;500) si chiede ad Excel di testare se la cella A1 contiene un valore inferiore a 20 e nel caso affermativo, di restituire il valore 300 (nella cella nella quale viene inserita la formula), altrimenti, ovvero nel caso in cui la condizione non si verifica, di restituire il valore 500.
Ora, se come terzo argomento della funzione utilizzassimo una ulteriore funzione SE, creeremmo una funzione “nidificata”: in questo caso, se la condizione posta nella prima funzione SE non si verifica, Excel esegue il nuovo test contenuto nella seconda funzione SE. Per tornare all’esempio precedente, attraverso la funzione =SE(A1<20;300;SE(A1<=40;400;500)) si chiede ad Excel di testare se il valore nella cella A1 è inferiore a 20 e nel caso affermativo di restituire il valore 300; nel caso in cui la condizione non si verifichi, Excel esegue il secondo test valutando se il valore contenuto in A1 è inferiore a 40 e nel caso affermativo ovvero, se il valore è compreso tra 20 e 40 (e ciò perché la prima condizione non è stata soddisfatta!), restituisce il valore di 400; se anche questa seconda condizione non si verifica (ad esempio, perché il valore contenuto in A1 è maggiore di 40) viene restituito il valore di 500 nella cella nella quale è stata utilizzata la funzione. La funzione SE può essere nidificata al suo interno fino a sette volte e, pertanto, si possono creare formule anche molto elaborate! Da notare che Excel esegue i test seguendo l’ordine di trascrizione nella formula e “termina” la sua elaborazione quando una condizione si verifica.
=> Contratto di agenzia: indennità, cessazione, non concorrenza
Applicazione ì”SE nidificata” al modello
Torniamo ora al nostro modello soffermandoci sulla formula utilizzata nella cella D10. Per comodità di esposizione (ma anche per evitare di spaventare il lettore!), scindiamo quest’ultima in “segmenti” teorici:
=SE(O(C10<0;C10>$B$6);0;…
Attraverso questa prima parte della formula si testa se il valore delle vendite (posto in C10) non rientra nell’intervallo degli scaglioni, ovvero se è minore di zero o maggiore del valore dello scaglione massimo (B6) e, nel caso in cui tale condizione si verifichi, di restituire uno zero, altrimenti …
… SE(C10<B4;C10*C4; …
Attraverso questa prima nidificazione si chiede ad Excel di testare se le vendite fatturate dal primo agente (C10) sono inferiori al valore massimo del primo scaglione (B4) e, nel caso affermativo di restituire la moltiplicazione tra tale valore e la percentuale del primo scaglione (C4), altrimenti …
… SE(C10<B5;B4*C4+(C10-B4)*C5; …
Attraverso questa seconda nidificazione si chiede ad Excel di testare (qualora le prime due condizioni non si siano verificate, ovvero se il fatturato rientra nell’intervallo dei dati e supera la capienza del primo scaglione) se il fatturato (C10) è inferiore al valore massimo del secondo scaglione (B5) e, nel caso in cui la condizione si verifichi, di restituire la somma tra il valore massimo del primo scaglione (B4) moltiplicato per la percentuale di provvigione (C4) e del valore residuo (C10-B4) moltiplicato per la percentuale relativa al secondo scaglione (C5), altrimenti …
… SE(C10<B6;B4*C4+(B5-B4)*C5+(C10-B5)*C6; …
Attraverso questa terza nidificazione si chiede ad Excel di testare se il fatturato (C10) trova capienza nel terzo scaglione (B6) e, nel caso in cui la condizione si verifichi, di sommare le moltiplicazioni tra il valore del primo scaglione (B4) e la relativa percentuale (C4), tra il valore del secondo scaglione (B5-B4) e la relativa percentuale (C5) e tra i valore residuo (C10-B5) e la percentuale del terzo scaglione (C6), altrimenti …
… B4*C4+(B5-B4)*C5+(B6-B5)*C6+(C10-B6)*C7)))
Attraverso quest’ultima parte si “chiude” la formula; se, infatti, Excel arriva ad “elaborare” quest’ultima parte della funzione significa che il fatturato (inserito in C10) trova capienza nell’ultimo scaglione (le prime quattro condizioni non si sono verificate) e quindi dovrà restituire la moltiplicazione tra i valori dei primi tre scaglioni e le relative percentuali, e del valore residuo (C10-B6) per la percentuale del quarto scaglione (C7).
La formula contenuta in D10, che è stata costruita con riferimenti assoluti in maniera tale da poter essere copiata nelle celle sottostanti fino alla riga 19, è pertanto la seguente:
=SE(O(C10<0;C10>$B$6);0;SE(C10<$B$4;C10*$C$4;SE(C10<$B$5;$B$4*$C$4+(C10-$B$4)*$C$5;SE(C10<$B$6;$B$4*$C$4+($B$5-$B$4)*$C$5+(C10-$B$5)*$C$6;$B$4*$C$4+($B$5-$B$4)*$C$5+($B$6-$B$5)*$C$6+(C10-$B$6)*$C$7))))
Nelle celle C20 e D20 sono stati calcolati i totali delle due colonne mentre nella cella D21, formattata con lo stile percentuale, è stata utilizzata la formula =SE(C20=0;0;D20/C20) che consente il calcolo della provvigione media corrisposta dall’azienda.
Personalizzare il modello
Il modello proposto può essere facilmente personalizzato incrementando il numero di righe relative agli agenti: è sufficiente, difatti, aggiungere nuove righe all’interno della parte tabellare e ricopiare la formula contenuta nella cella D10 verso il basso; per aggiungere nuovi scaglioni è necessario, invece, modificare la funzione SE contenuta in D10 aggiungendo una o più nidificazioni.
