Hi I need help with the Cashflow statement and the last 2 schedules..When replying please provide the Excel with the formulas... Thank you!
US Dollars, Millions Performance Drivers / Assumpti Historical Forecast Year -2.00 -1.00 Base Year 1.00 2.00 3.00 4.00 5.00 % Growth in revenues 0.06 0.10 8% 8% 8% 8% 8% CoGS % of revenue 0.52 0.56 0.59 58% 57% 56% 55% 54% SG&A % of revenue 0.06 0.08 0.10 12% 12% 12% 12% 12% R&D % of revenue 0.04 0.05 0.08 8% 8% 8% 8% 8% D&A as % Revenue 0.07 0.07 0.10 7% 7% 7% 7% 7% Effective interest rate 0.09 0.05 0.05 6% 6% 6% 6% 6% Taxes 0.36 0.43 0.40 40% 40% 40% 40% 40% Days Sales Outstanding 14.90 18.70 25.00 25.00 25.00 25.00 25.00 25.00 Days Inventory Outstanding 9.93 12.56 20.00 20.00 20.00 20.00 20.00 20.00 Days Payable Outstanding 12.00 8.37 10.00 10.00 10.00 10.00 10.00 10.00 CapEx as % Revenue 0.03 0.02 7% 7% 7% 7% 7% Working capital (excluding cash) 7.50 9.00 14.49 16.81 18.10 19.48 20.97 Debt repaid 2.00 2.00 2.00 2.00 2.00 Dividend payout ratio 50% 50% 50% 50% 50% Min Required cash balance 5.00 Days in a year 360.00Income Statements Historical Forecast Year -2.00 -1.00 Base Year 1.00 2.00 3.00 4.00 5.00 Revenues 145.00 154.00 169.00 182.5 197.1 212.9 229.9 COGS 248.3 75.00 86.00 99.00 105.9 112.4 119.2 126.5 Gross profits 134.1 70.00 68.00 70.00 76.7 84.8 93.7 103.5 SG&A 114.2 9.00 12.00 17.00 21.9 23.7 25.5 27.6 29.8 R&D 6.00 8.00 14.00 14.6 15.8 17.0 18.4 19.9 EBITDA 55.00 48.00 39.00 40.2 45.3 51.1 Depreciation & amortization 57.5 64.6 10.00 11.00 17.00 12.8 13.8 14.9 16.1 EBIT 45.00 17.4 37.00 22.00 27.4 31.5 36.2 41.4 47.2 Interest expenses 3.00 2.00 2.00 2.4 2.2' 2.0 1.9 EBT 1.8 42.00 35.00 20.00 25.0 29.4 34.2 39.5 45.4 Taxes 15.00 15.00 8.00 10.0 11.8 13.7 15.8 Net income 18.2 27.00 20.00 12.00 15.0 17.6 20.5 23.7 27.2Balance Sheets Historical Assets Forecast -2.00 -1.00 Base 1.00 2.00 3.00 Cash 4.00 3.00 2.00 5.00 1.00 12.4 7 10.0 Accounts receivable 8.2 6.00 10.8 8.00 11.0 11.74 12.7 13.7 14.8 Inventory 16.0 4.00 3.00 17.2 5.50 5.9 6.2 Current assets 13.00 6.6 7.0 13.00 7.4 18.24 30.9 29.9 29.6 33.8 35.7 Gross PPE 35.00 40.00 44.00 56.8 Accumulated depreciation 85.5 101.6 14.00 119.0 18.00 7 70.6 12.00 19.8 25.5 Net PPE 23.00 31.8 38.5 26.00 45.7 26.00 37.0 45.0 Good will 53.7 32.00 63.1 40.00 73.2 40.00 40.0 40.0 40.0 Other assets 40.0 16.00 18.00 40.0 18.00 0.0 0.0 Total assets 84.00 0.0 0.0 97.00 0.0 0.00 107.9 114.9 123.4 136.9 148.9 Liabilities Accounts payable 2.50 2.00 2.75 2.9 Current liabilities 3.1 3.3 2.50 3.5 2.00 3.7 2.75 2.9 3.1 3.3 3.5 3.7 Long term debt 35.00 36.50 40.00 F 38.0 36.0 Provisions and other liabilities 34.0 32.0' 10.00 12.00 30.0 15.00 15.0 Total liabilities 15.0 47.50 15.0 50.50 15.0 15.0 57.75 55.9 54.1 52.3 54.0 52.4Shareholders equity Common stock 25.00 30.00 30.00 30.0 30.0 30.0 30.0 30.0 Retained earnings 11.50 16.50 14.49 22.0 30.8 41.0 52.9 Total shareholders equity 66.5 36.50 46.50 44.49 52.0 60.8 71.0 82.9 96.5 Total liabilities & shareholders equity 84.00 97.00 102.24 107.9 114.9 123.4* BALANCE CHECK 136.9 148.9 0.00 0.00 0.00Cashflow Statements Year -2.00 -1.00 Forecast Operating Activities Base Year 1.00 2.00 3.00 4.00 5.00 Net income Add depreciation & amortization 15.0* 17.6 20.5* 23.7 27.2 Changes in operating assets/liabilities: 19.8 25.5 31.8 38.5 45.7 Increase in accounts receivable Increase in inventory -1.0 -1.0 -1.1 -1.2 -1.3 Increase in accounts payable -0.4 -1.0 -0.4 -0.4 -0.4 Cash flow from operations 0.2 0.2 0.2 0.2 0.2 Investing Activities 33.6 41.3 51.0 60.8 71.4 Investments in PPE (CAPEX) Sale of PPE 12.8 13.8 14.9 16.1 17.4 Investments in other assets 0.0 0.0 0.0 0.0 0.0 Sale of other assets 0.0 0.0 0.0 0.0 0.0 0.0 Cash flow from investment activities 0.0 0.0 0.0 0.0 -12.8 -13.8 -14.9 -16.1 -17.4 Financing Activities Issue of equity Equity retirement or treasury buybacks 0.00 0.00 0.00 0.00 0.00 Dividends paid 0.00 0.00 0.00 0.00 New debt / borrowings -7.5 0.00 -8.8 -10.2 -11.8 -13.6 Retirement or repayment of debt 0.00 0.00 0.00 0.00 0.00 Cash flow from financing activities -2.00 -2.00 -2.00 -2.00 -9.5 -2.00 -10.8 -12.2 -13.8 -15.6 Net cash flow for the year Beginning cash balance 11.3 16.7 23.8 31.6 38.4 Ending cash balance 1.0 5.3 10.3 17.2 5.3 26.5 10.3 17.2 26.5 36.6Debt & Dividend Payment Schedule Forecast Year 1.00 2.00 3.00 4.00 5.00 Cash balance before debt & dividends 1.00 5.00 -737.86 -1513.25 -857.62 Debt drawn to meet min balance 6.00 0.00 0.00 1520.25 864.62 Debt repaid 2.00 2.00 2.00 2.00 2.00 Dividends paid 709.0 740.9 773.4 862.6 904.4 Ending cash balance 1.00 -704.05 -737.86 -1513.25 -857.62 -899.45 Debt Schedules Forecast Long term debt 1.00 2.00 3.00 4.00 5.00 Opening balance Debt added 7 40.00 44.00 42.00 40.00 1558.25 6.00 0.00 0.00 1520.25 864.62 Debt repaid 2.00 2.00 2.00 2.00 2.00 Ending balance 44.00 42.00 40.00 1558.25 2420.86 Interest 2.52 2.58 2.46 47.95 119.37
