Home

Invest for Excel User Manual version 3.4

1. Folders and files Cash tow In Excel 2007 the ribbon menu is modified to include Invest for Excel commands Full Invest for Excel menus a File Input Result Analysis Format Other DAS Jodeonx Home Folders And Explore folder New Open Save Print Close Exit Excel screen Templates of active file s gt e se Menus Home Folders File Menus Short Invest menu as part of Excel menus e Home Insert Fage Layout Formulas Data Review View Developer Add Ins Invest le Folders And Templates E Basic Values 7 ic Profitability al Analyze 7 l fal Home screen Ep Options Gi J New eS Open i Save T Ela Calculations fe Compare ey Charts E Language 4 Comment iu Print Close A Exit gl Financing ak Reports Ej Break Even arii EN Calculation 1771 Help TER File Input Result Analysis i Format Other Menus 7 9 2 Menu setting at startup Invest for Excel remembers menu setting so that if Excel menus were active the last time the program was closed Excel menus will be active at the next startup 11 17 2014 Copyright Datapartner Oy Page 191 Invest for Excel 8 Help 8 1 User Manual Opens User manual pdf format 8 2 About Invest for Excel This dialog box shows the version of the program and to whom it is licensed D DataPartner Invest FOR EXCEL ENTERPRISE VERSION 3 7 Compilation 3 7 001 Copyright c DataPariner Oy 1995 2014 Raatihuoneenkatu 8 FIN 06100 PORVOO FIN
2. Return on net assets RONA Economic Value Added EVA and any of the entered Key financials Analyze result Factor Fw IRF MIRA DW A Payback EBIT Operating income EBIT Operating income o Return on net assets RONG In the Enterprise edition also the following ratios are available when FCFE is activated Net Present Value to equity NPVe Internal Rate of Return to equity IRRe Internal Rate of Return to equity before tax Modified Internal Rate of Return to equity MIRRe Payback time to equity and Simple payback time to equity When a period specific Key financial is selected you also need to specify which period you want to analyse Chart tite Cost analysis Analyze rows max 5 I Indude line for each analyzed row I Show line with all changes combined Analyse profitability indicator Return On Capital Employed ROCE Changes in analyzed rows values 11 17 2014 Copyright Datapartner Oy Page 101 Invest for Excel 3 2 10 Roll Forecast This feature is available in the Pro edition and Enterprise edition of Invest for Excel You can use this feature when you want to have a rolling forecast e a time frame that Roll ha stays the same but moves forward in time when new actual figures are available forecast Before rolling INCOME STATEMENT 1000 EUR 3 2014 6 2014 9 2014 12 2014 3 2015 6 2015 9 2015 12 2015
3. 01 Product line 4 Dz Product line E 03 Product line E 04 Other equipment 05 With the exception of old investment options all depreciation options are applied to the selected assets INVESTMENTS REALIZATIONS MA imputea depreciation BES Months per interval Depr 1 Product line A Depreciation straight line 2 Product line B Depreciation straight line 3 Product line Depreciation straight line 4 Other equipment Depreciation declining balance Any investments depreciation options can be altered separately afterwards if there are some options that are not the same for each asset 3 2 4 3 Imputed depreciation Investments in the Investment table can be depreciated using two separate depreciation schedules one for income tax effects and an imputed depreciation schedule for internal profit effects Page 64 Copyright Datapartner Oy 11 17 2014 Invest for Excel Activate imputed depreciation in the header bar of the investment table INVESTMENTS REALIZA TIONS El BM imputed depreciation Imputed depreciation rows are shown with yellow background INVESTMENTS REALIZATIONS El Imputed depreciation ARES E l NIRO 4 2007 42 2010 42 2011 Residual Months per interval Depr a eee 1 imputed depreciation straight line z l imputed depreciation straight line 3 o Imputed depreciation straight line O ad imputed depreciation
4. External charges Staff costs Other variable costs As two levels of up to 99 sub rows are available you can have up to 98010 rows for income for variable costs and for fixed costs 10 99 99 98010 3 2 5 1 Entering income On the rows under the header Income specified you can write sub headings Income specified License sales E Maintenance agreements Training services Consulting Programming Income can be entered in the table in the following complementary ways 1 As figures or Excel formulas in cells on income rows 2 By reference or link to another calculation e g another Excel spreadsheet 3 For the first period only Note check the time span does the column cover a month a quarter etc then distribute the income to other columns with the ancillary function of this button shown in the blue bar 4 By using Invest for Excel operators as illustrated in picture below e g multiply the increase in capacity obtained through the investment with the utilization rate and the sales price 11 17 2014 Copyright Datapartner Oy Page 69 Invest for Excel income production 316075 Click the button to the left to specify the income more precisely You may hide and un hide the specification in order to show the sum of this row only The sums of all rows will be added up to a sum total in the Income row For specifying income you may use up to 98010 r
5. 11 17 2014 Copyright Datapartner Oy Page 177 Invest for Excel INCOME STATEMENT aot FAK EO Break Even found Raw materials and consumables UTNE WVarlable COSTS The Break Even function now told us that the Break Even of the Sales price ton is 165 42 which is much lower than the planned price 350 The Profitability analysis screen shows that net present value is zero NPV 0 in the table Total investment capital Required rate of return Calculation term Calculation point Present value of net cash flow Present value of residual value Net Present Value NPV L gt NPVas a monthly annuity Discounted Value Added DCVA Internal Rate of Return IRR Profitability Index Pl Payback time 2 400 000 17 30 10 0 1 2005 56 094 56 094 0 0 26 048 17 30 1 00 Hint Use Copy Distribute function to create dependence between columns otherwise only the value in one cell is changed 5 9 1 Undo Cell Break Even In Invest for Excels main menu under the Analysis column the 2 to last item is Undo Break Even True to its name this function enables you to restore the situation prior to looking for the break even point Copyright O Datapartner Oy 11 17 2014 Page 178 Invest for Excel 6 Format 6 1 Formatting cells 1600 Pra RON 41 0931 347 1 953 116 Formatting calculation file cells is allowed using 1600 db Cut 353 116 Microsoft
6. Comment New Jet engines Sale amp leasback USD simulation Get figures from your investment calculation by clicking the exclamation mark 1 button in the top left corner When you have more than one calculation open you can choose from which calculation file you want to transfer figures to the column in question Please note the following decision making rules when comparing alternative investments Page 128 Copyright O Datapartner Oy 11 17 2014 Net present value NPV Monthly annuity Internal rate of return IRR Modified IRR Profitability Index Pl Payback time discounted Invest for Excel NPV is an indicator of how much value an investment or project adds to the firm The higher the NPV the better NPV should be used together with other indicators like IRR MIRR PI and Payback to get a wider perspective The higher the monthly annuity the better the investment is Monthly annuity can be used to compare alternative calculations with different investment terms Should not be used standalone The higher IRR the better the investment is The higher MIRR the better the investment is The higher PI the better the investment is The shorter payback time the better Can be ambiguous Never base a decision on one indicator only NPV is considered the most important indicator but when comparing different projects it doesn t necessarily reflect the size of the investments IRR MIRR and PI show the rel
7. Interval length months Number of intervals CMMA Y Calculation term begins 01 2013 in the beginning of period loe 3 a 01 2013 in the beginning of period lt 1 Press button to 12 2020 in the end of the period take a picture copy of the table Discount rate per annum 14 00 required rate of return 2013 2014 2015 2016 2017 gt CS 26 2 26 26 26 s BASIC VALUES a ESE jz ipti Project A picture where you want gt Syens heyy ry Calculation term begins 01 2013 in the beginning of period Cal culation point OV2013 fin the beginning of period Calculation term ends 122020 fin the end ofthe period Figures 1 1000 1000000 Curency uso Dis count rate per annum 0 required rate o freburn 2013 2014 2015 2016 2017 gt Income tax ze amp e sa 26 11 17 2014 Copyright O Datapartner Oy Page 19 Invest for Excel By default a message is displayed telling that a picture has been copied to the clipboard Check Don t show this message again before pressing OK if you don t want this message to be displayed again If you want to have the message displayed again go to Invest for Excel s Options and check Picture copy show Picture added to clipboard message This function can also be applied by pressing Shift while clicking the button This way it can be used in calculation files created befor
8. Invest for Excel 11 2 2 Consolidated balance sheet Company acquisition 1000 CONSOLIDATED BALANCE SHEET 12 2010 12 2011 12 2012 ASSETS Non current assets Intangible assets 6 066 5 785 5 503 Property plant and equipment 3 972 3 936 3003 Investments in associates 0 0 0 Other long term investments 14 14 14 Deferred tax assets 0 0 0 Long term interest bearing recelvables 0 0 0 Total non current assets 10 052 9735 9 400 Current assets Inventories 3 107 3211 3432 Trade and other receivables 1056 1095 1134 Cash and cash equivalents 105 r9 11 Total current assets A 057 A 281 44577 Total assets MD 1402300 13977 EQUITY Capital and reserves attributable the Company s equity holders share capital 0 0 0 Other equit 126 441 013 Minority interest 0 0 0 Total equity 126 441 613 LIABILITIES Non current liabilities Interest bearing liabilities 9 159 8 559 7 959 Deferred tax liabilities 652 566 arg Provisions 183 183 183 Other liabilities 64 64 bd Total non current liabilities 10 058 9 372 8 685 Current liabilities Interest bearing liabilities 708 708 708 Current tax liability 146 202 408 Trade and other payables 3071 3219 3 363 Total current liabilities 3925 A 209 4479 Total liabilities 13 984 13 581 13 165 Total equity and liabilities 14 110 14 023 13971 Page 206 Copyright O Datapartner Oy 11 17 2014 Invest for Excel t23 Consolidated cash flow statement Company acquisition 1000
9. Invest for Excel 9 3 Shareholder s equity and liabilities 9 3 1 Shareholder s Equity Share capital share issue premium and other restricted equity In addition to the opening balance item increase decrease in equity given in the cash flow table has an effect on this row Retained earnings The profit loss of the financial period accumulated Dividends entered in the cash flow table are subtracted from this row Profit loss for the period Profit loss for the period cumulative financial year from the Income statement 9 3 2 Accumulated appropriations and minority interests The changes of accumulated appropriations and minority interests come from the Income statement the balance is shown here 9 3 3 Liabilities Long term Liabilities Long term liabilities are divided in interest bearing and interest free long term debt In addition to the opening balance increases and decreases entered in the cash flow table will change these items The current portion of long term debt is shown separately under short term liabilities Short term Liabilities Short term liabilities are divided in interest bearing and interest free short term debt Interest bearing short term debt include short term borrowings which can be altered in the cash flow table and current portion of long term debt which is calculated from changes to long term debt entered in the cash flow table Example CASH FLOW STATEMENT 12 2005 12 2006 12 20
10. Sales price per ton E Row NN Detail rows For Load Factor Tons produced Row 446 degree of utilization Row 444 Create f Hide Delete Mumber of rows z e I Indent text Detail rows are shown in a different background color and texts are indented by default 22 Of utilization Pa 70 0 FOO Target lc E E 70 0 70 0 price per ton i ad Ly 3 2 5 3 8 Modify hide or delete detail level specification rows To modify detail level specification rows choose the specification row with the detail level rows and choose Modify Hide or Delete Page 76 Copyright Datapartner Oy 11 17 2014 Invest for Excel Row Specification Specification hor row Income production Row 442 Sub rows Insertidelete Load Factor degree of utilization Bow 444 Target load Factor Row 445 Detail rows For Load Factor Empty row 446 degree of utilization Row Empty row 447 4441 Empty row 448 Empty row 449 Sales price per ton G2 Row 450 Modify 2 Tons produced Row 451 Hide Delete You can create modify hide and delete detail rows in the same way as specification rows 3 2 5 3 9 Residual column and specification rows Residual column is not included when row specifications are created 12 2005 12 2006 12 2007 12 2008 Residual A AO AO oO AO as 78 000 79 950 81 949 83 997 86 097 oli Months per interval Income specif
11. the fee is a percentage of each withdrawal paid at the time of corresponding withdrawal D of undisbursed balance on each disbursement the percentual fee is calculated on the unused loan reservation paid each month there is a withdrawal E of paid interest a percentage fee calculated on paid interest paid each time interest is paid F of balance in advance repayment period a percentage fee calculated on loan balance paid according to amortization schedule but upfront G of balance in arrears repayment period a percentage fee calculated on loan balance paid according to amortization schedule H Fixed fee in advance the fee is entered paid according to amortization schedule but upfront Fixed fee in arrears the fee is entered paid according to amortization schedule J of disbursed balance in advance otherwise like option F but payments start already at financial closing continuing according repayment interval upfront K of disbursed balance in arrears otherwise like option G but payment schedule starts already at financial closing continuing according repayment interval L of undisbursed balance in advance drawdown period a percentage fee calculated on unused loan reservation paid upfront at selected payment interval but only during drawdown period M of undisbursed balance in arrears drawdown period a percentage fee calculated on unused loan reservat
12. 7 Invest Modified Internal Rate of Return based on DCVA MIRA 46 60 f 7 e Invesil Payback time years based on DEWA 3 0 IRRd and MIRRd are refreshed automatically when Profitability analysis is printed from the home screen Result sheet is activated and the program option Update analysis charts automatically is activated 11 17 2014 Copyright Datapartner Oy Page 125 Invest for Excel 4 2 Profitability analysis in acquisition calculations In acquisition calculations Interest bearing net debt of acquired company is subtracted from PV and Free cash flow based equity value is shown Interest bearing net debt of acquired company is calculated from pre calculation term balance sheet Definition Cash and bank interest bearing long term debt interest bearing short term borrowings Example Cash and bank 100 interest bearing long term debt 120 short term borrowings 60 current portion of long term loans 30 interest bearing net debt of 110 Note that interest bearing net debt of acquired company changes the definition of NPV Proposed investments in shares are shown on a separate row in the investment proposal PROFITABILITY ANALYSIS Project description Company acquisition 37 To Firm Nominal value of all investments 18201 Discounted investments 18 028 Required rate of return 2 75 Calculation term 10 0 years 1 2015 12 2024 Calculation point 1 2015 In the beginning of p
13. Profit laze for the period Depreciation difference 0 00d will 1170 000 Depreciation hal 234 O00 234 O00 234 000 234 000 Soe te A Se ee eS S amp S Segoe A a mm mm o 234 000 0 0 0 0 0 0 0 0 0 0 0 0 zooo ol Goodwill 234 000 Equity 1250000 41250000 1250000 1250000 1250000 Interest bearing long term debt 2750000 2475000 2200000 1925000 1650000 Amortizations 0 275 000 275 000 275 000 275 000 Financial expenses i 176 344 157 781 139 219 120 656 The texts of mother company group related figures are shown in blue 10 1 1 Allocation of overvalue according to IFRS 3 Overvalue in acquisitions can be allocated on existing assets and depreciated with added deferred tax liability according to the depreciation of the asset Make sure IFRS 3 is selected as the method for calculating goodwill IFRS 3 is the default method of handling goodwill N GAAP National GAAP refers to an alternative method with straightforward depreciation of goodwill without allocation A deferred tax liability is added to overvalue Page 198 Copyright Datapartner Oy 11 17 2014 Invest for Excel Overvalue before tax liability Deferred tax liability Overvalue The deferred tax liability is by default calculated as Overvalue before tax liability 1 Tax Tax In other words the tax liability equals Tax Overvalue Adjust deferred tax liability if necessary Overvalue ca
14. Rents 5 Other fixed costs I Include sum of changes line 4 Include line for each analyzed row Provisions increase f decrease Analyse profitability indicator Net Present Value NPV Changes in analyzed rows values so 4 20 fo flo wo Alo lso y The principle is the same as that applied to creating charts 1 Select the rows you want to analyse 2 Click the gt button 3 Give your analysis a title 4 You can choose if you want to include a line for each analyzed row a sum of changes line or both To create lines for each analyzed row each row is separately applied with the change percentages whereas for the sum of changes line all analyzed rows are changed at once 5 Choose analysis variable from the following Net Present Value NPV Internal Rate of Return IRR Internal Rate of Return before tax Modified Internal Rate of Return MIRR Profitability Index PI Discounted Value Added DCVA Payback time years Simple Payback years EBITDA Operating income before depreciation USD EBITDA EBIT Operating income USD EBIT 11 17 2014 Copyright Datapartner Oy Page 171 Invest for Excel Return on net assets RONA Economic Value Added EVA USD If Free cash flow to equity FCFE based profitability calculation is used the following variables are furthermore included Net Present Value to equity NPVe Internal Rate of Return to equity IRRe Internal Rate
15. Short term assets increase decrease Turnover period days Inventories Adjusted balance Increase decrease Inventories increase decreas f Current liabilities Average term of payment day Accounts payable Adjusted balance Increase decrease Other current liabilitie Change in other curre POE A a decr Current liabilities ingreas decrease Networking capital 77 7 O OOOO Click the small El button for the asset group you want to specify in more detail The Specify dialog box is shown for the asset group Accounts receivable Specify Don t specify Accounts receivable Specify C Don t specify Click OK and the working capital table will give you the chosen number of subgroups 11 17 2014 Copyright Datapartner Oy Page 91 Invest for Excel WORKING CAPITAL CS EEE EUR AE Months per interval Short term assets 1 Average term of payment days Accounts receivable Adjusted balance Increase decrease Average term of payment days d psted balance base decrease For each working capital subgroup you can define row s that the calculation is based on Click the m button for the subgroup m a Choose rows to base calculation on 445 Sales of electricity 444 Turbines pcs 445 Capacity per turbine kWh 446 Hours per period hours 447 Utilization rate 448 Price per kWh
16. Copy cell formatting to target cells to copy the cell formatting such as deci mals fonts and percentages Page 83 Invest for Excel 3 2 5 5 1 Annual change in cell When distributing the value of a cell you can choose to put the annual change in a cell in column D El gt e Copy Distribute ay i Distribute options INCOME STATEMENT oa ade EUR E 12 2015 12 2015 1212016 ea nde Months per inten i EEEE External charges E 13 201 e Staff costs Copy Formula in active cell Cost per kWh produced a 0 03 Change yearly a a Index base year 100 e Se A Rents Other fixed costs Depreciation 2 470 000 nso 1645 Financing income and expenses S e Financing income and expenses B bela dL les a INCOME STATEMENT EUR HES Months per interval External charges Staff costs Other variable costs 12 2015 12 2016 12 2017 12 2018 12 2019 12 2020 PY A O O O Y O E ee 0 03 103 103 0 04 104 O o 0 03 O04 5 0 5 0 6 5 0 3 5 0 5 0 6 100 00 105 110 122 128 Change yearly Index base year 100 cn 05 AA 5 858 517 3 982 000 4 233376 4 500 117 4 783 156 5 294 473 The annual change of the cell can easily be altered from this cell This feature is not available for all rows Page 84 Copyright O Datapartner Oy 11 17 2014 Invest for E
17. Named the first row Income production Page 72 Copyright Datapartner Oy 11 17 2014 Invest for Excel Clicked the button and created five specification rows Named the specification rows Clicked the button again and created two additional detail rows for Sales price ton Entered row names price component 1 amp 2 A Entered multiplier as operator for two specification rows Load factor amp Sales price Entered the values in column for 2005 Entered an Excel formula for tonnes produced Excel formula Capacity Load factor NDT Deleted operator for the row Tonnes produced only for information not to be included in calculation of the product of specification rows 9 Distributed the values from 2005 to other periods with the Copy distribute button 3 2 5 3 1 Create row specification Click the 3 button to open the following Row specification dialog box Row Specification Specification For row Income production Row 442 Sub rows Sub total rows For Income production Row 4427 le Create e e Number of rows a 0 Enter number of rows to add Five rows is default You can freely change the number of rows to be created max 99 You can also choose if you want to indent the texts or not When you have created the additional rows you will notice that the actual row host has be come a white protected total row If there were numbers on th
18. O Portrait IO Landscape Scaling C Adjust to o normal size 8 Fit to page s wide by tall Paper size A4 210 x 297 mm EJ First page number Page 32 Copyright O Datapartner Oy 11 17 2014 Invest for Excel 2 11 Printer To select the printer go to Invest for Excel s own File menu and click on Printer al CutePDF Writer Fax HP LaserJet 4050 Series PEL 5 Microsoft XPS Document Writer Sl Send To OneNote 2013 2 12 Exit x To exit Invest for Excel go to the File menu and click Exit A confirmation message is shown Exit Exit Invest for Excel and Microsoft Excel The program asks you whether or not to save the changes made to any open Excel workbooks since they were last saved 11 17 2014 Copyright O Datapartner Oy Page 33 Invest for Excel 3 Input 3 1 Basic Values sheet You begin your investment calculation entering the basic values Go to the Input menu to access the Basic Values screen Alternatively you can click the Basic Values button in the Home Screen Press New if you don t have a calculation file open Invest for Excel Could not detect an open Calculation file Cancel Open from Save in Private folder Common folder New from Template folder To open a new calculation file select the command New or to open a previously saved calculation file select Open 3 1 1 Calculation term When you start a new calculation file the Calculation te
19. Provisions Minority interest Liabilities Long term liabilities Short term liabilities Total liabilities SHAREHOLDERS EQUITY AND LIABILITIES 1 2015 12 2015 12 2016 12 2017 12 2018 12 12 12 12 0 0 0 0 0 5 264 688 4745 938 4227 188 3 465 000 2 970 000 4 950 000 4 455 000 3 960 000 3 465 000 2 970 000 314 688 290 938 267 188 D D 0 D D D D 0 D D D D 0 0 D D D D D E 3997218 1 187 500 3 800 000 3 499 563 2 866 784 2234 021 1 601 273 4987500 4763163 4226890 4029132 3653126 In investment calculations the balance sheet is mainly a supplementary report When you are calculating a corporate acquisition or want an investment calculation to take your existing assets into account the balance sheet is also an input table The balance sheet is described in more detail in Chapter 9 of this User Manual The following is a brief description of how to use the balance sheet as a report when calculating a new investment When you were entering the investments in the Investments screen you selected the type of fixed assets for the investment In the Assets section of the balance sheet under the heading Fixed assets and other non current assets you can see the book value of assets divided into intangible and tangible assets and investments Current assets are divided into inventories and receivables based on the data you have entered in the Working capital calculation As the cash flow is usually negative at
20. VYal Pight strctaller 111 With ActivesSheet My sheet assumed active Unprotect if necessary If ProtectContents Then fWasProtected True Unprotect moheetPassword Else fWasProtected False End If Apply outline level Cutline ShorLevels RowLevels intLevel Protect if sheet was protected If CWasProtected Then Protect m heetPassword Cursor top lett Range FirstParameter Select End With ActivelWindow Scrollcolumn 1 Macros ActiveWindow ScrollRow 1 End Sub Enter for example a button or buttons on your own sheet 1 2 31 4 Assign a macro to the button s prea De eid int Page 228 Copyright Datapartner Oy 11 17 2014 Invest for Excel Select the assignable macro Assign Macro Macro name AssignablelMacroDl AllocateOnAssetSelected AnalysisPactorChanged AnalysisRatioselected Analysis Refresh AnalysisvearChanged AssignablelMacrold 1 E i i ES Cancel Edit AssignablelMacrod 4ssignableMacro0s AssignablelMacrod AssignablelMacrods AssignablelMacrod AssignablelMacrod Description The selected assignable macro should correspond to the one that you used in the macro definitions sheet Macro workbook name Term change macro Personalxis_ TermChangeMacro Assignable macros Macro to run in Personal xls AssignableMacro0ty OutlineMySheetw Assignable AssignablelM cro03_ PrintReportl NN AssignableMakro4 PrintReport2_ NN When t
21. straight line Investments Realization 0 0 o 0 0 0 Depreciation 0 0 0 0 0 0 Realization profit floss 1 0 0 a 0 0 0 Oo of A Book value O o Y A Internal Realization Imputed depreciation Realization profit floss a A A A Imputed book value In the Depreciation method dialog box imputed depreciation is separated with a yellow Imputed box Asset 3 Existhg asset the terminal building a Cancel Depreciation 6 i Less options Depredation time years Shift factor 1 factor 1 Depredation method Straight line Old Investment New Investments Declining balance One time depreciation al a Dedining gt straight line Begin depreciation Sum of years digits 12 2015 months 12 Enter manually First depreciation year indudes No of months 12 Use consecutively Basis for depreciation 100 of investment Balance Sheet Items Investment ca tegory Allocate expenditure subsequent investments are summed and depreciated as one Goodwill Other intangible assets Machinery and equipment E l Buildings and structures Residual value wi Automatically calculate realization value at end of calculation term Independent from tax deprecation imputed depreciation can be calculated using any depreciation method 11 17 2014 Copyright Datapartner Oy Page 65 Invest for Excel 1 Hew investment x 10 000 Depreciation declining balance 3 000 2
22. the Include Debt residual correction option in the Options dialog box Debt residual correction is only available for calculation files of version 3 6 or newer Page 124 Copyright Datapartner Oy 11 17 2014 Invest for Excel 4 1 4 DCVA based profitability indicators DCVA based IRR IRRd MIRR MIRRd and Payback have been added to Profitability analysis WACC discount rate entered in Basic values DCVA sum of discounted yearly EVAs with capital cost based on WACC The yearly EVAs are also disounted using WACC IRRd the discount rate that gives DCVA 0 Goal seek is applied for finding IRRd The cash flow used for calculating MIRRd is yearly EVAs with capital cost based on IRRd MIRRd is calculated using the MIRR function using the WACC for both positive and negative EVAs Payback time years based on DCVA returns number of years from Calculation point Payback until the time cumulative DCVA is and stays positive Calculation point Payback is by default the beginning of the calculation term IRRd and MIRRd are not calculated automatically but require manual refreshing Discounted Value Added DYAN 72813 1 Internal Rate of Return based on DEWA RARO Modified Internal Rate of Return based on DCVA MIRAR _ x Pvback time years based on OCYA 3 0 Press th L button to refresh IRRd and MIRRa E Discounted Value Added DOYA 572013 1 Internal Rate of Return based on DEWA RARO 20 76
23. 1 Profitability analysis The result of the investment calculation is shown in this table E PROFITABILITYANALYSIS Project description Nominal value of all investments Required rate of return Calculation term Calculation point Present value of business cash flows PV of operative cash flow PV of residual value Present value of business cash flows Present value of reinvestments Total Present Value PV Investment proposal Proposed investments in assets Investment subventions Investment proposal Net Present Value NPV NPV as a monthly annuity Internal Rate of Return IRR Modified Internal Rate of Return MIRAR Profitability Index Pl Payback time years Return on net assets RONA Economic Value Added EVA Discounted Value Added DEVA Calculation is made by Calculation file Alpha Machine 37 Nominal E Nominal 2 835 000 D 2 835 000 2835 12 75 5 3 9 2015 PY 7 595 958 2328 935 9924893 0 9924 893 PV 2 745 878 g 2 745 878 7179015 152 633 50 27 37 20 3 61 3 2 76 2 1997 077 6 709 798 Datapartner Customer Support Csere JENS W DocomentaAbald5 T exampleziEone mallilaskelmat STE one mallilaskel i Show conclusions of profitability indicators Discounted investments 745878 years 9 2015 12 2020 In the beginning of period 12 75 12 75 gt 41 Based on discounted FCF Average 6 years Average 6 years profita
24. 11 17 2014 Invest for Excel Invest for Excel liquidates the accounts payable automatically at the end of the investment term in the last column otherwise they would remain outstanding unpaid To overrule this feature type in a value on the row for Adjusted balance in the Residual column Other current liabilities Besides accounts payable there may be other current liabilities like e g advance payments from customers tax liabilities accrued expenses and prepaid revenues These kinds of items are typically not necessary for investment calculations Current liabilities are basically calculated according to the formula fae Sum of amounts in the selected rows for the current period Current liability term of payment days a Number of days in period If the term of payment days is longer than the number of days per column e g 45 days in a monthly calculation two things happen 1 The balance increases only with 30 days costs not 45 2 The next period s will be affected additionally Adjusted balance in this row you can enter values or formulas The Adjusted balance will overrule the term of payment days The last row of the table Net working capital shows the netted working capital tied up in the project business The larger the inventories are the more capital they tie up The longer the payment term given to customers the more capital is out of the cash reserves The terms of paymen
25. 17 2014 Invest for Excel 5 8 Charts lt is easy to create diagrams and sensitivity analyses for use in the investment calculation using the Charts function Go to the Analysis Charts menu File Input Result Analysis Format Other in dw Mi da E we S Discount Total Income Variable Fixed Income Charts Cell Break Undo Factor Investment Costs Costs Variable even Break even Analysis Charts Break Even or use the button The Charts dialog box AU 8 Create new chart b C Create new analysis chart Spider C Create new analysis chart Tornado Goto chart Earnings and FCF Cash Flow Tornado Spider Profitability There are four alternatives in the dialog box Create new chart Create new analysis chart Spider Create new analysis chart Tornado and eo 11 17 2014 Copyright Datapartner Oy Goto chart if there are any existing charts in the file Page 167 Invest for Excel 5 8 1 Create new chart Calculation Income statement Chart title Select intervals All s as statement 12 2015 Working Capital Create chart from rows 12 201 5 14 Cash Flow 12 2017 Balance Sheet 12 2018 increase Key financials 12 2019 Capacity 12 2020 Cabin factor 12 2021 Average ticket price 12 2022 Mail service revenue 12 2025 Turnover 12 2024 cumulative financial year l Residual Other operating income Variable costs Fuel costs Fuel costs empty plane Fuel cost per flight Number of flights
26. 2 11 Investment category Balance Sheet Items Investment category Investments subventions Investment category is used for directing f Investments C Subventions investments to different categories in the profitability analysis on the result sheet Proposed Reinvestment Proposed C Reinvestments 3 2 4 2 12 Investments Subventions Select Investment for a normal investment Select Subventions if you want to separate the subvention part from an investment Subvention subsidy or grant Note that the investment should be entered as a negative gross amount and the subvention as a positive amount Use the same depreciation method and percent for investment and subvention 1 Project 3Y gross investment 250 000 Depreciation straight line 25 000 25 000 2 Project 3Y subvention O S sof as Depreciation straight line 2500 2500 Investments 225 e Realization Depreciation 22 son 22 son Realization profit floss l Book value Investments and subventions are separated in the Profitability analysis Investment proposal Mominal Pi Proposed investments in assets 250 000 250 000 Investment subventions 25 000 25 000 Investment proposal 225 000 225 000 3 2 4 2 13 Proposed Reinvestments This categorisation can be used to separate initial proposed investments from reinvestments A normal use of this category proposed investments require funding whereas reinvestments are covered by operative cash flow Rein
27. 4 Interest Rate The interest rate applied for that month 5 Interest Calculated The imputed interest for that month 6 Interest Accrued Cumulative unpaid interest 7 Interest Paid Interest payments according to interest payment schedule 8 Fees Calculated The 1 fee specified in 01Param sheet is calculated here 9 Fees Paid The payments of the 1 fee specified in 01Param sheet 10 Fees Calculated The 2 fee specified in 01Param sheet IRR The effective cost of finance including IRR 206318 j al fees and interests calculated with Internal Fees Total cash flow Rate of Return method Total Cash flow Curnulative 171 351 92 134 b74 6 565 325 33 565 325 15 0 00 00 000 OO 1 265 325 35 Fees Total Sum of the fees paid per month according to specification on 01Param sheet 0 00 216 015 35 2101 341 45 Cash flow Total monthly cash 1 fa 07 1 fa 01 2173 604 47 flow of this debt including loan withdrawals 000 O00 21735604 27 loan repayments interest payments and all 4 00 000 2179604 27 fees 1737 07 1 737 01 2177067 26 0 00 ooo 2177 867 26 Cumulative Total cash flow of this debt cumulated 11 17 2014 Copyright O Datapartner Oy Page 221 Invest for Excel 13 7 View sheet A report view of current debt Gives a good overview You may modify line and period settings by pressing button Debt taken for new production line Project financing Loan from Bank XW Figures 2 2006 3 2006 4 7006 5 2006 5 2
28. A A A Aa 89 3 2 6 Us 110 MAME o vedanta tenis wasted dcticausnidetmetanactuaieesisvaeteddemeacnsstse 90 3 2 6 1 Detailed definitionof Wotkine Capitalidad iia 90 AA O E A A 93 3 2 6 3 MTV CLOG Sha ove intersect a A 94 NON Oh Fame GA UIA E Ge LB E A ee ree EE cores een on 94 327 CSI BL iS ECU CU a oie A O cs es es ae A tae a 96 3 2 7 1 Correction of income tax for financial items oooooonnnnnnncnnnnnnnnnnnnnonnnnnnnnnnnnnnnnnnnnnonnnnnnnnnnnnnnnnnnnnnnnnnos 97 3 2 8 Balance sheet Pro and Enterprise editiOns oooooooonnnccnnnnnnnnnanononnnnnnonannncnnnnnnnonanancnnnnnss 98 3 2 9 KENIA das a wowed 99 2041 Key imancials in anal Ses aabt idda 100 SELLO KOUT OV CCOSE AA add 102 335 DEBT FINANCING ENTERPRISE EDITION ccescccsecceecccscccceceeccescseseceuceeusseseceeuceeseseseceeseeness 104 3 3 1 CAPUGlIZING financing COSIS ON GSSOUS arnasea ina Gee ERS AR a 107 A E A O E a ca vase ce caseatu A E cs castentenc sateen eaeesccueen ones 109 4 1 PROFITABICE Y ANALYSIS iectossesi corse chanted inclina id oidor cocos 109 Page 6 Copyright O Datapartner Oy 11 17 2014 Invest for Excel 4 1 1 Pro OONAN COLORS ti A A AAA AA CAE 110 4 1 1 1 Net Present Valls INP Vai ds 110 AAD Monthly anat of net present Vall ad Dcdo 110 4 1 1 3 Discounted Value Added DEV Atina A E AAA a Ei 110 A O NA O EE 111 4 1 1 5 internal Raver RSU RR AAA AAA EA al 111 4 1 1 6 Internal Rate of Return before tas data 112 A Modied IRR eeek a dis 112 4 1 1 8 Pro
29. Automatic realization is default In the 40 000 40 000 Enterprise edition of Invest for Excel the 50000 10000 _O0 residual column can be left out of the calcu 0 0 60 000 lation In this case the automatic realization 240 000 240 000 option is not available 0 0 50 000 300 000 60000 Oo 3 2 4 1 Depreciation method Select the depreciation method EJ button and specify calculation options in the Depreciation Method dialog box Depreciation method Depreciation More options Depreciation time years Depredation method Straight line Declining balance Begin depreciation One time depreciation maa a Dedining gt straight line 12 2015 months 12 Sum of years digits Enter manually First depreciation year indudes No of months 12 Use consecutively Balance Sheet Items Capitalized development costs Goodwill Other intangible assets Machinery and equipment Residual value j Automatically calculate realization value at end of calculation term The available depreciation methods are Straight line depreciation Declining balance depreciation One time depreciation Changing from declining balance to straight line depreciation applicable in Germany ata E o E Sum of years digits applicable in Switzerland and Spain 11 17 2014 Copyright O Datapartner Oy Page 55 Invest for Excel 6 Depreciation can be entered manually by selecting Enter manually You can also define the percentage of
30. Book value of assets The Impairment test options dialog box can be opened from the Book value of assets row beneath the Balance sheet and on the Result sheet Impairment test Impairment test el Book value of assets A Value in use Value in use B Control value growth capital impairment loss Control value B A 11 2 IFRS report sheet An IFRS report sheet can be added to an calculation file Available only in Enterprise edition Ye Number pe Alignment A Font H4 Border Pattern tO Row Height Column Width Insert Worksheet The IFRS report sheet uses the forecast data on the Calculations sheet Included reports e Consolidated income statement e Consolidated balance sheet e Consolidated cash flow statement Page 204 Copyright Datapartner Oy 11 17 2014 Invest for Excel 11 2 1 Consolidated income sheet E Show key figures Company acquisition 1000 CONSOLIDATED INCOME STATEMENT 12 2010 12 2011 12 2012 Continuing operations Sales 36 453 40 465 42 472 Other income 15 16 16 Materials and services 33 069 34 519 36 526 Employee benefit costs 2 623 2 162 2 097 Depreciation amortisation and impairment charges 314 335 356 Other expenses 1 592 1676 1 759 Operating profit 869 911 950 Share of profit of associates and joint ventures 0 0 0 Net financial items 511 4T3 435 Profit before income tax 350 438 516 Income tax expense 100 123 144 Profit for t
31. Documents eer i AOR Disable all macros with notification io Disable all macros except digitally signed macros Add ins Enable all macros not recommended potentially dangerous code can run ActiveX Settings RAS Developer Macro Settings Macro Settings _ Trust access to the VBA project object model Protected View v Microsoft Excel Trust Center The Trust Center contains security and privacy settings These settings help keep your computer secure We recommend that you do not change these settings Irust Center Settings Yv OK Cancel In Excel 2013 menu select File Options Trust Center Trust Center Settings Macro Settings Disable all macros with notification or Disable all macros except digitally signed macros 11 17 2014 Copyright Datapartner Oy Page 11 Invest for Excel 1 3 Solving problems Problems with installing program update If the installation doesn t update the files or there seems to be something wrong with the software try uninstalling the software before running the update From Start gt Control Panel gt Programs and features Vista 7 remove all versions of the software this doesn t remove the calculation files you have made Windows 8 Control Panel gt Programs gt Uninstall a program You might also check that the installation folder is empty usually C Program Files x86
32. EVA EVA can be defined as the net operating profit that a company earns above its cost of capital EVA is a trademark of Stern Stewart amp Co General EVA formula EVA NOPAT COST OF CAPITAL x CAPITAL INVESTED where NOPAT Net Operating Profit After Tax EBIT Tax Note that tax can be income tax in the Income statement or a calculated tax EBIT tax percent if the following option is selected COST OF CAPITAL Weighted Average Cost of Capital WACC discount rate CAPITAL INVESTED non interest bearing capital in the beginning of year Implementation in Invest for Excel EVA operating income EBIT tax discount factor 100 all assets working capital Assets and working capital average opening balance or at the end of the period Operating profit EBIT Operating income Net Operating profit after tax NOPAT EBIT Income tax for the period cumulative financial year or EBIT calculated tax EBIT 1 tax if the following option is selected 11 17 2014 Copyright Datapartner Oy Page 89 Invest for Excel 3 2 6 Working capital Use this table to calculate the amount of working capital required for your project business WORKING CAPITAL RES 1 2015 12 2015 12 2016 12 2017 Months per interval Short term assets E Average term of payment days 30 30 30 30 B Accounts receivable 0 133 333 147 807 152 612 Adjusted balance Increase decrease 0 1
33. Enclosure 4 Enclosure 5 Enclosure 6 Page 150 Copyright Datapartner Oy 11 17 2014 Invest for Excel When growing perpetuity is used the growth percentage is shown in Cash flow changes Enclosure 1 Specification of calculation Cash flow statement eo azn 122004 122005 122006 1272007 1272008 Met income 137 for 200 460 2525605 318 fof Variable costs 61 239 59 102 112 356 141 Bos Fixed costs 16 PD 19 333 19 913 20 510 Extraordinary income amp expenses Change in working capital 4 854 T 063 5 895 T 433 Cash fow from operations 5910 52924 64 982 114640 149161 100 901 Asset investments and realizations asooo Investment net cash flow 154 090 52924 84992 114640 149161 100 901 Discounted net cash flow 152 059 43 410 61 504 T3 207 24 O46 82 850 Cumulative discounted net cash flow 152 059 108 649 47 145 26 063 110108 162 958 Cash flow changes 12 2003 12 2004 12 2005 12 2006 12 2007 8 2008 gt gt gt 1212004 12 2005 12 2006 12 2007 12 2008 Met incame Variable costs Specification of value in use ets e a2r2005 12 2006 _ 1212007 12 2008 Discounted net cash flow 152 059 43410 61 504 T3 207 g4 046 57 850 162 458 Discounted residual 152 059 43410 61 504 73207 a4 046 52850 162958 11 17 2014 Copyright Datapartner Oy Page 151 Invest for Excel 4 8 Investment Proposal The third file type of Invest for Excel contains a form card for use when pre
34. Excel s menus and shortcuts 269 B Copy 309 194 194 ih Paste Options 215 277 149 al Da 158 927 2 00 J E 764 Paste Special 208 45 56 350 200 l Insert 10 5 Delete 5 900 75 93 917 Clear Contents E Quick Analysis 300 1330 343 922 1330 Filter P 543 022 83 84 2 Sort le Some of Excel s formatting functions are accessible from the Invest for Excel menu The interface language of the dialogue box has been defined already when installing the Microsoft Office package or Excel The Change Language function will not work here 2 00 7250 Format Cells a Pick From Drop down List Define Marne Hyperlink fF 52 310 6 2 Number This is Excel s Format Cell function Here you define the format in which figures are presented Category General Sample Number 64 Currency Accounting ort eae a Date Decimal places Time Fraction Scientific Special Custom Percentage formats multiply the cell value by 100 and displays the result with a percent symbol Invest for Excel 6 3 Text Alignment Use Excel s Alignment function to align text in active cells Text alignment Orientation Horizontal Vertical lo E _ Justify distributed Text control L Wrap text Shrink to fit C Merge cells Right to left Text direction comer e ctr Atoms y m Frinter fonts _ Normal font This is Excel s Format Fonts func
35. Fuel costs from passenger weight Fuel cost per passenger Number of passengers Handling costs Handling cost per passenger Number of passengers Gross margin _ Convert negative to positive _ Yearly cumulative financial year figures only cumulative financial year Fixed costs Create chart From the drop down menu choose first the report or Input screen from which to import rows to your chart Next mark the row or rows Tip When selecting several rows at the same time keep pressing the Ctrl button when selecting rows Page 168 Copyright Datapartner Oy 11 17 2014 Invest for Excel When you click the button in the centre of the dialog box the rows selected become sets of bars in your chart Give your chart a title Calculation Income statement Chart title Select intervals Rows Passenger traffic Number of passengers Create chart from rows Number of passengers 2 2018 Change yearly So Turnover 2 2019 increase EBITDA Capacity Free cash flow to firm FCFF Cabin factor EBITDA margin 6 Average ticket price Mail service revenue Turnover Residual cumulative financial year Other operating income Variable costs Fuel costs Fuel costs empty plane Fuel cost per flight Number of flights Fuel costs from passenger weight Fuel cost per passenger Number of passengers Handling costs Handling cost per passenger Number of passengers Gross margin _ Convert negative to positive Yearly cumulative f
36. Income 0 87 500 88 214 88 934 89 660 cumulative financial year 87 500 175 714 264 648 354 308 90 392 444 700 95 685 1 098 445 91 130 91874 92 624 93 380 94 142 94911 535 830 627 704 720 327 813 707 907 849 1002 759 Use the row Other operating income to enter extraordinary income outside of main business Page 70 Copyright Datapartner Oy 11 17 2014 Invest for Excel 3 2 5 2 Entering costs There are already some headings for Variable costs which you may change at will They are of the grey input field type Remember to enter costs as negative values outward cash flow Click the button to the left to specify the rows more precisely You may hide and restore the specification in order to show the product of this row only You have all the same functionality to add rows as in income 10 99 99 98 010 rows for variable costs The variable costs of the investment reduce the profits for the period The difference is shown as gross margins per interval and financial year and as a percentage of the profits per cumulative financial year Remember to enter costs as negative values You can use the formulas to specify other costs as well There are already some headings for Fixed costs which you may change at will Click the button on the left hand side to specify the rows more precisely You may hide the specification in order to show the product of this row only Hint It is not compulsory to split the c
37. Internal Rate of Return MIRR Profitability Index PIY Discounted Value Added DCVA Payback time years or Simple Payback time years as the profitability measure Note that Payback is limited to the calculation term Total investment s impact on profitability Total investment USD o 80 000 Change 20 0 Net Present Value NPV La 20 358 Change 346 7 Net Present Value NPV Key marcas ramos E 00000 EBIT Ka 42 7 Return on net assets RONA 18 0 Economic Value Added EVA USD 988 90 000 10 0 12 458 173 4 100 000 90 000 21 660 67 7 12 660 39 6 14 8 892 100 000 0 0 4 557 0 0 110000 100 000 21 660 67 7 11 660 36 4 12 3 2 172 110 000 10 0 3 343 173 4 120000 110 000 21 660 67 7 10 660 33 3 10 2 4 652 120 000 20 0 11 244 346 7 120 000 21 660 67 7 9 660 30 2 8 9 6 532 Beneath the bar chart is another drop down list from which you can choose the period for the financial statement s key figures you want to analyse The table shows how the change affects the Operating Result Return on Net Assets and Economic Value Added Operating Result comes from the Operating Profit cumulative financial year row in the Income statement 5 5 Variable costs analysis Refer to Item 5 4 Income analysis Here you simulate the effect of changes in variable costs 5 6 Fixed cost
38. Other receivables Bank and cash a O00 6 000 4 000 2 O00 6 027 2 25 1 332 4 845 Total Current Assets ASSETS AA a a a SHAREHOLDERS EQUITY AND LIABILITIES Shareholders equity Share capital 0 0 0 0 0 Share issue premium 0 0 0 0 0 Other restricted equity 0 0 0 0 0 Retained earnings 0 0 gra 2625 4 TEZ Profit lose for the period 0 975 1 652 2137 2457 Total shareholders equity a 2625 4762 7248 Minority interest Liabilities Long term liabilities Short term liabilities Total abilities a ao oa of l SHAREHOLDERS EQUITY AND LIABILITIES a as ara osam 6 Page 66 Copyright Datapartner Oy 11 17 2014 Invest for Excel 3 2 4 4 Hiding rows and grouping investments Sos lso a a INVESTMENTS REALIZATIONS By clicking the button on the left of the blue bar you can add investment rows to the screen or hide unnecessary rows Select the rows you want to hide AL i Select all Investment 2 Facilities El Investment 3 Software Investment 4 Installation Clear selections Investment 5 Investment 6 Investment 7 Investment 8 Investment 9 Investment 10 Investment 11 Investment 12 Investment 13 Investment 14 Investment 15 Investment 16 Investment 17 Investment 18 Investment 19 Investment 20 Investment 21 Investment 22 Investment 23 Investment 24 Investment 25 Investment 26 Investment 27 Investment 28 Investment 29 Investment 30 Investments totals invest
39. a short overview 3 pages Project details screen nomena lt Select where you want to move gt T ARE IME MEE Project information Description Financing the Space Station loan portfolio Total investment LL 2 06 244 TEUR Total financing Ob 244 TEUR Financing investment 100 So Utilized financing 2 164 995 TEUR Utlized total ab o Prepared by Jens Westerbladh Comments Two loans European Investment Bank Export Credit Agency terms Citigroup commercial loan Calculation figures Investment calculation Financing calculation Figures 1000 y Figures 1000 y Currency USD Currency EUR project currency The financing module enables you to compile a financing package consisting of one or more loans You can add loans by clicking the Add financing button The program covers various types of loans and the costs associated with them Page 104 Copyright O Datapartner Oy 11 17 2014 Invest for Excel Param sheet Entering loan parameters Billie lt I Ena lt Select where you want to mowe gt T Add tinancing Project Financing the Space Station loan portfolio Financing description European Investment Bank Export Credit Agency terms Total amount 2164 995 TEUR Currency eur of total financing BU OO Financial clasing Month 7 Year 2003 w 003 Drawdown period Months 36 2003 2006 3 years Repayment period Years E y months E Starts at C The end of drawdown
40. activate Private or Common folder at any time by pressing the activate buttons Page 22 Copyright Datapartner Oy 11 17 2014 Invest for Excel 2 1 2 Template Folders Templates are modified Invest for Excel files to be used as templates when starting a new calculation E g the user wants own headers and footers some pre entered information some pre selected settings some hidden rows some renamed rows pre set depreciations asset types and tax settings and some restrictions in user rights Template folders are folders where you save your templates Here you can tell the program where your templates are located When you create new files you may choose whether you start from Invest for Excel s standard template or from one of your saved templates Working Folders Template Folders Template Files Utility Files ZIP Template folders Remove Explore Templates C Des Documents Cale enpats Real Estate Templates C Users JENSW Documents al Estate Real Estate Templates Mergers and Acquisition templates C Users JENSW Documents YC malcW tion Mergers and Acquisition te Move Down 11 17 2014 Copyright Datapartner Oy Page 23 Invest for Excel 2 1 3 Template Files By specifying template files you decide whether to use the standard templates of Invest for Excel default templates or your company s own template You can either enter the path to your own folder or find it by clicking the button on the right ha
41. an acquisition of a business or a business function The profitability can be calculated by feeding in the data on the income and costs related to the acquisition of a business or a business function and the future cash flow The calculation will also return the recommended maximum purchase sum based on this information 10 1 Investment When starting an acquisition calculation select New Calculation file in the Invest for Excel menu and corporate acquisition as the calculation type The Corporate acquisition calculation type is selected only when Company A buyer evaluates the profitability of acquiring Company B the business or function to be acquired The Invest for Excel calculation is made of Company B and it shows the effects of the acquisition on Company A goodwill In the Depreciation screen select the Corporate Acquisition option when entering the purchase price of the corporate acquisition Depreciation method Asset 1 Company B E Cancel Depreciation More options Straight line Declining balance One time depreciation Declining gt straight line Sum or years digits Balance Sheet Items l Type of asset Buildings and structures Land and water Other tangible assets Investments in associated companies f Corporate acquisition As a default the first investment in the investment table is defined as corporate acquisition in a new investment calculation 11 17 2014 Copyright O Data
42. annum Cost of equity per annum 2003 2010 2011 2012 Income tax Enter the required after tax rate of return on equity Note that Invest for Excel doesn t make a distinction between Preferred stock and common stock Cost of equity should be return on all equity 11 17 2014 Copyright O Datapartner Oy Page 121 Invest for Excel Cash flow statement When FCFE is included the Free cash flow FCF is renamed to Free cash flow to firm FCFF and three rows are added to the Cash flow statement Free cash flow to equity FCFE Discounted free cash flow to equity DFCFE and Cumulative discounted free cash flow to equity CASH FLOW STATEMENT 1 2008 12 2008 12 2009 12 2040 12 2011 12 2012 Months per interval o ao n ao n na Cash flow from operations Variable costs E Fixed costs Extraordinary income amp expenses Income tax Cadiusted E Change in working capital Cash flow from operations a 37 000 248 000 a a 270 755 282 993 Asset investments and realizations 1 000 000 o o oo o Free cash flow to firm FCFF 4 000 000 137 000 248 000 259100 270755 282 993 Discounted free cash tow to firm DFCFF 1 000 000 124 545 204 959 194 666 184 929 175 716 e discounted free cash flow to firm A 00 ad IN 455 a 496 B30 E 901 A 15185 Information Financial cash flor Financial income and expenses o Correction of income tax for financial tems o Long term debt increase decrease 1 650 000 sy Changes in sho
43. be restricted to an extrapolation period of 1 100 years in addition to a perpetual period The standard way of calculating Perpetuity is discounting a perpetual cash flow E PV of residual value E 1364804 serle 353995 tapolation period Perpetual 7 Perpetuity 1212017 3530951 You can also limit the discounted cash flow to a number of years 1 100 by using the Extrapolation term dropdown list Perpetual The discounted cash flow is limited to the selected number of years E P of residual value E 728113 2zoi7 e 253005 Enrapoiation nerod avers 7 10 00 1588 538 This is useful if the expected economic lifetime of the primary asset is known but you don t want to create a full cash flow forecast for that long a period Also in impairment testing reporting standards can stipulate that a full forecast model should be created for a fixed number of years and the last year s cash flow should be used as base for discounted cash flows for another number of years 11 17 2014 Copyright O Datapartner Oy Page 119 Invest for Excel 4 1 2 2 Implied exit multiple When perpetuity extrapolation is used an implied exit multiple is automatically calculated in the bottom of the perpetuity calculation table The implied exit multiple is undiscounted residual value last year s EBITDA 4 1 2 3 EV EBITDA In a valuation file Profitability analysis an EV EBITDA ratio is calcula
44. cases how ever the basis is not 100 An example of this is an asset with a known salvage cost at the end of the economic lifetime The basis can be entered or calculated using a calculation dialog box opened from the Depreciations dialog box Calculate Basis For Depreciation Investment 600000 O Residual book value ATT Basis For depreciation ETS Basis of investment 83 33334 O0 Basis for depredation Cancel 100 2 of investment ze 3 2 4 2 3 Begin depreciation Select the period when depreciation l S begins Then select how many months Begin depreciation should be depreciated that period E g 12 2015 months 12 a If your calculation is on annual basis and operations start 15t of April 2015 First deprecation year indudes No of months for Straight line depreciation select fo Bo Begin depreciation 12 2015 and then select First depreciation year includes 9 months April Dec 3 2 4 2 4 First depreciation Enter the number of months on which to base the calculation of depreciation for the first interval You can enter the actual number of months or if local legislation allows it you can use a simpli fied method according to which depreciation is calculated over 12 months although the invest ment was made near the end of the financial year Default value is 12 months Page 58 Copyright Datapartner Oy 11 17 2014 Invest for Excel 3 2 4 2 5 Use consecutively The Use consecu
45. cell and choose Delete comment from the Other menu 7 7 Edit links Links to external documents can be edited from Invest for Excel s menus Excel 2003 Other Window Help Home screen Change Language GB FI SE DE PL ES RU Calculation Home screen Invest for Excel Menus Investment Income statement Options Working Capital Cash Flow Balance Insert Comment Delete Comment Calculator Show Toolbar Key financials Full Screen Edit Links Options Excel Menus Excel 2007 iis Home Insert Fage Layout Formulas Data Review View Developer Invest LL Folders And Templates gt Basic Values gt Profitability al Analyze 7 i fat Home screen EF Options 4 J New ES Open m Save Em Calculations El Compare 7 ik Charts i e Language 4 Comment E Format Invest eu Print a Close x Exit 221 Financing a Reports 7 EN Break Even z Menus File Input Result Analysis Format Page 188 Copyright Datapartner Oy 11 17 2014 Invest for Excel Result Input Analysis ay Se 9 amp Change Calculation Calculator Edit Options Insert Delete User Manual About Invest screen Language Excel Links Comment Comment pdf for Excel Home Other Help 11 17 2014 Copyright Datapartner Oy Page 189 Invest for Excel 7 8 Calculator Calculator How to use the calculator Click an input field of the calculator
46. charge Project Manager and person in charge Date processed or prepared Dates when the matter was handled and the initials of the presenting persons Print button for printing the investment proposal E Go to the following page of the investment proposal Page 154 Copyright O Datapartner Oy 11 17 2014 Invest for Excel Development Stefan Westerbladh 12 21 2004 Department Drawn Up by Date Project X Investment object Investment number Project title and number CATEGORY OF INVESTMENT Productivity investment Statutory investment Fixed by law Inv For expansion amp development Will be carried out as a project Maintenance investment L Carried out as normal work Replacement investment C Associate project C Strategic investment I AGREEMENT AHD DECISION Investment proposal is valid until Coverage of binding offers 9 Guotations are valid until Estimated duration of project Estimated startup time Change in personnel From decision to start up months Environment protection share From start up to full production mortis COST ESTIMATE PROFITABILITY Update Proposed investments 1 500 000 Discount rate i 6 0 Met working capital 16567 Average operating margin per annum 212 976 Interest from construction time Internal Rate of Return 9 16 1 Loss of margins MPY excluding residual value 214 095 Capital Requirement 1516667 MPY including residual value 1 005 629 Payback time years 10 0 Econ
47. chart is shown on a yearly basis regardless of used intervals Click to close the chart Residual values are not included in Payback Residual column entries and perpetuity are not included when payback is calculated Only cash flow during the actual calculation term is taken into account The reason for this is that residual values are often substantial and will make the cumulative dis counted cash flow positive independent of the length of the calculation term As a result if resi dual values are included in payback the shorter the calculation term the shorter payback will be This misleading effect has now been eliminated 4 1 1 10 Simple Payback years not discounted Definition Payback time refers to the number of years the investment has to generate cash flows equal to invested cash flows without considering time value of money The program calculates the Simple payback from the free cash flow Payback is the English term for the repayment period 11 17 2014 Copyright O Datapartner Oy Page 115 Invest for Excel Decision making rule The shorter the Simple payback the better the investment is This rule is however not straightforward For instance it fails to take into account time value of money and any consecutive cash flows although they might be significant for profitability Simple payback should be used only as a supplement to other measures of profitability Page 116 Copyright Datapartner Oy 11 17 2
48. eet ee 179 11 17 2014 Copyright O Datapartner Oy Page 7 Invest for Excel 6 3 TEXEALIONMEN aa dao aid 180 6 4 PP A saath ATE E EE TS 180 6 5 BODER ceea ii ET 181 6 6 FORNA Te TIE N a di oa 181 6 7 ROW IEC E E E AN 181 6 8 COLLUN M IPI a E E A 182 6 9 MARK UNMARK UNCLOCKED CELES casi a 182 ONI ARRANGE BUTTONS crena o a a ATA 182 OE INSERT WORKSHEET arado e a a ea 182 TT DOTHER qa 184 7 1 HOME SCRE Neona E sida 184 TZ CHANGE LANGUAGE a 184 IA AA 184 7 3 CALCULATION dla 186 7 4 OPTIONS nai Ad 187 7 4 1 A AA A anne taeda 187 7 5 INSERT COMMENT e ete 188 7 6 DELETE COMMENT ia aa 188 7 7 FOTENE O 188 7 8 CALCULATOR do o a 190 7 9 IX CEE MENUS dd add a 191 7 9 1 INVEST TOF LICEI M ERUS A seule Masta N A as Wty dep A a Ree 191 7 9 2 MENU seNe SATUD AAA AAN OE OEE AO OA 19 o HEEF sasson 192 8 1 SAL A E E O O A O da luactinbaeeeeas 192 8 2 AROCTINVES TT POR EXCEP diles 192 9 BALANCE SHEET PRO AND ENTERPRISE EDITION cccccccsssccccssscccsscscccsscscceeees 193 9 1 FIXED ASSETS AND OTHER LONG TERM INVESTMENTS ccoocccnccnnoconoccnnccnnoconoccnnoconoconarcnnoconacanircnnoss 193 9 1 1 TRTAR CI CAES A AAA TS 193 9 1 2 LIN TILA CIS WE AAA A AAA AA 193 9 1 3 FARNELL AL eee NOES I POR ic ON PTS 194 9 2 INVENTORIESAND CURRENT ASSETS aonne A aaa 194 9 2 1 Inventories and work in progress oooonnnnnnncnnnnnnnnnnnnnnnnnnnannnnnnonononnnnnnnnnnnnnnnnnannnnnnnnnnnananinnnnss 194 9 2 2 TRCCOIV E T A iii dote 194 9 2 3 Dante
49. from a cell to the following columns using Excel s Copy and Paste commands For that purpose nvest for Excel has a useful function which you can activate by clicking the button But first select the cell the contents of which you want to copy to the next columns We strongly recommend using Invest for Excel s Copy Distribute function instead of Copy and Paste in Excel Reasons are much more security i e less risk and ease of analysis Distribute Value of active cell Copy Distribute If you don t want to copy the value Copy Distribute options to all columns then select from the els list in the box to the right the last period to which the value shall be Copy Formula in active cell pajak gt copied 12 2017 Pe Checking Adjust with number of months per interval aman Example see picture at left The gt first month s income is 10 000 Annual change Se 0 11 2015 The next interval ends at financial closing 12 2015 also 1 month long Therefore the in come savings for that interval is Other a also 10 000 The third interval lasts Copy cell formatting to target cells an entire year why the amount A should be 120 000 If you check ma Adjust with number of months per Index base year 100 interval it will be SO Put annual change in cell D444 Distribute as _ Values Formulas Cancel If you apply this function to a figure that should not change that is it should not b
50. gt profitable Profitability Index PI 124 gt 1 profitable Payback time years 8 5 Based on discounted FCF Return on net assets ROMA 526 Average 10 years Economic Value Added EVA 223401 Average 10 years Discounted Value Added DEVA 1203 143 1 internal Rate of Return based on DEVA IRRd 14 75 gt 10 16 j profitable Modified Internal Rate of Return based on DCVA MIRRd 13 48 gt 10 16 profitable Payback time years based on DCVA 6 5 Cumulative discounted value added 1 2015 gt 12 2020 89 034 Cumulative discounted value added 1 2015 gt 12 2021 01 233 Calculation point Payback 1 2015 To Equity Cost of Equity 20 00 Discounted FCFE without residual value 379951 PY of residual value to equity 192 432 Debt residual correction O Net Present Value to equity NPVe profitable NPWe as a monthly annuity Internal Rate of Return to equity IRRe gt 20 profitable Modified Internal Rate of Return to equity MIRRe gt 20 profitable Payback time to equity years Based on discounted FCFE Calculation is made by Jens Westerbladh 317 7014 Calculation file es err EHTA D Malos lraTrammpurl and Laila iria ir aru Fliq hl roolr inal Fina aed GO rim alam Free cash flow to equity based indicators are shown in the To Equity part of the table 11 17 2014 Copyright O Datapartner Oy Page 123 Invest for Excel 4 1 3 1 Debt residual correction When free cash flow to equity based profitability ind
51. higher returns For example To enter new markets For example Source Honko 1979 The program converts the annual discount rate per interval and month Please note that there is a difference between the required rate of return before and after taxes Please note also that if the input values are not inflated the discount rate should not include inflation 3 1 2 2 1 WACC When specifying the discount rate you may draw on your knowledge about the company s required rate of return on its equity and its equity to liabilities ratio Click the e button beside the discount rate and then WACC and enter the necessary data The WACC dialog box has separate tabs for formulas Formula and definitions Usage If you select Use calculated WACC as discount factor WACC will be used as discount rate Page 42 Copyright Datapartner Oy 11 17 2014 Invest for Excel WACC Weighted Average Cost of Capital Calculate Formula Usage Debt WACC to Cost of debt 5 Tax rate 5 400000 300000 ste fee T E el ga y Required return on Equity Total capital equity 500000 300000 Total capital In the above example we have used 7 as the average borrowing rate for debt 15 for the required rate of return on equity the company s income tax rate is 29 Equity is about 500 000 while its liabilities amount to 400 000 When you click on the Use calculated WACC as discount factor button the program
52. investment can bear taking the interest rate into account You also see the margin of change from the planned value to the Break Even point as a figure and a percentage NOTE Invest for Excel cannot always calculate the Break Even point How well this function works depends on the data in the calculation Person in charge Reference to the front cover of the form Suggested responsible person Supporters Persons in favour of the suggestion Date and signature Level of approval decision Person or body deciding on the issue Date and possibly signature Decision Tick one of the following boxes e Accepted approved e Rejected refused e Postponed transferred or e Other other decision oa Go to the previous page of the investment proposal You can order customised investment proposal forms from DataPartner The program can use various investment proposal formats Thus each organisation can use its own forms in conjunc tion with Invest for Excel 4 8 1 Modified investment proposal form Note The default format represents suggested contents which can be made more applicable if necessary Do as follows to edit it The investment proposal file is protected without a password To undo the protection Exit from the Invest for Excel menu by selecting Other and after that Excel menus In Excel select Tools Protection Unprotect Sheet It is an Excel workbook that you can then edit quite freely You can rename heading
53. is depreciated using the same method also when depreciations are entered manually Enter Allocated overvalue before tax 5 S00 O O O Amount allocated on Immaterial rights o 5 500 Depreciation Enter 10 0 425 850 850 Aglance a 500 a 075 T 225 6375 E loca ed deterred tax liability 3 306 j j o Amount allamsted on Immaterial rights 3 306 j 0 o Depreciation 0 1565 331 3 31 Balance 3 306 3140 2610 2479 Total allocated overvalue 11 506 O O O To enter a new allocation press 121 A new allocation and tax liability appears E Allocated overvalue before tax 8 500 D 0 l Amount allocated on Immaterial rights 8 500 Depreciation Enter ss 10 of 425 850 850 Balance a 500 oO f 225 6 3r5 Amount allocated on Depreciation Straight line TA mee D D D D El Allocated deferred tax liability 3 306 j j j Amount allocated on Immaterial rights 3 306 j 0 o Depreciation j 165 331 3 31 Balance 3 306 3140 2810 2479 Amount allocated on 0 o 0 o Depreciation 0 j 0 o Balance 0 o 0 0 Total allocated overvalue 11 506 O O To remove an allocation press Ga To hide unhide details press A maximum of 12 allocations of overvalue can be specified Any remaining overvalue after allocations is shown as Goodwill and is not depreciated according to IFRS 3 Note This feature requires the Enterprise edition of Invest for Excel 10 1 2 Depreciation of goodwill When N Gaap is selected as goodw
54. money during each period One important thing to remember about an income statement is that it represents a period of time like the cash flow statement This contrasts with the balance sheet which represents a single moment in time You may want to do preliminary calculations in other Excel worksheets to get the values you want to enter in this Invest for Excel table You can make links to the results of Excel tables and show them here or to a separate workbook conveniently linked to the Invest for Excel table You can also create additional worksheets from the Invest for Excel menu item Format You may use Excel s functions and features quite freely For instance you can copy data from your own calculation tables create new formulas to calculate figures in the income and cost rows etc You can personally name all row headings in the shaded areas The blue bar at the top of the Income statement table shows the investment calculation term entered under Basic Values lt is divided into intervals specified by you and their length in months is shown below each heading INCOME STATEMENT Months per interval Income specified Income Other operating income Variable costs Raw materials and consumables External charges Staff costs Other variable costs Gross margin Fixed costs Staff costs Rents Other fixed costs Provisions increase decrease EBITDA Operating income before depreciation Depreciati
55. of Return to equity before tax Modified Internal Rate of Return to equity MIRRe Payback time to equity years Simple Payback to equity years Any Key financial ratio defined in the Key Financials table si also included Note that all variables may not be included if the calculation file is created with an older version of Invest for Excel 6 Select the change in value percentages sensitization and 7 Click OK Page 172 Copyright O Datapartner Oy 11 17 2014 Invest for Excel The buttons at the top from left to right The first button returns to the Create Analysis Chart screen The second button returns to the previous table or chart The third button goes to the next table or chart The Exclamation mark button updates the chart if the figures in the AS calculation have changed a e E Sales analysis 250 000 200000 4 150000 4 a Ww 2 100 000 w 3 4 Sales price gt E _ Sales volume A 50000 i Sales price Sales volume a E P z 0 4 50 000 100 000 30 20 The chart above shows that a 15 drop in turnover price volume output leads to negative NPV 11 17 2014 Copyright Datapartner Oy Page 173 Invest for Excel 5 8 3 Create new Sensitivity Analysis chart Tornado cocer i n 5 Create new chart p lt 5 Create new analysis chart Spider gt Goto chart ho A Tornado analysis chart type is available as an option to the traditi
56. options Current Figures 1 1000 1 Figures 1 1000 1000 Convert formulas to values Note that when multiple rates are used formulas are always converted to values Lock file option will give this warning ab NOTE A LOCKED FILE CAN T BE MODIFIED AND THE LOCK CAN T BE OPENED Are you sure that you want to continue THE FILE 15 NOW LOCKED AND CAN T BE MODIFIED lt is highly recommended to save the file with a new name at this point Would you like to save the file with a new name now We recommend to press Yes When saving Invest for Excel automatically adds to the offered file name the ending _LOCKED Now the saved file can t be changed 11 17 2014 Copyright Datapartner Oy Page 41 Invest for Excel 3 1 2 2 Discount rate per annum The Discount rate annual Discount Factor refers here to the required rate of return on the investment capital This corresponds to the annual yield required of investments of similar type lt can also be seen as the cost of capital Invest for Excel applies the discount rate to calculating net present values and to defining the payback term In other words the cash flows are discounted to the calculation point with the discount rate The calculation point is by default the start of calculation The discount rate works as the time factor of money For example 1 million USD received after one year is worth 909 090 million USD in today s money present value i
57. planning WInvfinProjectA xlsm 12 11 2013 12 35 19 C Users StefanW Documents Business planning InvfnProjectB xlsm 12 11 2013 12 44 39 C Users Stefanw Documents Business planning VInvfinProjectc xlsm 12 11 2013 13 04 14 11 17 2014 Copyright Datapartner Oy Page 145 Invest for Excel In the Options page you can choose Currency translation Check Translate currency Files Options Currency Translation i Translate currency when needed For currency translation use exchange rates in f Currency file InvecyConsolidationDec20 13 xlsm f Financing file source file C Consolidated file target file Consolidated file currency Consolidated file figures 1 For currency translation you can use exchange rates in a Currency file When new Currency file is chosen a new currency file is created and the rates are automatically refreshed from the European Central Bank web service Note that for this to work you need to be connected to internet and Microsoft Excel must be allowed to run internet queries Financing file source file the rates in the first financing files are used Consolidated file target file This option can be valid when the consolidated file is updated Used currency rates are copied to the consolidated file The consolidated file will include all periods found in the source files Example Source file A Figures USD Total amount BOO ee IRR 6 33284 All transac
58. signature has expired File Path c program Files lt 86 DataPartner Invest for Excel VIANVEST lam Macros have been disabled Macros might contain viruses or other security hazards Do not enable this content unless you trust the source of this file More information Show Signature Details Enable Macros Disable Macros Page 12 Copyright O Datapartner Oy 11 17 2014 Invest for Excel A program acquired from an unknown source may contain harmful viruses Excel will ask you to enable or disable the macros Select here Enable macros in order to activate Invest for Excel Program files contain digital signatures You can accept Datapartner as a reliable supplier check Always trust macros from this source This way the Security warning dialog box will not be shown again Trust Center Trusted Publishers x Macro Settings Trusted Locations Disable all macros without notification Trusted Documents Sm Disable all macros with notification Trusted App Catalogs Add ins O Enable all macros not recommended potentially dangerous code can run Activex Settings p g Developer Macro Settings L Trust access to the VBA project object model Protected View Message Bar Cancel Note that appearance may vary depending on the version of the Microsoft Office used Control number At first start up a dialog box will open up asking you to enter the control number for Invest for Excel You
59. the financing need after equity for each period Update from investment file 4 Update total investment Project ment file Exchange rate Total investment in financing file M Update investment cash flows InvSpec 11 17 2014 Copyright Datapartner Oy Page 215 Invest for Excel 13 5 O1Param sheet Enter parameters for debt financing Type of financing Commercial Loan Remove this financing Project Financing description Project financing Bank XYZ Total amount 2 316 000 USD Currency uso of total financing Financial closing Month 1 Year 2009 pa 1 2003 Drawdown period Months 3 1 2009 4 2009 0 3 years Repayment period Years 10 pa months lo a Starts at C The end of drawdown period ra 5 2009 4 2019 10 years The picture above shows the upper part of 07Param sheet Type of financing You may choose from 2 types Commercial loan and ECA ECA is short for Export Credit Agency Export Credit Agencies commonly known as ECAs are public agencies and entities that provide government backed loans guarantees and insurance to corporations from their home country that seek to do business overseas in developing countries and emerging markets Most industrialized nations have at least one ECA Selecting ECA here means that certain parameters for the loan are given it will a typical ECA standard debt under guidelines set by the OECD IN ALL OTHER CASES not ECA USE COMMERC
60. the uncertainty in the evaluation of investments Usually sensitivity analyses are calculations for studying how alternative assumptions in the va rious variables affect profitability The analyses can be used for studying when an investment becomes unprofitable or which assumptions make a difference between two profitable alterna tives with regard to their profitability Sensitivity analyses give an idea how the profitability of an investment project is affected by changing certain basic assumptions or values e g the acquisition cost increases by 10 or variable costs decrease by 5 You can do sensitivity testing freely using these tables and study the changes Your testing will not change the actual calculations The analysis can be done separately for each parameter They change equally much over the whole investment term In order to check the changes in a variable for certain periods only or to simulate simultaneous changes in several parameters enter the changes in the actual Invest ment calculation Combinations of two or more variables may lead to completely different results One way is to conduct the analysis using one variable at a time and identify a few variables that affect pro fitability the most After this you can feed the most critical variables into the investment calcu lation 5 1 Update charts automatically Charts can be updated automatically or manually default It s possible to set automatically update from
61. to financial years regardless of what periodi zation is used in the source files Longest common period Source file periods define periods used in the consolidated file If quarters are used in the source file the consolidated file will be in quarters If the source file periods differ the longest common period is used Example source file A 012005 02 2005 03 2005 04 2005 052008 06 2005 09 2005 source file B 012005 032005 09 2005 Source file E 032005 062005 092005 Consolidated file 03 2005 09 2005 4 6 4 2 Elimination of internal transactions Elimination is available in Enterprise edition for consolidation of calculation files The elimination function removes the effects of inter company transactions in a consolidation statement When consolidating calculation files it is possible to apply eliminations of internal transactions Eliminations can be specified on a separate sheet which can be included in the Invest for Excel Options Other options tab Options Financial Ratos Other Options Indude profitability calculation based on Free cash flow _ter quity FCFE Update analysis charts automatically Picture copy show Picture added to dipboard message An empty Eliminations sheet is included in the calculation file Page 138 Copyright O Datapartner Oy 11 17 2014 y Invest for Excel Input Result Analysis Format Other D da 2 Folders nd Explore folder New Open Save Print Close Shar
62. will find the control number on the registration card supplied with the program package If you have lost the control number please contact Datapartner Oy or your local reseller On Screen Manual To be able to view the Manual you need to have the Adobe Acrobat Reader software installed in your computer You can download it free of charge from the following Internet web site http www adobe com or direct from http www adobe com products acrobat readstep html Note that you may also print out the manual on paper 11 17 2014 Copyright O Datapartner Oy Page 13 Invest for Excel 1 5 Components Invest for Excel includes the following program files INVEST XLAM The start up file controlling other components INVHOME XLSM Home screen from which all functions of the program can be accessed INVCODE XLAM Main program code file INVFILE XLTM Template file for investment calculations INVCOMP XLTM Template file for investment comparisons INVPROP XLTM Investment proposal template INVFIN XLTM Financing template INVITV XLTM Impairment test verification template INVCALC XLAM Program code file calculator INVEST INI amp INVSTART INI Initialisation files containing start up information INVM UK PDF INVM SWE PDF INVM D PDF NVM FIN PDF INVM PL PDF amp INVM RU PDF User manuals in Acrobat Reader format INVGUIDE XLAM INVGUIFI XLAM INVGUISE XLAM INVGUIUK XLAM INVGUIPO XLAM INVGUISP XLAM Invest for Excel Guide f
63. 0 36 4 12 3 5 052 Page 163 Invest for Excel 5 3 Total investment analysis You can analyse the total investment s impact on profitability by entering alternative amounts of investment capital The program shows the impact of a 10 and 20 change as default Change the default values and then click on the Ly button to the left only the click updates the values The question to ask here is What happens to profitability if we can manage the same investment with a lesser capital outlay or what excesses can we go to without compromising our profitability target The changes in the total investment are an average e the possible distribution ratio between machines and buildings is maintained From the drop down list choose alternatively Net Present Value NPV Internal Rate of Return IRR Internal rate of return before tax Modified Internal Rate of Return MIRR Profitability Index PIY Discounted Value Added DCVA Payback time years or Simple Payback time years as the profitability measure Note that Payback is limited to the calculation term Total investment s impact on profitability Total investment USD er 80 000 Change 20 0 Net Present Value NPV Ka 20 358 Change ss Change ss 346 7 Net Present Value NPV 80 000 Key financials 12 2013 p EBITDA Operating income before depre ea 21 660 EBITDA NM 67 7 EBIT
64. 007 5 2008 USD e 1 2006 212006 3 2006 4 2006 4 2007 4 2008 4 2009 Number of months 1 12 1 Period a 3 m Repayment period 10 years Total financing Starting balance ob 000 1 400 000 2 316 016 2 316 016 2 084 414 1 852 613 Drawdown a 700 000 ob 000 916 016 Capitalized interest Principal payments br 231 602 231 602 231 602 Ending balance 00 000 1 400 000 z2 316 016 2 316 016 2 004 414 1 852 813 1 621 211 Loan arrangement fee 134 675 Guarantee fee 1 737 6601 5 906 5211 Billing charge z0 20 z0 Total fees d 134 675 173r b62 5 926 5231 Total debt service b c d 134 675 173r 327 903 306 794 238 183 Total cash flow a b c d 565 325 00 000 916 016 1 73r 327 903 306 794 295 183 Cumulative cash flow 565 325 1 265 325 2 181 341 2 179 604 1 551 621 1 544 820 1 246 645 All in rate p a 5 Db4 Total finance cost 599 520 Total interest and fees paid All in rate p a is the total cost of financing shown as an annual interest Total finance cost is the total costs paid for this debt Clicking on dialog box button gives you Rows Salime the opportunity to Select rows to hide Deselect a Hide Financial closing 1 select rows to be Project financing Loan From Bank HZ Drawdown period hidden to aor 2 select columns to Figures i i Figu Total Drawdown period term be hidden and Number oF months Period Number of months per column 3 select colu mn Toral oan i Hide Drawdown total intervals separ
65. 014 Invest for Excel 4 1 2 Perpetuity In the Enterprise edition the residual value of the investment calculation can be defined as a perpetuity value Perpetuity can be chosen in the Residual value dialog box button In this case the perpetuity calculation can be opened use the button in the result table El PROFITABILITY ANALYSIS Project description Nominal value of all investments 1 000 846 Required rate of return Calculation term D years 1 2015 12 2024 Calculation point 2015 ln the beginning of period 377 347 654 524 1 413 067 654 524 Present value of business cash flows 1 431 871 Present value of reinvestments D Total Present Value PV 1431871 Investment proposal Proposed investments in assets Investment subventions Investment proposal Net Present Value NPV profitable NPV as a monthly annuity Internal Rate of Return IRR 8 gt profitable Modified Internal Rate of Return MIRR gt 8 gt profitable Profitability Index PI AS i gt profitable Payback time years Based on discounted FCF Return on net assets RONA 12 9 Average 10 years Economic Value Added EVA 7305 Average 10 years Discounted Value Added DCVA 235 823 1 internal Rate of Return based on DCVA IRRd 11 08 profitable Modified Internal Rate of Return based on DCVA MIRRd 7 38 not profitable Payback time years based on DCVA 6 7 Perpetuity can be calculated A with the net cash flow for a select
66. 07 12 2005 12 2009 12 2010 12 2011 Long term debt increase decrease 1 600 000 of 200000 190 000 180500 171 475 162 901 Changes in interest bearing long term debt 1 600 000 200 000 190 000 160 500 171 475 162 901 Long term debt increase decrease 1 1 600 000 200 000 190 000 150 500 171 475 162 901 BALANCE SHEET 122005 12 2006 12 2007 12 2008 12 2005 12 2010 42 2011 Long term liabilities 1 600 000 1 400 000 1 210 000 1 029 500 655 025 695 124 540 368 Interest bearing long term debt 1 600 000 1 400 000 1 210 000 1 029 500 655 025 695 124 540 568 Interest free long term debt 0 0 0 0 0 o 0 Short term liabities O aooo 190 000 80 500 17iars 162901 154 758 Interest bearing short term liabilities 200 000 190 000 180 500 171 475 162 901 154 56 Short term borrowings 0 0 0 al o 0 Current portion of long term loans 200 000 190 000 160 500 171475 162 901 154 756 Accounts payable and Other interest free short term debt can be changed in short term liabilities in the Working Capital table Page 196 Copyright Datapartner Oy 11 17 2014 Invest for Excel 10 Corporate acquisition Enterprise edition Investment as a concept and the Invest for Excel tool are much more than the investments made in tangible assets such as machinery equipment and buildings in order to increase productivity or the related profitability calculations The program can also help you in the profitability evalu ation of
67. 100 1 470 Book value 10 000 7 on 4 an imputed depreciation straight line 2000 200 2 2 imputed book value 10 000 In the Income statement imputed depreciation is shown before EBIT The difference between imputed depreciation and tax depreciation is shown on Depreciation in excess of under imputed row before taxes EBITDA Operating income before depreciation 4351 4395 4 438 4433 EBIT Operating income Financing income and expenses Financing income and expenses a Financing income and expenses Financing file EBT Income after financing tems Extraordinary income and charges Realization profit lozs B Other extraordinary income charges J Income before appropriations and taxes Change in appropriations Other appropriations increase 1 decrease Income tax Minority interest Het income for the period 0 973 1652 2137 2 157 In the Balance sheet fixed assets book values are calculated using imputed depreciation The difference between imputed and total depreciation i e cumulative Depreciation in excess of under imputed is included in Accumulated appropriations BALANCE SHEET Fall BED r 1 2006 12 2006 12 2007 12 2008 42 2009 Months per interval ae ooon sao wapo 12 ASSETS Fixed assets and other non current assets Intangible assets a investments lap dA a d a Total fixed assets and other non current assets Current Assets Inventories and work in progress Accounts receivable
68. 12 2006 224 204 26 224 204 26 2 095 120 00 224 204 2b 2 670 915 74 12 122007 224 204 6 2 095 120 00 2 670 915 74 13 212007 224 204 25 2 095 120 00 2 670 915 74 14 32007 224 204 6 2 095 120 00 2 670 915 74 15 4 2007 224 204 6 2 695 120 00 2 670 915 74 16 52007 224 204 2b 2 095 120 00 2 670 915 74 17 6r2007 224 204 26 2 095 120 00 2 670 915 74 16 FDO 228 204 25 2 095 120 00 2 670 915 74 13 0220017 224 204 6 2 095 120 00 2 670 915 4 20 32007 224 204 2h 2 095 120 00 2 670 915 4 21 10200 224 204 6 2 095 120 00 2 670 915 74 22 112007 224 204 2h 2 095 120 00 2 670 915 74 23 122007 Saga e Far 46 7 45 2 695 120 00 573 205 19 2 097 662 55 Three cash flows are brought e Cash flow from operations e Investments and realizations e Free cash flow net cash flow All three are also shown as cumulative The reason for importing cash flows from investment calculation is to have a better idea of how much debt financing is needed when withdrawals need to made and how soon can the debt be repaid The best indicator for the need of financing Copyright Datapartner Oy Invest for Excel is cumulative investment net cash flow free cash flow Think of it as the debt balance needed But remember that you need more than that in order to pay financing costs e g interest 13 4 1 Include equity changes Equity changes can be included when updating financing file cash flows from the calculation file This way it s easy to see
69. 17 2014 Invest for Excel Calculation Term Periodization Financial years Starts in the beginning of year 2015 E nn Mumber of years a Ends in the end of year You may define the calculation term in financial year intervals or more detailed If you are making a calculation on annual basis just press OK Calculation Term Periodization Detailed 1 12 months Starts in the beginning of month 10 2015 H myr SR Number of calculation phases 1 Term years months Interval length months A detailed calculation term may hold one or two phases The calculation term can be split in two calculation phases with different interval lengths e g phase 1 on quarterly basis and phase 2 on annual basis 11 17 2014 Copyright Datapartner Oy Page 35 Invest for Excel Calculation Term Periodization Detailed 1 12 months Starts in the beginning of month 10 2015 MM YYYY Calculation point Number of calculation phases 2 Phase 1 Phase 2 WE ipti Term years months Interval length months In the example above the calculation term is 5 years and 3 months defined as 3 months in a start up phase on a monthly basis and a 5 year operational phase on yearly basis When the construction phase of the investment lasts several months or even years it makes sense to divide the calculation into two phases e construction phase and operations These two phases follow each ot
70. 1s calculated as follows _ FV positive cash flows reinvestment rate MIRR 5 1 PV negative cash flows finance rate n number of periods reinvestment rate required rate of return discount rate finance rate cost of capital WACC discount rate Page 112 Copyright O Datapartner Oy 11 17 2014 Invest for Excel In Invest for Excel is implemented same rate for positive cash flows as for negative cash flows 11 17 2014 Copyright O Datapartner Oy Page 113 Invest for Excel 4 1 1 8 Profitability Index PI Definition The Profitability Index is the Total Present Value PV divided by the sum of discounted Investments PI is the abbreviation of Profitability Index Benefit Cost Ratio is another term used for this measure Decision making rule An investment is profitable when the Profitability index gt 1 FORMULA Profitability index PV C C PV of Proposed investments PV Present value of all other cash flows Proposed investments don t include investments categorized as Reinvestments maintenance Implementation in Invest for Excel Profitability index PV of Free cash flow PV of proposed investments PV of proposed investments 4 1 1 9 Payback time years discounted Definition Payback time refers to the number of years the investment has to generate cash flows before NPV 0 The program calculates the payback time from the discounted net cash flow Payback i
71. 2 2014 1212015 1212016 1212017 1212016 Residual aa cons onths p Income spei When you unfreeze column headers only the row texts coiumns to the left are locked when you scroll right When you scroll up or down no rows are locked This is useful when you create formulas that reference rows above the current table When you freeze column headers both the row text columns to the left and the Header rows are locked when scrolling El gt fe INCOME STATEMENT 1 2014 42 2014 12 2015 12 2016 12 2047 12 2013 Residual Months per interval aa 122018 Income specified NS IAE E AA E EA ES Page 50 Copyright Datapartner Oy 11 17 2014 Invest for Excel 3 2 2 Buttons in Calculations sheet E E om EJE Hiding un hiding rows and grouping rows into categories See 3 2 3 Flip buttons for sub rows You can toggle hide unhide sub rows for the row with the Flip button by clicking it Running the Cell Break Even function You can analyse for any input variable what value returns NPV 0 Move the cursor to the cell to be changed before running the Break Even function For more information refer to the Analysis chapter Copy distribute value or formula to the next columns See 3 2 10 This button shows hides row and column indexing R1C1 A1 like in Excel This button takes you to the last column of the table It is very handy when there are a lot of columns and you want to have a quick look at the statu
72. 2 EXPLORE FOLDER OP ACTIVE PILE da 25 2 3 A A A SN CRETE HY eRe Mente eee 25 2 4 OPI IN ces tsi icra cg ca An e o ee 28 2 5 CLOSE miasan ami a a A a a aaesncGceneceaeeaeh aeeagies 28 2 6 SAVE RE AE ERE EEE A EE EEA E E TEER EE ERAO DET AREE EEE E RE 28 2 6 1 Working simultaneously on several calculatiONS oooooncccnnnninnnnnnnnnnnnnnnnnnnnonnnnnnnnnnnnacnnnnnnos 29 Zt SAVE SNARSHO Tato a ata 30 2 8 SHAREPOINT FICE MENO asas iaa 30 2 9 A A A E 31 AOS PEAGES TU rae a a e E A allan da om elosemn taps edebiea deeds 32 Del PRINTER O N E 33 DEN EX Een an O POPE EE N E EE teks 33 3 ANPU Penina o a Ea OaE NE aa ene een ee 34 31 BASIC VALUES SAB a n A eee 34 3 1 1 Calculat On Tor rar E E T E E 34 3 1 1 1 Calculation Ponies ee E a E E A E E AR 36 3 1 1 2 Residual VA UA A AAA A AAA AAA AAA AAA 37 3 1 1 3 ISCOrIC Al peude re rut il e teat tae 38 312 IATA AA A A A teenies aren 39 3 1 2 1 Cuttenoy conversi n arain i a di dd dc 39 5 es Discount rate peral cds 42 AN WACO O ON 42 SEDA TV Alla DIO CISCOUML TAE nia E A a 43 E IS COS OMe A O O A A 44 3 123 WNCOMeO TAX Cate 1 ar A ASA A ASA EAS AA 44 ALZA Income tax Oplos soe coa 45 3 1241 Tax Calculated atodos 45 SZ AeA Include positive tac e eeaeee a a drid 45 3 1 2 4 1 2 Include tax effects of financing items in discounted cash flOW ooooocncccccnncnnccnnnnnnonnnnnnss 45 3 1 2 4 1 3 Goodwill depreciation is tax deductible oooooonnnnnccnnnnnnnnnnnonononononnnnnnn
73. 200215 15 341800 626695 6469289 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 000 2200215 15 341800 626695 7095984 0 00 0 00 000 1650 16 165016 0 00 0 00 0 00 000 2200215 15 341800 626695 77 226 78 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 000 2200215 15 341800 626695 83 493 73 0 00 0 00 0 00 0 00 0 00 0 00 0 00 000 11580080 208441436 3 41800 626695 8976067 3760168 0 00 000 156331 1 563 31 10 00 10 00 0 00 000 208441436 341800 593711 9569778 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 000 208441436 341800 5937 11 10163489 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 000 208441436 341800 5937 11 107 572 00 0 00 0 00 000 156331 1 563 31 0 00 0 00 0 00 000 208441436 341800 5937 11 113509 10 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 000 208441436 341800 5937 11 11944621 0 00 0 00 0 00 0 00 0 00 0 00 0 00 000 11580080 196861356 341800 5937 11 12538332 3562264 0 00 000 147646 1 476 46 10 00 10 00 0 00 000 196861356 341800 560727 130 990 58 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 000 196861356 341800 5607 27 13659785 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 000 196861356 341800 5607 27 142 205 12 0 00 0 00 000 147646 1476 46 0 00 0 00 Columns from left to right see picture above 1 Capitalized interest If option is chosen Interest from drawdown period is D Capitalized on financing and paid according to repayment plan then the capitalized interests will show here 2 Principal payment Amortization of loan 3 Ending balance Loan amount after amortization
74. 2014 12 2015 12 2016 A Lots po Interval i a HU A AR 1 450 00 1 479 00 1 508 58 Internal sales p 550 00 561 00 572 22 Consolidated Sales No 0 00 900 00 918 00 936 36 By pressing the a button you can move to the corresponding row on the Calculations sheet ds os gt JLo i tele E INCOME STATEMENT Cai El 1000 EUR MES fel Wor 1 2014 1212014 1212015 1212016 Months per interval O To a n 1450 1479 1 You can use grouping of eliminations as a means to apply only part of entered eliminations when consolidating by checking Grouping h ELIMINATION 1 2014 12 2014 12 2015 o a a 0 00 1 450 00 1479 00 Internal sales 520 00 361 00 Consolidated Sales 900 00 918 00 A Group column appears to left You choose from to 25 groups of eliminations Months per interval ELIMINATION Months per interval gt nal sales fzolidated Sales il 2 3 4 5 b 7 8 Page 140 Copyright Datapartner Oy 11 17 2014 Invest for Excel Grouping is useful when you are consolidating different parts or levels of an organisation You could apply group 1 eliminations when consolidating from lowest level units to the an next level unit and all groups when consolidating to the highest level unit Note that you can include same rows several times and use different elimination groups This way the elimination of one calculation number can be different for different consolidations Elimination
75. 2044 zzz zon zans Months per interval Ooo oao oaa oa o al ma e A A Sales 4 900 4974 5 048 5 124 5201 Income Other operating income Variable costs Raw materials and consumables External charges Staff costs Other variable costs Gross margin Fixed costs Staff costs Rents Other fixed costs Provisions increase decrease 500 EBITDA Operating income before depreciation 3 256 Accumulated provisions are shown in the Balance sheet on the liabilities side riatic 0 0 0 0 0 Provisions 300 500 500 500 0 Liabilities Long term liabilities 0 0 0 0 0 Short term liabilities 0 0 0 0 0 Total liabiities o oY ee wA SHAREHOLDERS EQUITY AND LIABILITIES 3770 7524 3 2 5 8 Deferred taxes Page 86 Copyright Datapartner Oy 11 17 2014 m wo oo 200000 1600 pan A749 con 1004 347 1 953 116 353 116 3009 194 215 277 158 927 Invest for Excel Deferred taxes can be entered in the Income statement after ordinary Income taxes Deferred taxes do not affect Free cash flow INCOME STATEMENT 1000 ar 12 2010 12 2041 12 2012 42 2013 42 2014 Months per interval Ooo on aa a n a Sales 4900 4974 5 048 5 124 5201 Income Other operating income Variable costs Raw materials and consumables Gross margin Fixed costs Other foted costs Provisions increase decrease EBITDA Operating income before depreciation Depreciation
76. 23 877 524 361 502 928 481 494 460 061 221 858 513 091 Cumulative cash flow 1 063 910 1 436 343 2 001 486 1677611 1 153 250 650 322 168 828 291 233 513 091 All in rate p a 5 187 Total finance cost 513 091 Total interest and fees paid Page 106 Copyright O Datapartner Oy 11 17 2014 Invest for Excel 3 3 1 Capitalizing financing costs on assets Part of the financing costs in a Financing file can be capitalized on an investment asset and depreciated in the Income statement according to the depreciation plan of the asset You can choose to capitalize financing costs when they are read to the Calculation file Check the Capitalize financing costs before and including period option in the Update financing dialog box Update from financing file Invfin arplane xlsm Debt financing for airplane acquisition Select the last month of financing costs that you want to capitalize and the investment table asset you want to capitalize the financing costs on Update from financing file Invfin airplane xlsm Debt financing for airplane acquisitio Currency translation Capitalize 2 Restoration of airstrip 3 Existing asset the terminal building 4 5 6 7 8 Ej 11 17 2014 Copyright Datapartner Oy Page 107 Invest for Excel Without capitalization all financing costs go to the Financing income and expense INCOME STATEMENT re BE r 4 2008 12 2008 42 2009 42 2010 42 2011 42 2012 Months per inte
77. 242 11 2034 0 00 0 00 0 00 0 36 2 53 0 00 0 00 0 00 7 58 243 2034 0 00 0 E 0 36 0 E 2 53 0 2 0 s 0 36 Le fTotals 2000 100 00 200 00 253 755 039 95 795 Source file C Figures TJPY_ Total amount 200000 RR AA All transactions Withdrawals Principal Ending Interest Fees Financing cash flow at end of month TIP Y of total payment balance Accrued Paid Total Cash flow Cumulative 0 9 2014 152 000 00 76 00 0 00 152 000 00 0 00 0 00 600 00 151 400 00 151 400 00 1 10 2014 0 00 0 00 0 00 152 000 00 864 17 0 00 0 00 0 00 151 400 00 2 11 2014 0 00 0 00 0 00 152 000 00 1 728 33 0 00 0 00 0 00 151 400 00 3 12 2014 48 000 00 24 00 3 500 00 196 500 00 2 592 50 142 50 150 00 44 207 50 195 607 50 182 11 2029 0 00 0 00 0 00 4 000 00 22 926 67 0 00 0 00 0 00 72 072 50 183 2i 2029 0 00 0 00 4 000 00 0 00 22 942 50 95 00 0 00 4 095 00 76 167 50 ooo Totals 200 000 00 100 00 200 000 00 0 00 22942 50 73167 50 3000 00 76167 50 76 167 50 Consolidated file Figures EUR aaa IRA 5 98952 All transactions Withdrawals Principal Ending Interest Fees Financing cash flow at end of month EUR payment balance Accrued Paid Total Cash flow Cumulative 22 097 060 21 10022097 060 21 0 00 0 6 2014 119 269 47 119 269 0 00 119 269 47 0 00 0 00 5 963 47 113 306 00 1 7 2014 0 00 0 0 00 119 269 47 596 35 0 00 0 00 0 00 2 8 2014 0 00 0 0 00 119 269 47 1 192 69 0 00 0 00 0 00 3 9 2014 18259811 23 18259811 0 00 18 379 080 71 1 789 04 0 00 64 502 14 1
78. 25 42 216 499 50 194 495 50 4 5000 2930 60 25T20141 53583 63 75 00 75 00 60 00 60 00 435 00 270213 13 1677641 13 a aaa aar ral a arana nanara APASA ar The loan can be viewed also in report form You can set the rows columns and interval lengths as you please View sheet Example of loan summary SEBEBI lt Select where you lrantto moves acing Financing the Space Station loan portfollo Europena Investment Bank Ezpoft Credit Agen Figures 2003 212004 81 2004 212005 8 2005 212006 81 2006 8 2007 8 2008 8 2009 81 2010 712003 TEUR 712003 112004 712004 112005 712005 112006 712006 712007 712008 712009 712010 712011 71201 DT E TE TE E E E TI 95 a ls 216 500 1082 498 1082 498 1515 497 1515 497 2 164 995 2 164 995 1731996 1298 997 865 998 432999 Drawdown 216 500 865 998 432 999 649 499 Capitalized interest Principal payments 432 999 432 999 432 999 432 999 432 999 Ending balance 216 500 1082 498 1082 498 1515 497 1515 497 2 164 995 2 164 995 1731996 1298 997 865 998 432 999 i o 8038 26 792 32 150 37 509 45546 53 584 101809 30375 58 942 37 509 16 075 498 328 ECA Insurance Premium ECA Commitment fee CIRR Agency Fee Arrangement fee Legal costs Admin Fee 120 120 120 120 Total fees d 8710 1840 1624 1293 75 270 270 270 270 135 14 763 Total debt service b c d 8710 39878 26 792 33774 37 509 46 845 53 659 535 078 513 644 492 211 470778 4439 209 2678 086 To
79. 280 000 1260 000 1240 000 1220 000 1200 000 1180 000 1160 000 1140 000 1120 000 1100 000 New flight route nominal New flight route nominal New flight route nominal New flight route 4 4 3 IRR chart Internal rates of return on the investment alternatives Choose conventional IRR IRR before tax or modified IRR The bold line marks the required rate of return of each alternative Internal Rate of Return IRR New flight route nominal New flight route nominal New flight route nominal New flight route 11 17 2014 Copyright O Datapartner Oy Page 131 Invest for Excel The higher the IRR the better the investment is 4 4 4 Payback chart The payback times of investment alternatives ds Ls 52 2 Lia Payback time years New flight route o New flight route nominal o 7 8 7 9 8 0 8 1 8 2 8 4 8 5 8 8 3 6 The shorter payback time the better can sometimes be ambiguous Page 132 Copyright Datapartner Oy 11 17 2014 Invest for Excel 4 5 Marginal effect You can calculate if a new investment is more profitable compared to the current situation by creating a Marginal effect calculation Create two separate calculations of equal calculation term and with same basic assumptions discount rate tax percent etc First create an investment calculation describing the current situation i e a Status quo calcu lation Enter this calculation as file A in the Marg
80. 33 333 9 473 9 206 Other receivables Change in other receivables increase decrease Minimum cash Minimum cash increase decrease Short term assets increase decrease Inventories 1 Turnover period days Fuel Adjusted balance Increase decrease 2 Turnover period days Spare parts and oils Adjusted inventories Increase decrease Inventories increase decrease Current liabilities E 1 Average term of payment days Fuel Adjusted balance Increase decrease 2 Average term of payment days Spare parts and oils Adjusted balance Increase decrease Other current liabilities Change in other current liabilities increase fdecr Current liabilities increase decrease 0 43 938 1135 Change in working capital O 152386 9732 10067 Net working capital 0 162 118 172 185 3 2 6 1 Detailed definition of Working Capital Each of the main Working capital groups Short term assets Inventories and Current liabilities can be specified in up to 5 sub groups Page 90 Copyright O Datapartner Oy 11 17 2014 Invest for Excel WORKING CAPITAL Cs HE EUR AE Months per interval Short term assets Average term of payment days Accounts receivable Adjusted balance Increase decrease Other receivables Change in other receivables increase decr ase Minimum cash Minimum cash increase decrease
81. 4 950 000 4 950 000 Required rate of return 10 16 10 16 10 16 10 16 Calculation term years 10 0 10 0 10 0 10 0 Calculation term 1 2015 12 2024 1 2015 12 2024 1 2015 12 2024 1 2015 12 7024 Calculation point 1 2015 1 2015 1 2015 1 2015 Interval length months 12 12 12 12 PV of operative cash flow 5 581 858 5 695 520 5 898 706 5 657 464 PY of residual value 324 235 498 451 356659 473615 Present value of business cash flows 5 906 093 6 193 971 6255365 6131 079 Present value of reinvestments 0 O 0 O Total Present Value PV 5 906 093 6 193 971 6255 365 6 131 079 Proposed investments in assets 4 700 4 950 4 950 4 950 Investment subventions o 0 0 Investment proposal 4 700 000 4 950 000 4 950 000 4 950 000 Net Present Value NPY 1 206 093 1 243 971 1 305 365 1 181 079 NPV as a monthly annuity 15 749 16 244 17 045 Internal Rate of Return IRR 14 92 14 73 15 22 Modified Internal Rate of Return MIRR 12 71 12 66 12 77 Profitability Index PI 1 26 1 25 1 26 Payback time years 8 3 8 5 8 1 Calculation point Payback 1 2015 1 2015 1 2015 Return on net assets RONA 90 70 66 47 80 70 Economic Value Added EVA 262 333 199 546 221859 198 056 Discounted Value Added DCVA 1351 798 1090 102 1156 375 1019 634 Payback time years based on DCVA 6 2 7 8 6 9 7 4 r Calculation is made by Jens Westerbladh Jens Westerbladh Jens Westerbladh lens Westerbladh Date 31 7 2014 31 7 2014 31 7 2014 31 7 2014
82. 40 834 40 834 Tota To open an impairment test verification choose one of the following three different ways By clicking 1 The impairment test button in the Home Screen 2 The button in the top right corner of the Result table only impairment files 3 By selecting it from the Invest for Excel menu File New Impairment test opens a new blank verification File Open to open a saved impairment test verification Result Impairment test the following dialog is shown if a file is not open Could not detect an open Impairment test verification file e e Open from Save in Private folder Common folder 11 17 2014 Copyright Datapartner Oy Page 149 Invest for Excel 4 7 1 Perpetuity When perpetuity is used as residual value additional perpetuity information is shown Pm w Ea Impairment Test Verification Update La dro 7 EF verification number 12345 ash generating unit Machine eqment Financial period Ty Forecast period IS FBO Perpetuity from year ov2008 gt Basis for perpetuity calculation 123671 i Discount factor 13 3 Book value of asset 4 200 O00 Value in use specified OOOO EOS Discounted perpetuity 944 750 i Value in use E SO A Control value B A 907 708 o Prepared by Jens VWesterbladh Date and signature 3 12 2005 Mame claritication Enclosures Lv x Enclosure 1 Specification of calculation Enclosure 2 Enclosure 3
83. 5496 50 4 95000 625142 66979 53 32150 18 103 25 32 150 18 1436 343 00 19 1515496 50 4 95000 625142 T3230 96 103 25 1436 343 00 20 1515496 50 4 95000 625142 79432 3 103 25 1436 343 00 21 1515496 50 4 95000 625142 35733 50 103 25 1436 343 00 22 1515496 50 4 95000 625142 91935 23 103 25 1436 343 00 23 1515496 50 4 95000 625142 9323665 103 25 1436 343 00 24 1515496 50 4 95000 625142 104453 07 3750 54 103 25 37503 54 1395334 46 25 1515496 50 4 95000 625142 110739 49 103 25 1393 334 46 26 1515496 50 4 95000 625142 116990 92 103 25 1393 334 46 27 5 649 493 50 30 00 2164995 00 495000 625142 123242 34 103 25 129900 1299 00 643199 50 2047033 96 28 2164995 00 4 95000 3930 60 132172 94 2047033 96 29 2164995 00 4 95000 930 60 141103 55 2047 033 96 30 2164995 00 4 95000 3930 60 15003415 45546 0 45546 03 2001447 8 31 2164995 00 4 95000 930 60 152964 76 2001437 83 32 2164995 00 4 95000 930 60 167395 36 2001437 83 33 2164995 00 4 95000 930 60 176 225 97 2001437 83 34 2164995 00 4 95000 930 60 185 756 57 2001487 33 35 2164995 00 4 95000 2930 60 194637 13 2001437 83 36 2164995 00 4 95000 2930 60 Z203617 T8 535 3 63 75 00 75 00 75 00 53653 63 1947329 25 37 2164995 00 4 95000 930 60 212 543 34 1947 329 25 38 2164995 00 4 95000 2930 60 221473 99 1947329 25 34 2164995 00 4 95000 930 60 230409 59 1947329 25 40 2164995 00 4 95000 3930 60 239340 20 1947329 25 4 2164995 00 4 95000 3930 60 243270 80 1947329
84. 7 1 Correction of income tax for financial items Correction of income tax for financial items row is used when tax effect of financial items are not included in free cash flow but are taken into account in the profit statement Any difference between profit statement income tax and cash flow statement income tax is shown here and included in total cash flow INCOME STATEMENT ai P EUR BES GREE 1 2015 12 2015 12 2016 12 2017 12 2018 Turnower E Variable costs Gross margin Fixed costs EBITDA 929 674 973 458 Depreciation 18 75 318 7 495 000 495 000 EBIT Operating income 228 99 434 674 478 ade Financing income and expenses 276 EBT Income after financing items 714 137 866 288 624 3 Net income for the period 76 100 A 256 741 CASH FLOW STATEMENT Piel E 1 2015 12 2015 12 2016 12 2017 12 2018 l Months per interval 12 12 12 12 Cash flow from operations Income D 1 600 000 1713 680 1831 347 1 953 116 Variable costs 0 269 760 282 499 295 640 309 194 Fixed costs 0 582 500 594 150 606 053 670 464 Extraordinary income amp expenses D 0 0 0 0 Income tax adjusted 0 68 697 95 484 230 246 143 538 amp Change in working capital 0 152 386 9 732 10 067 10 411 Cash flow from operations 0 526657 731815 689361 819509 Asset investments and realizations 4 950 000 YA AS Free cash flow to firm FCFF asooo NO 1289361 819509 Discounted free cash flow to firm DFCFF 4 950 000 478 084 Cumulative disc
85. 8195 309 09 4 10 2014 0 00 0 0 00 18 379 080 71 100 507 73 0 00 0 00 0 00 5 11 2014 0 00 0 0 00 18 379 080 71 199 226 43 0 00 0 00 0 00 6 12 2014 3 717 979 50 3 717 980 376 262 47 21 720 797 74 297 945 12 34 561 38 16 125 53 3291030 11 245 11 2034 0 00 0 0 00 355 856 57 2528 168 07 0 00 0 00 0 00 8353 446 34 246 12 2034 0 00 0 355 856 57 0 00 2529 280 12 6 672 31 0 00 362 528 88 8 715 975 21 ss Totals 22097 060 21 100 22097 060 21 0 00 2529 280 12 8294013 70 421961 52 8715975 21 8715 975 21 The consolidated file can easily be updated from the Project sheet Project information Description Consolidated Total investment 22 396 218 EUR Total financing 22 097 060 EUR 11 17 2014 Copyright O Datapartner Oy Page 147 Invest for Excel 4 7 Impairment test verification An impairment test is performed press Update by comparing the book value of an asset cash generating unit to value in use future discounted cash flow before tax default discounted residual value A verification is produced for recording purposes Rule Value in use book value lt 0 gt Impairment loss Value in use book value gt 0 gt No need for action Impairment Test Verification a Sja s Update Financial period Forecast period PAG Book value of asset 4 100 000 Discounted cash flow 90 670 Discounted residual value 40 834 Value in use B 131 504 Control value B A 31 504 o Prepared by stefan
86. Acquistions and realizations 1 000 000 o 0 Group realization profits losses 1 j 0 j j Tax effects 0 12 209 10 4 Balance sheet The goodwill of the corporate acquisition is shown under assets in the balance sheet offset by adjusted shareholders equity and liabilities with interest ASSETS O o rs n Group goodwill acquisition oul 477 ool 477 oo 477 GROUP ASSETS 501 477 195 227 833 168 SHAREH LDERS EQUITY AHD LIABILITIES Check Equity and liabilities 4s et o j o Equity correction 195 523 195 523 195 523 Interest bearing long term debt 1 000 000 1 000 O00 1 000 000 GROUP EQUITY AND LIABILITIES 601 477 95 227 833 168 0 o 0 Check Equity and liabilities Assets Group Page 202 Copyright Datapartner Oy 11 17 2014 Invest for Excel 11 Impairment testing and IFRS functionality 11 1 Impairment testing The Cash flow table and the Impairment test calculation table under the Balance sheet in Invest for Excel include a Value in use row Value in use is the present value of future cash flows in continuous use and the realization value of the asset Value in use is needed when applying IAS 22 IAS 36 and IAS 38 to perform periodical impairment tests A control value is calculated by comparing the value in use to the tested assets book value The method for calculating the control value differs if a corporate acquisition or an asset is tested A detailed Impairment test verification is availab
87. Admis Por ERA aa Alltranractions Draudoun aVandnfmeath TEUR Y of total 3 ato Paid Calentalrd Paid Calestaled Paid Caleslaled Paid Caleslaled Paid Caleslaled Paid Calestaled Paid Total Carhflau Cumulativ 216 499 50 10 i 499 501 4 95000 2165 00 2165 oo 494 959 494 599 50 00 50 00 H 3709 95 20774952 207 739 52 216 499 50 4 95000 293 06 393 06 324 75 207 729 52 216 499 50 4 95000 93 06 1726 12 324 75 207 729 52 216 499 50 4 95000 393 06 2679 13 324 75 207 739 52 324749 25 15 00 541243 75 4 95000 93 06 3572 24 324 75 1299 00 1299 00 323450 25 531239 77 541243 75 4 95000 2232 65 5804 9 270 62 531239 71 541243 75 25 00 1052 497 50 4 95000 2232 65 3037 54 037 54 270 62 541 25 541 25 532669 96 1063909 73 1032 497 50 4 95000 4465 30 12 502 55 150 42 1063 909 73 1032 497 50 4 95000 4465 30 16 963 15 150 42 1063 909 73 1032 497 50 4 95000 4465 30 21433 45 150 42 1063 909 73 1032 497 50 4 95000 4465 30 25393 75 150 42 1063 909 73 1082 497 50 4 95000 4465 30 30364 05 130 42 1063 909 73 1032 497 50 4 95000 4465 30 34829 36 26 791 34 180 42 26791 51 1037 117 92 1082 497 50 4 95000 4465 30 39294 66 180 42 1037 117 92 14 1082 497 50 4 95000 4465 30 43759 96 130 42 1037 117 92 5 432 999 00 20 00 1515496 50 4 95000 4465 30 d8 225 26 180 42 1623 75 1623 75 431375 25 146 493 17 16 1515496 50 4 95000 625142 54476 69 103 25 1463 493 17 1 1515496 50 4 95000 625142 607234 103 25 1463 493 17 13 151
88. BIT Operating income and thereby indirectly on cash flow 11 17 2014 Copyright O Datapartner Oy Page 71 Invest for Excel Note When estimating the feasibility of an investment its mode of financing is usually not taken into account The assumption is that the financing cost is the discount rate This principle is based on the idea that the investment itself should first prove itself profitable before doing more detailed plan of financing Normally financing is taken into account only if the investment object is tied to a particularly beneficial mode of financing e g a subsidy subvention The row Financial income and expenses is for ntering financing costs and income from financing You can enter the NET of fi ancirgiacome antrexpenses on_one row or vou car use specification rows and detailed rows to specify finametag costs an You may name specification rows Users of Enterprise edition can use the financing module for the calcul and detail rows as you wish other types of financing costs involved The costs associated with financing as calculated m the Financing module can be imported to this table using the 1 button NOTE Please note that financing costs do not affect NPV IRR or Payback by default In the Income Tax Options dialog button in BASIC VALUES you can select to include tax effects from financing Extraordinary income and expenses Realisation profit or loss from investment object
89. C Consolidation C Variable costs C Balance U Impairment test _ Fixed costs E Program guide _ Key financials verification _ Income variable User manual pdf C Financing LJ Investment proposal C Charts aay Investment file Invfile3 gt Ja Comparison file Invcomp new flight route scer mp 15 Proposal file lt Not open gt mp 3 a Financing file Invfin airplane xlsm gt A www investforexcel com SpA 7 2 Change Language The Change Language function is quite useful It changes the language of parts of the program as selected In addition to the text in tables the language of printouts and the Manual texts will change You can make use of all the languages English Finnish Swedish German Polish Spanish and Russian You can change language by clicking the globe icon in the Home Screen or from the Invest for Excel menu Other Change Language lt is possible to change the language separately for The commands of the Invest for Excel program Any Calculation file Any comparison file and Any investment proposal file Any financing file Any impairment test verification file Page 184 Copyright Datapartner Oy 11 17 2014 Invest for Excel Choose type of files Select the files the that you want to language of which change language for you want to change Choose the language here Chdgose program modules which language yo Command language menus etc Calc
90. CONSOLIDATED CASH FLOW STATEMENT 12 2010 12 2011 12 2012 Cash flow from operating activities Operating profit before depreciations continuing operations 1183 1 246 1 307 Non cash flow items and divesting activities 0 0 0 Net financial items 511 4T3 435 Dividends received 0 0 0 Taxes 100 123 144 Funds from operations continuing operations of 650 126 Change in working capital 134 56 55 Net cash from operating activities continuing operations 437 594 673 Net cash from operating activities discontinued operations 0 0 0 Total net cash from operating activities 437 594 673 Cash flow from investing activities Capital expenditures 100 104 108 Acquisition of shares 9 000 0 0 Proceeds from sales of fixed assets 0 0 0 Proceeds from sales of shares 0 0 0 Change in other investments 0 0 0 Net cash used in investing activities continuing operations 9 100 104 108 Net cash used in investing activities discontinued operations 0 0 0 Total net cash used in investing activities 9 100 104 108 Cash flow before financing activities 4 663 490 565 Cash flow from financing activities Net change in loans 6 400 500 00 Dividends paid to the Company s equity holders 0 0 0 Other financial items 0 0 0 Net cash used in financing activities continuing operations 8 400 600 600 Net cash used in financing activities discontinued operations 0 0 0 Total net cash used in financing activities 8 400 600 600 Total net increase decrease in cash and market
91. D444 Distribute as 5 Values 8 Distribute Value of active cell Annual change 8 Formulas Other options Copy cell formatting to target cells Change indicators Change yearly Index base year 100 Copy Formula in active cell Copy Distribute options 9 2015 102015 H444 H445 Adjust with Distribute Value of active cell O number of 270 aa E 270 months per interval Annual change Fe Put annual change in pm D447 Distribute as Values Formulas Other options Copy cell formatting to target cells Change indicators L Change yearly 56 C Index base year 100 11 17 2014 Last period to include 12 2017 12 2018 12 2019 12 2020 Distribute Cancel Last period to include 10 2015 11 2015 12 2015 12 2016 12 2017 12 2018 12 2019 Copy Cancel Copyright Datapartner Oy in the columns referring to the previous column This makes simulations a lot easier In addition you can distribute the value of active cell as numbers instead of formulas should the situation call for it This leads to a static calculation making simulations more challenging Use the default Distribute Value of active cell and accept the selection with the Distribute button When you copy a cell containing an Excel formula to adjacent co lumns use the Copy formula in active cell function and click the Copy button See example to the left Other options Use
92. DataPartner Invest for Excel Then reinstall the program Problems with taking the program into use Note Our Excel programs use macros Allow the use of our macros the first time you use the program If nothing happens when you try to run the program then you might have as Macro Settings Disable all macros without notification in that case you need to change to Disable all macros with notification The lowest security level is not recommended The program doesn t start If the macro security level is high and the program doesn t start then it is possible that the digital certificate has expired Then you have to set the macro security level to Medium in the Excel menu Tools Macro Security Medium The lowest security level is not recommended A new digital certificate will be distributed with the next program version 1 4 Start up Invest for Excel s start up file is named INVEST XLAM Excel 2003 and older INVEST XLA You can start the program in four alternative ways e Click Start select Programs DataPartner Invest for Excel or e Start Excel and select from the menu File Open and select INVEST XLAM or e Open the file INVEST XLAM from the file list of files in Windows Explorer or e Click the Invest for Excel start up icon on your desktop Enabling macros Microsoft Excel Security Notice Microsoft Office has identified a potential security concern Warning The digital
93. E SharePoint server integration Page 230 Copyright Datapartner Oy 11 17 2014
94. E A e adie 209 24A EXCELES GOAL SEEK FUNCTION e eis hea eee 210 13 THE FINANCING MODULE ENTERPRISE EDITION ccsssccssscccsscccsssccscsccssccees 211 ESE GENERADA re Pe we ee aer aia 211 32 gt PROJECT SUPE arca balla 212 130 CURRENCY SHEET E E AE A oni EE N EE E A E E E E EE AE 214 F354 MM a aa ae a O AA AEE 214 T341 CUA CIU CHOICES AAA TASA TA E E N 215 ES NES A A O OAA 216 156 SOM SPECS HB aa aaa a arado lefa 220 IS VIEW SHE Da ts e a a entonado de aa ad nn EE s Aen EIET Rt hater 222 13 6 HANDENG OEMUCTPLE LOANS dt a a ld A aha el 223 13 9 UPDATING YOUR CALCULATION FILE WITH FINANCING cccecescecccceccececscsceccscecescecescesesccecs 223 14 CUSTOVM MACRO So neice sareeeicncosedacaaucbescnibidesccavebeciacccedosseuslacessseeeces 226 15 FEATURES OF INVEST FOR EXCEL cccccccccccccccccccccccccccccccccccccccccccccccccccscccocce 230 11 17 2014 Copyright Datapartner Oy Page 9 Invest for Excel 1 Introduction Invest for Excel was originally designed for investment appraisals capital budgeting investment feasibility studies investment calculations but has evolved to be a generally applicable financial modelling software Invest for Excel now also supports areas of usage like Valuation mergers and acquisitions Business planning budgeting forecasting long term strategic planning Profitability analysis of projects business products customers restructuring Project financ
95. EBIT Operating income Financing income and expenses EBT Income after financing items Extraordinary income and charges Income before appropriations and taxes Change in appropriations Appropriations increase decrease Income tax Deferred tax 2 Net income for the period Accumulated Deferred taxes are shown under Long term debt in the Balance sheet Liabilities Long term liabilities interest bearing long term debt Interest free long term debt Deferred tax liabilities Short term liabilities Total liabilities SHAREHOLDERS EQUITY AND LIABILITIES 11 17 2014 Copyright Datapartner Oy Page 87 Invest for Excel 3 2 5 9 Financial ratios Return on net assets ROHA dy ETTA 6 AG 7 Economic Value Added EVA 1 206 Met assets average 3 461 E Capital charge on net assets 174 331 When you click the bi button at the Return on Net Assets RONA row you can select the calculation basis of the RONA and EVA indicators Options Financial Ratios other Options Met assets is based on Opening balance Balance at the end of period Operating profit before tax EBIT Met operating profit after tax NOPAT Met income For the period Financial items appropriations RONA is based on Operating profit before tax EBIT Met operating profit after tax MOPATI Met income For the period Financial items appropriations EYA is based on ROPAT Use calculated tax EBIT tax percen
96. EUR 449 Produced kWh kWh 450 451 452 453 454 455 456 457 Reset to default settings Choose row s to base the calculation on from the list of rows In the table you can enter average number of days that you want to use as the term of payment E EUR BES O rr lt gt Nr 12 2015 12 2016 12 2017 yy A _ __ e Short term assets 1 Average term of payment days eS Accounts receivable 201 600 213 797 226 732 Adjusted balance Increase decrease 201 600 12 197 12 935 Page 92 Copyright O Datapartner Oy 11 17 2014 Invest for Excel 3 2 6 2 Short term assets Current receivables How much short term assets an investment project or business ties up depends mainly on Accounts receivable There are two ways to enter the accounts receivable 1 Enter the average term of payment for accounts receivable in days e the average number of days from delivery until payment and the program calculates the average amount of accounts receivable per interval based on sales Income row in Income statement and rotation Invest for Excel liquidates the accounts receivables automatically at the end of the investment term in the last column otherwise they would remain outstanding credit loss To override this feature type in a value on the row for Adjusted balance in the Residual column 2 Enter the estimated average accounts receivable per period on the line Adjusted balance This is an alterna
97. IAL LOAN AS TYPE OF FINANCING Please use following working order while entering parameters for debt Financing description Please feel free to describe this debt with own words Currency Select the currency of the loan Total amount Enter the size of the loan to be taken or of total financing if you entered the debt amount on Project sheet Financial closing Date when the loan is granted and possible startup costs are paid Select month and year Drawdown period Loan withdrawal period E g the length of the construction period of a project Enter the length of the drawdown period The drawdown period is defined as the time from the first drawdown to the starting point of credit i e the beginning of the repayment period which usually falls six months before the first repayment of principal If the transaction involves only a single loan withdrawal the drawdown period is usually zero In all other cases enter the anticipated number of months between the first draw and the starting point of credit Repayment period The term for repayment of loan in years months E g 10 years O months Starts at Starting Point of Credit Three options A Financial closing B First draw and C The end of drawdown period default When option A is selected the repayment period begins immediately from financial closing When option B is selected the repayment period starts from first withdrawal When option C is selected the repayment peri
98. Income specified Sales forecast 23 345 24 120 22 970 23 988 24 127 24 127 24 127 24 127 Income cumulative financial year Other operating income Variable costs Gross margin 21 864 22 326 21 314 22 232 22 337 22 337 22 337 22 337 Fixed costs total 4570 4610 4660 4 680 4703 4703 4703 4703 provsionsincease I7 demease 0 A A A A EBITDA Operating income before depreciation 17 294 17 716 16 654 17 552 17 634 17 634 17 634 17 634 Depreciation 32 35 35 44 88 247 7 691 EBIT Operating income 17 262 17 681 16 619 17 552 17 590 17 546 17 387 9 943 Financing income and expenses 120 122 A AR 125 125 125 125 tinancngincomeandemenses Fnendngfle PP EBT Income after financing items 17 142 17 559 16 497 17 428 17 465 17 421 17 262 9 818 A tsao afl Gaeta Net income for the period 17 142 17 559 16 497 1 488 17 465 17 421 17 262 cumulative financial year 17 142 34 700 51 197 52 685 17 465 34 885 52 147 cumulative financial year 73 4 73 1 72 7 55 8 72 4 72 3 72 0 47 5 In the Invest for Excel menus Roll forecast can be found in the Input page ca B File Input Result Analysis Format Other 6 Aaa a a gus E Home Basic Contact Investment Income Working Cash Balance Key screen Values Info statement Capital Flow financial Basic Values Calculations Rall forecast inations Financing Excel Menus Home Financing Menus In the sho
99. Invest for Excel Invest FOR EXCEL Version 3 7 Earnings and FCF 3 000 000 60 58 2 500 000 56 2 000 000 54 5 1 500 000 52 50 1 000 000 48 500 000 46 0 44 12 2016 12 2017 12 2018 12 2019 12 2020 12 2021 12 2022 12 2023 12 2024 Mas Turnover Mumm EBITDA E Free cash flow to firm FCFF EBITDA margin User Manual DataPartner Copyright Datapartner Oy A A for Excel Page 2 Copyright O Datapartner Oy 11 17 2014 Invest for Excel Copyright DataPartner Oy DataPartner Oy accepts no responsibility for any errors or omissions in this publication Further more DataPartner Oy accepts no liability for damages arising from the use of the program or this manual All rights are reserved regarding copying distribution and translation of this document This program is covered by copyright legislation Unauthorised duplication and distribution of the program or its contents is prohibited Any copying of the program apart from a safety copy for personal use is prohibited DataPartner Oy is not responsible for a program package tampered or altered in any way by the purchaser or a third party Microsoft Excel Office and Windows are registered trademarks of Microsoft Corporation Acrobat Reader is a registered trademark of Adobe Systems Incorporated The supplier accepts no liability for any indirect loss or damage suffered by the customer due to supplier s delay or omission Ther
100. Invest for Excel options Menu Other Options Options Financial Ratios Other Options lt Default settings For new investment File gt 11 17 2014 Copyright O Datapartner Oy Page 161 Invest for Excel When automatic update is activated every analysis on a sheet is updated when the sheet is acti vated Note that this updating takes some time 5 2 Discount factor analysis You can analyse the discount rate s impact on profitability by entering alternative discount rates A 10 and 20 change in the discount rate is shown as default Enter the analysis values and then click on the Ly button to the left Note that the analysis requires manual updating by pressing this button Discount factor s impact on profitability Discount factor Ht 9 60 10 80 12 00 13 20 14 40 Change 20 0 1 0 0 0 0 1 0 0 20 0 Net Present Value NPV 14 910 9 545 4 557 88 4 419 Change 227 2 1095 0 0 101 9 197 0 Net Present Value NPV Keyman E BIT Operating come before depres y oa de BI Opeatngincore USO y 9 60 21 660 67 7 11 660 36 4 12 3 10 80 21 660 67 7 11 660 36 4 12 3 12 00 21 660 67 7 11 660 36 4 12 3 2772 13 20 21 660 67 7 11 660 36 4 12 3 14 40 21 660 67 7 11 660 36 4 12 3 Economic Value Added EVA USD ww 492 1 632 3 912 9 052 The analysis can be done for Net Present Value Profitability Index
101. Isb Title Excel 97 2003 Workbook xls Subject XML Data xml Single File Web Page mht mhtml Web Page htm html Excel Template xItx Excel Macro Enabled Template xltm Excel 97 2003 Template xlt Text Tab delimited bd Unicode Text txt XML Spreadsheet 2003 xml Microsoft Excel 5 0 95 Workbook xls CSV Comma delimited csv Formatted Text Space delimited prn Text Macintosh txt Tait AAC MAC Pe a Manager Company Categories Comments a Hide Folders 11 17 2014 Copyright Datapartner Oy Page 159 Invest for Excel Folders And Files Folders Template Files ZIP when creating a new file use as template Investment File f Invests default investment file template CO Custom file Fixed assets Acquisition Impairment best pe hi Proposal File Invests default proposal file template le Custom File C InvestiMyPilesiMyProposal xls El Financing File f Invests default Financing file template f Custom File A Cancel To return to the Invest for Excel menu select the Invest for Excel option added to Excel s menu To access your modified form go to Invest for Excel s File menu select Folders and Files Select the Template Files tab and select the template you want Page 160 Copyright O Datapartner Oy 11 17 2014 Invest for Excel 5 Analysis Sensitivity analyses are aimed at reducing
102. LAND T www dataparnner fi This productis licensed to Organisation DataPartner Software User Jens Westerbladh License Number V04 01 00261 L Warning This computer program is protected by copyright law and international treaties Una or distribution of this program or any portion of it may result in severe civil and criminal penalfes The compilation number shows the exact release of your version of Inyest for Excel By pressing the button you get file version information on files Invfilei 3 7 3 7 001 Invfile Hotel MAX 500 xlsm 3 6 3 6 018 DE Gas LongtermPlan2012 23 xlsm 3 6 3 6 006 District Heat Network xls 3 2 3 2 002 Page 192 Copyright O Datapartner Oy 11 17 2014 Invest for Excel 9 Balance sheet Pro and Enterprise edition As a feature Balance Sheet gives the person doing investment calculations new scope This feature complements Invest for Excel calculations but its use is by no means obligatory Like other tables the Balance Sheet table has been divided into intervals It contains links to figures and sums from other tables as default To unhide the Balance Sheet categories and hide again click these buttons LE amp 9 1 Fixed assets and other long term investments INTANGIBLE ASSETS TANGIBLE ASSETS INVESTMENTS If you have checked the Include Historical Data option in the Calculation term dialog box you can enter his
103. LOYEES TECHNICAL STAFF CLERICAL STAFF PERSONNEL ADMINISTRATION LABOUR PROTECTION ADMIN 11 17 2014 Copyright Datapartner Oy Initials MAINTENANCE PRODUCTION MATERIALS QUALITY ADMIN Page 153 Invest for Excel Some data in the investment calculation can be copied using the button If you have several investment calculations open the program prompts you to choose one of them The Update function will take a while to run Enter the necessary data in other words use the applicable parts of the form Department Informative text field Drawn up by Name of person who did the investment proposal Date Date when the investment proposal was made or updated Investment object Informative text field on the investment Investment number Numbering of the proposal Project name and number Informative text field Description of investment Informative text field Go to this field with the mouse Implementation term Lead time from order to commissioning of object Project begins Project kick off month year Completion time Time when project is due for completion month year Start up time Utilization of object begins month year Investment capital Sum of proposed investments Feasibility and grounds for the investment Informative text field Go to this field with the mouse Environmental effects of the investment Acknowledgement of effects if any according to corporate regu lations Person in
104. Number of passengers currency data 469 Fuel cost per passenger 473 Number of passengers pa i gt AE Click the button 488 Ground staff oy 490 Maintenance costs fen The specified 847 Non currency 848 ree 849 rows will not be 850 i affected by the 851 currency trans 352 i j lation 854 855 856 857 Non monetary rows are not changed during currency In the second page you specify the conversion rate to use and if you want to convert formulas to values and lock the file 1 Specify non monetary rows 2 Currency exchange rates and options Current Figures 1 1000 Currency conversion rates 1 Convert all entered values using the same currency conversion rate entered here or choose Multiple rates if you want to convert different periods with different rates Conversion rate 9 16 EUR 9 16 1 1000 1000 SEK Note that formulas are not converted unless the option Convert formulas to values is selected lonerate _tuttiple rates Page 40 Copyright O Datapartner Oy 11 17 2014 Invest for Excel Note that in a currency conversion only value cells are converted and fomulas remain unchanged As a result a formula like 13000 5500 27000 will remain unchanged This could lead to erroneous results unless the option Convert formulas to values is used Multiple conversion rates can be specified if needed 1 Specify non monetary rows 2 Currency exchange rates and
105. Operating income USD AN 13 660 EBIT N 42 7 Return on net assets RONA z 18 0 Economic Value Added EVA USD 988 90 000 10 0 12 458 173 4 100 000 90 000 21 660 67 7 12 660 39 6 14 8 892 100 000 0 0 4 557 0 0 110 000 100 000 21 660 67 7 11 660 36 4 12 3 2 772 110 000 10 0 3 343 173 4 120 000 110 000 21 660 67 7 10 660 33 3 10 2 4 652 120 000 20 0 11 244 346 7 Beneath the bar chart is another drop down list from which you can choose the period for the financial statement s key figures you want to analyse The table shows how the change affects the Operating Income Return on Net Assets and Economic Value Added Operating Result comes from the Operating Profit cumulative financial year row in the Income statement Page 164 Copyright O Datapartner Oy 11 17 2014 Invest for Excel 5 4 Income analysis You can see the effect that changes in the total income has on profitability The program shows the impact of a 10 and 20 change as default The default percentage can be changed freely When you enter a change in income as a percentage e g 5 or 5 remember to click the button Ly to the left of the figures as only the click updates the values From the drop down list choose alternatively Net Present Value NPV Internal Rate of Return IRR Internal rate of return before tax Modified
106. Pl Discounted Value Added DCVA Payback time years and Simple payback time years Additionally up to six key financial ratios including user created ratios can be included for a selected period When Free cash flow to equity is included in the calculation Net Present Value to equity Payback time to equity years and Simple payback time to equity years can also be analysed Change in Cost of equity is used for these profitability ratios Page 162 Copyright Datapartner Oy 11 17 2014 Discount factor Change Cost of equity Change Net Present Value to equity NPVe Change Key financials 12 2013 Invest for Excel Discount factor s impact on profitability o 9 60 20 0 10 80 12 00 10 0 0 0 12 80 14 40 16 00 20 0 10 0 0 0 La 20 751 13 891 7 625 4172 1 82 2 0 0 Net Present Value to equity NPVe 14 40 12 80 14 40 16 00 EBITDA Operating income before deprec 21 660 21 660 21 660 EBITDA EBIT Operating income USD 67 7 67 7 67 7 m 11 660 11 660 11 660 EBIT La 36 4 36 4 36 4 Return on net assets RONA 12 3 12 3 12 3 Economic Value Added EVA USD 492 1 632 2 772 11 17 2014 Copyright Datapartner Oy 13 20 10 0 17 60 10 0 1891 75 2 17 60 21 660 67 7 11 660 36 4 12 3 3 912 14 40 20 0 19 20 20 0 3 367 144 2 19 20 21 660 67 7 11 66
107. STMENTS REALIZATIONS E AES 1 2005 12 2005 Residual Months per interval Depr 12 12 2005 1 Depreciation straight line Book value Now the asset is not liquidated no residual effect on cash flow You may always enter the residual value of the object INVESTMENTS REALIZATIONS PES 1 2005 12 2005 Residual Months per interval Depr J 42 12 2005 30 Depreciation straight lime 20 Book value o wwo y ee 11 17 2014 Copyright Datapartner Oy Page 63 Invest for Excel 3 2 4 2 15 Apply depreciation options to multiple investments Depreciation options defined for one asset can easily be applied to multiple investments This will save time if you have many assets using the same or similar depreciation options Press the El button beside the asset name to open the Apply Depreciation Options dialog box E 2 E ta Depreciation 10 Less options Depreciation time years 10 Shift Factor 1 Factor 1 Depreciation method Depreciation method Asset 1 Product line 4 Old Investment Mew Investments Declining balance One time depreciation Declining gt straight line sum of years digits 12 2007 months 12 Enter manually Begin depreciation Select the investment rows you want to apply the depreciation options to Apply Depreciation Options Select the investments to which vou wank to apply the EN depreciation options
108. SW Documents Calc Templates Invccy 1 xlsm x 2 2 Explore folder of active file Explore active file folder opens a new Explorer window showing the folder for ea the active file Explore folder of active file 2 3 New When creating a new calculation comparison proposal or financing file go to Invest for Excel s own File menu and select the command New e To make a new calculation select New Calculation file e To compare the results of different calculations scenarios select New Comparison File e To make an investment proposal select New Investment Proposal or e New Financing File to plan debt financing e New Impairment test verification for a detailed impairment test verification e New Currency File to create a new list of currency conversion rates 11 17 2014 Copyright Datapartner Oy Page 25 Invest for Excel Mew Calculation file New Comparison File New Investment Proposal New Financing File New Impairment Test Verification New Currency file Save in Private Folder Common Folder Choose here if you want to save the file in your private folder default or in the common folder e g on a file server If neither is selected then the last accessed folder is used When you choose New Calculation file and you are using the Enterprise edition of Invest for Excel you will get a new menu to select the main type of template this doesn t apply for Pro Standard or Lite
109. To update In the acquisition template in Share os 4 the Goodwill calculation you will find a Share capital button for financing of acquisition Share issue premium Other restricted equity Retained earnings Profit loss for the period Depreciation difference Overvalue before tax liability Deferred tax liability Overvalue El Allocated overralue before tax E Allocated deterred tax liability Total allocated overvalue Goodyvill Equity correction Interest bearing long term debt Amortizations Press the button A Dialog box will Financial expenses appear Group Loan Purchase price Financing Loan Select Use Financing file and Enter press Update Loan amount Loan of purchase price Repayment term years Interest other costs percent per annum Equity Equity amount Equity of purchase price Equity costs cancel 11 17 2014 Copyright Datapartner Oy Page 225 Invest for Excel 14 Custom macros Use the macro definitions sheet to use macros with the calculation file The macro definitions sheet can be shown by clicking Show macro definitions sheet in the File Locking Options dialog box File Locking Options Password HO Basic Values Calculations Result Check the Functions that should be LOCKED Calculation point relative to starting period Residual value calculation Figures 1 1000 1000000 Currency Discount Factor Tax p
110. Westerbladh Date and signature 11 9 2006 15 52 13 Enclosures Enclosure 1 Specification of calculation Enclosure 2 Enclosure 3 Enclosure 4 Enclosure 5 Enclosure E Page 148 Copyright Datapartner Oy 11 17 2014 Invest for Excel The verification can be locked from modifications by pressing Note Once the file is locked it can t be unlocked A specification of the calculation is automatically enclosed Enclosure 1 Specification of calculation Cash flow statement eo tzz006 aoa aooe 122008 t22010 1220m 122012 122098 _ 122014 122015 Extraordinary income amp expenses Change in working capital A E Neteashfow Discounted net cash flow 11 974 12045 10 804 9985 9 227 8 527 7 880 7 282 6729 47 052 Cumulative discounted net cash flow 11974 24019 34 824 44 808 54 035 62 562 70442 77 723 84 452 131 504 Cash flow changes 12 2006 12 2007 12 2008 12 2009 12 2010 12 2011 12 2012 12 2013 12 2014 12 2007 12 2008 12 2009 12 2010 12 2011 12 2012 12 2013 12 2014 12 2015 1 00 1 38 1 00 1 00 1 00 1 00 1 00 1 00 1 00 i 1 00 1 38 1 00 1 00 1 00 1 00 1 00 1 00 1 00 Fixed costs 0 50 0 69 0 50 0 50 0 50 0 50 0 50 0 50 0 50 AAA of value in use 12 2006 12 2007 12 2008 12 2009 12 2010 12 2011 12 2012 12 2013 12 2014 12 2015 net cash flow 11 974 12045 10804 9985 9227 8527 7 880 7282 6729 6218 90670 ae A residual
111. a new Investment calculation always start by entering 1 Basic Values and 2 Contact Information if needed After that you specify 3 Investments capital expenditures 4 Income and costs in the Income statement Table and 5 Changes in working capital in the Working Capital table The program then calculates the following in respect of the investment 6 Cash flow the calculation can be supplemented with financing data 7 Balance sheet calculated on the basis of your data input You may supplement the balance sheet with an opening balance Next you will see the result of the calculation in the 8 Profitability analysis Now you can analyse the effect of various variables on the profitability of the investment These variables include 9 Discount Rate used in calculations 10 Total Investment 11 Income 12 Variable costs 13 Fixed costs 14 Income Variable and 15 In addition to the above analyses you can create analysis charts for any input variable Na ar ar ar CON If necessary you can always return to input screens to simulate different situations That way you can have more than one variable in a chart Remember to save your work If you have several alternative investment projects you can compare them in the 16 Comparison table The differences between the alternatives can be illustrated using the following charts NPV chart IRR chart and Payback chart When you are satisfied wit
112. able securities 263 110 35 Total net increase decrease in cash continuing operations 263 110 39 Calculation sheet items can be included in the IFRS reports either as Continuing operations or Discontinued operations if thats appropriate As default all items are included in continuing operations Investments can be specified as part of continuing operations and discontinued operations in the Depreciation method dialog box Balance Sheet Items Investment category IFRS Is the asset part of continuous or discontinued operations Continuing operations Discontinued operations Note This will only affect grouping in IFRS reports 11 17 2014 Copyright Datapartner Oy Page 207 Invest for Excel Income statement rows can be divided in continuing and discontinued operations in the IFRS page of the Hide Show Rows dialog box Hide Show Ro Hide Show Rows Edit Row Texts IFRS Row Row text 35 Turnover 456 457 458 Other operating income E Continuing operations y Raw materials and consumables Continuing operations External charges Continuing operations J Staff costs C AT rn PO Continuing operations aae O CI 1 Note This feature requires the Enterprise edition of Invest for Excel Page 208 Copyright O Datapartner Oy 11 17 2014 Invest for Excel 12 Hints 12 1 Creating links to Excel workbooks When you are working on an Invest for Excel calculation and you want to cr
113. alculation Type of calculation Template Files Large investments C Users StefanWv Documents Large investments jectCalculationA xlsm 79 6 2010 18 31 ProjectCalculationB xlsm 29 6 2010 18 31 Save in Private Folder Common Folder 11 17 2014 Copyright Datapartner Oy Page 27 Invest for Excel 2 4 Open Go to the File menu and select Open to open a previously saved file Open ES Y A de gt ThisPC Local Disk C Users JENSW Documents Calc Examples v Search Examples pP Organize v New folder a EP SharePoint Name Date modified Type Size l d Budgeting 27 6 2014 12 42 File folder t OneDrive l CORRERE dJ Business planning 7 7 2014 11 00 File folder Electric Utilities 7 7 2014 11 01 File folder E This PC o a a i n Le Hospitality Management 15 10 2013 10 46 File folder je Desktop gt R sia hal a ps de Production 22 8 2014 16 38 File folder El Documents EENEN P ae Je Project profitability analysis 3 7 2014 17 54 File folder lg Downloads an he de Retail 15 7 2014 14 05 File folder i Music DB Testi 37 a 1 20 File f ms esting 3 25 8 2014 11 29 e folder E Pictures A N EE A de Transport and Logistics 31 7 2014 11 34 File folder Eh ege gr svifi isee z ae 3 y f FCF and terminal value xlsx 15 7 2014 13 58 Microsoft Excel W Ao BE Videos ani v lt gt aw I on Mat 147 4 File name FCF and terminal value xlsx w Inves
114. am shows row numbers automatically on the left side in the calculation This will help you to choose the right rows to hide unhide Page 78 Copyright Datapartner Oy 11 17 2014 Invest for Excel 3 2 5 3 11 Clear input cells of hidden rows When hiding rows in a calculation file you can choose to clear input cells on the rows to be hidden This way you can make sure that no numbers or formulas that could impact the result of the calculation will accidently be left on the hidden rows Text cells will not be cleared Check Clear input cells of hidden rows in the Hide Show Rows dialog box to use this function when you are hiding rows Hide Show Rows Edit Row Texts Select the rows you want to hide Row text Income specified Sales A Sales B Empty row Empty row Empty row Empty row Empty row Empty row Empty row Empty row Income cumulative financial year Other operating income Variable costs Raw materials and consumables External charges Staff costs Other variable costs Empty row Empty row Empty row Empty row Empty row Empty row Gross margin cumulative financial year cumulative financial year Fixed Staff costs 11 17 2014 Copyright Datapartner Oy Page 79 Invest for Excel 3 2 5 3 12Flip buttons for sub rows Flip buttons are automatically added for sub rows e Specification rows and Change indicator rows You can toggle hide unhide sub rows for th
115. an index where the starting year is 100 Notice that these indicators are calculated for your information only they are not input cells Use Copy Distribute function to edit change percentage After some practise you will find the benefits of this Copy Distribute function compared to the normal Copy function One of the benefits is the linking of formulas between cells which is a precondition for the Break even function If you change the figure in the first column and a formula covers it and the adjacent columns the figures in the other columns will also change Use these buttons to move between the first and the last column of the table Zl Cell Break even button Refer to the Cell Break even chapter 5 9 later in this manual for more information 11 17 2014 Copyright Datapartner Oy Page 85 Invest for Excel 3 2 5 6 Formatting cells Formatting calculation file cells is allowed using Microsoft Excel s menus and shortcuts 16001 Cut 269 7 EE Copy 194 ip Paste Options 149 o 2 2 ia i oo y o Paste Special 451 791 Clear Contents MN 5i 1330 Filter Sort Format Cells Pick From Drop down List 3 2 5 7 Provisions Changes in provisions can be entered in the Income statement before EBITDA Changes in provisions are non cash items and will affect free cash flow only through changes in income tax INCOME STATEMENT 1000 HES m 12 2010 42
116. and CAM iii dec 194 9 3 SHAREHOLDER S EQUITY AND LIABILITIES csccceccsccsccsccsscscesccssescesccecceccescescescesscccescescencs 196 9 3 1 Shareholder SEU aa E dai a reay 196 93 2 Accumulated Appropriations and minority INterestS ooooooononnnnnnnnnnnnonanononnnnnnnnnnannnnnnnnnnnnns 196 9 3 3 PP RR 196 10 CORPORATE ACQUISITION ENTERPRISE EDITION oococooccconoscccconnocccconnoscccconcosccccos 197 101 INVESTMENT a 197 10 1 1 Allocation of overvalue according to IFRS 3 ooooococonncananccnnnnnnnnnnonnnnnnnnnononnoncnnnnnnnnnanocnnnnnos 198 I0I2 Depreciation OF CIO AMAS ans 200 JOAS A E TT EA ATAT 201 V INCOME STATEMENT a A N E 201 WO AMAS OW E AE AE E ss 202 VA aA a a a tac on tuneoes 202 11 IMPAIRMENT TESTING AND IFRS FUNCTIONALITY eeseeeesseeccsccccsecccosccccsceccsecccseeccseeeo 203 FIE IMPAIRMENT TESTING aiii 203 Fid ISCONDOKAIC ACQUISITION SAA AA EE E OTA AOE 203 SE INCAS O unta a e a a a a a a 203 TL Ampa rmen tes OPONE AN E E O NEO 203 E IPRS REPORT SHEE Ponsa ia ona 204 11 2 1 Consolidated income See nt did iaa 205 Page 8 Copyright O Datapartner Oy 11 17 2014 Invest for Excel E722 Consolidated balance Mt e e o 206 11 2 3 Consolidated cash flow statement oooccccnnncnnnonnnnnnnnnnnnnnnnnnnnnnnnnnonnnnnnnnnnnnnnnnnncnnnnnnnnnnnnninnnns 207 12 CINES di il iia 209 12D ECREATING EINKS TO EXCEL WORKBOOK S ns e e a al dl ew 209 122 ISI IE MPA e e ie ee De ee eae 209 123 INCLUDING POSITIVE TAE E Aa einai
117. and enter a number see the example above You can also add references to the calculator To do this click one of the fields in the calculator with the mouse Then click the cell in the calculation table that contains the reference value see the example above Use the left mouse button to choose a calculation operator from the pull down menus Click the Enter button to copy the result to the active cell in the calculation table Page 190 Copyright O Datapartner Oy 11 17 2014 Invest for Excel 7 9 Excel menus This function changes the Invest for Excel menus to Excel menus Excel menus enable you to make better use of Excel s functions Please note though that an additional menu has appeared under the Excel menu Invest lt contains some of Invest for Excels functions 7 9 1 Invest for Excel Menus This function enables you to restore the Invest for Excel menu structure ES Invest for Excel Invhome xls Read Only re File Edit View Insert Format Tools Data Window Help Invest Adobe PDF BrT FEIE EET Sa A ea Arial 8 B 7 UE EA SW Al X fe Investment calculations Invest for Excel Menus Investment Income statement Working Capital Cash Flow Balance Key Financials File Format Input values Calc AEREA _ Basic values LJ Inves calculator Show Toolbar _ Contact information LJ incon put screen E Work About Invest for Excel
118. apartner Oy Page 143 Invest for Excel 4 6 5 Currency file Currency translation is available in Enterprise edition for consolidation of calculation files and financing files A currency file can be used to handle currency translation rates To create a new Currency file choose New in the Invest File menu Choose New Currency File in the opening dialog box Mew Calculation file Mew Comparison File New Investment Proposal New Financing File New Impairment Test Verification New Currency file Save in Private Folder common Folder A new currency file while will open Currency translation rates Refresh 1 000000 EUR EUR date 1 341500 USD EUR 133 270000 JPY EUR 13 11 2013 13 11 2013 You can enter the currency translation rates manually or update the rates from the European Central Bank web service by clicking the a button Note that all rates that are found in the European Central Bank web service will automatically be updated Note that for this to work you need to be connected to internet and Microsoft Excel must be allowed to run internet queries A new currency file can also be created from the Consolidation dialog boxes Page 144 Copyright Datapartner Oy 11 17 2014 Invest for Excel 4 6 6 Consolidation of financing files To consolidate financing files press Financing files in the Consolidate dialog box C Users StefanW Documents Business
119. argin 22 326 21 314 22 232 Fixed costs 4 610 4 660 4 680 22 337 22 337 22 337 4 703 4 703 4 703 EBITDA Operating income before depreciation 17 716 16 654 17 552 Depreciation 35 35 EBIT Operating income 17 681 16 619 17 552 Financing income and expenses 122 122 124 Financngincome sndevpenses fnancngtle EBT Income after financing items 17 559 16 497 17 428 Net income for the period 17 559 16 497 1 488 cumulative financial year 17 559 34 055 35 543 cumulative financial year 72 8 72 3 50 0 17 546 17 387 17 342 17 421 17 262 17 217 17 421 17 262 3 723 17 421 34 682 38 406 72 2 71 9 53 1 72 1 Fixed costs total 4 610 4 660 4 680 4 703 4 703 4 703 Provisions increase decrease You can see that actual values 2014 have moved left and quarter 3 2015 is cleared for actual numbers entry When rolling periods only all numbers stay in their cells and only the periods are moved one column to the left 11 17 2014 Copyright O Datapartner Oy Page 103 Invest for Excel 3 3 Debt financing Enterprise edition With the additional Financing module included in the Enterprise edition you can plan the financing of your investment Use the Financing module to find the need for financing in your investment calculation and transfers interest expenses and amortisation instalments to it The financing module will be explained in more detail in Chapter 11 Financing module Here is only
120. ately Starting balance Drawdown for drawdown period Capitalized interest Principal payments Repayment period and repayment Ending balance period empty row Total Repayment period term Interest expense fempky row Number of months per column Loan arrangement Fee Guarantee fee Billing charge Hide Repayment total empty row fempty row empty row empty row empty row empty row Cancel View Hide total of all columns Page 222 Copyright Datapartner Oy 11 17 2014 Invest for Excel 13 8 Handling of multiple loans SBAA lt Select where you want to move gt x Add financing _f Select type of Loan Add financing Select ECA only for Export Credit Agency types of loans eee select Commercial for all other loans Press Add financing button to include several loans to the financing package Three new sheets are generated caes e g O2Param O2Spec and 02View The same rules apply for these as for the first loan already described Please note that you also have sheets TotalSpec and TotalView These sheets show all specified loans summed up together as consolidated reports Please save your Financing file 13 9 Updating your Calculation file with financing Now you may return to your Calculation file for importing of financing items In the Income statement and the Cash flow statement you will find buttons with exclamation marks for importing financing ee Fr
121. ation and interest varies e Bullet only interest is paid One amortization of the whole loan at the end of the loan term e Customized totally flexible amortization You enter the amortizations by pressing the Enter principal payments button Financing type o Customized r T Balloon payment AS Amortization interval Months Enter y Enter principal payments 11 17 2014 Copyright O Datapartner Oy Page 217 Invest for Excel Balloon payment is one extra 1316 018 amortization in order to accelerate 0 0 1389 609 57 repayment Select only when ool 1 389 60957 applicable Can be used with all loan a ae types Select Balloon payment and a ee press Enter balloon gt button You ae Ta ei may then enter the balloon payment in e Eoo the period of your choice 0 00 1 158 007 98 000 100000000 156 007 98 0 00 158 007 98 0 00 158 007 98 0 00 158 007 98 li Balloon payment Enter balloon gt Select amortization interval of loan Amortization interval Months E You may select between 1 2 3 4 6 12 months interval or Enter If you select Enter you may input customized irregular amortizations by pressing the Enter principal payments button Enter Ea Enter principal payments gt Next section is about interest rates First field is for information only Interest based on Euribor 6 months Next field is about how interest will be handled during the drawdown period In
122. ative profitability and Payback shows the timing When comparing investments of different terms you should rather use the monthly NPV annuity than NPV It is important that the compared calculations are based on the same assumptions so that the comparison makes sense For printing purposes use the following buttons to hide and restore the alternatives you want Hide Empty A Show A Hides ron mes t rra the gt a the ira mes rra a By clicking LE button a dialog will open where you can select rows to hide or show Hide Show Rows Hide Show Rows Edit Row Texts Select the rows you want to hide Row text Investment object Nominal value of all investments Required rate of return Calculation term years Calculation term Calculation point Interval length months PY of net cash Flow PY of operative cash Flow PY of residual value Present value of business cash flows Present value of reinvestments Total Present Value PY Interest bearing net debt of acquired company Free cash Flow based Equity value Proposed investments in assets Investment subventions Proposed investments in shares Investment proposal Net Present Value NPY NPY as a monthly annuity Impairment test Book value of assets 4 Value in use B Control value B A Noe Select all Clear selections 11 17 2014 cua Copyright O Datapartner Oy Page 129 Invest for Excel 4 4 1 Edit Profitability compari
123. ative value is entered on row that has been confirmed as a positive investment Select what you want to enter Realization sale Positive investment In the investment table you can specify up to 30 different investment components partial investments with individual depreciation methods and depreciation percentages Bear in mind that the calculation should only include assets for one investment project Separate investment projects should be evaluated in separate calculation files You can enter several investments in the same row provided that their depreciation method is the same This way it is easy to calculate investment projects with more than one start up period Note that a realization applies to an investment row as a whole Part of an investment cannot be realized if the investment is not split in more than one row A name or short description of the investment can be entered in the first column The default description of the first row is the description of the investment project but you are welcome to type over Page 54 Copyright Datapartner Oy 11 17 2014 Invest for Excel The Residual column at the end of the 12 2013 12 2014 Residual calculation term is reserved for entering residual value In the Depreciation method Se dialog you can choose if you want an 200 000 200 000 ending book value to be automatically rea 250000 50000 Ol lized at the end of the calculation term or OO TOO 10 000 not
124. ave entered the debt parameters and the withdrawals the sum of debt taken will show here Utilized total Shows the ratio between the sum of entered withdrawals vs planned total finan cing Calculation figures In order to synchronize the numbers for updating from calculation file you need to select the figures and currency of both investment calculation file and financing file In the example above the investment calculation was done in 1000 SEK but the financing plan will be done in 1 USD When exchanging data both ways the numbers are automatically converted Page 212 Copyright Datapartner Oy 11 17 2014 Invest for Excel Pressing the exclamation Update Investment mark will open this dialog box Open Investment calculation files will be listed Select the one whose cash flow you want to import Notice the amounts units currency and exchange rate Press OK to update Cancel to cancel Update From investment File Invfile kapacitets keningz xls kapacitets kning I Update total investment Project Amount Units Currency Total investment in investment File ao 000 1000 SEK Exchange rate 7 011273 Total investment in Financing File 11 410 196 1 USE 4 Update investment cash flows InvSpec OK Cancel 11 17 2014 Copyright Datapartner Oy Page 213 Invest for Excel 13 3 Currency sheet This sheet is only for currency exchange rates used when updating numbers betw
125. ber Alignment Font Border Pattern Row Column Mark Unmark Arrange screen Height Width unlocked cells Buttons Worksheet reports sheet Menus Home Format Cells Sheets Menus FILE File Input Result Analysis Format Other 3 if mE Pas i Y p 3650 amp O Home Change Calculation Calculator Edit Options Insert Delete User Manual About Inwest Excel screen Language Excel Links Comment Comment pdt for Excel Menus Home Other Help Menus Excel menus HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW ADD INS POWER QUERY POWERPIVOT Invest o Folders And Templates Basic Values Profitability Ex Analyze E 3 Home screen Options Sey C New 5 Open H Save Ey Calculations FE Compare 14 Charts E Language 3 Comment inn ag o esn s r v E Print Close X Exit ipa Financing El Reports 7 E Break Even a FEI Calculation v g Help a File Input Result Analysis Format Other Menus You can switch between Excel and Invest for Excel menus at any time by pressing Excel Invest Menus Menus Menus or Menus 11 17 2014 Copyright O Datapartner Oy Page 21 Invest for Excel 2 File commands 2 1 Folders and Files 2 1 1 Working Folders By defining the folder you decide on the name and path of the folder where you primarily save your calculations You can have both a private folder and a common default folder When you do calculations for your own use you save them in your private folder When you want calculations to be shared with othe
126. ble profitable profitable protitable 27 86 2014 mx SHEN Machine 357 b3 xlem By removing the tick you remove the profitable not profitable comments An investment is considered profitable when the IRR and MIRR are at least as high as the discount rate capital cost NPV is positive gt 0 and PI is at least 1 Project description the descriptive text from the Basic Values table Nominal value of all investments is the sum of all investments defined in the Investments table Discounted Investments the discounted sum of all investments defined in the Investments table Required rate of return the discount rate entered in the Basic Values table Copyright Datapartner Oy Invest for Excel Calculation term Number of years and the starting and ending months of the project Calculation point The point in time to where the Free cash flow is discounted compounded By default the calculation point is equal to the starting point of the calculation term 4 1 1 Profitability indicators 4 1 1 1 Net Present Value NPV The net present value is the sum of discounted free cash flows after tax NPV is an abbreviation of the term Net Present Value a term widely used in other languages too The method for calculating NPV is called the Discounted cash flow DCF method The Dis counted cash flow method is based on discounting the expected net investment cash flows per interval to the beginning of the
127. ccccnnnnnnoonnnnnnnnnnnnnnnonononnnonannnnnnnnnnnos 64 ado FN PUL CCE PPE CIAO Msc dindcnecnerssainde datada lidad lat 64 3 2 4 4 Hiding rows and grouping TA ESCENA AAA AA AA AA SAA e 67 3 2 INCOME MEME dd id Odd 68 325A Teri WACOM sir ch chsh ose A a 69 S27 O AO 71 5250 Specilication and ACUISTA AAA AA A E AA Rud Sects bead uianti 12 320 Create TOW SPECIGI ON rea r E G T3 AL Modi row Spe CaO eai E RE TE A ETE 73 AL Taser TOWS pelore selee ed TOW erer r a a aa 74 E Delete selected TOWS niare RN 74 A HMidenshow row SPeCilIC AU ON sts ide td ee iad ae 13 e O Dl IS o IN II A leeuetesauscaees E RN 75 Lo Create detarl level Speciication 1OWS i cccieersccsese E ean dee EE 76 3 2 5 3 8 Modify hide or delete detail level specification rows cccccccccccnnonononnncnnnnnnnnnnnnnononononannnnnncnnnnnos 76 3 2 5 3 9 Residual column and specification rOWS sssssseccccccccecceeaasessseeeseececceeeeeseeauaaassseeseeeeeeeeeeeeas 71 2 0 de NOWONET 78 ALE Clear nput cells Ot hidden TOWS ta ai a A 79 ILIA lip DUMONS Tors UDOWA a seem inea Grtsar eva ed etes 80 A TCT OW ERAS ON 80 ZO COPA Distribute LUCO aida Ai td 82 SOs ANA CMa Oe ico ai te taunted 84 Seder ERASE INGICALOLS did dd a aa 85 D0 Rormaltimocelset a la adidas 86 IAN A A enue tits sigma A a a a 86 O D Lio 6 LANE Sarit POPE o RRE RR 86 o A LOS saa een i E a IAN 88 3 2 5 10 Return On Net Assets RONA V o A mines 88 a 2 10 Economics Value Added EVA aint A
128. cing and USOrenritivity sbem Use this input screen to enter contact information and other comments useful for yourself or your colleagues Project description Contact person Contact info Date El Comments Calculation file Link to the Basic Values screen Name of the person who made the calculation How to contact her him The date when the calculation was made or last updated lt Click this button to update the date Text field for comments Enter here the basic assumptions in the calcu lation any explanatory notes or comments and cross references or links to other calculations reports etc Name of the calculation file and the path to its folder Useful informa tion if you should forget in which folder the file was saved The path will be updated the next time you save it For explanations on buttons see chapter 1 9 11 17 2014 Copyright Datapartner Oy Page 49 Invest for Excel 3 2 Calculations sheet The result indicating the feasibility of the investment is calculated on the basis of the data input into the following spreadsheet tables Together with the calculated return this data forms the core of the calculation The tables show information both entered by user and calculated by the program 3 2 1 Unfreeze freeze column headers Calculation sheet You can unfreeze and freeze column headers by pressing the following button on the Calculations sheet 1 2014 1
129. copies the calculated WACC rate to the input field for imputed interest rate The WACC formula WACC Weighted Average Cost of Capital Calculate Formula Usage rr rp 1 T D r E Y Y where FP Adjusted cost of capital 4 D Market value of outstanding debt Po Current borrowing rate 6 E Market value of oustanding equity T Marginal corporate tax rate 3 W D E Company market value Pe Expected rate of return on equity 96 Use calculated WAC as discount Factor Cancel Enter percentages as numbers for example enter 7 as the figure 7 The program converts the figures automatically to percentages 3 1 2 2 2 Variable discount rates The Free cash flow of each period column can be discounted compounded to the calculation point using variable discount rates Specify that you want to use variable discount rates by clicking the button beside the Discount rate in Basic values Discount rate per annum 8 00 Select Variable discount rate in the Discount Rate dialog box 11 17 2014 Copyright O Datapartner Oy Page 43 Invest for Excel Discount Rate O Fixed discount rate When you press OK you are taken to the Cash flow statement on the Calculations sheet where you can specify a discount rate for each cash flow Free cash flow FCF 50 000 3797 4638 4667 Discounted free cash flow DFCF Cumulative discounted free cash flow 30 000 46 404 42 326 30 033 Discount rat
130. der at this point di lt Users JENSW Documents gt Calc Examples Business planning v Search Business planning Organize v New folder i Replace the name and Name Date modified Type J Consolidation 7 7 2014 11 00 File folder path suggested by the ihe Desktop e Eliminations 16 9 2013 9 15 File folder prog ram with a suitable E Documents B M 7 7 2014 11 07 File fold ergers f l LON U ile folder a a5 Invfile consolidated areas2 xlsm 17 10 2008 11 22 Microsoft Excel M descri ptive fi le nam e In usic a p 7 10 2008 10 27 E is fig Invfin debt for operations1 xlsm 17 10 2008 10 2 Microsoft Excel M O rder to fi nd yo u r Pe svifi ise calculations easily on E Videos is Local Disk C later occasions a Data D 2 BD RE Drive E Audio CD ea USE DISK DP F However we do G2 data5 Dc X GP data5 DO 2 recommend to keep the s i Invfile word in the File name Invfile monthly budget 2015 plus 4 years annually xlsm Save as type Excel Macro Enabled Workbook xism beg INAL Ng of th e name Authors Stefan Westerbladh Tags Add atag Title Add a title n O rd e r to S e pa rate save Thumbnail different types of Invest for Excel files JE This PC Hide Folders Tools v Select the Template Files tab if you have premade template files in template folder Choose the template you want and press Use the template to create a new c
131. e per annum Note that each columns Free cash flow FCF is discounted compounded to the calculation point using the discount rate specified for the column Enter annual discount rates even if the columns period is shorter than 12 months 3 1 2 2 3 Cost of equity Only in Enterprise edition Cost of equity is added to the Basic values table when FCFE calculation is selected to be included in the file FCFE can be included from the Options dialog box Invest menus Discount rate per annum E 10 00 required rate of return Cost of equity per annum 14 00 Enter the required after tax rate of return on equity Note that Invest for Excel doesn t make a distinction between Preferred stock and common stock Cost of equity should be return on all equity 3 1 2 3 Income tax rate Enter the company s income tax rate for each of the first five years The rate applicable in the fifth year is presumed to remain unchanged for the rest of the investment calculation term This feature enables you to take into account changing tax rates in your calculation If the organisation is exempted from taxation or if tax effects are for some other reason not taken into consideration enter O zero for each year Taxes are often ignored completely in investment calculations In that case the discount rate should also be defined before taxes When the tax rate has been specified the effect of taxation on income in the investment calcu
132. e version 3 7 which don t have camera buttons E a E 1 10 Invest for Excel menus The Excel ribbon menu is modified to include Invest for Excel commands Invest for Excel menus File Input Result Analysis Format Other Es Dekhe Ex Home Folders And Explore folder New Open Save Print Close SharePoint Go Exit Excel screen Templates of active file 7 To Menus Home Folders File Menus File Input Result Analysis Format Other N a a py A BEE H S m Home Basic Contact Investment Income Working Cash Balance Key Roll Eliminations Financing Excel screen Values Info statement Capital Flow financials forecast Menus Home Basic Values Calculations Financing Menus FILE File Input Result Analysis Format Other a hmm Ton E NPV Chart Ez A if E gt A E 6 e IRR Chart a Home Profitability Comparison _ i Investment Impairment Marginal Consolidation Excel screen Analysis Table Payback Chart Proposal Test Verification effect Menus Home Result Menus Page 20 Copyright O Datapartner Oy 11 17 2014 Invest for Excel Input Result Analysis Format Fe a fy im w M ii EE la Home Discount Total Income Variable Fixed Income screen Factor Investment Costs Costs Variable Home Analysis File Input Result Analysis Format Other N 0 O DF A tt MS Z aH Ll ss Other Wy 5 6 Charts Cell Break Undo Excel even Break even Menus Charts Break Even Menus Oo El Insert Add remowve IFRS Excel Home Num
133. e actual row they are moved to the first new additional row Enter descriptions for the new rows 3 2 5 3 2 Modify row specification Click the button to open the dialog box The Modify option is chosen by default 11 17 2014 Copyright Datapartner Oy Page 73 Invest for Excel Row Specification Specification hor row Income production Row 442 Sub rows Insert delete Capacity Tons Row 443 Load Factor degree of utilization Row 444 Sales price per ton E Row 445 Sub total rows For Income Empty row 446 production Row 4427 Tons produced Row 447 Modify O Hide Delete Number of rows 5 e You can change the number of rows with the Modify function Increasing number of rows will add rows to the end of the row specification Decreasing number of rows will delete rows from the end of the specification 3 2 5 3 3 Insert rows before selected row To insert rows before a specific row as opposed to adding to end using the Modify function follow these instructions Click the a button to open the dialog box O Activate the Insert delete page 9 Choose option Insert rows before selected row chosen by default Select the row before which you want to enter rows Enter number of rows to insert default is one row and click OK Row Specification Insert rows before row Income production Row 442 Sub rows Insertjdelete O Capacity Tons Row 443 Load Fact
134. e are summed 1 2 3 6 1 year depreciation is 3 6 book value 2 year 2 6 and 3 year 1 6 book value Enter manually Enter each depreciation manually in the investment table Please note the following Enter depreciations as negative values GAR 5 2003 6 2003 6 2003 As the depreciation percentage is not Depr SE A applicable here it can be deleted if it cee is not used in any entered formulas Book value O 20w 190 000 Depreciations affect the result of the calculation through tax effects If taxes are excluded depre ciations will have no effect on the Investment profitability ratios NPV IRR Payback etc INVESTMENTS REALIZATIONS E Page 56 Copyright O Datapartner Oy 11 17 2014 Invest for Excel 3 2 4 2 Depreciation options Press More options top right to show more depreciation options eT cms re options More functions will be available in the Depreciation method dialog box y Pret ea Less options Shift factor 1 factor 1 Straight line Old Investment New Investments Declining balance One time depreciation oe Decining gt straight ine Begin depredation Sum of years digits 12 2015 months 12 Enter manually First depredation year includes No of months Balance Sheet Items Investment category C Allocate expenditure subsequent investments are Investments subventions summed and depreciated as one In
135. e multiplied twelve times when changing from monthly to annual columns e g unit price margin number of employees do not check the Adjust with number of months per interval by default it is not checked Copy Distribute By entering the change as a per ae l centage you can determine how Copy Distribute options h f the foll i int 1212016 42 2017 1212013 lt n aia i crane ae DET e si wi X Ww 12 2017 prices or sales volume will increase J Copy Formula in active cell ae or decrease In the example to the left the calculation is divided into ATT Adjust with annual 12 month intervals The number of calculation can just as well be di te per vided into e g monthly or quarterly Annual change 2 NS intervals The change percentage Tut annual change in cell D444 is given on an annual basis The Distribute as program shows the value of the O Values 8 Formulas active cell and those of the next two cells Other options Copy cell formatting to target cells Change indicators _ Change yearly 96 Distribute _ Index base year 100 Cancel Page 82 Copyright O Datapartner Oy 11 17 2014 Invest for Excel By default Distribute as Formulas is selected This means that Invest for Excel will create formulas Copy Distribute options 122016 1212017 1212018 O Copy Formula in active cell 100000 Adjust with number of months per 2 E interwal L Put annual change in cell
136. e row with the Flip button by clicking it Flip Change indicators Other variable costs Change yearly Index base year 100 Other variable costs Flip Specification rows Other variable costs Change yearly Index base year 100 3 2 5 4 Edit row texts In this form you can change row texts in each language used in the program This function is only available in Pro and Enterprise editions Page 80 Copyright Datapartner Oy 11 17 2014 Invest for Excel Hide Show Rows Edit Row Texts he aF l a I z A 487 EBITDA Operating income before depreciation EBITDA Ergebnis vor Zinsen Steuern Abschreibur teuern Abschreibur 493 EBIT Operating income EBIT Ergebnis vor Zinsen und Steuern 494 cumulative financial year iv Re speri 495 cumulative financial year 500 Extraordinary income and charges Ausserordentliche Ertr ge und Aufwendungen 501 Realization profit toss Verkaufsgewinn verlust 502 Other extraordinary income charges Sonstige ausserordentiche Ertr ge und Aufwendur Ergebnis 2 vor R ckstellungen und Steuer Clear input cells of hidden rows Cancel It s also possible to filter out languages not needed The blue ones stay the white ones will be hidden 11 17 2014 Copyright Datapartner Oy Page 81 Invest for Excel 3 2 5 5 Copy Distribute function You can copy the formula or the value
137. ePoint Go screen Templates of active file Si Si z To Home Folders File goca D crousa ELIMINATION BE 41 2014 12 2014 12 2015 Months perintefval 14 4 H Basic Valug Calculations Eliminations Result Mu ICI A El Empty row Depreciation straight line Empty row Depreciation straight lime Empty row Depreciation straight line Empty row Depredation straight line Empty row Depreciation straight line Empty row Empty row Empty row Empty row Empty row Empty row Empty row Empty row Empty row Empty row Empty row Raw materials and consumables Staff costs Other variable costs Empty row Empty row Empty row Empty row IR Investments Realizations JS Income Statement WC Working Capital CF Cash Flow BS Balance Sheet in Oh ba ad Oo oo oo od od od EEEE i i a on Gt os on too ot os aN Press the gt button to include one or more rows and press OK when you are done Selected rows will be added to the sheet for entering eliminations You can include the same row as many times as you like 11 17 2014 Copyright O Datapartner Oy Page 139 Invest for Excel ELIMINATION 1 2014 12 2014 12 2015 12 2016 Vonths per interval o EOS TT 1 450 00 1 479 00 1 508 58 Consolidated Sales 1 450 00 1 479 00 1 1 508 58 58 You can change the elimination row description if you like and then enter the eliminations ELIMINATION 1 2014 12
138. eate a link to an Excel workbook saved earlier 1 Select first File Open and open the file you want to refer to 2 Return to your Invest for Excel calculation by choosing the file name in the Window menu 3 In Invest for Excel activate the cell to which you want to place the reference and enter the equals sign in the cell 4 Return to the Excel calculation use the mouse to activate the cell where the source file is and press the Enter key Now you have created a link between the workbooks It is advisable to save both workbooks Later on when you reopen the Calculation file Invest for Excel will ask whether you want to update the existing link to the Excel calculation If you choose Yes Invest for Excel will update the Calculation file with all changes made in the Excel workbook You can of course also make links to extra sheets within the Invest for Excel Workbook Insert Worksheet 12 2 Using templates If you use regularly the same rate of return requirement or other such information it makes sense to create a file called template You can save the template in any folder you choose see 2 1 3 Template Files The Company can limit and direct the use of calculations related to certain investments by setting default values When starting a new investment calculation open this template file and only enter the missing values into it Separate template files can be made for various types of recurring investments Whe
139. ed year as basis or B by ente ring the basis value for Perpetuity Furthermore you can calculate A a standard Perpetuity or B a growing Perpetuity For growing perpetuity a growth percentage should be entered Note that the growth percentage can also be negative if negative growth is to be expected Note that the impact of Perpetuity on the result of the calculation can be substantial Perpetuity should be used when calculating projects that are expected to go on for the foreseeable future An example of such a calculation is a corporate acquisition calculation 11 17 2014 Copyright Datapartner Oy Page 117 Invest for Excel Perpetuity is not applicable on projects with clear economic lifetimes The formulas for calculation of perpetuity A Standard Perpetuity P C r where P perpetuity C cash flow selected for perpetuity net cash flow for selected year or entered annual value r discount rate PV of perpetuity P 1 r where P perpetuity r discount rate per period t period B Growing Perpetuity P C r g where P perpetuity C cash flow selected for the perpetuity net cash flow for selected year or entered annual value r discount rate g growth rate PV of perpetuity P 1 r where P perpetuity r discount rate per period t period Page 118 Copyright Datapartner Oy 11 17 2014 Invest for Excel 4 1 2 1 Extrapolation period Perpetuity can
140. editions 2 Corporate acquisition valuation 3 Impairment Test save in Private Folder Common Folder Cancel For capital budgeting financial modelling profitability analysis business planning Lifecycle costing and product calculations choose 1 Asset investment project or business planning Choose 2 Corporate acquisition valuation for valuation of a company business for mergers and acquisitions and if you want to see how long term planning affects the equity value of the corporation This alternative provides extra lines showing group effects of acquisition This function requires the Enterprise edition Please refer to Corporate acquisition calculations in Section 10 for more information Page 26 Copyright Datapartner Oy 11 17 2014 Invest for Excel Choose 3 Impairment Test if you want to do an impairment test recoverability test on existing goodwill and assets The created file is an asset investment calculation file with the following settings Calculation term is five years by default Calculation point is locked to the start of the calculation term This is because compounding is not recognized in accounting standards The result sheet shows an impairment test calculation in addition to the standard profitability key figures The result sheet holds a button for creating updating an impairment test verification It is advisable to name the file and choose the fol
141. ee cash flow FCF EBIT Operating income l cumulative financial year Discounted free cash flow DFCP cumulative financial year Cumulative discounted free cash flow Financing income and expenses Walue in UZE Financing income and expenses Fhencial cach dan a Financing income and expenses ProFinance Financial income and expenses EBT Income after financing tems l A ees Long term debt increase decrease Changes in interest bearing long term debt Long term debt increase decrease Changes in long term debt ProFinance i l Changes in interest free long term debt Pressing the button Lt gives following dialog box E Equity increase dividends Left above Income statement To the right Cash flow statement Changes in short term borrowings Update Financing Total cash flow Update From financing file Open Financing files are listed Select the one you want to import from If none is listed please open one The currency and unit of the 2 files are listed here Invest for Excel converts automatically when importing Currency translation qa a The Clear function can be used for removing of Investment file 1 yi Eur gt imported values Exchange rate 1 206300 Financing file 1 xfw z Press OK Now financing costs are updated to Income statement income statement and to Cash flow statement Also loan withdrawals and ox cae repayments are updated in cash flow ana
142. een investment calculation file and financing file You only need to enter a currency exchange rate when you use it There is no need to update other exchange rates Currencies Currency prefixes Reference LISO 1 206300 USD EUR 1 0007 JPY 135 00000 JPYEUR 1 000 OOD lu AUD 1 596500 AUDVEUR 1 000 000 000 6 MZD 1 274500 NZDOEUR CYF 0573000 CYPEUR ZAR a 052000 Z4R EUR PLN 4 120200 PLM EUR CZK 30 205000 CAK EUR ROM 3559200 ROM EUR BGN 1 955700 BGM EIJR 13 4 InvSpec sheet The cash flows from your investment calculation file are brought to this sheet Although your investment calculation might have been e g on annual basis the financing file is always on monthly basis Figures USD Investment All transactions Cash flow from operations Investments and realizations Investment net cash flow at end of month Per period Cumulative Per period Cumulative Per period Cumulative Manth 7 278 812 85 7 278 812 85 2 695 120 00 2 895 120 00 4 303 692 85 4 303 692 85 12006 B03 150 00 503 150 00 503 150 00 603 150 00 1 22006 503 150 00 1 206 300 00 603 150 00 1 206 300 00 2 32006 1 588 820 00 2 095 120 00 1 659 620 00 2 895 120 00 3 4 2006 2 095 120 00 2 895 120 00 4 5 2006 2 095 120 00 2 695 120 00 5 6 2006 2 095 120 00 2 895 120 00 E 2006 2 095 120 00 2 895 120 00 f 98 2006 2 095 120 00 2 895 120 00 o 4 2006 2 095 120 00 2 895 120 00 g 10 2006 2 095 120 00 2 895 120 00 10 11 2006 2 8695 120 00 2 895 120 00 11
143. efore the supplier is not responsible for the correctness of cal culations and cannot be held liable for any economic consequences of decisions made on the basis of the calculations The supplier s liability for damages will under no circumstances exceed the price paid for the program DataPartner Oy Raatihuoneenkatu 8 A FIN 06100 Porvoo Tel 358 19 54 10 100 Fax 358 19 54 10 111 E mail datapartner datapartner fi support datapartner fi Internet http www investforexcel com 11 17 2014 Copyright Datapartner Oy Page 3 Invest for Excel Page 4 Copyright O Datapartner Oy 11 17 2014 Invest for Excel Table of contents INTRODUCCION AS A 10 1 1 SYSTENEREQUIREMEN ES ill 10 12 IENSTAREA TON dai al ora e te eat E Deedee 10 1 3 SON ING PROBLEMS sra tas 12 1 4 SN A O PPP ates acre ete teers se Sesto cl gree E terrae neces cers Sadat nm slo alecc ay ea ot teams sali Metaek 12 1 5 COMPONENTS aaa 14 1 6 PONE RING A TA dais take 14 1 7 WORKING ORDER andate 15 1 8 HOME SCREEN START UP SCREEN ada 16 1 9 GENERAR BUTTONS sx ana 18 1 9 1 PLACE CO DY AAA aras 19 LIO INVEST EOR EXCREMENUS rana oca 20 Z2 TIE COMMANDOS 00 oul aces ealeus a ciean aces Oeste ecco ES 22 2 1 FOLDERS AND PILES ear T A AE AO 22 Dsi WARNET Olde IS tt A A EA E 22 21 2 TEMPE A ONE tE 23 ZAD Femplale TOS se eet ite Settles Un o eines aha eects Santi NO EN 24 2 1 4 EEE Gt EAE A RES Tee TREN E SOLENT Ht tts E RATE OTe Ue E ee CPRERE PRO TE rrr 23 2
144. en gt Result M Basic Values I Profitability Analysis Perpetuity 4 Comparison Table M Investment Proposal Spi Profitability O Impairment Test Verification 4 Income Statement Working Capital 4 Cash Flow 4 Balance Sheet Key Financials Periods _ IFRS Reports Wa Ps Po Eliminations Shading Unmark unlocked cells gt T Remove background color from headers METEO vms Select what you want to print and from which files and then click the Print button You can also select the number of copies to print Preview is a good way to ensure that you get what you intend to print In Preview you can use all Excel s printing options to make the hard copy look the way you want it to look Please note that the charts you have created are listed to the right of the dialogue box and they can be selected to the print queue 11 17 2014 Copyright Datapartner Oy Page 31 Invest for Excel Periods You may select the columns to be printed e g only the 1 five years When you press the Periods button the following dialog box appears Print The periods marked blue will be printed 1 2015 12 2015 12 2016 12 2017 Each screen of the program also has a Print button for printing the active screen 2 10 Page Setup An Excel function for changing the page setup for printing Refer to Excel s Help function for further information Margins Header Footer Orientation
145. er an opening balance it can affect the change in working capital and that way the result of the in vestment calculation 9 2 2 Receivables The balance of accounts receivable and other receivables entered in the Working Capital table are shown here Note that if you enter an opening balance it can affect the change in working capital and that way the result of the investment calculation 9 2 3 Bank and Cash This row shows the cash situation at any time It should be equal to total cumulative cash flow in the cash flow table minimum cash reserve if entered in the working capital table Page 194 Copyright Datapartner Oy 11 17 2014 An example balance sheet BALANCE SHEET Month per interval ASSETS Fixed assets and other non current assets Intangible assets Immaterial rights Capitalized development costs Goodwill Other intangible assets Other intangible assets specified Other intangible assets Depreciation Straight line Invest for Excel 12 2005 1 2006 12 2006 12 2007 12 2008 12 2009 AO poo Ao n n al 1 250 1 250 1 000 FSD 500 250 0 0 0 0 0 0 0 0 0 O 0 0 0 0 0 O 0 0 1 250 1 250 1 000 Tol 500 250 0 0 0 O 0 0 1 250 1 250 1 250 1 000 fo s00 50 20 0 0 250 250 250 250 Tangible assets Machinery and equipment Machinery and equipment specified Machinery and equipment 346 000 363 000 355 740 313 460 241 220 220 960 26 O00 41 000 31 500 22 O00 12 500 3 O00 Depreciati
146. ercentage and other tax related options The dialog box is opened by pressing the button in Basic values and selecting Partial locking File Locking Full lock If a password has been entered for the locking settings of the file you need to enter the password before the File Locking Options dialog box is shown The macro definitions sheet can be hidden by pressing the button in the upper right corner of the macro definitions sheet Page 226 Copyright O Datapartner Oy 11 17 2014 Invest for Excel Custom macros can be assigned to buttons etc on your own sheet without direct linking by the use of the macro definitions sheet Macro workbook name Term change macro Description optional Personal xls TermChangeMacro Updates the columns in my sheets when calculation term is changed Assignable macros Macro to run in Personal xls Description faptional Outlines my sheets Buttons 1 4 showshides residual column my sheet Residual button Prepares and prints report 1 button Report 1 Prepares and prints report 2 button Report 2 AssignableMacral OutlineMySheet AssiqnableMacralz ShowHideResidual AssignableMacrols PrintReportl AssiqnableMacrol4 PrintReport AssignableMacroQs AssiqnableMacroUb AssigqnablebacroQ Assignablehacrale AssiqnableMacroaly Enter the name of file holding your macros under Macro workbook name Macro workbook name Personal xls Note that the file is assumed open when a macro is
147. ere is a bar with buttons and a drill down menu The functionality explained from left to right print function for printout of current sheet moves to the leftmost sheet moves one sheet left moves one sheet right moves to the rightmost sheet moves to start screen the drill down menu lets you select which function you move to the Add financing button adds more sheets for more loans e g 02Param 02Spec amp 02View 11 17 2014 Copyright Datapartner Oy Page 211 Invest for Excel 13 2 Project sheet Project information Description Debt taken for new production line Total investment ld 2 050 000 USD Total financing 1 640 000 USO Financing investment OU o Utilized financing 313 795 USO Utilized total 19 Prepared by Jens Westerbladh Comments Primary financing from bank xZ side loan from Prosperious Inc Calculation figures Investment calculation Financing calculation Figures ioon w Figures E y Currency SEK Currency luso project currency Description Describe your project financing plan the purpose of this financing or who is the financier Total investment The sum of investments capital expenditures from your calculation file Is updated by pressing the exclamation mark on the left Total financing Enter how much debt you are planning to take for this project Financing investment The percentage ratio of Total financing vs Total investment Utilized financing Once you h
148. eriod Present value of business cash flows Nominal PY Notes PV of operative cash flow 9527 PY of residual value 14 319 Present value of business cash flows 23 846 Present value of reinvestments 1 201 1 028 Total Present Value PV 22817 Interest bearing net debt of acquired company 2 356 Free cash flow based equity value EW 20 461 EV EBITDA 24 33 Based on EBITDA 12 2014 Y Investment proposal Nominal PY Proposed investments in assets 0 0 Investment subventions 0 g Proposed investments in shares 17 000 17 000 Investment proposal 17 000 17 000 Net Present Value NPV 3461 gt 0 gt profitable NPV as a monthly annuity 33 Internal Rate of Return IRR 480 gt 2 75 gt profitable Modified Internal Rate of Return MIRR 445 2 75 gt profitable Profitability Index PI 120 s1 gt profitable Payback time years Based on discounted FCF Page 126 Copyright Datapartner Oy 11 17 2014 Invest for Excel 4 2 1 Specification of interest bearing net debt Specification of Interest bearing net debt of acquired company has been added to profitability analysis in an corporate acquisition valuation file Enterprise edition only Iinterestbearing net debt of acquired company 257 000 Interest bearing long term debi 320000 Interestbearing shoretermliabiliies 45000 Bank and cash 120000 e o Dividend debt Net Debt Options Rows Interest bearing long term debt Interest bearing short term liabilit
149. ernative cost of capital has been taken into account discount factor Cumulative discounted free cash flow the realised total Discounted free cash flow Hint The investment has paid itself back when the Cumulative discounted free cash flow turns positive If you want to analyse the cash flows to be expected during each period or cumulatively not forgetting cash flows from financing enter the increase and decrease of debts increase of equity and possible dividend payments Input the cost of debt in the Income statement it will show here in Cash flow statement in the row Financing income and expenses These figures have no bearing on the overall profitability of the investment but by taking them into account you will see here the effect on total cash flow If you have the Enterprise edition of Invest for Excel you can k Bring the cash flow from the investment calculation to Financing module N Plan project financing in Financing module and hh O Update your calculation file Cash flow calculation with changes in debt Update your Calculation file Income statement with the cost of debt 5 See profitability indicators based on Free Cash Flow to Equity FCFE By taking the debt leverage into account the investor s true return is shown Refer to chapter 13 for more information on using the financing module Page 96 Copyright O Datapartner Oy 11 17 2014 Invest for Excel 3 2
150. es In the Analysis sheet each analysis can use Key financials as period specific ratios For each of the 6 period specific rows you can select EBITDA Operating income before depreciation EBITDA EBIT Operating income EBIT Return on net assets RONA Economic Value Added EVA or any of your own added Key financials 12 2016 aa EBITDA Operating income before depreciation 1000 Key financials EBITDA Operating income before depreciation 1000 EBITDA EBIT Operating income 1000 Euro EBIT o Return on net assets RONA Economic Value Added EVA 1000 Euro Return On Investment ROT Return On Equity ROE Solidity Net gearing P E ratio The selected Key financials are updated when the analysis is updated Income s impact on profitability Ghongeinincome Y Met Present Value NPV E Change 20 0 Key financials E 10 00 EBIT a 2 63 2 79 2 73 2 83 Economic Value Added EVA 1000 Return On Capital Employed ROCE Return On Equity ROE Net gearing e EV EBIT ratio ea Page 100 Copyright Datapartner Oy 11 17 2014 Invest for Excel When you create custom analysis charts you can analyse the following indicators NPV IRR IRR before tax MIRR Profitability Index PI DCVA Payback Simple Payback EBITDA Operating income before depreciation EBITDA EBIT Operating income EBIT Operating income
151. es Assets 52 500 52 500 52 500 52 500 52 500 NOTE The depreciation of the old investments will affect taxes in the same way that depreci ation of new investments will The result of the calculation will be altered This option can be used in combination with the Marginal effect function 3 2 4 2 9 Balance sheet items Define whether the investment concerns Tangible assets fixed property Intangible assets e g software or Investments securities Balance Sheet Items Investment category Capitalized development costs Goodwill Other intangible assets Machinery and equipment The only effect of the Type of asset selection is the correct grouping of assets in Balance sheet It does not affect result in any way 3 2 4 2 10 Corporate acquisition available only in the corporate acquisition valuation template in Enterprise edition Balance Sheet Items Investment category Select this option only when you are entering the purchase price of an acquisition This item will not be included in the balance The cost of the acquisition will be included in the E a Prepayments and construction in progress Type of asset cash flow of the investment in order to Other tangible assets ensure that the profitability analysis of the Investments in associated como acquisition is correct nies Corporate acquisition 11 17 2014 Copyright Datapartner Oy Page 61 Invest for Excel 3 2 4
152. es in analyzed rows values Period C Indude sum of changes line E gt 1 Result sensitivity analysis Income Variable costs m 10 m 10 Fixed costs 16 145 24 330 32515 40700 48 885 57071 65 256 Net Present Value NPV Page 176 Copyright O Datapartner Oy 11 17 2014 Invest for Excel 5 9 Cell Break Even By running the Cell Break Even function you can quickly calculate the break even point of the investment for example how much certain incomes can drop or costs rise while the NPV falls to zero level meaning that if implemented with the given target interest rate the investment would theoretically only just be feasible To start the Cell Break Even 1 First select the cell containing a value you want to analyse 2 Then select from Invest for Excel s menu Analysis Cell Break Even or click the Lal button 3 To restore the status afterwards select from the same menu Cancel Break Even Let us assume that you have defined the following income items NPV is positive Months per interval o Y Y Y R You now want to analyse how low the selling price could be before the NPV of the investment becomes negative Choose the first cell containing the selling price and run Cell Break Even You will be asked the question INCOME STATEMENT E j L EMEB 93 2015 o o 1 soo Choose OK When the Break Even point has been found Invest for Excel informs you as follows
153. f the required rate of return is 10 When specifying the discount rate you are looking for an answer to the question what returns would some other investment in the same risk category yield The higher the risk is the higher the yield expectation The level of the imputed interest rate depends also on the company s line of business the returns expected by the owners the liquidity of the company etc The criteria for the required returns on investment are therefore usually specified individually for each company and each case Companies often have a specific overall rate of return requirement on their capital investments The minimum rate of return is equal to financing costs The financing costs consist of costs for both debt and equity see WACC definition in next chapter In practice the required rate of return is often determined step by step depending on the kind of investments undertaken The step by step approach to required returns on different types of investments makes it possible to delegate investment decisions on the one hand and to manage investment activity according to the chosen investment policy on the other hand CLASSIFICATION OF INVESTMENTS Investments can be classified and the required returns can be staggered for example Class Investment motive Required rate of return Imperative reasons None To safeguard market shares For example To substitute present investments For example To cut costs For example To get
154. for Excel 7 4 Options Define how financial ratios Net assets RONA and EVA are calculated in a new calculation file or if an calculation file is active in the active calculation file Financial ratios are described further in chapter 3 259 Financial ratios Options Net assets is based on Cuerno bal Balance at the end of period Net operating profit after tax NOPAT Net income for the period financial items appropriations rn 7 4 1 Other Options Financial Ratios Other Options I Indude Debt residual correction Indude eliminations sheet Update analysis charts automatically 4 Picture copy show Picture added to dipboard message 1st option see chapter 4 1 3 Profitability calculation based on Free cash flow to equity FCFE 2nd option see chapter 4 6 4 2 Elimination of internal transactions 3 option you can choose to have sensitivity analyses updated automatically every time you activate a sheet including a sensitivity analysis 11 17 2014 Copyright Datapartner Oy Page 187 Invest for Excel 7 5 Insert comment You can insert a comment to any input cell It makes sense to use this function often to jog your memory or to assist other users of the program The text of the comment is usually hidden Cells with a comment inserted have a small red dot in the top right corner To display a comment move the cursor to the cell 7 6 Delete comment Deletes comment in active cell Select
155. ge 201 Invest for Excel bottom of the calculation show the effects on parent company A Enter the figures also in the working capital table The opening balance is entered into the balance sheet The data preceding the year of the corporate acquisition is entered into the history columns selec ted under basic values This information is used for estimating the business trend or continuity Chapter 0 gives further information on the Income statement When calculating the profitability of a corporate acquisition compared to an ordinary investment the difference will show at the bottom of the table as effect on the result and as indicators on group level Group Depreciation Financial and extraordinary tems Total tax effect Group result effect cum fin year 6 250 11 563 42 100 T o 45 350 11 563 Group Discounted Value Added DCVA Group Cumulative Discounted Value Added Group Operating profit Group Met operating profit after tax Group Met assets average Group Capital charge on net assets T0 Os 153 999 B 250 4 450 a13 97r ol 398 Group Return on net assets ROHA To 0 8 1 4 T Group Economic Value Added EVA 5 835 91 920 10 3 Cash flow The blue rows in the calculation will show how a corporate acquisition possible sales profit loss and taxes affect the cash flow Cash flow from operations Y a am Bm 32 61 Asset investments and realizations 31 250 3 34 453 Group tems
156. h your calculations you can process the results further to make an 17 Investment Proposal Submit that proposal to the decision makers 18 Option If you have the Financing module use it to produce financing calculations Enterprise edition only 19 Option If you have created a calculation for asset impairment testing use the impair ment test verification Enterprise edition only 11 17 2014 Copyright Datapartner Oy Page 15 1 8 Home Invest for Excel Screen Start up screen The Home Screen shows the contents and structure of the program From here you can go to any program module For example click the button to the left of the text Basic Values to go to the Basic Values screen where you begin any calculations ENTERPRISE DataPartner nN V e st FOR EXCEL Program version 3 7 C Basic values C Investment _ Profitability analysis C Discount factor _ Contact information _ Income statement _ Comparison table _ Total investment C Working capital C Marginal effect LJ Income L Folders and files C Cash flow C Consolidation C Variable costs C Balance C impairment test L Fixed costs Program guide _ Key financials verification _ Income variable User manual pdf __ Financing _ Investment proposal L_ Charts gt Investment file lt Not open gt gt BY Comparison file lt Not open gt gt Proposal file lt Not open gt a Financing file lt Not ope
157. he button is excecuted clicked in this example the OutlineMySheet macro located in Personal xls The advantage of assigning custom macros using the macro definitions sheet is that no linking between files is required 11 17 2014 Copyright Datapartner Oy Page 229 Invest for Excel 15 Features of Invest for Excel Features of Invest for Excel Invest for Excel Lite Flexible definition of calculation term Asset depreciation and realization calculation Income statement for specifying income and costs Working capital changes calculation Cash flow statement Investment performance ratios NPV IRR payback etc A great variety of sensitivity analysis Break even can easily be found on input variables Invest for Excel Standard Comparison of investment alternatives Marginal effect Function for creating custom graphics and sensitivity analysis Flexible adding of input and calculation rows Invest for Excel Pro Balance sheet Investment proposal form Currency conversion Languages English German Swedish Finnish Polish Soanish Russian Locking partial full Rolling forecast Renaming of headers rows and key factors Invest for Excel Enterprise Project financing module Consolidation the cooperative action of several investments IFRS and US Gaap impairment test Acquisitions and business simulations Perpetuity extrapolation term Profitability calculation based on Free Cash Flow to Equity FCF
158. he period from continuing operations 256 316 371 Discontinued operations Profit for the period from discontinued operations 0 0 0 Profit for the period 236 316 371 Attributable to Equity holders of the company 258 316 311 Minority interest 0 0 0 A couple of non IFRS standard key ratios can be included Show key figures Company acquisition 1000 CONSOLIDATED INCOME STATEMENT 12 2010 12 2011 12 2012 Continuing operations Sales 36 453 40 406 4 472 Other income 15 16 16 Materials and services 33 069 34 819 36 526 Employee benefit costs 2 623 2 162 2 097 Depreciation amortisation and impairment charges 314 335 356 Other expenses 1 592 1 676 1 759 Operating profit 069 911 950 Operating profit 23 22 22 Share of profit of associates and joint ventures 0 0 0 Net financial items 511 A73 435 Profit before income tax 330 438 316 Profit before income tax 0 9 1 1 1 2 Income tax expense 100 123 144 Profit for the period from continuing operations 256 316 371 Profit for the period from continuing operations 07 0 6 0 9 Discontinued operations Profit for the period from discontinued operations 0 0 0 Profit for the period 256 316 371 Profit for the period 0 7 0 8 0 9 Attributable to Equity holders of the company 250 316 311 Minority interest 0 0 0 Return on net assets RONAJ 9 6 3 5 9 96 Economic Value Added EVA 439 364 323 11 17 2014 Copyright Datapartner Oy Page 205
159. her without any gap in the tables only their intervals might be different In calculating this means that the program discounts the data from the end of each consecutive interval on a monthly basis from the end of each month and on an annual basis from the end of each year 3 1 1 1 Calculation point a a ae ET gt LE Calculation point is the point in time to which free cash flow is discounted compounded when result indicators NPV IRR etc are calculated An alternative calculation point can be defined for payback If an alternative calculation point is not defined the same calculation point is used for calculating payback as for other indicators Normally the net cash flow of the investment calculation is discounted to the beginning of the calculation term This is default for a new calculation in Invest for Excel if no customized calcu lation template is used Page 36 Copyright Datapartner Oy 11 17 2014 Invest for Excel If appropriate the calculation point can be changed to another point in time in which case cash flow before the calculation point will be compounded and cash flow after the calculation point will be discounted to the calculation point An alternative calculation point can be defined for payback calculation Here you can also choose not to include a zero period column 3 1 1 2 Residual value In the Enterprise edition you can select how the residual value of the investment calculation is ca
160. icators are calculated the return can be too favourable if there is an outstanding long term debt that has not been corrected for As an example let s we are looking at a company with an expected outstanding interest bearing long term debt of 45000 at the end of the calculation term No corrections have been done to the free cash flow to equity for this debt BALANCE SHEET EUR 42 2010 42 2044 42 2042 42 2013 12 2014 Residual Long term liabilities 78 700 69 700 60 700 49 200 49 200 interest bearing long term debt 72 000 63 000 54 000 45 000 45 000 45 000 interest free long term debt 200 1 200 1 200 1 200 1 200 1200 If the option Include Debt residual correction is checked in the Options dialog box Invest for Excel will automatically correct for this debt residual The option is checked by default in new calculation files Financial Ratios Other Options W Indude Debt residual correction The debt residual correction is shown in the Profitability analysis on the Result sheet discounted to the beginning of the calculation term or other selected calculation point by the Cost of Equity 45000 discounted 5 years by 15 22373 To Equity Cost of Equity 15 00 Discounted FCFE without residual value 100 782 PY of residual value to equity 12429 Debt residual correction 22373 Net Present Value to equity NPVe 90 838 Note that if you have corrected for the debt residual in the Residual column you should uncheck
161. ied Main products Any entries in the residual column should be entered on the main row above the row specifica tions 11 17 2014 Copyright Datapartner Oy Page 77 Invest for Excel 3 2 5 3 10 Hide show rows INCOME STATEMENT Months per interval By clicking the button on the left of the blue bar you can select rows to hide Hide Show Rows Hide Show Rows Edit Row Texts Select the rows you want to hide A Clear selections Row Row text 234 Income specified Product A Select zero rows Capacity free text Select empty rows free text Utilization rate Ase ol Select row specification E lied Es Select key ratios Empty row cumulative Financial year Other operating income Variable costs Raw materials and consumables External charges Staff costs Other variable costs Gross margin cumulative financial year Select level 1 cumulative Financial year Fixed costs Staff costs Select all Rents Other Fixed costs Empty row t EBITDA Operating income before depreciation cumulative Financial year cumulative Financial year Depreciation In this window you can choose which rows you want to hide Rows marked with blue will be hidden You can freely select rows by clicking them with mouse or use buttons on the right side in the window If specification rows are not in use the Select row specification button is hidden When you open the window the progr
162. ies Bank and cash Enter manually 4 3 Profitability analysis in impairment calculations Control value is calculated in the profitability analysis in an impairment test calculation Impairment test 31 12 2006 Book value of assets A eS 205 000 Value in Use B 612121 Impairment loss B A 92 879 For more information about impairment testing see chapter 11 Impairment testing and IFRS functionality 11 17 2014 Copyright O Datapartner Oy Page 127 Invest for Excel 4 4 Comparison table The following buttons above the Results table lead to the Comparison Table file Tothe comparison table 1 2 3 4 5 B Use these buttons to copy the result of the investment calculation to a comparison table If you are creating a new comparison table file you will be asked to name it and save it Comparison Tables are in separate files and you can use them independently If you cannot remember to which section from 1 to 6 you may already have transferred data you can safely look for a blank one If the column already contains data the program first makes sure that you want to overwrite it You can compare up to six different investment alternatives DO O gt O Os Hide Empty Show All PROFITABILITY COMPARISON New flight rout New flight route New flight route New fligt ut plisss ew ight route ew eht r ew ight route jew flight route nominal nominal nominal Nominal value of all investments 4 700 000 4 950
163. ill handling method goodwill will be depreciated By clicking on the depreciation row button a dialog box opens for entering the depreciation time in years Page 200 Copyright Datapartner Oy 11 17 2014 Invest for Excel Goodwill Goodwill 44 badwill Depreciation goodwill badwill depreciation time years Depreciation percent Depreciation per year 40 073 05 Badwill depreciation allowed le Yes Ho Cancel Note that you can determine whether depreciation of negative goodwill badwill is allowed 10 1 3 Financing By clicking the Gad button on the interest bearing long term debt row the financing structure for the corporate acquisition can be specified Enter the loan amount repayment term and financing costs annual percentage Cash flows are calculated for the loan when you press OK A finan cing file can also be used for specifying the loan Group Lown Purchase price 4000 000 Financing Loan f Use Financing file Loan amount 2 750 000 Loan of purchase price DO da Repayment term years Interest other costs percent per annum Equity Equity amount 1 250 000 Equity of purchase price 31 25 Equity costs Cancel 10 2 Income statement The expected income and costs of the future operations of Company B i e the expected INCOME STATEMENT is entered into the profitability calculation The extra blue rows at the 11 17 2014 Copyright Datapartner Oy Pa
164. ime depreciation No existing book value Shape keg gg Continue old depreciation plan Enter manually Purchase price Purchased when MM YY Starting balance 01 2015 Years left to depredate Carry over book value 5 500 Starting balance MM YYYY 01 2016 Capitalized development costs Beginning end of month Beginning Goodwill Other intangible assets Machinery and equi Residual value iw Automatically calculate realization value at end of calculation term There is no initial cash flow effect so the only effect is the tax effects of the depreciations and a possible realization residual value INVESTMENTS REALIZATIONS Ml imputed depreciation o a SEa i 1 2015 La nar Months per interval Book value Note that since this introduces a new asset to the calculation without any cash flow there will be an in balance in the balance sheet Page 60 Copyright Datapartner Oy 11 17 2014 Invest for Excel BALANCE SHEET 1 2015 12 2015 12 2016 12 2017 Residual ASSETS Fixed assets and other non current assets Total fixed assets and other non current assets 52 500 42 000 31 500 21 ooo ol Total Current Assets A o ano ASSETS 52500 42 000 31 500 21 000 21 000 SHAREHOLDERS EQUITY AND LIABILITIES Total shareholders equity 10 500 21 000 31 500 31 500 Liabilities Total liabilities SHAREHOLDERS EQUITY AND LIABILITIES aT 10 500 21 000 31 500 31 500 Check Equity and liabiliti
165. inal effect dialog Use the Status quo calculation as basis for the second investment calculation and add effects of the new investment Enter this calculation as file B in the Marginal effect dialog You can start the Marginal effect creation from the home screen or the Result menu Marginal effect Files Info Create margin calculation 6 A A Investment file situation before investment Cu Documents and Settings siMy Documents Invest CalciPormatka w Invest For Excel _PSwnrz_hist xls sd a B Investment File including new investment C Documents and Settings siMy DocumentlInvestCalciFormatka w Invest For Excel Poyenre_hist_moderniz xls E Note that the files should have the same assumptions calculation term pc for comparabilibr E Cancel You can select open calculations from the drop down lis or alternatively open an existing invest ment calculation from any folder with the browse button Start creating by pressing the Create button If you want to include sub rows in the Income Statement of the marginal calculation you need to define specific template with these sub rows 4 5 1 Assumptions used in a Marginal effect The source calculations have been created with the same basic assumptions unit currency income tax include positive tax effects and discount factor Basic values and balance fixed assets grouping are written to the Marginal effect from the first source calcula
166. inancial ye figures only cumulative fingw i Select the periods that you want to include in your chart from the box to the right Note You can change the figures for periods shorter than a year to annual figures with a tick in the box Yearly figures only Check the box Convert negative to positive to get the bars or lines of the chart above the X axis change the costs and investments to positive figures when appropriate Click the Create chart button Invest for Excel creates a separate worksheet containing the chart specified by you in your investment calculation file 11 17 2014 Copyright Datapartner Oy Page 169 Invest for Excel Earnings and FCF 3 000 000 2 500 000 2 000 000 1500000 lu 1 000 000 500 000 12 2016 12 2017 12 2018 12 2019 12 2020 12 2021 12 2022 mu Turnover E EBITDA mua Free cash flow to firm FCFF EBITDA margin Now you can continue formatting your chart using Excel s functions 5 8 2 Create new Sensitivity Analysis chart Spider i n Create new chart 5 Create new analysis chart Tornado Goto chart en Page 170 Copyright Datapartner Oy 12 2023 12 2024 11 17 2014 60 44 Invest for Excel Sales analysis Sales price Analyze rows max 5 Sales volume Other operating income Variable costs Raw materials and consumables Volume Cost per unit External charges Staff costs Other variable costs Fixed costs Staff costs
167. ing liquidity planning corporate finance Impairment testing recoverability test calculating value in use Consolidation of group including eliminations of internal transactions Lifecycle costing Post implementation reviews post audit calculations Cost comparisons Investment problems are usually solved technically with the aid of investment calculations The purpose of the calculations is to give the investment decision makers basic numeric estimable data to support investment decision making Furthermore all other relevant data including that which is difficult to estimate will be taken into account before a decision for or against the investment is made The same approach can be used in calculating real investments and finance investments alike As a result Invest for Excel is equally applicable when planning an investment in equipment or bonds The criteria to base decisions on for example the required return on the investment may differ The investment process can be divided into the following stages Finding an investment object Determining the factors that make each investment alternative advantageous Making investment calculations and comparing alternatives Planning how to finance the investment Deciding on the investment taking into account the optional factors as well Monitoring the investment DIAS 1 1 System requirements The workstation shall have at least 1 GB RAM The program files take about 150 MB
168. investment term using the investment s required rate of return NOTE If the calculation point is not set at the beginning of the calculation term free cash flow before the calculation point is compounded and free cash flow after the calculation point is dis counted to the calculation point using the required rate of return Decision making rule The investment is profitable when NPV 0 N Formula NPV gt FCF l r t 0 FCF t Free cash flow in period t t period r discount rate per period N number of periods For residual values t N In acquisition calculations net debt of the acquired company is deducted from NPV 4 1 1 2 Monthly annuity of net present value As the NPVs of two or more investments with different economic life are not directly comparable a monthly annuity of NPV can be used as the basis for comparison Decision making rule The higher the monthly annuity the better the investment is Formula NPV as monthly annuity NPV r 1 1 r 7 r discount rate per month n number of months 4 1 1 3 Discounted Value Added DCVA DCVA EVA for each financial year is discounted compounded to calculation point DCVA is the sum of discounted EVAs DCVA gives approximately the same result as NPV The following corrections are made to DCVA for compatibility with NPV Page 110 Copyright Datapartner Oy 11 17 2014 Invest for Excel Asset investment impairment test Sum of discounted cor
169. ion paid at selected payment interval but only during drawdown period N Entered when you select this option press the arrow on the right and you get to enter fees manually 13 6 01Spec sheet This sheet is a detailed report of one specified loan Page 220 Copyright Datapartner Oy 11 17 2014 Invest for Excel Project financing Loan from Bank XYZ Interest fixed Capitalized Principal Ending Euribor 6 months Loan arrangement fee Guarantee fee Billing charge interest payment balance Rate Calculated Accrued Paid Calculated Paid Calculated Paid Calculated Paid 0 00 231601595 0 00 3 41800 000 13467467 0 00 0 00 0 00 000 140000000 341800 199383 199383 0 00 13467467 0 00 0 00 0 00 0 00 0 00 0 00 000 231601595 341800 398767 598150 000 76341 33 0 00 0 00 0 00 0 00 0 00 0 00 000 231601595 341800 659679 12578 29 0 00 6 67 000 1737 01 1737 01 0 00 0 00 0 00 000 231601595 341800 659679 1917507 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 000 231601595 341800 659679 2577186 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 000 231601595 341800 659679 3236864 0 00 0 00 000 173701 1 737 01 0 00 0 00 0 00 000 231601595 341800 659679 3896543 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 000 231601595 341800 659679 4556221 0 00 0 00 0 00 0 00 0 00 0 00 0 00 000 11580080 220021515 3 41800 659679 5215900 52 159 00 0 00 000 1650 16 1650116 10 00 10 00 0 00 000 2200215 15 341800 626695 5842594 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 000 2
170. lation equals the tax rate Depreciation affects the result by reducing the amount of tax paid outward cash flow Page 44 Copyright Datapartner Oy 11 17 2014 Invest for Excel 3 1 2 4 Income tax options Press this button to open the income tax options dialog box Income Tax Options General Impairment Test Tax calculated automatically 5 ta P Indude positive tax effects Indude tax effects of financing items in discounted cash flow Note If WACC has been used as discount rate then tax effects of financing items should NOT be included in discounted cash flow Tax effect of financing items is induded in the income statement income tax row even if itis not included in discounted cash flow Goodwill depreciation is tax deductible Enter income tax manually Income tax can be calculated automatically or entered manually 3 1 2 4 1 Tax calculated automatically 3 1 2 4 1 1 Include positive tax effects The result of the investment is calculated on discounted cash flows after tax The effect of tax is usually a decrease in profit and cash By default only this negative tax effect is taken into account when evaluating an investment Checking this box however means that tax benefits are also taken into account when the investment usually in the beginning produces negative cash flows If the company is generally profitable and if its future financial statements are presumed to be critical the inves
171. lculated Residual Value Calculate residual value as Residual column in Calculations sheet C No residual value Cancel gt all Perpetuity can be used for going concern type of calculation where the operation is expected to continue for a foreseeable future In this case you use a calculation term of for example 5 or 10 years and select the last typical financial year as basis for perpetuity Extrapolation is useful in long term projections e g 40 years of operation can be done so that you model 10 years and then extrapolate 30 years with some trend The options for perpetuity extrapolation are specified in the Profitability Analysis table details in section 4 1 2 The Residual column which is the only residual value option in all other editions than Enterprise can be used to freely specify residual cash flow effects of any item in the calculation In the Enterprise edition you can also specify that no residual value is to be used for the calcu lation 11 17 2014 Copyright O Datapartner Oy Page 37 Invest for Excel 3 1 1 3 Historical periods You can also include historical financial statements Tick the years you wish to include and you will get the necessary columns in the following calculation tables If the forecast period starts in the middle of the current year you can include the months that have already passed into the calculations on e g a monthly or qua
172. le from the Home screen or the Result sheet 17 1 1 Corporate acquisitions In the case of a corporate acquisition the purchase price and net assets will generate goodwill in the balance sheet of the acquiring company This goodwill is depreciated in 5 20 years The depreciation affects the result This obligation to depreciate goodwill is often questioned by companies Why depreciate an assets value may even increase According to IAS 22 no depreciation is done on assets without a specific lifetime but these items must be impairment tested This test is done by comparing Value in use to the company s assets goodwill Control value value in use total assets goodwill 11 1 2 Fixed assets According to IAS 36 a company is required to recognize an impairment loss if the carrying amount of assets exceed their recoverable amount The recoverable amount of an asset or a cash generating unit is the higher of its fair value less costs to sell and its value in use Invest for Excel is used when the recoverable amount is value in use Control value value in use total assets When the control value is positive no impairment loss needs to be recognized 11 1 3 Impairment test options Working capital and financial assets can be included in the tested assets requires that the impairment test calculation file is created with version 3 4 or newer 11 17 2014 Copyright Datapartner Oy Page 203 Invest for Excel in tested
173. lidation file is created on annual basis except for the beginning and the end of the term regardless of periods used in the source file Investments are grouped by the Balance sheet fixed asset types in the investment table Incomes and costs are summed to the main row level leaving the yellow specifications and detailed specification rows out Income tax is summed from source files Income tax option E button in Basic values Tax calculated automatically can be activated to calculate income tax in the consolidated file 4 6 2 Investment summary and consolidation info Two additional sheets are created to the consolidated file Investment summary Shows Investments Subventions Net investment investment subvention Depreciation time and Depreciation for the first four years of the consolidated files The following fields are for entering additional information Field Explanation Cost center Cost center or other organization id Account Account number Prio Priority Responsibility Responsibility coding Info Information field Investment year Year of the initial investment Completed Investment completed Depr year Depreciation per year Comments Comments Consolidation info Shows Calculation file path Description Term User Date Total investment Discount factor NPV Unit Currency of the consolidated files The calculation file path is a hyperlink Clicking it will open the file Page 136 Copyright Datapartne
174. lysis Balance sheet is updated with debt Clear 11 17 2014 Copyright O Datapartner Oy Page 223 Invest for Excel EBIT Operating income cumulative financial ear So cumulative financial year Financing income and expenses Financing income and expenses a Financing income and expenses ProFinance EBT Income after financing tems Income statement Income statement is updated 122 500 122 500 16 3 O 75 005 179 931 Financial cash flow Financial income and expenses o 47 495 55 902 Long term debt increase decrease 1 339 647 95 997 191 993 Changes in interest bearing long term debt 95 997 191 993 Long term debt increase i decrease Changes in long term debt ProFinance 95 997 191 993 Changes in interest free long term debt Equity increase dividends 1 IO ae Changes in short term borrowings Cumulative total cash flow Cash flow statement is updated Liabilities Long term liabilities 1 243 650 1 051 657 059 563 interest free long term debt T T T Short term liabilities Accounts payable T 30 ooo 44 PZA Total liabilities SHAREHOLDERS EQUITY AND LIABILITIES 1339 647 1325 69 1255 549 Balance sheet is updated Page 224 Copyright O Datapartner Oy 11 17 2014 Invest for Excel When working with ACQUISITIONS you may also use the Financing module for planning a financing for the acquiring corporation Goodwill calculation Method Price me
175. ments totals Goodwill calculation Hide Show Rows Grouping ct Hide Show Rows You can add group headers for investments of similar kind Headers can be edited moved or removed at any time Group AA S Move Up headers are informative and don t Investment 2 Facilities Y Move Down affect calculation Investment 3 Software Investment 4 Installation Investment 5 Headers can be defined in each aida language by clicking the Edit header button Only Editions Pro amp Enterprise Hide Show Rows Grouping Set group headers text For investment rows Move header Header Texts Remove Header Write header texts for each language English GB Group 3 Finnish FI Ryhm 3 Swedish SE Grupp 3 German DE Polish PL Spanish ES cu co 11 17 2014 Copyright O Datapartner Oy Page 67 3 2 5 Income statement Invest for Excel Use the Income statement table to input all incomes and expenses estimated to arise from the implementation of this investment Consider the Income statement as a Profit loss account of planned project investment business The Income statement is one of the financial statements lt displays the revenues recognized for a specific period and the cost and expenses charged against these revenues including depreciations of various assets and taxes The purpose of the income statement is to show you whether the project investment business made or lost
176. message is shown 11 17 2014 Copyright Datapartner Oy Page 47 Invest for Excel O This function Is locked Or if you try to enter a value in a locked cell i The cell or chart you re trying to change is on a protected sheet To make changes click Unprotect Sheet in the Review tab you might need a password 3 1 2 5 2 Full lock Full lock means you can t edit any data in the file but you can still navigate and print on the calculation after locking the file Use this option when you want to ensure that no changes are made to the file NOTE Full lock can t be opened once it has been applied The program asks you to confirm that you want to lock the file and prompts you to save the file with a new name Lock the investment file NOTE A LOCKED FILE CAN T BE MODIFIED AND THE LOCK CAN T BE OPENED You will be prompted to save the file with a new name when locking is complete Page 48 Copyright Datapartner Oy 11 17 2014 Invest for Excel 3 1 3 Contact Information Project description Contact person Contact info Date Comments Calculation file CONTACT INFORMATION New flight route Calculation term 10 0 years 1 2015 12 2024 jens westerbladh datapartner fi 358 400 306 822 Airline considers opening a new flight route to a small town Nominal calculation CAU er JENS Whe pe Oto localbMicrorofes window tiNet Cachet Content Outlook ELA IG Tn neu Flight route incl Finan
177. method Asset Existing asset ee Cancel Depreciation Less options Depredation time years Shift factor 1 factor 1 Deprecation method Straight line Old Investment New Investments Dedi ing balar ant One time ramet tion f No existing book value Dedining gt straight line iz i E Sum of years digits Continue old depreciation plan Enter manually Purchase price 100 000 Purchased when MM YYYY 09 2010 Starting balance 01 2015 52500 Years left to depredate 525 C Carry over book value Balance Sheet Items Investment category Capitalized development costs Beginning end of month Goodwill Other intangible assets Machinery and equi Residual value r Automatically calculate realization value at end of calculation term 11 17 2014 Copyright Datapartner Oy Page 59 Invest for Excel The old depreciation plan will continue in the calculation term of the file INVESTMENTS y REALIZATIONS ta O dE i imputed depreciation RED r 1 2015 EROS PE Months per interval Depreciation straight line Book value 3 2 4 2 8 Carry over book value The book value of an existing asset can be carried over to a chosen point of time in the invest ment table and depreciated with a new depreciation plan using the Carry over book value option Piano A e Less options o Shift factor 1factor 1 Straight line Old Investment New Investments Dedining balance A One t
178. mount of non interest bearing cash reserves needed for implementing this investment 2 Other receivables Other than accounts receivable and cash buffer e g advances paid prepaid expenses and accrued income These kinds of items are typically not necessary for investment calculations The total of short term assets is Short term assets increase decrease Change in accounts receivable Change in other receivables Minimum cash increase decrease 11 17 2014 Copyright Datapartner Oy Page 93 Invest for Excel 3 2 6 3 Inventories Inventories tie up capital and have an impact on the investment s profitability Enter the rotation time of inventory in days or the inventory balance goods in stock in the Working Capital table Inventories comprise by theory Raw materials and consumables materials and supplies Work in progress and Finished goods If you select Turnover period days the calculation of the value of inventory will as default be based on the row Variable costs in the Income statement For Sy bee En more detailed inventory management click the El button for specification of inventories Inventories Specify Howmany gt y C Don t specify If you give both Turnover period days and Adjusted balance the program will only consider the latter The true residual of the inventory can be entered into the last column if a value different from that calcula
179. n gt mp 2 0 E Progra guide gt 3 Page 16 www investforexcel com G The Exit command closes both Invest for Excel and Microsoft Excel Remember to save your workbooks when you exit the program otherwise your work may be lost Please note that the Exit command will also close all other Excel workbooks open at the time The Guide function contains useful tips about using the program You can activate or close the Guide in the Home Screen When starting a new function a few tips on its use are displayed Even when you are feeling more confident about using the program you can check the comments on any particular part of the pro gram by clicking on the help button when available Create new file Open saved file Go to file Centralised printing of reports see chapter 2 9 The Change Language function By pointing to this globe button you can quickly change the language of various parts of the calculation Copyright O Datapartner Oy 11 17 2014 Invest for Excel nvest Click on the Invest for Excel logo to display information on the user licence FOR EXCEL D DataPartner nN ve st FOR EXCEL ENTERPRISE VERSION 3 7 Beta2 Compilation 3 7 001 Copyright c DataPartner Oy 1995 2014 Raatihuoneenkatu 8 FIN 06100 PORVOO FINLAND www datapartner fi This productis licensed to Organisation DataPartner Software User Jens Westerbladh License Number V04 01 00261 Warning This computer p
180. n be allocated on the Balance sheet fixed assets of the purchased company Immaterial rights Capitalized development costs Goodwill Other intangible assets Machinery and equipment Buildings and structures Land and water Other tangible assets Investments in associated compa Deferred tax assets Long term loans receivable Other investments If a depreciation method is defined for the asset in the Balance sheet Invest for Excel will use the same depreciation method as default E Allocated overvalue before tax Amount allocated on Depreciation Balance Enter value to allocate Overvalue before tax liability 16575 Deferred tax liability E Overvalue a 23025 E Allocated overvalue before tax e amount alocsted on Inmaterialrahts y asp Depreciation sratine y 100 Balance El Allocated deferred tax liability Amount allocated on Inmaterial rights Depreciation Balance Total allocated overvalue Goodwill Note that the entered value should be part of or all of Overvalue before tax liability An equal share of the deferred tax liability is automatically allocated Any remaining overvalue is shown as goodwill Allocated Straightline vw overvalue is depreciated according to selected depreciation method EPT The available methods are Declining bal Enter 11 17 2014 Copyright O Datapartner Oy Page 199 Invest for Excel Both overvalue and corresponding deferred tax liability
181. n either an investment proposal that you have previously prepared by clicking Open or a new blank investment proposal by selecting New in the menu window Fill in the necessary new data edit it and print the form as your proposal Note The text you wrote will remain unchanged even if you use the Change Language function Unlike other parts of the Invest for Excel program the Investment Proposal file can be edited You can undo the protection with the Excel command Review Unprotect Sheet and edit the existing texts formatting the way you want By contrast the figures on the second page that the program updates from the investment calculation can only be changed through reprogramming For more information refer to ltem 4 9 1 Modified Investment Proposal form Page 152 Copyright O Datapartner Oy 11 17 2014 Invest for Excel Development Stefan Westerbladh Department Drawn up by Project X Investment abject Investment number DESCRIPTION OF INVESTMENT Up date 12 21 2004 Date Project title and number Siro a IMPLEMENTATION TERM Lead time from order to commissioning of object PROJECT BEGINS Project kick off rmonthtyear COMPLETION TIME Ready t monthyear START UP TIME Utilization of object begins fmonthyear INVESTMENT CAPITAL 1 500 000 FEASIBILITY AND GROUNDS FOR THE INVESTMENT ENVIRONMENT AL EFFECTS OF THE INVESTMENT Person in charge Presenter DATE PROCESSED OR PREPARED Date Initials Date EMP
182. n similar types of investments are made repeatedly you don t have to do everything again from the start instead changing a few figures will suffice 12 3 Including positive tax effects See the bottom left corner of the text Basic Values screen and the chapter Income Tax Rate above Example Investment X results in a loss of 100 000 USD in the first interval The result of the company s remaining business shows a profit of 1 000 000 USD The corporate tax rate is 29 1 Without investing in X Our profit from other activities 1 000 000 Tax 29 290 000 2 We invest in object X 11 17 2014 Copyright Datapartner Oy Page 209 Invest for Excel Our profit from other activities 1 000 000 Result of investment 100 000 Total profit 900 000 Tax 29 261 000 If we choose Include positive tax effects the cash flow will increase by 29 000 USD during the first interval i e 290 000 261 000 29 000 12 4 Excel s Goal Seek function Example How many products XY have to be produced before earnings reach a certain level 1 First choose Excel menus from Invest for Excel s menu 2 Move the cursor to the cell the value of which you want to set at a specific level 3 Choose from Excel s Data What if analysis menu Goal Seek and enter the goal value To value in the dialog box 4 Specify the variable you want to analyse To do that activate the first cell containing a value of an input row in the calc
183. nd side If management wants to direct the calculation by setting certain default values they can be put in customised templates for use in certain investment projects The investment requests can also be customized according to the customer s wishes In that case the customer specific Proposal File shall be saved as the default template You can change the template in this dialogue box Find it by clicking in the Home Screen or in Invest for Excel s own File menu Working Folders Template Folders Template Files Utility Files ZIP When creating a new file use as template Investment file Invest for Excel s default investment file template 9 Fixed de C Users JENSW Documents CalciTemplateslInvfile BUD malli xltm Acquisition valuation E Impairment test PO E Proposal file Invest for Excel s default proposal file template C Users JENSW Documents Calc Templates Invprop XYZ Group xltm Financing file Invest for Excel s default financing file template C custom fl OoOo Page 24 Copyright Datapartner Oy 11 17 2014 Invest for Excel 2 1 4 Utility Files Folders And Files EY inthe Utility files tab you can define Working Folders Template Folders Template Fies Utity Fies zip default currency file create a new Currency File currency file and open existing New currency fle Opencurencyfte currency file A currency file includes Default currency fie exchange rates for currencies C WsersYEN
184. nnnnnnnnnnonononananoss 46 31242 Enterincome tax manually ii a A E T 46 31 243 Impairment test and income taR liada E 46 A E A EA E A E A E E A A A A 47 IEL Partal LOCAS cean ONS 47 ILa PaO Eana e e A ds 48 Fa COMA I ONIN GON ra E O T 49 32 CALCULATIONS SHEE ares 50 Iad Unfreeze freeze column headers Calculation sheet ooooncccnnnunooannnnnnnnnnononanoconnnnnnnonanonoss 50 11 17 2014 Copyright Datapartner Oy Page 5 Invest for Excel 5 22 buttons in Calculations shell ss A a A tii diet 51 5 2 ROW OUH N ULE AAA AAA A oe elena ord slo aaa bbe delenit 52 3 2 4 INVESTMENTS Kea AONE ti A A E EEE 54 3 2 4 1 De OE Cia tO MIC LOG ctinecag al ld A 55 a Deprecar ONS ea a E EE EE 57 S242 Investment expenditure allocated it A A A Ad 57 DAL J BASIS TOP Ce A RN 58 Sel Besim depreci A O II Lava duis nants a E avtacee tows 58 PARA FEAE 04 E 1 0 trees nets E OO RA 58 DZ WSS CONSEGUIS nl Saleen a dare apt se Cada ese a 59 a A Ds A Core PON IC OTR r DENTE TIMP aNT ENT a etre 59 S242 COME OLA depreciation A ai 59 Seis CAV OVET DOOKGN AIS id A AA 60 PLA MI gt SS TC INS A II A II dee uid det E 61 3 2 4 2 10 Corporate acquisition available only in the corporate acquisition valuation template in Enterpise Si Ri a ES 61 LL Investment Cate AA ei 62 324 LIZ investments Subventions anita 62 ZAZA PROPOSEIs REIMVESUNCIIS tra DA Ad AA 62 e a Residual Aia 63 3 2 4 2 15 Apply depreciation options to multiple investments cccccc
185. nnnnnonanancnnnnns 136 4 6 3 Updaling the consolidated Ue cererii einas ri eiet EAs E ANTE ELENE E AEE 137 4 6 4 CONSOLA AO OPUONS A aE E A Ad E A A EE 137 4 6 4 1 Consolidation tile PERS AA E AE EA IA 138 4 6 4 2 Elimination of internal transactions eissien a ieo O rS AAAA E TASOA RE AS 138 4 6 4 3 Consolidation with currency translation ici AA A AAA 143 4 6 5 CUT NOUS 144 4 6 6 COMSOUIAGTION OF INANCING TILES 2 iea e E EEE A N EEN 145 4 7 IMPAIR MENT TEST VERIFICATION losas 148 4 7 1 TAD LN a EE E ace T E O 150 AS INVESTMENT PROPOSED E a a A 152 4 8 1 Modified investment proposal fOrM oooooonnnnnnnnnnnnnnannnnnnnnnnnonanonnnnnnnnnnnanarnnnnnnnnnnanarcnnnnns 158 SS ANALYSIS A A A IrcinOsS 161 5 1 UPDATE CHARTS AUTOMATICALLY ai ta 161 2 DISCOUNT PACTOR ANALYSIS uta ala 162 Do LOTAGINVESTMENTANARSYS Sta tal 164 DEA ANCONS tt 165 Dos VARIABILE COSTS ANALYSIS tddi 165 O THEMED COSTS ANAL St rc 165 TJ INCOME VARIABLE ANAL SES diu dde 166 II PPP A 167 5 8 1 ETA AS A uae AVOR E E OETA 168 5 8 2 Create new Sensitivity Analysis chart SPider ccccccccccccccccccesecsccccceecaeeeseccceeeeaaasseeeeseeeaas 170 5 8 3 Create new Sensitivity Analysis Chart Tornado ooooncccnnnnnnnnonnncnnnnnnnnnannnnnnnnnnnnnnancnnnnnnos 174 DO CREE BREAKEVEN cenie ie EE E E A O 177 5 9 1 HOO CONDENA ete elves eo 178 6 PORMAT rsnceccccosevsccvesccceccanau E E EEE EE 179 6 1 FORMA TIING CES a E rer ase ae eres 179 O NUMBER a
186. ns table is used for input of capital expenditures and defining depreciation plans Also realizations and residuals are entered calculated here Enter each investment in its row in a time period when it will have an effect on the company s cash flow You can also enter realizations sales of assets in this table Remember to enter the investments as negative values and realization selling price as posi tive values The most common investments Capital Expenditures are fixed assets but they can just as well be activated costs e g development costs or other immaterial assets 1 Enter investments INVESTMENTS REALIZATIONS M imputed depreciation AE 1 2015 12 2015 12 2016 12 2017 12 2018 onthe per interval ee 12 1 y2 12 1 Aircraft 4 450 00U de Depreciation straight line 10 00 475 sa mats a 475 000 475 000 Bok value 200 000 10 00 20 a sa 20 POT o a mo 180000 160000 otoo 120000 2 50 23 750 23 750 267188 0 Depreciation straight lime e O A g E aus 2 Click the depreciation button to specify depreciation method Realizations Depreciation i A FE te 50 ME nnn Realization profit loss O Book value 5264688 4745938 4227188 3465000 2970000 Cell 149 Positive investments are also supported o eu o ae ee oe When you enter a positive value a popup dialog is displayed asking you to confirm type of entry The same will happen when a neg
187. od starts at the end of the Drawdown period Now press the Enter withdrawals button Enter loan withdrawals The 01Spec sheet is activated and you have the following view Page 216 Copyright Datapartner Oy 11 17 2014 Invest for Excel Total amount 2 316 096 Investment All transactions Investment met cash Flo Drawdown WSO 5 of total Per period at end of month 4 383 692 85 D 603 150 00 700 000 00 30 22 603 150 001 700 000 00 30 22 2 4 688 820 00 916 015 95 3 0 00 4 0 00 5 0 00 E 0 00 In the 2 column the color shows different phases light turquoise is Financial closing turquoise represents Drawdown period and Investment Investment net cash fio y Cash flow from operations Investments land realizations Investment net cash flow Investment Investment net cash Flon w Per period v Per ey x In the 4 and 5 columns here labeled Drawdown you enter the debt Withdrawals For each withdrawal you may enter either as value or percentage See example above Next please return to 01Param sheet Select type of loan Financing type A Equal amortizations Amortization interval A Equal amortizations B Annuity C Bullet D Customized e Equal amortizations each amortization is of same size payment and interest varies Each payment will be smaller than previous e Annuity each payment is of same size amortiz
188. of hard disk space Having been compiled with Visual Basic for Applications and being based on Microsoft Excel Invest for Excel cannot function without Excel Invest for Excel 3 7 runs in Excel 2007 Excel 2010 and Excel 2013 OS Microsoft Windows XP Windows Vista Windows 7 and Windows 8 1 2 Installation Installing on a workstation or a file server FROM CD ROM 1 Insert the software CD into the CD ROM drive or USB stick into USB port 2 Wait till the program starts or locate setup exe file 3 Follow the instructions of the installation program Page 10 Copyright Datapartner Oy 11 17 2014 Invest for Excel If the program doesn t start automatically then you have to click on setup exe on your CD USB Note that for Excel 2003 version 3 6024 of Invest for Excel is supplied The installation program will create a program group named DataPartner and add the start up icons for the program and the manuals Note Invest for Excel uses macros Allow the use of our macros when you use the program In Excel 2013 the Macro Settings should be Disable all macros with notification or Disable all macros except digitally signed macros General Formulas Proofing Save Language Advanced Customize Ribbon Quick Access Toolbar Add Ins Trust Center Excel Options Trust Center Trusted Publishers Macro Settings Trusted Locations B Disable all macros without notification rusted
189. of interest IRR before tax is calculated the same way as IRR but with taxes added back to the cash flow When free cash flow is separated for firm and equity IRR to Equity before tax is also available The IRR before tax row is hidden by default and must be unhidden manually Decision making rule The investment is profitable when IRR gt greater than or equal to the required rate of return discount factor 4 1 1 7 Modified IRR Modified IRR differs from the above method in that it reinvests the returns at the discount rate not at the Internal Rate of Return In terms of the calculation it means that interest accrues on the investment s cash flows primarily at the discount rate during its economic lifetime According to the traditional IRR method the total annual profit is assumed to be reinvested at the same interest rate meaning that the profit from this investment would be invested in something else yielding exactly the same rate of return If the internal rate of return differs significantly from the discount rate being much higher or lower use this method of calculating interest as it is more prudent and realistic Called Modified Internal rate of return according to the Baldwin theory after its creator it is abbreviated as Modified IRR in the Invest for Excel program Decision making rule The investment is profitable when Modified IRR gt greater than or equal to the required rate of return discount factor MIRR
190. omic life SENSITMITY ANALYSIS Defauitvalue worse IRR bette IRR BREAK EVEN POINT Certainty margin Certainty margin investment capital Operating Margin PERSON IN CHARGE LEVEL OF APPROVAL DECISION Date SUPPORTERS Date signature Signature _ Accepted Rejected _ Postponed Other 11 17 2014 Copyright Datapartner Oy Page 155 Invest for Excel Category of investment To choose one of the options click the appropriate cell Clicking adds a tick in the cell The following are the optional investment categories Productivity investment Inv For expansion and development mainte nance investment replacement investment strategic investment statu tory investment fixed by law You can also rename a category Place a tick to indicate whether the investment will be carried out as a project as normal work or as a parallel sidelong project Agreement and decision Cost estimate Page 156 Complete all applicable items or create new headings refer to Protection above Coverage of binding offers Enter as a percentage the proportion of investment acquisition costs that are tied to offers Estimated duration of project Enter a figure representing the duration of the project in months Change in personnel If the staff complement will be reduced in the course of the investment term enter the reduction as a negative of employees Correspondingly add employees as posi
191. on Buildings and structures Buildings and structures specified Buildings and structures Straight line o 15 000 6 000 3 000 6 O00 26 O00 6 S00 0 0 6 500 6 500 322 O00 322 000 324 240 291 450 256 r20 225 960 21 000 270 450 Depreciation Land and water Other tangible assets Investments Straight line Total fixed assets and other non current assets Current Assets Inventories and york in progress Accounts receivable Other receivables Bank and cash Total Current Assets ASSETS SHAREHOLDERS EGUITY AND LIABILITIES Shareholders equity Share capital Share issue premium Other restricted equity Retained earnings Profit loss for the period Total shareholders equity Accumulated appropriations Minority interest Liabilities Long term liabilities Interest bearing long term debt 202 O00 interest free long term debt 32 O00 Short term liabilities Total liabilities SHAREHOLDERS EQUITY AHD LIABILITIES 11 17 2014 25 760 0 0 322 000 322 O00 322 000 6 0 t 0 364 250 314 250 19 440 1 953 O 42 fai an 12 500 116 990 45 000 124 500 124 500 0 0 6 510 169 500 169 500 155150 0 0 0 234 000 229 000 189 000 197 000 17 000 157 000 13r 000 117 000 32 OOO 32 000 a2 000 32 D00 32 O00 12 200 17 200 35 400 34 200 a3 000 a3 O00 246 200 246 200 244 400 225 200 202 000 182 000 415 700 415 700 406 390 afd 360 351 064 325 716 Copyright O Datapartner Oy Page 195
192. on EBIT Operating income Financing income and expenses Financing income and expenses Financing income and expenses Financing file EBT Income after financing items Extraordinary income and charges Realization profit loss Other extraordinary income charges Income before appropriations and taxes Change in appropriations Appropriations increase decrease Minorityinterest Net income for the period a C C D Return on net assets RONA 0 0 0 0 Economic Value Added EVA 0 0 1 2015 12 2015 12 2016 12 2017 Residual E 12 2017 oO ojo o 0 0 0 0 0 0 The first calculation column reflects the beginning of the investment the zero point in time and is not used in the Income statement The first input calculation interval is in the next column lts duration might depend on the end of the financial year If detailed periodization has been selected and if the interval has been defined as 12 months long the investment term starts at the e g in the beginning of March and the financial year ends on December 31st the second column covers 10 months As default the columns are on annual basis full year Page 68 Copyright O Datapartner Oy 11 17 2014 Invest for Excel In the income statement there are 10 income rows 10 variable cost rows and 10 fixed cost rows available all with the possibility to add sub rows ncome specified Variable costs Raw materials and consumables
193. onal Spider analysis chart The Tornado chart shows how a selected result factor is affected when selected calculation rows are changed by selected minus and plus percentages Sales price Sales volume Other operating income Variable costs Volume Cost per unit External charges Staff costs Other variable costs Fixed costs Staff costs Rents Other fixed costs Provisions increase decrease Analyse profitability indicator Net Present Value NPY Changes in analyzed rows values Negative io lt Positive 49 I Include line for each analyzed row C Indude sum of changes line Feriod Page 174 Copyright Datapartner Oy 11 17 2014 Invest for Excel A v Ce Result sensitivity analysis Income Variable costs m 10 Fixed costs 30 000 20 000 10 000 0 10000 20 000 30 000 Net Present Value NPV change If the factor value is selected as Center value the chart will show the actual value of the changed result factor 11 17 2014 Copyright Datapartner Oy Page 175 Invest for Excel Sales price Sales volume Income Other operating income Raw materials and consumables Volume Cost per unit External charges Staff costs Other variable costs des e Include line for each analyzed row Rents Other fixed costs Provisions increase f decrease Analyse profitability indicator Net Present Value NFV i Chang
194. or degree of utilization Sales price per ton f Row 445 Empty row 446 Tons produced Row 447 g Insert rows before selected 2 EOW Mumber of rows 1 0 f Delete selected rows 3 2 5 3 4 Delete selected rows To delete selected rows as opposed to deleting from the end the Modify function follow these instructions Click the L button to open the dialog box O Activate the Insert delete page 9 Choose option Delete selected rows O Select the rows you want to delete and click OK Page 74 Copyright O Datapartner Oy 11 17 2014 Invest for Excel Row Specification Select rows to delete Income production Row 442 Sub rows Insertjdelete O Capacity Tons Row 443 Empty row 445 m Insert rows before selected row Number of rows 1 Empty row 447 Tons produced Row 448 Delete selected rows 2 3 2 5 3 5 Hide show row specification When you want to hide a row specification click the button to open the dialog box choose Hide O and click OK Row Specification Specification hor row Income production Row 442 Sub rows Insertidelete Capacity Tons Row 443 Load Factor degree of utilization Row 444 Empty row 445 Sub total rows For Income Sales price per ton E Row 446 production Row 442 Empty row 447 Tons produced Row 445 C Modify Hides O Delete To show unhide a hidden row specification click the 3 button to open the dialog b
195. osts in variable and fixed Just hide the rows between variable and fixed costs and change the heading Hide Show Rows Edit Row Texts Hide Show Rows Edit Row Texts 452 a53 ame Ct ass Otheroperatinaincome Liketoiminnan muut tuotot 457 Rawmaterialsend lt onsemables Aineet tarvikkeet ja tavarat E setae SSS 459 Staffoosts ttsti i s Y Cekitlst 460 Other variable costs Mat muta Select the rows you want to hide Row Row text 447 Programming Empty row Select zero rows Empty row Empty row Select empty rows cumulative financial year Other operating income Variable costs Raw materials and consumables External charges Staff costs Other variable costs Empty row Empty row 00 EMmprT 467 Gross margin 468 cumulative financial year 469 cumulative financial year 470 Fixed costs Other fixed costs Empty row 475 Empty row 476 Empty row A A o A 465 E SSS DM Clear input cells of hidden rows Clear input cells of hidden rows Income cumulative financial year Other operating income OPERATING EXPENSES Raw materials and consumables External charges Staff costs Other variable costs Result Provisions increase decrease EBITDA Operating income before depreciation Depreciation does not affect the cash flow of an investment directly Depreciation has an effect on taxation through E
196. ounted free cash flow to firm 4 950 000 4 471916 Information Financial cash flow Financial income and expenses Correction of income tax for financial items Long term debt increase decrease Changes in short term borrowings Free cash flow to equity FCFE Discounted free cash flow to equity DFCFE Cumulative discounted free cashflow to equity B Equity increase decrease N 1 187 500 pad EE ET 553793 107 996 In the Enterprise edition the Free cash flow to equity can be added for profitability analysis from owner s point of view See chapter 4 1 3 Profitability calculation based on Free cash flow to equity FCFE 11 17 2014 Copyright Datapartner Oy Page 97 Invest for Excel 3 2 8 Balance sheet Pro and Enterprise editions BALANCE SHEET EL EUR Months per interval ASSETS Fixed assets and other non current assets Intangible assets Tangible assets JMachinery and equipment Buildings and structures JLand and water Prepayments and construction in progress Other tangible assets Investments Total fixed assets and other non current assets Current Assets Inventories and work in progress Accounts receivable Other receivables Bank and cash Total Current Assets SHAREHOLDERS EQUITY AND LIABILITIES Shareholders equity Share capital Share issue premium Other restricted equity Retained earnings Profit loss for the period Total shareholders equity Appropriations
197. ows 10 sub headers grey 99 Specification rows yellow 99 Detail level rows fawn light yellow brown 10 99 99 98010 Additionally you can link to other Sheets and Workbooks You may use the calculation operators or blank of the drop down menu when specifying the relationships between the income rows When you start a new investment calculation the default operator is Change the operator when necessary As one of the operators is a blank space you can specify that row as informative text because the figure will not sum up or as a variable for use elsewhere INCOME STATEMENT j E PEZ PREA 1 2015 12 2015 12 2016 12 2017 Months per interval A A E A Se OE 160 tr sooj sain se rice 18 IE T mm mw w Turnover cumulative financial year Other operating income Variable costs E ress l li a as Fuel cost per fl ight Number of flights Fuel cost per passenger es of passengers Gross margin 30 2 1431181 cumulative financial year 330 24 1431 181 cumulative financial year E 83 5 Fixed costs Staff costs A EBITDA 0 747 740 837 031 929 674 Income cumulative financial year shows cumulative incomes per financial year For example if the financial year is 12 months and the calculation is monthly income is cumulated e g RES Y 1 2015 1 2015 2 2015 3 2015 4 2015 5 2015 6 2015 7 2015 8 2015 9 2015 10 2015 11 2015 12 201 1 1 1 1 1 1 1 1 1 1 1 Months per interval
198. ox choose Show and click OK Row Specification Specification For row Income production Row 442 Sub raws Insert delete Capacity Tons Row 443 Load Factor degree of utilization Row 444 Empty row 445 Sub total rows For Income Sales price per ton 5 Row 446 production Row 442 Empty row 447 Tons produced Row 445 3 2 5 3 6 Delete row specification To delete a row specification click the 3 button to open the dialog box choose Delete and click OK 11 17 2014 Copyright O Datapartner Oy Invest for Excel Row Specification Specification For row Income roduction Row 442 Sub rows Insert delete Capacity Tons Row 443 Load Factor degree of utilization Row 444 Empty row 445 Sub total rows For Income Sales price per ton 5 Row 446 production Row 442 Empty row 447 Tons produced Row 445 C Modify Hide c Delete O The result of the specification rows will be written on the main row Note that the Delete function will delete the specification rows irreversibly 3 2 5 3 7 Create detail level specification rows You can add one sub level of detail rows for specification rows Choose the specification row O to which you want to add new detail rows specify number of rows to add and click OK Row Specification Specification For row Income production Row 442 Sub rows Insert delete Load Factor degree of utilization Row 444
199. ox differs depending on the operating system and version of Excel used Page 28 Copyright O Datapartner Oy 11 17 2014 Invest for Excel T lt JENSW Documents Calc Examples Transport and Logistics Airlines v Search Airlines Organize v New folder A Name Date sad jm This PC de Desktop E Documents Je Downloads ab Music E Pictures svifi isee BE Videos Local Disk C a Data D Ls wn nem 45 Invcomp new flight route scenarios xlsm 31 7 2014 17 27 Microsoft Excel M G Invfin airplane xlsm 31 7 2014 17 27 Microsoft Excel M a5 Invfile new flight route incl financing and USD sensitivityxlsm 31 7 2014 17 27 Microsoft Excel M 45 Invfile new flight route incl financing xlsm 31 7 2014 16 32 Microsoft Excel M 45 Invfile new flight route incl working capital xlsm 31 7 2014 16 03 Microsoft Excel M 45 Invfile new flight route incl working capitalxlsm xlsm 31 7 2014 15 47 Microsoft Excel M a5 Invfile new flight route incl terminal building xlsm 31 7 2014 15 18 Microsoft Excel M 45 Invfile new flight route base case nominal xlsm 31 7 2014 14 56 Microsoft Excel M G5 Invfile new flight route base case xlsm 31 7 2014 14 25 Microsoft Excel M File name Save as type Excel Macro Enabled Workbook xlsm Authors Stefan Westerbladh Tags Addatag Title Add a title C Save Thumbnail Hide Folders Tools Cancel If Save As is used you will be prompted
200. partner Oy Page 197 Invest for Excel The basic data of the investment is entered regardless of balance item type Depreciation percent is however not needed for corporate acquisition The number of columns for entering historical data has been defined under basic values History columns can be toggled with the hide unhide button LE Enter the expected purchase price Company A is prepared to pay for Company B on the first row The other rows of investment table are by default reserved for future capital expenditures of Company B There are a total of 30 rows available and any of the rows can be hidden if not needed INVESTMENTS REALIZATIONS B imputed depreciation FES a 1 2006 12 2006 12 2007 12 2008 12 2009 12 2010 Months per interval Depr re 12 12 12 Depreciation q 0 y Depreciation straight line a OS SS SS Depreciation straight line A Investments 0 0 0 0 Realizations 0 0 0 0 0 0 Depreciation i 0 1 0 0 a Realization profit loss i 0 o 0 0 a Book value o o A A a y Group investments 4 000 000 0 a Group realizations o Group depreciation 0 0 o Group realization profits 7 losses 1 O 0 Group book value o00000 4000000 oo0o000 4oooooo 4000000 4000000 Goodwill calculation Method 1 2006 12 2006 12 2007 12 2008 12 2009 12 2010 se eS Io IIS FE Share aaa 100 00 Share capital Share issue premium Other restricted equity Retained earnings
201. period 82 2006 712011 5 years Financing type A Equal amortizations y Balloon payment Amortization interval Months E ster principal payments gt Interest based on Euribor 12 months During drawdown period B Paid from first draw according to interest payment interval Y Interest FixedMfoating indias fried f O Enter interestrate changes gt Interest margin p a 0 50000 Interest paymentinterval Interest year Total rate p a 4 95000 E months E days field pa 5 01126 TEUR Type Payment interval ECA Insurance Premium 0 10000 Upfront 35 of total financing at financial closing e l ECA Commitment fee 4 20000 of undisbursed balance on each disbursement i gt CIRR Agency Fee 0 30000 Upfront 3 of total financing at financial closing i atte LL Arrangement fee 50 00 Uptontamountatfinancialelosng e gt Legal costs 5 00 Fiedteenawanee O e le Admin Fee BO 00 Fixed fee in arrears wife gt Mot in use gt Mot in use gt All in rate p a 5 18702 Total finance cost 513 091 TEUR 11 17 2014 Copyright Datapartner Oy Page 105 Invest for Excel Spec sheet Specification of the loan in a detailed tabular form BETS IED GT setat utere gee usol ta meer gt Age rr Figures TEUR EereptaalersrtmsetEssk Espa interest Fixed O O A A a C A a AAA AS ECA Commilaral CIRR Agrang Pre Arrangrmrs fee rua
202. r On Error Resume Next Don t stop for any Error ee eee ee a a a a ee AAA AA r EA ARA AAA ERRATA EA EA AAA AAA First custom sheet Investment ile assumed active intMySheet iRPeturnMySheet 1 A ctiveWorkhook Err U With Sheets intMy5heet If Err lt gt O Then Exit Sub No sheet found Note The term change macro should be carefully programmed so that any program code execution of Invest for Excel is not compromised A total of 99 assignable macros are reserved for buttons etc on your own sheets in an calculation file To use an assignable macro follow these steps 11 17 2014 Copyright Datapartner Oy Page 227 Invest for Excel Enter the name of the macro Macro workbook name Term change macro Personal xls TermChangeMacro Assignable macros Moers to run in Personal xls AssiqnableMacrol1 C OutlineMySheet 20 8 The macro should be located in the specified macro workbook PERSONAL ALS MylnvestForExcelMacros Code t Format Debug Run Tools Add Ins a indow Help Mai ai 18 un 137 col 26 x General p A lul ka OutlineMysheet oub out lineMysheet i P Outlines my sheet according to caller object button 1 4 p Dim strCaller a3 String intLewel 4s Integer fWasProtected 4s Boolean On Error GoTo O Aipplication Sscreenlpdating False y Jul PAET EE strcaller Mid This Vorkbook Mames CallerObject RefersTo 5 Remo intLewel
203. r Oy 11 17 2014 Invest for Excel 4 6 3 Updating the consolidated file The consolidated file works like any investment calculation and it can be used as a starting point for a new calculation BASIC VALUES Project description Consolidated Calculation term years 10 years Interval length months Number of intervals an MAY YY am Calculation term begins 01 2006 in the beginning of period Calculation point 01 2006 in the beginning of period Calculation term ends 12 2015 fin the end of the period Figures 1 1000 1000000 Currency Discount rate per annum EJ 950 required rate of return 2006 2007 2008 2009 2010 gt Income tax You can update the consolidation file with the Update button if you make any changes to the source files NOTE If the calculation is updated from the source files any manual changes will be undone 4 6 4 Consolidation Options Pressing the Options tab reveals more consolidation functionality 1 2 3 4 5 6 7 8 Ej Currency Translation Currency Translation Use exchange rates in A AA Consolidated file currency EUR 11 17 2014 Copyright O Datapartner Oy Page 137 Invest for Excel 4 6 4 1 Consolidation file periods Periods in the consolidated file are defined in the Options tab Consolidation Files Options Consolidated file periods Financial year C Longest common period Financial year Periods are summed
204. rection of income tax for financial items Acquisition valuation Tax effects mother company Sum of discounted correction of income tax for financial items Interest bearing net debt of acquired company Net assets opening balance Decision making rule The investment is profitable when DCVA gt 0 4 1 1 4 Annuity of investment The investment annuity method shows the sum of discounted investments divided into fixed amortisation instalments over the estimated term of the investment using the discount factor as the interest rate This method is not a profitability gauge as such but it is a complementary indi cator Think of it as an annual leasing payment for the investment It is an imputed cost of the in vestment per year By default this row is hidden in the Profitability Analysis but it can be shown pressing the button found top left 4 1 1 5 Internal Rate of Return IRR The internal rate of return is the discount rate resulting in NPV 0 In other words this method is used to calculate the discount rate yielding a net present value of Cash flow from operations to equal the investment costs IRR is an abbreviation of the term Internal Rate of Return The term is widely used in other languages too Internal rate of return is the most common investment profitability index Companies usually have a policy concerning the required rate of return on the investment capital The required rate of return should at lea
205. rm dialog box is automatically shown You have to select the total time span term for your calculation By default the calculation term is defined on a yearly basis A more detailed approach can be selected where the investment term can be split into two phases in order to enable calculation intervals of variable lengths The phases can be divided into intervals ranging from a month for the most detailed to a year for a rough calculation The idea is to allow monitoring of the different phases such as the initial phase Phase 1 and the actual implementation phase Phase 2 in more or alternatively less detail You can open the Investment term dialog by clicking the button in the Basic Values screen The calculations sheet is limited to 240 columns It is advisable to use a calculation term that corresponds to the useful life of the investment object Ideal term would be the economical life of the object or if it is not known the technical life of the object or if neither is known the depreciation period of the object A shorter investment term can also be used as long as resi dual value of the project is included Select the month in which the company s financial year ends default is December The program automatically divides the calculation intervals into financial years This is particularly significant if the financial effect of depreciation and taxation on are taken into account Page 34 Copyright Datapartner Oy 11
206. rogram is protected by copyright law and international treaties Unauthorized reproduction or distribution of this program or any portion of it may result in severe civil and criminal penalties e Click on the button to see in which Invest for Excel edition the open files are created 3 7 001 3 7 001 Invfile new flight route ind financing and USD sensitivity x 3 7 3 7 001 Corporate Business planning monthly Area1 xls 3 3 3 3 003 Windmill park on the coast xlsm 3 6 3 6 007 11 17 2014 Copyright O Datapartner Oy Page 17 Invest for Excel 1 9 General buttons Description Print current table Move to previous table Move to next table Move to the Invest for Excel home page Create chart Show help for current table Saa Pressing the camera button will place a picture copy of the table or chart in the clipboard The picture can then be pasted into another sheet document or software See 1 9 1 Picture copy below Unfreeze freeze column headers Calculation sheet Page 18 Copyright Datapartner Oy 11 17 2014 Invest for Excel 1 9 1 Picture copy Invest for Excel files created with version 3 7 or newer templates have camera buttons at each table Pressing the camera button will place a picture copy of the table or chart in the clipboard The picture can then be pasted into another sheet document or software BASIC VALUES Project description Calculation term years E 8years
207. rs save them in the common folder The private folder is normally located in a folder that only you have access to and the common folder is located on a shared server Place the folder in a place on the server of which backups are taken automatically Ask the PC Administrator to create on the server a common folder with read and write access for anybody doing investment calculations A e Lila b i 4 F hi Working Folders Template Folders Template Files Utility Files ZIP Define separate folders directories for your own calculation files and files you want to share with others Default Folder Private Folder C Users JENSW Documents Calc Examples Transport and Logistics Airlines A Activate Frivate folder Common Folder X DATA4 Calculation examples Activate Common folder No default folder active folder MC Users JENSW Documents Calc Examples Think of th defined folders as default folders that can be e calculation filss in these locations Calculation files can be sa dsily accessed You don t have to save all You ved anywhere without loss of functionalify fa Define which folder you will use more often the default folder or select No default folder meaning that the last used folder is active when opening and saving files Define the path to the fold r by typing it in or by clicking on the button on the right hand side It s possible to
208. rt menu LS Folders And Templates 23 Profitability J New 5 Open e Save gt de Print Gf Close A Exit File EE Basic Values 1 1 Compare Investment Income statement Working Capital Cash Flow Balance Key financials Roll forecast her metant al Page 102 Copyright Datapartner Oy 11 17 2014 Invest for Excel Roll forecast Roll Calculations sheet Periods and Input cell values and formulas in the Calculations sheet will be shifted one period to the left Last historical column will be deared for input of latest outcome Roll Options Roll periods and numbers Y Convert formulas to values when needed to preserve value 7 Roll periods only You can choose to roll periods and numbers or periods only When you roll numbers formulas are converted to values if the moved formula would change the value of the cell If you however prefer to keep all formulas even if values would change you can uncheck the Convert formulas to values when needed to preserve value checkbox After rolling periods and numbers INCOME STATEMENT 1000 EUR 6 2014 9 2014 12 2014 3 2015 6 2015 9 2015 12 2015 3 2016 Months perintenal a aaa aa a Income specified Sales forecast 24 120 22 970 23 988 24 127 24 127 24 127 24 610 Income cumulative financial year Other operating income Variable costs Variable costs total 2 894 2 756 2 856 Gross m
209. rt term borrowings Cumulative discounted free cash flow to equity 350 O00 310 470 1508 256 691 ogy pS 071 E Equity increase i decrease t do aooo oy ma e a a a a a Cumulative total cash flow 45 064 203 894 376 589 563 706 765 825 Note that Invest for Excel doesn t make a distinction between preferred stock and common stock so there s no separation of preferred dividends Page 122 Copyright O Datapartner Oy 11 17 2014 Invest for Excel Profitability analysis The Profitability analysis is divided in two parts when FCFE based indicators are included PROFITABILITY ANALYSIS Project description New flight route To Firm Nominal value of all investments 4950 Discounted investments 4950 Required rate of return 10 16 Calculation term 10 0 years 1 2015 12 2024 Calculation point 1 2015 In the beginning of period Present value of business cash flows Nominal PV PV of operative cash flow 5 703 599 PV of residual value 452 742 Present value of business cash flows 6156 341 Present value of reinvestments 0 Total Present Value PV 6 156 341 Investment proposal Nominal PY Proposed investments in assets 4 950 000 4 950 000 Investment subventions g 0 Investment proposal 4 950 000 4 950 000 Net Present Value NPV 1206 341 profitable NPV as a monthly annuity 15 752 Internal Rate of Return IRR 14 69 gt 10 16 gt profitable Modified Internal Rate of Return MIRR 17 59 gt 10 16
210. rterly level Year relative to investment term start year 0 Indude current year 2015 Interval length Number of Number of months months j 01 2015 09 2015 Note that although historical periods are not included in calculating the result of the calculation they can affect cash flow Changes in working capital can be affected by opening balance in Balance Sheet Also depreciations of opening balance book values will affect income tax calculation The history columns can be hidden if necessary when printing or presenting calculations Click on LL in the blue bar at the top of the calculation tables When you wish to unhide the columns re click the same button Page 38 Copyright O Datapartner Oy 11 17 2014 Invest for Excel 3 1 2 Basic Values BASIC VALUES Calculation phase 2 Total Description Start up Calculation term years 5 years 5 years months 3 months 3 months Interval length months Number of intervals Calculation term begins 10 2015 in the beginning of period Calculation point 10 2015 inthe beginning of period Calculation term ends 12 2020 in the end of the period Figures 1 1000 1000000 Currency Discount rate per annum required rate of return Per interval 10 00 0 80 per month 2015 2016 2017 2018 2019 gt Income tax B20 x w Enter the following data Project description An informative text shown as an identifier in Inves
211. run If you want to run a macro ofyaur own after a calculation term change in Invest for Excel enter the name of the macro under Tema change macro Term change macro TermChangeMacro The specitied macro workbook should contain the macro Option Explicit Personal xls These macros used with the custom sheeta in the Invest for Excel investment file Contact info thisis melmycacmpany com Const woaheetPassword ThePassword General sheet password This macro is fun after a calculation term in the company template Updates the custom sheets periods to correspond to Calculation sheet Dim intMiyctoll s Integer First colma after zero periodi Dim intHhyCole 4s Integer Last column before residual Dim intHiyNo ftols s Integer Number of columns Dim iTemp s Integer sTemp s String Temporary variables Dim iTempe 4s Integer More temporary variables Dim intCalcSheet s Integer Calculations sheet index Dim intzerocCol s Integer ero column index in calculations sheet Dim intResidualCol as Integer Residual column in calculations sheet Dim intCalcColl is Integer First column calculations sheet Dim intCalccCole As Integer Last column calculations sheet Dim intCalcNootcols as Integer Number of colinas calculation sheet Dim inthySheet s Integer Sheet index Dim flasProtected 4s Boolean Protect sheet afterwords only if it was protected a AAA EEE RARA RARA RARA AAA AAA EEE AE RA AREA AAA AAA AAA AREA AAA r r a a r r
212. rval SA AO AO AO AA EBITDA Operating income before depreciation 150 i 300 E 315 000 330 750 341 288 Depreciation 100 000 100 000 100 000 EBIT Operating income 150 T 300 000 215 000 230 750 Financing income and expenses Mas Si Financing income and expenses a Financing income and expenses Financing file 32 B63 EBT Income after financing items ee 143 600 267 338 186 075 205563 225 838 When capitalization is used the capitalized part of the financing costs is included in the chosen assets book value and depreciated accordingly LL REALIZATIONS BB imputed depreciation FES organ 41 2008 12 2008 12 2009 42 2010 42 2011 12 2012 Ne per interval Depr Pte 2 1 000 000 eS m straight line 40 00 00 106 906 106 906 106 906 do Book vale OC 1036 400 1063063 962 156 855 250 748 344 1 000 000 Mo ee A EE INCOME STATEMENT RES 5 BE amp 4 2008 12 2008 12 2009 12 2010 12 2011 12 2012 Months per interval AS AO AO AO a A EBITDA Operating income before depreciation 150 E 300 e 315 000 330 750 347 258 106 906 106 906 106 906 Depreciation 20 925 AE EI 150 000 300 000 179 169 198 656 656 a Financing income and expenses Financing file EBT Income after financing tems EBIT Operating income 1450 000 300 000 208 094 223 644 Financing income and expenses E Financing income and expenses GE Page 108 Copyright Datapartner Oy 11 17 2014 Invest for Excel 4 Result 4
213. s and add or delete headings and input fields and move fields Page 158 Copyright Datapartner Oy 11 17 2014 Invest for Excel BUT 1 Note that the Change Language function will not work with the edited items 2 Caution must be taken when editing the proposal form otherwise the update function might not work as it should Note that if you need to make changes to this section you can also contact Datapartner and ask for an offer for the work involved Remember to save the modified form as a new template Select File menu then Save as change the file type from XLSM Excel Macro Enabled Workbook to XLTM Excel Macro Enabled Template Give the file a name and save it in the folder you prefer See chapter 2 1 3 template files for more information about using templates v T de gt ThisPC Local Disk C Users JENSW Documents Calc Templates gt w Search Templates p Organize v New folder JE v di Offentligt A Name Date modified Type Size J Kemira 6 9 2013 11 39 File folder jM This PC gt a a rm Dect p Invfile BUD malli xitm 27 6 2014 13 23 Microsoft Excel M 1201 KB eskto E xf Invprop XYZ Group xltm 5 6 2014 17 46 Microsoft Excel M 1 146 KB E Documents y File name Invfile template xitm y Excel Macro Enabled Template xitm v Save as type a Excel Workbook xIsx Excel Macro Enabled Workbook xlsm Tags Excel Binary Workbook x
214. s this data comes straight from the Invest ment table Extraordinary income or expenses expressly associated with the implement ation of this investment are given here Use as many rows as you need Income tax total income taxes for the financial year The income tax is calculated as a percentage of the sum of Income before appropriations and taxes and Appropriations Other tax options are available see 3 1 2 4 Net income for the period profit loss is shown per period cumulatively per financial year and as a percentage of the profits per cumulative financial year The Net income for the period is the sum of Income before appropriations and taxes Appropriations increase decrease Income tax Deferred tax and Minority interest 3 2 5 3 Specification and detail rows Invest for Excel allows flexible handling and processing of rows You can add name delete and hide specification rows freely for many rows with the buttons You can also select cal culation operators by yourself For an example see the table below E RES Ba 1 2005 12 2005 12 2006 Months per interval PA A 12 Income specified Income production 316 875 439 558 Capacity Ton 10000 di Load factor f degree of utilization e EGE sales price ton Eb sales price component 1 5 Sales price component 2 18 15 Tons produced a dos 6 BERS In the calculation shown the user has 1
215. s analysis Refer to Item 5 4 Income analysis Here you simulate the effect of changes in fixed costs 11 17 2014 Copyright Datapartner Oy Page 165 Invest for Excel 5 7 Income variable analyses You can create up to 6 Income variable analyses An Income variable can be selected from Investment rows Income rows Variable cost rows or fixed cost rows Example Variable s impact on profitability ESE E Change in value lt Not in use gt aid 19 1 Turbines 15 pcs h sample value 9 2014 29 2 Construction Internal Rate of Return IRR eee Change 444 Turbines 445 Capacity per turbine 447 Utilization rate 448 Price per kWh 449 Produced kWh 450 451 ey Ea 5 1 458 461 Other operating income 463 Raw materials and consumables 464 External charges 465 Staff costs 466 Other variable costs Key financials 12 2014 467 Cost per kWh produced EBITDA Operating income before depreciation 7 968 Produced kwh facoy Variable s impact on profitability Change in value g Sample value apoi irtera ratorem 14 6 Internal Rate of Return IRR Key financials 12 2014 10 0 EBITDA Operating income before depreciation f 720 You can choose if you want to include row numbers in the dropdown lists with the check button beside the analysis Show row numbers Page 166 Copyright O Datapartner Oy 11
216. s of the last column You may also need this function when entering the residual value of the investment object as a positive figure in the last column Residual Go to the first column of the spreadsheet with this button If the calculation includes historical data columns you can hide or unhide them by clicking this button on the right Other buttons Button 11 17 2014 Eee Hels Description Print current table Move to previous table Move to next table Move to the Invest for Excel home page Go to chart create a chart To select an existing chart or create a new one based on the figures in the table Show help for current table Pressing the camera button will place a picture copy of the table or chart in the clipboard The picture can then be pasted into another sheet document or software Unfreeze freeze column headers Calculation sheet Copyright Datapartner Oy Page 51 Invest for Excel 3 2 3 Row outlining Row outlining in the Calculations sheet is available from Standard edition of Invest for Excel Modifying outline levels is available from Pro edition Each table statement in the Calculations sheet of a calculation file has two controls for outlining The dropdown list is for selecting outline level 1 lv Five levels have been reserved for outlining Outline Choose Modify Choose outlne level Apply to f Whole sheet In the Choose tab you can selec
217. s options in consolidation are found in the Options tab of the Consolidation dialog box By default all eliminations are applied Consolidation x Files Options Consolidated file periods 4 Apply eliminations En PE 7 3 4 5 6 7 8 3 You can choose to apply only selected groups of eliminations when you are doing a partial consolidation Fies Options Consolidated file periods Finandal year Longest common R W Apply eliminations In the Invest for Excel menus Eliminations can be found in the Input page 11 17 2014 Copyright Datapartner Oy Page 141 Invest for Excel In the short menu Investment Income statement Working Capital Balance Key financials Roll forecast Investment calia nmre Page 142 Copyright Datapartner Oy 11 17 2014 Invest for Excel 4 6 4 3 Consolidation with currency translation To consolidate with currency translation check Translate currency in the Options tab of the Consolidation dialog box 1 T 3 4 a 6 7 B g La Currency Translation For currency translation use exchange rates in InwccyYearEnd xlsm al 7 4 Consolidated file currency Specify the currency file to use when translating currencies and specify consolidated file currency and the unit in which to show figures Note that each consolidated source files must have currency specified in the basic values 11 17 2014 Copyright Dat
218. s the English term for the repayment period Decision making rule The shorter the Payback time the better the investment is This rule is however not straightforward For instance it fails to take into account any consecutive cash flows although they might be significant for profitability Therefore Payback should be used only as a supplement to other measures of profitability You can also see when the Payback point will be reached break even point i e cumulative discounted cash flow becomes greater than 0 If you want to know the payback time without the effect of the discount factor change the discount rate to zero 0 in the Basic Values screen Payback is calculated using NPV formula N Formula O NPV gt FCF l r t 0 FCF t Free cash flow in period t t period r discount rate per period N number of periods For residual values t N Payback time years The number of periods t for which NPV 0 12 Duration of period in months In Invest for Excel payback is presented in years with one decimal E g 3 6 is 3 years 60 of 4th year not 3 years and 6 months Page 114 Copyright Datapartner Oy 11 17 2014 Invest for Excel Payback can also be viewed as a chart Click the Uhr button Cumulative Discounted Free Cash Flow USD Payback time NPV 0 3 4 Based on discounted FCF 150 000 100 000 100 000 12 2013 12 2014 12 2015 12 2016 12 2017 12 2018 12 2019 12 2020 The
219. senting the calculation to the investment decision makers It is a so called investment proposal Many key figures are automatically transferred from the investment calculation to this form hence they need not be filled in manually When the calculations are ready experts in various parts of the organisation have been heard and the matter has been discussed once more face to face with a colleague you can print the calculation in report form It is also possible to send the calculations as files e g attached to e mail messages The investment proposal contains general information about the project the decision making process the persons responsible for the project and the key figures The investment proposal is a separate file that you can freely name save copy or print To open an investment proposal form choose one of the following three different ways By clicking 1 The investment proposal button in the Home Screen 2 The button in the top right corner of the Result table 3 By selecting it from the Invest for Excel menu Choose File New Investment Proposal creates a new investment proposal Choose File Open Invprop XYZ opens a proposal called Invprop XYZ When switching to non opened Investment proposal the following window will open up Invest F Ed Could not detect an open Investment proposal File Cancel W Private Folder Common Folder Open From Sawe in Now you can ope
220. son texts Profitability comparison row texts can be edited in all available languages Press the button in the top left corner of the Profitability comparison table Select the Edit Row Texts tab to edit row texts Pile ashowiows Hide Show Rows Edit Row Texts Row English GB German DE PROFITABILITY COMPARISON RENTABILITATS Y ERGLEICH Figures Werteinheit i Investment object Investitionsobjekt Mominal value of all investments Gesamtinvestition Nominalwert i Required rate of return Renditeforderung i Calculation term years Planungshorizont Jahre i Calculation term Planungshorizont i Calculation point Kalkulationszeitpunkt i Interval length months Dauer der Periode Monate PY of met cash Flow Barwert des betrieblichen Cash Flows I PY of operative cash Flow Barwert des Operativen Cash Flows PY of residual value Barwert der Restwerte i Present value of business cash Flows Barwert des betrieblichen Cash Flows i Present value of reinvestments Barwert der Reinvestitionen i Total Present Value PY Barwert gesamt i Filter languages Use the tabs at the bottom of the page the buttons in the Home screen or the Invest for Excel menu to access the charts Page 130 Copyright O Datapartner Oy 11 17 2014 Invest for Excel 4 4 2 NPV chart The net present values of the investment alternatives Eje JL Net Present Value NPV 1320 000 1300 000 1
221. st match the financing costs The required rate of return is staggered according to the type of investment Staggering the required returns on different types of investments makes it possible to delegate investment decisions and to direct operations in line with the chosen investment policy lt makes sense to assess investment alternatives in advance on the basis of a specific rate of return requirement as companies usually become aware of potential investment objects one by one FORMULA The internal rate of return IRR is defined as the compound rate of return r that makes the NPV equal to zero N Formula 0 NPV Y FCF 1 IRR t 0 FCF t Free cash flow in period t t period IRR Internal rate of return per period N number of periods For residual values t N 11 17 2014 Copyright Datapartner Oy Page 111 Invest for Excel Multiple Internal Rates of Return Sometimes a series of Free cash flows has more than one IRR Invest for Excel searches for 40 possible IRR s If it finds more than one solution it will return the highest and the lowest IRR 4 1 1 6 Internal Rate of Return before tax Internal rate of return before tax can be found in the Profitability analysis table Internal Rate of Return IRR 33 04 gt 12 Internal Rate of Return before tax 4181 Modified Internal Rate of Return MIRR 2377 gt 12 In some type of calculations like real estate valuations IRR before tax is
222. t 3 2 5 10 Return On Net Assets RONA Definition Operating income before tax EBIT OF after tax NOPA T RONA Operating income before tax EBIT OF after tax NOPAT Net assets 3 3 Average Opening balance or Balance at the end of period That is the return on the invested capital Concerning the net capital of this investment only the figure might not be comparable with the return on the net capital of the whole company Where NOPAT Net Operating Profit After Tax EBIT Tax Note that tax can be income tax in the Income statement or a calculated tax EBIT tax percent if the following option is selected Page 88 Copyright O Datapartner Oy 11 17 2014 Invest for Excel Net Assets Total fixed assets and other non current assets Net Working Capital Where Total fixed assets and other non current assets are the sum of assets in balance sheet BALANCE SHEET EUR Y 1 2015 Months per interval ASSETS Fixed assets and other non current assets Intangible assets 0 Tangible assets 4 950 000 Investments 0 Total fixed assets and other non current assets 4950 000 Net Working Capital is the sum of the Working capital statement last row Net assets definitions for period t Net Assets Net Assets y Net Assets Average a t Net Assets OpeningBalance Net Assets _ Net Assets ClosingBalance Net Assets 3 2 5 10 1 Economic Value Added
223. t concerning accounts payable liabilities work in an opposite way Change in working capital is Short term assets increase decrease plus Inventories increase decrease plus Current liabilities increase decrease Net working capital Working capitali 1 Change in working capital 11 17 2014 Copyright Datapartner Oy Page 95 Invest for Excel 3 2 7 Cash Flow Statement The cash flow calculation is a report giving a general view of the distribution of cash flows CASH FLOW STATEMENT o 1000 ES 1 2006 12 2006 12 2007 12 2008 Months per interval aa ao u Cash flow from operations Income Variable costs Fixed costs Extraordinary income amp expenses Income tax Change in working capital Cash flow from operations Asset investments and realizations Free cash flow FCF Discounted free cash flow OFCF Cumulative discounted free cash flow Information Financial cash flow Financial income and expenses Correction of income tax for financial tems Long term debt increase f decrease Equity increase dividends 1 Changes in short term borrowings Total cash flow Cumulative total cash flow While calculating the overall profitability of an investment NPV IRR MIRR and Payback Invest for Excel discounts the Free cash flow of the investment Discounted free cash flow means the current free cash flow discounted to the Calculation point in time usually the beginning The alt
224. t for Excel s input screens reports and printouts Calculation phase Description Informative text Description texts are showing if you have selected two phases and chosen to use phase descriptions Figures 1 1000 Unit 1 1000 1000000 Specifies the accuracy of amounts In the case of a large investment it is practical to enter sums in thousands or even larger units When using thousands as accuracy level enter 1000 in this cell The unit used is shown as an informative text in the calcula tions Currency Informative text The currency unit used in the calculation Type in currency unit e g USD 3 1 2 1 Currency conversion Currency conversion is valid only for Pro and Enterprise edition Note This function is used only to convert a readymade calculation from one currency to another Use the button to open the Currency conversion dialog box 11 17 2014 Copyright Datapartner Oy Page 39 Invest for Excel In the first page of the dialog box you can specify any non monetary rows that should not be included when the currency conversion is done 1 Specify non monetary rows 2 Currency exchange rates and options Rows that could contain non monetary data Non monetary rows 450 Average ticket price 444 Number of passengers 465 Fuel costs empty plane 445 Number of passengers Mark the rows 466 Fuel cost per flight 57 Number of fights that contain non 468 Fuel costs from passenger weight 470
225. t if the outline level selection applies to the current table statement or the whole sheet In the Modify tab you can modify the outline level for each row in the current table statement Page 52 Copyright Datapartner Oy 11 17 2014 Invest for Excel Outline Choose Modify Row Row text 326 EBITDA Operating income before depreciation 1 a Izy cumulative Financial year 3 328 cumulative Financial year 2 GEE Depreciation E a EBIT Operating income E 333 cumulative Financial year 3 334 cumulative Financial year E 335 Financing income and expenses 1 336 Financing income and expenses E 337 Financing income and expenses Financing File 3 338 EBT Income after financing items 1 339 Extraordinary income and charges z 340 Realization profit loss 3 341 Other extraordinary income charges 3 M 342 Income before appropriations and taxes 2 M coc Modifying outline levels is available from Pro edition A locking option for outline levels have been added in the File locking Options dialog File Locking Options Password Basic Values Calculations Result Check the parameters that should be LOCKED Hide Show specific rows Edit texts Financial ratio calculation 11 17 2014 Copyright Datapartner Oy Page 53 Invest for Excel 3 2 4 Investments Realizations The Investments Realizatio
226. t payments follow the amortization Interest payrnent interval schedule but not always You may select 1 2 3 4 6 or 12 months interest E payment intervals months Interest is calculated either based on the assumption of 360 days per year or 365 days per year The simplified 360 360 days days is more common Interest year Entering other costs related to the debt financing In the left column Fees you may name the fees In the 2 column you enter either a percent or a monetary value Please do not use format Then select from the dropdown menu how the fee is calculated Fees USO Type Payment interval Loan arrangement fee O s0000 A Upfront of total financing at Financial dosing Guarantee fee O 30000 F F of balance in advance repayment period ma j gt Slag heno 10 00 1 Fred tee in arrears lale le gt C O E C O O E C O IL C O ES CO IE CO ES COC OG All in rate p a 3 05499 Total finance cost 476 433 USD You may select from 15 options Not in use use this option when you don t want to calculate a fee A Upfront of total financing at financial closing 11 17 2014 Copyright Datapartner Oy Page 219 Invest for Excel the fee is calculated as a percentage of total debt paid immediately at financial closing B Upfront amount at financial closing the fee is entered not calculated payable immediately at financial closing C Flat on each disbursement
227. tal cash flow a b c d 207 790 856 120 26 792 399 225 37 509 602 653 53 659 535 078 513 644 492 211 470 778 449209 513 031 Cumulative cash flow 207 790 1063 910 1037 118 1436 343 1398 834 2001488 1947 829 1412 751 899 107 406 896 63 882 513 091 All in rate p a 5 1187 5 Total finance cost 513091 Total interest and fees paid When the financing is made up of several loans the program will make a summary of all loans including details and a report TotalSpec amp TotalView sheets TotalView sheet Example of summary report lt Select where you want to move gt v Financing the Space Station loan portfolio TOTAL FINANCING Figures 3 2003 3 2004 3 2005 3 2006 3 2007 3 2008 3 2009 3 2010 3 2011 2 2003 TEUR 2 2003 2 2004 2 2005 2 2006 2 2007 2 2008 2 2009 2 2010 2 2011 7 2011 7 2011 Number of months 12 12 12 12 12 12 12 12 5 101 Total financing 2 164 995 Starting balance 1 082 498 1515 497 2 164 995 1 948 496 1515 497 1 082 498 649 499 216 500 Drawdown 1 082 498 432 999 649 499 Capitalized interest Principal payments 216 500 432 999 432 999 432 999 432 999 216 500 Ending balance Interest expense 8 035 58 942 83 055 107 167 91 092 69 659 48 225 26 792 5 350 498 320 Total fees 10 550 1624 1 299 210 270 270 270 270 14 763 Total debt service b c d 18 588 60 566 64 354 323 877 524 361 502 928 401 494 460 061 221 850 2 676 086 Total cash flow a b c d 1 063 910 372 433 565 145 3
228. ted Presentvalue of business cash flows PY of operative cash flow 152 831 PV of residual value 87 032 male 247 o 5 83 Present value of business cash flows 239 863 Presentvalue of reinvestments maintenance etc Total Present Value PV Interest bearing net debt of acquired company Free cash flow based equity value EV 239 863 EV EBITDA 6 66 Based on EBITDA 12 2009 EV is the Free cash flow based equity value also referred to as Enterprise value and EBITDA can be selected from a dropdown list consisting of the periods in the calculation Page 120 Copyright O Datapartner Oy 11 17 2014 Invest for Excel 4 1 3 Profitability calculation based on Free cash flow to equity FCFE Enterprise edition includes a break down of free cash flow to Free cash flow to firm FCFF and Free cash flow to equity FCFE Program options You can include FCFE in the Invest for Excel program options W Indude Debt residual correction Update analysis charts automatically Basic values Cost of equity is added to the Basic values table when FCFE calculation is included in the file BASIC VALUES Calculation term years 10 years Interval length months Number of intervals Midi Calculation term begins 012008 fin the beginning of period Calculation point 012008 fin the beginning of period Calculation term ends 122017 n the end of the period Figures 110001000000 Currency Discount rate per
229. ted by the application is desired Inventories are basically calculated according to the formula Sum of amounts in the selected rows for the current period Inventory turnover period days y P yey Number of days in period If the turnover period is longer than the number of days per column e g 45 days in a monthly calculation two things happen 1 The balance increases only with 30 days costs not 45 2 The next period s will be affected additionally 3 2 6 4 Current liabilities Accounts Payable How much less working capital is needed thanks to payment term to suppliers There are two ways to enter the accounts payable 1 Enter the average term of payment for accounts payable in days i e the average number of days from receiving the goods until payment The program calculates the average amount of accounts payable per interval in default based on the 2 first rows of Variable costs Raw materials and consumables and External charges in the Income statement For more detailed liabilities management click the button Specify Accounts Payable ES for specification of current liabilities Gnas Don t specify 2 Enter the estimated average accounts payable per period on the line Adjusted balance This is an Li _ o alternative to entering the information on rotation The values in Adjusted balance overrule the values of term of payment for accounts payable Page 94 Copyright Datapartner Oy
230. terest from drawdown period E Paid From First draw according to interest payment interwal The alternative methods and their explanations A Paid monthly from financing interest calculated on loan amount and paid monthly B Paid from first draw according to interest payment interval interest paid according to selected payment interval starting from first loan withdrawal C Paid at first principal payment after drawdown period the first interest payment paid at the first amortization D Capitalized on financing and paid according to repayment plan during drawdown period interests are not paid accrued interests are put on top of loan so the loan amount will be bigger Enter interest rate Page 218 Copyright O Datapartner Oy 11 17 2014 Invest for Excel In the example to the right the benchmark rate Euribor 6 m is Interest rate p a entered a margin Interest margin p a It is not necessary to break the interest in Total rate p a 3 41800 SOPA Yield p a 3 44721 Yield p a is effective annual rate Interest When interest rate is floating you may enter forecasted interest rates for future periods by pressing the Enter interest rate changes gt button You can Fixedfloating sri A Floating Enter interest rate changes gt Stick to tne initial interest rate if you don t think you can predict the future Select interest payment interval Typically interes
231. that numbers can t be changed in a Snapshot file it can be for documentation and reporting issues for example as addendum for investment proposals for post audit purposes for banks within finance talks e The Snapshot file does not include program buttons or program macros so the file can be given to a person who does not have Invest for Excel e The Snapshot file is suited for sending via e mail thanks to the small file size NOTE Hidden rows can t be unhidden in a Snapshot file 2 8 SharePoint file menu A SharePoint file menu has been added for quick open from and save to a SharePoint location Enterprise edition is required File Input Result Analysis Format Other Window Help Explore folder of active file a y wy e fu gt POE FERN Oy Folders And Template Files y ul Mew Open Close Save Save s save To Zip file Ns Save Snapshot Sharepoint Open from Sharepoint Page Setup Save to Sharepoint Printer Save As to Sharepoint Print Sharepoint Location Exit To set default SharePoint location select SharePoint location in the Invest for Excel SharePoint menu Page 30 Copyright Datapartner Oy 11 17 2014 Invest for Excel Open from Sharepoint save to Sharepoint Save As to Sharepoint http go datapartner fi Calculations 2 9 Print a Select the reports to be printed Invcomp new flight route scenarios xlsm 7 lt Not Op
232. the beginning of the investment term the item Bank and cash in the balance sheet is also negative If financing of the investment was taken into account cash and bank would of course be positive When evaluating profitability of an investment it s perfectly normal Page 98 Copyright O Datapartner Oy 11 17 2014 Invest for Excel and in most cases even desirable to ignore financing and let bank and cash be negative in the beginning of the investment term Profits for the current financial year and the past years accumulate equity Debts are shown under liabilities and similarly accounts payable is shown under short term liabilities ES 3 2 9 Key financials Key financials A table with 60 editable rows is reserved for entering your own key financials C a E OODanas ES E Bin 1 2015 12 2015 12 2016 12 2017 12 2018 Months per interval Seje a t e J KEY FINANCIALS E wi BEHR LOE E ih 12 2018 Months per interval Return On Investment RON Return On Equity ROE 242 EV EBIT ratio Return On Capital Employed ROCE Number of shares outstanding diluted Earnings per share EPS Equity ratio 6 8 P S ratio Number format of Key financials cells can easily be changed using the button in the header bar The key financials can be used in Invest for Excel charts and sensitivity analyses 11 17 2014 Copyright O Datapartner Oy Page 99 Invest for Excel 3 2 9 1 Key financials in analys
233. the investment that declining straight line or sum of years digits depreciation is based on the default value is 100 That way you can calculate depreciation of a part of the investment only for instance when the residual value has been set and depreciation concerns the difference between purchase price and residual value Straight line Enter either the depreciation percentage or expected economic lifetime of the object depreciation time Invest for Excel automatically calculates the other The depreciation is calculated from the initial investment purchase price Declining balance Enter the depreciation percent The depreciation is calculated from the ending balance of each financial year Ending balance is the same as book value at the end of previous year new investments during ongoing financial year One time depreciation The asset is depreciated 100 in the specified first depreciation period Declining gt Straight line The higher of declining balance or straight line depreciation is used This method which ensures maximum depreciation is used in Germany Spe cify the depreciation percentage and the economic lifetime of the invest ment object Sum of years digits This depreciation method uses the sum of years left to depreciate when calculating the depreciation The sum of the years digits is a form of accelerated depreciation Example A 3 year depreciation plan the digits in the years of the asset s useful lif
234. them Consolidation Consolidate First you select type of consolidation usually Calculation files C Users JENSW Documents Calc Examples Business planning Mergers DE_Gas_LongtermPlan2012 23 xlsm 16 9 2013 10 43 56 C Users JENSW Documents CalclExamples Business planning MergersDE_Heating_LongtermPlan2012 23 d 15 9 2013 10 13 07 C Users JENS W Documents CalclExamples Business planning Mergers DE_Production_PTS2012 23 xlsm 16 9 2013 10 48 18 C Users JENSW Documents CalclExamples Business planning WMergersDDET_Transfer_LongtermPlan2012 23 16 9 2013 10 55 18 Press Add to choose the files you want to consolidate The chosen files can be saved as a consolidation description dsc for later use Open and save the consolidation description file with the open and save buttons El 7 The new button lar the file list Start the consolidation by pressing the Consolidate button 11 17 2014 Copyright O Datapartner Oy Page 135 Invest for Excel NOTE The consolidation may depending on the number of source files take several minutes The program proposes that you name and save the new consolidation file 4 6 1 Assumptions used in consolidation The source calculations have been created with the same assumptions for income tax include positive tax effects and discount factor Basic values are written to the consolidated file from the first source calculation By default the conso
235. three depreciation methods straight line declining balance or enter manually Choose from the drop down menu Enter the depreciation percentage in the grey cell to the right of the drop down menu if straight line or declining balance deprecation 11 17 2014 Copyright O Datapartner Oy Page 193 Invest for Excel 9 1 3 Investments There are four asset types under this heading Investments in associated companies Deferred tax assets Long term loans receivable and Other investments For each fixed asset type there are three rows The first row ending in specified shows asset investments specified in the investment table The second row shows the book value entered in historical period after depreciation The third row shows the depreciation of the historical book value You can choose between three depreciation methods straight line declining balance or enter manually Choose from the drop down menu Enter the depreciation percentage in the grey cell to the right of the drop down menu if straight line or declining balance deprecation 9 2 Inventories and current assets INVENTORIES RECEIVABLES BANK AND CASH Enter the balances for history periods only The figures entered the working capital calculation or taken from the Income statement based on rotation days are shown by default 9 2 1 Inventories and work in progress This row shows the sum total of all inventories in the Working capital table Note that if you ent
236. tion Here you define the font in which text and numbers are displayed Page 180 Copyright Datapartner Oy 11 17 2014 Invest for Excel 6 5 Borders Use Excel s Format Cell function to change the borders style and color of the column where the cursor Is Cutline Inside The selected border style can be applied by clicking the presets preview diagram or the buttons above 6 6 Format pattern Format pattern command This Microsoft Excel command allows paca i you to change the cell pattern of ealeciad celle Pattern Style E EEH U E HEHEHEHE HE E BEES O O MAME E SEO A E See Aa E EEO A E Anaco A E EEL A E Anaco A 6 7 Row Height Use Excel s Row Height function to set the height of the row where the cursor is 11 17 2014 Copyright O Datapartner Oy Page 181 Invest for Excel 6 8 Column Width Column width 10 Ok Cancel Use Excel s Column Width function to set the width of the column where the cursor is 6 9 Mark Unmark unlocked cells Mark Unmark unlocked cells In Invest for Excel the unlocked cells are marked with grey or yellow background colour SS a 50 000 200 O00 6 10 Arrange Buttons at In case Excel misplaces some of the buttons in Invest for Excel calculation file you can use this function to put them back in the right places Arrange Buttons 6 11 Insert worksheet Here is one way of including a table to the workbook
237. tion defined as A Specification rows first level are included if operators or have not been used If operators or have been used the margin value is calculated to the header row Taxes are calculated using the tax percent in the basic values Custom templates can be used for Marginal effects 11 17 2014 Copyright O Datapartner Oy Page 133 Invest for Excel 4 5 2 Updating a Marginal effect calculation The Marginal effect works like any investment calculation and it can be used as a starting point for a new calculation BASIC VALUES Update Calculation term years 6 years Interval length months 12 Number of intervals 5 MM YY Calculation term begins 01 2006 fin the beginning of period Calculation point 01 2006 fin the beginning of period Calculation term ends 12 2011 fin the end of the period Figures 1 1000 1000000 Sea Currency EUR Discount rate per annum EJ 12 25 required rate of return 2006 2007 2008 ARS Income tax You can update the Marginal effect with the Update button on the basic values sheet in case the source calculations have changed NOTE If the calculation is updated from the source files any manual changes will be undone Page 134 Copyright O Datapartner Oy 11 17 2014 Invest for Excel 4 6 Consolidation The Consolidation summarizes several workbooks and creates a new workbook cha based on
238. tions Withdrawals Principal Ending Interest Fees Financing cash flow at end of month USD of total payment balance Accrued Paid Total Cash flow Cumulative 0 6 2014 160 000 00 20 00 0 00 160 000 00 0 00 0 00 8 000 00 152 000 00 152 000 00 1 7 2014 0 00 0 00 0 00 160 000 00 800 00 0 00 0 00 0 00 152 000 00 2 8 2014 0 00 0 00 0 00 160 000 00 1 600 00 0 00 0 00 0 00 152 000 00 3 9 2014 160 000 00 20 00 0 00 320 000 00 2 400 00 0 00 0 00 160 000 00 312 000 00 4 10 2014 0 00 0 00 0 00 320 000 00 4 000 00 0 00 0 00 0 00 312 000 00 5 11 2014 0 00 0 00 0 00 320 000 00 5 600 00 0 00 0 00 0 00 312 000 00 6 12 2014 480 000 00 60 00 0 00 800 000 00 7 200 00 7 200 00 0 00 472 800 00 784 800 00 125 11 2024 0 00 0 00 0 00 40 000 00 259 000 00 0 00 0 00 0 00 226 000 00 126 12 2024 0 00 0 40 000 00 O 259 200 00 1 200 A 0 41 200 00 267 200 00 po Totals 800 000 00 100 00 800 000 00 000f 259200 00 259200 00 8000 00 267200 00 267200 00 Source file B Figures MEUR Total amount AA RR 5 94477 All transactions Withdrawals Principal Ending Interest Fees Financing cash flow at end of month MEUR of payment Accrued 5 Cash flow z 0 VETE 17 00 35 00 0 00 17 00 0 00 000 0 de 16 94 oa 1 10 2014 0 00 0 00 0 00 17 00 0 09 0 00 0 00 0 00 16 94 2 11 2014 0 00 0 00 0 00 17 00 0 18 0 00 0 00 0 00 16 94 3 12 2014 3 00 15 00 0 35 19 65 0 27 0 03 0 02 2 61 19 55 Page 146 Copyright Datapartner Oy 11 17 2014 Invest for Excel
239. titability Index PD A A E 114 ALLE Payback time years CUSCO UM E A AS 114 4 1 1 10 Simple Payback years NOt discounted vin icine ave dedi 115 4 1 2 PAPI od 117 4 1 2 1 EXtrapolaton Pond O E TEREE 119 4122 Iphed exit multiples erases E E nd dese 120 AE WANG BIBER A nA bi alas aah 120 4 1 3 Profitability calculation based on Free cash flow to equity FCFE ccccccccccceeeeeeeeeeeees 121 4 1 3 1 Debt residual COMETA iio 124 4 1 4 DOEVAsDased Pro tab MACAO AAA AA 125 4 2 PROFITABILITY ANALYSIS IN ACQUISITION CALCULATIONS ccsecesccsccsccsscescescessenscsscescescesses 126 4 2 1 Specification Of inter st bearing nebdebi ii iii 127 4 3 PROFITABILITY ANALYSIS IN IMPAIRMENT CALCULATIONS cccccccceeeeseeeeeeeeeeeceeeceeeeeeaaaaaeeeeees 127 Ah COMPARISON TABLE oa oral E 128 4 4 1 Edu Profitability comparison lexis A A ARES 130 4 4 2 NEV CHOU AAA A AA EEE 131 4 4 3 IRRE NOT e R E EEE E E EE 131 4 4 4 P ACK CGE oe a a a a 132 A IMA RCN A PEFFE T niee nna a a hast ae aan Nacoete eta dati al aa deat 133 4 5 1 Assumptions used in a Marginal ele ivi Bess 133 4 5 2 Updating a Marginal effect calculan eich od ie 134 AO CONSOLIDATION ry picie oceania taacatcngie aaah rene tesa sues ausauessveeneieotcat ace gus ie uanianeaaeen eee teatentaes eae eueae 135 4 6 1 ASSUIMpPLIONS us d in consoltdati ON lt lt lt da 136 4 6 2 Investment summary and consolidation IN O oooooonnnncnnnnnnnnnanononnnnnnnnnannnnn
240. tive numbers Environmental protection share If a part of the investment is targeted at environmental protection indicate its proportion as a percentage of the total investment Investment proposal is valid until Enter the date of expiry of the investment proposal Quotations are valid until Enter the date or expiry of tenders for the acquisition of the investment as proposed Estimated start up time Enter the month and year when the investment object will be taken into use From decision to start up months Lead time from date of investment decision to that of taking the object in use From start up to full production months Lead time from taking into use until investment object is in normal use normal operating rate Click this button in order to update figures from investment calculation Remember to click here if you change the investment calculation Capital requirement Data can be entered in the grey cells Other values come from the investment calculation Proposed investment Sum of proposed investments from the calculation file Note that reinvestments are not included Net working capital Short term assets of first period inventories accounts payable Copyright Datapartner Oy 11 17 2014 Profitability Sensitivity analysis 11 17 2014 dl amp PO h A wn Invest for Excel Interests from construction time Enter here financing costs accrued during implementation if yo
241. tive to entering the information on rotation term of payment NOTE If you enter values in Adjusted accounts receivables then these values overrule the values calculated from entered days Although Average term of payment days is visible in calculation only the Adjusted balance values are taken into account Explanations of rows used in Working Capital Short term assets table Receivables average term of payment days enter number of days per period Accounts receivable calculated according to the formula Accounts receivables are basically calculated according to the formula sum of selected rows for the current period Accounts receivable term of payment days pay reay Number of days in period If the term of payment is longer than the number of days per column e g 45 days in a monthly calculation two things happen 1 The balance increases only with 30 days income not 45 2 The next period s will be affected cumulatively Adjusted accounts receivable in this row you can enter values or formulas The values will overrule the Accounts receivable values per period Change in accounts receivable Accounts receivable Accounts receivable Or if the Adjusted accounts receivable where used then Change in accounts receivable Adjusted Accounts receivable Adjusted Accounts receivable Other Short term assets supported by Invest for Excel are 1 Minimum cash reserves Enter here the a
242. tively option is useful when investments continue during many years of operation Typical use If you want each year s new investments to be depreciated for a half year enter 6 months and select the Use consecutively option 3 2 4 2 6 Old investment It is possible to include depreciation of an existing asset in the calculation The book value and depreciation method of an existing asset can be specified in the Old investment page For the best quality calculation find out the purchase price of the asset when it was purchased how it is depreciated and use the Continue old depreciation plan option Note that including an old investment in the investment table is an alternative to including and depreciating book values in the balance sheet In chapter 3 1 1 3 the use of historical periods was explained Including an old investment in the investment table does not require including historical periods in the calculation However if historical periods are not used and no opening balance sheet items have been entered there will be a difference in the balance sheet 3 2 4 2 7 Continue old depreciation plan Enter purchase price and time Depreciation will be calculated using these parameters It is not necessary to include historical periods in the calculation for this However if a complete starting balance is not entered in a historical period there will be an unaddressed difference in the ba lance sheet Depreciation
243. tment files xI v The appearance of the dialogue box differs to some extent depending on the operating system and version of Excel used Note that if you use Microsoft Excel s Open function you will be prompted to activate macros You can open a file from any drive and folder When you start Invest for Excel it activates the default folder you have specified If you do not choose to save the files in another folder Invest for Excel automatically saves all calculation files in this folder You can also open other Excel files with this function 2 5 Close The Close function of the File menu will close the active Excel workbook as the same function in Excel would do If you have made changes to the file after last saving it the A program asks you Do you want to save the changes you made to file name ose 2 6 Save file or Save Snapshot The operating system may limit the use of certain symbols or To save the file left click the File menu then select Save or Save as or Save To Zip characters in the file name Save When you save a file by choosing Save in the File menu or by clicking on the floppy icon in the toolbar on top the file is saved in the active folder The file will retain the name it had when you opened it or given to a new file when created It is safer to select the Save as function which allows you to specify folder and file name to use The appearance of the Save As dialog b
244. tment will have positive tax effects a reduction in taxable income on a company level which can be taken into account in the calculation by choosing this option 3 1 2 4 1 2 Include tax effects of financing items in discounted cash flow By default this option is off if any financing items have been included in Income statement the income tax effect of the financing items is included in the Income statement but not in the discounted cash flow When this option is activated the income tax effects of financing items are also included in discounted free cash flow and therefore affect NPV This is an alternative to taking financial 11 17 2014 Copyright Datapartner Oy Page 45 Invest for Excel structure into account in the discount factor This option should NOT be activated when WACC is used as discount factor 3 1 2 4 1 3 Goodwill depreciation is tax deductible When this option is activated goodwill deprecation is tax deductible 3 1 2 4 2 Enter income tax manually When this option is activated income tax is entered manually The income tax row cells are unlocked and formulas are cleared The Income tax row in the cash flow statement by default holds a reference to the income tax row in the Income statement 3 1 2 4 3 Impairment test and income tax IFRS recommends that an impairment test should be done before tax with a pretax discount factor This is default in Invest for Excel option Calculate Value in use after ta
245. to replace any existing files 2 6 1 Working simultaneously on several calculations Invest for Excel supports simultaneous handling or processing of several calculation files When you change from one function to another for example via File gt Go To gt 1 Calculation file the program displays a list of all files related to that function In order to choose the file you want just click on the appropriate name The situation could look as follows File input Result Analyse Format Other iN Ya t e Horne Folders And Explore folder New Open Seve Print Close Shweboir Gi bit Excel of activ o o a wren Templates ol active file Home Foden 4 Investment file e ile 2 Companion ft 66 fi G 3 Proposal file 4 Financing fe SA 3 D tepatemnent Test veificalon lor a r Go To Calculation file Invfile 1 New flight route 37f xlsm E The same function applies to other type of files you can work with several files at a time 11 17 2014 Copyright Datapartner Oy Page 29 Invest for Excel 2 7 Save Snapshot Save Snapshot creates a light file size about of original size copy of active calculation file The Invest for Excel formulas are deleted the cells contain only values and all cells are locked Buttons and program macros are also left out Date and time YYYY MM DD_HH MM SS is added to file name NO CHANGES ARE MADE TO THE ORIGINAL CALCULATION FILE Example uses e Thanks to the fact
246. torical balances in the left hand columns The last history period balance acts as the opening balance for the investment calculation The figures belonging to this group which have been entered in the Investments table will be shown by default 9 1 1 Intangible assets There are four asset types under this heading Immaterial rights Capitalized development costs Goodwill and Other intangible assets For each fixed asset type there are three rows The first row ending in specified shows asset investments specified in the investment table The second row shows the book value entered in historical period after depreciation The third row shows the depreciation of the historical book value You can choose between three depreciation methods straight line declining balance or enter manually Choose from the drop down menu Enter the depreciation percentage in the grey cell to the right of the drop down menu if straight line or declining balance deprecation 9 1 2 Tangible assets There are four asset types under this heading Machinery and equipment Buildings and structures Land and water and Other tangible assets For each fixed asset type there are three rows The first row ending in specified shows asset investments specified in the investment table The second row shows the book value entered in historical period after depreciation The third row shows the depreciation of the historical book value You can choose between
247. u want to include them in the budget Loss of margins Here you can specify the disruption of other activity due to taking the investment in use Please note that loss of margin should also be taken into account in the investment calculation The figures are copied from the investment calculation Discount rate The rate of return requirement entered under Basic Values discount factor Average operating margin per annum The program calculates the ave rage EBITDA operating income before depreciation per year on the basis of the margin per period Internal Rate of Return ls abbreviated IRR It is a value taken from the Result screen representing annual income from the investment as a percentage NPV excluding residual value NPV PV of residual value NPV including residual value NPV from Profitability analysis Payback time years Payback time with the discount rate of interest taken into account Shows how long it takes before the net cash flow as discounted to the present time with the discount rate is positive If you want to know the payback time without interest change the discount rate to zero 0 in the Basic Values screen Economic life Often the same as the investment term entered in the Investment Term dialogue box If the calculation includes construction time the investment calculation term is longer than the economic life of the investment object The economic life of the investment object can howe
248. ulation files DE Neu Flugroute 37 xlsm Comparison files Inveomp new flight route scenari Investment proposals _ Financing files lt Not open gt Impairment Test Verification lt Not open gt If several files are open at the same time the program lists them all Select the file s of which you want to change the language The file must be open for this function to work Changing the language will not affect Excel s own dialog boxes 11 17 2014 Copyright DatapartnerOy Page185 Invest for Excel 7 3 Calculation Calculation Iteration 8 Automatic Maximum iterations 100 O Automatic except data tables Maximum change O Manual Recalculate before save Sheet options Update remote references C Precision as displayed 1904 date system Save external link values Transition formula evaluation Transition formula entry Calc Now F9 Calc Sheet In the Calculation Options dialog box you can adjust Invest for Excefs manner of calculating In practice you are unlikely to need to change the default settings One reason might be that you have devised formulas for calculations requiring iteration Being an Excel function it will work in the same language as your Excel For more information refer to Excel s Help function Open the Calculation dialog box from Invest menu Other Calculation Calculation Excel Page 186 Copyright Datapartner Oy 11 17 2014 Invest
249. ulation table 5 Choose OK in the Goal Seek window 62003 6 2003 Fi2003 Set cell To value By changing cell Goal Seek Status Goal Seeking with Cell 1235 Found a solution Target value 10000 Current value 10 000 The result will be shown in the Goal Seek Status window and in the calculation Page 210 Copyright Datapartner Oy 11 17 2014 Invest for Excel 13 The Financing module Enterprise edition Project Currency InvSpec OiParam OlSpec Oiview O2Param 025pec f O2View TotalSpec TotalView 13 1 General The Financing file InvFin consists initially of the following sheets Project general information and synchronization between investment calculation file InvFile and the financing file Currency currency rates for synchronization between investment calculation and project financing InvSpec will include cash flows from calculation file once you have updated the numbers with the exclamation mark button in Project sheet 01Param here define the parameters of loan 1 This is the main input sheet 01Spec in this sheet you will see all details of loan1 payment schedule 01View is a report of the loan payment schedule and other financing costs TotalSpec shows all specified loans payment schedules consolidated in detail TotalView is a report showing all loan payment schedules aggregated BBS IE IEDR lt select where youwanttomove gt v Add financing On the top th
250. unction in several languages WACCENG BMP WACCGER BMP WACC calculation charts WACCSWE BMP WACCFIN BMP WACCPOL BMP WACCSPA BMP IRibEn01 XLAM IRibEn02 XLAM Program menus IRibFi01 XLAM IRibFi02 XLAM IRi0Ge01 XLAM IRibGe02 XLAM IRibP001 XLAM IRibPo02 XLAM IRibRu01 XLAM IRibRu02 XLAM IRibSp01 XLAM IRibSp02 XLAM IRibSw01 XLAM IRibSw02 XLAM Invest for Excel automatically creates a default directory called CALC in which calculation files can be saved If the software is running in a network the CALC folder must have both read and write rights 1 6 Entering Data As a general rule negative cash flows should be entered as negative figures and positive cash flows as positive figures Consequently enter costs and investments as negative figures and profits or realisations sale of investment objects as positive figures You should avoid using the Cut amp Paste function Use Copy and Paste command instead When calculating the profitability of an investment try to estimate the marginal effects of the in vestment unless you use the Marginal effect function It is equally important to remember not to let the expected result influence the values you enter when making investment calculations Do not change data only to get a positive net present value NPV etc always be objective when entering numbers Page 14 Copyright Datapartner Oy 11 17 2014 Invest for Excel 1 7 Working Order When starting
251. ver exceed the investment term The investment s cost of acquisition and EBTDA 10 from the starting point Investment capital Proposed investments The first column shows the sum total of the investment as added up in the investment calculation Look under Profitability which internal rate of return applies for the base case The total investment has been increased by 10 in the second column The third column shows the corresponding internal rate of return The acquisition cost is 10 lower in the fourth column and The fifth column shows the corresponding internal rate of return EBITDA The first column shows the average EBITDA operating income before depreciation per year as obtained from the Investment calculation Look under Profitability which internal rate of return applies for the base case The operating profit is 10 lower in the second column The third column shows the corresponding internal rate of return In the fourth column the operating profit is 10 better and Copyright Datapartner Oy Page 157 Invest for Excel 5 The fifth column shows the corresponding internal rate of return Break even Point Investment capital This is how much the investment may cost at a maximum without the required returns having to be reduced You also see the margin of change from the planned value to Break Even point as a figure and a percentage Operating Margin The lowest the average EBITDA per year the
252. vestments Subventions Proposed Reinvestments Residual value Proposed C Reinvestments fv Automatically calculate realization value at end of calculation term A shift factor of 1 1 25 or 1 5 can be used to consider wearing of the asset in shift use These factors are used in Germany When shift factor 1 is used the depreciation is not altered 3 2 4 2 1 Investment expenditure allocated Subsequent investment expenditures can be depreciated as one investment using the Allocate expenditure option Allocate expenditure subsequent investments are summed and depreciated as one 11 17 2014 Copyright Datapartner Oy Page 57 Invest for Excel Example of a 2 000 000 investment depreciated starting 12 2005 in a traditional straight line matter and allocated as one investment expenditure INVESTMENTS REALIZATIONS EJE PES TE 1 2005 zanir 12 2006 42 2007 Months per interval H E a 1a 1 Straight line 500 000 1 4100 000 Depreciation straight line 40 00 O00 200 O00 Book value FO 000 2 Straight line allocated 500 000 1 400 000 Depreciation straight line 200 200 200 Book value The allocated investment expenditures 2 are depreciated as a whole from the first depreciation whereas traditional investment expenditures 1 depreciations accrue with the book value 3 2 4 2 2 Basis for depreciation In most cases the basis for depreciation is 100 of the purchase price In some
253. vestments are typically maintenance investments Proposed investments are shown with blue background Investment table 1 New investments 254 68 345 Depreciation straight line 20 005 64 529 64 529 64 529 64 529 Book value 2 Subsidy Depreciation straight line Book value 3 Reinvestments 50 Depreciation straight line Book value 10 000 Profitability analysis Result sheet Page 62 Copyright Datapartner Oy 11 17 2014 Invest for Excel Present value of reinvestments 110 110 Total Present Value PV 456 404 Nominal PY 322 645 322 645 Investment subventions 50 860 50 860 Investment proposal 271 785 271 785 Net Present Value NPV 184 619 3 2 4 2 14 Residual value Book value at the end of the calculation term is automatically realized sold when automatic residual calculation is checked This gives a cash flow effect of the Residual book value of asset This option is only available if the residual column is used Residual value i Automatically calculate realization value at end of calculation term INVESTMENTS REALIZATIONS PES 1 2005 12 2005 Residual Months per interval Depr Ye ee ee 1 20 00 Depreciation straight line 20 Book value 100 EN Now the object is liquidated at book value If you remove the tick unchecked Residual value Automatically calculate realization value at end of calculation term INVE
254. x is off Income Tax Options General Impairment Test Use this option to calculate Value in use after tax with an after tax WACC and iterate a pretax WACC that will give a corresponding result This is an alternative to calculating Value in use with a pretax WACC Sometimes it is easier or more accurate to do an impairment test after tax with an after tax WACC and then iterate a pre tax WACC that will give a corresponding result This can be done by activating this option Page 46 Copyright Datapartner Oy 11 17 2014 Invest for Excel 3 1 2 5 File locking This function is only available in Pro and Enterprise editions You can protect the calculation file from being altered by pressing the lock button You can choose if you want to lock the whole file or some parts of it 3 1 2 5 1 Partial locking When you choose Partial locking you can choose the parameters that should be locked This is useful when you use a template with pre entered data and or settings that should not be altered A password can be applied for the Partial locking options NOTE The password is case sensi tive Password Basic Values Calculations Result Check the functions that should be LOCKED Calculation point relative to starting period Residual value calculation Figures 1 1000 1000000 If you enter a password the program asks you to confirm it If you try to use a protected function the following
255. xcel 3 2 5 5 2 Change indicators In the Distribution dialog box you can choose to include Change indicators under the selected row SCAT ies INCOME STATEMENT faa EUR Ala 12 2015 12 2016 12 2017 12 2018 12 2019 Months per interval 12 Staff costs oy Other variable costs 2932 01 1030806 1093170 1159307 1278 135 ui Copy Distribute Copy Distribute options i py O OOO Oo Last period to Fixed costs MEA A a Staff costs Copy Formula in active cell 9 Distribute Value of active cell Adjust with number of 003 months per i interval Other fixed costs pc iene m Provisions increase decrease w el annual change in cell D467 Dis te as Values 9 Formulas EBIT Operating income a ae Copy cell formatting to target cells Financing income and expenses Financing income and expenses E Financing income and expenses Financing file EBT Income after financing items Chosen Change indicators are written to the sheet La al INCOME STATEMENT faa EUR Sf Ga 12 2015 12 2016 12 2017 12 2018 12 2019 Months per interval 12 12 Staff costs Other variable costs 972 000 1 030 806 1093 170 1 159 307 1 278 135 Change yearly 5 0 5 0 5 0 5 0 Index base year 100 100 00 105 110 116 122 Change yearly shows the change per year of the parent row Index base year 100 shows the cumulative change as
256. you are using The table or tables are appended to your investment calculation and any changes in them are relayed to the actual Invest for Excel tables through links Insert Worksheet Page 182 Copyright Datapartner Oy 11 17 2014 Invest for Excel For example you can separate and calculate the total of fixed costs in a table like this When you have established a link e g between the Income statement and the separate table of fixed costs all changes in the fixed costs are also relayed to the Invest for Excel calculations Note that you can rename move and delete an inserted sheet like any Excel sheet An optional IFRS reports sheet can be added to a calculation file in the Enterprise edition of Invest for Excel See chapter 11 2 below for details Add remove IFRS reports sheet 11 17 2014 Copyright O Datapartner Oy Page 183 Invest for Excel 7 Other 7 1 Home Screen We call the start up window of the Invest for Excel program the Home Screen The Home Screen functions also as a menu from which you can go to almost every part of the Invest for Excel program by clicking the Home Screen buttons ENTERPRISE D DataPartner N V e st FOR EXCEL Program version 3 7 _ Basic values _ Investment Profitability analysis C Discount factor C Contact information _ Income statement C Comparison table _ Total investment LJ Working capital LL Marginal effect C Income C Folders and files C Cash flow

Invest for Excel User Manual version 3.4

Contents

Download Pdf Manuals

Related Search

Related Contents