Visit to download the full and correct content document: https://ebookmass.com/product/the-essentials-of-financial-modeling-in-excel-a-concis e-guide-to-concepts-and-methods-michael-rees/
More products digital (pdf, epub, mobi) instant download maybe you interests ...
eTextbook 978-1133366171 Financial Accounting: An Introduction to Concepts Methods and Uses 14th Edition
https://ebookmass.com/product/etextbook-978-1133366171-financialaccounting-an-introduction-to-concepts-methods-and-uses-14thedition/
Learn VBA Programming: For Finance & Accounting: A Concise Guide to Financial Programming with VBA Schwartz
https://ebookmass.com/product/learn-vba-programming-for-financeaccounting-a-concise-guide-to-financial-programming-with-vbaschwartz/
Excel 2022 3 Books In 1: A to Z Mastery Guide on Excel Basic Operations, Excel Formulas Joe Webinar
https://ebookmass.com/product/excel-2022-3-books-in-1-a-to-zmastery-guide-on-excel-basic-operations-excel-formulas-joewebinar/
Samurai: A Concise History Michael Wert
https://ebookmass.com/product/samurai-a-concise-history-michaelwert/
The History
of Emotions:
A Student Guide to Methods and Sources Katie Barclay [Barclay
https://ebookmass.com/product/the-history-of-emotions-a-studentguide-to-methods-and-sources-katie-barclay-barclay/
Elephants under human care : the behaviour, ecology, and welfare of elephants in captivity Paul A. Rees
https://ebookmass.com/product/elephants-under-human-care-thebehaviour-ecology-and-welfare-of-elephants-in-captivity-paul-arees/
Concise Guide to Paralegal Ethics (Aspen Paralegal)
https://ebookmass.com/product/concise-guide-to-paralegal-ethicsaspen-paralegal/
The Market Research Toolbox: A Concise Guide for Beginners
https://ebookmass.com/product/the-market-research-toolbox-aconcise-guide-for-beginners/
Personal Financial Planning for Executives and Entrepreneurs: The Path to Financial Peace of Mind 1st ed. Edition Michael J. Nathanson
https://ebookmass.com/product/personal-financial-planning-forexecutives-and-entrepreneurs-the-path-to-financial-peace-ofmind-1st-ed-edition-michael-j-nathanson/
Table of Contents
Cover
TitlePage
Copyright
Dedication
AboutThisBook
TheAuthor
PartOne:IntroductiontoModeling
1ModelingandItsUses
1.1WHATISAMODEL?
1.2WHATAREMODELSUSEDFOR?
2PrinciplesofModelDesign
2.1INTRODUCTION
2.2DECISIONIDENTIFICATION,FRAMING,ANDSTRUCTURE
2.3DECISIONCRITERIAANDINFORMATIONNEEDS
2.4SENSITIVITY‐BASEDDESIGN
2.5DATAANDDATASOURCES
2.6MODELMAPPINGANDAPPROXIMATIONS
2.7BUILDINGANDTESTING
2.8RESULTSPRESENTATION
2.9BIASES
PartTwo:EssentialsofExcel
3Menus,Operations,Functions,andFeatures
3.1INTRODUCTION
3.2STRUCTUREANDMENUS
3.3CALCULATIONSUSINGARITHMETIC
3.4FUNCTIONBASICS
3.5ACOREFUNCTIONSET
3.6FURTHERPROPERTIESANDUSESOFFUNCTIONS
3.7CALCULATIONSETTINGSANDOPTIONS
3.8KEYTIPSANDSHORTCUTS
3.9ABSOLUTEANDRELATIVEREFERENCING
3.10AUDITINGANDLOGICTRACING
3.11NAMEDRANGES
3.12BESTPRACTICES:OVERVIEW
3.13BESTPRACTICES:FLOW
3.14BESTPRACTICES:TIMEAXIS
3.15BESTPRACTICES:MULTIPLEWORKSHEETS
3.16BESTPRACTICES:FORMATTING
3.17MODELTESTING,CHECKING,ANDERRORMANAGEMENT
3.18GRAPHSANDCHARTS
4SensitivityandScenarioAnalysis
4.1INTRODUCTION
4.2BASICORMANUALSENSITIVITYANALYSIS
4.3AUTOMATINGSENSITIVITYANALYSIS:ANINTRODUCTION
4.4USINGDataTables
4.5CHECKINGTHERESULTS,LIMITATIONS,ANDTIPS
4.6CREATINGFLEXIBILITYINTHEOUTPUTSTHATAREANALYZED
4.7SCENARIOANALYSIS
4.8VARIATIONSANALYSIS
4.9USINGGoalSeek
4.10FURTHERTOPICS:OPTIMIZATION,RISK,UNCERTAINTY,ANDSIMULATION
PartThree:GeneralCalculationsandStructures
5GrowthCalculationsforForecasting
5.1INTRODUCTION
5.2GROWTHMEASUREMENTANDFORECASTING
5.3LOGICREVERSALS
5.4FORECASTINGSTRUCTURESINPRACTICE
5.5SIMPLIFYINGTHESENSITIVITYANALYSISANDREDUCINGTHENUMBEROF PARAMETERS
5.6DEALINGWITHINFLATION
5.7CONVERSIONSFORMODELPERIODS
5.8FURTHERTOPICS:LOGARITHMICANDEXPONENTIALGROWTH
6ModularStructuresandSummaryReports
6.1INTRODUCTION
6.2MOTIVATIONFORSUMMARYAREASANDTHEIRPLACEMENT
6.3EXAMPLEI:SUMMARIESANDCONDITIONALSUMMARIES
6.4EXAMPLEII:TARGETS,FLAGS,ANDMATCHING
6.5SENSITIVITYANALYSIS
6.6COMMENTSONFORMATTING
6.7INITIALIZATIONAREAS
7ScalingandRatio‐drivenForecasts
7.1INTRODUCTION
7.2BASICUSES
7.3LINKSTOLENGTHOFMODELPERIODS
7.4DAYS'EQUIVALENTAPPROACHES
7.5EXAMPLEI:FORECASTINGFROMREVENUESTOEBITDA
7.6USINGRATIO‐BASEDFORECASTINGEFFECTIVELY
7.7EXAMPLEII:RATIO‐BASEDFORECASTINGOFCAPITALITEMS
7.8FURTHERTOPICS:LINKSTOGENERALRATIOANALYSIS
8CorkscrewsandReverseCorkscrews
8.1INTRODUCTION
8.2CLASSICALCORKSCREWS
8.3BENEFITSANDFURTHERUSES
8.4REVERSECORKSCREWS
9WaterfallAllocations
9.1INTRODUCTION
9.2EXAMPLEI:COSTSHARING
9.3EXAMPLEII:TAXCALCULATIONS
9.4OPTIONSFORLAYOUTANDSTRUCTURE
9.5FURTHERTOPICS:WATERFALLSFORSHARINGCAPITALRETURNSORCARRIED INTEREST
10.1INTRODUCTION
10.2EXAMPLEI:LINEARSMOOTHING
10.3EXAMPLEII:PROPORTIONALSMOOTHING
10.4USESOFTAPERINGANDINTERPOLATION
10.5TRIANGLES
10.6FURTHERTOPICS:TRIANGLES
PartFour:EconomicFoundationsandEvaluation
11BreakevenandPaybackAnalysis
11.1INTRODUCTION
11.2SINGLE‐PERIODBREAKEVENANALYSIS:PRICESANDVOLUMES
11.3BREAKEVENTIMEANDPAYBACKPERIODS
12InterestRatesandCompounding
12.1INTRODUCTION
12.2STATEDRATESANDCALCULATIONSWITHOUTCOMPOUNDING
12.3COMPOUNDINGTYPESANDEFFECTIVERATES
12.4CONVERSIONOFEFFECTIVERATESFORPERIODSOFDIFFERENTLENGTHS
12.5AVERAGEEFFECTIVERATES
12.6IMPLIEDRATESANDBOOTSTRAPPING
13LoanRepaymentCalculations
13.1INTRODUCTION
13.2EFFECTIVERATESFORINTEREST‐ONLYREPAYMENTS
13.3ALIGNINGMODELPERIODSWITHINTERESTREPAYMENTS
13.4CONSTANTREPAYMENTLOANSUSINGTHEPMTFUNCTION
13.5CONSTANTREPAYMENTLOANS:OTHERFUNCTIONS
13.6PERIODSOFDIFFERENTLENGTHS
14Discounting,PresentValues,andAnnuities
14.1INTRODUCTION
14.2THETIMEVALUEOFMONEY
14.3CALCULATIONOPTIONSFORPRESENTVALUES
14.4ANNUITIESANDPERPETUITIES
14.5MULTI‐PERIODAPPROACHESANDTERMINALVALUES
14.6FURTHERTOPICSI:MATHEMATICSOFANNUITIES
14.7FURTHERTOPICSII:CASHFLOWTIMING
15ReturnsandInternalRateofReturn
15.1INTRODUCTION
15.2SINGLEINVESTMENTSANDPAYBACKS
15.3MULTIPLEPAYBACKS:AVERAGERETURNSANDTHEINTERNALRATEOF RETURN
15.4USINGECONOMICMETRICSTOGUIDEINVESTMENTDECISIONS
15.5PROPERTIESANDCOMPARISONOFNPVANDIRR
PartV:CorporateFinanceandValuation
16TheCostofCapital
16.1INTRODUCTION
16.2RETURNS,COSTS,ANDOPPORTUNITYCOSTSOFCAPITAL
16.3THEROLEOFRISKINDETERMININGTHECOSTOFCAPITAL
16.4THEPROPERTIESANDBENEFITSOFDEBT
16.5THEFINANCINGMIXANDTHEWEIGHTEDAVERAGECOSTOFCAPITAL
16.6MODIGLIANI‐MILLERANDLEVERAGEADJUSTMENTS
16.7THECAPITALASSETPRICINGMODEL
16.8FURTHERTOPICS:DERIVATIONOFLEVERAGINGANDDELEVERAGING FORMULAS
17FinancialStatementModeling
17.1INTRODUCTION
17.2FINANCIALSTATEMENTESSENTIALS
17.3KEYCHALLENGESINBUILDINGINTEGRATEDFINANCIALSTATEMENTMODELS
17.4FORECASTINGOFTHEINTEGRATEDSTATEMENTS:ASIMPLEEXAMPLE
17.5THEDYNAMICFINANCINGADJUSTMENTMECHANISM
17.6GENERALIZINGTHEMODELFEATURESANDCAPABILITIES
17.7STEPSANDPRINCIPLESINBUILDINGAFINANCIALSTATEMENTMODEL
17.8FURTHERTOPICS:AVOIDINGCIRCULARITIES
18CorporateValuationModeling
18.1INTRODUCTION
18.2OVERVIEWOFVALUATIONMETHODS
18.3PRINCIPLESOFCASHFLOWVALUATION
18.4FREECASHFLOWFORENTERPRISEVALUATION
18.5THEROLEOFTHEEXPLICITFORECAST
18.6EXAMPLE:EXPLICITFORECASTWITHTERMINALVALUECALCULATION
18.7FURTHERTOPICSI:ENTERPRISEVALUEBASEDONFREECASHFLOWAND EQUIVALENCES
18.8FURTHERTOPICSII:VALUE‐DRIVERFORMULAS
18.9FURTHERTOPICSIII:IMPLIEDCOSTOFEQUITY
19RatioAnalysis
19.1INTRODUCTION
19.2USEANDPRINCIPLES
19.3RATIOSFORPROFITABILITYANDVALUATION
19.4RATIOSRELATINGTOOPERATIONSANDEFFICIENCY
19.5RATIOSFORLIQUIDITYANDLEVERAGE
19.6DuPontANALYSIS
19.7VARIATIONSANALYSISWITHINTHEDuPontFRAMEWORK
19.8FURTHERTOPICS:PORTFOLIOSANDTHEPIOTROSKIF‐SCORE
PartSix:DataandStatisticalAnalysis
20StatisticalAnalysisandMeasures
20.1INTRODUCTION
20.2DATASTRUCTURESINEXCELANDTHEIMPACTONFUNCTIONALITY
20.3AVERAGESANDSPREAD
20.4THEAGGREGATEFUNCTION
20.5CONDITIONALAGGREGATIONS
20.6DATABASEFUNCTIONS
20.7CORRELATIONS,COVARIANCE,ANDREGRESSION
20.8EXCELTABLES
20.9PIVOTTABLES
20.10FURTHERTOPICS:MOREONAVERAGES,CORRELATIONS,ANDCONFIDENCE INTERVALS 21DataPreparation:Sourcing,Manipulation,andIntegration
Index
21.1INTRODUCTION
21.2MODELINGCONSIDERATIONS
21.3OVERVIEWOFDATAMANIPULATIONPROCESS
21.4CLEANINGEXCELDATASETS
21.5INTEGRATIONOFEXCELDATASETS
21.6FURTHERTOPICSI:INTRODUCTIONTOPowerQuery–APPENDINGTABLES
21.7FURTHERTOPICSII:INTRODUCTIONTOPowerQuery–DATAMANIPULATION
21.8FURTHERTOPICSIII:INTRODUCTIONTOPowerPivotANDTHEDATAMODEL
EndUserLicenseAgreement
List of Illustrations
Chapter1
Figure1.1InfluenceDiagramofaSimpleRevenueModel
Figure1.2ExcelModelThatContainsFormulasbutNoValues
Figure1.3ExcelModelwithInputCellsPopulatedwithValues
Figure1.4InputCellswithColor‐Coding
Figure1.5UsingaModeltoCompareSalesRevenuesforBusinessDesignOptio...
Chapter2
Figure2.1Basic“Go/NoGo”DecisionwithSub‐Options
Figure2.2UsingtheDecisiontoDesigntheModelThatSupportstheDecision
Figure2.3UsingaSensitivity‐BasedThoughtProcesstoDefineModelVariabl...
Chapter3
Figure3.1CoreMenuTabs
Figure3.2TheHomeTab(left‐hand‐sideonly)
Figure3.3TheFormulasTab(left‐hand‐sideonly)
Figure3.4ExampleoftheSUMFunction
Figure3.5TheInsertFunctionMenu
Figure3.6TheIFFunctionandItsArguments
Figure3.7EnteringtheUNIQUEFunctioninaSingleCell
Figure3.8TheDynamicOutputRangeoftheUNIQUEFunction
Figure3.9Using#ToRefertoaDynamicOutputRange
Figure3.10TheCalculationOptionsontheFormulasTab
Figure3.11EffectofChangestoInputValuesinManualSetting
Figure3.12AccessingtheMenuUsingKeyTips
Figure3.13SelectingaRangetobeCopied
Figure3.14ResultsAfterPasting
Figure3.15TheAdjustedandCompletedModel
Figure3.16ThePasteSpecialMenu
Figure3.17CentralCostsAllocatedAccordingtoTrips
Figure3.18FormulasUsedtoAllocateCentralCost
Figure3.19TheFormulas/FormulaAuditingMenu
Figure3.20TheFormulaView
Figure3.21UsingTraceDependentsandTracePrecedents
Figure3.22InspectingaFormulaUsingtheF2Key
Figure3.23TheWatchWindow
Figure3.24UsingtheNameManager
Figure3.25SimpleModelwithNamedInputs
Figure3.26TheNameBox
Figure3.27AccessingtheGoTo(F5)Functionality
Figure3.28DiagonalDependencyPaths
Figure3.29HorizontalandVerticalDependencyPaths
Chapter4
Figure4.1AccessingaDataTableUsingData/What‐IfAnalysis
Figure4.2RecapofCab(Taxi)BusinessProfitModel
Figure4.3ThreeRawDataTableStructures
Figure4.4CompletingaTwo‐WayDataTable
Figure4.5TheCompletedTwo‐WayDataTable
Figure4.6TheRawDataTableStructuresforDataTableswithMultipleOutputs
Figure4.7SummaryAreawithSelectionMenu
Figure4.8DataTablewithChoiceofOutputstoAnalyze
Figure4.9UsingDataValidationtoRestrictaUser'sChoicestoValidItems...
Figure4.10ModelInputsAreReplacedbyCellReferencestotheScenarioCho...
Figure4.11ImplementingtheScenarioResultsUsingaDataTable
Figure4.12SimpleExampleofVarianceAnalysis
Figure4.13ExampleofUsingGoalSeek
Chapter5
Figure5.1BasicGrowthForecast
Figure5.2HistoricalInformationandGrowthForecasting
Figure5.3CommonLayoutofGrowthForecasting
Figure5.4Multi‐periodForecastUsingtheCommonLayout
Figure5.5ReducingtheNumberofSeparateInputAssumptions
Figure5.6FullSeparationofInputsfromCalculations
Figure5.7DataTableofYear5RevenuestoTwoGrowthAssumptions
Figure5.8UsingInflationasaSeparateItem
Figure5.9ComparisonofMeasurementandForecastingResults
Figure5.10RawDataonGrowthRatesMeasuredbyEachMethod
Figure5.11CalculationofTotalandAverageGrowthUsingEachMethod
Chapter6
Figure6.1AnInitialFive‐YearModelwithQuarterlyPeriods
Figure6.2SummaryofFive‐YearandSpecifiedYear
Figure6.3UsingFlagFieldstoFindWhenaTargetisMet
Figure6.4UsingaDataTableforItemsintheSummaryReport
Figure6.5SettingaConditionalFormatRule
Figure6.6DependencieswithoutInitializationArea
Figure6.7UseofanInitializationAreatoBeAbletoHaveConsistentFormu...
Chapter7
Figure7.1HistoricalCalibrationandRatio‐BasedForecastforaFlowItem...
Figure7.2HistoricalCalibrationandRatio‐BasedForecastforaStockItem...
Figure7.3UsingtheDays,EquivalentMethod
Figure7.4PriceForecastfortheExampleModel
Figure7.5SalesRevenueCalculation
Figure7.6CalculationofFixedandVariableCosts
Figure7.7CalculationofEBITDAintheSimpleModel
Figure7.8CalculationofCapExUsingaVolume‐BasedRatioandInflation...
Chapter8
Figure8.1FrameworkforaCorkscrewStructure
Figure8.2LinkingofCapExintotheCorkscrewStructure
Figure8.3LinkingofCapExintotheCorkscrewStructure
Figure8.4CompletionofStructurefortheFirstPeriod
Figure8.5CompletedStructurewithDependencyPathsShown
Figure8.6BasicRatioAnalysisofAssetstoSales
Figure8.7CalculationofNetFlowItems
Figure8.8CoreStructureofaReverseCorkscrew
Figure8.9InclusionofOneFlowItem
Figure8.10CompletionofBothFlowItems
Chapter9
Figure9.1GeneralSplitUsingtheMINFunction
Figure9.2TwoCategoryWaterfallSplit–VerticalLayout
Figure9.3TwoCategoryWaterfallSplit–HorizontalLayout
Figure9.4CapacitiesoftheMultipleLayers
Figure9.5CompletedCalculationsofMultipleLayerExample
Figure9.6WaterfallStructureforTaxCalculation
Figure9.7VerticalWaterfallStructuredbyItem
Figure9.8TimeAxisonaVerticalWaterfallStructuredbyItem
Figure9.9VerticalWaterfallStructuredbyBand
Figure9.10CapitalReturnWaterfallwithSingleThreshold
Figure9.11CapitalReturnWaterfallwithAlternativeValue
Figure9.12CapitalReturnWaterfallwithAlternativeValue
Chapter10
Figure10.1OverviewofModelwithInterpolatedGrowthRates
Figure10.2TheFormulaUsedinCellH11
Figure10.3ProportionalSmoothingwithFlexiblePeriodStart
Figure10.4LogicFlowforEachForecastFormula
Figure10.5FormulaUsedinCellH8
Figure10.6ExampleoftheEffectofaCombinedSmoothing
Figure10.7TriangleInputs:Time‐SpecificPurchasesandGenericTimeAlloca...
Figure10.8Time‐SpecificAllocations(Step1)
Figure10.9Time‐SpecificAllocations(Step2)
Figure10.10TriangleOutputsFeedingaCorkscrew
Chapter11
Figure11.1ModelUsedforSingle‐PeriodAnalysis
Figure11.2CostStructureasVolumeisVaried
Figure11.3Revenue,Cost,andProfitasVolumeisVaried
Figure11.4ThresholdsandCombinationstoAchieveBreakeven
Figure11.5Time‐BasedForecastfromSalestoEBITDA
Figure11.6CompletedModelwithForecasttoCashFlows
Figure11.7CompletedSetofCalculations
Figure11.8TheFormulaViewoftheCompletedCalculations
Chapter12
Figure12.1ExampleofCompoundedInterestCalculations
Figure12.2ExampleoftheEFFECTFunction
Figure12.3EffectivePeriodicRatesforDifferentCompoundingFrequencies
Figure12.4UseofFVSCHEDULEFunction
Figure12.5YieldCurveBootstrappingAssumptionsandContext
Figure12.6YieldCurveBootstrappingResults
Chapter13
Figure13.1UseoftheDerivedFormulatoCalculateanEffectiveRateGiven...
Figure13.2ExplicitCalculationoftheEffectiveRateGivenRepayments
Figure13.3ExampleofthePMTFunction
Figure13.4FunctionArgumentsforthePMTFunction
Figure13.5ExplicitCalculationofLoanRepaymentUsingaCorkscrewStructu...
Figure13.6PaymentValuewithStart‐of‐PeriodPayments
Figure13.7ExplicitCalculationWhenPaymentIsattheStartofEachPeriod
Figure13.8ReversalofNaturalValueswhenUsingPMT
Figure13.9VerificationofCalculationsUsingSignReversal
Figure13.10ExamplesoftheRATE,NPER,FV,andPVFunctions
Figure13.11RatesWhentheLoanPeriodIsaMultipleoftheCompoundingPer Chapter14
Figure14.1TheAssumedCashFlowProfileforaDiscountingExample
Figure14.2TheAssumedOne‐YearDiscountRates
Figure14.3PossibilitiestoCalculatetheDiscountFactors
Figure14.4TheDiscountedCashFlowsandtheTotal
Figure14.5ConstantDiscountRatewithExplicitProfile
Figure14.6UseoftheNPVFunction
Figure14.7ValuinganAnnuitybyExplicitCalculationoftheCashFlows
Figure14.8ApplicationoftheAnnuityFormulas
Figure14.9InputAssumptionsforTwo‐StageTerminalValueCalculation
Figure14.10ImplementationofTwo‐StageTerminalValueCalculation
Chapter15
Figure15.1PercentageReturnsCalculatedExplicitlyinaSimpleCase
Figure15.2ReturnsExpressedonaPer‐PeriodBasis
Figure15.3ExamplewithPaybackOccurringinTwoPeriods
Figure15.4InflatingorDiscountingCashFlowstoAchieveaTotalValueof...
Figure15.5UsingtheIRRFunction
Figure15.6IRRwithSeveralPeriodsofInvestmentandPayback
Chapter16
Figure16.1ThresholdLevelforDebt‐EquitySubstitutionandwithoutTaxes...
Figure16.2ThresholdLevelforDebt‐EquitySubstitutionandwithoutTaxes...
Figure16.3TheLeverageEffectofDebtonReturnstoEquity(atBookValue)
Figure16.4EffectofDebtwithTaxes
Figure16.5EffectofDebtIfChargesWereNotOffsetAgainstTaxes
Figure16.6GenericEffectofLeverageonCostofCapital:Equity,Debt,and...
Figure16.7ASimpleExampleoftheCalculationoftheExpectedReturn
Chapter17
Figure17.1IncomeStatementforSimpleModel
Figure17.2CashandEquityCorkscrews
Figure17.3TheBalanceSheetfortheBaseCase
Figure17.4TheBalanceSheetwithaLowerInitialCapitalInjection
Figure17.5ImplementationoftheAdjustmentMechanism
Figure17.6CompletionofStatementstoReflecttheEquityInjection
Figure17.7ExampleofAddinganAccountsReceivableFunctionality
Chapter18
Figure18.1ForecasttotheNOPATline
Figure18.2CalculationoftheValueintheExplicitForecastPeriod
Figure18.3TerminalValueCalculation
Figure18.4TotalEnterpriseandEquityValue
Chapter19
Figure19.1GenericExampleofDuPontAnalysisUsingLinearScales
Figure19.2VariationsAnalysisUsingComponentParts
Chapter20
Figure20.1RawDataforInputtotheStatisticalFunctions
Figure20.2ExamplesoftheUseofAGGREGATE
Figure20.3UsingAGGREGATEwithitsFourthArgument
Figure20.4AugmentedDataSetwithMonthandYearInformation
Figure20.5UseoftheAVERAGEIFSFunction
Figure20.6DataSetwithFieldHeaders
Figure20.7ExampleofaCriteriaRangeforaDatabaseFunction
Figure20.8FunctionArgumentsfortheDatabaseFunctions
Figure20.9ResultsofApplyingtheDatabaseFunctions
Figure20.10DataSetforCorrelationandRegressionAnalysis
Figure20.11X‐YScatterPlotwithTrendlineDisplayed
Figure20.12CalculationofSlope,Correlations,andStandardDeviations
Figure20.13CreatingaTable
Figure20.14TheTableDesignTab
Figure20.15EnteringaFormulaThatWillRefertoaTable
Figure20.16CompletedPivotTablewithaRowStructure
Figure20.17FirstSteptoInsertaPivotTable
Figure20.18CompletionofStep‐by‐StepCreationofaPivotTable
Figure20.19PopulationofthePivotTableStructure
Figure20.20ResultsoftheLINESTFunction
Chapter21
Figure21.1RawDataandDesiredTransformation
Figure21.2CalculationStepsforOneItem,ShowninaColumn
Figure21.3RowFormoftheCalculationsandResults
Figure21.4DataIncludingTransactionValuesinLocalCurrency
Figure21.5TableswithAdditionalInformationThatNeedtoBeReferenced
Figure21.6AugmentedMainTableShowingCountryNames
Figure21.7MainTablewithFurtherAugmentation
Figure21.8TheCompletedFlatTable
Figure21.9ResultsofAppendingTwoTablestoCreateaThird
Figure21.10ThePowerQueryEditor
Figure21.11SelectingtoCreateaConnectionOnly
Figure21.12Queries&ConnectionsBeforeAppending
Figure21.13UsingtheTable.CombineOperation
Figure21.14UsingPowerQueryfortheFullProcess
Figure21.15TablesandTheirRelationshipswithintheDataModel
Figure21.16CreationofaMeasure
Figure21.17PivotTablethatDisplaystheValueofaPowerPivotMeasure
METHODS
MichaelRees
Thiseditionfirstpublished2023
©2023MichaelRees
Allrightsreserved.Nopartofthispublicationmaybereproduced,storedinaretrievalsystem,ortransmitted,in anyformorbyanymeans,electronic,mechanical,photocopying,recordingorotherwise,exceptaspermittedby law.Adviceonhowtoobtainpermissiontoreusematerialfromthistitleisavailableat http://www.wiley.com/go/permissions
TherightofMichaelReestobeidentifiedastheauthorofthisworkhasbeenassertedinaccordancewithlaw. Registered Offices
JohnWiley&Sons,Inc.,111RiverStreet,Hoboken,NJ07030,USA
JohnWiley&SonsLtd,TheAtrium,SouthernGate,Chichester,WestSussex,PO198SQ,UK
Editorial Office
TheAtrium,SouthernGate,Chichester,WestSussex,PO198SQ,UK
Fordetailsofourglobaleditorialoffices,customerservices,andmoreinformationaboutWileyproductsvisitusat www.wiley.com.
Wileyalsopublishesitsbooksinavarietyofelectronicformatsandbyprint‐on‐demand.Somecontentthat appearsinstandardprintversionsofthisbookmaynotbeavailableinotherformats.
Trademarks:WileyandtheWileylogoaretrademarksorregisteredtrademarksofJohnWiley&Sons,Inc.and/or itsaffiliatesintheUnitedStatesandothercountriesandmaynotbeusedwithoutwrittenpermission.Allother trademarksarethepropertyoftheirrespectiveowners.JohnWiley&Sons,Inc.isnotassociatedwithanyproduct orvendormentionedinthisbook.
Limit of Liability/Disclaimer of Warranty
Whilethepublisherandauthorshaveusedtheirbesteffortsinpreparingthiswork,theymakenorepresentations orwarrantieswithrespecttotheaccuracyorcompletenessofthecontentsofthisworkandspecificallydisclaimall warranties,includingwithoutlimitationanyimpliedwarrantiesofmerchantabilityorfitnessforaparticular purpose.Nowarrantymaybecreatedorextendedbysalesrepresentatives,writtensalesmaterialsorpromotional statementsforthiswork.Thefactthatanorganization,website,orproductisreferredtointhisworkasacitation and/orpotentialsourceoffurtherinformationdoesnotmeanthatthepublisherandauthorsendorsethe informationorservicestheorganization,website,orproductmayprovideorrecommendationsitmaymake.This workissoldwiththeunderstandingthatthepublisherisnotengagedinrenderingprofessionalservices.The adviceandstrategiescontainedhereinmaynotbesuitableforyoursituation.Youshouldconsultwithaspecialist whereappropriate.Further,readersshouldbeawarethatwebsiteslistedinthisworkmayhavechangedor disappearedbetweenwhenthisworkwaswrittenandwhenitisread.Neitherthepublishernorauthorsshallbe liableforanylossofprofitoranyothercommercialdamages,includingbutnotlimitedtospecial,incidental, consequential,orotherdamages.
Library of Congress Cataloging‐in‐Publication Data
Names:Rees,Michael,1964‐author
Title:TheessentialsoffinancialmodelinginExcel:aconciseguidetoconceptsandmethods/MichaelRees.
Description:Hoboken,NJ:JohnWiley&Sons,Inc.,2023.|Includesindex.
Identifiers:LCCN2022043302(print)|LCCN2022043303(ebook)|ISBN9781394157785(paperback)|ISBN 9781394157792(adobepdf)|ISBN9781394157808(epub)
Subjects:LCSH:Finance—Mathematicalmodels.|Corporations—Finance—Mathematicalmodels.|Microsoft Excel(Computerfile)
Classification:LCCHG106.R4392023(print)|LCCHG106(ebook)|DDC332.0285/554—dc23/eng/20220908
LCrecordavailableathttps://lccn.loc.gov/2022043302
LCebookrecordavailableathttps://lccn.loc.gov/2022043303
CoverDesign:Wiley
CoverImage:©Skylines/Shutterstock
This book is dedicated to Elsa and Raphael.
About This Book
ThisbookprovidesaconciseintroductiontofinancialmodelinginExcel.Itaimstoprovidereaderswithawell‐structuredandpracticaltoolkittolearnmodeling“fromthegroundup.”Itisuniqueinthatitfocusesonthe conceptsandstructuresthatarecommonlyrequiredwithinExcelmodels,ratherthanonExcelperse. Thebookisstructuredintosixparts(containingtwenty‐onechaptersintotal):
PartIintroducesfinancialmodelingandthegeneralfactorstoconsiderwhendesigning,building,andusing models.
PartIIdiscussesthecorefeaturesofExcelthatareneededtobuildandusemodels.Itcoversoperationsand functionality,calculationsandfunctions,andsensitivityandscenarioanalysis.
PartIIIcoversthefundamentalstructuresandcalculationsthatareveryfrequentlyusedinmodeling.This includesgrowth‐basedforecasting,ratio‐drivencalculations,corkscrewstructures,waterfalls,allocations, triangles,andvariationsofthese.
PartIVdiscusseseconomicmodeling,measurement,andevaluation.Itcoverstheanalysisofinvestments, interestcalculationsandcompounding,loancalculations,returnsanalysis,discounting,andpresentvalues.
PartVtreatsthecoreapplicationsofmodelingwithincorporatefinance.Itcoversthecostofcapital,the modelingoffinancialstatements,cashflowvaluation,andratioanalysis.
PartVIcoversstatisticalanalysis,aswellasdatapreparation,manipulation,andintegration.
Readerswillgenerallyobtainthemaximumbenefitbystudyingthetextfromthebeginningandworkingthrough itinorder.Itisintendedthatthereaderbuildsfromscratchthemodelsthatareshown,toreinforcethelearning experienceandtoenhancepracticalskills.Ofcourse,theremaybeareaswhicharealreadyfamiliartosome readers,andwhichcanbeskim‐read.Nevertheless,thetextisintendedtobeconciseandpractical,andtocontain informationthatispotentiallyusefuleventoreaderswhomayhavesomefamiliaritywiththesubject.
Althoughthetextisfocusedontheessentials,atvariousplacesitbrieflyhighlightssomeaspectsofmore advancedtopics.ThesearedescribedinFurtherTopicssections,whicharesituatedattheendofsomechapters. Thesesectionscanbeskippedatthereader'sdiscretionwithoutaffectingthecomprehensionofthesubsequent text.Notethatanotheroftheauthor'sworks(Principles of Financial Modelling: Model Design and Best Practices Using Excel and VBA,JohnWiley&Sons,2018)discussesindetailsometopicsthatareonlybriefly(ornot) coveredinthistext(notablyVBAmacros,optimization,circularities,namedranges,andothers).Forconvenience, inthecurrenttextthisothertextisoccasionallymentionedatspecificplaceswhereitcontainssignificant additionalmaterialsrelatedtothediscussion,andissubsequentlyreferredtoas PFM.
The Author
Dr.MichaelReesisaleadingexpertinquantitativemodelingandanalysisforapplicationsinbusinesseconomics, finance,valuation,andriskassessment.HeisProfessorofFinanceatAudenciaBusinessSchoolinNantes (France),whereheteachessubjectsrelatedtovaluation,financialengineering,optimization,riskassessment, modeling,andbusinessstrategy.HisearlieracademiccredentialsincludeaDoctorateinMathematicalModelling andNumericalAlgorithms,andaBAwithFirstClassHonoursinMathematics,bothfromOxfordUniversityin theUK.HehasanMBAwithDistinctionfromINSEADinFrance.HealsostudiedfortheCertificateof QuantitativeFinance,graduatingtopoftheclassforcoursework,andreceivingtheWilmottAwardforthehighest finalexammark.Priortohisacademiccareer,hegainedover30years'practicalexperience,includinginsenior rolesatleadingfirmsinfinanceandstrategyconsulting(JPMorgan,MercerManagementConsulting,andBraxton Associates),aswellasworkingasanindependentconsultantandtrainer.Hisclientsincludedcompaniesand entrepreneursinprivateequity;auditingandconsulting;finance;bankingandinsurance;pharmaceuticalsand biotechnology;oil,gas,andresources;construction;chemicals;engineering;telecommunications;transportation; thepublicsector;software;andtrainingproviders.Inadditiontothistext,heistheauthorof Principles of Financial Modelling: Model Design and Best Practices Using Excel and VBA (2018); Business Risk and Simulation Modelling in Practice: Using Excel, VBA and @RISK (2015);and Financial Modelling in Practice: A Concise Guide for Intermediate and Advanced Level (2008).
1.1 WHAT IS A MODEL?
Afinancialmodelisarepresentationofareal‐lifesituationinbusiness,economics,orfinance.Thatis,itidentifies theitemsthatrepresentthecorepropertiesofthesituationandexpressestherelationshipsbetweenthese.For example,thereisarelationshipbetweenthepriceperunit,thevolumesold,andthesalesrevenueofabusiness. Thiscanbecapturedvisuallyinaninfluencediagram(Figure1.1).
Fromapuretheoreticalperspective,therelationshipcanbedefinedmorepreciselyusingaformula:
(Where V isthevolumesold, P istheaveragepriceachievedperunit,and S isthesalesrevenue.)
ItisworthnotingthatthearrowsinFigure1.1indicatethedirectionalityofthelogic.(Forconvenience,such diagramswouldusethe“natural”left‐to‐rightflowwhereverpossible.)Ontheotherhand,intheformula, thelogic is“right‐to‐left”:Inthecontextofnumericalcomputations(andmodeling),the=signisusedtomeanthattheitem ontheleft(calledtheoutput)iscalculatedfromthoseontheright(calledtheinputs).Inotherwords,although fromapurelymathematicalperspectivetheleft‐andright‐sidesoftheformulacouldbereversed,thiswouldnot bepossiblefromtheperspectiveofmodeling,whereformulasactnotonlyasastatementofmathematicalequality, butalsoofdirectionality.
Figure1.1InfluenceDiagramofaSimpleRevenueModel
AnExcelworksheetcanbeusedtorepresentthesituationinaverysimilarway(i.e.tocaptureonlythe relationships).Forexample,Figure1.2showstheformula(incellC4)thatcalculatesSalesRevenueby multiplyingthevalueofthePriceperUnit(cellC2)withthatoftheVolumeinUnits(cellC3),evenastheinput cells(i.e.C2andC3)arecurrentlyblank.
1.2ExcelModelThatContainsFormulasbutNoValues
InExcel,the*symbolisusedformultiplication,sothattheformulacanbethoughtofas:
ThepresentationofthevalueofcellC4reinforcesthatC2andC3aretheinputs(ontheright),withC4beingthe output(ontheleft).
Ofcourse,ifonewereinterestedinrepresentingtherelationshipsfromatheoreticalperspectiveonly,thenExcel wouldbearelativelyineffectivewaytodoso(i.e.onewouldbebetterusingmathematicalformulasdirectlyina textdocument).Inpracticalapplications,aworksheet(model)willbepopulatedwithactualinputvalues,which arerelevantforthespecificsituationunderconsideration.Forexample, Figure1.3showsthesamemodelbut wheretheinputcells(C2andC3)containspecificvalues,sothatcellC4calculatestheSalesRevenuethat correspondstothatsituation.
Theinputvaluescanbeenteredoralteredbythemodeler(orbyanotheruserofthemodel).Forthisreason,when workinginExcel,itisgoodpracticetomakeacleardistinctionbetweeninputsandcalculations(formulas),sothat auserknowswhichitemscanbechanged(andwhichshouldnotbe). Figure1.4showsanexampleofusingthe shadingofacelltomakesuchadistinction(whileperhapslessclearintheblackandwhiteimage,inadditionto thebackgroundshading,thefontofsuchacellcanbeadifferentcolortothatofthefontusedofacalculatedcell, inordertoincreasethedistinctionfurther).
Figure
Figure1.3ExcelModelwithInputCellsPopulatedwithValues
Figure1.4InputCellswithColor‐Coding
Notethatinmathematicalformulas,eachitem(suchas P, V,or S)representsboththenameandthevalueofthat variable.However,inExcel,bydefaultaformulais“stand‐alone,”andiscreateddirectlywithreferencetothe valuesoftheitems.Thatis,aformulaisgivenmeaning(orcontext)bythelabelsthatareusedinothercells.For example,inFigure1.4,cellC2containsavalue(10),butweknowthatthisrepresentsthepricebecauseofthe labelthatisenteredincellB2.Similarly,weknowthatthevalueinC3representsthevolumebyreferencetothe labelinB3.Similarly,thelabelinB4forthesalesrevenueisanappropriatelabelforthevalueinC4simply becausethevalueiscalculatedbymultiplyingpricewithvolume(ratherthan,say,addingthem).Infact,inExcel, itispossibletocombinetheseroles(i.e.ofthelabelandthevalue)bynamingthecells(suchasnamingcellC2 “Price,”cellC3“Volume”andC4“SalesRevenue”).Thisisdiscussedin Chapter3inmoredetail.
Ofcourse,theinputvaluesshouldbeappropriateforthesituationthatisbeingaddressed.Further–asanentry intoacellofExcel–eachinputvalueisasinglenumber(inprinciple).Theoutputisalsoasinglenumberthatis calculatedfromtheinputs.Theterms“basecase”or“centralcase”areoftenusedtorefertothissinglecore “reference”case,whichinprincipleshouldrepresentarealisticscenarioforthevaluesthatmayarise(other scenarioscanbedefinedandstoredinthemodel,suchasoptimisticorpessimisticcases;see Chapter4).
Infact,veryoften,thevalueusedforaninputissimplyabestestimateorajudgment,orafigurethatisderived fromacomparablesituation,orfromanalyzinghistoricaldata.Therefore,theremaybesomeuncertaintyastothe truevalue.Similarly,someinputsmayrepresentitemsoverwhichadecision‐makerhassomecontrolinreal‐life andthereforerelatetoadecisionorchoice(ratherthanbeinguncertain).Thesesituationscanbecapturedbyusing variousvaluesfortheinputassumptionstocreatescenarios,meaningalsothattherewillbearangeinvaluesfor theoutput.Infact,thecreationandanalysisofthepossiblerangeofoutcomesisanimportantpartofusing modelingindecisionsupport.Themethodstodosorangefromthesimplest(e.g.copyingthemodelseveraltimes, usingdifferentinputvaluesforeach),tospecificsensitivity‐analysisfunctionalitywithinExcel(coveredin Chapter4),aswellastomoreadvancedapproaches,includingriskanalysis,simulation,andoptimizationanalysis (whicharementionedintheFurtherTopicsSectionsinChapter4andChapter20).
1.2 WHAT ARE MODELS USED FOR?
Modelscanbeusedtotrytogainabetterunderstandingof(orinsightabout)areal‐lifesituation,withtheaimof makingamoreinformeddecisioninrelationtoit.Forexample,onemaybedecidingwhethertolaunchabusiness ornot.Withintheoveralldecision,theremaybeseveralsub‐options:
Amid‐market“base”businessdesign(reasonablepricesandvolumes)
A“premium”focus(higherpricesbutachievinglowervolumes)
A“mass‐market”approach(lowerpricesandachievinghighervolumes)
Figure1.5UsingaModeltoCompareSalesRevenuesforBusinessDesignOptions
Assumingthatonecanestimatethevolumeofconsumers’purchasesthatarelikelyateachpricepoint(oruse marketresearchtodoso),themodelcouldbeusedtocapturetheeffect(onsalesrevenues)ofeachoption.Figure 1.5showsanexample.
(Notethatforsimplicityofpresentationatthisstage,themodelhasbeencreatedthreetimes,i.e.ineachcolumn C,D,andE,usingthecommonsetoflabelsincolumnB.However,inpracticethemodelwouldtypicallybe createdonlyonce,andasensitivityorscenarioanalysiswouldbeusedtoshowthedifferentcases.)
Also,ofcourse,inareal‐lifesituation,therewouldalmostcertainlybemorefactorstoconsider.Forexample,the decisiontolaunchthebusiness(andwithwhichdesign)wouldnodoubtalsoneedtoreflectthecostsandthe potentialtomakeaprofit(notjustthesalesrevenue).Modelsshouldthereforebedesignedsothattheyinclude andevaluatethekeydecisioncriteria(asdiscussedindepthin Chapter2).
Thesetofapplicationsofmodelingistoonumeroustolistfully,butincludesforecasting,businessplanning, investmentanalysis,valuation,targetsetting,creditassessment,portfolioselection,andoptimization,andrisk analysis.Withineachofthese,therearemanyvariations,sincemostbusinessesordecisioncontextshavespecific featuresthatneedtobecaptured.Nevertheless,therearenumerousconceptswhicharecommontomany situations,aswellascalculationsandtypesofstructuresthatarefrequentlyusedinExceltoimplementthese.The focusofthistextistotreattheseessentialsindetail.
2.1 INTRODUCTION
Modelingactivitytakesplacewithinanoverallcontextandawidersetofbusinessprocesses.Atahighlevel,the mainstepstoconsiderwhenplanningandbuildingafinancialmodelfordecisionsupportare:
Identifyingthedecisionanditsstructure,options,andcriteria.
Mappingtheelementsofreal‐lifethatshouldbecaptured,includingthevariablesandlogicflow
Buildingandtestingthemodel.
Usingrelevantexternaldata.
Usingtheresults,includingpresentation,graphics,sensitivityanalysis,reports,anddocumentation. Thischapterexploresthesetopics,discussingthecoreprinciplesofeachpointandthemainpracticalissues.Note thatinthischapter,thediscussionisstillquitegeneric;infact,mostoftheprinciplesapplywhetheramodelisto bebuiltinExcelorinsomeotherplatform.However,therestofthebook(fromChapter3onwards)isdevotedto implementingthesewithintheExcelenvironment.
2.2 DECISION IDENTIFICATION, FRAMING, AND STRUCTURE
Amodelisgenerallyusedtosupportadecisionprocessinsomeway.Therefore,itisimportanttoestablishwhat decisionisbeingaddressed,whataretheobjectives,andwhataretheconstraintsorlimitationsthatmustbe respected.
Acommonfailingofdecisionprocessesisknownasthe“fallacyofchoice”:Thisiswherewhatwouldhavebeen thebestdecisionoptionisnotconsideredatall.Clearly,foramodeltobemostuseful,itmustalsoreflectthe relevantdecisionandthemostappropriateorbestoption(s).
Generically,onemaythinkofadecisionashavingabinarystructure(“goornogo?”).Mostcommonly,Excel modelsreflectthis:Themodelrepresentsthe“go”option,whereasthe“nogo”optionisnotmodeledexplicitly (i.e.itisimplicitlyconsideredasbeingneutralorevaluatingtozero).
Itisalsofrequentlythecasethat(withinthe“go”option)therearesetofsub‐optionswhicheachhavethesame structure.Thatis,thereisonlyonemodel,andthesub‐optionsarecapturedasscenarios(eachsimplyusing differentinputvalues).Ifthereweremajorstructuraldifferencesbetweenthesub‐optionsthenadifferentmodel wouldberequiredforeach(and,inthatcase,theyarestrictlyspeakingnotsub‐optionsatall). Figure2.1 illustratesthisforthesituationdiscussedin Chapter1(seeFigure1.5andtheassociateddiscussion).
Othertypesofdecisionstructuresincludeallocationsoroptimizations(e.g.howmuchcapitalshallweallocateto projectA,andhowmuchtoprojectB?),multiplestructurallydifferentoptions(suchaswhethertorenovateone's house,buyanewcar,orgoonvacation),anddecisionsequences(e.g.usingaphasedapproachratherthanmaking asingleup‐frontdecision).Thesemayrequiremoreadvancedmodelsandtoolstoproperlyaddressthem. However,thecorepointsarethattheappropriatedecisionneedstobeidentifiedandthatthemodelshouldreflect thestructureofthedecisionsituation.
2.4 SENSITIVITY‐BASED DESIGN
Sensitivityanalysisistheexplorationofthechangesthatoccurtothevalueofacalculateditemwhenoneormore oftheinputvalue(s)ischanged.Itisakeypartofdecisionsupport,asitcan:
Helptounderstandtheconditionsunderwhichadecisionmakessense(ornot).Forexample,whileabase casemayindicatethata“go”decisionispreferable(to“nogo”),asensitivityanalysiscouldidentifythatthis istrueonlyifcostsdonotrisebymorethan10%.
Establishtherangeoflikelyoutcomesandgenerallytoassessthepotentialupsidesanddownsides. Identifytherelativeimportanceofthekeyinputvariables,andhencetheeffectivenessofpotential managementactionsthatcouldbeusedtomaximize(oroptimize)theoverallresultwhilemitigatingor reducingrisk.
Aseeminglyobvious–butoftenoverlooked–pointisthatsensitivityanalysisshouldbeconsideredbeforethe modelisbuilt(i.e.asaplanninganddesigntool):Ifitisconsideredonlyafterwards,themodelmayhavebeen builtinawaywhichdoesnotallowthenecessarysensitivitiestoberun!Theapproachtoimplementingsensitivity techniquesvariesaccordingtothestagewithinthemodelingprocess:
Atthedesignandplanningstage,itrevolvesaroundidentifyingaspreciselyaspossiblethesensitivitiesthat willneedtoberunlater.Thiscanhelptodefinethevariablesthatshouldbeincludedinthemodel,theirroles asinputsoroutputs(i.e.thelogicflow),aswellasthelevelofdetailorgranularitythatisneeded.
Whenamodelisbeingbuilt,itcanbeusedtoverifyandtestitsgeneralbehavior,notablybycheckingthat therelationshipsthatarepresentinthereal‐lifesituationarereflectedproperly.Itcanalsobeusedtodevelop andcheckcomplexcalculations,bytestingtheirresultsatvariousvalues(ideallyacombinationofsimple values,extremevalues,andvalueswhicharecriticalinhowtheformulaswouldevaluate).
Figure2.3showsasimpleillustrationoftheuseofasensitivitythoughtprocessinmodeldesignandplanning. WhencreatingamodelforecastofSalesRevenue,therecouldbeseveralfundamentallydifferentapproachesto thechoiceofvariablesandthelogicused.Theseinclude:
VolumemultipliedbyPrice.
MarketSizemultipliedbyMarketShare.
SumoftheSalesRevenueperCustomer(orperproduct,geographicregion,etc.).
Byconsideringthenatureofthesensitivityanalysisthatwillbeneededwhenthemodeliscomplete,oneshouldbe abletoseewhichoftheseismostappropriateorwhatvariationsofoneofthemmayberequired.(Theprocess couldalsohighlightthatnoneoftheseoptionsaresuitableandthatothermodelingapproachesshouldbe considered.)
2.5 DATA AND DATA SOURCES
AsnotedinChapter1,modelsalmostalwaysnotonlycapturetherelationshipsbetweenitems,butalsohaveinput fieldsthatarepopulatedwithrelevantvaluesoractualdata.Insomecases,thevaluesusedfortheinputsmay simplybeabestestimateorajudgmentmadebyan expert(e.g.forabespokeorunusualprojectwherelittle historicalorcomparableinformationisavailable).Insuchacase,theprocessofdataintegrationmaybesosimple thatitmaybarelybethoughtofasonethatrequiresspecificconsiderationortechniques.Ontheotherhand,in veryadvancedapplications(suchasmachinelearning),onemayneedtoautomatethesourcing,manipulation, integration,andanalysisoflargedatasetsthatarestoredonexternalservers,andwhicharebeingfrequently updated.Therefore,whilesomedataanalysisisusuallyrequiredaspartoftheoverallmodelingactivity,itsnature couldrangefromsimpletocomplex,highlyadvancedanalytics.Ingeneral,oneiscommonlyfacedwitha situationthatisinbetweenthesetwoextremes:Frequently,somedataisavailableandneedstobeanalyzedor summarizedtoinformthevaluesusedforthemodelassumptions.Forexample,whenbuildingaforecastingmodel tovalueabusinessbasedonitsexpectedprofitabilityoverthenext5to10years,itmaybesufficienttoforecast theoperatingcostsatanaggregatelevelonly(e.g.thetotalcostbycategoryineachquarteroryear),evenasthe business'sITandaccountingsystemsmaybeabletoprovideamuchmoredetailedbreakdownofthehistorical costofeverylineitem.Similarly,regressionorcorrelationanalysismaybeneededtoexploreordetermine relationshipsbetweenvariablesthatmaybeneededwithinthemodel.
Fromthepointofviewofmodelplanninganddesign,whenfacedwithamodelingsituation,itisusefultoask questionssuchas:
Theoverallroleandimportanceofdatatothemodel.Howmuchdataisavailable,andfromwhattypesof sources?Whatwouldthedatabeusedfor?
Howisthedatastructured?Howmanydatasetsarethere?Wherearetheylocated?Howcantheybe accessed?Howlargearethey?
Isthedata“clean”andconsistent?Howaccurateisit?Coulditcontainerrors,orneedcorrection?Would someformofmanipulationorintegrationberequired?
Howoftenwillthedatabeupdated,andwillthemodelalsoneedtobeupdatedasoften?
Theanswerstothesequestionswillhelptoformaclearpictureofthedataandlogicalstructuresandofthe techniquesthatarerequiredtoimplementamodel.Thesearediscussedindetailin PartVI
2.6 MODEL MAPPING AND APPROXIMATIONS
Theterm“mapping”referstothetranslationofthecoreaspectsofthereal‐lifesituation(andtherelationships withinit)intothemodel.Thisrequiresonetodefine:
Thevariablestobeusedandtheirrole(inputs,calculations,outputs).
Theoveralllogicandflow.
Thelevelofdetail(granularity)ofthese,suchaswhethertherevenuesarebrokendownforeachindividual product,whetherthetimeaxisisannualormonthly,andsoon.
Fromapracticalpointofview,itisoftenbesttostartwithitemsthatrelatetooperationalandinvestmentissues (suchasprices,volume,revenues,operatingcosts,investmentneeds,andsoon)beforecapturingthefullimpactof theseoncashflowsandontheconsequencesforthefinancing.
Themappingprocessisnottobedoneinisolation:Itshouldconsidertheissuesmentionedearlier(notablythe decisionstructureandcriteria,theuseofsensitivityanalysisasathought‐process,andtheavailabilityanduseof
datasets).Theprocessalsorequiresthatthemodelerhasanadequateknowledgeoftheareaofapplication,in ordertounderstandthebehaviorandrelationshipswithinthereal‐lifesituation.Forthisreason,thistextaimsto providesomecoreelementsofthisforarangeofcommonapplicationareas.(However,inaspecificcasethatone isfacedwith,itmaybenecessarytodoextraresearchorstudyspecificconceptsineconomics,finance,or accountinginmoredepth.)
Amodelisusuallyanapproximationorasimplification:Therearetypicallyaspectsofasituationthatcannotbe capturedinfulldetailorrarescenariosthatmayoccurinreallifethatarenotreflectedinthemodel.Forexample, ifamodelusespriceandvolumeasitsunderlyinginputs(withrevenuescalculatedasanoutput),thenitprovides notracingback(explanationorcausality)ofthefundamentalfactorsthatthemselvesdeterminethepriceor volume.Forexample,inthecaseofahotelbusiness,anincreaseininterestratesmayreducebusinessactivityand hencereduceboththepriceandthevolumethatmaybeabletoachieve(aswellasincreasingitscostof borrowing).Byextendingthelogicofthemodelfurther“backwards”inthecausalitychain,onewouldcreatea larger,morecomplex,andmoredetailedmodel.However,theinputsforthenewmodelarestillonlypotential “startingpoints”forayetlargermodel,andsoon.Thus,eventhemostdetailedmodelisunlikelytoeverbeable tocapturethefullbehaviorofthereal‐lifesituationineverypossible situationthatcouldarise,whilethecreation ofsuchadetailedmodelmayinvolvesignificantwork,andnotnecessarilybemoreaccurate,norprovide additionalbenefitorinsight.Akeyskill(andobjective)istocapturethekeyelementsinawaythatissufficientto fulfilltheparticularpurposeathand,notablytosupporttheneedsofdecision‐makers.Thephrases“Everymodel shouldbeassimpleaspossible,butnosimpler”(adaptedfromaquotebyEinstein),and“Everymodeliswrong, someareuseful”shouldalsobekeptinmindaspotentiallyhelpfulguidelines.
2.7 BUILDING AND TESTING
Thereareseveralprinciplesinvolvedinbuildingmodelsinaccordancewith“bestpractices.”Theseincludeto:
Namethevariablesclearly
Separateinputsfromcalculations.
Createtransparencyandclearlogicalflows.
Breakcomplexcalculationsintocomponents.
Usemodularstructures(largelyself‐containedthatcontaincloselyrelateditems,andwhicharelinked togetherwitharelativelysmallsetoflinkstocreateawhole).
Testthemodelrobustlybeforeapplyingit.
Theimplementationoftheseprinciplesisoftenspecifictothemodelingplatformusedandarethereforediscussed indetailinthecontextofExcelinChapter3.
2.8 RESULTS PRESENTATION
Thecoreprinciplesofusingmodelresultsfordecisionsupportinclude:
Presentingthesituationandassumptionsclearly
Highlightingthekeyinformationneedsthatdecision‐makershave,notablytheevaluationofthedecision criteriaandrelatedreportingneeds.
Showingtherangeofpossibleoutcomes(e.g.throughsensitivity,scenario,orriskanalysis),andtheleversby whichtheoutcomecanbeinfluenced.
Usinggraphicaloutputasappropriate.Careshouldbetakentoensurethatthemostappropriatetypeofgraph ischosenandthatthemessagethatthechartisintendedtoconveyiseasytoseedirectlyonthechart.
Providingadequatedocumentation.Thisshouldnotethecontextinwhichthemodelisvalid,orwhether adaptationsarenecessaryasthedecisionsituationorcriteriachanges.
Tryingtominimizetheeffectofbiases.
(Onceagain,mostoftheseprinciplesarecoveredinmoredetailin Chapter3,toshowtheirimplementationinthe Excelenvironment.)
2.9 BIASES
Biasescanimpactdecisionidentificationandselectioneveniftherightinformationhasbeengiventodecision‐makersfromatechnicalorobjectiveperspective.Thetopicofbiasesislinkedtohumanpsychology(atthe individualandgrouplevel)andtoevolution.Whileitsscopeisverylarge,importantexamplesofbiasesinclude:
Thebiasof“framing.”Thismeansthat–forthesamesituation–onewouldmakeadifferentdecision dependingonhowtheinformationispresented.
Thebiasof“lossaversion.”Thismeansthatpeoplearetypicallyrisk‐seekingtoavoidlosses,butrisk‐averse inthefaceofgains.
Thesebiasescaninteractoroverlapintheireffects.Forexample,onemaybereluctanttosellaninvestment(such asastock)thathasdecreasedinvalue(i.e.toretaintheinvestmentratherthanrealizingitsvaluebyturningitinto cash).Atthesametime,ifaskedtoincreaseone'sholdingofthesameinvestment,onemayalsobereluctanttodo so.Thatis,onesimultaneouslyprefersholdingtheinvestmentratherthancashandholdingcashratherthanthe investment.Similarly,abusinessdecisionaboutwhethertocontinuetoinvestinapoorprojectcanbeaffectedby whetherthefocusisonloss‐avoidance(“Wehaveinvestedsomuchthatwecan'tstopnow.”)ratherthanpotential gains(“Let'sinvestinthefutureofthishighlyuncertainproject–afterall,itcouldturnoutwellintheend.”).An awarenessofpotentialbiasescanbeimportanttominimizingtheireffect.However,afulltreatmentofthisrich topic(andrelatedaspectssuchasbehavioralfinance)isbeyondthescopeofthistext.
3.1 INTRODUCTION
Excelisgenerallyanexcellentplatformformostcommonfinancialmodelingapplications.Itiseasytolearnata basiclevelandcanprovideatransparentandflexibleenvironment(ifusedwell).Ithasmanyfunctionsand features,andnewfunctionalityisaddedregularly.Incaseswhereonemayneedtoconductoperationsthatarenot presentinthedefaultversionsofExcel,thesecanoftenbefoundinthefreeshippedadd‐insorbyusingVBA macros.(However,mostoftheapplicationswherethesewouldbeneededarebeyondthescopeofthistext.) Finally,althoughthereareareasofapplicationwhereExcelmaynotbeapracticaltoolforthefinal implementationofasolution(suchasifveryhighcomputationalintensityisrequiredtomanipulatehugedatasets extremelyquickly),itcanoftenbeaconvenientplatformtocommunicateandtesttheconceptsortodevelop prototypesforthesesolutions.
ThischapteraimstohighlightthefeaturesofExcelthatareindispensabletoanymodelerworkingincommon areasofapplication.Itisintendedtobeastructuredsummary,notonlytoactasaguideforreaderswhoareless familiarwithExcel,butalsotoformachecklistofcorecompetencesforthosewhoalreadyhavesomeexperience. Thetopicscoveredrelateto:
Thestructureandmenus.
Thecreationofcalculations.
Themostimportantandfrequentlyusedfunctions(around100arehighlighted;thesearethefunctionsused laterinthetext).
Thecoreoperationsthatareneededtobuild,test,anduseamodel.
Calculationoptionsandsettings.
TheuseofshortcutsandKeyTips.
Bestpracticesandthecreationoftransparency.
Auditing,testing,andthetracingoflogic.
Usinggraphsandcharts.
3.2 STRUCTURE AND MENUS
SomeofthekeypointsaboutthestructureofExcelare:
AnExcelworkbookcontainsoneormoreworksheets.Eachworksheetismadeupofatabularsetofcells, whichareidentifiedbyacolumn‐rowreferenceaddress(suchasC5,toindicatethecellinthethirdcolumn andfifthrow).
Eachcellmaybeblankorhavecontententeredintoit,suchasanumberortext.Formulasorfunctionsmay insteadbeentered;theseconductcalculationsthatgenerallyreturnnumbers(butcanreturntext,logical items,orerrors).
Typically,tocreateacalculationinacell,theinputsarethevaluesthatarepresentinothercells.(Fixed numberscanbeembeddedwithinformulas,butitisingeneralnotgoodpracticetodoso.)
Asetofcellsthatformsarectangularstructure(orrange)canbereferredtobyusingthecelladdressofthe firstandlastcells.Forexample,C5:E9referstotherectangularrangewhichstartsatcellC5andendsatE9. Thisallowsformultiplecellstobereferredtosimultaneously.
Aswellasitsvalueorcontent,acellorrangehasotherproperties.Theseincludethesizeandcolorofthe font,thetypeofshading(ifany),andthetypeofborderplacedaroundit(ifany).
TheExceltoolbarcontainsseveraltabs,eachofwhichhasadescriptivetitlethatrelatestothetypesofmenu operationsthatareofferedbytheitemsonthetab.ThecoretabsarepresentbydefaultwhenExcelisfirstinstalled (seeFigure3.1).
Figure3.2showstheleft‐hand‐sideoftheHometab,whichcontainsmanyfrequentlyrequiredoperations.
