INSEAD Library

Digitized

Table of Contents Introduction 1 WHAT IS A FULLY INTEGRATED MODEL?.............................................................................. 1 STEPS IN CREATING A MODEL .......................................................................... ................. I HOW TO USE THIS REFERENCE GUIDE ................... ........................................................ 1 Modellng Baslcs 3 ACCESSING THE MENU WITH THE KEYBOARD ..................................................................... 3 ....... .............................. 3 OTHER ESSENTIAL KEYBOARD SHORTCUTS ................... Selecting cells with the sticky shift key ................... .................................................................. 4 4 Moving across worksheets ......................................................................................................... ............................................................4 Writing a formula with the keyboard ................... . . 5 Writing a cross sheet formula with the keyboard............................................................................ Copying formulae ......................................................................................................................... 6 MODELING FORMULAE .................................................................................................................. 6 Subtotals ....................................................................................................................................... 6 Growth formulae ............................................................................................................................. 8 % of formulae .................................................................................................................................. 9 Base analysis ................................................................................................................................ 9 10 CELL COMMENTS....................................................................................................................... Inserting a cell comment ..................... .................................................................................. 10 .................................................................................... 11 Viewing a cell comment ............ Deleting a cell comment............ ....................................................................................... 11 Setting u p the Model Template 13 SETTING UP THE TEMPLATE FOR A SINGLE SHEET MODEL .................... ............... 13 15 SETTING UP THE TEMPLATE FOR A MULTI-SHEET MODEL..................................................... . ....................................................................................................... 16 MATRIX INTEGRITY........... . CELL FORMATS ........................................................................................................................... 16 Business comma................................................................................................................... 17 Percent format ............................. ............................................................................................ 20 23 Historical numbers style.............................................................................................................. Input style...................................................................................................................................... 25 Applying a style to a cell ............................................................................................................... 28 Merging styles...............................................................................................................................29 .. . 31 Input Historical Numbers INPUTTING INCOME STATEMENT HISTORICAL NUMBERS...................................................... 31 Depreciation................................................................................................................................ 31 Amortization ...............................................................................................................................32 Interest income and interest expense............................. ........................................................... ... 33 ........................................................................................ 33 Putting it all together INPUTTING BALANCE SHEET HISTORICAL NUMBERS ....................................................... 33 Calculating Ratios and Bulldlng Assumptlons 35 CALCULATING RATIOS............................................................................................................... 35 35 BUILDING ASSUMPTIONS ............................................................................................................. 37 Projecting the Income Statement SALES I REVENUE.................... . .................................................................................................. 37 COST OF SALES (COGS) ............................................................................................................... 37 DEPRECIATION............................................................................................................................... 37 . ................................................................................................. 39 GROSS PROFIT ......................... 39 SGandA ................................................................................................................................................ AMORTIZATION .............................................................................................................................. 40 OPERATING PROFIT ......................................................................................................................40 NON-RECURRING ITEMS........................................................................................................... 40 41 INTEREST INCOME AND EXPENSE.............................................................................................. . ..................................................................................... 41 PROFIT BEFORE TAXES ................... . TAX 41 .......................................................................................................................................... . . . . . ................................................................................................................................... . . Modeling Modeling ....... ...... . . ....................................................................................................... .. . . . . ..................................... .......................................................................... ..................... . . ............................................................................... ..................................................................................................... NET INCOME ................................................................................................................................. 42 SHAREHOLDER INFORMATION................................................................................................. 42 Basic and diluted shares outstanding........................................................................................... 42 43 Basic and diluted earnings per share (EPS) ............................................................................. Dividend per share ........................................................................................................................43 Projecting the Balance Sheet ...........................................................................................................45 SALES-DRIVEN ACCOUNTS .......................................................................................................... 45 46 NET FIXED ASSETS (NET PPBE) ................................................................................................. INTANGIBLES .................................................................................................................................. 47 RETAINED EARNINGS............................................... ................................................................. 47 50 TREASURY STOCK......................................................................................................................... . . 50 COMMON STOCK AND ADDITIONAL PAID IN CAPITAL............... ........................................ DEBT ........................ ............................................................................................................... 51 Inputting the historical debt values ........................................................................................... 51 Short-term debt ............................................................................................................................. 52 Long-term debt.............................................................................................................................. 52 Balancing the Balance Sheet 57 57 BALANCE CHECK ........................................................................................................................... . ................................................................................ 58 . CASH FLOW STATEMENT ................... . Four rules of cash ......................... . . ......................................................................................... 58 Mark-up the balance sheet ........................................................................................................ 59 . ......................................................... 60 Modeling cash flow from operationsx .............................. 61 Operating Working Capital........................................................................................................... Other Operating Accounts ....................................................................................................... 62 63 Net Cash Flow from Operations ................................................................................................... Cash flow from investing................. ....................................................................................... . . 63 65 Cash flow from financing..................... ................................................................................... lw 66 Net cash f o ................................................................................................................................ EXCESS CASH IREVOLVER ..................................................................................................... 67 67 Calculating excess cashlrevolver ............................................................................................... Balancing the balance sheet ....................................................................................................... 70 72 ADDING THE REVOLVER LINE TO THE DEBT SCHEDULE........................................................ Interest Income and Interest Expense 73 METHODS OF INTEREST CALCULATION .................................................................................. 73 Inaccurate interest calculations .................................................................................................... 73 .................................................................................................... . 73 Circularity ................... 74 CALCULATING INTEREST EXPENSE ........................................................................................... Interest rates .................................................................................................................................74 Interest expense ......................................................................................................................... 75 75 Interest expense summary ......................................................................................................... CALCULATING INTEREST INCOME .............................................................................................. 75 . ............................................. 77 LINKING INTEREST INTO THE INCOME STATEMENT ........... . Last year method ......................................................................................................................... 77 Average method................. . ........................................................................................................77 A problem with circular models..................................................................................................... 79 81 The ISERROR function............................................................................................................... And Finally 83 CONGRATULATIONS!.................................................................................................................. 83 RATIOS ............................ ............................................................................................................. 83 . 83 BEAUTY SAVING............................................................................................................................. MODELING STANDARDS ............................................................................................................... 84 . . . . . ............................................................................................................ . . ............................................................................................. .. . . .........................................................................................................................................