Quan esteu creant models financers a Microsoft Excel, les funcions són el nom del joc. També heu de comprovar el vostre treball, i tornar-lo a comprovar, per assegurar-vos que no hi ha cap error que passi per les esquerdes. Finalment, per fer que el vostre treball sigui ràpid i fàcil, les dreceres del teclat són un salvavides.
Funcions essencials d'Excel per construir models financers
Avui dia hi ha més de 400 funcions disponibles a Excel, i Microsoft continua afegint-ne més amb cada nova versió del programari. Moltes d'aquestes funcions no són rellevants per utilitzar-les en finances, i la majoria dels usuaris d'Excel només utilitzen un percentatge molt petit de les funcions disponibles. Si utilitzeu Excel amb finalitats de modelització financera, necessiteu una comprensió ferma de les funcions més utilitzades, com a mínim.
Tot i que n'hi ha moltes, moltes més que us resultaran útils a l'hora de crear models, aquí teniu una llista de les funcions més bàsiques de les quals no us podeu faltar.
Funció |
Què fa |
SUMA |
Suma, o suma, un rang de cel·les. |
MIN |
Calcula el valor mínim d'un rang de cel·les. |
MAX |
Calcula el valor màxim d'un rang de cel·les. |
MITJANA |
Calcula el valor mitjà d'un rang de cel·les. |
RODONA |
Arrodoneix un únic nombre al valor especificat més proper , normalment a un nombre sencer. |
REDONDACIÓ |
Rondes fins un únic número a la valor especificat més proper, generalment a un nombre sencer. |
ARRODONIR A LA BAIXA |
Rondes baix un únic número a la valor especificat més proper, generalment a un nombre sencer. |
SI |
Retorna un valor especificat només si s'ha complert una única condició. |
IFS |
Retorna un valor especificat si s'han complert condicions complexes . |
COUNTIF |
Compta el nombre de valors d'un rang que compleixen una certa únic criteri. |
COUNTIFS |
Compta el nombre de valors d'un interval que compleixen diversos criteris. |
SUMIF |
Suma els valors en una gamma que compleixen una certa únic criteri. |
SUMIFS |
Suma els valors d'un interval que compleixen diversos criteris. |
CERCA V |
Busca un interval i retorna el primer valor corresponent en una taula vertical que coincideix exactament amb l'entrada especificada. |
BUSCAR |
Busca un interval i retorna el primer valor corresponent en una taula horitzontal que coincideix exactament amb l'entrada especificada. Es retorna un error si no pot trobar la coincidència exacta. |
ÍNDEX |
Funciona com les coordenades d'un mapa i retorna un valor únic basat en els números de columna i fila que introduïu als camps de funció. |
PARTIT |
Retorna la posició d'un valor en una columna o fila. Els modeladors sovint combinen MATCH amb la funció INDEX per crear una funció de cerca, que és molt més robusta i flexible i utilitza menys memòria que la BUSCAR V o BUSCAR HL. |
PMT |
Calcula el pagament anual total d'un préstec. |
IPMT |
Calcula el component d' interès d'un préstec. |
PPMT |
Calcula el component principal d'un préstec. |
VPN |
Té en compte el valor temporal dels diners proporcionant el valor actual net dels fluxos d'efectiu futurs en dòlars actuals, en funció de l'import de la inversió i la taxa de descompte. |
Ser un bon modelador financer té molt més que simplement conèixer moltes funcions d'Excel. Un modelador especialitzat pot seleccionar quina funció és millor utilitzar en cada situació. Normalment, podeu trobar diverses maneres diferents d'aconseguir el mateix resultat, però la millor opció sempre és la funció o solució que sigui la més senzilla, clara i fàcil d'entendre per als altres.
Què cal cercar en comprovar o auditar un model financer
Si fa temps que utilitzeu Excel, probablement preferiu crear els vostres propis fulls de càlcul o models financers des de zero. En un entorn corporatiu, però, la gent poques vegades té aquesta oportunitat. En lloc d'això, s'espera que es facin càrrec d'un model existent que hagi construït algú altre.
Potser esteu assumint un paper que esteu agafant el relleu d'una altra persona i hi ha un model d'informes financers existent que haureu d'actualitzar cada mes. O us han dit que calculeu les comissions de vendes cada trimestre a partir d'un full de càlcul monstruós de 50 pestanyes que no us agrada molt l'aspecte. No només hereteu els models d'altres persones juntament amb les entrades, les suposicions i els càlculs que ha introduït el modelador original, sinó que també hereteu els errors del modelador.
Si vas a assumir la responsabilitat del model d'una altra persona, has d'estar preparat per assumir-lo i fer-lo teu. Heu de ser responsable del funcionament d'aquest model i segur que funciona correctament. Aquí teniu una llista de coses que hauríeu de comprovar la primera vegada que hereteu el model financer d'una altra persona:
- Familiaritzeu-vos amb el seu aspecte i sensació. Mireu cada full per veure quins esquemes de colors s'han utilitzat. Llegeix qualsevol documentació. Hi ha alguna clau per ajudar a veure quines cèl·lules són quines? El modelador ha diferenciat entre fórmules i supòsits codificats?
- Mireu bé les fórmules. Són coherents? Contenen valors codificats que no s'actualitzen automàticament i, per tant, provocaran errors?
- Executeu una comprovació d'errors. Premeu el botó Verificació d'errors a la secció Auditoria de fórmules de la pestanya Fórmules de la cinta per veure d'un cop d'ull si hi ha errors d'Excel al full que puguin causar problemes.
- Comproveu si hi ha enllaços a fitxers externs. Els enllaços externs poden ser una part vàlida del procés operatiu de treball, però heu de saber si aquest fitxer rep entrades de llibres de treball externs per assegurar-vos que ningú canviï els fulls o els noms de fitxers sense voler, provocant errors al vostre model. Trobeu enllaços externs prement el botó Edita enllaços a la secció Connexions de la pestanya Dades de la cinta.
- Reviseu els intervals anomenats. Els intervals amb nom poden ser útils en un model financer, però de vegades contenen errors a causa de noms redundants, així com d'enllaços externs. Reviseu els intervals amb nom al Gestor de noms, que es troba a la secció Noms definits de la pestanya Fórmules de la cinta. Suprimiu els intervals amb nom que continguin errors o que no s'estiguin utilitzant i, si contenen enllaços a fitxers externs, preneu-ne nota i assegureu-vos que siguin necessaris.
- Comproveu els càlculs automàtics. Les fórmules s'han de calcular automàticament, però de vegades, quan un fitxer és molt gran o a un modelador li agrada controlar els canvis manualment, el càlcul s'ha configurat com a manual en lloc d'automàtic. Si veieu la paraula Calcula a la barra d'estat inferior esquerra, això vol dir que el càlcul s'ha configurat com a manual, de manera que probablement tingueu una investigació complexa! Premeu el botó Opcions de càlcul a la secció Càlcul de la pestanya Fórmules de la cinta per canviar entre el càlcul manual i automàtic del llibre de treball.
A més d'aquests passos, aquí teniu algunes eines d'auditoria útils a Excel que podeu utilitzar per comprovar, auditar, validar i, si cal, corregir un model heretat perquè pugueu confiar en els resultats del vostre model financer:
- Inspeccionar el llibre de treball. Coneix les característiques ocultes del teu model i identifica les característiques potencialment problemàtiques que, d'altra manera, podrien ser molt difícils de trobar amb aquesta eina poc coneguda. Per utilitzar-lo, obriu el llibre de treball, feu clic al botó Fitxer de la cinta; a la pestanya Informació, feu clic al botó Comprova si hi ha problemes.
- F2: si les cel·les d'origen d'una fórmula es troben a la mateixa pàgina, la drecera F2 posa la cel·la en mode d'edició, de manera que aquesta drecera és una bona manera de veure visualment d'on provenen les dades d'origen.
- Traça precedents/dependents: les eines d'auditoria d'Excel tracen les relacions visualment amb fletxes de línia traçadora. Podeu accedir a aquestes eines a la secció Auditoria de fórmules de la pestanya Fórmules de la cinta.
- Avaluar la fórmula: desmunteu les fórmules llargues i complexes amb l'eina Avaluar la fórmula, a la secció Auditoria de fórmules de la pestanya Fórmules de la cinta.
- Eines de comprovació d'errors: si cometeu un error, o el que Excel creu que és un error, apareixerà un triangle verd a la cantonada superior esquerra de la cel·la. Això passarà si ometeu cel·les adjacents o si introduïu una entrada com a text, que sembla que hauria de ser un número.
- Finestra de visualització: si teniu cel·les de sortida que voleu vigilar, aquesta eina mostrarà el resultat de les cel·les especificades en una finestra independent. Podeu trobar aquesta eina a la secció Auditoria de fórmules de la pestanya Fórmules de la cinta. És útil per provar fórmules per veure l'impacte d'un canvi en les suposicions en una cel·la o cel·les separades.
- Mostra les fórmules: per veure totes les fórmules d'un cop d'ull en lloc dels valors resultants, premeu el botó Mostra les fórmules a la secció Auditoria de fórmules de la pestanya Fórmules de la cinta (o utilitzeu la drecera Ctrl+`). Mostrar fórmules també és una manera molt ràpida i senzilla de veure si existeixen valors codificats.
Dreceres de teclat d'Excel per a modeladors financers
Si dediqueu molt de temps a modelar a Excel, podeu estalviar-vos temps aprenent algunes tecles de drecera. Gran part de les habilitats del modelador es refereixen a la velocitat i la precisió, i practicant aquestes dreceres fins que es converteixin en memòria muscular, seràs un modelador més ràpid i precís.
Aquí teniu una llista de les dreceres més útils que haurien de formar part del vostre ús diari del teclat si sou un modelador financer:
Edició |
|
Ctrl+S |
Guarda el llibre de treball. |
Ctrl+C |
Còpia. |
Ctrl+V |
Enganxa. |
Ctrl+X |
Tallar. |
Ctrl+Z |
Desfer. |
Ctrl+Y |
Refer. |
Ctrl+A |
Seleccionar tot. |
Ctrl+R |
Copieu la cel·la de l'extrem esquerre a tot el rang. (Primer heu de destacar l'interval.) |
Ctrl+D |
Copieu la cel·la superior cap avall de l'interval. (Primer heu de destacar l'interval.) |
Ctrl+B |
Atrevit. |
Ctrl+1 |
Caixa de format. |
Alt+Tab |
Canvia programa. |
Alt+F4 |
Tanca el programa. |
Ctrl+N |
Nou llibre de treball. |
Maj+F11 |
Nou full de treball. |
Ctrl+W |
Tanca el full de treball. |
Ctrl+E+L |
Eliminar un full. |
Ctrl+Tab |
Canvia de llibres de treball. |
Navegant |
|
Maj+barra espaiadora |
Ressalta la fila. |
Ctrl+barra espaiadora |
Destaca la columna. |
Ctrl+– (guionet) |
Suprimeix les cel·les seleccionades. |
Tecles de fletxa |
Mou a noves cel·les. |
Ctrl+Pg amunt/Pg avall |
Canvia de fulls de treball. |
Ctrl+tecles de fletxa |
Aneu al final de l'interval continu i seleccioneu una cel·la. |
Maj+tecles de fletxa |
Seleccioneu l'interval. |
Maj+Ctrl+tecles de fletxa |
Seleccioneu el rang continu. |
a casa |
Mou al principi de la línia. |
Ctrl+Inici |
Mou a la cel·la A1. |
En Fórmules |
|
F2 |
Edita la fórmula, mostrant les cel·les precedents. |
Alt+Enter |
Inicieu una línia nova a la mateixa cel·la. |
Maj+tecles de fletxa |
Ressaltar dins de les cel·les. |
F4 |
Canvia la referència absoluta ("$"). |
Esc |
Cancel·la una entrada de cel·la. |
ALT+= (signe igual) |
Suma les cel·les seleccionades. |
F9 |
Torna a calcular tots els llibres de treball. |
Ctrl+[ |
Ressaltar les cèl·lules precedents. |
Ctrl+] |
Ressaltar les cèl·lules dependents. |
F5+Enter |
Torna a la cel·la original. |
Per trobar la drecera per a qualsevol funció, premeu la tecla Alt i les tecles de drecera mostraran la cinta. Per exemple, per anar al Gestor de noms, premeu Alt+M+N i apareixerà el quadre de diàleg Gestor de noms.
A la cantonada superior esquerra, trobareu la barra d'eines d'accés ràpid. Podeu canviar les dreceres que apareixen a la barra d'eines d'accés ràpid fent clic a la fletxa petita a la dreta de la barra d'eines i seleccionant el que voleu afegir al menú desplegable que apareix. Per exemple, si afegiu Enganxa especial a la barra d'eines d'accés ràpid, es pot accedir a Enganxa especial amb la drecera Alt+4. Tingueu en compte que això només funciona quan la barra d'eines d'accés ràpid s'ha personalitzat, i tot el que poseu a la quarta posició s'accedirà mitjançant la drecera Alt+4.