WIXLIB

Axis of Spreadsheet EvilMedium riskLower riskNormally avoid, but there are specific circumstances inwhich they may be usefulRisky if used incorrectly, but can be safely deployed if alldevelopers and users understand and mitigate the risks05VLOOKUP / HLOOKUP06Complex formula Wrongcolumn / row can be referenced if new columns or rows are inserted.Inflexible - cannot look left and cannot be used between sheets.Other appropriate alternatives are available.Difficult for a model user or reviewer to understand and edit.Significantly increases the risk that the formula contains an error.12{Array functions} 0809They are complex and hard for most model usersto understand and amend.If formula is edited and Ctrl Shift Enter is not used, formula willreturn the wrong value.Multi-cell array functions are difficult to control when making changesto worksheet structure (e.g. insert or delete rows / columns).Nested IF statements 13 Don’t refresh automatically so if the source data is updated, they must beupdated manually.Calculations in Pivot Tables are opaque.Significantly increases the file size.10Dynamic named ranges11Merged cells14 All of these functions need to be used very carefully to ensure theyget the intended results, as they use simplified and often impreciseassumptions.The IRR sometimes produces inaccurate results.An approach applying first principles may be more appropriate.Avoidable VBA code Greatly reduces transparency and requires specialist skills to maintainthe model.Very difficult to ensure VBA code will work in all circumstances.Difficult for most people to review.14ROUND, ROUNDUP, ROUNDDOWN15ISERROR, ISERR, IFERRORVery hard for anyone to understand logic.Very risky when editing and difficult to check whether all branches of theformula work as intended.Pivot tables ** Note, 'complex formula' can take many forms (long formula, poor syntax,complicated functions, etc.). A common rule of thumb is any formula with 3 ormore parenthesis should be consideredcomplex.07XNPV / NPV / IRR 16May inadvertently hide mistakes particularly when used on error checks.If rounding is implemented too early in a string of logic, it could causethe results at the end of the logic string to be inaccurate.Excel Error messages are useful and have a purpose – to notify you of errors!These functions hide a wide range of error messages, some of whichyou may need to see in order to know that something has gone wrong.Always test for the specific error (e.g. ISNA).External links Easyfor error to occur if rows or columns are inserted in either the source ordestination file without a corresponding change in the other file.Need to ensure that the links are always referring to the right file,particularly if the file name changes or the file moves.Not transparent to model users and difficult to understand and amend.Difficult to identify (only accessible via the Name Manager).A change in model structure can inadvertently create an error.Introduces risks if used with formulas: may not copy across as expected.Makes it more difficult to select individual rows or columns and to cut /copy / paste cells.PwC Global Financial Modeling GuidelinesDeveloping best-in-class financial models15

Modeling GuidelinesThese Modeling Guidelines exist to assist ensure a high andconsistent level of quality across every model produced. TheseModeling Guidelines are considered to be universal for all models.Note exceptions to these Guidelines may be justified in certaincircumstances; such as, for output and dashboard sheets where theModeling Guidelines conflict with the visual requirements ofthesesheets.OtherComments& SourcesPrinting 6ChecksPwC Global Financial Modeling GuidelinesCellFormatsDeveloping best-in-class financial models17

Modeling Guidelines01 Model Settings02 Workbook StructureInclude general model settings on an inputssheetThe following model characteristics provides importantcontext for all users:2) General model settingsModel nameProject ABCModel owner(s)Ryan NicholasModel status (e.g. draft, final)FinalModel start dateJanuary 1, 2016Model end dateDecember 31, 2025Number total periods10Number of actual periods2Display general model settings on the coversheetTypically the cover sheet is the first worksheet within the model.Displaying general model settings reiterates the importance ofthese settings and helps communicate them clearly.Maintain distinct sheet typesDistinct worksheets assist users orientate through the model; examples ofsuch worksheets include: Cover sheet (which may contain: model overview, notes and tableof contents, navigation) Inputs sheet(s) Calculations sheet(s) Outputs sheet(s) Review sheet (contains independentreview steps, notes and a signoff for eachSection).Table of contents for each sheet and,potentially, each section provides usefulcontextA table of contents for workbooks with several sheets outlines thestructure and composition ofthe underlying workbook.This provides a summary ofthe model as well as navigation.Hyperlinks can be applied to table ofcontents to facilitate easy navigationHyperlinks create efficient navigation. Including a hyperlink back tothe tableof contents ensures any sheet can be accessed within twoclicks.18PwC Global Financial Modeling GuidelinesDeveloping best-in-class financial models19

Modeling Guidelines02 Workbook Structure (cont.)Keep the model content within one workbookwhenever possibleWhere possible, workbooks should be self-contained with noexternal links to other workbooks.If external links must be used, consider Guideline 9.Avoid hiding a worksheetHidden sheets can cause errors or confusion for users.03 Worksheet StructureMaintain key items in the same locationEnsuring key information (such as: title, sub-title, navigation, and errorchecks) is located in the same location provides consistency and aidswith comprehension.Maintain columns with clear and consistent purposesExamples include columns for the following purposes: headers,descriptions, units, comments, sources, error checks, sensechecks,totals and time series.Maintain consistent column widths and labelsfor inputs and calculations of the sameperiodicityThe intent is to maintain column widths and labels consistencyacross all input, calculation and outputsheets where possible.CalculationSheetOutputSheet20PwC Global Financial Modeling GuidelinesDeveloping best-in-class financial models21

Modeling Guidelines03 Worksheet Structure (cont.)Divide sheets into ‘sections’ using clearly formattedsection headingsCreate an organized workbook by using consistent formatting for sectionheadings that are appropriately ordered to clearly distinguishdifferences.04 Cell FormatsFormat in accordance with a formatting keyA formatting key helps provide context over different valueswithin the model (as an example, the difference between all linksand inputsfrom external workbooks vs internal sources).An example of format key is as follows. Note changes and additionsto these defined formats may be required to meet client or modeldemands.Format key User input Link in from external workbook / source Notification Formula (100% formula) Text (100% text and text that includes formula) Link in from different worksheet Hyperlink (navigation) Work in progress / incomplete Error CheckUse cell and sheet protection to control access tocells that are to remain consistentDisplay the format key prominently in the modelThis reduces the likelihood of accidental errors and encourages usersto only change inputcells.Display the format key in a prominent location (such as coversheet) to make it easier for users.Apply sheet tab coloring to differentiate inputs,calculations and outputsEnter inputs once (on an inputs sheet) in themodelColors help model users to quickly identify the purpose of eachworksheet. Use a consistent colorfor each sheet type.Having the same input entered more than once necessitates thatmultiple edits are required as assumptions change. Rather, use alink to the source input cell. Try not to daisy-chain (i.e. link toother links, rather than linking to the source).Different types ofoutputs to have different colors.22PwC Global Financial Modeling GuidelinesDeveloping best-in-class financial models23

Modeling Guidelines04 Cell Formats (cont.)Formatted numbers appropriately (e.g. %, x )and have a units columnApplying consistent number formatting ensures uniformity andhelpsavoid and identify calculationissues.Enter a heading or name (hardcoded text) inthe model onceRepeated headings or names can be linked to one source.Limited choice or binary inputs can use in celllist boxes (i.e. data validation and list)Allows for binary, limited or multiple choices without cell links.Maintain ‘List’ inputs in a dedicated ‘lists’section on an inputs sheetConsistently align all numbers across rows anddown columnsAlignment assists with analysis and checking as anomalies (such aslarge vs small, positive vs negative values, etc.) stand out more readilywhen aligned and are easily comparable. Different alignment may beapplied to numerical values (e.g. percentages, currency, units)vs text.Not all formatting guidelines are applicable onoutput sheetsA named range can be utilized to ensure ‘Lists’ are consistent in thenaming convention and description.Output sheets must visually meet stakeholder requirements and candeviatefrom the formatting in the restof the modelin order to tellthe story.Ensuring ‘Lists’ are maintained can assist ensure all uniquecombinations are captured.Bracket negative numbersBracketing negative numbers easily differentiates between ‘inflows’and ‘outflows’.Negative num bers m5050(50)50Format zeros as dashesThis allows 0s to be visually distinct from 1s in flags.24PwC Global Financial Modeling GuidelinesDeveloping best-in-class financial models25

Modeling Guidelines05 Time Series LabelsCalculate time series flags or counters in oneplace in the model where practicalMaintain consistent time series labels in thesame location for sheets with similar timeseries contentTimingflags serve multiple purposes: indication for the timing of events,toggling cells on/off, and dynamic updating without introducingcomplex formulas.Maintaining consistent labels (such as header, period start date, periodend date, period type (e.g. actual, forecast) and period number ) in thesame location ensures uniformity.Adopt an approach of a single periodicity perworksheet (e.g. do not mix quarterly and annualon a single sheet)Link time series as a calculation to the labels onthe relevant inputs sheetThis avoids the risk and complexity of multiple periodicities.Labels on all other sheets can be direct links to the inputs sheet.Utilizing ‘named ranges’ may be effective in applying consistenttimeseries information and cell location.Calculate time series labels on the inputs sheetusing general model settingsOnce initially calculated, all instances of that same time seriesthroughout the model can be pulled from that initial calculation, timeseries calculations flow throughout themodel.CalculationCreate separate ‘input’, ‘output’ and / or ‘calculation’ worksheets ifmore than one periodicity isused.Summary columns to the left of the main time series are se consistent calculations whensummarizing Monthly / Quarterly toAnnualThisassistswith error identification asinconsistentvaluesareeasierto spot.Do not daisy chainSubtotals, as opposed to SUMIF on thesubtotal, can be more robust.Usechecks to compare Annualsubtotals to Monthly/ Quarterlysubtotals.26PwC Global Financial Modeling GuidelinesDeveloping best-in-class financial models27

Modeling Guidelines06 FormulasAvoid repeating identical calculations inseparate cellsConstruct formulas so they are consistentacross columnsAll formulas should be able to be copied across all columns.Instead, linksubsequent identical calculations to the original formula.As a default, set workbooks to calculate‘automatically’ (not ‘manually’) and notenable iterative calculationsThis is a workbook level setting based on the first workbook open.Calculate formulas which are constant acrosstime periods in the constants columnTo distinguish constant values (i.e. those that do not change over atime series from those that do), maintain a column for constantvalues.This visually distinguishes constant calculations from timeseriescalculations.Avoid cells that contain both a link to adifferent worksheet and a calculationInstead, use conversion factors and flags with inter-sheet linksornamed ranges.Revenue Price QuantityRevenue Revenue 28mmillionsmm300 Inputs'!K240 * Inputs'!K2413.00100300 Inputs'!K240 Inputs'!K241 K99 * K100300 Price * QuantityPwC Global Financial Modeling GuidelinesAvoid circularreferencesCircular references introduce risk intothe model which could result in multiplepossibleoutputs.Circular references are used tocalculate inputs, but are often assumedto be necessary partsof the model’scalculations.There is almost no commercialsituation where a circular referenceis required.Developing best-in-class financial models29

Modeling Guidelines06 Formulas (cont.)Avoid any references to blank cellsUsethe ‘notification’ format and insertzeros to ensure this criteria is met.Avoid hardwired enlinkto otherformulas.Some exceptions include: 1, 0, -1 (and occasionally 12 and 4).Break down complex formula into separate cells/ rowsFor example, when referring to opening balances in t -1 (i.e. openingbalance for the firstperiod).Clearly define each row with a descriptive labelEnsure that all labels adequately describe the row’s content.When formulas are complex, either break the formulas down intosimpler formulas in separate cells/rows or incorporate logic diagramsof the formulas.Include a text label beside a named rangeThis ensures that all named ranges are easilyidentifiable.Flow calculationsfrom left to rightacross columns andacross sheets in theworkbook. However,summary sections maybe placed at the top ofa sheetA common best practice is to differentiate with different formatting (suchas smaller font size & italics).This formatting style ensuresconsistency and efficiency in thecalculations.30PwC Global Financial Modeling GuidelinesDeveloping best-in-class financial models31

Modeling Guidelines07 ChecksInclude a column in each worksheet to sumup error checks in every applicable rowIt’s best practice to include error checks wherever possible. This allowsquick identificationof thesourceof any issuetriggered.Eacherrorcheckrowcan besummarizedinadesignatedcolumn.Include a dedicated section in eachworkbook for sense checksThe sense checks section consolidates all sense checks from every sheetin the model.Sense checks are for ‘rules of thumb’ that indicate model values areoutside normal parameters (e.g. revenue growth is above X%) orfinancial / contractual checks that don’t necessarily indicate modelerror (e.g.covenant tests and negative cash balances).Link overall error and sense check results tothe sheet sub-title on every sheetLink the sheet sub-title and to display the result of any error checks.Include a cell in the freeze panes across each worksheetthat summarizes the error checks in the worksheetE.g. No errors checks triggeredin worksheet, but errorstriggered in workbookMaintain a total error check in the same location of every worksheet.Model users may decide to show: the number of distinct errors; a binaryindication whether there is an error or not in the worksheet; or the actualmagnitude (differences) inerror.Use conditional formatting to show when error &sense checks are triggeredMaintain a dedicated section in the workbook tosummarize all error checksConditional formatting serves as an alarm for the builder(s) anduser(s). Use a distinct cell color to indicate triggered error checks andsense checks.The error checks section consolidates all error checks fromeveryworksheet in the model.Error checks are for logical/mathematical issues that mustbeerrors(e.g. the balance sheet notbalancing).32PwC Global Financial Modeling GuidelinesDeveloping best-in-class financial models33

Modeling Guidelines07 Checks (cont.)08 Comments &SourcesFormat header sheets to change when an error istriggeredAllow for Comments Sources Responsibilitycolumns in input sheetsUseadistinct cell color to indicatetriggerederror checks andsense checks.The location of these columns may vary depending upon specificmodel requirements.E.g. Formatting changeswhen error check is triggered.Make use of the ABS function for error checksThis function simplifies the error check by converting negative topositive values. -- ( ABS( 1 - SUM(T57:T58) ) ErrorTolerance )34Input source comments could include a hyperlink to the source.Include notes describing the workbook and itsoverall content in a cover sheetEnsures check is positiveChangesTRUE/FALSE to 1/0Comments Source Responsibility columns onthe inputs sheet explain input sourcesThe notes can describe the purpose of the model, the intended users,any assumed knowledge and highlight any unusualor complex sections.Named Range: Allows for Excelcalculation engine inaccuracyPwC Global Financial Modeling GuidelinesDeveloping best-in-class financial models35

Modeling Guidelines09 External links10 Printing &ViewingSeparate links external to workbooks throughthe use of a dedicated inputs section / sheetMaintain the same print zoom percentagefor every sheetIn order to reduce the risk inherent in the model, it is importantto centralize and identify all external workbook links.As a general rule, an 85% page view (zoom) may be appropriate.If there are many links to multiple externalworkbooks, centralize their location (such asdedicated input sheet(s))Organize links to an external workbook with a consistent layout.Where external workbook links are used, recalculatethe subtotals and use error checks to ensure that thesource and destination locations remain consistentError checks test that changes to the source file are picked up.Set worksheets view to Normal not Page Break Preview.Cell A1 can serve as the active cell of each sheetbefore finalizing the workbookThe cell that is active when the workbook is saved is what the users willfirst see when they open the workbook. Consider saving accordingly (e.g.the cover sheet, instruction sheet, inputs / assumptions,disclaimer, etc).Use ‘freeze panes’ to keep the titles and timesseries labels in viewUtilize freeze panes to keep important labels in constant viewincluding row headers, titles, column descriptions andchecks.Apply freeze panes to worksheets where a time series label is applied.Model builders mayalso utilize ‘split’ view, however this isonly suggestedifmultipleview paneswith separate sections are going to be utilized.Frozen panes sectionCalculationsHeaderEnsure that time series labels are consistent withlabels in any linked external workbook& Period startSheet check sPeriod endError-Period typeSense-Period counterSectionDescriptionErrorSenseRow Total20172018201920202021202220231 Jan 171 Jan 181 Jan 191 Jan 201 Jan 211 Jan 221 Jan 2331 Dec 1731 Dec 1831 Dec 1931 Dec 2031 Dec 2131 Dec 2231 Dec ForecastForecast-123456781.0%ChecksUnits20161 Jan 1631 Dec 16ActualConstant1) RevenueProduct ARevenueFirst year sales - Product AVolume growth - Product A (low scenario)This ensures consistency between workbooks.20151 Jan 1531 Dec 15Volume - Product Aunits millionsFirst year price - Product A Price growth (low scenario)%Price - Product ARevenue - Product A100%units millions 253.0654.1255.2056.3157.43 m50.005,0005,3045,5725,8546,0916,2756,4646,660Cost of goods soldCost of goods sold % - Product A36PwC Global Financial Modeling %less Cost of goo

financial models built inExcel; however, these principles can be applied across anyfinancial modeling exercise. As the financial modeling industry evolves towards becoming technology agnostic, PwC has developed new methodologies so it can deliver and review models in manytechnologies. Finan