ContentsIntroduction

Modellingstepbystep

The10goldenrulesofmodelling

Step1.Objectives,structure,shell

Step2.Inputhistoricalnumbers

Step3.Incomestatementassumptionsandforecast(exceptinterest)

Step4.Balancesheetassumptionsandforecast(exceptcashanddebt)

Step5.Cashflowstatementforecast

Step6.Plugthebalancesheetcash

Step7.Detaileddebtschedule

Step8.Linkshort-termandlong-termdebtintothebalancesheet

Step9.Calculateinterestandlinkintotheincomestatement

Step10.TurnoniterationsinExcelandthemodel’scircularityswitch

Step11.Completeratios,analysisandfinalchecks

CIRCULARITYINFORECASTMODELS

HOWTOCHECKAMODEL–STEPBYSTEP

EXCELESSENTIALSFORMODELLERS

IntroductionWhatisafinancialforecastmodel?

A model takes a company’s key financial statements and forecaststhem into the future, usually for a five to ten year period. It is rare toforecast beyond this, as longer periods are harder to predict and thenumbersbecomemoreuncertain.

AmodelistypicallysetupinanExcelfilewhichwillhaveseparateareasfor the financial statements themselves, forecast assumptions andworkings. Excel formulas link the forecast numbers to forecastassumptions. The assumptions are commonly based on estimatedfuturegrowth ratesandmargins for each line item in the company’sincomestatementandbalancesheet,andsoestimatingassumptionsisakeypartofthemodellingprocess.Poorassumptionswillresultinapoorqualityforecast.Onewaytocheckthequalityoftheforecastistolookatthetrendsinprojectedmarginsandgrowthratesbasedontheestimatednumbers produced in themodel and somost models will also have acomplexratioandanalysissection.

Oncethebasicforecasthasbeenbuilt,themodelcanbeusedforfurthertypes of analysis, and additional worksheets such as discounted cashflowvaluationorleveragedbuyoutvaluationcanbeadded.

Financial models are widely used in the banking environment and bycorporates.Modelscanhelpinmakingimportantfinancialdecisionssuchasnewinvestments,divestmentsandrestructuring.

HowtousethisManual

Thismanualwillguideyouthroughtheprocessofbuildingamodelstepby step.We have provided amodel which you can build as you workthrough the manual. The template and solution files for this model,referred to as theCase Model, can be found on ourBG Portal (BG

Library)

Note that the model template provided already contains historicalnumbersandforecastassumptions.

IfyouarenewtoExcelandmodellingthenmakesureyoureadtheExcelEssentials for Modellers chapter first and work through the practiceexercisesinthepracticefilewhichisalsoavailableatourwebsite.

There are also additional chapters onCircularity in ForecastModelswhichincludesadetailedexplanationofcircularityinmodelsandhowtodealwithitandHowtoCheckaModelwhichcovershowtofindandfixtypicalerrors.

ModellingstepbystepBuildinga financialmodelshouldbedone inanorganisedway.This isessential in helping you to avoid mistakes and will make your modeleasiertofollow.Werecommendthatyoufollowthestepsbelowinorder,toachievethebestresults.

Objectives,structure,shell

Inputhistoricalnumbers

Incomestatementassumptionsandforecast(exceptinterest)Balancesheetassumptionsandforecast(exceptcashanddebt)

Cashflowstatementforecast

Plugthebalancesheetcash

Detaileddebtschedule

Linkshort-termandlong-termdebtintothebalancesheet

Calculateinterestandlinkintotheincomestatement

TurnoniterationsinExcelandthemodel’scircularityswitch

Completeratios,analysisandfinalchecks

Eachstepisexplainedfurtherinthefollowingchapters.

It is recommended that you download the practice model from ourwebsiteandbuildthemodelasyouworkthroughthismanual.

The10goldenrulesofmodelling

1. Keepyourmodelstructureeasytofollowanduser-friendly.

Itisimportantthatyourmodelhasagoodcoverorcontrolsheet(seelater).Consistentstylesandformattingwillensurethatyourmodellooksprofessional.CommentcellstogivemoreinformationtotheuseronwherethenumbersaresourcedfromintheAnnualReportorwhyanadjustmentisbeingmade.

2. DonothaveanyotherExcelworkbooksormodelsopenasyoubuildthemodel.

Itiseasytoaccidentallyfliptothewrongworkbookandlinkcellsinerror.Inaddition,acircularityinoneopenworkbookcancausecircularerrormessagesinanotheropenworkbook.Seethecircularitychapterformoreinformation.

3. AlwaysmodelwithiterationsinExcelturnedoff,andthemodel’scircularitycontrolswitchturnedoff,ifthereisone.

ThisissothatyouwillseeExcel’scircularityindicatorsassoonasyoubuildacircularformulasothatyoucanavoiderrorswhichmightdestabilizeyourmodelandcauseittoblowup.Seethecircularitychapterformoreinformation.

4. Never repeat an assumption on another page. Always link tooneoriginalassumptioncell.

Anewuserofthemodelwouldneverknowthatthesameassumptionneedschanginginmorethanonelocation.

5. Nohard-codednumbersinformulas.

Anewuserofthemodelwouldhavenoideaofwherethehardnumbersareandthereforenotbeabletochangethem.

6. Neverrebuildaformula

Iftheformulahasalreadybeenbuiltonceinthemodelandiscopyable,itisawasteoftimetorebuilditfromscratchandyouaremorelikelytointroduceanewerrorindoingso.Bettertocopyitfromonelocationtoanother,ifneededelsewhere.Remembertodoublecheckthatyourcellreferencesarecopiedcorrectly.

7. Alwaysbuildsubtotalsforthehistoricyears.

Thisgivesyouanextracheckthatthenumbersyouinputforthehistoricyearsarecorrect.Thisisespeciallyimportantintheincomestatementwhereyouarelikelytohavecleanedsomeofthehistoricnumbers.

8. When building formulas, put the assumption first for easierformulachecking

Thetraceprecedentsshortcut,CTRL+F5,willonlygocross-sheettothefirstreferenceintheformula.Seetheerrorcheckingchapterfor

moreinformation.

9. Ideallybuildonlytheyearoneforecastfortheentiremodelandcopyrightonlyoncethemodelbalances

Peopleoftencorrecterrorsinthefirstforecastyearandthenforgettocopyrightthecorrectionsacrosstheremainingyears.

10. Avoiduncopiableformulasasfaraspossible.

Peopleoftenrecopyformulasrightinamodelastheyarecheckingandchangingassumptions,whichcouldresultinerrorswhereformulaswerenotintendedtobecopiedright.

Step1.Objectives,structure,shell

ObjectivesFinancialmodels help companies tomake financial decisions and theycanbepreparedformanyreasons.Belowaresomeofthem:

• Budgeting/forecasting• Valuation• Credit• Companyrestructuring• Debtrestructuring• Investments/divestments(investinginnewassets,selling/part-disposalofassets,buyinganewcompany,sellingacompanyetc.)

Before you begin you need to knowwhy you are preparing a financialmodel,who is going to use it andwhat result it should present.Makesurethatyouknowtheanswerstothesequestionsbeforeyoubegin.

StructureModelstructurescanvarywidelysobeforeyoustartworkingyouneedtodecidewhichtypeofmodelyouwillcreate:

• Towermodel–alloftheinformationisshownononespreadsheet.Thesemodelsaregenerallyeasytonavigateaseverythingisoneplacebutiftheybecometoolong,theythenbecomemuchhardertomanage.

• Bookmodel–eachmainfinancialstatementanddetailedscheduleispresentedonaseparatespreadsheet.Thesemodelsmightendupwithalotoftabsbuttheycanbeeasiertomanoeuvrearoundthanequivalenttowerstylemodels.Checkingformulascanbeabittrickierwiththisstyleofmodel,especiallywheretheyare‘cross-sheet’i.e.includecellreferencesfromdifferenttabsbuttherearewaysofdealingwiththiswhichwewilllookatlater.

Themodelusedforillustrationinthismanualisabookstyleofmodel.

ShellIfyouarenotusinganexistingmodelshell, thenyouwillneed tobuildoneyourself.

Thismeans:

• SetuptheExcelfileandmakesurethatiterationsinExceloptionsareturnedoff.Thiswillhelpinidentifyingandpreventingcircularerrorsasyouareinputtingnumbers.Formoreonthis,seetheerrorsection.

Whilstcircularityistheexistenceofacircularformulainamodel(aformulawhichincludesitselfintheanswer),iterationsareanExceltoolforsolvingsolvablecircularformulas.Interestinamodelisdeliberatelybuiltasacircularformula,butascircularformulascande-stabilisemodels.

SeetheCircularityinForecastModelschapterformoreoncircularityanditerations.

• Designacontrolsheetwithkeyinformationaboutthecompanybeingmodelled,includingcurrency,units,year-end,detailsofthemodelbuilderandanyswitchesifused,makingsuretheyareinitiallysettooff.Ifyouareusingacircularityswitch,thenyoucan

rangenameitatthisstage,tomakeiteasiertoreferenceinformulaslater.

Forhelponhowtorangenamecells,seetheExcelEssentialsforModellerschapter.

• Decidewhichotherworksheetstoinclude–inabookstylemodelyouwillneedseparatesheetsforincomestatement,balancesheet,cashflowstatement,debtschedule,detailedcalculationssuchasproperty,plantandequipment(PPE),andratiosasaminimum.

• Decidewheretoshowthemodelassumptions–historicandprojectedgrowthratesandratios

Inabookstylemodelyoucouldchoosetoshowallassumptionsonaseparatesheetoryoucouldshowthemthreadedthroughoutthemodel–balancesheetassumptionsonthebalancesheetpage,incomestatementassumptionsontheincomestatementpageetc.

Iftheassumptionsareallonasinglepage,yourmodelwillcontainagreaternumberof‘cross-sheet’formulaswhicharetrickiertocheckbuteasiertokeeptrackofastheyareallinoneplace.

• Decideonstylesandformatsforinputcells,textandhistoricalandforecastnumbers.

Banksoftenhavetheirowninternalguidelinesonhowinformationshouldbepresentedinthemodelssocheckwithyourcolleagues.

Werecommendthatallnumbersareshowntoonedecimalplace,exceptforearningspershare(EPS),revenuegrowthrateanddividendspershare,whichareusuallyshowntotwodecimalplacesifpresented.

Itiscommonwithinmodelsforhardcodednumberstobeshowninbluefontcolourandformulas(calculatednumbers)tobeshowninblack.

Inthesamplemodelforthismanual,inputcellshaveagrey

backgroundandblueandboldfont whilsttheformulacellshaveawhitebackgroundandblackfont

Itiseasiertomodelwhereexpensesanddeductionsarepresentedasnegativenumbers.

Forhelponstylesandformatting,seetheExcelEssentialsforModellerschapter.

• Setuptheincomestatement,balancesheetandcalculationssheetswithheadingsandchoosewhichlineitemstoshow.YouwillneedtotypetheseheadingsintothemodelmanuallyifyouarestartingfromablankExcelworkbook.

Forhelponwhatthemodeltemplateshouldlooklike,seetheCaseModelwhichsupportsthismanual,andthesamplescreenshotsbelow.

Modelstypicallyshowtwoorthreeyearsofhistoricnumbersandfivetotenyearsofforecastnumbers.

Notethatwhilstmostmodelstendtocloselyfollowtheheadingspresentedinthepublishedfinancialstatements,itiscommontogroupsomelineitemstogethertosimplifythem.

SeeStep4.Balancesheetassumptionsandforecastchapterformoreinformationonoperatingcash.

IntheincomestatementitisnormaltoshowdepreciationandamortisationseparatelysothatEBITandEBITDAarepresentedandforecastedseparately.Dependingonthelayoutofthemodelincomestatement,thismaymeanthatyouwillhavetofindthehistoricalnumbersfordepreciationandamortisationandremovethemfromhistoricalcostofgoodssoldand/orselling,generalandadministrativeexpenses,respectively.

Makesurethatyoucommentcellswhereyouhavemadeadjustmentstothenumbersandclearlyshowthecalculationstepsintheformulassothatotherscancheckyourwork.

intheformulassothatotherscancheckyourwork.

Incomestatementshell

Balancesheetshell

Calculationssheetshell

Step2.InputhistoricalnumbersIncluding thehistorical information isanessential step inbuildinga fullmodel. This helps us to understand the business we aremodelling aswellasbeingastartingpointfordevelopingtheforecastassumptions.

The historical information will be included in two of our three mainfinancialstatements:

• Incomestatement• Balancesheet

There isnoneedtorecreateandthenreconcile thehistoricalcashflowstatement.Thecashflowstatement inamodelshowslinebylinedetailforforecastnumbersonly.

Howmanyyearsofhistoricaldata?

You should aim at including two or three years of historical data. Thatamountof informationshouldgiveyouan insight intoany trends in thebusinessandisagoodbaseforbuildingforecasts.

Cleanthehistoricincomestatementnumbers

When including historical information in the income statement you willhavetocleanthenumbers.Thismeansthatyoumustexcludeanyone-off and exceptional items using the information found in the AnnualReport. Non-recurring items can be found in any line in the incomestatement and finding them and deciding whether or not to adjust forthemcanbetrickyandaquestionof judgement.There isoftennorightanswer.

Sometimes companies disclose the non-recurring items on the incomestatement as a separate line item, and sometimes companies disclosethemintheNotestotheFinancialStatement.

Typical one-off items are asset impairments, business restructuringchargesandlargegains/lossesondisposals.

Whyclean?

Thismeans that the projected numberswill not be distorted by one-offitems that affected historic numbers and should therefore be morereliable.Inparticular,EBITandEBITDAneedexcludetheimpactofnon-recurringitems.

The non-recurring items themselves will still need to be shown in aseparatelineiteminthemodelincomestatementsothatthenetincomestillmatchesthehistoricreportednetincome.

If you wish to also show a clean net income line in the incomestatement,thenyouwillneedanotherlinetoshowpost-taxnon-recurringitemswhichyoucanadjustoutofreportednetincome.

Tofindthevalueof thepost-taxnon-recurring items,usethedisclosurefromtheAnnualReportifthereisany,orassumethattheyweretaxedatthecompany’smarginaltaxrateineachyear.

Makesurethatyoudocumentwhichhistoricnumbershavebeencleanedbyshowingtheadjustmentsclearlyinthecalculationsandbyaddingcommentscontainingpagereferencestothefinancialstatementstotherelevantcells.

Calculate all subtotals and totals in the historic year incomestatement and balance sheet and cross-check to the actualfinancials

Never simply type in subtotals or totals for the historic numbers. Bybuilding formulas, youhaveanextra checkas towhether thenumberswere input correctly. This is especially important where you haveconsolidated line items compared to the actual financials orwhere youhave cleaned numbers, for example in the income statement, wherebottomlinenetincomeshouldmatchthereportednumbers.

Whenyoubuildtheforecasts,allsubtotalsandtotalscanbecopiedoverfrom the historic years to avoid unnecessarily re-building formulas andpotentiallycreatingnewerrorsindoingso.

Incomestatementhistoricalnumbers

IncludeabalancecheckformulainallhistoricbalancesheetsIfyourhistoricyearbalancesheetsdonotbalance,thenyouarealreadyofftoabad start! Check by building an Excel formula at the bottom of eachbalancesheet.

Itcanbeassimpleas:

=TotalAssets–TotalLiabilities

Ormore sophisticated if you use an IF statement = IF (Total Assets =

Total Liabilities,“OK”,Total Assets – Total Liabilities) This formula isextremelyimportant–ifthebalancesheetdoesnotbalance,theamountbywhich it isoutcanhelpyouto identifywheretheerroroccurred.Formoreonthis,seetheHowtocheckamodelchapter.

Balancesheethistoricalnumbers

Input historical numbers on workings sheets It is normal to showhistoricalcapitalexpendituresand/oradditionstointangibleassetsinthehistoric year columns in the property, plant and equipment and/orintangible assetsworkings in the calculations sheet(s). These numberswillneed tobe identified in thepublished financialstatementsand thentypedintothemodelmanually.Makesurethatyoucommentthesecellsto explain where exactly the numbers are located in the financialstatements.

Calculationssheethistorical

numbers

Step3.Incomestatementassumptionsandforecast(exceptinterest)

Oncethehistoricalnumbershavebeen input intothe incomestatementand balance sheet and subtotals and totals calculated, a ratio, growthrateorotherassumptionmustbecalculatedforeachlineitemexcludingsubtotals.

Bylookingattrendsintheratiosyouhavebuiltforthehistoricnumbers,andothersourcesofinformationsuchasfurtherdisclosureintheAnnualReport or broker research, you should be able to build forecastassumptionsforeachlineitemintheincomestatementandthebalancesheet.

Let’sstartwiththeincomestatementandworktop-downfromsales.

Remember thatwhenyouget toasubtotal,copy the formularight fromthehistoricyearanddonotrebuildagainfromscratch.Thiswillminimizeerrorsinyourmodel.

SALES(REVENUE)

Salesareakeydriverofmanyotherlineitemsinthemodel,bothintheincomestatementandthebalancesheet.

HowtoforecastSales are generally forecast using a growth rate assumption. Somemodelscontaindetailedsalesforecastswhicharesplitoutbybusinessorgeographical sector and each sector projected individually. It is alsopossible to separatesalesgrowth intocomponentsofpricegrowthandvolumegrowth.Volumegrowthcanalsobeseparatedintomarketgrowthand share of market growth, all of which can be forecast separatelydependingonhowmuchdetailisneeded.

HistoricratiosAssumingasimplegrowthrateassumption,calculatethegrowthrate inthehistoricyearsusing the formula:Growth rate=Currentyearsales /Prioryearsales–1

Saleshistoricgrowthrateexample

Forecastassumptions

Usecompanydisclosuresandequity research tohelpdevelop forecastsalesgrowthassumptions.Itiscommontoassumethatthecompanywillhavereachedastable long-termgrowthrateby the finalprojectedyearand togradually taper thegrowth ratedownyearbyyear to this targetrate.

Salesforecastassumptionsexample

Forecast

Build the forecastusinga formula to link lastyear’ssales to thegrowthrateforecast:Forecastsales=Prioryearsales*(1+Growthrate)

Salesforecastexample

COSTOFGOODSSOLDEXPENSE(COGS)

Cost of goods sold represents the direct costs of making sales. In amanufacturingcompanythiswouldbecomprisedofthecostofinventorysoldduring theperiodand factorystaffsalariesandoverheadssuchasdepreciation.

HowtoforecastCOGS is typically forecast using margin assumptions based onCOGS/Sales.

HistoricratiosCalculate thehistoricCOGSmarginusing the following formula:COGSmargin%=COGS/Sales

COGShistoricmarginexample

Forecastassumptions

Checkcompanydisclosuresandequityresearchtoseeifthecompanyisplanning foranychange inCOGSexpense. Ifstablehistoricallyandnochangeexpected,usethemarginfromthelatestfinancialyearorusetheaverageofthehistoricyearmarginsinallprojectedyears.

COGSforecastassumptionsexample

Forecast

Buildtheforecastusingaformulatolinktheassumptiontosalesontheincomestatement.

ForecastCOGS=ForecastCOGSmargin%*Forecastsales

COGSforecastexample

GROSSPROFITFORECAST

Copy right the formula forgrossprofit from theprior year,asyouhavealreadycalculatedgrossprofitforthehistoricalyears.

Grossprofitforecastexample

SELLING,GENERALANDADMINISTRATIVEEXPENSE

Selling, general and administrative expense represents other costsassociatedwithgeneratingsalesandcontainsitemssuchasheadofficestaff salaries, non-production related overheads, marketing anddistributioncosts.

HowtoforecastSG&A expense can be forecast using margin assumptions based onSG&Aexpense/Salesoralternativelybyusingagrowthrateassumption.A growth rate assumption is useful where the company has plans toreduce the SG&A expense relative to sales and there is informationprovidedaboutthetimingandamounts.

Historicratios

EitherCalculate the historic SG&Amargin using the following formula: SG&Amargin%=SG&Aexpense/Sales

OrCalculate the historic growth rate inSG&Ausing the following formula:SG&Agrowthrate%=CurrentyearSG&Aexpense /PrioryearSG&A

expense-1

SG&Ahistoricmarginexample

ForecastassumptionsCheckcompanydisclosuresandequityresearchtoseeifthecompanyisplanning foranychange inSG&Aexpense. If stablehistoricallyandnochangeexpected,usethemarginorgrowthratefromthelatestfinancialyearorusetheaverageofthehistoricyearmarginsorgrowthratesinallprojected years. If changes are expected, then reflect them in theforecastSG&Agrowthrate%.

SG&Aforecastassumptionsexample

ForecastSG&Aexpense

BuildtheforecastusingaformulatolinktheassumptiontosalesontheincomestatementortoprioryearSG&Aexpense.

Either

ForecastSG&A=ForecastSG&Amargin%*ForecastsalesOr

ForecastSG&A=(1+ForecastSG&Agrowthrate%)*PrioryearSG&AexpenseForecastSG&Aexpenseexample

EBITDAEBITDA is a profit measure that you are unlikely to see in publishedfinancialstatements,butisanimportantnumberincorporatefinanceandvaluation. It stands for ‘EarningsBefore Interest,Tax,DepreciationandAmortisation’ and is a variation onEBIT, ‘EarningsBefore Interest andTax’.

Themost important featureofEBITDAisthat it isanormalisednumber(‘clean’).Thismeansthatnon-recurringitemshavebeenremoved.

SeeStep.2Inputhistoricalnumbersformoreonwhyandhowtocleanincomestatementnumbers.

At this stage, you will have already calculated and shown normalisedEBITDA in the historical numbers, therefore copy right the formula forEBITDAfromthehistoricyearintotheforecastyear.

EBITDAforecastexample

DEPRECIATIONEXPENSE

This represents the annual depreciation charge against net property,plant and equipment (PPE) and is usually calculated as part of thedetailedPPEworkingsona separate sheet.Remember that this isnotusuallydisclosedseparatelyintheincomestatementasreportedbythecompany,but in themodelwedeliberatelyseparate itoutanddisplay itasaseparateincomestatementline.

Ifyouareworking lineby linedown the incomestatement,at thispointyoumightwanttostopandgotothePPEworkingssheettocompletethePPEcalculations.Youcanthenreturntothe incomestatementandlinktheforecastdepreciationnumbersin.

Alternatively you can forecast the depreciation directly in the incomestatementandbuildthePPEworkingssheetlater.

HowtoforecastWhere PPE is being forecast on a net basis, depreciation expense istypicallyforecastas%oflastyear’snetPPEnumber.

HistoricratiosCalculatehistoricdepreciationexpenseas%ofprioryearnetPPEusingthe following formula: Depreciation expense to prior year net PPE% =Currentyeardepreciationexpense/PrioryearendingnetPPE

Note that if youonlyhave twoyearsofhistoricbalancesheetnumbersthen you will only be able to calculate this ratio for the most recenthistoricyear.

DepreciationtoprioryearnetPPE%example

ForecastassumptionsIt is usual to keep this assumption at the same level as the latesthistorical year as it is hard tomake any assumptions about howassetlivesordepreciationpoliciesinthecompanymightchangeinthefuture.

Depreciationforecastassumptionsexample

Forecastdepreciationexpense

BuildtheforecastusingaformulatolinktheassumptiontoprioryearnetPPE:Forecastdepreciation=Depreciationexpense/prioryearnetPPE%*PrioryearnetPPE

Forecastdepreciationexpenseexample

AMORTISATIONEXPENSE

Thisrepresentstheannualamortisationchargeagainstintangibleassetswithdefinitelives.Rememberthatasfordepreciation,thisisnotusuallydisclosed separately in the income statement as reported by thecompany,butinthemodelwedeliberatelyseparateitout.

HowtoforecastAmortisationisusuallyforecastusingexpectedannualamountsorusing%ofprioryear’snetintangibles.

HistoricratiosEither link historic amortisation expense from the historic incomestatement into the historic assumptions, or alternatively calculate theamortisationexpensetoprioryearnetintangibles%:

Amortisationexpensetoprioryearnetintangibles%=Currentyear

Amortisationexpensetoprioryearnetintangibles%=Currentyearamortisationexpense/Prioryearendingnetintangibles

Amortisationhistoricamountsexample

Forecastassumptions

Expected future amortisation expense is sometimes disclosed in theNotes to the Financial Statement, in which case you can use thesenumbers in your forecast assumptions. Otherwise the policy onamortisation may be disclosed and you can reflect that in yourassumptionsinstead.

Alternatively, ifnoother informationcanbefound,thentheamortisationexpense is kept flat as per the previous year or basedon theaverageamortisationamountsoverthehistoricyears.

Amortisationforecastamountsexample

Forecastamortisationexpense

Forecastamortisationeitherbylinkingtotheamountsintheassumptionsorbyusingtheformula:

Forecastamortisationexpense=Amortisationexpensetoprioryearnetintangibles%*Prioryearnetintangibles

Forecastamortisationexpenseexample

EBIT

EBITistheprofitsubtotalwhichrepresents‘EarningsBeforeInterestandTaxes’.Itissimilartooperatingprofit,howeveritisnormalisedtoexcludenon-recurring itemsandnon-core incomeandexpenses.Becareful,assometimescompaniespresentEBIT in their incomestatementsbut it isnotanormalisednumber.

SeeStep.2Inputhistoricalnumbersformoreonwhyandhowtoclean

incomestatementnumbers

At this stage, you will have already calculated and shown normalisedEBITinthehistoricalnumbers,thereforecopyrighttheformulaforEBITfromthehistoricyearintotheforecastyear.

EBITforecastexample

NETINTERESTEXPENSE/(INCOME)

Interestexpenseand interest incomearenormally forecastasseparateitemsonthedebtcalculationssheet,alsoknownasthe‘debtschedule’.Ontheincomestatementtheycanbepresentedasseparateitemsorasonesingle‘netinterestexpense/(income)’line.

On the debt schedule, interest income is calculated on surplus cashbalanceandinterestexpenseiscalculatedforbothshort-termandlong-termdebt.Dependingonthecomplexityofthemodel,thelong-termdebtmay be separated out into each individual debt item, with a separateinterestexpenseforecastcalculatedforeach.

The interest calculation is often based on an average debt balancewhich creates circularity in themodel once it is linked into the income

statement.Circularitycanmakethemodelunstableandfor thisreason,wedonotcalculateorlinktheinterestforecastnumbersintotheincomestatement until all other numbers have been forecasted and linked in.Thiswillbeexplainedfurtherlater.

For now, you can either leave the interest line(s) in the incomestatement emptyor youcan keep interest flat andnon-circular bylinking to the amount in the latest historical income statement. Ifyoudothis,makesureyoucolourthefontorshadethecellssothatyoudonotforgettoupdatethenumberslater.

Example

NON-RECURRINGEXPENSE/(INCOME)

As explained earlier, non-recurring items are ‘one-off’ items thatpotentially distort historic and forecast numbers. You will have alreadyexcludedthemfromthehistoricincomestatementnumbers.

HowtoforecastBydefinition,non-recurring itemsarenotexpectedtorecurandsotheyarenormallyforecastatzero,however,sometimesfutureexpectednon-recurring itemsaredisclosedby thecompany,orcanbeseen inequityresearchforecastnumbers.Thiscouldbethecaseifthecompanyhasanongoing restructuring program. If forecast numbers are available, thenyoushouldusethem!

Historicratios

Showthenettotalamountofhistoricnon-recurringitemsbylinkingbacktothehistoricincomestatement.

ForecastassumptionsForecastatzeroorusingavailableinformationasabove.

Forecast non-recurring expense/(income) Build the forecast using aformulatolinktotheassumptions.

Forecast non-recurring expense/(income) example

PROFITBEFORETAX(PBT)

At this stage, you will have already calculated and shown PBT in thehistorical numbers, therefore copy right the formula for PBT from thehistoric year into the forecast year. This number will change after theinteresthasbeenlinkedintotheincomestatement.

PBTforecastexample

TAX

Thetaxexpenselinerepresentstheaccountingtaxchargefortheyear,whichisnotthesameasthecashpaid.

HowtoforecastTax expense is usually forecast using assumptions for effective taxrates(ETR).Effective taxratesarebasedon theratioof taxexpense /profit before tax and may be quite different frommarginal tax rates,whicharebasedonlegalratesoftax.

Marginaltaxratesmayalsobeusedinamorecomplexmodel,tofindoutthepost-taximpactofnon-recurringitemsonnet income,inwhichcaseboth effective and marginal tax rates will need to be included in theassumptionssection.

Historicratios

Calculatethehistoriceffectivetaxratesusingthefollowingformula:

ETR%=Taxexpense/Profitbeforetax

Historic marginal tax rates can be found in the Notes to the FinancialStatementsintheAnnualReport,inthetaxdisclosurenote.

Taxhistoricratesexample

Forecastassumptions

Itisusualtoassumetheeffectivetaxratewillremainconstantandmodelflat based on the latest historic year, however if there were significantnon-recurringitemsdistortingthehistoriceffectivetaxrates,thenacleaneffectivetaxrateshouldbeusedfortheforecastinstead.

Mostcompaniesdiscloseareconciliationofmarginaltoeffectivetaxratein theNotes to theFinancialStatement,andyoushouldbeable toseethetaximpactofany‘one-off’itemshere.

Alternatively the tax impact of non-recurring items may be disclosedtogetherwiththenon-recurringitemsthemselveselsewhereintheNotesto the Financial Statement. This will enable you to calculate a cleaneffectivetaxrate.

Ifyouarealsoforecastingthecompany’smarginaltaxrate,unlessotherinformation isavailable in theAnnualReport, it isusual toassume that

thisratewillremainflatbasedonthelatesthistoricyear.

Taxforecastassumptionsexample

Forecasttaxexpense

BuildtheforecastusingaformulatolinktheETR%assumptiontoprofitbeforetaxontheincomestatement.

Forecasttaxexpense=ForecastETR%*Forecastprofitbeforetax

Forecasttaxexpenseexample

NETINCOME(PROFITAFTERTAX)

Atthisstage,youwillhavealreadycalculatedandshownnet incomein

the historical numbers, therefore copy right the formula for net incomefrom the historic year into the forecast year. This number will changeaftertheinteresthasbeenlinkedintotheincomestatement.

Netincomeforecastexample

POST-TAXNON-RECURRINGITEMS

Inorder tocalculateanormalized (clean)net incomenumber, thenon-recurring items net of their own tax impact must be removed fromreportednetincome,asforthehistoricincomestatementinthemodel.

HowtoforecastBythisstage,youhavealreadyforecastthenon-recurringitems.Tofindthepost-taxamounts it is usual toapply themarginal tax rate to thesenon-recurring items, unless the exact tax rate applicable has beendisclosedorcanbededucedfromtheaccountingdisclosures.

Historicratios

Youdonotneedtocalculateanyhistoricratiosforpost-taxnon-recurringitems.

ForecastassumptionsNo additional forecast assumptions are needed, as you already haveforecastnon-recurringitemsandforecasttaxrates.

Forecastpost-taxnon-recurringitemsBuildtheforecastusingaformulatocalculatepost-taxnon-recurringitems.

Forecastpost-taxnon-recurringitems=Forecastnon-recurringitem*(1–Marginaltaxrate%)Or

Use thepost-taxamountsasdisclosedby thecompanyPost taxnon-recurring items example

RECURRINGNETINCOMEAt this stage, you will have already calculated and shown normalisedrecurring net income in the historical numbers, therefore copy right theformula for recurringnet income from thehistoricyear into the forecastyear.Thisnumberwillchangeaftertheinteresthasbeenlinkedintotheincomestatement.

Recurringnetincomeexample

DIVIDENDSANDDIVIDENDSPERSHARE

Dividends represent the company’s net income distributed to theshareholders.

Dividendsarenotpresented in the incomestatementwhichyousee inthe Annual Report, but it is common to present them underneath theincome statement in a financialmodel. Fromhere theywill be used tocalculatebalancesheet retainedearningsandwill also link to thecashflowstatement.Dividendspersharearepresentednext to theearningspersharemeasures.

HowtoforecastDividends can be forecast using a payout ratio assumption or by adividendgrowthratepershare.

Ifyouforecastusingapayoutratioyoumustdividethedividendamountbythebasicnumberofsharesoutstandingtogetthedividendpershare.

Ifyouforecastusingdividendpersharegrowthratethenyouwillneedtocalculate forecastdividendper share firstand thenmultipleby forecastbasicnumberofsharestogetforecasttotaldividends.

Historicratios

EitherCalculatethehistoricpayoutratiousingthefollowingformula:

Dividendpayoutratio%=Dividendpaid/Netincome

OrCalculate the historic growth rate in dividend per share using thefollowingformula:

Div.persharegrowthrate%=(Currentyeardiv.pershare/Prioryeardiv.pershare)-1

Dividendshistoricpayoutratioexample

Forecastassumptions

Manycompaniesdisclosea targetpayout rateor targetgrowth rate for

dividendssomakesureyouchecktheAnnualReporttoseeifthereareany relevantdisclosures. It isoften in themanagement,discussionandanalysis section, rather than in the detailed Notes to the FinancialStatement. Also check equity research and historic newsannouncements.Ifnoinformationisprovided,youcanassumetherateisasperthelasthistoricyear.

Dividendforecastassumptionsexample

Forecastdividends

Buildtheforecastusingaformulatolinktheassumptionintotherelevantincomestatementnumber(s).

Either

Forecastdividendtotalamount=Forecastdividendpayoutratio%*Forecastnetincome

Or

Forecastdividendpershare=(1+Dividendpersharegrowthrate%)*Prioryeardividend

Forecastdividendsexample

BASICANDDILUTEDEARNINGSPERSHARE

As in theAnnualReport,earningspersharenumbersarepresentedatthe bottom of the income statement. Earnings per share (EPS) is animportant investor ratiowhichgives thenet incomeattributable toeachshare.

In a model, we use the clean net income (recurring net income) tocalculate the EPS, although in some models both ‘as reported’ and‘recurring’EPSareshown.

The number of shares is based on ‘weighted average sharesoutstanding’.This isnotnecessarily thesameassharesoutstandingattheyearend. It isanumberwhichhasbeen time-weighted toallow forchangessuchasnewissuesorsharebuybacksthatoccurredpart-waythroughtheyear.

In addition, there are two versions of theEPS, the basic EPS and the

dilutedEPS.ThedilutedEPSisa‘worstcasescenario’whichallowsforthe conversion of dilutive instruments such as share options andconvertiblebonds.

Companies give detailed disclosures on the EPS calculations in theNotestotheFinancialStatement.

HowtoforecastThe forecastnet incomehasnowbeencalculatedandso it isonly theweightednumberofsharesoutstandingwhichneedstobeforecast.

HistoricratiosIfyouwanttopresentrecurringEPSthencalculateforthehistoricyearsusingthefollowingformulas:

Basic recurring EPS = Recurring net income / Basic weightedaveragesharesoutstanding

Diluted recurring EPS = Recurring net income / Diluted weightedaveragesharesoutstanding

You will need to type the historic basic and diluted weighted averagesharesoutstandingintothehistoricassumptionsifyouhavenotyetdonethis.Make sure you comment the cells to explainwhere in theAnnualReportyoufoundthenumbers.

HistoricEPSexample

Forecastassumptions

Unlessspecific informationabout futuresharechangeshasbeengivenby thecompany, then it isusual toassumethat thesharenumberswillremainflatasperthelasthistoricyear.

ForecastbasicanddilutedEPSCopy right the formulas for basic and diluted EPS from the prior year.Thesenumberswill changeafter the interest has been linked in to theincomestatement.

ForecastbasicanddilutedEPSexample

Step4.Balancesheetassumptionsandforecast(exceptcashanddebt)Nowbuildthebalancesheetforecastlinebylinestartingfromthetop.

Initiallyleavethecashforecastemptyandkeeptheforecastshort-termandlong-termdebtlinesflatbasedonthelasthistoricyear.

Useseparatecalculationsheets formorecomplexbalancesheet itemswhennecessary(seelater).

CASHANDCASHEQUIVALENTSCashandcashequivalentscontainsurpluscashandhighlyliquidshort-terminvestments.

Unlike most line items in the model, there is no assumption forforecastingcash.Theamount is foundbycompleting the forecastcashflowstatementandforecastcashflowstatementreconciliation.

In some models, analysts choose to separate operating cash fromexcess cash as explained below. In this case, it is the ‘Excess cash’forecastwhich is leftblank initiallyand laterpluggedfromthecashflowstatementreconciliation.

Youhavealreadyinputthehistoriccashbalancesintothemodelbalancesheet.

Leave the forecast cash line empty until the cash flow statementhasbeencompleted.

Youmaywanttohighlightthislinetoremembertocompletelater.

Excesscashforecastexample

OPERATINGWORKINGCAPITALITEMS

Operatingworkingcapitalincludesoperatingcurrentassetsandliabilities.

Examples of operating working capital assets include accountsreceivable, prepayments, inventories and sometimes operatingcash.

OperatingcashOperatingcashisalineitemwhichanalystssometimeschoosetoshowinthemodelbalancesheet,eventhoughitisunlikelythatyouwouldeversee it inanycompany’spublishedAnnualReport.Theargument is thatcertain types of companies, such as retailers, must always keep aminimumlevelofcashinthebusinesstobeabletocarryouttheirdailyoperations.Analystswillapplyanassumptionsuchasoperatingcashis2% of sales each year, and apply this retrospectively in the historicyears as well as for the forecast years. This operating cash is anoperatingitemandshouldbepartofoperatingworkingcapital.

Examplesofoperatingworkingcapitalliabilitiesincludeaccountspayables,accruedexpensesandtaxespayable.

Thereisusuallyaseparatecalculationsheetforoperatingworkingcapitalinthemodelandtheindividualitemscanbeforecastedeitherthereorinthebalancesheet.

The movement in net operating working capital is calculated on theoperatingworking capital calculation sheetandwill be reflectedasoneiteminthecashflowstatementlater.

HowtoforecastThekeyoperatingworkingcapitalitemsofaccountsreceivable,inventoryand accounts payable are typically forecast based on ‘days ratios’ asbelow.

Other operating working capital items are forecast based onmarginsrelativetosalesorcostofgoodssold,dependingonwhattheitemis.Taxespayableisoftenforecastbasedontheratiototaxexpense.

HistoricratiosCalculatethehistoricratiosforeachitemasfollows:

Accountsreceivablesdays=Accountsreceivable/Sales*365

Inventorydays=Inventory/COGSexpense*365

Accountspayabledays=Accountspayable/COGSexpense*365

Other short-term operating assets margin % = Other short-term

operating assets / Sales Taxes payable to tax expense% = Taxespayable/TaxexpenseOthershort-termoperatingliabilitiesmargin%=Othershort-termoperatingliabilities/COGS

Historicratiosexample

Operatingcash

Ifforecastingoperatingcash,thenforthehistoricyears,youwillneedtoapply a historic ratio of operating cash margin to be retrospectivelyapplied to the historic balance sheet cash, as well as for the forecastamounts. Analysts typically use an assumption of between 0.5% and2.0% for operating cash to sales. Type in your assumption as a hard-codednumber.

Youwillneedtoapplythisassumptioninthehistoricyearstofindhistoricbalancesheetoperatingcash.Onceyouhavetheamounts,thensubtractthem from the cashas reported in theAnnualReport balancesheet tofindexcesscashinthehistoricyears.

Cashandcashequivalents(asreported)=Operatingcash+ExcesscashHistoricoperatingcashtosales%example

Historicoperatingcashexample

Forecastassumptions

Check the company disclosures and equity research to see if thecompanyisplanningonincreasingorchangingworkingcapitalefficiency.If stable historically and no changes expected, use the ratio from thelatest historical year or the average ratio of the historic years for allprojectedyears.

Foroperatingcash,keeptheassumptionflatfromthehistoricyears.

Operatingworkingcapitalforecastassumptions

ForecastoperatingworkingcapitalitemsBuildtheforecastsusingformulastolinktheassumptionstotherelevantincomestatementlines.

Forecastaccounts receivable=Forecastaccounts receivabledays /365*ForecastsalesForecast inventory=Forecast inventorydays /365*ForecastCOGSexpense

Forecastaccountspayable=Forecastaccountspayabledays/365*ForecastCOGSexpenseForecastothershort-termoperatingassets= Other short-term operating assets margin % * Forecast salesForecast operating cash = Forecast operating cash to sales % *Forecastsales

Forecasttaxpayable=Forecasttaxpayabletoforecasttaxexpense% * Forecast tax expense Forecast other short-term operatingliabilities=Forecastothershort-termoperatingliabilitiestoCOGS%*ForecastCOGS

OWCforecastexample

If you calculated theOWC forecast numbers on the calculations sheetinstead,nowlinkthenumbersfromtheretothebalancesheet.

Calculationofnetoperatingworkingcapital

IfyoucalculatedtheOWCforecastnumbersonthebalancesheet,nowlink thesenumbers into theoperatingworkingcapitalcalculationon thecalculations sheet where you must calculate net operating workingcapital

Netoperatingworkingcapital=Operatingcurrentassets–Operatingcurrentliabilities

Thiswillbeusedlater,whenthecashflowstatementforecastisbuilt.

NetOWCexample

SHORT-TERMAND/ORLONG-TERMINVESTMENTS

Short-term investments includesharesordebtheld inother companiesandcouldalso includederivatives.Someof these itemsareaccountedfor at market value on the balance sheet date and this is difficult toforecast.

Howtoforecast

Short and long-term investments tend to be kept flat, in linewith latesthistoricyear.

PROPERTY,PLANTANDEQUIPMENT(PPE)

Theproperty,plantandequipment line in thereportedbalancesheet isthenetresultofgrossPPE(PPEatcost)andaccumulateddepreciation.The Notes to the Financial Statement will give differing levels ofdisclosure depending on which accounting rules the company isfollowing.

WithintheNotestotheFinancialStatement,allcompaniessplittheirPPEout by type of asset but companies following IFRS also give detailedbreak-downsofhowgrossPPEandaccumulateddepreciationchangedovertheyear,withprioryearcomparatives.

AsimplermodelwillforecastnetPPEwiththeresultbeinglinkeddirectlytothebalancesheet,asbelow.

Whether simple or complex PPE, the calculations are usually on aseparatespreadsheet.

HowtoforecastABASEtableisbuiltonthePPEcalculationsheetasfollows:

B BeginningnetPPEamount(aspertheendingamountlastyear)

A AdditionstonetPPE–capitalexpenditure(thisneedstobeforecast)

S SubtractionsfromnetPPE–depreciationexpense(thisneedstobeforecast)

E EndingnetPPEamount(calculateasbeginningnetPPE+capex-depreciation)

TheBASEtable isa ‘corkscrew’table– lastyear’sendingnetPPEisthesamenumberasthisyear’sbeginningnetPPE.

PPEBASEtableexample

Capital expenditure is typically forecast as a margin based on capitalexpendituretosales.

Depreciation expense is typically forecast as% of last year’s net PPEnumber.(SeeStep3.Incomestatementassumptionsandforecastformoreondepreciationexpense).

HistoricratiosDonotattempttobuildtheBASEtableforthehistoricyears!

ThesimplemethodofmodellingnetPPEdoesnotallowfordisposalsorother changes which are not captured by the BASE table above. ThismeansthatthehistoricBASEtableisunlikelytosumtonumberswhichmatchthehistoricbalancesheetnumbers.

InthehistoricyearBASEtable:

InputtheCapexnumbersforallhistoricyears,ifnotdoneinStep2.

Input the ending net PPE amount for themost recent historic year

only.

Calculatethehistoriccapex/salesmarginusingthefollowingformula:

Capexmargin%=Capex/Sales

IfyouhavenotyetdonethedepreciationforecastthenbuildtheforecastusingaformulatolinktheassumptiontoprioryearnetPPE:

DepreciationexpensetoprioryearnetPPE%=Currentyeardepreciationexpense/PrioryearendingnetPPE

Note that if youonlyhave twoyearsofhistoricbalancesheetnumbersthen you will only be able to calculate this ratio for the most recenthistoricyear.

PPEhistoricratiosexample

Forecastassumptions

CapexInvestmentincapexisakeycontributortogrowthinacompanysomakesurethatyourcapextosalesforecastassumptionsfitwithyourforecastsales growth assumptions. In addition, your capex to depreciation ratioshouldbeabove1ifthecompanyiscontinuingtogrow.

Assumingthatsalesgrowthistaperingdowntoastablelong-termrate,itiscommontokeepthecapextosales%forecastassumptionflat,basedonthelatesthistoricalyearratio.

If you will be adding further components to the model, such as adiscountedcashflowvaluation,youmightneedtoreturnandadjustthisassumptionatalaterstage.

DepreciationItisnormaltokeepthedepreciationtoprioryearnetPPE%assumptionat thesame levelas the latesthistoricalyearas it ishard tomakeanyassumptions about how asset lives or depreciation policies in thecompanymightchangeinthefuture.

PPEforecastassumptionsexample

ForecastnetPPE

BuildtheforecastnetPPEBASEtableasfollows:

B BeginningnetPPEamount(linktotheendingamountlastyear)

A AdditionstonetPPE–capitalexpenditure(forecastcapextosales%*forecastsales)

S SubtractionsfromnetPPE–depreciationexpense(depreciationexpensetoprioryearnetPPE%*PrioryearnetPPEorlinktothedepreciationforecastintheincomestatementifthishasalreadybeenbuilt)

E EndingnetPPEamount(calculateasbeginningnetPPE+capex-depreciation)

Finally, link theendingnetPPEamounts to thebalance sheet and thedepreciationamountstotheincomestatementifdepreciationhadnotyetbeenforecast.

ForecastnetPPEexample

GOODWILL

Goodwillonlyappearsinconsolidatedfinancialstatementsandindicatesthat the company has made acquisitions of subsidiaries in the past,paying higher than fair value of accounting net assets acquired. It is a

typeofintangibleasset.Underaccountingrules,itissubjecttoanannualimpairment test to check for any falls in value which must then bereflectedviaanimpairmentcharge.

HowtoforecastIt is impossible to predict the result of future impairment tests or theamountofgoodwillonfutureacquisitionsandsogoodwillisnormallykeptflatandlinkedtothelatesthistoricyearamount.

It is good practice in modelling to include assumptions for forecastgoodwill so that the goodwill forecasts link to assumptions rather thandirectlytoprioryearbalances.

Goodwillforecastexample

INTANGIBLEASSETS

Intangibleassetsother thangoodwill could includebrands,patentsandlicences. If these assets have definite lives the company will calculateandexpenseamortisationannuallyagainstthem.

HowtoforecastIt is usual to simply subtract the incomestatementamortisation chargefromthebalancesheetintangibleassetsamounteachyear.

Alternatively, an intangible assets BASE table can be constructed andadditionstointangiblesaswellasamortisation,forecastedandadjustedfor.

HistoricratiosTypicallynonecalculated.

ForecastassumptionsNone as the forecast intangibles amounts depend on forecastamortisation,alreadyprojectedintheincomestatement.

ForecastintangibleassetsBuild the forecast using a formula to subtract this year’s amortisationexpensefromlastyear’sintangibleassetbalance.

Forecastintangibleassets=Prioryearintangibleassets–Currentyearamortisationexpense

Forecastintangibleassetsexample

PENSIONASSETSANDLIABILITIES

If thecompanyhasdefinedbenefitpensionschemes, itmightwellhavevariousasset and liability balances relating topensionson thebalancesheet.Thesearealmost impossible topredict for futureyearsandsoacommontreatmentinmodelsistokeepflatbasedonlatesthistoricyear.Alternatively calculate pension asset or liability to salesmargin% andforecastthemarginflatbasedonlatesthistoricyear.

LONG-TERMDEFERREDTAXASSETSANDLIABILITIES

Deferredtaxassetsandliabilitiesareaparticulartypeofprepaymentandaccrualrelatingtotax.

HowtoforecastDeferred tax assets and liabilities are often forecast using marginassumptions based on deferred tax assets (liabilities) sales oralternativelydeferredtaxassets(liabilities)EBIT.

HistoricratiosCalculatethehistoricdeferredtaxmarginusingthefollowingformulas:

Deferredtaxassetssales%=DeferredtaxassetsSales

Deferredtaxliabilitiessales%=DeferredtaxliabilitiesSales

ForecastassumptionsTypically assume the projected ratios will remain flat as per the latesthistoricyear.

Forecastlong-termdeferredtaxassetsandliabilities

Forecastdeferredtaxassets=Deferredtaxassets/sales%*Sales

Forecast deferred tax liabilities =Deferred tax liabilities / sales% *Sales

OTHERNON-CURRENTASSETS/LIABILITIES

Othernon-currentassetsmight includeitemslike long-termreceivables.Other non-current liabilities might include items like provisions. Acommon treatment for operating non-current assets/liabilities is toforecast thenon-currentassets/liabilitiestosalesmargin%andtokeepthismarginflatbasedonlatesthistoricyear.

Othernon-currentassets/liabilitieshistoricratios

Othernon-currentassets/liabilitiesforecastamounts

SHORT-TERMANDLONG-TERMDEBT

Short-termdebtrepresentsdebtpayablewithinoneyear,whilstlong-termdebtrepresentsdebtpayableafteroneyear.Debtismodelledindetailonaseparatedebtcalculationssheetordebtschedule.

Fornow,keepbothshort-termandlong-termdebtforecastsflatasper the latest historic year balance sheet andmake sure that youcolourthefontorshadethecellsincoloursothatyoudonotforgettogobackandupdatethenumberslater.

Thedebtschedulewillbecompletedonlyafter thecash flowstatementhasbeenbuiltandcashlinkedintothebalancesheet.

Shortandlong-termdebtinitialforecastexample

SHAREHOLDERS’EQUITY

Theshareholders’equity in themodelbalancesheet isoftenpresentedas a single line item. This is in contrast to shareholders’ equity in thereportedbalancesheetwhichcanbesplitintoseveraldifferentlineitemssuch as share capital, share premium (APIC), retained earnings, othercomprehensiveincomeandtreasurystock.

Inthemodelthebreakdownofshareholders’equityisusuallypresentedonaseparatecalculationsheetintheformofaBASEtable.

HowtoforecastTheBASEtableisbuiltontheshareholders’equitycalculationsheetasfollows:

B Beginningshareholders’equityamount(aspertheendingamountlastyear)

A Additionstoshareholders’equity–netincome

A/S

A/SAdditionsto/subtractionsfromshareholders’equity–sharesissuedorrepurchased

S Subtractionsfromshareholders’equity–dividendspaid

E Endingshareholders’equityamount(calculateasbeginningshareholders’equity+netincome+sharesissued/(sharesrepurchased)–dividendspaid)

The BASE table is a ‘corkscrew’ table as for net PPE – last year’sending shareholders’ equity is the same number as this year’sbeginningshareholders’equity.

HistoricratiosDonotattempttobuildtheBASEtableforthehistoricyears.

This method of modelling shareholders’ equity does not allow forchangeswhicharenotcapturedby theBASE tableabove.Thismeansthat thehistoricBASE table isunlikely tosumtonumberswhichmatchthehistoricbalancesheetnumbers.

Theonlynumber in thehistoricyearshouldbe themost recentendingshareholders’ equity amount, so link the ending shareholders’ equityamountforthemostrecenthistoricyeartothebalancesheet.

Shareholders’equityhistoricBASEtableexample

ForecastassumptionsForecastnetincomeislinkedfromtheforecastincomestatement.

Forecastdividendsarelinkedfromtheforecastincomestatement.

Share issuance/(repurchases) are forecast at zerounless the companyhasdiscloseddetails of its intention to issueorbuy-back shares in thefuture,inwhichcaseusethenumbersgiven.

Shareholders’ equity forecast assumption example

Forecastshareholders’equity

Buildtheforecastshareholders’equityBASEtableasfollows:

BBeginningshareholders’equityamount(linktotheendingamountlastyear)

AAdditionstoshareholders’equity–linktoreportednetincomeontheincomestatement

A/SAdditionsto/subtractionsfromshareholders’equity–linktosharesissuedorrepurchasedforecastassumption

SSubtractionsfromshareholders’equity–linktodividendspaidintheforecastincomestatement

EEndingshareholders’equityamount(calculateasbeginningshareholders’equity+netincome+sharesissued/(sharesrepurchased)–dividendspaid)

Forecastshareholders’equityBASEtableexample

Finally,linktheendingshareholders’equityamountsfromthecalculationsheettothebalancesheet.

CHECKTHEBALANCESHEETBALANCE…

Copy right the formula for thebalance checkand youwill see that thebalancesheetdoesnotyetbalance!

Thisisbecausewehavecompletedalllineitemsexceptforcash.

Sonowweneedtobuildtheforecastcashflowstatement…

Balancecheckexample

Step5.CashflowstatementforecastThecashflowstatementsheetinourmodelisemptyatthisstage.Wehavenotinputthehistoriccashflowstatementsastheyarenotneeded.Wealreadyknowthehistoriccashbalancesfromthehistoricbalancesheets.

To obtain our forecast cash flow statements, wemust build them fromscratch, using the forecast income statement, balance sheet andcalculationsasnecessary.

This requires a knowledge of accounting and a systematic, disciplinedapproach.

Errorsinmodelsareoftenintroducedatthisstage,aspeopletryandtakeshortcuts toavoidhaving tobuild thecash flowstatement fromscratch.Onecommonriskyapproach is tosimplycopy the labels fromthecashflow statement in the company’s Annual Report, or from anothercompleted forecast model. The time taken to then find all the errorsintroduced,more thanoutweighs the time ‘saved’ throughtakingariskyshortcutsuchasthis.

Onceyouhavethe‘answer’tocashamountattheendoftheyear,youcan’plug’thisnumberintothebalancesheettomakeitbalance.

Ifyoubuild thecashflowstatement fromscratch,youare far lesslikelytointroduceerrorsandfarmorelikelytohaveamodelwhichwill‘balance’firsttime!

The cash flow statement: accounting The cash flow statementexplainsthemovementinbalancesheetcashandcashequivalentsfrom

oneyeartothenext.

In thecash flowstatement, thenetcash flow for theyear, reflects thesumofmovementsinallbalancesheetitemsfromlastyeartothisyearexceptforcashitself.

Thisgivesus thestartingpoint forunderstandinghowtobuild thecashflowstatement.Weneedtoreflectthemovementineverybalancesheetlineexceptforexcesscash,somewherewithinthecashflowstatement.

Thecashflowstatementgroupscashflowsintothreesections:

OperatingcashflowsThissectiontypicallybeginswithnetincomefromtheincomestatementandthenshowsthecashimpactofthechangeinbothcurrentandnon-current operating assets and liabilities from the balance sheet, line byline.Operatingassetandliabilitiesrelatetothedaytodayactivitiesofthecompany and include items such as accounts receivable, inventory,accounts payable, deferred taxes and pension. In addition, non-cashexpensessuchasdepreciationandamortisationareaddedback tonetincomeintheoperatingcashflowsection.

Thismethodofstartingwithaprofitnumberandreversingoutnon-cashchangestoworkbackwardstoacashnumber,isknownastheindirectmethod.

Operatingcashflowsexample(accountingversion)

Netincome X

Depreciation&amortisation X

(Increase)/decreaseinaccountsreceivable (X)/X

(Increase)/decreaseininventory (X)/X

Increase/(decrease)inaccountspayable X/(X)

(Increase)/decreaseinlong-termdeferredtaxassets (X)/X

(Increase)/decreaseinlong-termdeferredtaxassets (X)/X

(Increase)/decreaseinpensionassets (X)/X

Increase/(decrease)inlong-termdeferredtaxliabilities X/(X)

Increase/(decrease)inpensionliabilities X/(X)

Increase/(decrease)in/operatingprovisions X/(X)

Operatingcashflows X

The alternativemethod to calculate operating cash flowswould be thedirectmethod. This would involve listing out all direct operating cashflows,suchascashsalesmade,cashreceiptsfromaccountsreceivable,cash payments for inventory, and cash paid to accounts payable. Thismethodisverydifficultforsomeoneoutsidethecompanytoapply.

Remember in the model, the short-term operating items are oftensummarized inoperatingworkingcapital,andsoonly thechange innettotaloperatingworkingcapitalisshowninthemodelcashflowstatement,alongside the other adjustments to net income such as the addition ofdepreciationandamortisation,andmovementsinotheroperatingassetsand liabilities. This makes it simpler than the cash flow statementpresentedintheAnnualReport.

Operatingcashflowsexample(modellingversion)

Netincome X

Depreciation&amortisation X

(Increase)/decreaseinoperatingworkingcapital (X)/X

(Increase)/decreaselong-termdeferredtaxassets/pensionassets

(X)/X

(Increase)/decreaseinlong-termdeferredtaxassets (X)/X

(Increase)/decreaseinpensionassets (X)/X

Increase/(decrease)inlong-termdeferredtaxliabilities X/(X)

Increase/(decrease)inlong-termdeferredtaxliabilities X/(X)

Increase/(decrease)inpensionliabilities X/(X)

Increase/(decrease)in/operatingprovisions X/(X)

Operatingcashflows X

InvestingcashflowsThissectionof thecashflowstatementshowsthecashimpactof itemswhich relate to thecompany’s investmentssuchascapitalexpenditure,sales and purchases of PPE and sales and purchases of investmentssuchassharesordebtinothercompanies.

Investingcashflowsexample

Capitalexpenditures(tangibleassets) (X)

Capitalexpenditures(intangibleassets) (X)

Cashproceedsfromsaleofinvestments X

CashproceedsfromsaleofPPE X

InvestingcashflowsX/(X)

FinancingcashflowsThissectionof thecashflowstatementshowsthecashimpactof itemswhichrelatetothedebtandequityfinancingofthecompanyandincludes

itemssuchasdividendspaid,andchangesindebtandequityissued.

Be aware that depending on which set of accounting rules are beingfollowed, some items have alternative classifications. Interest expenseand income and dividends received can sometimes be allocated tooperatingcashflows,eventhoughthisseemscounter-intuitive.

Thethreecashflowsubtotalssumtonetcashflow for theyear,whichcouldbeaninfloworanoutflowofcash.

Thisshouldmatchthemovementinbalancesheetcash,sotocheck,thefinal section of the cash flow statement is a reconciliation to balancesheetcash.

Financingcashflowsexample

Increase/(decrease)indebt X/(X)

Shareissuance/(repurchase) X/(X)

Dividendspaid (X)

InvestingcashflowsX/(X)

ReconciliationtobalancesheetcashThiscanbeassimpleasasingleline:

Currentyearbalancesheetcash=Prioryearbalancesheetcash+CurrentyearnetcashflowWhenyoulinkthecurrentyearbalancesheetcashintothebalancesheet,thebalancesheetshouldbalance!

Cashimpactofbalancesheetchanges

The balance sheet is made up of assets, liabilities and equity. Thisrelationshipissummarizedbytheaccountingequation:

Assets=Liabilities+Equity

Anotherwayofexpressingtheaccountingequationis:

Usesoffunds(assets)=Sourcesoffunds(liabilities+equity)

Thinkaboutit:anassetisauseofcompanyfundsasthecompanymustfindthemoneytobuyit,whereasliabilitiesorequityissuedareasourceoffundswhich,whencreated,providesmoneytothecompany.

Ifauseoffundsincreasesfromlastyeartothisyear,thecashimpactisnegative, so youmust subtract the increase in the balance sheetassetintheappropriatesectionofthecashflowstatement.Theoppositeistrueiftheuseoffundsdecreases.

E.g.ifthecompany’saccountsreceivablesincreasefromlastyeartothisyear,thisisbadnewsforthecompany’scashposition–theyaremakingsalesoncreditratherthansalesforcashandmustwaitforthemoneytobepaidtothem.

If a source of funds increases from last year to this year, the cashimpactispositive,soyoumustaddtheincrease inthebalancesheetliability or equity item in the appropriate section of the cash flowstatement.Theoppositeistrueifthesourceoffundsdecreases.

E.g. if the company’s debt increases from last year to this year, this isgoodnews for thecompany’scashposition–by takingout loans, theyareobtainingcash.

We can summarize these rules as follows, and use them to help usconstructourforecastcashflowstatementinthemodel:

Subtracttheincreaseinusesoffundsinthecashflowstatement

Addthedecreaseinusesoffundsinthecashflowstatement

Addtheincreaseinsourcesoffundsinthecashflowstatement

Subtract the decrease in sources of funds in the cash flowstatement

Buildingthemodelcashflowstatement

Itisessentialtofollowamethodicalprocesswhenbuildingthecashflowstatement and thereforehighly recommended that you followthestepsbelow,inorder,toachieveamodelwhichbalancesonthefirsttry.

Gotothebalancesheetandhideallforecastcolumnsexceptforthefirstforecastyear.Itiseasiesttoinitiallybuildandcheckthecashflowstatementforthefirstyearonly.Onceeverythinghasbeenchecked,thentheformulascanbecopiedrightacrossremainingforecastyears.

Nowin thebalancesheet, inasparecolumnto therightor leftofeachitem, type the labels ‘operating’, ‘investing’ or ‘financing’ depending onwherethemovementneedstogointhecashflowstatement.Dothisforallitemsexceptcashandanysubtotals.

Foranyitemwhichispartofoperatingworkingcapital,type‘OWC’nexttoitinstead.

ForanyitemwhichhasbeenforecastedthroughuseofaBASEtable,itistheadditionsandsubtractionsintheBASEtablewhichwillneedtobelabelled instead.Type‘BASE’next tothe iteminthebalancesheetandthengototheBASEtablecalculationontherelevantsheetinthemodel,and type ‘operating’, ‘investing’ or ‘financing’ next to eachaddition/subtractionthereinstead.

Bytheendofthisprocesseverylineiteminyourbalancesheetwillhavealabel,exceptforcashandanysubtotalsortotals.

Nowgotoyourcashflowstatementandtypeinthesectionheadings,‘Operatingcashflows’,‘Investingcashflows’and‘Financingcashflows’,leavingafewblankrowsbetweeneach.Youcaneasilyinsertordeleteextrarowsifyouneedtolater.

Gobacktothebalancesheetandlookforthefirstlabelleditem.Typethecorrespondinglabelintothecashflowstatementandbuildtheformulatoadd or decrease themovement using the rulesmentioned above. It isextremelyimportantthatthemovementisshowncorrectlyaspositiveornegative.Oncethemovementintheitemhasbeenincludedinthecashflow statement, you can delete the workings label for it in the balancesheet. Then go to the next labelled balance sheet itemand repeat thesameprocess.

Theorderofitemsinthemodelcashflowstatementdoesnotmatteraslongasyoustartwithnet incomeandmakesure that theother itemsareallcorrectlyallocatedbetweenoperating,investingandfinancing.

Whenyoureachthefirstbalancesheetitemmarkedas‘OWC’,gototheoperatingworkingcapital calculationsheetand reflect themovement innetoperatingworkingcapitalintheoperatingcashflowsectionofthecashflowstatement.

Remember thatnetoperatingworkingcapital isauseof funds,soanyincreasemustbedeductedinthecashflowstatement.

Asyouaredealingwithalloperatingworkingcapital items inonestep,youcannowdeleteall‘OWC’labelsfromthebalancesheet.

When you reach a balance sheet item which has been forecastedthroughuseof aBASE table, thengo to the relevant calculation sheetandtransfereachitemmarkedintheBASEtabletotheappropriateplaceinthecashflowstatement,deletingthelabelsasyoufinishdealingwitheachone.Nowgobacktothebalancesheetitemanddeletethe‘BASE’labelfortheitemyouhavejustdealtwith.

Check:bytheendofthisprocessalloftheworkingslabelswhichyouhad typed into the balance sheet and calculations sheets, shouldhavebeendeletedandyourcashflowstatementiscompleteexceptforsubtotalsandthecashreconciliation.

FinalstepsSubtotaleachsectionof thecash flowstatement:operatingcash flows,investingcashflowsandfinancingcashflows.

Buildanoveralltotalfor‘netcashflow’whichsumsthethreesubtotals.

Netcashflowexample

Addthelabelsforthereconciliationtobalancesheetcashbelowthenetcashflowsubtotal,asbelow.

Nowbuildthecashreconciliation.Inthelatesthistoricyearcolumnonly,linkendingcashbalancebacktothehistoricyearbalancesheet.Donotcopythisformularight.

Build thecashreconciliationformula in theforecastyears, linkingto therelevantcellsonthecashflowstatementonly:

Endingexcesscash=Beginningexcesscash+Netcashflow

If everything is correct, the ending excess cash in the reconciliationshouldmatchthebalancesheetcheck!

Iftheendingexcesscashfromthereconciliationinthecashflowstatementdoesnotmatchthebalancesheetcheckinthebalancesheet,youmustfindandcorrecttheerror(s)beforecontinuing.

Forhelponfindingandfixingerrors,seetheHowtoCheckaModel–StepbyStepchapter.

Thefinalstepistocopyrightalloftheformulasfromtheyear1cashflow

statementacrosstheremainingforecastyears.Ifyouhidcolumnsonthebalance sheet and calculation sheet while you were building the cashflow statement, you must now unhide them and delete any remainingworkingslabels.

If everything is correct, the ending excess cash in the reconciliationshouldmatchthebalancesheetcheckineveryyear.

Iftheendingexcesscashfromthereconciliationinthecashflowstatementdoesnotmatchthebalancesheetcheckineveryyearofthebalancesheet,youmustfindandcorrecttheerror(s)beforecontinuing.

Forhelponfindingandfixingerrors,seetheHowtoCheckaModel–StepbyStepchapter.

Step6.Plugthebalancesheetcash

BalancethebalancesheetGotothebalancesheetandlinktheforecastcashtotheendingcashonthecashflowstatement.

Thebalancesheetshouldnowbalance!

Ifyourbalancesheetdoesnotbalanceyoumustfindtheerror(s)andcorrectthembeforeproceedingasitwillbehardertofindtheerrorsifyoucontinuetoaddmoreformulas.

Forhelp,seetheHowtoCheckaModel–StepbyStepchapter.

If youhadpreviouslyhighlighted theexcesscash lineasa reminder tocompleteitlater,youcannowremovethehighlight.

Step7.DetaileddebtscheduleWhilstbuildingthebalancesheetwekeptbothshort-termandlong-termdebt flat, linking to the latest historic year amounts. Now it is time tocalculatethedebtforecastnumbersindetail.Thisisdoneonaseparatedebtschedule.

There are many different ways to set up the debt schedule and thefollowing is just one example. In practice you may see alternativepresentations.

Inmorecomplexmodelsyoumayseea ‘debtwaterfall’whichassumesthat any surplus cash available aftermandatory repayments on debt isused tomake extra repayments, allowing the company to pay its debtdownfasterthananymandatoryrepaymentsaloneimply.

Debtscheduleexample

DEBTASSUMPTIONS

Beforeforecastingdebtyouwillneedtobuildtheassumptionssection.

INTEREST

Forecastinterestratesareneededinordertocalculateinterestexpenseandinterestincome.

Howtoforecast

Interest expense and income are forecast using assumptions forprojectedinterestrates.

InterestexpenseYouwill need separate interest rates for long-termdebt and short-termdebt. In a more complex model, long-term debt may be split into itscomponentsand if so, a separate interest ratewill beneeded for eachdebtissue.

InterestincomeYou will need an interest rate assumption for the interest earned onsurpluscash.

HistoricratiosThereisnoneedtoshowanythinginthehistoricassumptionsforinterestrates,astheyarenotneededinthemodel.

ForecastassumptionsChecktheNotes to theFinancialStatement in theAnnualReport.Mostcompanieshavedetaileddebtdisclosurenotesandyoushouldbeabletofinddetailedinformationoninterestrates.

Finding an appropriate interest income rate on excess cash can betrickierandifnothinghasbeendisclosedbythecompany,thenyoumayhavetoestimateityourself.

Remembertocommentthecellstoexplainwheretheassumptionshavecomefrom.

BUILDINGTHEDEBTSCHEDULE

CashavailabletorepaydebtThefirstsectionofworkingsonthedebtschedulecalculatestheamountofcashwhich isavailable tomakedebt repayments.Thiscalculation isdonefortheforecastyearsonly.

Typeinthelabelsandlinkforecastformulasasfollows:

Beginningexcesscashbalance

Linktoprioryearbalancesheetexcesscash

Cashavailabletorepaydebt

Linktothesumofcashflowstatementitems:netcashfromoperatingactivities,netcashfrominvestingactivities,andindividuallysummedcashflowsfromfinancingactivities,allexceptforissuance/repaymentofdebt

Totalcashavailable =beginningexcesscashbalance+cashavailabletorepaydebt

Cashavailabletorepaydebtexample(firstyearonly)

Once you have checked the formulas, copy right across all forecastyears.

Long-termdebt

Long-termdebtisrepayableinmorethanoneyear.Inasimplemodelitcan be forecast as a single line item, in a more complex model theindividual long term debt issues can be separated out and modelledindividuallyonthedebtschedule.

HowtoforecastLong-termdebtisgenerallymodelledusingasimplifiedBASEtable.Debtissuancemaynotbemodelledbecauseit israrethatwewouldknowinadvancehowmuchlong-termdebtwillbeissuedbythecompanyinthefuture,orwhen.

Set up the BASE table for long-term debt on the debt schedule asfollows:

B Beginninglong-termdebt

S Mandatoryrepayments

E Endinglong-termdebt

HistoricratiosDonotattempttobuildtheBASEtableforthehistoricyears.

Theonlynumberinthehistoricyearsshouldbethemostrecentendinglong-termdebt, so link theending long-termdebtamount for themostrecenthistoricyearfromthebalancesheet.

ForecastassumptionsMost companies have detailed debt disclosures in the Notes to theFinancial Statement. From here you should be able to identify whenindividualdebtissueswillmature.Typetherelevantrepaymentamountsinto each forecast year as hard number inputs, using SUM formulaswhere there aremultiple repayments in any one year and commentingthecellswithnotesonpagereferencestotheAnnualReportwheretheinformationcanbetracedto.

Forecastlong-termdebt

B Beginninglong-termdebt–linktoendingamountinprioryear

S Mandatoryrepayments–linktotheforecastassumption,butreversethesigntoshowasnegative

E Endinglong-termdebt–sumtheabove

Checkthatendinglong-termdebtisnotnegativeinanyyear–ifitis,youmighthaveoverstatedtherepayments.

Interestonlong-termdebtAsyoubuildthedebtsheet,youmustnowtype the labels for ‘interest rate’ and ‘interest expense’ below endinglong-termdebt, but leave the forecast amounts empty for now. Interestmustbethefinalstepinthemodel.

Remainingcashavailableforshort-termdebtThisnumberisneededtoforecastchangesinshort-termdebt/revolverandshouldbeshownasaseparatelineitembelowthelong-termdebtinterest.

Remainingcashavailableforshort-termdebt =

Totalcashavailable-MandatoryRepayments

Short-termdebt/revolver

Thesimplestwaytomodelshort-termdebt istoassumethatanyshort-termdebt in thehistoricyear is fullypaidoff in the first forecastyearofthemodel, and that going forwards the company uses a ‘revolver’, anoverdraftfacility,asandwhenneeded.

In more complex models, you can also include the short-term part oflong-termdebtrepaymentsinthebalancesheetshort-termdebt.

HowtoforecastShort-term debt, like long-term debt is generally modelled using asimplified BASE table but unlike long-term debt, we allow for bothissuanceandrepayment.

Set up the BASE table for long-term debt on the debt schedule asfollows:

B Beginningshort-termdebt

A/SIssuance/(repayment)ofshort-termdebt

E Endingshort-termdebt

HistoricratiosDonotattempttobuild theBASEtablefor thehistoricyears.Therearenohistoricratios.

Theonlynumberinthehistoricyearsshouldbethemostrecentendingshort-termdebt,solinktheendingshort-termdebtamountforthemostrecenthistoricyeartothebalancesheet.

ForecastassumptionsThere are no forecast assumptions for the issuance/repayment of theshort-termdebt.Thechangeisdrivenbytheremainingcashavailablefor short-term debt i.e. cash available after repayments on long-termdebt,asfollows:

• Ifcashavailableforshort-termdebtispositive,paydowntheshort-termdebt.Thiswillavoidthecompanyincurringunnecessaryinterestcosts.

• Ifcashavailableforshort-termdebtisnegative,thismeansthatthecompanydidnotgenerateenoughcashtocoveritsmandatorypaymentsonlong-termdebtandsoshort-termdebtmustbeincreasedtocovertheshortfall.

Forecastshort-termdebt

B Beginningshort-termdebt–linktoendingamountinprioryear

A/SUsetheMINfunctioninExceltobuildaformulawhichwillpayofftheopeningshort-termdebtifcashavailableispositiveorissuemoreshort-termdebtifcashavailableisnegative,presentinganyrepaymentasnegative:

=-MIN(cashavailableforshort-termdebt,openingrevolver)

UseoftheMINfunctionhereensuresthattherepaymentwillnotexceedtheshort-termdebtopeningbalance.

E Endingshort-termdebt–sumtheabove

Forecastshort-termdebtexample

Interestonshort-termdebt

Asyoucontinuetobuildthedebtsheet,youmustnowtypethelabelsfor

‘interest rate’ and ‘interest expense’ below ending short-term debt, butleavetheforecastamountsemptyfornow.Interestmustbethefinalstepinthemodel.

RemainingcashavailableafterrevolverYou can add this as an optional extra line after the revolver interestexpense. It shows where the company could potentially increase itsmandatorydebtrepaymentsandbyhowmuch.

Remainingcashavailableafterrevolver=Remainingcashavailableafterlong-termdebt+

Issuance/(repayment)ofshort-termdebt

Remainingcashafterrevolverexample

Debtsummaryandinterestsummary

Summaries toshowtotaldebtand total interestexpense, ifyouwish toshowit,cannowbeincluded.

InterestincomeTypelabelsforcash,interestrateandinterestincome.

Linkthelatesthistoricyearcashandforecastcashamountstothemodelbalancesheetcashandcashequivalents.

Leave interest rate and interest income empty for now, as for interestexpenseabove.

Netinterestcalculation

Add the labels for the net interest calculation and complete it onceinteresthasbeencalculatedlater.

Thedebtsheetisnowcompleteexceptforinterest.

Debtsheetsummaryexample

Step8.Linkshort-termandlong-termdebtintothebalancesheet

Link the ending long-termdebt amount from thedebt schedule tothebalancesheetandcheckthatthebalancesheetstillbalances!

Whilst the long-term debt numbers have changed, the cash flowstatement is automatically picking up the revised numbers and so themodelshouldnotgooutofbalance.

Ifyourbalancesheetdoesnotbalanceyoumustfindtheerror(s)andcorrectthembeforeproceedingasitwillbehardertofindtheerrorsifyoucontinuetoaddmoreformulas.

Forhelp,seetheHowtoCheckaModelchapter.

Nowlinktheshort-termdebtamountfromthedebtscheduletothebalancesheetandagaincheckthatthebalancesheetstillbalances!

As for the long-term debt, whilst the short-term debt numbers havechanged,thecashflowstatementisautomaticallypickinguptherevisednumbersandsothemodelshouldnotgooutofbalance.

Ifyourbalancesheetdoesnotbalanceyoumustfindtheerror(s)andcorrectthembeforeproceedingasitwillbehardertofindtheerrorsifyoucontinuetoaddmoreformulas.

Forhelp,seetheHowtoCheckaModelchapter.

Remembertochangethefont/fillcoloursbacktomatchtherestof

thebalancesheet ifyouhadpreviouslychangedthemtomarkthecells.

Balancesheetwithupdateddebtforecastnumbersexample

Step9.Calculateinterestandlinkintotheincomestatement

Themodel isalmostcomplete.Thefinalstep is tocalculatethe interestexpenseand incomeon thedebtscheduleand then link through to therestofthemodel.

Dependingonhowinterestiscalculated,thiscanintroducecircularityintothe model, and this is the reason why the model is built with both‘iterations’ in Excel options switched off and circular switches in themodelsettooffi.e.zero.

Tounderstandcircularityanditerations,seetheCircularityinForecastModelschapter.

Waystocalculateinterestincomeandexpense

Interestonopeningdebtorcashbalance

Thisisthesimplestwaytocalculateinterest.Linktheinterestrateintotheforecastassumption,andthencalculatetheinterestbasedonlastyear’sendingdebtorcashbalance.Thisisanon-circularmethodforcalculating interest, but it can result in inaccuracies in the interestcalculationsiftherewerelargechangesindebtorcashbalancesovertheyear.

Interestonaveragedebtorcashbalance

Thisisamoreaccuratemethodforcalculatinginterestasitallowsforchanges in the debt or cash balance over each year, however, itmakesthemodelcircularonceitislinkedintotheincomestatement.

CalculateinterestexpenseGoto thedebtscheduleandforeachof long-termandshort-termdebt,link forecast interest rates for interest expense to the forecast interestrateassumptions.

Now build the formulas for the interest expense forecasts using theAVERAGEfunctioninExcel:Interestexpense=forecastinterestrate%*AVERAGE(beginningdebt:endingdebt)

Alternatively, if youwant to avoid circularity in themodel and accept aless accurate interest estimate, then calculate interest on openingbalancesas follows: Interestexpense=Forecast interestrate%*Prioryearendingdebt

Interestexpenseexample

Interestexpensesummary(optional)

Youcannowlinktheforecastinterestexpenseamountsintotheinterestexpensesummarysection,ifyouhaveone,andsumthem.

CalculateinterestincomeGo to the interest income section on the debt schedule and link theforecast interest rates for interest income to the forecast interest rateassumptions.

Now build the formulas for the interest income forecasts using theAVERAGEfunctioninExcel:Interestincome=Forecastinterestrate%*AVERAGE(beginning cash : ending cash) Alternatively, if you want toavoid circularity in the model and accept a less accurate interestestimate,thencalculateinterestonopeningbalancesasfollows:Interestincome=Forecastinterestrate%*Prioryearendingcash

Interestincomeexample

NetinterestcalculationInterest income and total interest expense can be linked into the netinterestcalculationsection.Makesurethatyoushowinterestexpenseasnegativeand thensumto interest incometogive thenet interestwhichwillfeedintotheincomestatement,ifyouareshowingnetinterestratherthanseparatelinesforinterestincomeandinterestexpense.

Netinterestcalculationexample

Thenext step is to linknet interest from thedebt schedule to theincomestatementusinganIFstatementwhichlinkstothemodel’scircularityswitch.

Using the circularity switch makes it easier to control circularity in themodelandtofixpotentialproblems.

To understand more about circularity switches, see theCircularity inForecastModelschapter.

Theswitchisusuallyabinaryswitchwhichissetto ‘0’whenoffand‘1’when on. It is located on themodel control sheet and is often given arange-namesuchas‘circ’.

Go to the model income statement and use an IF statement in theinterest line to link to the net interest result on the debt schedule: Netinterestexpense=IF(circ=1,netinterestexpense,0)

As thecircularity switch is currently set tooff, youshouldseea rowofzeros for the forecast net interest expense in the income statementRemembertochangethefont/fillcoloursbacktomatchtherestoftheincomestatement ifyouhadpreviouslychangedthemtomarkthecells.

Linknetinterestexpenseintotheincomestatementexample

Step10.TurnoniterationsinExcelandthemodel’scircularityswitch

TurnoniterationsinExceloptions.ThiswillenableExceltosolvefortheinterestwhichhasnowbeenlinkedintothemodelincomestatement.

Next, turn on themodel’s circularity switch, for exampleby typinga ‘1’intotherelevantcellonthecontrolsheet.

The interest numberswill impact the income statement, balance sheet,cash flow statement and debt schedule, however, despitemany of thenumberschanging,thebalancesheetshouldstillbalance!

Ifyourbalancesheetdoesnotbalanceyoumustfindtheerror(s)and

correctthembeforeproceedingasitwillbehardertofindtheerrorsifyoucontinuetoaddmoreformulas.

Forhelp,seetheHowtoCheckaModelchapter.

Thisisonlythefirstcheckofseveral.Abalancingbalancesheetdoesnotguaranteeanerror-freemodel.

NowgototheHowtoCheckaModelchapterandcompletetheadditionalmodelchecksbeforeproceeding.

Financialstatementscompletewithinterestanditerationsexample

Incomestatement

Balancesheet

Cashflowstatement

Step11.Completeratios,analysisandfinalchecks

Mostmodelswillhaveaseparateworksheetcontainingmargin,growth,profitabilityandcreditratios.

Theseratiosarecalculatedforbothhistoricyearsandforecastyearsandare an important part of the model. By examining the trend in theseratios,andcheckingabsolutevaluestomakesurethey‘fit’withwhatyouknowaboutthecompany,theyprovideafinalgoodcheckthatthemodelanditsassumptionsarereliable.

Use the example below to build a ratios analysis sheet for yourmodelandcheckthatthenumbersandtrendsmakesense.

NowgototheHowtoCheckaModelchapterandcompletethefinalmodelchecks.

Ratioanalysisexample

CIRCULARITYINFORECASTMODELSWhatiscircularity?

A circular formula is a one which includes itself in the answer. Somecircularformulasaremathematicallyimpossibletosolveandareusuallytheresultofaccidentalerrors,whilstothers,suchasinterestinamodel,canbesolvedthroughaniterativeprocessandaredeliberate.

Circularformulasare

EitherUnsolvablee.g.accidentalerrorsinmodelformula(s)

OrSolvablee.g.interestinamodel(seelater)

UnsolvablecircularformulasBelowisanexampleofacircularitywhichisimpossibletosolve:

A B C

1 5

2 5

2 5

3 =sum(A1:A3)

4

Tryitinablankworksheet.

IterationsoffSCREENSHOTOFEXCELITERATIONSTICKBOXOFF

If the iterations option in Excel is switched off, you will see threeindicatorsofacircular issue in theworksheetassoonasyou typeacircularformulain.

Circularformulaindicator1If this is the first timeacircular formulahasbeenput into themodel,adialogueboxwillappearwithawarningasbelow:

This is to alert the user and give them a chance to stop and checkwhetheritwasadeliberatecircularityornot.

Selectthe‘OK’buttontocontinue.

Circularformulaindicator2The status bar will now contain an extra message, ‘CIRCULARREFERENCES’ next to ‘READY’ at the bottom left-hand corner of thespreadsheet.

Anexampleisbelow:

If a cell reference is given in themessage, it will be one of the cellsincluded in the circularity, but not necessarily the one where thecircularityoriginated.

Ifnocell reference isgiven, thencheckeverysheet in themodeluntilyoufindonewithacellreference,sothatyoucanstarttofindthesourceofthecircularity,ifyouneedto.

If there isno cell reference on any sheet, and only the ‘CIRCULARREFERENCES’ message is given, then check to see if you have anyotherExcelworkbooksopen. It is likely that thecircularity is inanotherworkbook. This iswhy you should not have any otherExcel files openwhile you are building your model (see the 10 golden rules ofmodellingchapter)

Circularformulaindicator3Theanswertothecircularformulawillbegivenas‘0’.

If youseeunexpectedzeros in the forecastnumbers foramodel, thencheck the toolbar immediately to see if there is circularity in themodelwhichcouldbecausingthis.

EXCELThe iterations option in Excel and circularity Werecommended that at the start of the model building process that youturnedofftheiterationsoptioninExcel.

The iterations option in ExcelExcel is a tool that can be used to solvecircular formulas, if theyaremathematicallypossible tosolve.Excelwilltryandcalculatetheanswertotheformularepeatedly,alwaysusingtheanswer from the most recent attempt, until it has reached a pre-setnumber of tries (default is 100) or until the difference between the twomost recent answers is so small as to be negligible (default is 0.001)Whathappenswheniterationsareturnedon?

If you turn iterations on, Excel will try and calculate the answer to allcircular formulas in theworkbook. If theyaremathematically impossibleto solve, the answers will be meaningless. Every time the worksheetrecalculates,theanswerstotheseformulaswillgetbiggerandbiggerasExcelrepeatedlytriedtosolvethem.

Meaninglesssolutionstounsolvablecircularformulaswheniterationsareturnedon

Itisimportanttonotethatifiterationsareturnedonandyouwriteacircularformula,Excelwillnotgiveanyofthecircularformulaindicatorsabove.

You may notice that the message ‘CALCULATE’ appears next to‘READY’onthetoolbar.The‘CALCULATE’messagetellsyouthatExcelisattemptingtosolveanycircularformulasthroughiteration.

Asyoubuildamodel,youneedtoknowassoonasyouwriteacircularformulasothatyoucancorrectit,ifitwasanaccidentalerror.Circularitydestabilizesmodels,allowingothererrorstospreadrapidlythroughthemodelandcausea‘blow-up’

Model blow up caused by circular error example

ThisiswhyworkingwithExceliterationsturnedoffuntiltheveryendisagoldenruleofmodelling.

SolvablecircularformulasBothinterestexpenseandinterestincomeinamodelarecirculariftheyarecalculatedonaveragerevolverorcashbalances.

Averageofthisyear/lastyearRevolvergivesInterestexpensegivesNetincomegivesNetcashflowgivesthisyearRevolver.

....soyouareusingthisyear’srevolveramounttofindthisyear’srevolveramount!

Averageofthisyear/lastyearExcessCashgivesInterestincomegivesNetincomegivesNetcashflowgivesthisyearExcessCash.

....so you are using this year’s cash amount to find this year’s cashamount!

This iswhyusingbeginningdebtorcashbalances tocalculate interestexpenseorincomeavoidscircularity,butitisalessaccuratemethod,asdescribedearlier.

When iterations inExcelare turnedon,Excelcansolve for the interestexpense or income number by calculating them and feeding themthroughthemodelagainandagain.

As above, Excel will try and calculate the answer to the formularepeatedly,alwaysusingtheanswerfromthemostrecentattempt,untilithas reached a pre-set number of tries (default is 100) or until thedifference between the two most recent answers is so small as to benegligible(defaultis0.001).

CIRCULARSWITCHESINMODELSIt iscommon to findacircularitycontrol switchon thecoversheetofamodel.

Theswitchesareusuallysimplebinaryswitches,intowhichyoutypea‘1’ifyouwantcircularity‘on’ora‘0’ifyouwantcircularity‘off’.

The switch is only active if it is linked to the interest in the incomestatementviaanIFstatementanditiscommontorangenameswitches(for more on range naming, see the Excel essentials for modellerschapter).

Netinterestexpense=IF(circ=1,netinterestexpense,0)

Ifyouareusingacircularswitchinyourmodel,onceyouhavewrittentheaboveformulaintoyourincomestatement,asafinalstepyoumustturnonExceliterationsandalsoturnontheswitch.

Whyuseacircularswitch?

Circularity destabilizes models, making them easy to ‘blow up’. If youwanttochange,updatethemodelorevenfindandcorrecterrors,itisfareasiertodothiswhenthereisnocircularitypresent.Ifyoumakeanerrorinamodelwhich iscircular,usingCTRL+Z toundoyouractions,canoftenincreasethenumberoferrors!

YoumustremovecircularityfromthemodelandmakesurethatiterationsinEExcelareturnedoffbeforemakingchanges.SeetheHowtocheckamodelchapterformore.

If there is no circular switch, to remove circularity, youmust delete theforecast interest formulas from the income statement and then re-typetheminonceyouhavefinishedmakingchecksorchanges.Thisistime-consumingandincreasestheriskofaddingerrorsintothemodel.

Withacircularswitch,toremovecircularityyoucanjustsettheswitchto‘0’toremovetheinterestandthenbackto‘1’toreinstateitwhenyouareready.Thisiseasierandmoreprofessional.

Fordetailsofhowtocreateandrangenameacircularityswitch,seetheExcelEssentialsforModellerschapter.

Therelationshipbetweencircularityanditerations

Circularityistheexistenceofacircularformulainamodel.

Iterationsareatoolforsolvingsolvablecircularformulas.

Thefollowingsummaryassumesthatinterestintheincomestatementislinkedviaacircularitycontrolswitchandthatthemodeliserror-free:

Circularity IterationsWhatyouseeinthemodel

Nocircularformula(circularityswitch‘off’)

Iterationsturnedon

Mostlikelynothing!Whenyouwriteacircularformulayouwillnotgetanywarnings.Thisisdangerousandnotrecommended.

Nocircularformula(circularityswitch‘off’)

Iterationsturnedoff

Whenyouwriteacircularformulayouwillseethethreeindicatorsofcircularityasmentionedearlier.Thisisidealwhenyouarebuildingthemodel.

Circularformulainmodel(circularityswitch‘on’)

Iterationsturnedoff

Statusbar:CIRCULARREFERENCES–withorwithoutacellreference.Formulasthroughoutthemodelwilloftengiveincorrectresultsuntiliterationsareturnedon.

Circularformulainmodel(circularityswitch‘on’)

Iterationsturnedon

Statusbar:CALCULATE.Thisisidealwhenthemodelisfinished.

HOWTOCHECKAMODEL–STEPBYSTEP

Checkingyourmodelforerrorsatregularintervalsisanessentialpartofthemodellingprocess.Errorsarebestfoundandcorrectedasearlyoninthemodelbuildingaspossible.Themorecomplexamodel,theharderitistofinderrors.

Remember to close all other Excel files whilst you are checking themodel, so that youdonotaccidentally linkcellsand toavoidcircularityerrormessagesinyourmodelthatrelatetoanotheropenworkbook.

1. IstheiterationsoptioninExcelticked?

Yes

Turn it off. You need to see Excel’s circularity indicators whilstcheckingandcorrectingyourmodel (see theCircularity in forecastmodelschapterforwhy).Goto2.

No

Goto2.

2. Checkthestatusbaroneverysheetforcircularitymessages.Arethereany?

Yes

This means that you have circular formulas in the model. Did youdeliberatelymodelinterestascircular?

Yes

Youneedtoremovetheinterestwhileyoucheckthemodel.

Makesurethattheincomestatementinterestcontainsonlyzeros,bysetting the circularity control switch to off if youareusingone, or isblank,bytemporarilydeletingtheforecastformulasforinterest.

Goto3.

No

Goto5.

3. Check the status bar on every sheet for circularity messagesagain.Arethereany?

Yes

This means you have additional unwanted circular formulas in themodelwhichyouneedremove.Goto4.

NoGoto5.

4. Canyou seeExcel auditing arrowson someof thenumbers intheworksheet?

Yes

Startwithanynumbermarkedwitharrowsandcheckthattheformulais correct. Then trace the precedents for that cell and go to theprecedentcell(s).Check the formula in theprecedentcell andagaintracetheprecedents.Keepgoinguntilyouhavefoundandcorrectedtheerror.Ifyoumisstheerroryouwilleventuallyfindyourselfbackatthecellyoustartedwith–it’sacircularreference!Whentheadditionalcircularityisremoved,thearrowswilldisappearandthestatusbarwillclear.Goto5.

No

Go to the FORMULAS tab on the ribbon. Then go to ERRORCHECKING.ThengotoCIRCULARREFERENCES.Youshouldseeadrop-downmenuwitha list of cell references.Theseare the cellswhichare included in theunwanted circularity.Check the formula ineach cell until you have found and corrected the error. When theadditional circularity is removed, the CIRCULAR REFERENCESoptionwillbegreyedoutandunavailabletoselect.Goto5.

5. Checkthebalancesheet.Doesitbalanceinallyears?

Yes

Thisisagoodsign,butdoesnotnecessarilymeanthatyourmodeliserror-free.Goto10.

No

Goto6.

6. Doesthebalancesheetbalanceinthefirstforecastyearbutnotinothers?

Yes

Thiscouldmeanthataformulawascorrectedinthefirstforecastyearbutnotcopiedrighttotheotherforecastyears.Copyrightallformulasinthefirstforecastyearacrossallotherforecastyearsoneverysheetinthemodel.Goto5.

No

Goto7.

7.Gotothefirstforecastyearandlookatthevalueinthebalancecheck.Isitthesamevalueinalloftheforecastbalancechecks?

Yes

Therecouldbeanerror in linkinganopeningbalance from theprioryear. Look for the value in the balance sheet and related calculationsheets for balance sheet items in the prior year and check that it hasbeen included in themodelcorrectly. Ifyoucorrectany formulas,makesurethatyoucopythecorrectionrightacrossallforecastyears.

No

Goto8.

8.Gotothefirstbalancecheckwhichisnotzeroandsearchforthisvaluethroughoutthemodelinthesameforecastyear.Canyoufindamatchtothebalancesheetcheck?

Yes

It is likely that this value has not been correctly included in allsubtotals and/or dependent formulas. Check the dependent cells. Ifyou find the error, make sure you copy the correction across allforecastyears.

No

Goto9.

9.Dividethebalancecheckbytwo.Searchforthisvaluethroughoutthemodel in thesame forecast year.Canyou findamatch to thebalancesheetcheck?

Yes

Most likely you have found this value (half of the original balance

sheetcheck)inthecashflowstatement.Youhavea‘sign’error–apositivevaluewasshownasnegativeorviceversa.Correctthesignandcopythecorrectionrightacrossallforecastyears.Goto5.

No

Youmayhavemorethanoneerror.Youwillneedtousetheultimatebalancesheetcheckingmethod.

UltimatebalancesheetcheckingmethodThis method is guaranteed to help you identify the reason why yourbalancesheetdoesnotbalanceandisespeciallyusefulwheretherearemultipleerrors,howeveritispotentiallytime-consumingandshouldonlybeusedwheretheotherchecksabovehavefailed.

a. Hideallforecastcolumnsonthebalancesheetexceptforthefirstone.

b. Inablankcolumntotherightofthevisibleforecastbalancesheet,foreachlineitemexcludingsubtotals,cashandrevolver,buildformulasthatcalculatethecashimpactofthemovementfromlastyeartothisyearasfollows:

Changeinasset =lastyearassetvalue-thisyearassetvalue

Changeinliabilityorequity

=thisyearliabilityorequityvalue-lastyearliabilityorequityvalue

c. Sumthecashimpactsofbalancesheetmovements

d. Checkthesumofcashimpactsofbalancesheetmovementsagainstthenetcashflowonthecashflowstatementforthe

sameyear.Wherethemodelbalancesheetisinbalance,theywillmatch.

Ultimatebalancesheetcheckexample

If your balance sheet does not balance, then these numbers will notmatchanditisyourjobtofindandcorrecttheerrorsuntiltheydo.

Each difference calculated can be found somewhere on the cash flowstatement, with the exception of some more complex items describedbelow.

Complexities in matching balance sheet differences to cashflows

BalancesheetitemsforecastedusingBASEtables

WherethebalancesheetitemwasforecastedthroughuseofaBASEtable, then it is the individual adjustments in the BASE table whichyouwillneedtomatchto thecashflowstatement.Forexample, thechangeinPPEwillbematchedtothesumofdepreciationandcapexinthecashflowstatement.

Operatingworkingcapital

Themovement inoperatingworkingcapital ispresentedasasinglevalueinthecashflowstatement.Thisshouldmatchtothesumoftheindividualcomponentsofoperatingworkingcapitalmovementsinthebalancesheet.

Try and match each difference from the balance sheet differencescolumnto thecash flowstatement for the first forecastyear.Whenyoufindamatch,changethefontcolourofthetwomatchednumbersinbothbalancesheetworkingscolumnandthecashflowstatement.

Whenyoufindtwonumbersthatshouldmatchanddonot,youhavefoundanerror!Checktheformulasuntilyouhaveidentifiedthecauseoftheerrorandcorrectit.

Bytheendofthisprocessyourbalancesheetshouldbalance.

e. Nowchangeallfontcoloursbacktooriginalanddeletetheworkingscolumnonthebalancesheet.Unhidethepreviouslyhiddenforecastcolumnsandcopyrightallformulasfromthefirstforecastyearacrosstheremainderoftheforecast.Ifyoumadecorrectionsoferrorsonanyothersheet,makesurethatyoucopythoserightaswell.

Nowgoto5.

10.Yourbalancesheetbalancesbutthisdoesnotnecessarilymeanthatthemodel iserror-free.Someerrorsdonotcause thebalancesheet togooutofbalance.Nowitistimeforfinalchecks:

TRENDCHECKSStartwiththemainfinancialstatementsandscanthemlinebyline.Don’teven think about the formulas, for now just step back and look at thenumbers ‘big picture’. For every line in the balance sheet, including

subtotals,startatthetopandworkyourwaydown,lookingatthetrendsacrossthehistoricandforecastyears.

Arethereanynumbersthatlookodd?

Wrongsign?

Toobig/toosmall?

Increasingwhenshouldbedecreasing?

Decreasingwhenshouldbeincreasing?

Bigincreaseordecreasefromoneyeartoanother?

Anunexpected‘zero’?

This ‘reasonableness’ check on the numbers is an extremely powerfultoolandshouldnotbeunderestimated.90%ofallerrorsarepickedupjustbylookingatthenumbers.

Trendcheckexample

Questionstoask

Whyisn’tthereanysalesgrowthinforecastyear5?WhatiscausingtheunexpectedzerointheCOGSyear5forecast?Why is there suchabig jump inSG&Aexpense fromyear0 to year1forecast?Isamortisationsupposedtobethesameamountinallforecastyears?Isnetinterestexpensesupposedtobepositiveinforecastyear1?

Investigateanysuspiciousnumber/trend.

Ifyouhaveidentifiedanunusualnumberortrend,nowisthetimetodrill down into the detail. Start with the formula in the first forecastyearandusetheformulachecks,asbelow

FORMULACHECKS

Onceyouhavecompletedthetrendchecks.Itisstillworthcheckingtheformulas.Youcanhideallforecastyearsexceptforthefirstonesothatitiseasiertoperformthechecks.

Toseetheallof theformulasat thesametime:Go toFORMULAS,SHOWFORMULAS,orusethekeyboardshortcutCTRL+`.

Repeat when you want to hide the formulas The formulas for the firstforecastyearshouldallcontainreferencestoassumptionsinexactlythesame column throughout the model, unless they refer to a beginningbalance.Thismakesthecheckingeasy–ifyouarecheckingnumbersincolumnE,thenallofthecellreferencesshouldbetoEcellsorpossiblyDifreferringtoanopeningbalance

SHOWFORMULASexample

Tocheckcalculations:

F2Makestheformulavisibleinthecellandcoloursthecellreferencesintheformulaandputsmatchingcolouredboxesaroundtherelevantcellswheretheyareonthesamespreadsheet

F2tocheckaformulaexample

F2,F9

Showstheanswertothecalculationwithunlimiteddecimalplaces.Thiscanbeusefulifthemodelisonlydisplayingoneortwodecimals.

F2, F9 to check a formula example

F2,select

AnothervariationistouseF2,thenhighlightjustapartofaformulabeforetypingF9.Excelwillcalculatetheanswertojustthatpartoftheformula.Thisisusefulforcheckingcomplexformulas.

F2 to check a section of a formula example

Becareful–whenyouhavefinishedthecheck,youmusttypeESCAPE.IfyoutypeENTER,yourformulawillbeoverwrittenwithahardnumber.

Totraceformulaprecedents/dependents

CTRL+[ Highlightsallprecedentcellsonthesameworksheet.PressENTERtomovefromoneprecedenttoanotherinturn.PressF5,ENTERtoreturntoyourstartingcell.

Iftheformulacontainsareferencetoacellonanothersheet,youwillbetakentothiscellontheothersheetonlyifitisthefirstcellreferenceintheformula.YoucannotthenuseENTERtomovetothenextprecedent,onlyF5,ENTERtoreturntoyourstartingcell.

CTRL+] Highlightsalldependentcellsonthesameworksheet.PressENTERtomovefromonedependenttoanotherinturn.PressF5,ENTERtoreturntoyourstartingcell.

Iftheformulacontainsareferencetoacellonanothersheet,youwillbetakentothiscellontheothersheetonlyifitthefirstcellreferenceintheformula.YoucannotthenuseENTERtomovetothenextdependent,onlyF5,ENTERtoreturntoyourstartingcell.

Traceprecedentsexample

FormulaauditingusingtheFORMULAStabontheExcelribbonTheFORMULAAUDITINGmenuontheFORMULAStabontheExcelribboncanalsobeusedforchecking.

Formulaauditingexample

AutomaticerrorcheckinginExcel

WithinExceloptionsfromtheFILEtab,theFORMULASsectioncontainstickboxesthatallowyoutoselectindividualerrorcheckingrulesandturnautomaticerrorcheckingonoroff.

Whenerrorcheckingisturnedon,Excelwillalertyoutoarulebreachbyputtingagreenflagonthetopright-handcornerofacell.

Theserulesarenotdesignedspecificallyforfinancialmodellingandareoftennothelpful. It is recommended thatyou turntheautomaticerrorcheckingoffandrelyonyourowndetailedchecks.

Checkcomplexformulasbychangingassumptions

If youhave complex formulas in themodel, especially thosewhich areshowingzeroastheanswer,itcanbeagoodideatochecktheformulasareworking correctly by changing a linked assumption to force a non-zeroanswer.Makethelinkedassumptionextremelybig,sothatyoucanseetheimpactontheanswerandcheckit.Onceyouhavechangedtheassumptionandcheckedthattheformulaisworking,makesurethatyoureturnittoitsoriginalvalue.

Even if a formula is not returning zero, this type of ‘stress test’ whichdeliberately changes assumptions to look at the impact on the formulasolutionscanbeuseful tomakesure thatcomplex formulashavebeenbuiltcorrectly.

11.Now turnExcel iterations on and link interest into the incomestatementusingacircularswitchifyouhaveone.Doesthebalancesheetstillbalance?

Yes

Yourmodeliscomplete!

No

Youknowwithcertaintythatanyerrorislinkedtotheinterest.Hideallforecastcolumnsexceptforyear1andtracealldependentslinkedtointerest to ensure that it has been linked correctly into the model.Whenyou find theerror(s)makesureyouunhide the forecastyearsandcopyrightthecorrection(s)acrossallofthem.Yourmodelshouldnowbalanceandiscomplete.

EXCELESSENTIALSFORMODELLERSThis chapter is vital reading if you are new tomodelling in Excel. It isdividedintothefollowingsections:Usethekeyboardtospeedupyourmodelling

This section gives showsa range of usefulways that you can use thekeyboardratherthanthemouse.

• HowtousethekeyboardtoaccesstheExcelribbonandcommands• Howtousethequickaccesstoolbar• HowtocustomizetheExcelribbon• Keyboardshortcutswhichavoidusingtheribbon• Fastercellselectionusingthekeyboard• Fasterformulawritingusingthekeyboard• TheAutoSumshortcut:ALT=

Usecommentstomakeyourmodeluser-friendlyThissectionshowsyou how to add, delete and edit cell comments to provide moreinformation to the user of themodel such aswhere you have sourcednumbersfrom.

Createandrangenameacircularityswitch

Thissectionshowsyouhowtocreateacircularswitchinyourmodelandhowtorangenameitsothatitiseasilyidentifiableandeasytoreferenceinformulas.

StylesandformattinginExcelIfyourcompanydoesnothaveastandardmodeltemplateand/orsetofstyles, then you might need to create your own. This section is an

introductiononhowtoefficientlyformatandcreatenewstyles.

ThischapterassumesExcelversions2007andlater.

The practice file for this chapter can be found on our BG Portal (BGLibrary):

UsetheKeyboardtoSpeedupyourModelling

Using the keyboard shortcuts in Excel will allow you to work far moreefficientlythanusingthemouse.Ideallyyouwouldnotusethemouseatall,butsometimesacombinationofbothkeyboardandmouseiseasierandfaster.

HowtousethekeyboardtoaccesstheExcelribbonandcommandsAnexampleoftheExcelribbonisshownbelow:

Toactivate thebuttons, you canuse yourmouse to clickon them,butkeyboardshortcutsareaquickeralternative.

Tosee thekeyboardshortcuts for themainmenu itemspress theALTkey, and the shortcuts will appear on the ribbon as below:

Once the shortcuts appear you can then type the relevant letter ornumbertoaccessataboracommandbutton.

PressALT,followedbyH(notcasesensitive)toaccesstheHOMEmenu

andseealltheHOMEshortcuts.YoudonothavetokeepALThelddownasyoupressH.

Example:howtochangethefontstyleusingthekeyboardonly

PressALTfollowedbyH,followedbyFFtochangethefonts.Pressthedownarrowtoopenthefontselectionbox,thenusetheup/downarrows to select a font, and then press ENTER. Alternatively starttypingthenameofyourrequiredfontintothefontselectionboxonceyouhaveaccessedit,untilitisselectedinthedrop-downlistandthenpressENTER.

Inthebottomrighthandcornerofsomesections,thereisasmallarrow.Clickingthearrowortypingtherelevantletterswillrevealfurtheroptions.

Whenyouarefinished,pressESCtoexittheribbon.

HowtousethequickaccesstoolbarYoucanadda‘quickaccesstoolbar’toExcelversions2007andlater.Thisisaminitoolbarthatyoucanplaceabovetheribbononthetoplefthandcornerofthescreen.Youcanaddpopularcommandssuchas‘increasedecimal’or‘fontcolour’andthenaccessthemusingtheALTkeyfollowedbytherelevantnumber.

GotoEXCELOPTIONS,QUICKACCESSTOOLBARtoaddorremovebuttonstoorfromthequickaccesstoolbar.

Alternatively,rightclickonyourchosenbuttonfromtheribbonandselectAddtoQuickAccessToolbar.

HowtocustomizetheExcelribbon

InExcelversions2010onwardsyoucancustomizetheexistingribbonbyadding or deleting commands, and/or add a personalized tab to theribboncontainingthebuttonsyouusethemost.GotoFILE,OPTIONS,CUSTOMIZERIBBONtoseethis.

Keyboardshortcutswhichavoidusingtheribbon

There are several keyboard shortcuts which can access commandsdirectlywithoutneedingtogoviatheribbonatall.TheseshortcutsoftenrequireyoutoholddowntheCTRLkeywhilstsimultaneouslytypingtherelevant letter or number key. Some of the function keys also accessusefulshortcuts.

The tables below contain a long list of Excel shortcuts, with the mostimportantonesforyoutostartusingnowhighlighted.

ExcelShortcuts

Fastercellselectionusingthekeyboard

Peoplenewtomodellingoftenusethemousetoselectcells,howeveritismorepreciseandfastertousethekeyboardasexplainedbelow.

To select asingle cell, use the arrow keys to pick the cell which youwant.Thearrowkeyswillmoveyourselectionbyoneroworonecolumnatatime.

To selectmultiple cells, hold down the SHIFT key while you use thearrowkeystoselecttheextracellsyouwishtoselect.

Example:selectarangeofcells

ToselectcellsC2 toG3,selectcellC2asyourstartingcell.ThenholddowntheSHIFTkeyandpresstherightarrowkeytoreachG2.KeeptheSHIFTkeydepressedwhileyouthenpressthedownarrowtoG3.

Iftherearenumbersortextinthecellswhichyouareselecting,thereisafasterway toselectblocksofcells.Holddown theCTRLkeyat thesame time as SHIFT before you simultaneously press the arrows toselectallthewaythroughtothelastpopulatedcellintherange.

To select the populated cellsC2 toG3, select cellC2 as your startingcell. Then hold down theCTRL key and theSHIFT key and press therightarrowkeytoreachG2.KeeptheboththeCTRLkeyandtheSHIFTkeydepressedwhileyouthenpressthedownarrowtoG3.

Formoretipsoncellselection,usethe‘Shortcutsforselecting’ inthetableabove.

Fasterformulawritingusingthekeyboard

EnteringaFormula

AllformulasinExcelstartwith‘’

First type ‘=’ into thecellwhereyouwant towrite the formulaand thenenter your formula. Your formula will replicate how you would write aformulaoutsideofExcel,withbracketsusedasnecessary.

Forexample=(A2+B2)*C2 toadd thecontentsofA2andB2and thenmultiplytheresultbyC2.

FormulaOperators:

+Plus

-Minus

/Divide

*Multiply

() Brackets

^Hat:raisesthevaluetothepowerofanothervalue(3^2=9)

ToenteraformulaintoExcelExcelusingthekeyboardonly:1. =2. Usearrowstoselectthefirstcell3. Enterchosenoperator,asabove4. Usearrowstoselectsecondcell5. Repeatsteps3and4untilyouhave

finishedtheformula6. ENTER

Example:buildtheformulasusingthekeyboard

Howdoyoubuildthegrossprofitformulasusingonlythekeyboard?

TIP:Build the formula for the firstyearand thencopy to therightusingtheshortcutCTRL+R

1. SelectcellC62. Type=3. PresstheUpArrowtwicetoreachcellC44. Type–5. PresstheUpArrowoncetoreachcellC56. PressENTER7. HolddownSHIFTandpresstherightarrowfourtimestoreach

G68. HolddownCTRLandpressR

Cross-sheetFormulasWhenbuildingmodels,formulasoftenreferencecells in different worksheets. These formulas are called ‘cross-sheet’formulas. Again, they can be written quickly and efficiently using thekeyboardonly.

Towriteacrosssheetformulausingthekeyboardonly:1. Type=2. UseArrowstoselectcellonsameworksheetorifthefirstcell

referenceisonanothersheet,useCTRLPgUpandCTRLPgDntotogglebetweenworksheetsandthenselectthecellyouwant.

3. Enteroperator4. Usearrows(andworksheettoggle)toselectsecondcell5. Repeatsteps3and4untilyouhavefinishedformula6. ENTER

Asyouarebuildingtheformula,youwillseethatwhenyougotoanothersheet,thenbothtabswillbehighlighted–thetabofthesheetwhereyoustartedtheformulaandthetabofthesheetyouhavecrossedoverto.

Whenyoureferenceacell inanotherworksheetwithin inaformula,thesheet name with an exclamation mark will appear in the formula. Forexamplewhen referencingcellD5 in the IncomeStatementWorksheet,thereferencewillappear:=IncomeStatement!D5

Example:buildcross-sheetformulasusingthekeyboard

Howdoyoubuild the forecast sales formulason the incomestatementwhenthesalesgrowth%assumptionsareonaseparatesheet?

TIP:Build the formula for the firstyearand thencopy to therightusingtheshortcutCTRL+R

1. SelectD4onIncomeStatement2. Type=3. Type(1+4. CTRLPgUptotoggletoAssumptionsSheet.5. UsearrowstoselectD4onAssumptionssheet6. Type)*

7. CTRLPgDntotoggletoIncomestatementsheet8. PressLeftArrowoncetoselectC4onIncomeStatementsheet9. ENTER

10. HolddownSHIFTandpresstherightarrowfourtimestoreachH4

11. HolddownCTRLandpressR

TheAutoSumfunctionshortcut–ALT=

OneofmostfrequentlyusedformulasinmodelsistheSUMfunctionforaddingnumberstogether.

1. =SUM(2. Selectcellstoaddup3. Type)4. ENTER

It can bewritten and builtmanually, as above (using the keyboard forspeedandnot themouse),however it ismuch faster touse theALT=shortcutwhichisthekeyboardequivalenttothe∑commandbutton.

Onsomekeyboards the= isat the topofanumberkey, inwhichcasetheshortcutisALTSHIFT=

HowtoAutoSumusingtheALT=shortcutPlaceyourcellselectionatthebottomofthecolumnofnumbersortotherightofarowofnumberswhichyouwishtosum,thenholddownALTandpress=.

Excelwillshowtheproposedrangeofnumberstosumwithananimatedborder(alsoknownas‘marchingants’)andshowtheproposedformulain

theformulabar:

Ifyouarehappywiththeproposedrange,thenpressENTER.

Example:sumthetotalcurrentassetsinthebalancesheetextract

Toaddnumbersmanuallyusingthekeyboard:

1. SelectC92. Type=SUM(3. PresstheUpArrowoncetoselectC84. HolddownSHIFTandpresstheuparrowthreetimestoextend

thecellselectiontoC55. Type)6. PressENTER7. HolddownSHIFTandpresstheRightArrowfourtimestoreach

G98. HolddownCTRLandpressR

ToaddnumbersusingtheautosumshortcutALT=:

1. SelectC92. HolddownALTandthenpress=3. PresstheuparrowoncetoselectC84. HolddownSHIFTandpresstheuparrowthreetimestoextend

thecellselectiontoC55. Type)6. PressENTER7. HolddownSHIFTandpresstherightarrowfourtimestoreach

G98. HolddownCTRLandpressR

HowtoAutoSumarangeofcells

TheAutoSumshortcutcanalsobeusedtosumarangeofcells.

One way of doing this is to select the entire cell range including theempty row where you want the totals:

NowholddownALTandpress=

Allofthetotalswillbeenteredautomatically.

How to AutoSum non-contiguous cells AutoSum can still be usedwherecellsarenotdirectlynexttoeachother(non-contiguous).

UseALT=towritetheformula,andthenusearrowkeyscombinedwithSHIFT if necessary to change the proposed selection.Manually type acommaintheformulatobeabletoselectanotherrange.PressENTERwhenfinished.

Example:sumthetotalassetsinthebalancesheetextractusingAutoSum

1. SelectC152. HolddownALTandthenpress=3. HolddownCTRLandpresstheuparrowtwicetoreachC94. Type,5. HolddownCTRLandpresstheuparrowoncetoreachC136. PressENTER7. HolddownSHIFTandpresstherightarrowfourtimestoreach

G158. HolddownCTRLandpressR

Usecommentstomakeyourmodeluser-friendlyItisextremelyimportantthatyourmodelisuser-friendly.Thisisnotonlythebenefitofpeoplewhowerenotinvolvedinbuildingthemodelandwanttouseit,butalsofortheoriginalmodelbuilderswhocanforgetovertimewhytheydidthingsinacertainwayorwhereexactlythehistoricalnumberscamefrom.

InExcel, thecomments featureallows theuser toaddacomment toacell into which important information can be typed. You will see a redtriangleonthetopright-handcornerofcellswithcomments.These‘flag’swillnotshowwhenthemodelisprinted.

Commentshidden

Commentsopen

Commentscanbeadded,removedorviewedusingtheREVIEWtab.

YoucanusetheALTkeyfollowedbytherelevantletter(s) toaccessthecommandsasforanyothertabintheribbon,howeverothershortcutsexist:

Toaddacomment HolddownSHIFTandpressF2WriteyourtextinthecommentboxandthenpressESCtwice

Toviewasinglecomment Selectthecellcontainingthecomment,holddownSHIFTandpressF2OrRollyourmouseoverthecellcontainingthecomment

Toeditacomment Selectthecellcontainingthecomment,holddownSHIFTandpressF2

Toresize/relocateacomment

Withthecommentopenforeditingusethemousetoclickanddragtheentirecomment(torelocate)ortodragtheborders(toresize).PressESCtwicewhenfinished

Todeleteacomment Openthecommentforediting,pressESConeandthenpressdeleteOrGototheHOMEtab,selectCLEARandthenselectCLEARCOMMENTS

Todeleteallcommentsonasheet

Selectthewholesheet,thengototheHOMEtab,selectCLEARandthenselectCLEARCOMMENTS

Tochangetheusernamethatappearsatthetopofeachcomment

GotoFILE,OPTIONS,GENERALandchangetheusername

Toprintthesheetwithcommentsvisible

GotoPAGESETUP,SHEET,andthenselectyourchosenpreferenceintheCOMMENTSdrop-downbox

CopyComments SelectthecellwiththecommenttocopyandholdCTRL+C.GotothecellwherethecommentneedstobecopiedtoandholdALT+E+S+C

Example:addcommentstothehistoricyearnumbers

1. SelectC72. HolddownSHIFTandthenpressF23. Typethecommentintothecommentbox4. PressESCtwice5. SelectC86. HolddownSHIFTandthenpressF27. Typethecommentintothecommentbox8. PressESCtwice

AlternativelythecommentscanbecopiedfromcellsC3andC4andthenpasteddirectlyintotheopencommentsboxes.

CreateandrangenameacircularswitchSwitchesareoftenusedtocontrolcircularityinmodels.FormoreinformationonhowtheyareusedseetheCircularityinForecastModelschapter.

Theswitchisacellintowhichtheusertypicallytypeseithera1ora0tosettheswitchtoonoroff.

The cell is often range named so that the cell name can be useddirectly in formulas. A range named cell is automatically an absolutecell reference which means that when any formulas referencing thenamed cell are copied to other locations in the worksheet, the namedrangedoesnotchange.

HowtonameacellNamed cells and ranges can be added, amended or deleted via theNAMEMANAGERontheFORMULAStabontheExcelribbon.

1. Selectthecelltoname2. OntheribbongotoFORMULAS,NAMEMANAGER3. SelectNew,the‘NEWNAME’dialogueboxwillappear4. Type

thecellnameintothe‘Name’box.PressOK

Or use the keyboard shortcut CTRL + F3 to access the NAMEMANAGER:1. Selectthecelltoname

2. HolddownCTRLandpressF33. SelectNew,the‘NEWNAME’dialogueboxwillappear4. Typethecell

nameintothe‘Name’box.PressOK.

Orselectthecellandthentypethenewnameintothecellnamebox:

1. Selectthecelltoname.2. Usethemousetoselecttheexistingnameinthecellnamebox.3. Typethenewcellnameintothe‘Name’box.PressOK.

Example:formatandrangenameacircularityswitch

1. SelectcellC3andapplychosenformattingfromtheHOMEtabontheribbone.g.fullborder,bluefontcolour,numberformattoonedecimalplace2. HolddownCTRLandpressF3

3. SelectNewtoaccesstheNEWNAMEdialoguebox4. Typeyourchosennamee.g.Circintothe‘Name’boxandpressOK

5. Writeaformula‘=Circ’intoanothercellonthesheetandcheckthatitcorrectlyreferencesthecontentsofcellC3

Finally,notethatcellnamingwithinmodelsshouldbelimitedasitmakesproofingofthemodelchallenging

Using dialogue boxeswithout themouse:When dialogue boxesareactivated,youcanusethekeyboardratherthanthemouse.

Tab Changestheactivebutton,movingforwards

SHIFT+Tab Changestheactivebutton,movingforwards

ALT+Underlinedletter

Activatesabuttondirectly

ALT+Downarrow Opensadrop-downbox

Up/Downarrows Usetochooseoptionfromdrop-downbox

CTRL+PgUp Movesuptoanothertabinthedialoguebox

CTRL+PgDn Movesdowntoanothertabinthedialoguebox

EnterCompletestheactionandclosestheboxwhen‘OK’buttonis`activated

StylesandformattinginExcelItisimportantthatmodelshaveconsistentstylesandformatting.Thiswillmakethemodeleasiertofollowandensurethatitlooksprofessional.

Many companies have their own preferences for styles and formatswhich have already been set up. If this is the case for your company,mostlikelyyouwillbeabletoopenupastandardtemplatefileandworkfromthis.

Ifyoudonothaveastandardtemplate,thenit isusefultoknowhowtocreateandamendneworexistingstylesandformatsinExcelyourself.

WhatisthedifferencebetweenstylesandformatsinExcel?

Formattingisusedtosettheappearanceofacellanditscontents.Thiscoversaspectslikewhetherthecellhasaborderornot,whetherithasafillcolourornot,whichfontstyleisused,andhowmanydecimalplacesarenumberspresentedto.

A style is a collection of formatting settings which have been groupedtogetherandgivenaname,suchas‘Normal’.

Excelhaspre-definedstylesalreadyavailablebutthesecanbemodified.

Toseetheavailablestylesinaworkbook,gotoHOME,STYLES,CELLSTYLES

HowdoIformatacellinExcel?

Select the cell(s) you wish to format and then use the options on the

HOMEtabtoformat.AlternativelyusethekeyboardshortcutCTRL+1toaccesstheFormatCellsdialogueboxandselectoptionsfromthere.

The‘Normal’styleinExcel

ThedefaultcellstyleinExcelis‘Normal’.Toseewhatthismeans,selectCELLSTYLESasabove,rightclickon‘Normal’andselectModify.

Tochangeanyof the formattingoptionswithin theNormal style, selectFormat. If you only want to define one aspect of a style, such as theappearance of numbers, then untick the other options before selectingFormat.

ThiswillopentheFormatCellsdialoguebox,howeveranychangesyoumakeherewillnowapplytoallcellsofthisstyleintheworkbookandnotjustonecell.

Make your desired changesandpressOK.Youwill return to theStyledialogue box and will see the summary of changes made. Press OKagain.

HowdoIcreateanewstyleinExcel?

Theeasiestwaytocreateacompletelynewstyleistoformatasinglecellandthen,withthiscellselected,gotoHOME,STYLES,CELLSTYLES,NewCell Style.Youwill see the formatting included in your new style.TypethenewnameintotheStylenameboxandpressOK.

NowgoandcheckCELLSTYLESagainandyoushouldseeyournewstyleisavailable.

HowdoIapplyastyleinExcel?

To apply a style, select the cell(s) youwish to apply the style to, thenselect your chosen style from theoptions inCELLSTYLES,andpressENTER.

Example:createanewstylecalled‘Modelinput’inExcel

1. Selectanycell(hereC11)andtypeanynumbersothatyoucanseetheexistingformatting2. UsetheHOMEtabontheribbonto

formatthecell:in‘font’selectfontname‘Arial’,fontsize‘10’,fillcolourgreyandfontcolourblue3. GotoCELLSTYLESintheHOMEtab,andselectNewcellstyle4. Untick(deselect)number,alignment,borderandprotectionandchecktheotherformatoptionsarecorrect5. Type‘Modelinput’intothestylenamebox

6. PressENTER

Alternatively,forstep2,usetheCTRL+1shortcuttoaccesstheFormatCells dialogue box andmake formatting selections there before settingthenewcellstyle.

HowdoIdeleteastyleinExcel?

YoucannotdeletethedefaultNormalstyleinExcel,butotherstylescanbe deleted by right-clicking the style name inCELLSTYLES and thenselectingdelete.

Do Ihave to re-createmynewcell stylesevery time Iopenanewworkbook?

No, there are various solutions to this problem. The easiest is to copystyles fromaworkbookcontainingallof thestylesyouwant intoanewworkbook.

1. Makesurethatboththefilecontainingthestylesyouwantandyournewfilewhereyouwouldalsoliketohavethesamestylesareopen2. GotoyournewfileandHOME,CELLSTYLES,Mergestyles3. IntheMergestylesfrom:dialoguebox,selectthefilecontainingthestylesandpressOK

4. Youwillbeaskedifyouwanttomergestyleswhichhavethesamenames.Ifyouselectyes,thismeansthatifyoumodifiedanydefaultstylesintheoldfile,thenthosemodifiedstyleswillreplacetheequivalentstylesinyournewfile5. GotoCELLSTYLESandcheckthatthenewstylesareavailable