Quando un contabile registra in contabilità le movimentazioni bancarie c’è sempre qualcosa che non torna.
Tra i vari problemi ce n’è uno che è particolarmente odioso: il caso dell’incasso di una somma a saldo di diverse fatture che però (maledetti loro) non vengono specificate.
O più in generale dover scoprire da quali importi è composto un saldo.
Parte così una sorta di caccia al tesoro. Si stampano i partitari del soggetto che ci ha inviato il denaro e con una penna si iniziano a spuntare le fatture, partendo dalle meno recenti, per comprendere quali fatture potrebbe aver saldato il denaro ricevuto.
I più virtuosi fanno questo su Excel, guadagnando del tempo.
Ma non è sufficiente, serve una soluzione migliore. Di seguito te la propongo.
Ti mostrerò come costruire un foglio Excel in grado, automaticamente e velocemente di trovare al tuo posto da quali importi è composto il tuo saldo.
Senza impazzire a cercarli.
Se non hai tempo o non hai voglia di impostare il modello, puoi scaricarti il mio file Excel già pronto.
Dovrai solo inserire i numeri e premere un tasto.
Indice del post
Prima di tutto, chiama
Voglio specificarlo, perché a volte (incomprensibilmente) non lo si fa.
Se stai cercando la composizione del saldo di un pagamento, chiama chi ha inviato il denaro. E’ una soluzione banale che chiude la questione prima ancora di mettere su soluzioni complicate.
Una chiamata veloce al reparto contabilità o tesoreria del cliente/soggetto che ci ha inviato il denaro, chiedendo quali partite volesse andare saldare, è la prima cosa da fare.
Per diversi motivi, fra i quali il non voler attendere 20 minuti al telefono, potresti non riuscire nel tuo intento. Rimanendo tristemente con l’estratto conto in mano, pensieroso e scoraggiato dall’operazione di spunta che ti attende.
Se sei in questa situazione, fermo lì e continua a leggere.
Il Risolutore di Excel
La soluzione che ti propongo utilizza un componente aggiuntivo di Excel che devi attivare prima di iniziare: il Risolutore (sembra il nome di un film di Schwarzenegger, lo so).
Puoi attivare il risolutore in questo modo:
- Clicca sul menu File -> Opzioni
- Premi sulla sezione Componenti aggiuntivi
- Vai su Componenti aggiuntivi di Excel nella casella Gestisci
- In Componenti aggiuntivi disponibili seleziona Componente aggiuntivo “Risolutore” e poi Clicca su Ok
NB. In Excel 2007 troverai la scheda Componenti aggiuntivi in Opzioni di Excel dopo aver cliccato sul pulsante con l’icona di Office in alto a sinistra. In Excel 2003 la troverai sotto il menu Strumenti
Al termine di questo processo, per verificare che il componente aggiuntivo sia attivo, troverai l’icona Risolutore all’interno del menu Dati all’interno del gruppo Analisi
Per maggiori info sull’installazione del Risolutore ti rimando direttamente alla pagina di Assistenza Microsoft.
Per quanto possa essere utilizzato anche per scopi molto complessi, utilizzeremo il Risolutore per il più semplice di questi: provare tutte le combinazioni possibili fino a trovare il risultato.
Daremo in pasto al risolutore un saldo e, tramite alcune celle di controllo simili ad interruttori e collegate a ciascun numero che potrebbe formare quel saldo, lui arriverà alla combinazione vincente.
In pratica avremo:
- Una lista di importi tra i quali cercare (es. le fatture ancora da saldare di un cliente)
- Il saldo di cui vogliamo conoscere la composizione (es. un bonifico incassato senza informazioni sulle fatture saldate)
- La lista di celle di controllo (tante quante gli importi tra i quali cercare) contenenti un valore binario (0/1)
- Una formula di check che verifica le combinazioni
Impostare il modello
L’ideale è impostare il modello in modo che ti sia utile in più occasioni. Potrai così salvare il modello ed utilizzarlo all’occorrenza.
Nell’immagine che vedi sotto ti mostro la soluzione che trovi anche nel template che puoi scaricare.
- Le celle da D13 a D22 contengono gli importi (es. fatture) che potrebbero comporre il saldo obiettivo
- Cella C9 ospita il saldo del quale vogliamo conoscere la composizione (nel nostro esempio 15.000)
- Le celle da E13 a E22 sono valorizzate a 0 e potranno contenere solo valori pari a 0 o 1.
- Cella C10 contiene una formula di controllo che ci sarà utile nell’impostare il Risolutore, eccola:
=MATR.SOMMA.PRODOTTO(D13:D22;E13:E22)-C9
Analizziamola insieme.
La funzione “MATR.SOMMA.PRODOTTO” esegue la somma dei prodotti di coppie di valori provenienti da due liste. Te lo mostro graficamente:
Tornando al nostro modello, al risultato della funzione “MATR.SOMMA.PRODOTTO(D13:D22;E13:E22)” andiamo a sottrarre il valore di C9, ovvero il nostro saldo obiettivo.
Perché?
Poiché quando il risultato di questa formula sarà 0 (zero) vorrà dire che la sommatoria dei prodotti sarà uguale al saldo obiettivo.
E quindi?
Così impostato il modello potremo chiedere al nostro risolutore: “Utilizzando soltanto 0 o 1, cambia i valori della colonna di check in modo che il risultato della funzione di check sia 0”
Risultato?
Avremo un “1” accanto a quegli importi che formano il nostro saldo obiettivo: avremo scoperto la composizione del saldo.
Guarda questo esempio commentato:
Come vedi da questo esempio, gli importi moltiplicati per il valore di check saranno pari a 0 quando il valore di check è 0 e saranno pari all’importo stesso quando il valore di check è uguale a 1.
Sommando questi prodotti otterremo un totale che, sottratto al saldo obiettivo, restituirà un valore pari a 0 solo quando avremo assegnato “1” al valore di check dei giusti importi.
Usare il risolutore
A questo punto avrai già un modello funzionante ed utile all’occorrenza per testare manualmente una composizione di saldo.
Con il Risolutore però, faremo fare il lavoro a lui, senza perdere tempo.
A questo punto lanciamo il Risolutore e riempiamo la maschera con i valori sulla scorta di quanto vedi in questo esempio (clicca per ingrandire):
Questi i dati da inserire:
- “Imposta obiettivo”: inserisci la cella che contiene il tuo saldo obiettivo
- “A:” seleziona “Valore di” e imposta 0
- “Modificando le celle variabili”: inserisci il range di celle della colonna di check
- “Soggette ai vincoli”: aggiungi tre diverse condizioni riguardanti il range di celle della colonna Check:
- Devono essere maggiori/uguali a 0
- Devono essere minori/uguali a 1
- Devono essere numeri interi
Lascia tutto invariato ed ora premi su RISOLVI.
A questo punto si aprirà la finestra di dialogo “Risultati Risolutore” e contemporaneamente potrai vedere sul foglio di lavoro quali valori di check sono pari a 1: gli importi in corrispondenza di questi “1” sono proprio gli importi che compongono il saldo.
Informazioni e indicazioni aggiuntive
Ci sono due considerazioni finali da fare:
- Il risolutore potrebbe trovare più di una soluzione, caso non comune ma neanche così raro in contabilità sopratutto per fatture di importo ricorrente.
Per risolvere questo problema dovremmo scendere nel complesso, me ne occuperò presto attraverso un videocorso dedicato che comprenderà questo ed altri utilissimi tool per l’ufficio amministrativo. - All’aumentare degli importi da cui “pescare” per trovare il saldo obiettivo le combinazioni che il Risolutore deve provare aumentano esponenzialmente. Questo significa una grande necessità di potenza di calcolo. Se hai un computer di scarsa potenza, potresti pensare di ridurre preventivamente il numero di importi da sottoporre a test, selezionandone un gruppo ristretto da far elaborare al Risolutore
- Un altro trucco per aiutare il Risolutore è quello di impostare manualmente a “1” il valore di check per quell’importo/i parziale/i che siamo certi compongano il saldo. Il risolutore avrà un indizio in più e dovrà provare meno combinazioni
- Imposta la griglia degli importi da sottoporre a test nel modo che preferisci, per esempio potresti replicare le colonne del partitario usate dal tuo programma gestionale così da poter copiare direttamente da questo e incollare su Excel
