Explanatory notes for Cash Flow analysis A preliminary financial analysis which indicates the potential economic gain from successful implementation of the proposed project should be made using the Cash Flow Analysis. For our purpose, we wish to know how the companies have analysed the financial exposure and potential return that they expect from the proposed project. What are the estimated investment cash flow and the projected earnings cash flow? Can the companies reasonably cope with the peak and aggregate investments that commercial success will entail? A cash flow analysis of the proposed project is required in order to generate from it 3 main economic indicators that are essential in assessing the economic viability of the project: The joint project's estimated Internal Rate of Return (IRR), Net Present Value (NPV) and Payback Period (PBP). Please include in your proposal both the cash flow analysis table (see below) and an analysis of the resulting values of the above three economic indicators. Set out hereunder are step-by-step instructions on how to prepare the cash flow analysis, with reference to the accompanying chart, illustrating a sample project (see below): NOTE: The cash flow analysis is an Excel spreadsheet and is available from VISTECH as an e-mail attachment or it can be downloaded from VISTECH’s website. The eight-year period used in the chart below is chosen to cover the development phase (1 to 3 years) and sales growth to a peak, followed by a drop in sales as the product becomes obsolete. It is important that the whole life-cycle of the product be considered, since the concept of liquidating the venture in the last year is used in calculating an Internal Rate of Return (IRR). Shortening of the period under review can lead to results which are quite misleading. However, a period of eight years should only be used if it is relevant for your particular case. NOTE: In the cash flow table below and in the spreadsheet, the mandatory input cells are highlighted in yellow. The cells highlighted in blue are input cells with default values which can be overridden. In the explanations below, lines requiring input data are underlined. Cash Flow Analysis: Sample Calculation No. Cash-Flow component Derivation N = No. of Years Y Calendar year Ist Calendar Year 2004 2005 2006 2007 2008 2009 2010 2011 Project year 1 2 3 4 5 6 7 8 Q No. of units sold (Units) estimate 6,000 7,000 9,000 10,000 8,000 5,000 P Product Price ($/unit) estimate 1,000 950 900 900 800 800 S Product Sales (K$) =QxP or estimate 0 6,000 6,650 8,100 9,000 6,400 4,000 M% Manufacturing Cost (% of sales) 30% 30% 30% 30% 30% 30% 30% 30% M Manufacturing Cost (K$) =M% x S 0 1,800 1,995 2,430 2,700 1,920 1,200 O% Operating Expenses (% of sales) 35% 35% 35% 35% 35% 35% 35% 35% O Operating Expenses (K$) =O% x S 0 2,100 2,328 2,835 3,150 2,240 1,400 D Development Expenses (K$) estimate 700 900 C Capital Expenses (K$) estimate 700 300 E Depreciation (K$) linear over 5 yrs. 0 140 200 200 200 200 60 0 I Before Tax Income/Loss (K$) =S-M-O- D-E (700) (1,040) 1,900 2,128 2,635 2,950 2,180 1,400 T1 Cumulative Losses carried over (K$) (700) (1,740) 0 0 0 0 0 0 T2 Taxable Income (K$) 0 0 160 2,128 2,635 2,950 2,180 1,400 T% Income Tax Rate (%) 32% 32% 32% 32% 32% 32% 32% 32% 32% T Income Tax (K$) =T% x T2 0 0 51 681 843 944 698 448 OF Operating Cash Flow (K$/Yr.) =I+E-T (700) (900) 2,049 1,647 1,992 2,206 1,542 952 W% Working Capital (% of sales change) 25% 25% 25% 25% 25% 25% 25% 25% W Working Capital Change (K$) =W% x (Sn- Sn-1) 0 1,500 163 363 225 (650) (600) V Residual Value of Assets 0 0 0 0 0 0 0 1,000 AF Total Annual Cash Flow (K$) =OF-C- W+V (700) (1,600) 249 1,484 1,629 1,981 2,192 2,552 CF Total Cumulative Cash Flow (K$) (700) (2,300) (2,051) (567) 1,062 3,043 5,236 7,788 R Annual Discount Rate (%) 15% DAF Annual Discounted Cash Flow (K$) (609) (1,210) 164 849 810 856 824 834 DCF Cumulative Discounted Cash Flow (K$) (609) (1,819) (1,655) (806) 4 860 1,684 2,519 IRR Inernal Rate of Return (%) 43% LINE Y: The calendar year corresponding to the first year of the project, which is the year in which the development will start under VISTECH support. LINE Q: Number of Units Sold – for each one of the years of commercial sales, enter an estimate of the number of units of products to be sold per year, using the expected market share and total available market over the product sales in life. (This should correspond with your estimate presented in Section D of the Proposal.) LINE P: Product Price – for each one of the years of commercial sales, enter the estimated product price, in $/unit, giving expression to the change in price over the years as a result of changes in the market environment (optional). (This price profile should correspond with your estimate presented in Section D of the Proposal.) LINE S: Product Sales – if an estimate has been given for the sales quantity (Line Q), and of the sales price (Line P), then Line S is a computed line. Optionally, the annual product sales can be inputted directly, in K$/Yr., by using either the value of new product or process substitution to prospective customers, the volume of similar products, or market share multiplied by market size. The estimate should be based on market study, research and experience. (This product sales projection should correspond with your estimate presented in Section D of the Proposal.) LINE M%: Manufacturing Cost – enter the estimated direct manufacturing cost of the product, as a % of the product sales (or of the sales price). It is usually between 30- 50% and is estimated using either the cost of similar products (processes), the development costs multiplied by an experience-based markup factor, or a detailed breakdown and manufacturing plan. You can enter either a fixed percentage for all years (in the "derivation" column), or enter a specific percentage for each year, expressing changes in manu-facturing efficiency or in costs of labor/material with the years (optional). LINE O%: Operating Cost – enter the estimated operating cost of the product as a % of the product sales (or of the sales price). It is usually between 20-35% and is estimated using historical ratios or detailed operating plans for the product (process). Continuing R&D, selling costs, advertising, general and administrative expenses, etc., are typical elements. You can enter either a fixed percentage for all years (in the "derivation" column), or enter a specific percentage for each year, expressing changes in operating costs as volumes change with the years (optional). LINE D: Development Expenses – include all development and start-up expenses for years 1-3 of the cash flow planning period (up to initial commercialization), in K$/Yr. Estimates should be based either on a comparison with similar developments or on a detailed product (process) development plan. NOTE : The total project budget should be included here, not just the portion to be financed by the companies. Likewise, repayments to VISTECH should be excluded from the cash flow analysis. LINE C: Capital Expenditures – include all investments in fixed capital, in K$/Yr., required to provide for the manufacturing and logistic functions during commercialization, including buildings, manufacturing equipment, vehicles, warehouses, etc. LINE E: Annual Depreciation – (A computed value, given in K$/Yr.) Computed assuming straight line depreciation of all the capital investment (Line C) over a 5 year period. The depreciation is not a cash- flow component and is included only as an allowance for income tax calculations. LINE I: Before Tax Income/Loss – (A computed value, given in K$/Yr.) Equals Line S minus Line M minus Line O minus Line D minus Line E. LINE T1: Cumulative losses carried over – (A computed value, given in K$) Equals the cumulative annual before tax losses (Line I) net of the cumulative annual before tax income (Line I), as long as this computed value is negative. LINE T2: Taxable Income – (A computed value, given in K$/Yr.) Equals the annual before-tax income (Line I) net of any cumulative losses carry-over (Line T1) and serves as the basis for the calculated income (corporate) tax. LINE T%: Income Tax Rate – enter the applicable income (corporate) tax rate, as a %. You can enter either a fixed percentage for all years (in the "derivation" column), or enter a specific percentage for each year, expressing changes in income (corporate) tax rates due to changes in approved enterprise status, or other expected changes in government policy (optional). NOTE : The tax rate for the company expected to be responsible for actual product commercialization should be used. Where both companies are expected to have major responsibilities for product sales, a weighted average tax rate should be used, reflecting the tax rate of each company in proportion to the total sales expected for each company. LINE T: Income Tax – (A computed value, given in K$/Yr.) Equals Line T2 multiplied by Line T%. LINE OF: Operating Cash Flow – (A computed value, given in K$/Yr.) Equals Line I minus Line T plus Line E. LINE W%: Working Capital Rate – the estimated annual working capital required to fund inventories and receivables (net of payables), reflecting growing sales. It is estimated as a function of the volume of product sales and expressed as a percentage applied to the changes (increase or decrease) in product sales. The working capital rate is assumed constant throughout the planning horizon and is usually assumed between 25-35%. Use your experience as a base or a detailed plan for the product (process), including carrying times and costs. LINE W: Changes in Working Capital – (A computed value, given in K$) Equals the working capital rate (Line W%) multiplied by the difference between the current year's sales and the prior year's sales (year N minus year N-1 in Line S). Note that this line is zero if sales do not increase and it is negative when sales decline. LINE V: Residual Value of Assets – (A computed value, given in K$) This computed value is needed only for calculating the IRR (see below). It equals the cumulative capital expenses (Line C) minus the cumulative depreciation (Line E), plus the cumulative working capital change (Line W) over the whole lifetime of the project. LINE AF: Total Annual Cash Flow – (A computed value, given in K$/Yr.) Equals Line OF minus Line C minus Line W in all but the last year. In the last year, the same formula is used but Line V is added. This has the effect of liquidating the venture in the last year and selling off all the assets on the books. LINE CF: Cumulative Cash Flow – equals the sum of Line AF cumulatively to date. In the early years of the project this will be negative. The year in which it becomes positive indicates the simple payback period (disregarding time-value of money), in years. LINE R: Annual Discount Rate – Enter the annual discount rate (interest rate, or hurdle rate) by which annual cash flows will be discounted, as a %. The discount rate is used to calculate the discount factor, and it is usually between 10-25%, depending on the technological and marketing uncertainty inherent in the project (the venture's risk). LINE DAF: Total Annual Discounted Cash Flow – (A computed value, given in K$/YR.) Equals the total annual cash flow of Line AF multiplied by the discount factor, for each year (e.g., for a discount rate of VISTECH Procedures Handbook 25%, Line AF in year I is multiplied by 0.80, Line AF in year 2 is multiplied by 0.64, Line AF in year 3 is multiplied by 0.51, etc.) LINE DCF: Total Cumulative Discounted Cash Flow – (A computed value, given in K$) Equals the cumulative value of Line DAF. The cumulative value at the end of the last year of the project (year 8 in the example) is known as the Net Present Value (NPV) of the project ($2,519K in the example) at a given discount rate (15% in the example). The NPV is one of the economic parameters that has to be presented in assessing the economic viability of the proposed project. The cumulative discounted cash flow will usually be negative in the first few years. The number of years in which the cumulative discounted cash flow (Line DCF) is negative (4 years in the example) is referred to as the Payback Period (PBP) and is also employed as an economic indicator of a project's risk. LINE IRR: Internal Rate of Return (IRR) – (A computed value given as a %) is the discount rate at which the Net Present Value is equal to zero and is given in the "derivation" column (43% in the example). It is an economic indicator of the project's overall return on investment potential.