The Ultimate Guide to Financial Modeling Best Practices - Wall Street Prep
publicité
The Ultimate Guide to Financial Modeling et Practice Introduction Like man computer programmer, people who uild nancial model can get quite opinionated aout the “right wa” to do it. In fact, there i urpriingl little conitenc acro Wall treet around the tructure of nancial model. One reaon i that model can var widel in purpoe. For example, if our tak wa to uild a dicounted cah ow (DCF) model to e ued in a preliminar pitch ook a a valuation for one of 5 potential acquiition target, it would likel e a wate of time to uild a highl complex and feature-rich model. The time required to uild a uper complex DCF model in’t juti ed given the model’ purpoe. On the other hand, a leveraged nance model ued to make thouand of loan approval deciion for a variet of loan tpe under a variet of cenario neceitate a great deal of complexit. Undertanding the purpoe of the model i ke to determining it optimal tructure. There are two primar determinant of a model’ ideal tructure: granularit and exiilit. Let’ conider the following 5 common nancial model: Model One page DCF Purpoe Granularit Flexiilit Ued in a u ide pitch ook to provide a Low. all-park valuation range Low. Not reuale without tructural modi cation. Will e ued in valuation range for one of everal potential i u cient) / mall. ntire a peci c pitch and circulated etween jut 1-3 deal team memer. acquiition target. anali can t on one workheet < 300 row) Full Ued to value target compan in a fairne integrated opinion preented to the acquiring compan Medium for ue in the fairne opinion and circulated etween deal time DCF oard of director memer. Comp model Ued a the tandard model the entire template indutrial team at a ulge racket ank Medium Low. Not reuale without tructural modi cation. Will e tailored High. Reuale without tructural modi cation. A template to e ued for a variet of pitche and deal man analt and aociate, poil other takeholder. Will e ued people with varing level of xcel kill. Retructuring uilt peci call for a multinational model corporation to tre tet the impact of High Medium. ome re-uailit ut not quite a template. Will e ued oth the deal team and counterpart at the client rm. elling 1 or more uinee a part of a retructuring advior engagement Leveraged nance model Ued in the loan approval proce to analze loan performance under variou operating High High. Reuale without tructural modi cation. A template to e ued group wide. cenario and credit event Financial model granularit A critical determinant of the model’ tructure i granularit. Granularit refer to how detailed a model need to e. For example, imagine ou are taked with performing an LO anali for Dine. If the purpoe i to provide a ack-of-the-envelope oor valuation range to e ued in a preliminar pitch ook, it might e perfectl appropriate to perform a “high level” LO anali, uing conolidated data and making ver imple aumption for nancing. If, however, our model i a ke deciion making tool for nancing requirement in a potential recapitalization of Dine, a far higher degree of accurac i incredil important. The di erence in thee two example might involve thing like: Wall treet Prep | www.walltreetprep.com Forecating revenue and cot of good egment egment and uing price-per-unit and #-unit-old driver intead of aggregate forecat Forecating nancial acro di erent uine unit a oppoed to looking onl at conolidated nancial Analzing aet and liailitie in more detail (i.e. leae, penion, PP&, etc.) reaking out nancing into variou tranche with more realitic pricing Looking at quarterl or monthl reult intead of annual reult Practicall peaking, the more granular a model, the longer and more di cult it will e to undertand. In addition, the likelihood of error grow exponentiall virtue of having more data. Therefore, thinking aout the model’ tructure — from the laout of the workheet to the laout of individual ection, formula, row and column — i critical for granular model. In addition, integrating formal error and “integrit” check can mitigate error. Financial model exiilit The other main determinant for how to tructure a model i it required exiilit. A model’ exiilit tem from how often it will e ued, how man uer, and for how man di erent ue. A model deigned for a peci c tranaction or for a particular compan require far le exiilit than one deigned for heav reue (often called a template). A ou can imagine, a template mut e far more exile than a compan peci c or “tranaction peci c model. For example, a that ou are taked with uilding a merger model. If the purpoe of the model i to analze the potential acquiition of Dine Apple, ou would uild in far le functionalit than if it purpoe wa to uild a merger model that can handle an two companie. peci call, a merger model template might require the following item that are not required in the deal-peci c model: 1. Adjutment to acquirer currenc 2. Dnamic calendarization (to et target’ nancial to acquirer’ cal ear) 3. Placeholder for a variet of income tatement, alance heet and cah ow tatement line item that don’t appear on Dine or Apple nancial 4. Net operating lo anali (neither Dine or Apple have NOL) Together, granularit and exiilit largel determine the tructural requirement of a model. tructural requirement for model with low granularit and a limited uer ae are quite low. Rememer, there i a trade-o to uilding a highl tructured model: time. If ou don’t need to uild in ell and whitle, don’t. A ou add granularit and exiilit, tructure and error proo ng ecome critical. The tale elow how the granularit/ exiilit level of common invetment anking model. High exiilit High granularit Leveraged nance credit model Low exiilit Integrated LO model Wall treet Prep | www.walltreetprep.com Merger model template “one ize t all” Integrated DCF model Integrated Merger Model Integrated operating model Low granularit Trading comp template “ack of the envelope” accretion/dilution model Tranaction comp template DCF “one pager” LO “one pager” imple operating model Financial model preent-ailit Regardle of granularit and exiilit, a nancial model i a tool deigned to aid deciion making. Therefore, all model mut have clearl preented output and concluion. ince virtuall all nancial model will aid in deciion-making within a variet of aumption and forecat, an e ective model will allow uer to eail modif and enitize a variet of cenario and preent information in a variet of wa. Now that we have etalihed a imple framework for tructuring model, it’ time to dicu peci c feature of model architecture, error proo ng, exiilit and preentation. Financial model tructure elow, we la out the ke element of an e ectivel tructured model, mot of which will go a long to wa to improve the model’ tranparenc. A a model ecome more complex (due to higher granularit and exiilit), it naturall ecome le tranparent. The et practice elow will help to x thi. Formatting Color coding Jut aout everone agree that color coding cell aed on whether it hold a hard coded numer or a formula i critical. Without color coding, it i extremel di cult to viuall ditinguih etween cell that hould e modi ed and cell that hould not ( i.e. formula). Well uilt model will further ditinguih etween formula that link to other workheet and workook a well a cell that link to data ervice. While di erent invetment ank have di erent houe tle, lue i tpicall ued to color input and lack i ued for formula. The tale elow how our recommended color coding cheme. Tpe of cell xcel formula Color Hard-coded numer (input) =1234 lue Formula (calculation) =A1*A2 lack Link to other workheet =heet2!A1 Green Link to other le =[ook2]heet1!$A$1 Red Link to data provider (i.e. CIQ, Factet) =CIQ(IQ_TOTAL_RV) Dark Red While everone agree that color coding i ver important, keeping up with it can e a pain in native xcel. It’ not ea to format cell aed on whether the are input or formula, ut it can e done. One option i to ue xcel’ “Go To pecial” (covered in our xcel Crah Coure, which ou can enroll in here). Alternativel, color coding i dramaticall impli ed with a third part xcel add-in like Macaacu (which i undled with Wall treet Prep elf-tud product and oot camp enrollment), Capital IQ or Factet. Thee tool allow ou to “autocolor” an entire workheet in one click. Wall treet Prep | www.walltreetprep.com Comment Inerting comment (hortcut hift F2, ee our ential xcel hortcut Lit) in cell i critical for footnoting ource and adding clarit to data in a model. For example, a cell containing an aumption on revenue growth that came from an equit reearch report hould include a comment with a reference to the reearch report. o how much commenting do ou need? Alwa err on the ide of over commenting. No managing director will ever complain that a model ha too man comment. Additionall, if ou’re on a conference call and omeone ak how ou came up with the numer in cell AC1238 and ou lank, ou’ll regret not commenting. ign convention The deciion on whether to ue poitive or negative ign convention mut e made efore the model i uilt. Model in practice are all over the place on thi one. The modeler hould chooe from and clearl identif one of the following 3 approache: Convention 1: All income poitive, all expene negative. Advantage: logical, conitent, make utotal calculation le error-prone Diadvantage: Doen’t align with convention ued pulic ling, % margin calculation appear negative Convention 2: All expene poitive; non-operating income negative. Advantage: Conitent with pulic ling, % margin calculation appear poitive Diadvantage: Negative non-operating income i confuing, utotal calculation are error-prone, proper laeling i critical Convention 3: All expene poitive except non-operating expene. Advantage: Avoid negative non-operating income preentation; margin evaluate to poitive Diadvantage: Preentation not internall conitent. Proper laeling i critical. Our recommendation i Convention 1. The reduced likelihood of error from eaier utotaling alone make thi our clear choice. In addition, one of the mot common mitake in modeling i forgetting to witch the ign from poitive to negative or vice vera when linking data acro nancial tatement. Convention 1, virtue of eing the mot viil tranparent approach, make it eaier to track down ign-related mitake. Formula Wall treet Prep | www.walltreetprep.com Avoid partial input (all model) Hard coded numer (contant) hould never e emedded into a cell reference. The danger here i that ou’ll likel forget there i an aumption inide a formula. Input mut e clearl eparated from calculation (ee elow). One row, one calculation Mot invetment anking model, like the 3-tatement model, rel on hitorical data to drive forecat. Data hould e preented from left to right. The right of the hitorical column are the forecat column. The formula in the forecat column hould e conitent acro the row. Ue roll-forward (“A” or “cork-crew”) calculation Roll-forward refer to a forecating approach that connect the current period forecat to the prior period. Thi approach i ver ueful in adding tranparenc to how chedule are contructed. Maintaining trict adherence to the roll-forward approach improve a uer’ ailit to audit the model and reduce the likelihood of linking error. Write good (and imple) formula There i a temptation when working in xcel to create complicated formula. While it ma feel good to craft a uper complex formula, the oviou diadvantage i that no one (including the author after eing awa from the model for a it) will undertand it. ecaue tranparenc hould drive tructure, complicated formula hould e avoided at all cot. A complicated formula can often e roken down into multiple cell and impli ed. Rememer, Microoft doen’t charge ou extra for uing more cell! o take advantage of that. elow are ome common trap to avoid: 1. implif IF tatement and avoid neted IF 2. Conider uing ag Wall treet Prep | www.walltreetprep.com implif IF tatement IF tatement, while intuitive and well undertood mot xcel uer, can ecome long and di cult to audit. There are everal excellent alternative to IF that top-notch modeler frequentl ue. The include uing oolean logic along with a variet of reference function, including MAX, MIN, AND, OR, VLOOKUP, HLOOKUP, OFFT. elow i a real-world example of how an IF tatement can e impli ed. Cell F298 ue an urplu cah generated during the ear to pa down the revolver, up until the revolver i full paid down. However, if de cit are generated during the ear, we want the revolver to grow. While an IF tatement accomplihe thi, a MIN function doe it more elegantl: Revolver formula uing IF tatement Revolver formula uing MIN The revolver formula uing MIN a an alternative to IF alo hold up etter when additional complexit i required. Imagine that there’ a limit on annual revolver draw of $50,000. Look at how we have to modif oth formula to accommodate thi: Wall treet Prep | www.walltreetprep.com Revolver formula using IF statement Revolver formula uing MIN While oth formula are challenging to audit, the formula uing IF tatement i more di cult to audit and i more vulnerale to getting completel out of hand with additional modi cation. It ue neted (or emedded) IF tatement, which our feele human rain have a hard time with once there’ more than one or two. Fortunatel, xcel ha made thi a it eaier in 2016 with the introduction of the IF function, ut our preference for reling on more elegant function remain. We pend a lot of time in our xcel Crah Coure going over the man wa “IF alternative” function can e ued to power-charge xcel. Reduce date-related formula complexit uing ag Flag refer to a modeling technique mot ueful for modeling tranition acro phae of a compan, project or tranaction over time without violating the “one row/one calculation” conitenc rule. Imagine ou’re uilding a model for a compan that’ contemplating ankruptc. ach phae of the retructuring proce ha it own ditinct orrowing and operating characteritic. In our example elow, the compan’ revolver “freeze” once it goe into ankruptc and a new tpe of orrowing (“DIP”) act a the new revolver until the compan emerge from ankruptc. Additionall, a new “xit” facilit replace the DIP. We inert 3 “ ag” in row 8-10 to output “TRU/FAL” aed on the phae we’re in. Thi enale u to uild ver imple, conitent formula for each revolver without having to emed IF tatement into each calculation. In cell F16 the formula i =F13*F8. Whenever ou appl an operator (like multiplication) on a TRU, the TRU i treated like a “1” while a FAL i treated like a “0.” Thi mean that the pre-ankruptc revolver i the de facto revolver when the pre-ankruptc ag evaluate to TRU and ecome 0 once the ag evaluate to FAL (tarting in column I in our example elow). The main ene t i that with the ue of jut an extra 3 row, we’ve avoided having to inert an ort of conditional tet within the calculation. The ame applie to the formula in row 20 and 204 — the ag have prevented a lot of extra code. Wall treet Prep | www.walltreetprep.com Name and named range Another wa man modeler reduce formula complexit i uing name and named range. We trongl caution againt uing name and named range. A ou’re proal eginning to ene, there i alwa ome kind of tradeo with xcel. In the cae of name, the tradeo i that when ou name a cell, ou no longer know exactl where it i without going to the name manager. In addition, unle ou are proactivel deleting name (ou aren’t), xcel will retain thee name even when ou delete the named cell. The reult i that a le ou’re uing toda to uild a DCF contain dozen of phantom name from prior verion of the model, leading to warning meage and confuion. Don’t calculate on the alance heet — link from upporting chedule. In invetment anking, our nancial model will frequentl involve nancial tatement. Ideall, our calculation are done in chedule eparate from the output ou’re working toward. For example, it’ preferale that ou don’t perform an calculation on the model’ alance heet. Intead, alance heet forecat hould e determined in eparate chedule and linked into the alance heet a illutrated elow. Thi conitenc help in the tranparenc and auditing of a model. Wall treet Prep | www.walltreetprep.com How to reference cell Never re-enter the ame input in di erent place For example, if ou’ve inputted a compan name in the rt workheet of the model, reference that workheet name — don’t re-tpe it into the other workheet. The ame goe for ear and date entered into a column header or a dicount rate aumption ued in a variet of di erent place in the model. A more utle example of thi i hard coding utotal or P when ou can calculate it. In other word, calculate whenever poile. Alwa link directl to a ource cell a it i more di cult to audit “dai chained” data The one major exception to thi i when “traight-lining” ae period aumption. For thi, go ahead and dai chain. The reaon i that traightlining ae period aumption i an implicit aumption, which can change, thu making it poile for certain ear in the forecat to ultimatel end of with di erent aumption than other ear. Avoid formula that contain reference to multiple workheet Compare the two image elow. It i more di cult to audit the formula in the rt image ecaue ou’ll need to ounce around to di erent workheet to view the precedent cell. Whenever poile, ring the data from other workheet into the active workheet where the calculation i made. Link aumption into tandalone cell in the calculation and output heet If ou’re working with larger model and ou have aumption that need to e referenced from a eparate workheet, conider linking aumption directl into the workheet where ou’re uing them, and color code them a a ditinct workheet reference link. In other word, don’t have an input reference emedded into a calculation (i.e. =D13*input!C7). Intead, ue a clean reference =input!C7 and a eparate cell for the calculation. While thi create a redundant cell reference, it preerve the viual audit-ailit of the model ta and reduce the likelihood of error. Wall treet Prep | www.walltreetprep.com Avoid linking le xcel allow ou to link to other xcel le, ut other might not have acce to the linked-to le, or thee le ma get inadvertentl moved. Therefore, avoid linking to other le whenever poile. If linking to other le i a mut, e vigilant aout color coding all cell reference to other le. Workheet One long heet eat man hort heet A long workheet mean a lot of crolling and le viual compartmentalizing of ection. On the other hand, multiple workheet igni cantl increae the likelihood of linking error. There’ no hard and fat rule aout thi, ut the general ia hould e toward a longer heet over multiple, horter workheet. The danger of mi-linking acro workheet i quite real and hard to mitigate, while the iue of cumerome crolling and lack of compartmentalization aociated with long workheet can e draticall mitigated with xcel’ plit creen functionalit, clear header and link from a cover heet or tale of content. Don’t ‘hide’ row — ‘group’ them (and do it paringl) A model often ha row with data and calculation that ou do not want to how when the model i printed or when ou pate the data into a preentation. In thi ituation, it’ often tempting to hide row and column for a “cleaner” preentation of reult. The danger i that when the model i paed around, it i ver ea to mi (and potentiall pate over) the hidden data. Keeping input (aumption) together (for high-granularit model) Nearl ever nancial modeling expert recommend a tandard that iolate all of the model’ hard-coded aumption (thing like revenue growth, WACC, operating margin, interet rate, etc…) in one clearl de ned ection of a model — tpicall on a dedicated ta called ‘input.’ Thee hould never e commingled with the model’ calculation (i.e. alance heet chedule, the nancial tatement) or output (i.e. credit and nancial ratio, chart and ummar tale). In other word, think of a model a compried of three clearl identi ed and phicall eparated component: Aumption → Calculation → Output Advantage: Conitent, reliale architecture: Once a model i uilt, the uer ha onl one place the need to go to change an aumption. Thi create a conitent ditinction etween area in the model that the uer work in v. area the computer work in. rror mitigation: toring all aumption in one place make it far le likel that ou’ll forget to remove old aumption from a prior anali and inadvertentl ring them into a new anali. Yet depite thee advantage, thi practice ha never een widel adopted in invetment anking. One reaon i impl poor practice. ome model would clearl ene t from an input/calculation/output eparation, ut are often uilt with no forethought given to tructure. Imagine uilding a houe without an pre-planning. ure, ou’ll avoid the pain of all that planning, ut ou’ll encounter unforeeen prolem and end up redoing work or adding complexit working around what’ alread een done. Thi prolem i rampant in invetment anking model. Another reaon i that man invetment anking model are impl not granular enough to merit the additional audit trail and legwork. The anale anker perform are often roader than the are deep. For example, a pitch ook might preent a valuation uingWall 4 ditreet erent valuation model, ut Prep | www.walltreetprep.com none of them will e overl granular. Common invetment anking anale like accretion dilution model, LO model, operating model and DCF model uuall don’t delve into detail eond the limit of pulic ling and aic forecating. In thi cae, moving ack and forth from input to calculation to output ta i unnecearil cumerome. A long a ou’re diligent aout color coding, placing aumption on the ame heet and right elow calculation i preferale in maller model ecaue our aumption are viuall right next to the output, making it ea to ee what’ driving what. The other conideration i the numer of a model’ uer. The advantage of the “input together” approach grow with the numer of a model’ intended uer. When ou have man uer, our model will inevital e ued people with a wide range of modeling pro cienc. In thi cae, a conitent and reliale tructure that prevent uer from getting into the gut of the model will reduce error. In addition, it will alo reduce the amount of time a uer ha to pend in the model — a uer can impl locate the area for input, ll them in, and the model (in theor) will work. That aid, depite attempt I team to tandardize model, man invetment anking model are eentiall “one-o ” that get materiall modi ed for each new ue. Aide from comp model which lend themelve to ecoming template, mot model are ued primaril their original author (uuall an analt and aociate) who undertand the model well. The ottom line on keeping input all together Unfortunatel, there’ no etalihed enchmark for when it make ene to eparate out aumption. The ideal approach depend on the cope and goal of the model. For a imple 1-page dicounted cah ow anali not intended for frequent reue, it i preferale to emed input throughout the page. However, for a large full-integrated LO model with man det tranche to e ued a group-wide template, the ene t of keeping all input together will outweigh the cot. No pacer column etween data levator jump In long workheet, dedicating the leftmot column for placing an “x” or another character at the tart of chedule will make it ea to quickl navigate from ection to ection. Wall treet Prep | www.walltreetprep.com Annual v quarterl data (periodicit) Mot invetment anking model are either quarterl or annual. For example, a U.. equit reearch earning model will alwa e a quarterl model ecaue one of it ke purpoe i to forecat upcoming earning, which are reported rm quarterl. imilarl, a retructuring model i uuall a quarterl model (or even a monthl or weekl model) ecaue a ke purpoe of thi model i to undertand the cah ow impact of operational and nancing change over the next 1-2 ear. On the other hand, a DCF valuation i a long term anali, with at leat 4-5 ear of explicit forecat required. In thi cae, an annual model i appropriate. There are alo model for which oth quarterl and annual period are ueful. For example, a merger model uuall need a quarterl period ecaue a ke goal i to undertand the impact of the acquiition on the acquirer’ nancial tatement over the next 2 ear. However, attaching a DCF valuation to the comined merged companie ma alo e deired. In thi cae, a poile olution i to roll up the quarter into an annual model and extend thoe annual forecat further out. When determining a model’ periodicit, keep in mind the following: 1. The model mut e et up with the mallet unit of time deired, with longer time period eing aggregated (rolled up) from thoe horter time period. If ou’re uilding an integrated nancial tatement model in which ou want to ee quarterl and annual data, forecat the quarterl data rt. 2. Keep the quarterl and annual data in eparate workheet. It i eaier to audit what’ going on when period aren’t commingled. Additionall, commingling quarterl and annual data in one workheet will either A) force ou to violate the one row/one formula conitenc et practice or ) ou will have to jump through ome craz hoop to maintain the conitenc. Circularit Circularit refer to a cell referring to itelf (directl or indirectl). Uuall, thi i an unintentional mitake. In the imple example elow, the uer ha accidentall included the um total (D5) in the um formula. Notice how xcel ecome confued: ut ometime a circularit i intentional. For example, if a model calculate a compan’ interet expene aed on a cell that calculate the compan’ revolving det alance, ut that revolving det alance i itelf determined (among other thing) the compan’ expene (including interet expene), then we have a circularit: The logic of uch a calculation i ound: A compan’ orrowing need hould take into account the interet expene. A uch, man invetment anking model contain intentional circularitie like thee. Wall treet Prep | www.walltreetprep.com ince unintentional circularit i a mitake to avoid, the uage of intentional circularit in nancial model i controverial. The prolem with intentional circularit i that a pecial etting mut e elected within ‘xcel Option’ to prevent xcel from miehaving when a circularit exit: ven with thee etting elected, xcel can ecome untale when handling circularit and often lead to a model “lowing up” (i.e. the model hortcircuit and populate the preadheet with error), requiring manual intervention to zero out the cell containing the ource of circularit: While the underling logic for wanting to incorporate a circularit into a model ma e valid, circularit prolem can lead to minute, if not hour, of wated auditing time tring to locate the ource() of circularit to zero them out. There are everal thing modeler can do to etter cope with circularit, mot notal the creation of a imple circuit reaker, which create a central place in the model that “reet” an cell containing a circularit or wrapping an error-trap formula (IFRROR) around the formula that i the ource of the circularit. Circuit reaker or an IFRROR error-trap Wall treet Prep | www.walltreetprep.com When uilding an intentional circularit, ou MUT uild a circuit reaker and clearl identif all the circularitie in our model. In our imple example, we placed a circuit reaker in D17 and altered the formula in D8 o the circularit i zeroed out when the uer witche the reaker to “ON”: Approach 1: Adding a circuit reaker toggle An alternative approach i to impl wrap an IFRROR function around the ource of the circuilarit. When the model hort circuit, the IFRROR function evaluate to the FAL condition and populate the model with 0 automaticall. The primar downide to thi approach i that the make nding unintentional circularitie harder. That’ ecaue ou can never explicitl turn the reaker on or o – the IFRROR doe it automaticall. That aid, a long a all circ are handled with an IFRROR function, the model will never low up. Approach 2: Adding an error trap uing the IFRROR function ottom line: To circ or not to circ? Depite the circuit reaker and error trap olution, man elieve it i preferale to impl outlaw all circularit from nancial model. For example, the wa to altogether avoid the intentional circularit in the example aove i to calculate interet expene uing eginning det alance. For quarterl and monthl model with minor det uctuation, thi i deirale, ut for an annual model with a large forecated change in det, the “ x” can lead to a materiall di erent reult. Therefore, we do not elieve in a lanket “an.” Intead, we provide the following imple guideline: Wall treet Prep | www.walltreetprep.com A circularit i onl OK if all the following condition are met. 1. It i intentional: At rik of tating the oviou, ou mut undertand exactl wh, where, and how the circularit exit. The example decried aove i the mot common ource of circularit in nancial model. 2. You have “enale iterative calculation” elected in our xcel etting: Thi tell xcel the circularit i intentional and enure xcel doen’t throw up an error and populate the entire model with random zero everwhere. 3. You have a circuit reaker or error trap formula: A circuit reaker or error trap formula enure that if the le get untale and #DIV/0! tart populating the model, there i an ea and clear wa to x it. 4. The model will not e hared with xcel novice: Circularitie, even with a circuit reaker, can create confuion for xcel uer not familiar with it. If the model ou are uilding will e hared with client (or a managing director) that like to get into the model ut are generall unfamiliar with xcel, avoid the circularit and ave ourelf the headache. Don’t ue macro Keep macro to an aolute minimum. Ver few people know how macro work, and ome uer cannot open le that ue macro. ver additional macro i a tep cloer to making our model a “lack ox.” In invetment anking, thi i never a good thing. The onl macro regularl tolerated in anking model are print macro. rror checking xcel i an amazing tool. Unlike oftware peci call deigned to perform a particular et of tak (i.e. real etate invetment oftware, ookkeeping oftware), xcel i a lank canva, which make it ea to perform extremel complicated anale and quickl develop invaluale tool to help in nancial deciion making. The downide here i that xcel anale are onl a good a the model uilder (i.e. “Garage in = garage”). Model error i aolutel rampant and ha eriou conequence. Let’ reak up the mot common modeling error: 1. ad aumption: If our aumption are fault, the model’ output will e incorrect regardle of how well it i tructured. 2. ad tructure: ven if our model’ aumption are great, mitake in calculation and tructure will lead to incorrect concluion. The ke to mitigating #1 i to preent reult with clearl de ned range of aumption (cenario and enitivitie) and make the aumption clearl de ned and tranparent. reaking model out into input→calculation→output help other quickl identif and challenge our aumption (Addreed in detail in the “Preentation” ection aove). The far more perniciou modeling error i #2 ecaue it’ much more di cult to nd. A ou might imagine, the prolem grow exponentiall a the model’ granularit increae. Thi i wh uilding error check into our model i a critical part of model uilding. uild in error check The mot common error check in a nancial model i the alance check — a formula teting that aet = liailitie + equit: Wall treet Prep | www.walltreetprep.com Anone who ha uilt an integrated nancial tatement model know it i quite ea to make a imple mitake that prevent the model from alancing. The alance check clearl identi e to the uer that a mitake ha een made and further invetigation i required. However, there are man other area of model that are prone to error and thu could merit error check. While ever model will need it own check, ome of the more common one include: nuring ource of fund = ue of fund nuring the quarterl reult add up to annual reult Total forecat depreciation expene doe not exceed PP& Det pa-down doe not exceed outtanding principal Favor direct calculation over “plug” elow we how two common wa that uer et up a ource & ue of fund tale in nancial model. In oth approache, the uer accidentall reference intangile aet. In approach 1, the incorrect data i linked into D37. The model notice that ource do not equal ue and throw an error meage in D41. The econd (and equall common) approach tructurall et D52 equal to D47 and ue D49 a a plug to enure ource and ue alwa equal. Which approach do ou think i preferale? If ou gueed the rt approach, ou are correct. The prolem the econd (“plug”) approach i that ecaue of the mi-linking in D50, the model incorrectl calculate the amount of ecured loan required for the tranaction, and no error i identi ed. Wall treet Prep | www.walltreetprep.com Whenever a direct calculation i poile, ue it, along with an error check (i.e. “do ource equal ue?”) intead of uilding plug. Aggregate error check into one area Place error check cloe to where the relevant calculation i taking place, ut aggregate all error check into a central ea to ee “error dahoard” that clearl how an error in the model. rror trapping Model that require a lot of exiilit (template) often contain area that a uer ma not need now, ut will need down the road. Thi include extra line item, extra functionalit, etc. Thi create room for error ecaue xcel i dealing with lank value. Formula like IFRROR (and IRROR), INUMR, ITXT, ILANK are all ueful function for trapping error, epeciall in template. Preent-ailit Cover Page and TOC When a model i deigned for ue more than jut the model uilder, include a cover page. Cover page hould include: 1. Compan and/or project name 2. Decription of the model 3. Modeler and team contact information Include a tale of content when the model i u cientl large to merit it (a good rule of thum i more than 5 workheet). Workheet deign Lael workheet the nature of the anali (i.e. DCF, LO, Fintatement, etc…). Ta hould ow logicall from left to right. When following the input→calculation→output approach, color the workheet ta aed on thi diviion: Wall treet Prep | www.walltreetprep.com 1. Include the compan name on top left of ever heet 2. Include the heet purpoe, cenario elected (when relevant), cale and currenc prominentl elow the compan name on each heet 3. Page etup for printing: When a heet i too long to t in one page, the top row containing compan name, purpoe of the page, currenc and cale hould e diplaed on top of each page (elect “row to repeat at top” (Page Laout>Page etup>heet) 4. Include le path, page numer and date in footer cenario and enitivitie The purpoe of uilding a model i to provide actionale inight that wan’t otherwie readil viile. Financial model hed light on variet of critical uine deciion: How doe an acquiition change the nancial tatement of an acquirer (accretion/dilution)? What i a compan’ intrinic value? How much hould an invetor contriute to a project given peci ed return requirement and rik tolerance? Virtuall all invetment anking model rel on forecating and aumption to arrive at the output preented to client. ecaue aumption are de nition uncertain, preenting the nancial model’ output in range and aed on a variet of di erent cenario and enitivitie i critical. In thi pot aout cenario anali and thi pot aout uing data tale for enitivit anali, we addre the two mot e ective wa to preent nancial output in nancial model. Concluion and further reading We wrote thi guide to provide a framework applicale to invetment anking model. For thoe that want to dive deeper into uilding peci c invetment anking model, conider enrolling in our aghip nancial modeling program. For thoe that want to get into the weed of modeling theor, I recommend the following text: uilding Financial Model John Tjia Financial Modeling imon enninga Wall treet Prep | www.walltreetprep.com
