Document Sample

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 |

OTHER DOCS BY liaoqinmei

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.