Financial Modeling Tips

For many entrepreneurs, thinkers, dreamers, and borderline maniac developers, the requirement of a financial model – most often built-in this ancient program called “Excel” – may seem like one of the biggest stumbling blocks on the yellow brick road to fame and fortune. Thankfully, it doesn’t have to be that way!

A financial model is simply a logical and orderly representation of a business, which, if built correctly, will become one of the most vital tools in the entrepreneur’s tool box. While many excellent modeling-for-beginners guides can be found on the internet, I’ve attempted to collect some lesser known and/or absolutely essential tips and tricks that should be helpful to any intermediate Excel user trying to hone their skill (articles for newbies and advanced users coming soon). Here are some sanity-saving tips for your perusal and entertainment:

Financial Forecasting Models

Hardcodes = landmines

It’s a fundamental rule of basic modeling but it must be said. Repeat after me: “I will never put a hardcode and a formula in the same cell.” All hardcodes or “inputs” should pull out of formulas and consolidated in an inputs / assumptions section. Never repeat an input and always change their color to blue so you can find them easily.

Bottoms up

Always take a bottoms up approach when structuring your model. By bottoms up, I mean breakdown everything you want to model into fundamental units, make those units inputs, and then build-up accordingly. For example, monthly shirt sales at a shirt-only retail store could be the result of the following inputs: foot traffic per day, days open per week, conversion rate per customer, average order quantity, and price per shirt. To a reasonable extent, the more granular your model, the more accurate and defensible it’s likely to be.

You Can Always Add More Rows

As simple as it sounds, one of the biggest fallacies of a novice / intermediate modeler is attempting to keep his or her model confined to the viewable screen, one page, or even a few pages. In the latest Excel you have 1,048,576 rows and 16,384 columns to work with – USE THEM!! Rather than multiply, subtract, and divide all in one cell or row, do it in three! The simpler your formulas are, the happier your brain will be. You can always create a monthly model. Also, you can create annual and quarterly summaries.

Tabs

Use them sparingly if possible. A financial model for a basic company should fit on one tab (see previous point). Hint: you can stack your P&L, BS, and SCF on top of each other. Summaries may warrant an additional tab, but can also be done in columns to the far right of your model.

One Column and Drag

If my model starts in January 2014, I should be able to write formulas row by row until January is complete and then, in one fell swoop, drag my formulas over (CTRL + R) to December 2019 without changing any formulas. Sticking to this rule may be tricky at first, but will make your life oh so much easier.

Think Binary

In an effort to add dynamicity to your model, you may end up writing a lot of date “logic” into formulas. Rather than write the date logic and the actual calculation in the same cell, always break out the date logic into a system of 1’s and 0’s. For instance, if I want my revenue to start when my store opening date input is greater-than-or-equal-to the column date, then I would first write a formula that displays 0’s up until the store opening date, and then 1’s thereafter. Once I have this “logic,” not only can I link my revenue calculation to it, but I can link other formulas as well. For instance, I would probably want my store employee payroll to start on the store opening date. The end result? SIMPLE FORMULAS.

Dates are Numbers

In Excel all dates are actually real numbers (e.g., January 1, 2013 = 41,275). Once you understand this tidbit of knowledge it opens a whole new world of dynamic modeling (the numerical value allows you to easily reference dates with logical functions). As a general rule every formula in your model should in some roundabout way link back to a date cell in the same column. The point being that if your development takes 8 months instead of 4 months, or your store opens 3 months later than expected, your model should adjust accordingly through an easy change of inputs. Last point on dates: Enter one date (the “model start date”) and then write all other dates as EDATE or EOMONTH functions.

Google It

It’s impossible to learn Excel by reading an article or a book. Work in Excel and always challenge yourself with, “There must be an easier / more efficient way to do this.” Google whatever you are doing, and I guarantee you (i) there is in fact an easier way and (ii) somebody else has already posed your exact same question to the Internet gods.

Life Changing Shortcuts

Little nuggets of efficiency gold…oh wait, you have a Mac? Get a PC and Google “shortcuts”. There’s too many to give here (once you compile a good list, unplug your mouse for best results).

V-Lookups? Cool Bro

Please, I know you think it’s super advanced, but just don’t use them (see point number 1 on landmines). There is something called Index Match which is far superior. Take the time to learn it.

Going Circular (References)

Avoid it if you can. If you have to do it, fine, but check the “enable iterations” box and build in a “circuit breaker” or be prepared for a lot of #REF!

Format and Summarize

Here’s a real world scenario: I am an investor. I am busy. I’ve spent two minutes looking at your model, and I don’t get it. I am done talking to you. Create annual summaries, FORMAT, FORMAT, FORMAT, and maybe even throw in some nice graphs. PS – make sure it’s printable, a la print preview (CTRL + F2).

Rapid Fire Tips

Don’t hide rows, group them. Don’t hide tabs, Mr. Investor will find them (move them to another workbook). Avoid “plugging” for numbers. Keep the “Round” function off-limits. Save all formatting until the end. Build in checks everywhere. Never sum across empty rows or columns. For the sake of your computer, and the wall you throw it against, save your work every 15 minutes – as a new version!

RULE #1: CHECK YOUR WORK

A wrong model is worse than no model at all.

That’s all for now. As a closing thought, financial modeling should always be a deliberate and thoughtful process. Take your time and always question yourself. In the end, it’s the absolute confidence and familiarity with your model that will let you take the investors’ questions head on and show that cool “Steve Jobs-esque” confidence in the face of difficult questions.