Forcast Model

of 7

Please download to get full document.

View again

All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
PDF
7 pages
0 downs
10 views
Share
Description
1. Company: ABC Model: Forecast Financials and Performance Analysis Years: 5 Years Version: 1.0 Prepared By: Syed Muhammad Ali - ACCA Components: Dash Board Inputs F…
Transcript
  • 1. Company: ABC Model: Forecast Financials and Performance Analysis Years: 5 Years Version: 1.0 Prepared By: Syed Muhammad Ali - ACCA Components: Dash Board Inputs F Income Statement F Balance Sheet F Cash flow F Ratios Legends: INPUTS DROP DOWN LISTS LINKED INPUTS
  • 2. Main Menue DASH Board 00 01 02 03 04 Action Forecast Income Statement Sales 42,766.88 49,181.91 56,559.20 65,043.08 74,799.54 sales Growth (%) 15% 15 Net Profit 1,419.35 1,650.58 2,324.85 2,686.56 3,041.42 Cost of Goods Sold (%) 87% 87 Interest (%) 4% 4 Forecast balance Sheet Tax Rates (%) 20% 20 Cash and Deposit 5,830.46 5,749.62 7,423.33 5,528.73 6,573.21 Total Assets 26,354.37 29,356.19 34,570.88 43,252.72 49,955.80 Total Liabilities 21,120.39 22,131.86 25,451.64 32,521.54 37,399.77 Shareholder Funds 5,233.98 7,224.33 9,119.24 10,731.18 12,556.03 Forecast Cash Flow Earning before interest,tax,depreciation and amortization 2,394.95 3,196.82 3,676.35 4,227.80 4,861.97 Operating Cash Flow (7,711.34) 2,635.29 1,264.91 2,021.86 2,354.02 Performance Analysis Core Ratios Return on Sales (NPAT/Sales %) 2.76% 3.36% 4.11% 4.13% 4.07% OK Asset Turnover (Sales/Total Assets) 1.55 1.68 1.64 1.50 1.50 OK Asset leverage (Total Assets/Equity) 5.28 4.06 3.79 4.03 3.98 worse Return on Equity (NPAT/Equity %) 22.52% 22.85% 25.49% 25.04% 24.22% OK Profitability Gross Profit Margin (Gross Profit / sales %) 12.31% 13.00% 13.00% 13.00% 13.00% OK Net Operating Profit Margin (Net operating profit/Sales %) 3.23% 4.87% 5.46% 5.46% 5.46% OK Net Profit before tax Margin (Profit before tax / sales %) 2.76% 4.20% 5.14% 5.16% 5.08% OK Return on Capital employed (ROCE) 10.20% 16.40% 17.53% 17.32% 17.17% OK Return on Assets (ROA) 4.99% 8.16% 8.93% 8.21% 8.17% OK Operating Efficiency Inventory Days 3.07 2.37 2.37 2.96 2.96 Better Trade Receivalbe Days 55.09 42.25 42.25 52.81 52.81 OK Creditors Days 33.33 31.27 31.27 36.48 36.48 Worse Funding Gap 24.83 13.35 13.35 19.29 19.29 OK Financial Structure Current Ratio 1.58 1.72 1.77 1.67 1.68 Better Quick asset ratio 1.56 1.70 1.75 1.65 1.66 Better Working Capital 8,164 10,668 13,079 15,285 17,793 Better Gross Gearing (%) 61.27% 50.52% 48.20% 47.62% 47.19% worse Solvency 6.90 7.22 10.46 10.46 10.46 OK Cash Flow Ratios 5.61% 6.50% 6.50% 6.50% 6.50% OK Net Operating Cash Flow/Sales -7.96% 5.36% 2.24% 3.11% 3.15% Better Cash Flow before Financing/Sales -14.04% -0.17% -2.84% -1.96% -1.96% OK 00 01 02 04 Forecast balance Sheet Cash and Deposit 5,830.46 5,749.62 7,423.33 6,573.21 Total Assets 26,354.37 29,356.19 34,570.88 49,955.80 Total Liabilities 21,120.39 22,131.86 25,451.64 37,399.77 Shareholder Funds 5,233.98 7,224.33 9,119.24 12,556.03 00 01 02 03 04 Forecast Cash Flow Earning before interest,tax,depreciation and amortization 2,394.95 3,196.82 3,676.35 4,227.80 4,861.97 Operating Cash Flow (7,711.34) 2,635.29 1,264.91 2,021.86 2,354.02 Performance Analysis Funding Gap 18 00 01 02 03 04 Action Funding Gap 24.826 13.350 13.350 19.293 19.293 OK 00 01 02 03 04 - 10,000.00 20,000.00 30,000.00 40,000.00 50,000.00 60,000.00 70,000.00 80,000.00 - 500.00 1,000.00 1,500.00 2,000.00 2,500.00 3,000.00 3,500.00 Sales Net Profit 00 01 02 04 - 10,000.00 20,000.00 30,000.00 40,000.00 50,000.00 60,000.00 Cash and Deposit Total Assets Total Liabilities Shareholder Funds Funding Gap 00 01 02 03 04 0.000 5.000 10.000 15.000 20.000 25.000 30.000 Funding Gap 00 01 02 03 04 (10,000.00) (8,000.00) (6,000.00) (4,000.00) (2,000.00) - 2,000.00 4,000.00 6,000.00 Earning before interest,tax,depreciation and amortization Operating Cash Flow
  • 3. Main Menu Inputs Forecast 00 01 02 03 04 Actual Forecast Forecast Forecast Forecast Initial Sales 42,766.88 49,181.91 56,559.20 65,043.08 74,799.54 Sales Growth 4% 15% 15% 15% 15% Cost of Goods Sold / Sales 87.70% 87% 87% 87% 87% Depreciation Rates 19.10% 15% 15% 15% 15% SG&A/sales 6.70% 6.50% 6.50% 6.50% 6.50% Interes Paid on Debt 2.30% 4% 4% 4% 4% Interest Earned on Cash - 2% 2% 2% 2% Exceptionals/sales - - - - Marginal tax rate - 20% 20% 20% 20% Dividend payout ratio 35.60% 40% 40% 40% 40% Net Fixed Asset/sales 12.50% 8% 8% 8% 8% Intangibles/sales - - - - Current assets/sales 52.20% 40% 40% 50% 50% current liabilities/sales 32.20% 30% 30% 35% 35% Debt/sales 20.20% 15% 15% 15% 15% Deferred tax/sales - - - - - New Equity - 1,000 500 - - Net Fixed Assets 5,345.86 3,934.55 4,524.74 5,203.45 5,983.96 Debt 8,293.00 7,377.29 8,483.88 9,756.46 11,219.93 Current Assets 22,324.31 19,672.76 22,623.68 32,521.54 37,399.77 Current Liabilities 13,770.94 14,754.57 16,967.76 22,765.08 26,179.84
  • 4. Main Menu FORECAST INCOME STATEMENTS 00 01 02 03 04 Actual Forecast Forecast Forecast Forecast Sales 42,766.88 49,181.91 56,559.20 65,043.08 74,799.54 Cost of Goods Sold 37,506.55 42,788.26 49,206.50 56,587.48 65,075.60 Gross Profit 5,260.33 6,393.65 7,352.70 8,455.60 9,723.94 Depreciation- manufacturer 848.26 801.88 590.18 678.71 780.52 - Amortization / Others - - - - - Sales,General and Administration over heads 2,865.38 3,196.82 3,676.35 4,227.80 4,861.97 Net Operating profit 1,546.68 2,394.95 3,086.16 3,549.09 4,081.45 Interest Expense 127.33 331.72 295.09 339.36 390.26 Interest Income - - 114.99 148.47 110.57 Other Financial Income - - - - - Profit after Financial item 1,419.35 2,063.23 2,906.07 3,358.20 3,801.77 Exceptional Expense - - - - - Profit before Tax 1,419.35 2,063.23 2,906.07 3,358.20 3,801.77 Tax - 412.65 581.21 671.64 760.35 Net Profit after Tax 1,419.35 1,650.58 2,324.85 2,686.56 3,041.42 Dividend 505.29 660.23 929.94 1,074.62 1,216.57 Retained profit for the year 914.06 990.35 1,394.91 1,611.94 1,824.85
  • 5. Main Menu FORECAST BALANCE SHEET 00 01 02 03 04 Actual Forecast Forecast Forecast Forecast Cash and Deposit 5,830.46 5,749.62 7,423.33 5,528.73 6,573.21 Marketable securities - - - - - Trade Receivables (Debtors) 4,949.30 5,692.88 6,546.81 9,411.04 10,822.69 Inventory 241.57 277.86 319.54 459.33 528.23 Sundry Assets 11,912.35 13,702.03 15,757.34 22,651.17 26,048.85 Current Assets 22,933.68 25,422.39 30,047.01 38,050.27 43,972.98 Land and Building 1,091.04 1,254.70 1,442.91 1,659.34 1,908.24 Plant and Machinery 3,626.35 4,170.30 4,795.85 5,515.22 6,342.51 Depreciation 1,296.70 1,491.20 1,714.88 1,972.11 2,267.93 Net Property, Plant and Equipment 3,420.70 3,933.80 4,523.87 5,202.45 5,982.82 Other Investments - - - - - Intangibles / Goodwill - - - - - Non Current Assets 3,420.70 3,933.80 4,523.87 5,202.45 5,982.82 Total Assets 26,354.37 29,356.19 34,570.88 43,252.72 49,955.80 Short Term Bank Loan - - - - - Trade Creditors 3,186.87 3,665.66 4,215.51 5,655.81 6,504.19 Other Creditors 2,479.48 2,851.99 3,279.79 4,400.39 5,060.45 Other Current Liabilities 7,161.04 8,236.92 9,472.45 12,708.87 14,615.21 Current Liabilities 12,827.39 14,754.57 16,967.76 22,765.08 26,179.84 long term Bank Loan 8,293.00 7,377.29 8,483.88 9,756.46 11,219.93 Deferred TaxLiability - - - - - Long Term Liabilities 8,293.00 7,377.29 8,483.88 9,756.46 11,219.93 Ordinary Shares 2,000.00 3,000.00 3,500.00 3,500.00 3,500.00 Retained Profit 3,233.98 4,224.33 5,619.24 7,231.18 9,056.03 Shareholder Funds 5,233.98 7,224.33 9,119.24 10,731.18 12,556.03 Total Liabilities and Equity 26,354.37 29,356.19 34,570.88 43,252.72 49,955.80 - - - - -
  • 6. Main Menu FORECAST CASH FLOW 00 01 02 03 04 Actual Forecast Forecast Forecast Forecast Net Operating Profit 1,546.68 2,394.95 3,086.16 3,549.09 4,081.45 Depreciation / Amortization 848.26 801.88 590.18 678.71 780.52 Earning before interest , tax, Depreciation and Amortization 2,394.95 3,196.82 3,676.35 4,227.80 4,861.97 Change in current asset (22,933.68) (2,488.71) (4,624.62) (8,003.26) (5,922.71) Change in current Liabilities 12,827.39 1,927.18 2,213.19 5,797.32 3,414.76 Net Operating Cash flow (7,711.34) 2,635.29 1,264.91 2,021.86 2,354.02 Retrun on Investment and servicing from finance Interest received - - 114.99 148.47 110.57 interest expense (127.33) (331.72) (295.09) (339.36) (390.26) dividends (505.29) (660.23) (929.94) (1,074.62) (1,216.57) Net Cash from retuen on investments (632.62) (991.95) (1,110.04) (1,265.51) (1,496.25) Taxation Taxation - (412.65) (581.21) (671.64) (760.35) deferred Tax - - - - - Net cash from tax - (412.65) (581.21) (671.64) (760.35) Investment Activities Expenditure on Fixed Asset (4,268.96) (1,314.98) (1,180.25) (1,357.29) (1,560.88) Expenditure on Long term investments - - - - - Marketable Securities - - - - - Net Cash from investment activities (4,268.96) (1,314.98) (1,180.25) (1,357.29) (1,560.88) Exceptional items - - - - - Net Cash flow before financing activities (12,612.92) (84.29) (1,606.59) (1,272.58) (1,463.47) Financing Activities Long term Loan 8,293.00 (915.71) 1,106.59 1,272.58 1,463.47 Share Capital 2,000.00 1,000.00 500.00 - - Net Cash from financing activities 10,293.00 84.29 1,606.59 1,272.58 1,463.47 Cash and cash equivalent during the year (2,319.92) 0.00 (0.00) 0.00 (0.00) Cash and Deaposit at beginning of the year 5,830.46 5,749.62 7,423.33 5,528.73 Cash and Deposit at the end of the year 5,830.46 5,749.62 7,423.33 5,528.73
  • 7. Main Menu FORECAST FINANCIAL RATIOS 99 00 01 02 03 04 Action Actual Actual Forecast Forecast Forecast Forecast Forecast Core Ratios Return on Sales 5.90% 2.76% 3.36% 4.11% 4.13% 4.07% OK Asset Turnover 1.86 1.55 1.68 1.64 1.50 1.50 OK Asset leverage 4.81 5.28 4.06 3.79 4.03 3.98 worse Return on Equity 52.80% 22.52% 22.85% 25.49% 25.04% 24.22% OK Profitability Gross Profit Margin 14.11% 12.31% 13.00% 13.00% 13.00% 13.00% OK Net Operating Profit Margin 6.60% 3.23% 4.87% 5.46% 5.46% 5.46% OK Net Profit before tax Margin 5.90% 2.76% 4.20% 5.14% 5.16% 5.08% OK Return on Capital employed 26.95% 10.20% 16.40% 17.53% 17.32% 17.17% OK Return on Assets 12.29% 4.99% 8.16% 8.93% 8.21% 8.17% OK Operating Efficiency Inventory Days 3.99 3.07 2.37 2.37 2.96 2.96 Better Ttade Receivalbe Days 35.97 55.09 42.25 42.25 52.81 52.81 OK Creditors Days 37.46 33.33 31.27 31.27 36.48 36.48 Worse Funding Gap 2.50 24.83 13.35 13.35 19.29 19.29 OK Financial Structure Current Ratio 1.20 1.58 1.72 1.77 1.67 1.68 Better Quick asset ratio 1.17 1.56 1.70 1.75 1.65 1.66 Better Working Capital 2,363 8,164 10,668 13,079 15,285 17,793 Better Gross Gearing 55.13% 61.27% 50.52% 48.20% 47.62% 47.19% worse Solvency 9.42 6.90 7.22 10.46 10.46 10.46 OK Cash Flow Ratios EBITDA/Sales 9.21% 5.61% 6.50% 6.50% 6.50% 6.50% OK Net Operating Cash Flow/Sales 0.31% -7.96% 5.36% 2.24% 3.11% 3.15% Better Cash Flow before Financing/Sales 2.65% -14.04% -0.17% -2.84% -1.96% -1.96% OK Assumption Action can be calculated by the comparision of three years performance if forecast year performance (01) improved by the preceding two years than performance is "Better", if forecast year performance (01) is improved as compared to either of preceding years than perforamce is "OK" , if forecast year performance (01) is fallen as compared to preceding two years than performance is "worst"
  • Related Search
    We Need Your Support
    Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

    Thanks to everyone for your continued support.

    No, Thanks