For many entrepreneurs, thinkers, dreamers, and borderline maniac developers, the requirement of a financial model \u2013 most often built-in this ancient program called \u201cExcel\u201d \u2013 may seem like one of the biggest stumbling blocks on the yellow brick road to fame and fortune. Thankfully, it doesn\u2019t have to be that way!<\/p>\n
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\u2019s tool box. While many excellent modeling-for-beginners guides can be found on the internet, I\u2019ve 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:<\/p>\n
It\u2019s a fundamental rule of basic modeling but it must be said. Repeat after me: \u201cI will never put a hardcode and a formula in the same cell.\u201d All hardcodes or \u201cinputs\u201d 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.<\/p>\n
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\u2019s likely to be.<\/p>\n
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 \u2013 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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
In an effort to add dynamicity to your model, you may end up writing a lot of date \u201clogic\u201d 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\u2019s and 0\u2019s. 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\u2019s up until the store opening date, and then 1\u2019s thereafter. Once I have this \u201clogic,\u201d 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.<\/p>\n
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 \u201cmodel start date\u201d) and then write all other dates as EDATE or EOMONTH functions.<\/p>\n
It\u2019s impossible to learn Excel by reading an article or a book. Work in Excel and always challenge yourself with, \u201cThere must be an easier \/ more efficient way to do this.\u201d 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.<\/p>\n
Little nuggets of efficiency gold\u2026oh 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).<\/p>\n
Please, I know you think it\u2019s super advanced, but just don\u2019t use them (see point number 1 on landmines). There is something called Index Match which is far superior. Take the time to learn it.<\/p>\n
Avoid it if you can. If you have to do it, fine, but check the \u201cenable iterations\u201d box and build in a \u201ccircuit breaker\u201d or be prepared for a lot of #REF!<\/p>\n
Here\u2019s a real world scenario: I am an investor. I am busy. I\u2019ve spent two minutes looking at your model, and I don\u2019t get it. I am done talking to you. Create annual summaries, FORMAT, FORMAT, FORMAT, and maybe even throw in some nice graphs. PS \u2013 make sure it\u2019s printable, a la print preview (CTRL + F2).<\/p>\n
Don\u2019t hide rows, group them. Don\u2019t hide tabs, Mr. Investor will find them (move them to another workbook). Avoid \u201cplugging\u201d for numbers. Keep the \u201cRound\u201d 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 \u2013 as a new version!<\/p>\n
A wrong model is worse than no model at all.<\/p>\n
That\u2019s 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\u2019s the absolute confidence and familiarity with your model that will let you take the investors\u2019 questions head on and show that cool \u201cSteve Jobs-esque\u201d confidence in the face of difficult questions.<\/p>\n","protected":false},"excerpt":{"rendered":"
Financial Modeling Tips For many entrepreneurs, thinkers, dreamers, and borderline maniac developers, the requirement of a financial model \u2013 most often built-in this ancient program called \u201cExcel\u201d \u2013 may seem like one of the biggest stumbling blocks on the yellow brick road to fame and fortune. Thankfully, it doesn\u2019t have to be that way! A […]<\/p>\n","protected":false},"author":930,"featured_media":2743,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[43902],"tags":[],"class_list":["post-5525","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-professional-development"],"acf":[],"yoast_head":"\n