By , Excel was outselling Lotus and Quattro Pro. By , Excel v5. Most of the business world continues to use Excel for professional accounting and financial modeling based valuation to this day. While financial modeling Excel sheets are powerful and very customizable, it is also very much a manual labor of love, leading to unnecessary friction for end-users.
It comes down to comfortability. At this point, most of us grew up learning the basics of accounting in Excel. We were told that Excel is the best and only option for custom financial reporting and modeling.
Even though Excel takes manual wiring and countless hours of tracking and maintenance, many modelers won't look away simply because they are so comfortable in spreadsheet-based software like Excel. However, the tide is beginning to turn.
Business Intelligence started out by finance professionals tracking and reporting on operational data in Excel, but now hundreds of companies offer BI services and key performance tools that can automate and manage more complete data at a faster pace. So why hasn't that same proliferation of companies happened in the financial modeling space?
Because it's hard! Taking today's data and reporting on it in various ways is a straightforward process. Trying to create a variety of scenario-based projections into the murky future is not. But before we get into why that is hard, we need to understand what a financial model is, what kinds of financial models are out there, and how to build one.
In order to understand why Excel is no longer the best option for financial modeling, we must first discuss the various types of financial models that a CFO or financial analyst might employ or be called upon to produce for stakeholders.
The various types of financial models analysts use today map to common goals of financial models , which include the following:. As you can see, financial modeling can be used to accomplish very different goals and has a wide range of applications. That being said, there are at least 10 types of financial models commonly used in business:. And, to a degree, their implicit biases can and will affect the integrity of the model.
But no matter how complex your financial model or how diverse your choices and possible outcomes, building a financial model usually involves the same 5-step process:. Input reliable historical data: Many financial models look at the past 3 years of historical data, if not more. Identify your questions and assumptions: This is one of the trickiest parts of financial modeling. Are you asking the right questions and making the right assumptions? Have you considered every possible outcome of every possible choice?
What about the probabilities of each outcome? The accuracy of your historical data and your assumptions will greatly impact how accurate the results of your model may be. Present your findings to stakeholders: Every financial model should have very clear, easy-to-digest results. Do you finance that new warehouse and pay off the debt, or do you run the risk of draining your capital too quickly? Should you hire that new sales lead, or would it make more sense to invest in a new marketing CRM instead?
Whatever choice you make, hopefully, your financial model paints an accurate picture of the degree of risk and reward behind each choice. The countless applications of financial modeling across various industries is well outside the scope of this article. That being said, better financial modeling is absolutely critical for any business to continue growing with as little downside risk as possible. And it goes without saying that avoiding losses is usually more important than chasing profits.
This is especially relevant during the COVID pandemic, as businesses across sectors are faced with unknown risks of a scale and scope they may have never encountered before.
Without competent financial modeling, any business in operation right now is like a ship adrift at sea without navigational instruments. While the seasoned navigator may—by gut feel or instinct—steer the ship in the right direction, his luck is bound to run out in dark and stormy weather. Technology has exploded in a way few of us could have envisioned back when Lotus was still popular.
And while Excel is certainly more powerful than ever and adequate for basic financial modeling, it is also completely outdated for more sophisticated scenarios. When using Excel, analysts are forced to waste countless hours tweaking their rows and columns, methodically double-checking algorithms, carefully curating access across different spreadsheet versions for different collaborators, and meticulously crafting presentations from scratch—just for one set of assumptions and questions!
Taken further, Excel is only two-dimensional. Fortunately, this is the 21st century. Good forecasting skills increase the dependability of a model. Financial Modeling is full of minute details, numbers, and complex formulas. Different groups use it like operational managers, management, clients. These people will not decipher any meaning from the Model if the Model is looking messy and hard to understand.
Hence, keeping the Model simple in presentation and at the same time rich in detail is of great importance. Financial Modeling is easy, as well as complicated. If you look at the Model, you will find it involved; however, it has smaller and simple modules. The key here is to prepare each smaller modules and interconnect each other to train the final financial model.
You can refer to this step by step guide on Financial Modeling in Excel for detailed learning. Full-Scale Modeling is a lengthy and complicated process and hence disastrous to go wrong. It is advisable to follow a planned path while working on a financial model to maintain accuracy and avoid getting confused and lost.
Following are the logical steps to follow:. This has been a guide to what is Financial Modeling. You can learn more about Financial Modeling from the following —. Any ways, I just want to let you know how grateful I am to you for sharing all your knowledge. I refer back to your ration analysis. Your email address will not be published. Save my name, email, and website in this browser for the next time I comment. Free Investment Banking Course. Login details for this Free course will be emailed to you.
Forgot Password? What is Financial Modeling? Understanding Financial Modeling Financial Modeling is either building a model from scratch or maintaining the existing Model by implementing newly available data to it. Well built models will further distinguish between formulas that link to other worksheets and workbooks as well as cells that link to data services.
While different investment banks have different house styles, blue is typically used to color inputs and black is used for formulas. The table below shows our recommended color coding scheme. While everyone agrees that color coding is very important, keeping up with it can be a pain in native Excel.
Alternatively, color coding is dramatically simplified with a third party Excel add-in like Macabacus which is bundled with Wall Street Prep self-study products and boot camp enrollments , Capital IQ or Factset. Inserting comments Shortcut Shift F2, see our Essential Excel Shortcuts List in cells is critical for footnoting sources and adding clarity to data in a model. For example, a cell containing an assumption on revenue growth that came from an equity research report should include a comment with a reference to the research report.
So how much commenting do you need? Always err on the side of over commenting. No managing director will ever complain that a model has too many comments. The decision on whether to use positive or negative sign conventions must be made before the model is built. Models in practice are all over the place on this one. The modeler should choose from and clearly identify one of the following 3 approaches:. Convention 1: All income positive, all expenses negative.
Convention 2: All expenses positive; non-operating income negative. Convention 3: All expenses positive except non-operating expenses. Our recommendation is Convention 1. The reduced likelihood of error from easier subtotaling alone makes this our clear choice. In addition, one of the most common mistakes in modeling is forgetting to switch the sign from positive to negative or vice versa when linking data across financial statements.
Convention 1, by virtue of being the most visibly transparent approach, makes it easier to track down sign-related mistakes. Hard coded numbers constants should never be embedded into a cell reference. Inputs must be clearly separated from calculations see below. Most investment banking models, like the 3-statement model , rely on historical data to drive forecasts. Data should be presented from left to right.
The right of the historical columns are the forecast columns. The formulas in the forecast columns should be consistent across the row. Roll-forwards refers to a forecasting approach that connects the current period forecast to the prior period. This approach is very useful in adding transparency to how schedules are constructed. There is a temptation when working in Excel to create complicated formulas. While it may feel good to craft a super complex formula, the obvious disadvantage is that no one including the author after being away from the model for a bit will understand it.
Because transparency should drive structure, complicated formulas should be avoided at all cost. A complicated formula can often be broken down into multiple cells and simplified. So take advantage of that. Below are some common traps to avoid:. IF statements, while intuitive and well understood by most Excel users, can become long and difficult to audit.
There are several excellent alternatives to IF that top-notch modelers frequently use. Below is a real-world example of how an IF statement can be simplified. Cell F uses any surplus cash generated during the year to pay down the revolver, up until the revolver is fully paid down.
However, if deficits are generated during the year, we want the revolver to grow. The revolver formula using MIN as an alternative to IF also holds up better when additional complexity is required. Look at how we have to modify both formulas to accommodate this:. While both formulas are challenging to audit, the formula using IF statements is more difficult to audit and is more vulnerable to getting completely out of hand with additional modifications. Fortunately, Excel has made this a bit easier in with the introduction of the IFS function , but our preference for relying on more elegant functions remains.
Each phase of the restructuring process has its own distinct borrowing and operating characteristics. This enables us to build very simple, consistent formulas for each revolver without having to embed IF statements into each calculation. The same applies to the formulas in rows 20 and — the flags have prevented a lot of extra code. Another way many modelers reduce formula complexity is by using names and named ranges.
We strongly caution against using names and named ranges. In the case of names, the tradeoff is that when you name a cell, you no longer know exactly where it is without going to the name manager. In investment banking, your financial models will frequently involve financial statements. Instead, balance sheet forecasts should be determined in separate schedules and linked into the balance sheet as illustrated below. This consistency helps in the transparency and auditing of a model. The same goes for years and dates entered into a column header or a discount rate assumption used in a variety of different places in the model.
A more subtle example of this is hard coding subtotals or EPS when you can calculate it. In other words, calculate whenever possible. For this, go ahead and daisy chain. The reason is that straight-lining base period assumptions is an implicit assumption, which can change, thus making it possible for certain years in the forecast to ultimately end of with different assumptions than other years. Compare the two images below. Whenever possible, bring the data from other worksheets into the active worksheet where the calculation is made.
C7 and a separate cell for the calculation. While this creates a redundant cell reference, it preserves the visual audit-ability of the model tab and reduces the likelihood of error. Excel allows you to link to other Excel files, but others might not have access to the linked-to files, or these files may get inadvertently moved. Therefore, avoid linking to other files whenever possible. If linking to other files is a must, be vigilant about color coding all cell references to other files.
A long worksheet means a lot of scrolling and less visual compartmentalizing of sections. On the other hand, multiple worksheets significantly increases the likelihood of linking errors.
A model often has rows with data and calculations that you do not want to show when the model is printed or when you paste the data into a presentation. The danger is that when the model is passed around, it is very easy to miss and potentially paste over the hidden data. In other words, think of a model as comprised of three clearly identified and physically separated components:.
One reason is simply poor practice. Imagine building a house without any pre-planning. This problem is rampant in investment banking models. Another reason is that many investment banking models are simply not granular enough to merit the additional audit trail and legwork. The analyses bankers perform are often broader than they are deep. For example, a pitch book might present a valuation using 4 different valuation models, but none of them will be overly granular.
In this case, moving back and forth from input to calculation to output tabs is unnecessarily cumbersome.
0コメント