搜索
TLFeBOOKBUILDINGFINANCIALMODELSAGuidetoCreatingandInterpretingFinancialStatementsJOHNS.TJIAMcGraw-HillNewYorkChicagoSanFranciscoLisbonLondonMadridMexicoCityMilanNewDelhiSanJuanSeoulSingaporeSydneyTorontoTLFeBOOKCopyright©2004byJohnS.Tjia..Allrightsreserved.ManufacturedintheUnitedStatesofAmerica.ExceptaspermittedundertheUnitedStatesCopyrightActof1976,nopartofthispublicationmaybereproducedordistributedinanyformorbyanymeans,orstoredinadatabaseorretrievalsystem,withoutthepriorwrittenpermissionofthepublisher.0-07-144282-0ThematerialinthiseBookalsoappearsintheprintversionofthistitle:0-07-140210-1.Alltrademarksaretrademarksoftheirrespectiveowners.Ratherthanputatrademarksymbolaftereveryoccurrenceofatrademarkedname,weusenamesinaneditorialfashiononly,andtothebenefitofthetrademarkowner,withnointentionofinfringementofthetrademark.Wheresuchdesignationsappearinthisbook,theyhavebeenprintedwithinitialcaps.McGraw-HilleBooksareavailableatspecialquantitydiscountstouseaspremiumsandsalespromotions,orforuseincorporatetrainingprograms.Formoreinformation,pleasecontactGeorgeHoare,SpecialSales,atgeorge_hoare@mcgraw-hill.comor(212)904-4069.TERMSOFUSEThisisacopyrightedworkandTheMcGraw-HillCompanies,Inc.(“McGraw-Hill”)anditslicensorsreserveallrightsinandtothework.Useofthisworkissubjecttotheseterms.ExceptaspermittedundertheCopyrightActof1976andtherighttostoreandretrieveonecopyofthework,youmaynotdecompile,disassemble,reverseengineer,reproduce,modify,createderivativeworksbasedupon,transmit,distribute,disseminate,sell,publishorsublicensetheworkoranypartofitwithoutMcGraw-Hill’spriorconsent.Youmayusetheworkforyourownnoncommercialandpersonaluse;anyotheruseoftheworkisstrictlyprohibited.Yourrighttousetheworkmaybeterminatedifyoufailtocomplywiththeseterms.THEWORKISPROVIDED“ASIS.”McGRAW-HILLANDITSLICENSORSMAKENOGUARANTEESORWARRANTIESASTOTHEACCURACY,ADEQUACYORCOMPLETENESSOFORRESULTSTOBEOBTAINEDFROMUSINGTHEWORK,INCLUDINGANYINFORMATIONTHATCANBEACCESSEDTHROUGHTHEWORKVIAHYPERLINKOROTHERWISE,ANDEXPRESSLYDISCLAIMANYWARRANTY,EXPRESSORIMPLIED,INCLUDINGBUTNOTLIMITEDTOIMPLIEDWARRANTIESOFMERCHANTABILITYORFITNESSFORAPARTICULARPURPOSE.McGraw-Hillanditslicensorsdonotwarrantorguaranteethatthefunctionscontainedintheworkwillmeetyourrequirementsorthatitsoperationwillbeuninterruptedorerrorfree.NeitherMcGraw-Hillnoritslicensorsshallbeliabletoyouoranyoneelseforanyinaccuracy,errororomission,regardlessofcause,intheworkorforanydamagesresultingtherefrom.McGraw-Hillhasnoresponsibilityforthecontentofanyinformationaccessedthroughthework.UndernocircumstancesshallMcGraw-Hilland/oritslicensorsbeliableforanyindirect,incidental,special,punitive,consequentialorsimilardamagesthatresultfromtheuseoforinabilitytousethework,evenifanyofthemhasbeenadvisedofthepossibilityofsuchdamages.Thislimitationofliabilityshallapplytoanyclaimorcausewhatsoeverwhethersuchclaimorcausearisesincontract,tortorotherwise.DOI:10.0136/0071442820TLFeBOOKCONTENTSINTRODUCTIONvCHAPTER1AFinancialProjectionModel1CHAPTER2DesignPrinciplesforGoodModelBuilding13CHAPTER3StartingOut23CHAPTER4YourModel-BuildingToolbox:FKeysandRanges47CHAPTER5YourModel-BuildingToolbox:Functions63CHAPTER6GuerillaAccountingforModeling109CHAPTER7BalancingtheBalanceSheet119CHAPTER8IncomeStatementandBalanceSheetAccounts145CHAPTER9PuttingEverythingTogether155CHAPTER10TheISandBSOutputSheets193CHAPTER11TheCFSheet199CHAPTER12Ratios:KeyPerformanceIndicators209CHAPTER13ForecastingGuidelines227iiiTLFeBOOKCHAPTER14TheCashSweep237CHAPTER15TheCashFlowVariationforCashSweep257CHAPTER16RecordingMacros271CHAPTER17On-ScreenControls287CHAPTER18BellsandWhistles297CHAPTER19WritingaMacroinVisualBasicforApplications315INDEX329ivContentsTLFeBOOKINTRODUCTIONThisbookwillteachyouhowtobringtogetherwhatyouknowoffinance,accounting,andthespreadsheettogiveyouanewskill—buildingfinancialmodels.Theabilitytocreateandunder-standmodelsisoneofthemostvaluedskillsinbusinessandfinancetoday.It’sanexpertisethatwillstandyouingoodsteadinanyarena—WallStreetorMainStreet—wherenumbersareimportant.Whetheryouareaveteran,juststartingoutonyourcareer,orstillinschool,havingthisexpertisecangiveyouacompetitiveadvantageinwhatyouwanttodo.Bythetimeyouhavecompletedthestepslaidoutinthisbook,youwillhavecreatedaworking,dynamicspreadsheetfinancialmodelwithGenerallyAcceptedAccountingPrinciples(GAAP)thatyoucanusetomakeprojectionsforindustrial/man-ufacturingcompanies.(Banksandinsurancecompanieshavedif-ferentflowsintheirbusinessesandarenotcoveredinthisbook.)Alongtheway,Iwilltakeyouthroughatouroftheessen-tialsinExcelandmodeling(Chapters1to5),then‘‘guerillaaccounting’’togiveyousomefamiliaritywiththissubject(Chapter6)beforeplungingintoactualmodelbuilding(Chapters7to11).Icovertheperformanceindicatorsthatamodelshouldhave(Chapter12)andguidelinesformakingusefulforecasts(Chapter13).Intherestofthebook(Chapters14to19),Itakeyoubacktobuildingadditional‘‘bellsandwhistles’’toaddtothebasicmodelthatyouhavebuilt.vTLFeBOOKFIRST,SOMEDEFINITIONSAspreadsheetcanbeusedtotabulateandorganizenumbers,butitdoesnotbecomeamodeluntilitcontains