Forum di Excel e VBA, per condividere esperienze e risolvere problemi su:
Excel, Word, Access, Power Point, Microsoft Office, macro, Visual Basic e Visual Studio.
Provvigioni multi livello
- Hai un problema con Excel?
Provvigioni multi - livello
di emanu (utente non iscritto) data: 12/06/2015 18:59:50
Buonasera a tutti :D
Necessito del Vs aiuto, inanzittutto per comprendere se Excel è ciò di cui ho bisogno e se e come sia in grado di farlo.
Problema: devo creare un file che mi consenta di gestire una sorta di calcolo provviggioni a livello piramidale.
Esistono esclusivamente questi cinque livelli:
1) tetto massimo da fatturare per passare al livello successivo: 5000 Euro con provvigione 20%
2) tetto massimo da fatturare per passare al livello successivo: 25000 Euro con provvigione 30%
3) tetto massimo da fatturare per passare al livello successivo: 50000 Euro con provvigione 40%
4) tetto massimo da fatturare per passare al livello successivo: 100000 Euro con provvigione 45%
5) tetto massimo da fatturare per passare al livello successivo: 250000 Euro con provvigione 50%
Faccio un esempio dove X (azienda), Y (utente1), Z (utente2)
Y e Z guadagnano il 20% di ciò che fatturano se sotto i 5000. X guadagna il 30%-20%= guadagno effettivo 10% sul fatturato di Y e Z.
A loro volta Y e Z trovano persone che lavorano e così via.....
Se superano il primo livello aumentano le percentuali sia per l'azienda che per chi sta sotto.
La problematica è che non saprei come impostare il file Excel dal momento che i calcoli sarebbero molto variabili sia per le persone che man mano si aggiungono sia per i diversi scaglioni di guadagno per fatturato.
Pensavo di organizzare il tutto per fogli di lavoro e creare un foglio di lavoro per ogni livello al cui interno inserire le varie persone che vi lavorano in quel range di fatturato mentre per l'azienda avere un foglio a parte dove inserire tutti i guadagni dai vari livelli.....
MAmma mia come sono confuso....spero che qualcuno di voi mi sappia dare una dritta....
di Marius44 data: 13/06/2015 10:09:47
Ciao Emanuele (?)
non confonderti, non mi sembra così difficile. Se ho capito bene le tue spiegazioni, basta creare una tabella con i fatturati e le rispettive percentuali.
Ma non mi dilungo oltre, allego un file dove potrai notare che i calcoli dovrebbero essere in linea con le tue spiegazioni. Guarda le formule in ogni cella.
Ciao
Mario
di emanu (utente non iscritto) data: 13/06/2015 12:25:24
Ciao Mario,
che dire....sembra che mi sia complicato io la vita su un problema forse molto semplice.
Ti ringrazio per il tempo che mi hai dedicato e, se non sono di troppo disturbo, ti chiederei di spiegarmi bene le formule utilizzate.
Ancora Grazie
Emanuele
di Marius44 data: 13/06/2015 13:10:19
Talvolta un problema che ci assilla può farci andare in confusione. Un "estraneo" esamina asetticamente e, ma non sempre, trova la soluzione.
Veniamo alle formule.
Col.A - elenco Azienda e nominativi principali e rispettivi partners;
Col.B - B3 somma fatturati nominativi principali; B5 somma fatturati nominativo 1 suoi partners (vale anche per B10 e per B14);
Col.C - fatturati dei singoli nominativi (DATI DA INSERIRE MANUALMENTE);
Col.D - in tutte le celle (escluso riga 3 che vedremo dopo) vi è la formula CERCA.VERT(Cx;tabella;2), cioè cerca la percentuale relativa al fatturato singolo nella tabella;
Col.E - in E5, E10 e E14 vi è la formula CERCA.VERT(Bx;tabella;2), cioè cerca la percentuale relativa al fatturato del nominativo principale;
Col.F - In F5, F10 e F14 vi è il calcolo delle provvigioni per i nominativi principali.
E veniamo al calcolo per l'Azienda.
In D5 la solita formula che cerca la percentuale relativa (ipotetica) al fatturato globale, cioè CERCA.VERT(B3;tabella;2).
In E5 la formula è CERCA.VERT((B3*D3-SOMMA(F5+F10+F14));tabella;2) cioè calcolo le provvigioni (ipotetiche) relative al fattura globale e detraggo le provvigioni dei nominativi principali; questo dato è quello da cercare in tabella.
Spero di essere stato chiaro
Ciao e fammi sapere,
Mario
di emanu (utente non iscritto) data: 22/06/2015 16:53:35
Ciao Mario,
ti ringrazio per la dettagliata spiegazione delle formule da te utilizzate per venir incontro alle mie problematiche.
Adesso ho creato un file suddividendo il tutto per fogli di lavoro (come da allegato).
Graficamente credo che sia ben leggibile e comprensibile.
Problema: non riesco a collegare le formule su più fogli e mi sorge un dubbio....è possibile farlo?
Pensa che sono anche riuscito a mettere un grafico che mostra l'andamento del fatturato mensile per ogni agente.
Poi dopo le colonne sul fatturato, ho inserito quelle percentuale che dovrebbero ricollegarsi mese per mese alla tabella provvigioni per i vari scaglioni e il ricavo mensile (guadagno) dell'agente.
Su tutti questi dati dovrebbe poi ricollegarsi il fatturato dell'azienda....
mi sa tanto che mi sono solo complicato la vita...
di Marius44 data: 22/06/2015 22:13:18
Ciao Emanuele
Ho dato uno sguardo al file che hai allegato e, sono sincero, non ho capito nulla anche perché la tua attuale richiesta differisce molto rispetto alla prima. Vedo l'Azienda, vedo i fogli con i vari livelli di fatturato e relative percentuali. Non vedo altro. Mi manca, in particolare, l'Agente Capo e i suoi subalterni.
In mezzo alla marea di nomi definiti (che saranno senz'altro utili per te) ho individuato la tabella fatturati/provvigioni. In base a questa ho inserito, Foglio ASSICONF S.R.L. in Q7 la seguente formula: =SE(R7>0;CERCA.VERT(R7;tblRicavi1922;2);"") che si riferisce ai Ricavi di Genn.
La stessa formula può essere ricopiata in tutte le altre celle simili (vari livelli).
Non riesco ad individuare:
a) dove inserire la percentuale degli Agenti-Capo;
b) dove inserire il calcolo per la percentuale dell'Azienda.
Fin qui il tuo lavoro.
Se mi permetti un consiglio: il tutto mi sembra troppo ridondante; molti fogli riportano dati ripetuti ed il foglio dell'Azienda dovrebbe calcolare solo i dati rivenienti dagli altri fogli. Ma, ripeto, non ho capito molto.
Fammi sapere.
Caio,
Mario
di emanu (utente non iscritto) data: 25/06/2015 13:16:25
Ciao Mario,
hai ragione. Ti allego nuovamente il file con anche un esempio reale nel livello 1.
La mia idea e l'impostazione grafica che ho adottato è la seguente (spero di essere più chiaro):
il foglio di lavoro Assiconf presenta la prima tabella che riporterà nelle colonne dei mesi la somma del fatturato totale dei vari fogli livello e la seconda parte della tabella presenta le colonne "PCT-GEN" dove vorrei che comparisse la percentuale azienda in relazione al fatturato totale del livello e accanto nella colonna "RICAVI GEN" il relativo guadagno dell'azienda.
Le tabelle sotto servono per inserire gli agenti che appartengono all'azienda Assiconf direttamente.
Il modo in cui ho strutturato la tabella sia Azienda che Livelli vari è il seguente....
Al primo rigo inserisco il nome dell'agente capo
sotto i nomi che appartengono all'agente capo.
Accanto c'è il fatturato mese per mese....ho inserito già i primi dati....e vorrei anche quì che nella colonna PCT-GEN appaia la percentuale sulla base del fatturato del mese e nella colonna RICAVI-GEN il guadagno dell'agente.
La parte più difficile credo sia legata all'azienda perchè il guadagno dell'azienda è il differenziale tra la PCT azienda e la PCT livello.
In effetti credo di aver capito la tua perplessità perchè non ho pensato ad una cella con la provvigione totale dell'agente capo su cui far calcolare quella dell'azienda che è la differenza in pct tra pct azienda 20% e pct raggiunto dal capo.
Es. capo1 fatturato tot. 25000 30%
Azienda 30%-20%= 10% su 5000
Riuscirò ad impostarlo sul foglio che ho preparato
di Marius44 data: 25/06/2015 15:16:19
Ciao Emanuele
Andiamo per gradi:
a) parliamo dopo dell'Azienda;
b) nel file allegato ho inserito qualche formula ed anche 3 ulteriori colonne nel foglio del primo livello per calcolare la percentuale ed il quantum per l'Agente-Capo.
Vedi se va bene così (le formule potranno essere successivamente adeguate alla tua impostazione); poi, vedremo di proseguire.
Ciao,
Mario
di Marius44 data: 25/06/2015 16:46:35
Ciao Emanuele
mi sono accorto che nel file che t'ho inviato il calcolo delle provvigioni dell'Agente-Capo non è corretto.
Guarda il nuovo file PR_2_Marius (lascia perdere il precedente).
Fammi sapere. Ciao,
Mario
di emanu (utente non iscritto) data: 26/06/2015 16:18:26
Ciao Mario,
ho visto il file.
La mia preoccupazione era legata al fatto che oggi c'è un agente capo con due sotto...quei due sotto diventeranno a loro volta agenti capo....una vera struttura piramidale....secondo te c'è un modo per ridurre le colonne ed ottenere comunque con le formule i risultati desiderati...
Vorrei poterlo semplificare e far si che l'unico dato che ogni mese si va ad imputare sia il fatturato di ogni agente....
di Marius44 data: 26/06/2015 16:55:16
Ciao Emanuele.
Beh, il sistema, anche se un po' laborioso, c'è. Ma la struttura del tuo elaborato è alquanto complessa e si potrebbe fare qualcosa forse ripensandolo dall'inizio.
Ascolta. Quando un Sub-Agente (chiamiamolo così) diventa Agente-Capo avremo un doppione perché non si può toglierlo dalla precedente posizione in quanto i dati relativi hanno fornito, fino a quel momento, un contributo al precedente capo e bisogna creare una SUA nuova posizione con la sua struttura. Secondo me occorrono: un Foglio-riepilogo per l'Azienda dove far confluire tutti i dati (immaginando di lasciare le posizioni vuote per future occorrenze) e tanti Fogli-Agente per quanti sono gli Agenti-capo (lascia perdere i Livelli, puoi chiederli con delle ricerche mirate). Pensaci un po', non dovrebbe essere tanto difficile.
Per le formule di collegamento non è un problema (anche se ho avuto qualche difficoltà ad inserire le mie per via dei nomi definiti che vi sono dentro).
Non mi ha detto, però, se il tipo di elaborato che ti ho inviato andava bene quanto per la parte del Livello1 (non ho fatto alcun collegamento con l'Azienda).
Fammi sapere. Ciao,
Mario
di Marius44 data: 26/06/2015 18:16:07
Ariciao Emanuele.
Ti allego un esempio della mia idea. Ovviamente è da migliorare sotto tanti punti vista: grafico, strutturale, funzionale, ecc.
Vedi se puoi svilupparlo (tu che hai ben presenti le tue esigenze) e poi ci sentiamo.
Ciao,
Mario
di emanu (utente non iscritto) data: 01/07/2015 09:18:04
Ciao Mario,
ancora grazie per aver migliorato il mio file.
Mi ero troppo concentrato sui livelli....in effetti operando e creando fogli per agente-capo, la gestione diviene più semplice e chiara.
Alcune domande (ti riallego il file su cui sto lavorando):
1. sono molto colpito e curioso di comprendere come creare un elenco a tendina dinamico, una maschera per inserimento agente e sub-agente e la tabella nascosta.
2. non ho ben compreso perchè ci sono più righe pct.
Grazie
di Marius44 data: 01/07/2015 12:57:02
Scusa Emanu
Ma il mio esempio lo hai guardato bene? voglio dire le varie formule nelle diverse celle le hai notate? Se mi chiedi perché tante righe di pct vuol dire che non ti sei reso conto di ciò che fanno. A questo punto, pur con la massima disponibilità, non penso che ci siamo. Se hai bisogno di un vestito confezionato su misura, vai dal sarto! Se invece tu sai già "cucire" un po' e ti serve un aiuto, siamo qui. Tutto però dipende dal tuo grado di conoscenza sia Excel sia VBA. Se sei alle prime armi l'elaborato che vuoi realizzare è come affrontare con la clava un carrarmato!
Torniamo al programma (ma tu, scusami, datti una mossa: leggi, compra libri o riviste sull'argomento, segui i tutorial, ecc): le diverse percentuali del foglio agenti che ti ho inviato si riferiscono (guarda il tuo elaborato - a proposito, quando è possibile, evita le celle unite; talvolta sono un problema. Intanto togli i nomi definiti tranne quello che si riferisce alla tabella-fatturati-percentuali):
cella C6 alla provvigione da calcolare sul fatturato totale (diretto e indiretto) dell'Agente di riferimento;
cella C8 alla provvigione da calcolare sul fatturato DIRETTO dell'Agente di riferimento;
cella C10 alla provvigione da calcolare sul fatturato INDIRETTO (costituito dal fatturato dei Sub-agenti dell'agente di riferimento)
Nel tuo schema MANCANO i riferimenti ai Sub-agenti.
Sistema quanto sopra detto nel tuo file (per le formule adatta quelle del mio esempio) e poi me lo rimandi e vedremo di andare avanti per gradi.
Ciao,
Mario
di emanu (utente non iscritto) data: 04/07/2015 14:04:23
Ciao Mario,
scusami ma mi sarò espresso male.
Il mio non è assolutamente un modo per ottenere dagli altri e in questo caso da te, un lavoro già bello e pronto.
E' vero non sono molto pratico di Excel o forse non sono molto afferrato con la matematica e di conseguenza l'utilizzo di Excel diventa ancora più ostico...sto cercando di comprendere le formule che hai utilizzato e di venirne a capo.
La domanda che ti avevo posto era solo per capire se tutte le pct fossero utili al calcolo.
Ti chiederei gentilmente di riallegarmi il file con i calcoli perchè non l'ho salvato correttamente e non posso nemmeno riscaricarlo...(non pensavo che dopo un pò gli allegati sparissero).
Perdonami se ti ho dato l'impressione di uno che vuole qualcosa senza nemmeno applicarsi...
Grazie
Emanuele
di emanu (utente non iscritto) data: 04/07/2015 14:11:57
Anzi guarda provo a ricostruirle io.
Allora:
Fatturato diretto: è il fatturato che l'agente o subagente fa mese dopo mese (no formula, inserimento manuale dei dati)
Fatturato indiretto: è ciò che l'agente e i vari subagenti hanno dal fatturato di chi sta sotto loro. La formula che dovrei utilizzare è per l'agente di riferimento (=SOMMA di tutte le celle del mese di riferimento del fatturato diretto dei subagenti)
Fatturato totale: somma del fatturato diretto + indiretto
A questo punto direi che dai subagenti posso togliere la riga del fatturato indiretto....perchè l'idea che mi hai dato è di creare un foglio per ogni agente di riferimento. Quindi se il subagente diventa agente continuerà ad esistere nel vecchio foglio però a sua volta ci sarà un foglio tutto suo con i suoi subagenti....è corretto?
di Marius44 data: 04/07/2015 16:55:33
Ciao Emanuele
Qualche volta perdo la pazienza e invito gli utenti a fare un piccolo sforzo per mettere in grado chi volesse aiutarli a farlo nel migliore dei modi e, principalmente, per arricchire il bagaglio di conoscenze di ciascuno. Vedo che qualcosa l'ho ottenuta e sono contento di ciò.
Non pensare, comunque, che mi ero scordato. Anzi? Ti allego il file sul quale sto lavorando. Come vedi riporta, in buona sostanza, quello che dici nel tuo ultimo post.
La UserForm servirà per l'inserimento degli Agenti di Riferimento e/o dei Sub-Agenti (per vedere la maschera Alt+F11) ma è ancora incompleta; manca, infatti, il codice per la registrazione.
Già, la registrazione degli Agenti! Deve tener conto di molti riferimenti e, cosa basilare, assegnare un codice univoco a ciascuno. In base a detto codice si potranno (dovranno) recuperare i dati da esporre nel foglio dell'azienda.
Nel Foglio Base (il primo) vi saranno dei pulsanti per le varie opzioni. Se vai verso destra vedrai che ci sono gli elenchi dei nomi e dei codici per agenti e sub-agenti e la tabella fatturato/pct.
Sei d'accordo su come ho impostato il problema? Se si, devi provare ad andare avanti (sbagliando s'impara) e ci risentiamo. Da parte mia continuo.
Ciao e buon weekend
Mario
di emanu (utente non iscritto) data: 05/07/2015 21:57:27
Ciao Mario,
ti ringrazio.
Ho controllato il file che ti riallego con alcuni dati reali per gli agenti (al momento solo questi). Ho notato che la provvigione resta fissa sul fatturato diretto al 20%, anche se per prova inserisco importi superiori a 5000.
Inoltre non ho compreso perchè il totale ricavo diretto e indiretto sia la somma di ricavo diretto e residuo ricavo indiretto (non ho ben compreso a cosa serva questa riga che va ad annullarsi e restare a zero).
Per quanto riguarda la userform, ho cominciato a riempire la tabella agenti con codici.
Da quello che ho capito facendo alt+f11, compariranno pulsanti nel foglio base che renderanno più semplice il lavoro, a tal proprosito volevo chiederti:
sarà possibile ricercare agenti e quando li ricerco che succede? si apre un foglio con solo il loro riepilogo?
Grazie
Buona serata
di Marius44 data: 06/07/2015 15:09:47
Ciao Emanuele
La percentuale cambia a seconda dei livelli e dei parametri fissati, Per andare a 30% deve superare 25000 (oppure devi cambiare i parametri)
Per quanto attiene i "dati reali" da te immessi, stai calmo. Come già detto, se il tutto deve essere automatico, SOLO i dati riguardanti il fatturato saranno immessi manualmente, tutto il resto no.
Un Agente "guadagna" la percentuale riferita al SUO fatturato PIU' la differenza tra la percentuale raggiunta cumulando i fatturati (suo e dei sub-agenti) MENO ciò che è stato pagato ai sub-agenti. Sempre se non ho capito male.
La UserFor NON E' quella che hai riempito (quello è solo il foglio dove andranno i dati). La maschera (o UserForm) la vedi cliccando Alt+F11 e poi facendo doppio-clic su Form1
Se guardi il Foglio Base (Foglio1) quello dove c'è la tabella fatturati/pct c'é scritto (ma in seguito saranno pulsanti) Visualizza: Agenti, Sub-Agenti, Azienda. Ed ancora: Inserisci Agente (poi verrà Salva e chiudi).
In conclusione: vedi se vanno bene i livelli e le relative pct.
Fra qualche giorno ti invierò il file aggiornato.
Ciao,
Mario
PrMtLv_2
di Marius44 data: 07/07/2015 16:13:51
Il file allegato PrMtLv_2 è, a mio avviso, completo per quanto riguarda automazioni tra i fogli e formule nei fogli degli agenti. Il tutto DEVE essere automatico (spostamenti tra fogli tramite i pulsanti Visualizza e Ritorna) come pure i calcoli (nelle celle “colorate”). Le uniche immissioni manuali sono ammesse SOLO NELLE CELLE BIANCHE.
Ho inserito il primo foglio agente Fabrizio _ (è previsto l’inserimento in una maschera di Cognome e Nome; se non si vuole inserire il Nome indicare un _) compresi i suoi sub-agenti.
Prova ad inserire tu il secondo foglio utilizzando il pulsante Inserisci Nuovo Agente e seleziona l’opzione Agente. Una volta completato l’inserimento avrai davanti la schermata del nuovo foglio. Clicca sul pulsante Ritorna. Utilizza di nuovo il pulsante Inserisci Nuovo Agente per inserire i sub-agenti (uno alla volta) selezionando l’opzione Sub-agente selezionando nel ComboBox il relativo agente di riferimento. Per visualizzare un foglio utilizza i pulsanti relativi e seleziona il nominativo.
I codici di Agente e Sub-agente vengono creati in automatico. A sinistra della dicitura Agente di riferimento noterai una cifra: è il numero di sub-agenti che fanno capo al titolare del foglio. Si possono inserire fino a 99 agenti e, per ciascuno, 99 sub-agenti. UNICO LIMITE: NON CI POSSONO ESSERE DUE FOGLI CON LO STESSO NOME.
Una volta completato l’inserimento rimandami il file (denominalo con un numero progressivo).
Per quanto riguarda il Foglio Azienda: ho visto che hai inserito dei dati in Fatturato no agenti (senza indicazioni di pct). Poi, più in basso, Agenti Assiconf S.r.l. coi i dati di fatturazione identici a quelli del primo riquadro. Perché questa distinzione? A cosa si riferisce il riquadro No Agenti?
Fammi sapere se va bene così com'è impostato. Aspetto notizie.
Ciao,
Mario
di emanu (utente non iscritto) data: 09/07/2015 08:28:30
Ciao Mario,
ieri sono riuscito a capire bene il meccanismo molto "macchinoso" e a mio parere molto complesso delle provvigioni.
A questo punto mi chiedo, anche se dopo ciò che hai fatto sono rimasto senza parole sulle funzionalità dell'applicativo Office, se Excel sia in grado di poter gestire una così complessa situazione.
Cerco di spiegarti bene come funziona.
L'agente ha i diversi scaglioni di percentuale associati al suo guadagno. Il problema nasce nel fatto che se ad esempio a luglio l'agente A01 fattura 4500 euro di fatturato diretto, non appena un subagente A02 fattura qualcosa che consente all'agente A01 di superare la soglia 5000, scatta la nuova percentuale del 30%. Però se subito dopo un altro subagente (o anche l'agente stesso per fatturato diretto) gli aumenta il suo fatturato totale la nuova percentuale vale solo su quello.
Esempio
A01:
alla data 15/07/2015 fattura direttamente 4500.
alla data 16/07/2015 A01AS01 fattura 580.
Totale A01: 5080, pertanto nuovo scaglione 30%. Ma dei 5080 prenderà ancora il 20%. Il nuovo scaglione si applica sulle somme successive il raggiungimento della nuova soglia, quindi....se:
alla data 18/07/2015 A01 o A01AS01 o A01AS02...o altri subagenti fatturano 1000 euro.
Report finale: A01 avrà guadagnato il 20% su 5080 e il 30% sui 1000 euro.
Ulteriore fatturato sarà conteggiato al 30%.
Tanto per complicare la vita, l'obiettivo del nuovo livello ha valore trimestrale. Quindi se l'agente nel periodo giugno-luglio-agosto fattura 5000 automaticamente passa di livello. Se arriva a 4999, comunque resta nel livello che ha raggiunto.
Esempio A01 fattura di totale diretto e indiretto:
Giugno: 2500
Luglio:2000
Agosto: 1500
Ha raggiunto l'obiettivo dei 5000 e passa al 30%.
Se dovesse arrivare a 4999, resta al 20% e il conteggio si riazzera rimanendo però sul livello precedentemente raggiunto.
Spero di averti spiegato bene le modalità molto complesse.....tra l'altro il guadagno sul subagente è sul fatturato che quest'ultimo fa...quindi se non ho capito male il tuo calcolo, mi basta spostare la formula dalla cella del guadagno del subagente a quella del suo fatturato diretto per ottenere il differenziale.
Per il resto che dirti....sono davvero colpito dal file che mi hai allegato...è stupenda la semplicità con cui posso inserire o ricercare agenti e subagenti....ti sono davvero molto grato....ma ancora una richiesta:
da solo non ci sarei mai riuscito....ma vorrei apprendere le modalità con cui hai creato quei "pulsanti" sul foglio base....conosci un buon manuale che posso acquistare che parta dalle basi e mi consenta di apprendere le nozioni tali da poter essere in grado da solo di ricreare un foglio simile?
Ancora grazie per il tempo, la pazienza
di Marius44 data: 09/07/2015 17:21:07
Ragazzi com’è dura quando ti lasciano per 48 ore senza collegamento Internet. Hanno spostato ad altra centralina la mia linea telefonica e per due giorni sono rimasto “isolato”.
Ma veniamo a noi. Parto dall’ultima tua richiesta: migliorare la preparazione. Si può, eccome! In commercio ci sono tanti di quei manuali, sia Excel sia VBA, che non saprei dirti qual è il migliore. Secondo me, per chi è agli inizi, uno vale l’altro. L’importante è che illustrino bene i concetti base.
I “Pulsanti” – non ho la versione 2013 ma credi che debbano essere sempre stare al loro posto, e cioè: barra delle funzioni -> Sviluppo -> Inserisci; qui ti si apre una mascherina con Controlli Modulo e Controlli ActiveX; quelli che ho usato io sono i Controlli Modulo a cui poi ho associato una Sub con le operazioni da fare (le trovi facendo Alt+F11 -> doppio clic su Foglio1)
Ed ora il problema “Nuovo”: non avevi parlato (anzi, avevo capito male io) di percentuali progressive. Nulla di grave, ma le formule sono un po’ diverse come pure è diversa la tabella dalla quale attingere i dati (non solo le percentuali). Ci ho già lavorato sopra e, quanto prima, ti invierò il nuovo file nel quale ho implementato/completato il Foglio Azienda.
Veniamo al calcolo delle percentuali (l’esempio vale per tutti gli Agenti o subAgenti – il fatto che si trimestrale non incide, e vedremo i perchè).
Un Nominativo raggiunge i seguenti fatturati:
Lug 3000
Ago 4000
Set 5000
Primo e secondo mese 20% (ovvero primo mese 20% e secondo 30%??)
Terzo mese 30%
Se ho capito bene e cumulando i fatturati dei tre mesi dovremmo avere:
Lug 3000 x 20%
Ago 2000 x 20% (meglio 1999 x 20%) PIU’ 2000 x 30% (meglio 2001 x 30%)
Set 5000 x 30%
E’ esatto?
Se si, andiamo avanti supponendo che abbia un subAgente
Per quanto concerne l’Agente-capo, se fattura come sopra, nulla cambia. Per il sub-Agente fattura
Lug 3000
Ago 4000
Set 5000
Per il sub-Agente i calcoli sono come sopra; per l’Agente-capo dovrebbe essere
Fatturato diretto Fatturato indiretto Fatturato cumulato
Lug 3000 3000 6000 di cui 3000 x 20% + 1999 x 20% + 2001 x 30%
Ago 4000 4000 8000 di cui 1999 x 20% + 5001 x 30%
Set 5000 5000 10000 di cui 4999 x 20% + 5001 x 30%
Ma nel trimestre considerato sia l’Agente sia il sub-Agente hanno raggiunto, complessivamente, un fatturato di 9000 ciascuno e, pertanto, l’Agente-capo 18000. E’ così?
Nel trimestre successivo si riparte da zero (non capisco cosa intendi per “livello raggiunto”.
Fammi sapere al più presto per completare il lavoro.
Ciao,
Mario
di emanu (utente non iscritto) data: 09/07/2015 17:55:57
Ciao Mario,
1. Un Nominativo raggiunge i seguenti fatturati:
Lug 3000
Ago 4000
Set 5000
In questo caso guadagna 20% Lug, 20% Ago, 30% Set (la somma vale solo al termine dei 3 mesi).
2. In realtà non è molto semplice perchè non appena l'Agente Capo sia che si tratti di fatturato diretto, sia che si tratti di fatturato indiretto supera i 5000 parte la nuova soglia ma non da 5000.
Mi spiego meglio:
Se sono a 4000 e grazie al fatturato indiretto di euro 1500 raggiungo la soglia 30%, in realtà guadagnerò il 20% di 5500 (perchè anche se vero che ho raggiunto la nuova soglia che ha come base 5000, in realtà la nuova soglia parte dopo il mio raggiungimento) e quindi se dopo questi 5500 guadagno ad esempio 2000 allora il mio guadagno sarà:
20% di 5500
30% di 2000
Tutto ciò sempre nello stesso mese....per quello secondo me è troppo complesso...ti allego un piccolo schema per cercare di farti comprendere meglio l'idea....guarda le date di riferimento....Grazieee di tutto
di Marius44 data: 13/07/2015 12:58:39
Ciao Emanuele
Con le nuove informazioni ho dovuto "ripensare" all'impostazione della scheda (Prova_Marius che allego) prevedendo una sorta di PrimaNota dove registrare le operazioni. I calcoli nella parte destra in alto sono automatici mentre per la parte bassa e per il calcolo provvigioni "a richiesta" devo studiare le formule relative. Ovviamente se l'impostazione della scheda fosse accettabile, bisognerà rivedere anche la scheda dell'Azienda.
Fammi sapere, intanto, come ti sembra la scheda.
Ciao,
Mario
di emanu (utente non iscritto) data: 13/07/2015 13:41:23
Ciao Mario,
la scheda mi sembra ottima nel senso che se non ho capito male, quì dovrei avere un riepilogo relativo al corretto fatturato e salto di provvigione dell'agente?
Ancora due cose:
1. ho acquistato 2 libri...spero sia spiegato passo passo....così con un pò di calma e buona volontà potrò acquisire le conoscenze che hai utilizzato per creare questi automatismi molto efficaci ;)
Il primo: Microsoft Excel 2013. Macro e VBA Alessandra Salvaggio
Il secondo: Excel e Office VBA. La guida completa Hoepli (questo dopo aver letto e studiato il primo)
Ce la metterò tutta! Promesso
2. Non ti avevo risposto ad un tuo quesito relativo ad una tabella sull'azienda che avevo chiamato "Tabella Agenti no fatturato"....E' una semplice tabella che imcrementa il fatturato diretto dell'azienda...i cui agenti hanno acquistato un sussidio ma non faranno gli agenti e quindi non continuano il percorso...
Grazie come sempre....
di emanu (utente non iscritto) data: 15/07/2015 17:40:56
Ciao Mario,
per errore ho meso la spunta su risolta....o forse ho dimenticato di torglierla
di emanu (utente non iscritto) data: 19/07/2015 16:18:38
Ciao Mario,
è da un pò che non ci sentiamo....vorrei capire il meccanismo con cui stai costruendo il nuovo file...così da poter creare le parti mancanti.
Fammi sapere
Buona Domenica
di Marius44 data: 19/07/2015 23:56:23
Ciao Emanuele
pensavi fossi sparito! Beh, no! Solo che doveri familiari, piaceri parentali e impegni di varia natura non m'hanno lasciato molto tempo a disposizione.
Ma veniamo a noi.
Ho realizzato (ma solo per questa volta e perché hai dimostrato voglia d'imparare) quanto da te richiesto. DEVI TENER PRESENTE che tutto ( o quasi ) è automatico; alcune immissioni manuali SOLO nelle celle non colorate.
Il pulsante di Visualizzazione della scheda di riepilogo Azienda calcola inserisce le colonne relative agli agenti e riporta i totali di fatturato e l'ammontare delle provvigioni di ciascuno; riporta anche il totale del fatturato dei NO agenti. Non fa alcun calcolo provvisionale per l'azienda perché non so come va fatto. Puoi completarlo tu.
Ciascuna scheda ha delle colonne di PrimaNota (dove inserire data, codice agente e importo) e, nella parte alta dx vedrai le somme di ciascuno mentre nella parte bassa (cliccando sul pulsante relativo) appariranno le provvigioni di ciascuno. le due scritte in rosso (parte alta sx) eseguono un calcolo provvisionale per mese e per agente (secondo quello che è inserito nelle celle bianche adiacenti).
Ti allego il file. Provalo e fammi sapere come va.
Ciao,
Mario
PS. Troverai una scheda col mio nome che m'è servita per le prove. Puoi eliminarla ma non scordarti di cancella quanto scritto nelle colonne "L,M,N,O" della scheda "base"
di Marius44 data: 22/07/2015 10:45:14
Ciao Emanuele
son passati tre giorni ed ancora non mi hai detto nulla. C'è qualcosa che non va? Oppure non riesci a "muoverti" con i pulsanti?
Aspetto tue notizie. Ciao,
Mario
di emanu (utente non iscritto) data: 23/07/2015 18:24:59
Ciao Mario,
stavo provando ad utilizzare il file....che dire funziona egregiamente!
Ho atteso un pò nel risponderti ed in particolare vorrei chiederti alcune cose:
- nella scheda base c'è il pulsante "modalità d'utilizzo" che non è operativo....quali funzioni dovrebbe svolgere.
- sempre nella scheda base posso aggiungere sotto la tabella delle provvigioni agente una tabella per l'azienda...oppure non posso scriverci perchè se apporto modifiche rischio di bloccare alcune funzionalità da te create?
- per quanto riguarda l'azienda voglio provare a costruire il calcolo delle provvigioni....pertanto devo creare la tabella delle provvigioni....quello che mi preoccupa è il collegamento con le tabelle degli agenti....quando utilizzo le formule devo sempre fare riferimento alla scheda base giusto?
di Marius44 data: 23/07/2015 19:54:42
Ciao Emanuele
- scheda base: pulsante "Modalità di utilizzo" - dovrà visualizzare un foglio (pdf, word, oppure una listbox con riferimento ad un foglio .xls) dove ci saranno le spiegazione per l'utilizzo del file;
- scheda base: "posso aggiungere sotto la tabella delle provvigioni agente una tabella per l'azienda".
NOOOOO!
Puoi aggiungerla nelle celle C22:K35 (crea un tuo pulsante che agisca SOLO su quelle celle);
- "per quanto riguarda l'azienda voglio provare a costruire il calcolo delle provvigioni....pertanto devo creare la tabella delle provvigioni....quello che mi preoccupa è il collegamento con le tabelle degli agenti....quando utilizzo le formule devo sempre fare riferimento alla scheda base giusto?" In parte. Il calcolo delle provvigioni globali dell'azienda non ho capito come vanno fatte. Tu prova (nel riquadro che t'ho detto - oppure in un Foglio a parte che aggiungerai alla fine; dopo potremo eliminarlo) e ... sbizzarisciti (ho sempre la mia copia!!!).
Hai provato tutte le funzionalità? I calcoli delle provvigioni sono come da te richiesto? Le verifiche danno i risultati attesi?
Ciao,
Mario
di emanu (utente non iscritto) data: 28/07/2015 22:56:14
Ciao Mario,
ho fatto tutte le verifiche, utilizzando dati reali così da non sbagliarmi. Funziona molto bene il calcolo...l'unico problema...e sinceramente non ricordo se ti avevo già dato informazioni in merito...riguarda il fatto che se un agente o subagente ha raggiunto la nuova soglia di percentuale (es. 30%), anche se il mese successivo non ha fatturato la soglia di quella percentuale....la mantiene comunque....perchè è come se conquistasse quel livello e non può più andare sotto....(es. se a giugno fatturo 6000, raggiungo il 30%....se a luglio mi va male e fatturo solo 3000 comunque avendo raggiunto il 30% prendero il 30% su 3000)....
per l'azienda...mi trovo in difficoltà...avendo creato tutto in automatico...e l'unica formula che vedo nel foglio (tutte le altre ci sono ma sono in VBA) è quella della matrice nella scheda agente e sub relativa al mese...non so come procedere...l'azienda ha un calcolo aihmé più complesso....ti allego un foglio con le tabelle...
Un'altra domanda...per Assiconf ho notato che avevi creato delle tabelle agenti che hanno però lo stesso codice degli altri che carico...
di Marius44 data: 29/07/2015 07:32:13
Ciao Emanuele
mi dici che "il livello raggiunto rimane" - parlo delle percentuali.
Domanda: vale per tutto l'anno? vale per un trimestre? vale per un mese (come ho capito io)? o per quanto tempo rimane valido?
per l'Azienda: come ti ho detto aggiungi un foglio alla fine e fai tutti calcoli che vuoi relativamente a quest'ultimo foglio collegandoti agli altri fogli; alla fine, quando i calcoli esporranno quello che t'aspetti, vedremo come riadattarlo.
Codice Agenti: se l'inserimento avviene tramite la "maschera" che t'ho fatto hanno sempre un codice diverso (guarda i tre o i sei caratteri - qualcosa di diverso dev'esserci , altrimenti rivediamo). A me sembra a posto.
Sto preparando il foglio Istruzioni (che poi t'allegherò).
Fammi avere le notizie che t'ho chiesto per le percentuali in modo da adeguare i calcoli.
Ciao,
Mario
di Marius44 data: 31/07/2015 18:32:18
Ciao Emanuele,
non sono io che sparisco ma tu che ... t'infratti
Ho rivisto le lunghe ... lettere che ci siamo scambiati. In effetti in una parlavi di provvigioni trimestrali e poi ripartono da zero.
Bene. Ho rivisto il tutto ed aggiustato con queste caratteristiche (ho tolto il Foglio Verifica perchè non serve). Ho aggiunto il foglio Istruzioni.
Manca sempre il calcolo per l'Azienda. Prova a farlo tu oppure dimmi come ti serve e vediamo di realizzarlo.
Fammi sapere al più presto che poi vorrei andare ... in ferie col mio nipotino.
Ciao,
Mario
PS il file allegato è "PrMtLv_Trimestr"
di emanu (utente non iscritto) data: 31/07/2015 20:52:11
Ciao Mario,
scusami...hai ragione...purtroppo problematiche familiari non mi hanno consentito di mettermi di "testa" sul file.
In merito al calcolo provvigioni agente...mi piace moltissimo il file che hai realizzato con la divisione mensile...mi chiedevo se esiste una formula che consenta una volta raggiunto un livello di mantenerlo a vita.
Per quanto riguarda l'azienda...il calcolo è lo stesso di cui ci siamo parlati tempo addietro...ovvero la differenza tra percentuale raggiunta dall'azienda e percentuale dell'agente...il resto calcolato sul fatturato agente....ti allego un file con cui spero di chiarire meglio i calcoli...vorrei impostarli ma vorrei capire meglio la scheda che avevi creato.
Quello che differenzia l'azienda dall'agente è anche la tabella provvigioni che ho inserito in apposita scheda...
Grazie Mille per il tempo che continui a dedicarmi
di emanu (utente non iscritto) data: 31/07/2015 21:04:04
Dimenticavo di dirti che nel file allegato "PrMtLv_Definitivoconistruzioni", ho creato la scheda provvigioni azienda dove ho inserito tutte le tabelle dell'azienda che fondamentalmente cambiano come periodo...ed ho fatto un breve calcolo di esempio....grazie a te ho finalmente compreso la formula cerca.vert....(perche la formula termina con un 2?)
Grazie
di Marius44 data: 31/07/2015 21:49:42
Ciao Emanuele
1) il calcolo delle provvigioni si può fare O mensile O trimestrale; se a te piace quello mensile non si possono rispettare i trimestri.
2) la funzione (non formula) CERCA.VERT cerca un dato nella PRIMA colonna di una tabella e restituisce un dato nella riga trovata nella colonna indicata (e nel nostro caso la colonna è la seconda, quindi 2; se avessimo una tabella composta da 10 colonne cercherebbe sempre nella prima e restituirebbe il dato nella colonna da 2 a 10 come tu vuoi, sempre uno alla volta). Al riguardo ti consiglio, comunque, di leggere l'Help di Excel.
Ed ora i calcoli per l'azienda. Precisiamo innanzi tutto come devono essere fatti: mensili o trimestrali? Abbiamo parlato di trimestrali con ripartenza da zero. E' così?
Oppure devono essere diversi: mensili per l'azienda e trimestrali per gli agenti?
Hai guardato il nuovo file che ti ho inviato? Non c'è più la scheda di verifica perchè non serve e la parte inferiore della scheda-agente riporta i calcoli delle provvigioni a trimestre. Non va bene?
Aspetto tue notizie.
Ciao,
Mario
di emanu (utente non iscritto) data: 03/08/2015 19:41:30
Ciao Mario,
grazie per la spiagazione dettagliata sulla funzione CERCA.VERT
In merito alle provvigioni Azienda, si tratta di un calcolo sempre mensile così come per gli agenti ed i subagenti.
Diciamo che ogni trimestre viene fatta una verifica e se azienda, agente o subagente hanno raggiunto (sommando i fatturati dei tre mesi) una soglia, la ottengono....una sorta di bonus per così dire.
A parte ciò, ho notato che ogni mese il tutto si riazzera....secondo te è possibile (sempre se non sto chiedendo cose fantascientifiche) far si che se a giugno raggiungo il livello 30%....a luglio io parta da quel livello? perchè come ti dicevo una volta raggiunti i livelli ti restano a vita...non puoi più scendere sotto.
di emanu (utente non iscritto) data: 03/08/2015 19:43:16
Scusami se ti rispondo solo adesso...ma stranamente non mi è arrivata la notifica via mail....
Grazie
Buona Serata
di Marius44 data: 07/08/2015 19:26:18
Caro Emanuele
Finalmente è completo (quasi, mancano le Istruzioni per la registrazione dei dati). Dovrebbe andar bene, fai delle prove e fammi sapere.
Come noterai ho cambiato lo schema della SCHEDA, ho eliminato il foglio Verifica perché non necessario, ho accorpato le percentuali dell’Azienda (guarda Foglio Base celle AF15:AJ25) mettendo le date da te indicate però all’incontrario (dalla più futura alla attuale)
E’ mia intenzione: accorpare in uno i Fogli Azienda e PrimaNota in modo da avere tutto in un’unica videata.
Quando clicchi su Visualizzare azienda vengono ricalcolati in background tutti i dati; quando clicchi su PrimaNota Azienda i calcoli NON vengono aggiornati automaticamente ma devi cliccare sul pulsante presente nel Foglio.
Ti allego il file (PrMtLv_Ultimo)
Aspetto tue notizie e … poi festeggeremo.
Ciao,
Mario
- Vuoi Approfondire?
