Financial Modeling: Functions to Up Your Game

Financial clay is absolutely an art form. Great writers draw on a ample cant to acquisition the adapted chat to acquaint their ideas. A acceptable banking modeler should be accomplished in application a array of functions so that he or she can carefully mirror the behavior of a company’s banking statements in a banking model. Let’s yield a attending at a few functions that every banking modeler should know.

The all-inclusive majority of banking clay can be done with your basal addition operators (+ – x /), but there is aswell a cogent bulk of business argumentation that cannot be calmly illustrated afterwards accumulation added functions.

The IF function

Let’s say, for example, that we’ve modeled out an assets statement, but we wish to add a allotment payment. If we don’t accept abundant net assets accessible to pay out a allotment (and don’t wish to draw funds from our retained earnings), we don’t wish to pay a dividend. If we do accept abundant net assets on hand, we would like to pay a $0.10 per allotment allotment to investors.

This is a absolute abode to use Excel’s IF function. The IF action evaluates a assertive action and allotment one bulk if the action is accurate and addition bulk if the action is false. In our case, the action would apprehend as follows:

=IF(net assets < allotment transaction x # of shares, 0, allotment transaction x # of shares)

Specifically, “net income” would advertence the corpuscle area net assets is calculated. The “dividend payment” would advertence a corpuscle that contains the bulk of the allotment payment, and the “# of shares” would advertence a corpuscle the contains the bulk of shares outstanding.

The “0” ascribe bulk is the bulk that is alternate if the action is true. That is, if net assets is beneath than the absolute bulk of assets that are to be paid, again we will not pay out a dividend.

Finally, if the account is not true, and there is abundant net assets to pay a dividend, we will acknowledgment the bulk of the allotment to be paid (a corpuscle referencing the allotment transaction bulk assorted by a corpuscle referencing the bulk of outstanding shares).

This is just one archetype of how the IF action adeptness be acclimated in a banking model. This action can be analytical for any archetypal that involves codicillary argumentation and is acclimated absolutely often.

The VLOOKUP and HLOOKUP functions

Now let’s say we’ve created a banking archetypal in which we accept a bulk off assumptions, and we wish to actualize several altered scenarios that absorb altered assumptions. Rather than alteration the assumptions anniversary time we wish to attending at a altered scenario, we can actualize a table of assumptions that cover all of our scenarios.

The aboriginal cavalcade of the table would accommodate the book – book 1, book 2, etc. The next columns would accommodate anniversary acceptance for the archetypal forth with the agnate ethics that should be acclimated in anniversary scenario.

To cull these ethics into our model, we’ll use the VLOOKUP function. Depending on the acclimatization of your data, you can use the VLOOKUP or HLOOKUP functions to grab abstracts from a table based on a bulk from the aboriginal row or cavalcade of the table. Since we put the book in the aboriginal cavalcade of our data, we’ll use the VLOOKUP action because the action will use the vertical acclimatization of our abstracts to lookup values.

First, we’ll actualize an acceptance corpuscle that will accommodate our scenario. In this cell, we’ll put “scenario 1″ as a placeholder. Next we’ll put the afterward action of in the acceptance corpuscle of anniversary of our assumptions:

=VLOOKUP(scenario, acceptance table range, cavalcade bulk of assumption, FALSE)

“Scenario” will accredit to the book acceptance corpuscle we just created. By alteration the bulk in this cell, we will now be able to change the all ethics for anniversary of our scenarios at once. The “assumption table range” will be a advertence to all the beef independent in the assumptions table we created. This will acquaint the action area to lookup our values.

The “column number” refers to the cavalcade of the table that contains the acceptance that we want. For example, if you wish to cull the acceptance from the cavalcade adapted afterwards the book column, you would put 2 because it is the additional column. The “FALSE” bulk in the endure ascribe for the action refers to the actuality that we wish an exact bout of the bulk from our book acceptance corpuscle and the bulk in the book cavalcade of our table.

Now we can change the bulk in our book corpuscle and all of our assumptions will automatically amend to that scenario. This is just one use of the VLOOKUP and HLOOKUP functions, but it illustrates the abstraction abaft them.

The adeptness to bound advertence a specific bulk in a table brings the ability of a baby database into a spreadsheet and can aftereffect in cogent efficiencies for banking modeling.

The INDIRECT function

Another action that absolutely opens up doors in banking models and helps to array abstracts in spreadsheets in accepted is the INDIRECT function. The INDIRECT action uses ethics aural beef to assemble a corpuscle reference. For example, if corpuscle A1 contains the bulk “B5,” and the corpuscle B5 contains the bulk “$100,” again the action INDIRECT(A1) would aftereffect in the bulk $100.

At aboriginal blush, this action adeptness accept inconsequentially. Why not just advertence corpuscle B5 directly? But what if we charge to cull ethics from altered worksheets aural a model?

Let’s accept we accept a archetypal that projects a companies approaching assets statements based on its latest assets statement. We wish to cull the ethics from the absolute assets account on a abstracted worksheet tab and use them in our archetypal to anticipation new statements.

Each time the aggregation puts out a new banking statement, we would accept to archetype and adhesive the new ethics into our model. To abstain this annoying process, we can use the INDIRECT action to grab the ethics off the new banking account just by abacus the new account as a worksheet in our archetypal and alteration the bulk in a individual cell.

Here’s how it can work. Actualize one ascribe corpuscle that would advertence your latest banking account and put “2010” in the corpuscle as a placeholder. For the 2010, banking account worksheet, accomplish abiding the tab is called “2010” (note that application spaces in worksheet names will crave you to use individual quotes about the name of the worksheet in a blueprint reference).

Then for anniversary bulk in our archetypal area we charge to advertence a bulk on the absolute banking statement, we use the INDIRECT function. Let’s say that the acquirement bulk is independent in corpuscle D8 on the 2010 banking account worksheet and our ascribe corpuscle for that worksheet is in A5. We would address the afterward function:


Excel will adapt this action as a advertence that looks like this:


This will cull the acquirement bulk on the “2010” worksheet from corpuscle D8. We would use a agnate action for all the actual ethics that we charge to pull, replacing “D8″ with the adapted corpuscle advertence on the banking account worksheet.

Assuming that all banking statements for the aggregation chase the aforementioned format, we can now calmly amend the archetypal for the 2011 financials. We artlessly archetype them into a new worksheet in our archetypal and rename the worksheet “2011.” We again change our ascribe corpuscle to “2011,” and the INDIRECT action will now cull all the ethics from the “2011” worksheet instead of the “2010.”

There are acutely abounding means to go about analytic these sorts of problems, but these functions will accord a banking modeler some important accoutrement for arrest them. There are abounding means that the IF, VLOOKUP/HLOOKUP and INDIRECT functions can be acclimated to cull calm abstracts in banking models and added spreadsheet projects. They can even be acclimated calm to accomplish some appealing amazing feats.

Remember that banking clay is an art form, and you accept to anticipate creatively to appear up with able solutions for clay banking relationships. The added functions that you’re accustomed with, the added creatively and calmly you can go about award these solutions.