公司金融_Financial Modeling

Class07FinancialModelingDanielSungyeonKimPekingUniversityHSBCBusinessSchool1QuickreviewoflastWednesdayFinancingmodelingModelIIModelIII2Apro-formaisapredictionofhowthefirm’sfinancialstatements--balancesheet,incomestatement–willlookinfutureyearsPro-formasarenoteasytoprepare;wehavetoensurethatallfinancialstatementsareconsistentassets&liabilitiesmustmatchconnectionsbetweenbalancesheet&incomestatementmustbemaintainedAnyassumptionaboutsalesandcostsaffectsnotonlytheincomestatement,butalsothebalancesheetBalancesheetsandincomestatementsareinternallyconnected3EXAMPLE4IncomeStatementSalesCostDepreciationInterestExpenseInterestIncomeTaxesNetIncomeDividendRetainedEarningsBalanceSheetCashandMarketableSecuritiesDebtOtherCurrentAssetsEquityGrossFixedAssetsStocksAccumulatedDepreciationAccumulatedRetainedEarningsNetFixedAssetsTotalAssetsTotalLiabilitiesandEquity5TheProblem:UnliketheIncomeStatement,theBalanceSheethastobalance!Assets=Debt+EquityWhenvaluingafirm,wecareaboutitsfutureoperationsThereforewewanttousefirmanalysistoprojecttheseoperating-relateditemsindependentlyE.G.,sales,COGS,AR,APSowecannotchangeoperating-relateditemstomakethebalancesheetbalanceandtomaintaintherelationshipb/tISandBSUsefinancingsideitemstobalance!6PLUGWeconsideracandidatefortheplugfromthreefinancingactivitiesCash/marketablesecuritiesDebtEquityAnycombinationofthesethreecanalsobeplugsThechoiceofaplugreflectsafirm’sfinancialpolicy.Fromafinancialviewpoint,thistellsyouhowthefirmfinancesitsshortfallsandwhatitdoeswithitsexcesscash7AfirmdecidesthefollowingIfthereiscashshortfall(FCF0),thefirmwillborrownewdebttofundfreecashflowIfthereisexcesscashif(FCF0),thefirmwillpaydownexistingdebtNonewstockiseverissuedFirm’scashbalanceneverchangesThendebtisthe“plug”8CurrentAssetsCurrentLiabilitiesFixedAssetsDEBTStockRetainedEarningsLinkB/SwithI/SandmakeBSbalanceNoclosed-formsolutionWehavetogoforanumericalsolutionEnablingnumericalsolutioninExcel:Iterationsinthepro-formastatementsInExcel2007clicktheMicrosoftOfficeButton/ExcelOptions/Formulas/Calculationoptionssection,selecttheEnableiterativecalculationcheckbox.9AssumesthatfirmsonlyusedebtfinancingThenwhydoweactuallyobservesomanyfirmsusingequityfinancing?ThisproblemisfixedinModel210Whatifthefirmhasatargetdebt-equityratiothatitwishestomeet?Afirmcannotissue/repaydebtandmaintaintheTargetDebtissues/repaymentmustbebalancedwithequityissues/repurchasesInsuchamodel,bothdebt&equityareplugsOurexample:firmattemptstoreduceitsdebt-equityratiofrom56%inYear0to40%inYear511TARGET_D_Edenotesthetargetdebt-equityratioofthefirm:Debt=TARGET_D_E*EquityEquity=Stock+RetainedEarningsStock=TotalAssets–CurrentLiabilities–Debt–RetainedEarning12ModeldoesnotincorporatecashexplicitlyThiscreatesaproblemwhensalesgrowthislowanddebtapproaches0RedoModel1withsalesgrowth=0Whathappenstodebtinthiscase?ThisproblemisfixedinModel313Bothmodels1and2assumethatfirmcontinuouslyissuesandrepaysdebtIsthatarealisticassumption?Whatifthereisnodebtlefttorepay?Whathappensifexistingdebtcannotberepaid?Soweneedamodelwherefirmcanaccumulatecash&marketablesecurities(andearninterestincomeonthem)14Incorporatescashintothepro-formas:Cashisanon-operatingasset,notrelatedtosalesSowelistitseparatelyfromoperatingCAAssumption:Firmcanissue(i.e.,increase)debtbutcannotrepay(i.e.,decrease)itinthetimehorizonbeingconsidered,i.e.,ifFCF0,firmissuesdebtordrawsdowncashIfFCF0,firm’scashincreasesSobothcashanddebtareplugs15BalanceSheetwithtwoPlugs:16CashandSecuritiesCurrentLiabilitiesCurrentAssetsDebtFixedAssetsStockRetainedEarningsRecallthatwhendebtwastheonlyplug,thendebtwasissuedtofinanceshortfallsandrepaidwhentherewasanexcessoffundsInModel3,wecanonlyraisedebt,wecannotrepayitButifdebtcannotdecrease,thedefinitionofdebtdependsonifthefirmisoperatingwithashortfalloranexcessSowehavetoconsiderexcessesandshortfallsseparatelyIfFCF0,DebtorCashistheplugDebt=OperatingAssets–CurrentLiabilities–Stock–RetainedEarningsCashandSecuritiesgoto0whendebtisissuedIfIfFCF0,CashistheplugDebt=Previousperiod’sdebtCashandSecurities=TotalLiabilitiesandEquity–CurrentAssets–FixedAssets17SupposeTotaloperatingassetst–CLt–EquitytDebtt-1i.e.,thereisneedtoraisemoredebtThen,Debtt=Totaloperatingassetst–CLt–EquitytElse,Debtt=Debtt-1Casht=TotalLiabilitiest+Equityt–totaloperatingassetst18ProjectingtherestofincomestatementNon-depreciationoperatingcosts:DistinguishbetweenfixedandvariablecostsProjectingtherestofbalancesheetUseefficiencyratiostoprojectNWCneedsCapitalexpenditure:Distinguishbetweenreplacementneedsandexpansionneeds19DepreciationisnotdirectlylinkedtosalesWewanttofocuson:Costofgoodssold(COGS)Sales,general&administrativeexpense(SG&A)WeprojectOperatingcostsOperatingcosts=COGS+SG&A-DepreciationCombiningCOGS/SG&Ahelpsremovedepreciation20ProjectingOperatingCostsFocusonCostofGoodsSold(COGS)andSelling,General,&AdministrativeExpenses(SG&A)Beforeprojectingoperatingcosts,weneedtoexcludedepreciationchargesDepreciationnotdirectlylinkedtosalesIfcompanie