Spreadsheet Conventions for Financial Models

Document Sample
Spreadsheet Conventions for Financial Models Powered By Docstoc
					                   Spreadsheet Conventions for Financial Models

 Caution: You do not always have to conform to these conventions – Foolish consistency is the hobgoblin of a petty mind




                                                                                                            1
Spreadsheet Conventions for Financial Models
 Model Layout and Structure


• If somebody else picks up your model, can they easily verify that
               correct.
  the model is correct

            If other people cannot work through your model and
            understand it in a couple of hours, there is a problem

• When you pick up your model after not using it for a couple of
  months, can you replicate and explain it.

            If you start sweating when you think about opening the
            model, there is a problem.




Spreadsheet Conventions for Financial Models                     2
    Layout and Review


•    If you are reviewing a model, you would like:
            To see what the inputs to the model are (you do not want to
            search around spreadsheets to find the model results).
            To be able to quickly understand calculations in the model.
            To be comfortable that the model does not have errors.
            To add calculations of added revenue and expense, deferred
            debits and other components to the model (add rows to template
            models)
            models).
            To supplement the model with additional time periods (add
            columns to template models).
                                              analysis,
            To be able to perform sensitivity analysis scenario analysis and
            Monte Carlo simulation
            Re-use the model in future years.




Spreadsheet Conventions for Financial Models                              3
    Objectives of a Well Designed Model


•    A well structured model:
            Should b fl ibl (easy t change f
            Sh ld be flexible (       to h              l      d l delays, added expense
                                                formulas, model d l         dd d
            items, alternative forecast horizons, different assumptions)
            Should be presentable and transparent in a way that is convincing to
            readers of the model that it is accurate
            Should allow risk analysis with sensitivity analysis, scenario analysis, break-
            even analysis and Monte Carlo simulation
            Should model alternative decisions (investment, lending, contracting,
            selling)
            Should effectively present results where the key drivers and the key outputs
            are clearly demonstrated
            Should be accurate as demonstrated by balancing accounts, extreme
            scenarios and other tests
            Should be stable where macros, range names or circularity do not create
            errors in the model




Spreadsheet Conventions for Financial Models                                      4
    Programming, Accounting and Engineering


•    Using excel with if statements, combo boxes, range names, look-up tables, macros
     and switch variables is a necessary, but easy to learn skill
•    Construction of a basic financial model requires knowledge of accounting principles
     -- cash flow, depreciation, balance sheets and financial ratios
•    Working through value drivers requires knowledge of how the business process –
                 value,
     what drives value how the cost components are developed and how the production
     process is modeled …

• Effective Modeling
           Mechanics and Accounting 25%
           Assumptions and Value Drivers 45%
           Presentation 15%
           Auditing 15%




Spreadsheet Conventions for Financial Models                                    5
    Spreadsheet Organisation and Conventions


•     Organization:
                                                           y
              Model drivers and cash flows should be clearly laid out
              Inputs should have a different colour (Colour and then Move)
              Periods in different sheets should correspond to the same column
•     Simple Formulas
              Use max and min instead of if statement
              Financial statements should only have simple formulas
•     Financial Calculations
              Positive number convention
              Use switches and cork-screws
•     Excel Tools
              Named ranges may be appropriate for variables which have a single value such as the
              life of the plant
              Try to avoid circular references
              Describe column titles and macros in detail




Spreadsheet Conventions for Financial Models                                             6
Model Organisation




                                               7
Spreadsheet Conventions for Financial Models
 Model Organization


• Clear progression of inputs and assumptions to outputs

• Use of the Financial Model as a Database

• Presentation of assumptions that drive value

• Organization of sheets in financial statements with inputs,
  workings, financial statements and outputs

• Table of Contents and Settings

• Necessary and unnecessary complexity




Spreadsheet Conventions for Financial Models                    8
 Do Not Make Important Inputs Difficult to Find and
 Adjust

• The key drivers should be easy to change and should be easy to
  find

            All inputs should be in one or a few sheets and should be
            coloured

            Don’t put the key inputs in sheets that are far from the
            beginning

            The inputs should be easy to change (use combo boxes or
            spinner boxes so the inputs can be changed from more
            than one sheet)




Spreadsheet Conventions for Financial Models                      9
    Example of Spreadsheet Organisation and Database of
    Financial Information
•    There is no set rule for organising spreadsheets. But the following list provides and example of
     how a financial model is a database of financial information on the value of a company.
              Historic Financials
              Assumption Book
              Revenue Working                          Virtually all of the information that
                                                       drives the value of a company can
               p      g            g
              Operating Cost Working
                                                       be found somewhere in the
              Other Working                            model.
              Aggregation
              Financial Statements
              Financial R ti
              Fi    i l Ratios
              Valuation
              Graphs
              Input Back-up
              Background Sheets




Spreadsheet Conventions for Financial Models                                                 10
    MODEL ORGANISATION: Meaning of Colours in
                              g
    Inputs and Outputs
•    Colorus should mean something that makes the spreadsheet easier to
     follow

            Inputs in a color

            Links to another sheet

            Links from another sheet

            Key outputs

• Don’t Simply Colour to Make the Spreadsheet Pretty!

• “Formulas should not bounce from sheet to sheet without
        direction
  clear direction” Collect items earlier sheets at the top of
  subsequent sheets.




Spreadsheet Conventions for Financial Models                      11
 Example of Moving Between Sheets

                                                            Macro named
                                                            spreadsheet colors all
                                                            sheets will perform this
                                                            function automatically




                                                  Colors
                                               correspond
                                                 to sheet
                                                  names




Spreadsheet Conventions for Financial Models                                12
 Colors Should Mean Something




                                                         yellow, red,
                                               There are yellow red blue and
                                               black codes.


                                               There is no documentation of
                                               what the colors mean




Spreadsheet Conventions for Financial Models                   13
 Example of Non-transparent Model – No Idea where
 projections come from




             What is the value
             What drives value (sales,
             ROIC, etc.)
             What are inputs and what are
             formulas



Spreadsheet Conventions for Financial Models    14
    Sheet Organization and Table of Contents

                                                                      Too Many Sheets,
• PUT THINGS IN ORDER!                                                  Not Organized

•    Investment banks like many sheets so that you can see
     the components of each page
            Many sheets can be easy to to print
               •   Each sheet prints as page
               •   Too many sheets can make the links difficult to
                   follow
            No clear rule on the number of sheets
            The sheets should be organized in a logical
            order
               •   Input sheets
               •   Working Sheets
               •   Financial Statements, etc
                                                                     Use the Table of
            For consolidation in mergers, keep at least all
            financials on the same page                              Contents Macro
                                                                     Provided on the CD




Spreadsheet Conventions for Financial Models                               15
Model Organisation: Make Inputs Easy to Find and
Calculations Easy to Follow




                                                   16
Spreadsheet Conventions for Financial Models
 Making Calculations Easy to Follow


• Inputs should be on a single sheet

• Drivers should be gathered together

• Show outputs next to inputs to make sure the model is working
              boxes,
  using combo boxes spinner boxes and scenario titles

• Repeat combo boxes in different sheets and key outputs in
  different sheets whilst developing the model

• Don’t over-use fancy excel formulas and excel functions when
  you could solve the same problem with a simple function

• Present the formula components so that the trace precedent and
  trace dependent can be used




Spreadsheet Conventions for Financial Models              17
    When Presenting Financial Ratios, Present the
    Numerator and the Denominator

•    The numerator and the denominator are shown so that the reader can see
                                      made.
     precisely how the calculation is made




Spreadsheet Conventions for Financial Models                      18
    Use Data Tables with Row Incidies to Describe when
    Values Occur

•    Show the explicit value so one
     does not have to guess what cell
     the value goes into.


                             One does not know
                             when the long-term
                             and when the short-
                             term occurs

                                                   Corrected C
                                                   C                 Use table
                                                          t d Case – U a t bl
                                                   with the year specified




Spreadsheet Conventions for Financial Models                      19
    See the Results of Inputs and Outputs on the Same Page
    – Keep key results and inputs in sight using Boxes

•    Often, a spreadsheet will have settings for valuation methods, covenants,
                                  forth
     interest rate methods and so forth. It is nice to see the how the results vary
     when inputs change, even though the inputs should still be on one page. In the
     example below, the sensitivity factors are shown along with the graph and the
     IRR’s, so that you can see what happens when you change the factor.



While working on a model,
                    sight.
keep key results in sight
                                                               When you use boxes to test
                                                               sensitivity analyses and to
This can be done with                                          develop risk analysis, make a
spinner boxes and combo                                        macro that re-sets the
b
boxes                                                          assumption to base case
                                                               values




Spreadsheet Conventions for Financial Models                         20
Model Formatting: Comments, Column Set-up




                                               21
Spreadsheet Conventions for Financial Models
 SPREADSHEET CONVENTION: Setting-Up Sheets
                               g p
 with Indentation Columns
•    Add Three or Four Narrow Columns at the beginning of the
     sheet
•    Make a long column for the name of the row
•    Add a column for units
•    Add a column for formula definition
•    Make all time periods start in the same column in different
     sheets




Spreadsheet Conventions for Financial Models                   22
    Formatting


•    Use rules from accountants

            Rule of lines

            Coding of Accounts

            Double Entry

•    Use grouping instead of hiding rows or columns. This can be done by
     holding the SHIFT, ALT,    keys.

•    Title accounts as either detailed, or total and use the auto filter selection
     from the data option.




Spreadsheet Conventions for Financial Models                               23
   Effective Use of Comments


  • While using comments is obvious, using comments that describe
                                         effective.
    what a spreadsheet does can be very effective I suggest using
    a comment in the A1 cell of each sheet as illustrated below.



A comment in A1
that describes what
the sheet does is a
pain, but it is worth
p ,
it later on and it
forces each sheet
to be organised.


Note how you can
format the
comment and make
it like a help screen



 Spreadsheet Conventions for Financial Models              24
 Efficient Layout with Indents




Spreadsheet Conventions for Financial Models   25
 SPREADSHEET CONVENTION: Formulas Should
 Be the Same Across Columns

•    If you repeat a column, it should not change the spreadsheet

•    You can use switches to keep the formulas the same for historic
     and future periods

•    An exception is the construction period in project finance models

• “A well build model will use the same formulas
  for historic and forecasted periods without any
  modification




Spreadsheet Conventions for Financial Models                   26
 SPREADSHEET CONVENTION: Formulas Should
 Be the Same Across Columns

• Example: If 2008 is column “H” in the input
  sheet, 2008 should also be column “H” in other
  sheets such as the financial statements.




Spreadsheet Conventions for Financial Models   27
 Formulas Not the Same Because of Blanks




                   If you insert a column or
                   change the life of an asset,
                   the blank columns will not
                   automatically adjust




Spreadsheet Conventions for Financial Models      28
 SPREADSHEET CONVENTION: Keep Row Spacing
                            p      p    g
 Consistent for Aggregation

• A painful part of modelling is aggregating debt issues. To make
                                        problems,
  this less painful and less subject to problems keep the spacing
  the same for different issues as shown below.


                                                           Issue 1 and Issue 2
                                                           have the same format
                                                           so that aggregation is
                                                           much easier




Spreadsheet Conventions for Financial Models               29
    Problems with Inserting Columns – Vlookup and Macros


•    Say you have used a look-up table
     with an index value, a table and
     where the column selected is defined
     in the vlookup function. If you insert a
     column between the vertical index of                    Notice the
     the look-up table and the column                        different
     defined in the look-up table, the                       result of
               f
     results of the look-up table will                       the look-up
     change.
                                                             table
•    Notice that inserting a column
     changed the result
•    A better way is to combine match and
     index functions
•    When using match and index, first
     show the result of the match                      The match and
     statement and show the result of the              index command
     index somewhere on the sheet. Then                is more flexible
     use the index.                                    (allows sorting
                                                       from low to high)



Spreadsheet Conventions for Financial Models      30
 Example of Inconsistent Periods




                  Where there are inconsistent periods,
                  Financial ratios and returns are more
                  difficult.
                  difficult




Spreadsheet Conventions for Financial Models              31
 Corrected Approach with Replacement and Retirement




            Instead of blanks, this
            sheet has formulas with
            zeros.


                                               Automatically retires and replaces
                                                            assets




Spreadsheet Conventions for Financial Models                                        32
 SPREADSHEET CONVENTION: Labels should be
 Consistent, Concise and Have Similar Names

• Choose names that are as concise as possible. Names greater
       10 15
  than 10-15 characters become cumbersome to type and more
  prone to errors.
                              Fleet Proforma

                              747-200F                         Note difference in the way
                              MD 11
                              MD-11           MD 11
                                              MD-11             737 s
                                                                737’s are defined and no
                              MD-11F          MD-11F           definition of 747 in second
                              A340            A340            column. This makes use of
                              A300-600R       A300-600R
                              A319            A319
                                                              look up tables very difficult
                              A320            A320-200
                              A321            A321
                              B737-200/300/700B737-300
                              MD-82           MD-82
                              MD-90           MD-90
                              Regional jets   Regional jets
                              Other-TU204     Other
                              CRJ




Spreadsheet Conventions for Financial Models                             33
 SPREADSHEET CONVENTION: Repeating Inputs
                           p     g p
 to Make Calculations Clear to Readers

•    In Order to Keep Formulas Simple, it can be beneficial to repeat
     the inputs

          Inputs are near the formula

          Inputs are also in a separate sheet allowing for value drivers
          etc.




Spreadsheet Conventions for Financial Models                     34
Simple Formulas




                                               35
Spreadsheet Conventions for Financial Models
    SPREADSHEET CONVENTION: Keep Formulas as
                               p
    Simple as Possible
•     Keep formulas simple

          Separate complex formulas into components

          Document the flow of the formulas
            • Manager should be able to read the printouts and understand the model

            • Show the formula computations in a cell

          Make the presentation transparent by showing components near the
          formula

          Formulas should not extend past the formula box

          The longer the formula, the likelier it is that a sign could be reversed
          when calculating things such as interest expense and interest
          income.




Spreadsheet Conventions for Financial Models                               36
 Keeping Formulas Simple


• When you first program a sheet, the formulas are often too
  complicated

• This occurs when you add features to a spreadsheet

• Sometimes there are macros when the computations can be
  accomplished in other ways.

• It is easier to add classes for different types of plant, sales, etc.,
                  interpolate
  than to try an interpolate.

• Bad Cell Risk

                                    environment,
            When working in a team environment it is advisable to
            assign one person to be responsible for the master
            spreadsheet and for making all changes and updates.



Spreadsheet Conventions for Financial Models                      37
 Try not to Make the Models and Formulas too Complex


• In reviewing a model, it is a lot easier to audit relatively simple
  formulas.
  formulas

            Show revenues, expenses and capital expenditures
            separately

            Present the financial statements

            Compute various outputs

• Complex models with range names, macros, long “if” statements,
  numerous sheets can be difficult to review

  Don t
• Don’t try to do multiple things in one section of the spreadsheet




Spreadsheet Conventions for Financial Models                     38
 Example of Too Complex Formulas




       When formulas are this long, it is
       very difficult to audit them




Spreadsheet Conventions for Financial Models   39
 Use of ALT-ENTER to Layout complex Formulas


• If you cannot avoid complex formulas, you can use the ALT-
                       lay-out
  ENTER command to lay out a complex formula and make the
  components easier to read.


Putting the formula
on different lines
can make a long
formula somewhat
          read.
easier to read


The ALT,ENTER
creates a line
break.




Spreadsheet Conventions for Financial Models            40
 Example of Complex Formula




                            If Statement and Difficult to Follow Look-up
                            tables




Spreadsheet Conventions for Financial Models                               41
 Documented Lookup Table




                                               Look-up table inputs are near
                                               the look-up table and are
                                               illustrated,
                                               ill t t d range names are
                                               used




Spreadsheet Conventions for Financial Models                                   42
 Simplified Formula




                                     Formula is still a little complex, but it is
                                     illustrated from lines above and it is
                                     documented with range names




Spreadsheet Conventions for Financial Models                                        43
 Try Not to Use Too Many Excel Functions


• Excel functions can be very helpful, but they can cause confusion
                              don t
  if people reading the model don’t have the same background as
  you.




Spreadsheet Conventions for Financial Models                44
 More Readable Excel


• The following example still uses the excel commands, but at a bit
  more understandable




Spreadsheet Conventions for Financial Models                45
 Example of Non-Transparent Calculation

                                                              You do not know which Rate is the driver of
                                                              revenues.


                                                              You do not know whether to change one
                                                              year or multiple years


                       Year
                                                 0        1           2           3            4            5

   Colocation revenue
   Total Area allocated to colocation    5,000             5,000       5,000       5,000        5,000        5,000
   Rate per Sq Ft (indicative)           2,800               980       1,400       1,960        2,240        2,520
   Number of Racks                         700               700         700         700          700          700
   Rate per Rack                        20,000            20,000      20,000      20,000       20,000       20,000
   Occupancy                             100%                35%         50%         70%          80%          90%

    Colocation revenue                                  4,900,000   7,000,000   9,800,000   11,200,000   12,600,000



                                                     Calculations are not in logical order
                                                       Which quantities and prices determine
                                                       the revenues of 20,000 – You need to know
                                                       which one drives the revenues




Spreadsheet Conventions for Financial Models                                                             46
   Improved Sheet with Logical Order and Coded Inputs

                             Re-order to show
                            calculation process
  Year                                                             0        1           2             3             4             5

  Colocation revenue
     Number of Racks                            No         700                  700          700           700           700           700
     Rate per Rack                              AED     20,000               20,000      20,000        20,000        20,000        20,000
     Occupancy                                  Pct      100%                   35%         50%           70%           80%           90%
          Colocation revenue                                              4,900,000   7,000,000     9,800,000    11,200,000    12,600,000
     Total Area allocated to colocation         Sq Ft    5,000                5,000       5,000         5,000         5,000         5,000    Average
           p    q (indicative)
     Rate per Sq Ft (          )                AED       ,
                                                         2,800                  980       1,400
                                                                                           ,             ,
                                                                                                        1,960          ,
                                                                                                                      2,240          ,
                                                                                                                                    2,520       ,
                                                                                                                                               2,800

  Managed services revenue
     Number of Racks                            No          100                 100         100           100           100           100
     Rate per Rack                              AED     200,000             200,000     200,000       200,000       200,000       200,000
     Occupancy                                  Pct       100%                   5%         10%           15%           20%           30%
          Managed services revenue                                        1,000,000   2,000,000     3,000,000     4,000,000     6,000,000
     Total Area allocated to managed services   Sq Ft    1,000                1,000       1,000         1,000         1,000         1,000    Average
     Rate per Sq Ft (indicative)                AED     20,000                1,000       2,000         3,000         4,000         6,000     11,400

  Total Revenues
      Colocation revenue                        AED                       4,900,000   7,000,000     9,800,000    11,200,000    12,600,000
      Managed services revenue                  AED                       1,000,000   2,000,000     3,000,000     4,000,000     6,000,000
          Total Revenue                         AED                       5,900,000   9,000,000    12,800,000    15,200,000    18,600,000




                                                                  - Inputs in a different colour
                                                                  - Sheet in a different colour
Indents                                                           - Add indents and units
                                                                  - Move to first sheet
                                                                    Re order
                                                                  - Re-order so can see how calculations are made




Spreadsheet Conventions for Financial Models                                                                        47
    Example of Long Formulas and Other Problems


•    There are a couple of problems
     here




                                               Inputs should be
                                               in color

                                                                            Formula Difficult to
                                                                            Follow and Too
                                                                            Long




                                                                  Formulas with
                                                                  blanks

Spreadsheet Conventions for Financial Models                           48
    Multiple Links from the Same Source
                                               Before you
                                               delete a
                                               number, use
•    If you are re-using a number              the trace
                                               dependent
                     source,
     from an initial source link to the
                                               function
     original number in the source
     rather than using links to the
     prior link. This enables the trace
     precedent and trace dependent
     keys to be used more effectively.




     With direct links to the
     source instead of indirect
     links, the dependent
     functions work more
     efficiently




Spreadsheet Conventions for Financial Models   49
    Use of Arrows for Auditing


•    Some people believe strongly in using the precedent and dependent arrows. For
     example,                  cell,                                          cells.
     example before deleting a cell check whether it is a dependent for other cells

•    You can also use the CNTL, [ and the CNTL, ] key stroke sequence.

•    In the example below, the trace dependent shows that deleting a cell will cause
     problems


                                                                        Use the trace
                                                                        dependent and
     Notice the trace                                                   make sure
     dependent buttons                                                  nothing depends
     are shown on the                                                   on the cell
     excel bar                                                          before deleting a
                                                                        cell.




Spreadsheet Conventions for Financial Models                          50
 Put Things in Correct Order in Cash Flow Waterfall


• Modeling a cash flow waterfall can be complex

            Defaulted debt

            Covenants

            Debt Service Reserves

• Show details of minimum and maximum tests

• Put the waterfall in logical order

• Make calculations refer to lines above, if possible




Spreadsheet Conventions for Financial Models            51
    Waterfall Example


•    The example below illustrates the model of a cash flow waterfall. Note that you
     should put the cash balance back into the waterfall and then subtract it at the
     end of the waterfall.




Spreadsheet Conventions for Financial Models                          52
 Illustration of Problems with Formatting of Waterfall




                                               This example shows a typical
                                               example of a cash flow page
                                               that does not order the
                                               waterfall. It is very difficult to
                                               determine the default
                                               probability on the loan without
                                               seeing the cash flow to each
                                               issue.




Spreadsheet Conventions for Financial Models             53
 SPREADSHEET CONVENTION: Max, Min rather
                            ,
 than If Statements

• When possible, use max or min statements rather than if
  statements

            The max and min statements are easier to audit
               • If statement

                     – If (EBT > 0, EBT x tax rate, 0)
                     – Max(EBT x tax rate, 0)
                                                        through,
            The max and min are more difficult to think through but
            much easier to audit later on




Spreadsheet Conventions for Financial Models                   54
    SPREADSHEET CONVENTION: Positive Number
    Convention
•    In the financial statements, input all formulas as positive numbers, even if
     the item is a subtraction from net income or cash flow.
            For example, even though capital expenditures and debt re-
            payments are a reduction in cash flow, the numbers should be
            positive and the totals should subtract the numbers
•    This is helpful for audits and makes sub-totals and summations much
     simpler – you don’t have to remember whether to add or subtract the
     numbers.
•                                                         (e.g.         W.C.)
     This does not mean that every number is positive (e g negative W C )
     The idea is that subtotals should be consistent with the logic of the
     calculation – e.g. capital expenditure is a subtraction from cash flow.

      e         the positive u be convention, show
• When you use t e pos t e number co e t o , s o what        at
  is deducted and added to the total in the title of each row.




Spreadsheet Conventions for Financial Models                            55
    Example of Positive Number Convention


•    In this example, the draws are
     added and the repayments are
     subtracted even though all
     numbers are shown as positive
     numbers.




Spreadsheet Conventions for Financial Models   56
    SPREADSHEET CONVENTION: Model Switches

•    Models often have switches
            Switch for ti    i d
            S it h f time periods
               • Time period of explicit cash flows in valuation
               • Time period for terminal value in valuation
                 Construction period in project f
               • C                              finance model
            Tax carryforward methods
            Interest Timing
            Depreciation Methods
            Dividend Methods
•                       g                       y
     Put the Switches Together and Make them Easy to Understand
•    Use logical statements to create switches




Spreadsheet Conventions for Financial Models                       57
 Use Excel Conventions


• Excel Uses a Number of Conventions such as:

            Logical Variables
               • True = 1

               • False = 0

            Row first and Column Second



            For example, in setting a switch from a question – make
            sure that true is 1.

            For example, in reading in a table, use the row first and
            column second convention



Spreadsheet Conventions for Financial Models                     58
 Example of Using Switches




•The sheet below uses
switches to define
various calculations.
The switches are part of
the calculation where
TRUE = 1 and FALSE =
0.




Spreadsheet Conventions for Financial Models   59
 Example of Switches Difficult to Find




Spreadsheet Conventions for Financial Models   60
    SPREADSHEET CONVENTION: Presentation and
    Use of Cork Screws
•    Cork Screws involve:

            Laying out the beginning and ending balance

            Showing retirements and new additions

            Beginning balance = ending balance from previous period

            Ending balance = Beginning balance + Additions - Deductions

            Cork screws are useful for balance sheet accounts and capacity
            balance
               • LIFO Inventories

               • Number of Planes by Type

               • Debt issues and cash reserves




Spreadsheet Conventions for Financial Models                          61
 Example of Problems without Cork Screws – Cannot See
 Flow of Capacity




Spreadsheet Conventions for Financial Models   62
 Corrected with Cork Screws




                                                Clearly lay out the beginning
                                               balance and the ending balance




Spreadsheet Conventions for Financial Models                    63
 Use of Cork Screws




                                               Beginning Balance of current year
                                                is the ending balance of the last
                                                              year




Spreadsheet Conventions for Financial Models                          64
    SPREADSHEET CONVENTION: Show units in
    working calculation
•     You should show the units in each calculation where the production
                                    developed
      portion of the spreadsheet is developed. The use of units is illustrated
      below:




                                                              Show the units in a
                                                              separate column




Spreadsheet Conventions for Financial Models                             65
Range Name Advantages, Disadvantages and Optimal Use




                                                  66
Spreadsheet Conventions for Financial Models
    SPREADSHEET CONVENTION: Appropriate Use of
                             pp p
    Range Names
•    Some suggest that each cell in a spreadsheet should be named for
     documentation.
•    Problems with Range Names
          Cannot trace the calculations
          Models become unstable
          Difficult to add to models
•    Benefits of Range Names
                    g
          Macros
          Look-up Tables
          Switches
          Combo Boxes




Spreadsheet Conventions for Financial Models                       67
 Moving Between Sheets


•    Given the number of sheets in a financial model, it is important
     to keep track of where the numbers come from:
          Mark which rows are to be transferred to another sheet
            • Use one color for rows that are to be transferred out
            • Use another color for rows that are transferred in
            • Transfer Inputs at the top of the page

                       i                th                      d
          If you are using range names, then use a range name code
          for each sheet
                                                       End of the code is the sheet
            • E.g.                                     where the range name is
                                                       located
                  – INPUT_tax_rate
                  – WORKING_Revenue




Spreadsheet Conventions for Financial Models                              68
 Range Name Conventions


• Document name of the range in the spreadsheet

• Can color named ranges

• Clean-up range names

• Use range names in macros




Spreadsheet Conventions for Financial Models      69
 Use of Named Ranges




   •    Some people believe every range should be named in the
        spreadsheet
   •                                y
        A convention is to name every variable in the sheet with the same
        prefix
   •    You cannot have spaces in named ranges
   •                               Insert, Name,
        Find named ranges with the Insert Name Define Command in Excel

   • If you are using a range name, show the name of
              ,    ,                        p
     the cell, row, column or table in the spreadsheet so
     it is easy to see where the range name is located.



Spreadsheet Conventions for Financial Models                  70
 Example of Range Names




              Example of Using Range Names
                 in the model




Spreadsheet Conventions for Financial Models   71
 Problem with Range Names – Delete a Name and You
 Get Errors




                               Name Box




Spreadsheet Conventions for Financial Models   72
 SPREADSHEET CONVENTION: Circular Reference
 Problems

• Circular Reference Solutions

            Use the Iteration Check Mark on the Options, Calculations
            menu

            Use macro to work through equations

            Compute the equations

• Problems with iteration

            Cannot use the undo key

            Goal seek does not work

            Model is unstable




Spreadsheet Conventions for Financial Models                   73
Spreadsheet Convention: Keep Top of Sheets in Same Format
when Working with Multiple Sheets




                                                   74
Spreadsheet Conventions for Financial Models
 Consolidating Sheets


• With multiple assets it is sometimes more effective to work with
                                       sheet.
  multiple sheets rather than a single sheet

            Keep track of the age of each plant

            Measure the real costs by the age of the plant and then
            inflate the real costs later

            If you are copying the same sheet, do
            not use range names!




Spreadsheet Conventions for Financial Models                   75
Working with Dates in Models




                                               76
Spreadsheet Conventions for Financial Models
    Show the Age and the Number of Operating Days
    Explicitly in the Model

•    A difficult problem in models is coming up with start dates and end dates

            This can become complex if the project begins in the middle of a year.

            Use switches, cork screws and many rows to model the beginning date
            and ending date




Spreadsheet Conventions for Financial Models                           77
Excel Tools




                                               78
Spreadsheet Conventions for Financial Models
 SPREADSHEET CONVENTION: Do not force different
 inputs to be the same

• Example:

            Book life and economic life

            Book depreciation and tax depreciation




Spreadsheet Conventions for Financial Models         79
 Macros


• Keep Complex Macros to Minimum

• Use Range Names in Macros

            Otherwise cannot insert or delete columns and rows

            Makes documentation of macro more straightforward

• Uses of Macros

            Sensitivity analysis

            Goal seek (although can use solver)




Spreadsheet Conventions for Financial Models                     80
 Location of Macros


•    Create a button for macros

•    Keep the macros in separate sheet

•    Keep programs in the same module

•    Put functions between programs so they can be found




Spreadsheet Conventions for Financial Models               81
    Documentation of Macros


•    It is good to
     explain exactly
     what the macro
     does and use
     excerpts from word
     or from power
     point to explain the
     process




                   Insert documentation by
                   creating a word
                   document and copying
                   and then pasting special
                   into a spreadsheet.



Spreadsheet Conventions for Financial Models   82
Auditing and Documentation




                                               83
Spreadsheet Conventions for Financial Models
    Model Checks


•    Standard and Poor’s
            Simple h k           long way t li it errors
            Si l checks can go a l        to limit
            Always keep the operating margin (EBITDA/Sales) in sight
•    McKinsey
            If the ROIC exceeds WACC, then the market value should be
            more than book value
            Steady state growth should occur when the terminal period is
            reached
            Check valuation with the multiples
•    For Project Finance Models
            Keep IRR’s, cash fl
            K    IRR’                d DSCR’s in i ht
                           h flows and DSCR’ i sight
            Project IRR should equal Equity IRR with no debt




Spreadsheet Conventions for Financial Models                           84
    Documentation of Models


•    Document the models with comments, descriptions, range names,
        columns
     or columns.

•    Think about titles of columns and make them as descriptive as
     possible.

•    Include the units on all the columns.

•    It would be good to place comments in each line of a model to
     describe the source of data and the calculation.
                                                                                                                           Titles difficult to i t t
                                                                                                                           Titl diffi lt t interpret
           Date                       06/26/03 Total Generation                 3,393.00
           Hour                           1000 Actual System Lambda
           Hour Number                    1017      Thursday         Peak
           Weekday                           5     Weekday         Mkt Pr $        69.35
                                                Cumulative
                                  Capacity Not   Capacity       Variable  Cumulative PI Generation for       Remaining     Remaining
                                  Dispatched     Available       Cost     OW Load and    UP in Intact         Available    Cumulative     Base Capacity
                                     (MW)          (MW)        ($/MWH)     Transmission   Case (1)            Capacity      Capacity         (MW)
        Presque I l d A l i
        P       Island Analysis   Base - Actual   Sum of prior   Unit Cost sht   Fill w/ low cost
           Presque Isle 9                 3.00           3.00    $     14.15                3.00     3.00            -              -            80.00    6/22/2003   80.00
           Presque Isle 7                 7.00          10.00    $     14.36               10.00     7.00            -              -            80.00    6/12/2003   80.00
           Presque Isle 8                 5.00          15.00    $     14.35               15.00     5.00            -              -            80.00     6/9/2003   80.00
           Presque Isle 6                (2.00)         13.00    $     18.83               13.00    (2.00)           -              -            80.00    6/25/2003   80.00
           Presque Isle 4                 2.00          15.00    $     19.12               15.00     2.00            -              -            50.00    6/10/2003   50.00
           Presque Isle 5                 5.00          20.00    $     19.07               20.00     5.00            -              -            80.00     6/8/2003   80.00
           Presque Isle 3                  -            20.00    $     19.63               20.00      -              -              -            50.00     7/1/2003     -
           Presque Isle 2                  -            20.00    $     36.92               20.00      -              -              -            30.00    6/18/2003   30.00
           Presque Isle 1                20.00          40.00    $     46.00               21.00     1.00          19.00          19.00          20.00    6/21/2003   20.00
                Total                    40.00                                                      21.00                                       550.00




Spreadsheet Conventions for Financial Models                                                                                                      85
 Auditing Suggestions


•    Mechanical Checks

•    Compute Comparative Ratios

•    Test Extreme Values

•    Perform Sensitivity Analysis

•    Compare Forecasts to History

•    Do not be afraid to re-do models




Spreadsheet Conventions for Financial Models   86
    Mechanical Checks


•    Balance Sheet Balances
          Use b () function
          U abs() f    ti
          Check in each year
•    Project Finance
          Positive Dividends
          Ending debt and asset balance
          Source and use statement balance
•    Corporate Model
          Historic Income Reconciles
          Hi t i B l       Sheet Ties
          Historic Balance Sh t Ti




Spreadsheet Conventions for Financial Models   87
 Example of Mechanical Checks




Spreadsheet Conventions for Financial Models   88
 Comparative Ratios


•    There is no magic formula
          Compute and Graph
            • Interest/Debt
            • Depreciation/Gross Plant
            • EBITDA/Sales
            • Dividend Payout Ratio
            • Return on Investment
            • Return on Equity

          Put in very simple cases




Spreadsheet Conventions for Financial Models   89
 Test the Model with Extreme Values


•    Put in 100% Equity and make sure cash flow before financing
     equals free cash flow

•    Put in beginning and end of year to test year fraction inputs

•    Put in large negative cash flows to see what happens




Spreadsheet Conventions for Financial Models                    90
 Auditing


•    Keep Inputs from Formulas in the same sheet
•    Use Range Names for Scalars and Arrays
•    Use Sensitivity Analysis
•    Try M d l ith Extreme V l
     T Model with E t      Values
•    Check Balance Sheet, Sources, Total Construction, Total Debt,
     Ending Balances, Dividends etc.
•    Total Debt Issues = Debt on Balance Sheet
•    Historic Assets = Modeled Assets
•    Historic Income = Modeled Income




Spreadsheet Conventions for Financial Models                91
 Use of Sensitivity Analysis and Tornado Diagrams to
 Check Model




Spreadsheet Conventions for Financial Models     92
 Two Methods for Audit


•    Method 1: Trace the formulas

          Use F2 key



•    Method 2: Test model with extreme inputs

          Example – Move the cash up and down and make sure the
          debt and cash are working




Spreadsheet Conventions for Financial Models              93
Reference: Modeling Concepts




                                               94
Spreadsheet Conventions for Financial Models
 Time Periods in Models


• Reasons for small time increments

            Accurate interest expense

            Accurate revenue or expense accounting

            Timing of capital expenditures




Spreadsheet Conventions for Financial Models         95
    Method for Accounting for Small Time Increments


•    Keep track of dates

•    Insert time annual and periodic time periods at top of the sheet

            Use look-up tables

            Use if statement to increment time periods
               • If prior quarter < 4, increment by 1, otherwise reset to 1

               • If prior month < 12, increment by 1, otherwise reset to 1




Spreadsheet Conventions for Financial Models                                  96
    Use of Sumif to Aggregate Small Time Increments


•     The sumif command can be used to aggregate monthly or quarterly figures into
      annual amounts

•     Sumif (sum range, year, periodic range)

                Use absolute cell reference for the sum range

                Use the absolute reference only on the row number for the year

                Use the absolute reference only on the column name for the periodic range

                                      Year
    Sum Range                                             Periodic Range




Spreadsheet Conventions for Financial Models                               97
 Debt Modeling Concepts


• Use cork screws

• Look-up tables for installments and interest rate

            Look-up tables with TRUE where use less than or equal to
            for the period

• Periodic computations

            In project finance, combine with monthly source and use
            statement

• Test re-payment using min statement and beginning balance

            Min beginning balance, scheduled repayment




Spreadsheet Conventions for Financial Models                  98
Model Checks




                                               99
Spreadsheet Conventions for Financial Models
    More Issues


•     How can we make the models flexible enough to incorporate inevitable
      delays in construction and alternative retirement dates?
•     What are some of the excel rules that guide accurate and efficient
      development of models?
•     How can project finance models be audited to check errors that we will
      make?
•     What should we do to incorporate alternative debt structures and interest
      during construction into the model?




Spreadsheet Conventions for Financial Models                          100
 Model Checks


• Add a balance sheet and check to make sure that it balances

• Lower assumptions and see what happens when there is
  negative cash flow

• Check the final balances of assets to make sure they are
  depreciated

• Make sure the sources of funds equal the uses of funds

• Check the ending debt balance




Spreadsheet Conventions for Financial Models                 101
 Income Statement Checks


Relate various lines from the income statement to the balance sheet:

          Consistency of expenses and revenues with history if possible

          Check average interest rate: Interest expense divided by the
          average debt

          Compute depreciation rate: Depreciation expense divided by the
          gross plant

                          p            p
          Growth rates on prices and expense ratios




Spreadsheet Conventions for Financial Models                         102
 Check the Financial Statements


• Read the financial statements

            Be able to explain trends in EBITDA and Operating cash
            flow relative to historic data

            Evaluate the capital expenditures as compared to historic
            amounts




Spreadsheet Conventions for Financial Models                   103
    Other Checks


•    Dividends should be logical -- there should be no dividends during the
     construction period

•    Logical dividend checks - you must not allow negative dividends

•    Vary the economic assumptions and see if the results change as
                   ’
     expected. Don’t try to explain away illogical results

•    Print out the financial statements and read them. This is old fashion, but
     it is the best check.




Spreadsheet Conventions for Financial Models                           104
 Model Transparency


•    Transparency is not a precise concept, but in general, the model
     should

          Show the key drivers

          Allow an audit that can be performed in a couple of hours

          Clarify what are inputs and what are outputs

          Financial statements should be a primary part of the model




Spreadsheet Conventions for Financial Models                   105
 Simple Example




Spreadsheet Conventions for Financial Models   106
•   Circularity

•   Columns

•   Solver – try by hand

•   Solver – number of iterations

•   Complex

            IDC

            Plant Additions

            Plant Retirements

•   Redundant Stuff

•   Subtotals




Spreadsheet Conventions for Financial Models   107

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:68
posted:9/23/2011
language:English
pages:107