Guide to alance heet Projection Common approache to forecating alance heet line item when uilding a 3 tatement model Thi article i part of a larger guide: How to uild an Integrated 3 tatement Financial Model alance heet projection exercie Imagine that we are taked with uilding a 3-tatement tatement model for Apple. aed on analt reearch and management guidance, we have completed the compan’ income tatement projection, including revenue, operating expene, interet expene and taxe – all the wa down to the compan’ net income. Now it’ time to turn to the alance heet. etting up the alance heet forecat Tpicall, the main alance heet ection of a model will either have it own dedicated workheet or it will e part of a larger workheet containing other nancial tatement and chedule. efore we dive into individual line item, here are ome alance heet et practice (click here for a complete guide to nancial modeling et practice): 1. At leat two ear of hitorical data It i recommended that at leat two ear of hitorical reult are inputted into the model to help provide ome context to forecat. Data i organized in column acending from left to right. 2. Reclaif GAAP to uit our need Companie preent their alance heet in wa that are not alwa optimized for anali. For example, companie ma lump line item with di erent driver together. In thee cae, the line item need to e eparated and forecating approache hould e tailored to the nature of the item. Converel, GAAP require that certain line item e roken out into current and long-term component (deferred taxe and deferred revenue are common example). However, for forecating purpoe, the can e comined ecaue the are forecat uing the ame driver. 3. Ue upporting chedule All forecating need to e done in upporting chedule — either in the ame workheet or in dedicated eparate workheet. Thi i where the forecating and calculation hould take place. The conolidated alance heet impl pull the nihed product — the forecat — to preent a complete picture. Working capital We tart the alance heet forecat forecating working capital item. (For a complete guide to working capital, read our “Working Capital 101” article.) roadl peaking, working capital item are driven the compan’ revenue and operating forecat. Conceptuall, working capital i a meaure of a compan’ hort-term nancial health. Working capital item include: Account receivale (AR) Grow with ale (net revenue). Uing an IF tatement, model hould enale uer to override with da ale outtanding (DO) projection, where da ale outtanding (DO) = (AR / Credit ale) x da in period. Wall treet Prep | www.walltreetprep.com Inventorie Grow with cot of good old (COG). Override with inventor turnover (Inventor turnover = COG / Average inventor). Prepaid expene If prepaid expene comprie expene predominantl clai ed a G&A, grow with G&A. If ou aren’t ure, grow with revenue. Other Current Aet Grow with revenue (preumal thee are tied to operation and grow a the uine grow). If there’ reaon to elieve the are not tied to operation, traight-line the projection. Account paale If the paale are generated predominantl for inventor, grow with COG. If ou aren’t ure, grow with revenue. Override with paale pament period aumption. Accrued xpene If the accrued expene are largel for expene that will e clai ed a G&A, grow with G&A. If ou aren’t ure, grow with revenue. Deferred revenue Refer to ale that cannot e recognized a revenue et. xample include gift card and oftware for which upfront pament implie right to future upgrade. Grow with the revenue growth rate. Taxe Paale Grow with the growth rate in tax expene on the income tatement. Other current liailitie Grow with revenue. If there’ reaon to elieve the are not tied to operation, traight-line the projection. PP& and intangile aet The larget component of mot compan’ long term aet are xed aet (propert plant and equipment), intangile aet, and increaingl, capitalized oftware development cot. Thee line item are alo driven largel the compan’ operation. In other word, the more revenue, the more capital pending and purchae of intangile we expect to ee. Unlike working capital, PP& and intangile aet are depreciated or amortized (with a few notale exception like land and goodwill). Thi create a laer of complexit in the forecating, a illutrated elow: The PP& roll-forward PP& (OP) + capital expenditure ‑ depreciation‑ aet ale = PP& (OP) Wall treet Prep | www.walltreetprep.com Line Item (ee formula aove) How to forecat PP& (OP) Reference from lat period’ OP Capital expenditure Ue equit reearch or management guidance when availale. In the aence of guidance, aume purchae in line with hitorical trend a a % of ale. Approach 1: Forecat a a % of capital expenditure uing hitorical depreciation a a guide. Depreciation Approach 2: Depreciation waterfall anali (ueful when companie provide u cient detail). Aet ale Mot companie do not regularl o oad aet a a matter of coure, o arring peci c guidance, aume no aet ale. That aid, ome indutrie (like RIT) require recurring aet ale forecat. The intangile aet roll-forward intangile aet (OP) + purchae – amortization = intangile aet (OP) Line Item (ee formula aove) Intangile How to forecat Reference from lat period’ OP Aet (OP) Approach 1: Ue equit reearch or management guidance when availale. Purchae Approach 2: In the aence of guidance, look at hitorical purchae (dicloed in the cah ow tatement). If hitorical purchae are igni cant, grow a a % of ale. If hitorical trend are lump or undicloed, aume no new purchae. Amortization Companie tpicall dicloe future amortization expene for the current intangile aet in 10K footnote. Of coure, if forecating new purchae, thi will have incremental impact on future amortization. In thi cae, appl the hitorical ratio of amortization/purchae. Goodwill Goodwill i uuall traight-lined in a 3-tatement nancial model. In other word, if goodwill on the latet alance heet i $400m, it ta at $400m inde nitel. (For more on goodwill, read our quick primer on how goodwill i created.) That’ ecaue to do anthing ele would impl either: 1. Future goodwill impairment or 2. Future acquiition where the compan pa in exce of the fair market value of the aet acquired. It i di cult to relial forecat uch thing. One exception to thi i when modeling private companie that amortize goodwill. Deferred tax aet and liailitie Deferred taxe are complex (here’ a primer on deferred taxe) and, a ou ee elow, are either grown with revenue or traight-lined in the aence of a detailed anali. Deferred tax aet Approach 1: ince mot DTA are tied to operation (revenue recognition timing di erence and NOL) grow with revenue. Approach 2: traight-lining i alo acceptale in the aence of u cient dicloure to full undertand the nature of the deferred taxe. Deferred Approach 1: ince DTL are often tied to a dicrepanc etween ook and tax depreciation method, DTL will grow with operation over the long run. A tax a reult, a common approach when the full nature of the DTL in’t known i to grow with revenue, jut like DTA. liailitie Approach 2: traight-lining i alo acceptale in the aence of enough dicloure to full undertand the nature of the DTL Wall treet Prep | www.walltreetprep.com Note that DTA and DTL can e clai ed in the nancial tatement a oth current and non-current. Other non-current aet and liailitie You’ll often encounter catch-all line item on the alance heet impl laeled “other.” ometime the compan will provide dicloure in the footnote aout what’ included, ut other time it won’t. If ou don’t have good detail on what thee line item are, traight-line them a oppoed to growing with revenue. That’ ecaue unlike current aet and liailitie, there’ a likelihood thee item could e unrelated to operation uch a invetment aet, penion aet and liailitie, etc. Long term det elow we ee Apple’ 2016 det alance. We oerve that Apple ha oth hort-term commercial paper and long-term det (including a portion that’ due thi ear): Let’ focu on long term det for now and get ack to the commercial paper later. Companie will uuall provide a footnote dicloure of future maturitie of long-term det. In Apple’ 2016 10K, ou can ee a tpical det maturit dicloure which identi e all the upcoming maturitie of longterm det (including the $3.5 illion current portion of long term det that i due in 2017): o we know thee note will e coming due – after all, Apple i contractuall required to pa them down. Thi might lead ou to elieve that forecating det i jut a matter of reducing the current det alance thee cheduled maturitie. ut a nancial tatement model i uppoed to repreent what we think will actuall happen. And what will mot likel actuall happen i that Apple will continue to orrow and o et future maturitie with additional orrowing. That’ ecaue mot companie replace (or “re nance”) maturing det with new det. Companie do thi to maintain a tale capital tructure. Thi mean that even when the footnote dicloe that det will e paid down, it i more appropriate to aume that det ta at current level or grow to re ect a xed capital tructure. Mechanicall we do thi either: 1. Holding the compan’ long term det alance contant or Wall treet Prep | www.walltreetprep.com 2. Growing long term det at the growth in the compan’ net income (argual a etter approach ecaue it tie det to equit growth uing net income a a prox for equit growth). hareholder equit We have now identi ed the forecating technique for all aet and liailitie except for cah and the revolver. We now turn to forecating the line item in the tatement of hareholder’ equit. The four ig line item in that ection are: 1. Common tock and APIC 2. Treaur tock 3. Retained arning 4. Other Comprehenive Income Common tock and APIC Companie iue new common tock in one of two wa: New tock iuance (IPO or econdar o ering) Companie do thi to raie capital, tpicall to fund growth. For example, if a compan want to raie $100m via an equit o ering, the get $100m in cah (deit cah) with a correponding $100m increae in common tock and APIC (credit). Wh do companie iue tock and how doe it compare to raiing mone orrowing from a ank? In ome wa it’ like orrowing, ut rather than paing interet, the hare iuance dilute exiting equit owner. How do we forecat future iuance? ince companie don’t iue tock (via IPO or econdar o ering) on a regular ai, mot of the time, no forecat of tock iuance from thi i necear (i.e. we aume no new hare iuance unle there i peci c juti cation). tock-aed compenation Companie iue tock-aed compenation to incentivize emploee with tock in addition to cah alar. Companie primaril iue tock option and retricted tock to emploee. Accounting for tock-aed compenation Although no cah exchange hand when companie iue their emploee option or retricted tock, companie mut recognize an expene for thi (which the etimate uing an option pricing model). For example, if Apple gave an emploee 1,000 tock option at $150 exercie price, and which vet equall over the next 2 ear, Apple might etimate that thi ha a preent value of $5,000 ($5 per option). Thi ha the e ect of deiting retained earning (ince tock-aed compenation expene i accounted for a an operating expene), while the o etting credit i common tock and APIC. elow ou can ee that Apple’ common tock and APIC account i increaed the $2.863 in tock-aed compenation expene: Wall treet Prep | www.walltreetprep.com How do we forecat tock-aed compenation expene? The mot common wa to forecat tock-aed compenation i to traight-line hitorical ratio of C to revenue or operating expene. ince tockaed compenation expene increae capital tock, whatever we forecat mut increae common tock. ince it alo reduce retained earning ut ha no cah impact, we alo need to add it ack to net income in the cah ow tatement (ee elow). Treaur tock ome companie u ack their own hare when the have exce cah. For example, if a compan u ack $100 million of it own hare, treaur tock (a contra account) decline (i deitted) $100 million, with a correponding decline (credit) to cah. Conceptuall, a hare uack i eentiall a dividend to remaining hareholder paid in the form of additional ownerhip of the compan. In our example, the $100 million that the compan want to return to hareholder can actuall e achieved one of two wa: via a cah dividend or equivalentl via a $100m uack. The per hare increae to each hareholder (all ele equal) hould amount to exactl $100 million in aggregate value. One ene t with the hare repurchae approach i that unlike a cah dividend, tax can uuall e deferred paid hareholder on the uack. From a modeling perpective, arring ome management guidance or thei on future uack, if a compan ha engaged in recurring uack hitoricall (the amount of uack can e found on the hitorical cah ow tatement), traight-lining the amount into the forecat period i uuall reaonale. Forecating hare outtanding and P hare iuance and uack that we forecat on the alance heet directl impact the hare forecat, which i important for forecating earning per hare. For a guide on how to ue the forecat we’ve jut decried to calculate future hare outtanding, read our primer on Forecating a Compan’ hare Outtanding and arning Per hare. Retained earning Retained earning i the link etween the alance heet and the income tatement. In a 3-tatement model, the net income will e referenced from the income tatement. Meanwhile, arring a peci c thei on dividend, dividend will e forecat a a percentage of net income aed on hitorical trend (keep the hitorical dividend paout ratio contant). The retained earning roll-forward retained earning (OP) + net income – dividend (common and preferred) = retained earning (OP) Line item (ee aove formula) How to forecat Net income From income tatement forecat Dividend (Common and Preferred) Forecat a a % of net income aed on hitorical trend. Other comprehenive income (OCI) Under GAAP, there are man nancial activitie whoe gain and loe don’t impact net income: Gain and loe on foreign currenc tranlation, derivative, etc. Intead, the are clai ed a “other comprehenive income” (OCI) and are accumulated in a alance heet line item ditinct from retained earning. You can ee thi in Apple’ alance heet (oerve that the line “accumulated other comprehenive income” declined $1,427m during the ear from an accumulated alance of $1,082 to a negative $354m): Wall treet Prep | www.walltreetprep.com And in a eparate chedule in the 10K ou can ee a full reakout of $1,427m in ear-over-ear change in OCI (much like the income tatement i a reakout of the ear over ear change in retained earning): Forecating OCI Forecating OCI i fairl traightforward. ecaue the gain and loe that ow into thi line item are di cult to predict, the afet et i to aume no change ear-over-ear going forward (in other word, traight-line the lat hitorical OCI alance on the alance heet): The other comprehenive income roll-forward: OCI (OP) +/- OCI generated during the ear = OCI (OP) Line item (ee formula aove) OCI generated during the ear How to forecat Aume no OCI gain and loe in the forecat (i.e. traight-line hitorical OCI alance). Forecating cah and hort term det (revolving credit line) Lat ut not leat, we turn to the forecating of hort term det and cah. Forecating hort term det (in Apple’ cae commercial paper) require an entirel di erent approach than an of the line item we’ve looked at o far. It i a ke forecat in an integrated 3-tatement nancial model, and we can onl quantif the amount of hort term funding required after we forecat the cah ow tatement. That’ ecaue cah and hort term det (the revolver) erve a a plug in mot 3-tatement nancial model – if after everthing ele i accounted for, the model i forecating a cah de cit, the revolver will grow to fund the de cit. Converel, if the model i howing a cah urplu, the cah alance will impl grow. Learn more in our primer on Modeling the Revolving Credit Line. alancing the model Wall treet Prep | www.walltreetprep.com Finall, an alance heet forecat in’t complete if the alance heet doe not alance. While a compan’ reported alance heet will alwa how aet equaling liailitie plu equit, when forecating the alance heet, an numer of mitake can lead to the model getting out of alance. In fact, the trength of a 3-tatement model i that the three tatement are interlinked. However, thee inter-linkage alo increae the potential for error. ome of the mot common reaon the alance heet doen’t alance include: 1. ign (+/-) are witched For example, if our capital expenditure i inputted in the alance heet a a negative (or in the cah ow tatement a a poitive), our model will e out of alance. 2. Milink For example, if our model accidentall reference dividend intead of tock-aed compenation into the common tock chedule, our model will e out of alance. 3. Cah ow tatement error Getting a model to alance i uuall more aout getting the cah ow tatement correct than it i aout getting the alance heet correct. For example, if ou forecat that “other long term aet” on the alance heet grow at the ame rate a revenue ut forget to include the cah impact of thi change on the cah ow tatement, our model will not alance. To ee thi in action, ee our cah ow tatement “quick leon.” 5 tep to alancing our model 1. Print out the full model. 2. eginning with the account receivale line on the /, calculate the cah impact of each line of the / with a calculator. 3. Once ou’ve made the calculation, verif that thi cah impact i correctl expreed on the cah ow tatement. 4. Once veri ed on the CF, cro o oth the alance heet and cah ow tatement line item with a pencil. 5. Proceed to the next line and continue until ou get to the lat line of the alance heet. While thi can e a time conuming proce, the good new i that if ou follow the aove tep correctl, ou will locate the error and our model will alance. Wall treet Prep | www.walltreetprep.com
