Una delle operazioni più comuni per un’attività commerciale è preparare preventivi e fatturare la merce venduta: operazioni che si possono svolgere agevolmente con un foglio di calcolo Excel. Meno semplice far interagire i fogli.Immaginiamo di utilizzare Excel per compilare un preventivo: invece di inserire i dati (prodotto, codice, prezzo, giacenza in magazzino, ecc) e ricopiarli da elenchi separati (magari anche cartacei), possiamo automatizzare queste operazioni, creando una serie di fogli di lavoro connessi tra loro.
=> Calcolo IVA online con il tool gratuito di PMI.it
Creazione cataloghi
Rinominiamo il foglio 1 e 2 di un nuovo file Excel rispettivamente come MERCE e CLIENTI, dopodiché trascriviamo gli archivi prodotti e clienti della nostra attività. Nel foglio MERCE indichiamo i campi: Prodotto, Cod. Prodotto, Giacenza, Prezzo unitario, Aliquota IVA, IVA. Per calcolare l’IVA trascriviamo sulla cella F2 (del foglio MERCE) la formula “=D2*E2”, dopodiché col quadratino di riempimento trasciniamo nelle celle sottostanti.
Nel foglio CLIENTI invece ricreiamo i campi: Cod., Cognome Nome e/o Rag. Sociale, Indirizzo, Città, Prov, C.Fisc.
Prospetto Preventivi
Una volta creati gli archivi ci possiamo dedicare alla parte più importante: le maschere Preventivi e Fatture.
Innanzitutto, rinominiamo il Foglio 3 come PREVENTIVO, e ricopiamo all’interno il contenuto sotto riportato (nelle stesse celle). Si può anche personalizzare il preventivo, agendo sulla maschera: inserendo il logo dell’azienda con la ragione sociale, colorando le colonne o formattando i dati inseriti.
Ora possiamo iniziare a collegare i vari fogli. Selezioniamo le celle B12:B22, attiviamo la barra DATI e clicchiamo sul pulsante CONVALIDA DATI. All’interno della finestra “Convalida dati“, selezioniamo ELENCO dal menù a tendina e scriviamo =MERCE!A2:A1000 nella casella Origine. Infine clicchiamo su OK.
Ogni volta che faremo click su una cella della colonna Prodotto, potremo selezionare le merci da inserire nel preventivo direttamente dal menù a tendina collegato al catalogo. Ora iniziamo invece ad automatizzare il foglio facendo in modo che, una volta selezionato il prodotto, vengano eseguiti tutta una serie di calcoli. Realizziamo i punti successivi ricordandoci di confermare col tasto INVIO dopo aver trascritto ogni formula. Posizioniamoci nella cella D12 del foglio PREVENTIVO. Utilizziamo ora la funzione CERCA.VERT (che ci permetterà di ricercare il costo del prodotto selezionato), e la funzione SE (che invece lascerà vuota la cella qualora non venga selezionato nessun prodotto).
Ora scriviamo =SE(B12=0;””;(CERCA.VERT(B12;MERCE!A1:F1000;4;FALSO))).
Nella cella A12 scriviamo una formula che trascriva automaticamente il codice del prodotto selezionato: =SE(B12=0;””;CERCA.VERT(B12; MERCE!A1:F1000;2;FALSO)).
Posizioniamoci su E12 per scrivere =SE(D12=””;””;C12*D12). In tal modo calcoleremo il costo complessivo della merce selezionata.
Posizioniamoci su G12 e scriviamo: =SE(F12=””;E12;E12-(E12*F12)). Questa formula ci consentirà di calcolare un eventuale sconto applicato al prodotto. La percentuale di sconto può essere trascritta sulla cella E12.
Scriviamo ora sulla cella H12 la seguente formula: =SE(B12=””;””;(CERCA.VERT(B12; MERCE!A1:F1000;5;FALSO))*G12) per calcolare l’IVA.
Posizioniamoci su I12 e scriviamo una formula che consenta di visualizzare il costo, comprensivo di Iva ed eventuali sconti, relativo a ciascun prodotto: =SE(B12=0;””;G12+H12).
Infine scriviamo sulla cella H24 la formula per calcolare il totale del preventivo: =SE((SOMMA(I12:I22))=0;””;SOMMA(I12:I22)).
Il collegamento del foglio PREVENTIVO all’archivio prodotti dovrebbe essere terminato. Proviamo adesso a collegarlo anche all’archivio Clienti, attraverso questa breve procedura: posizionarsi sulla cella A5, attivare la barra DATI e cliccare sul pulsante CONVALIDA DATI: nella finestra “CONVALIDA DATI” scegliere “ELENCO” dal menù “CONSENTI” e scrivere =CLIENTI!B2:B1000 nella casella ORIGINE. Al termine clicchiamo su OK.
Posizionarsi sulla cella A6 e digitare la seguente formula
=SE($A$5=0;””;CERCA.VERT($A$5;CLIENTI!B2:G1000;2;FALSO)), Passare alla A7 e digitare =SE($A$5=0;””;CERCA.VERT($A$5;CLIENTI!B2:G1000;3;FALSO)). Infine, su B8 digitiamo =SE($A$5=0;””;CERCA.VERT($A$5;CLIENTI!B2:G1000;5;FALSO)).
Ora possiamo posizionarci sulla cella A5 e scegliere dal menù a tendina il cliente destinatario del preventivo: in automatico nelle celle sottostanti verranno trascritti i dati corrispondenti alla persona o all’azienda selezionata.
Un consiglio che possiamo darvi è quello di distinguere le celle nelle quali sono presenti le formule da tutte le altre, in modo da evitare di cancellarne erroneamente il contenuto: in questo modo, visualizzando il prospetto, sapremo subito che agiremo solo sulle celle differenziate e non su tutte le altre, su cui invece sarà Excel a lavorare.
Creare il prospetto fatture
Attiviamo la barra HOME e clicchiamo Inserisci foglio, chiamandolo FATTURA (possibilmente utilizzando gli stessi indirizzi di cella) e ricopiandovi le voci come da esempio:
Ora automatizziamo la fattura, collegandola ai fogli precedenti. Posizioniamoci sulla cella A7 e scriviamo la formula =SE(PREVENTIVO!A5=0;””;PREVENTIVO!A5) per riportare automaticamente il cliente trascritto nel Preventivo.
Per trascrive automaticamente i dati correlati al cliente (Indirizzo, Cod. Fiscale ecc.), nelle celle A8, A9, B10 e B11 riportiamo rispettivamente le seguenti formule:
- =SE(PREVENTIVO!A6=0;””;PREVENTIVO!A6)
- =SE(PREVENTIVO!A7=0;””;PREVENTIVO!A7)
- =SE(PREVENTIVO!B8=0;””;PREVENTIVO!B8)
- =SE(A7=””;””;CERCA.VERT(A7;CLIENTI!B1:G1000;6;FALSO))
Per riportare in automatico i prodotti descritti sul Preventivo all’interno della nostra fattura, scriviamo rispettivamente su A15, B15, C15 e D15 le seguenti formule:
- =SE(PREVENTIVO!B12=0;””;PREVENTIVO!B12)
- =SE(PREVENTIVO!C12=0;””;PREVENTIVO!C12)
- =SE(PREVENTIVO!D12=””;””;PREVENTIVO!D12-(PREVENTIVO!D12*PREVENTIVO!F12))
- =SE(A15=””;””;PREVENTIVO!G12)
Selezioniamo le celle E14:F14 e uniamole cliccando il pulsante < a > della barra HOME. Posizioniamoci su E15 e scriviamo =SE(A15=””;””;CERCA.VERT(A15; MERCE!A1:F1000;5;FALSO)) in modo da riportare l’aliquota IVA da applicare.
Per riportare l’IVA calcolata sul Preventivo, posizioniamoci su F15 e scriviamo =SE(E15=””;””;PREVENTIVO!H12).
Infine, selezioniamo le celle A15:F15 e, utilizzando il quadratino di riempimento, trasciniamo in basso fino alla riga 25: in questo modo ricopieremo nelle celle sottostanti le formule impostate in precedenza.
Terminiamo l’impostazione della nostra Fattura calcolando imponibile, IVA e totale fattura: posizioniamoci sulla cella B29 scriviamo =SE(SOMMA(D15:D25)=0;””;SOMMA(D15:D25)), mentre sulla cella B30 scriviamo =SE(SOMMA(F15:F25)=0;””;SOMMA(F15:F25)) e in ultimo, nella cella B33, calcoliamo il netto da corrispondere con la formula =SE(B29=””;””;B29+B30).
Ora non rimane che provare: create il vostro Preventivo e al termine spostatevi sulla Fattura: è già pronta, non rimane che stamparla e soprattutto incassarla!
