Hi, I need urgent help with this financial forecast project. I did the Income statement and the Balance sheet but got stuck while doing the Cashflow statements. I don't know how to do this part. Please help me complete the Cashflow statements and schedules. Also, if you could upload an excel file so I can see the formulas. If not that's ok too. Thanks much!

X --= 5741 Financial Forecast Project - Microsoft Excel Planning & Consolidation File Home Insert Page Layout Formulas Data Review View 3 Calibri 11 - Wrap Text General % Cut E Copy Paste Format Painter Clipboard BIU Merge & Center $ % % 4.0 .00 .00.0 In: Conditional Format Cell Formatting as Table Styles Styles Alignment Number Font for M14 1 1 31 12 21 E A B D F G H 1 J K L 1 US Dollars, Millions 2 3 -2 +1 +5 4 5 I 6 Historical -1 6.2% 55.8% 7.8% 5.2% 7.1% 5.5% 42.9% 51.7% 6.2% 4.1% 6.9% 8.6% 35.7% 7 8% 58% 12% 8% 7% 6% 40% Forecast +3 8% 56% 12% 8% 7% 6% 40% Base Year 9.7% 58.6% 10.1% 8.3% 10.1% 5.0% 40.0% 25.0 +4 8% 55% 12% 8% 7% 6% 40% +2 8% 57% 12% 8% 7% 7 6% 40% 25 8% 54% 12% 8% 7% 6% 40% I 8 9 .. ::.: : : : : : : : : Performance Drivers / Assumptions Year % Growth in revenues COGS % of revenue SG&A % of revenue R&D % of revenue D&A as % Revenue Effective interest rate Taxes Days Sales Outstanding Days Inventory Outstanding Days Payable Outstanding CapEx as % Revenue Working capital (excluding cash) Debt repaid Dividend payout ratio Min Required cash balance Days in a year 10 11 18.7 25 25 25 14.9 9.9 12 20 20 20 20 13 12.0 12.6 8.4 3% 9.0 10 25 20 10 7% 16 20.0 10.0 2% 14.5 10 7% 17 10 7% 14 7% 10 7% 21 15 7.5 18 19 16 2 50% 2 50% 2 2 50% 2 50% 17 2 50% $ 5.00 360 18 19 20 Income Statements +4 +5 Historical -2 -1 Base Year 145.0 $ 154.0 $ 169.0 75.0 86.0 99.0 70.0 68.0 70.0 9.0 12.0 17.0 6.0 8.0 14.0 +1 $ 182.5 105.9 $ 229.9 126.5 $ 248.3 134.1 114.2 76.7 103.5 21.9 27.6 29.8 14.6 18.4 19.9 Year Revenues $ COGS Gross profits SG&A R&D EBITDA Depreciation & amortization EBIT Interest expenses EBT Taxes Net income Forecast +2 +3 $ 197.1 $ 212.9 112.4 119.2 84.8 93.7 23.7 25.5 15.8 17.0 45.3 51.1 13.8 14.9 31.5 36.2 2.6 2.6 28.9 33.6 11.6 13.4 17.3 20.1 55.0 39.0 40.2 57.5 64.6 48.0 11.0 10.0 16.1 17.4 17.0 22.0 45.0 37.0 41.4 47.2 3.0 2.0 2.0 2.6 12.8 27.4 2.5 24.9 9.9 14.9 42.0 35.0 20.0 8.0 2.6 38.7 15.5 15.0 15.0 44.5 17.8 26.7 27.0 20.0 12.0 23.2 Balance Sheets Assets +1 +5 Historical -2 -1 3.0 $ 2.0 $ 6.0 $ 8.0 $ 4.0 3.0 Base 1.0 5.0 $ $ Cash $ Accounts receivable $ Inventory Current assets 11.7 Forecast +2 +3 5.0 $ 5.0 $ 13.7 $ 14.8 $ 6.2. 6.6 24.9 26.4 5.0 $ 12.7 $ 5.9 +4 5.0 $ 16.0 $ 7.0 28.0 17.2 5.5 7.4 13.0 13.0 18.2 23.6 29.7 35.0 44.0 56.8 70.6 85.5 101.6 119.0 40.0 14.0 12.0 13.8 17.4 12.8 44.0 18.0 26.0 40.0 Gross PPE Accumulated depreciation Net PPE Good will Other assets Total assets 16.1 85.5 101.6 14.9 70.6 40.0 26.0 40.0 18.0 56.8 40.0 40.0 23.0 32.0 16.0 84.0 40.0 18.0 18.0 18.0 18.0 102.2 18.0 171.5 40.0 18.0 189.3 97.0 125.6 139.7 155.0 Liabilities 2.5 2.0 2.8 2.9 3.1 3.3 3.5 3.7 Accounts payable Current liabilities 2.5 2.0 2.8 2.9 3.1 3.3 3.5 3.7 35.0 36.5 40.0 44.0 44.0 44.0 44.0 57.7 10.0 12.0 33.8 45.2 44.0 71.1 118.6 15.0 57.8 85.4 47.5 50.5 80.7 92.3 105.0 133.1 Long term debt Provisions and other liabilities Total liabilities Shareholders equity Common stock Retained earnings Total shareholders equity 25.0 30.0 30.0 30.0 30.0 30.0 30.0 11.5 16.5 14.5 14.9 30.0 17.3 47.3 20.1 23.2 26.7 36.5 46.5 44.5 44.9 50.1 53.2 56.7 84.0 97.0 102.2 125.7 139.7 155.1 171.9 189.8 Total liabilities & shareholders equity BALANCE CHECK 0.00 Forecast +3 -2 -1 Base Year +1 +2 +4 +5 20.0 14.9 23.2 27.0 10.0 12.0 17.0 17.3 13.8 20.1 14.9 26.7 17.4 11.0 12.8 16.1 Cashflow Statements Year Operating Activities Net income Add depreciation & amortization Changes in operating assets/liabilities: Increase in accounts receivable Increase in inventory Increase in accounts payable Cash flow from operations Investing Activities Investments in PPE (CAPEX) Sale of PPE Investments in other assets Sale of other assets Cash flow from investment activities Financing Activities Issue of equity Equity retirement or treasury buybacks Dividends paid New debt / borrowings Retirement or repayment of debt Cash flow from financing activities Net cash flow for the year Beginning cash balance Ending cash balance 11.0 +1 +2 Forecast +3 5.0 +4 +5 1.0 5.0 5.0 Debt & Dividend Payment Schedule Year Cash balance before debt & dividends Debt drawn to meet min balance Debt repaid Dividends paid Ending cash balance $ 6.0 2.0 2.0 2.0 2.0 2.0 5.0 2.0 2.0 2.0 2.0 3.0 $ 2.0 $ 1.0 5.0 5.0 5.0 5.0 5.0 Forecast Debt Schedules Long term debt +1 +2 +3 +4 +5 44.0 44.0 44.0 40.0 6.0 44.0 2.0 2.0 2.0 2.0 Opening balance Debt added Debt repaid Ending balance Interest 2.0 2.0 2.0 2.0 2.0 35.0 36.5 40.0 44.0 44.0 44.0 2.5 44.0 2.6 44.0 2.6 2.6 2.6 X --= 5741 Financial Forecast Project - Microsoft Excel Planning & Consolidation File Home Insert Page Layout Formulas Data Review View 3 Calibri 11 - Wrap Text General % Cut E Copy Paste Format Painter Clipboard BIU Merge & Center $ % % 4.0 .00 .00.0 In: Conditional Format Cell Formatting as Table Styles Styles Alignment Number Font for M14 1 1 31 12 21 E A B D F G H 1 J K L 1 US Dollars, Millions 2 3 -2 +1 +5 4 5 I 6 Historical -1 6.2% 55.8% 7.8% 5.2% 7.1% 5.5% 42.9% 51.7% 6.2% 4.1% 6.9% 8.6% 35.7% 7 8% 58% 12% 8% 7% 6% 40% Forecast +3 8% 56% 12% 8% 7% 6% 40% Base Year 9.7% 58.6% 10.1% 8.3% 10.1% 5.0% 40.0% 25.0 +4 8% 55% 12% 8% 7% 6% 40% +2 8% 57% 12% 8% 7% 7 6% 40% 25 8% 54% 12% 8% 7% 6% 40% I 8 9 .. ::.: : : : : : : : : Performance Drivers / Assumptions Year % Growth in revenues COGS % of revenue SG&A % of revenue R&D % of revenue D&A as % Revenue Effective interest rate Taxes Days Sales Outstanding Days Inventory Outstanding Days Payable Outstanding CapEx as % Revenue Working capital (excluding cash) Debt repaid Dividend payout ratio Min Required cash balance Days in a year 10 11 18.7 25 25 25 14.9 9.9 12 20 20 20 20 13 12.0 12.6 8.4 3% 9.0 10 25 20 10 7% 16 20.0 10.0 2% 14.5 10 7% 17 10 7% 14 7% 10 7% 21 15 7.5 18 19 16 2 50% 2 50% 2 2 50% 2 50% 17 2 50% $ 5.00 360 18 19 20 Income Statements +4 +5 Historical -2 -1 Base Year 145.0 $ 154.0 $ 169.0 75.0 86.0 99.0 70.0 68.0 70.0 9.0 12.0 17.0 6.0 8.0 14.0 +1 $ 182.5 105.9 $ 229.9 126.5 $ 248.3 134.1 114.2 76.7 103.5 21.9 27.6 29.8 14.6 18.4 19.9 Year Revenues $ COGS Gross profits SG&A R&D EBITDA Depreciation & amortization EBIT Interest expenses EBT Taxes Net income Forecast +2 +3 $ 197.1 $ 212.9 112.4 119.2 84.8 93.7 23.7 25.5 15.8 17.0 45.3 51.1 13.8 14.9 31.5 36.2 2.6 2.6 28.9 33.6 11.6 13.4 17.3 20.1 55.0 39.0 40.2 57.5 64.6 48.0 11.0 10.0 16.1 17.4 17.0 22.0 45.0 37.0 41.4 47.2 3.0 2.0 2.0 2.6 12.8 27.4 2.5 24.9 9.9 14.9 42.0 35.0 20.0 8.0 2.6 38.7 15.5 15.0 15.0 44.5 17.8 26.7 27.0 20.0 12.0 23.2 Balance Sheets Assets +1 +5 Historical -2 -1 3.0 $ 2.0 $ 6.0 $ 8.0 $ 4.0 3.0 Base 1.0 5.0 $ $ Cash $ Accounts receivable $ Inventory Current assets 11.7 Forecast +2 +3 5.0 $ 5.0 $ 13.7 $ 14.8 $ 6.2. 6.6 24.9 26.4 5.0 $ 12.7 $ 5.9 +4 5.0 $ 16.0 $ 7.0 28.0 17.2 5.5 7.4 13.0 13.0 18.2 23.6 29.7 35.0 44.0 56.8 70.6 85.5 101.6 119.0 40.0 14.0 12.0 13.8 17.4 12.8 44.0 18.0 26.0 40.0 Gross PPE Accumulated depreciation Net PPE Good will Other assets Total assets 16.1 85.5 101.6 14.9 70.6 40.0 26.0 40.0 18.0 56.8 40.0 40.0 23.0 32.0 16.0 84.0 40.0 18.0 18.0 18.0 18.0 102.2 18.0 171.5 40.0 18.0 189.3 97.0 125.6 139.7 155.0 Liabilities 2.5 2.0 2.8 2.9 3.1 3.3 3.5 3.7 Accounts payable Current liabilities 2.5 2.0 2.8 2.9 3.1 3.3 3.5 3.7 35.0 36.5 40.0 44.0 44.0 44.0 44.0 57.7 10.0 12.0 33.8 45.2 44.0 71.1 118.6 15.0 57.8 85.4 47.5 50.5 80.7 92.3 105.0 133.1 Long term debt Provisions and other liabilities Total liabilities Shareholders equity Common stock Retained earnings Total shareholders equity 25.0 30.0 30.0 30.0 30.0 30.0 30.0 11.5 16.5 14.5 14.9 30.0 17.3 47.3 20.1 23.2 26.7 36.5 46.5 44.5 44.9 50.1 53.2 56.7 84.0 97.0 102.2 125.7 139.7 155.1 171.9 189.8 Total liabilities & shareholders equity BALANCE CHECK 0.00 Forecast +3 -2 -1 Base Year +1 +2 +4 +5 20.0 14.9 23.2 27.0 10.0 12.0 17.0 17.3 13.8 20.1 14.9 26.7 17.4 11.0 12.8 16.1 Cashflow Statements Year Operating Activities Net income Add depreciation & amortization Changes in operating assets/liabilities: Increase in accounts receivable Increase in inventory Increase in accounts payable Cash flow from operations Investing Activities Investments in PPE (CAPEX) Sale of PPE Investments in other assets Sale of other assets Cash flow from investment activities Financing Activities Issue of equity Equity retirement or treasury buybacks Dividends paid New debt / borrowings Retirement or repayment of debt Cash flow from financing activities Net cash flow for the year Beginning cash balance Ending cash balance 11.0 +1 +2 Forecast +3 5.0 +4 +5 1.0 5.0 5.0 Debt & Dividend Payment Schedule Year Cash balance before debt & dividends Debt drawn to meet min balance Debt repaid Dividends paid Ending cash balance $ 6.0 2.0 2.0 2.0 2.0 2.0 5.0 2.0 2.0 2.0 2.0 3.0 $ 2.0 $ 1.0 5.0 5.0 5.0 5.0 5.0 Forecast Debt Schedules Long term debt +1 +2 +3 +4 +5 44.0 44.0 44.0 40.0 6.0 44.0 2.0 2.0 2.0 2.0 Opening balance Debt added Debt repaid Ending balance Interest 2.0 2.0 2.0 2.0 2.0 35.0 36.5 40.0 44.0 44.0 44.0 2.5 44.0 2.6 44.0 2.6 2.6 2.6