Introduction
The report is to create five year forecast of financial statement of the company with the given assumption of the current years and future years.
Backward calculation process has to use to prepare the Financial Statements i.e Income Statement, Balance Sheet and Cash Flow statement along with Ratios for the year 2019, and on the basis of 2019 statements and given assumptions, Forecast will be done for Year 2020 to 2024.
Data Available
There is no specific Data available other than Sales data assumption, so first sales has to be calculated and accordingly everything as per sales ratios can be generated. As per assumption following table is available for the market share for the industry and for the company:-
| ABC’s | |||||
| Industry (units) | Market Share | Growth Rate | Share | In Unit | |
| Sales | |||||
| Growth | 70,00,000.00 | 33% | 5% | 15% | 10,50,000 |
| Mature | 1,00,00,000.00 | 50% | 2% | 10% | 10,00,000 |
| Decline | 30,00,000.00 | 17% | 0% | 5% | 1,50,000 |
| Total | 2,00,00,000.00 | 22,00,000 |
As per assumption the product Decline will be discontinued after 5 years.
Assignment Assumption
| Ratios/Statistics Net profit margin Asset turnover (x:1) Return on assets Total debt to equity (x:1) Return on equity (ROE) Accounts payable days Gross margin External debt to equity Return on sales Receivable days Inventory days WC excluding cash (x:1) Assets to equity (x:1) Dupont ROE Normal Dividend payout Cost of debt Cash tax rate Accounting tax rate Market statistics: Risk free rate Market rate expectation Beta Shares outstanding Capital Contributed (M$) Sustainable growth rate Cost of capital (WACC) Equity costs CAPM Share price (2019) Rating Agency Criteria | 2019 8.94% 2.0 17.88% 1 35.8% 34.50 45.0% 67.0% 8.94% 20 15 1 2 35.8% 40% 5% 26.50% 37.00% 4% 10% 1.6 20,000,000 40 3.0% 9.4% 13.6% 13.53 | Subsequent Year Calculate in subsequent years Calculate in subsequent years Calculate in subsequent years Calculate in subsequent years Calculate in subsequent years Stays same all years Calculate in subsequent years Stays same all years Calculate in subsequent years Stays same all years Stays same all years Calculate in subsequent years Calculate in subsequent years Calculate in subsequent years Stays same all years Stays same all years Stays same all years Stays same all years Stays same all years Stays same all years Stays same all years Stays same all years Stays same all years (in millions) Long term mature company Stays same all years Stays same all years Do valuation Credit Rating Ratios AAA A < 1 times > 1 < 3 times > 10% 5 to 10% >10 times >6 to 10 times >20% >10% to 20% up 3% flat | AT ROS Lev ROS X AT X Lev |
Calculation
Additional Assumptions:-
Additional Capital expenses done will have average life of 10 years with straight line method of Depreciation
Few of the items are balancing figures like capital expenditure in the statement
Other Current Assets and other Current liabilities are increased by 5 %
| SALES | |||||
| ABC’s | 2019 | ||||
| Industry (units) | Share | In Unit | Price | Total Sales | |
| Sales | |||||
| Growth | 70,00,000.00 | 15% | 10,50,000 | 100 | $10,50,00,000 |
| Mature | 1,00,00,000.00 | 10% | 10,00,000 | 75 | $7,50,00,000 |
| Decline | 30,00,000.00 | 5% | 1,50,000 | 50 | $75,00,000 |
| Total | 2,00,00,000.00 | 22,00,000 | 18,75,00,000.00 | ||
| COGS | (Balancing Figure) | 10,31,25,000.00 | |||
| Gross Profit @45% | 8,43,75,000.00 | ||||
| Depreciation | 10,90,000.00 | ||||
| Fixed Cost | 2,67,53,750.00 | ||||
| NET PROFIT | 5,65,31,250.00 |
Sales forecast
| 2020 | 2021 | |||||
| Unit | Price | Total | Unit | Price | Total | |
| Sales | ||||||
| Growth | $11,55,000 | $105 | $12,12,75,000 | $12,70,500 | $110 | $14,00,72,625 |
| Mature | $10,20,000 | $75 | $7,65,00,000 | $10,40,400 | $75 | $7,80,30,000 |
| Decline | $1,50,000 | $38 | $56,25,000 | $1,50,000 | $25 | $37,50,000 |
| Total | 20,34,00,000.00 | 22,18,52,625.00 | ||||
| COGS | 11,39,04,000.00 | 12,64,55,996.25 | ||||
| Gross Profit @45% | 8,94,96,000.00 | 9,53,96,628.75 | ||||
| Depreciation | 10,90,000.00 | 10,90,000.00 | ||||
| Fixed Cost | 2,67,53,750.00 | – | – | 2,67,53,750.00 | ||
| NET PROFIT | – | – | 6,16,52,250.00 | – | – | 6,75,52,878.75 |
| 2022 | 2023 | 2024 | |||||||
| Unit | Price | Total | Unit | Price | Total | Unit | Price | Total | |
| Sales | |||||||||
| Growth | $13,97,550 | $116 | $15,40,79,888 | $15,37,305 | $122 | $16,94,87,876 | $16,91,036 | $128 | $21,58,23,743 |
| Mature | $10,61,208 | $75 | $7,95,90,600 | $10,82,432 | $75 | $8,11,82,412 | $11,04,081 | $75 | $8,28,06,060 |
| Decline | $1,50,000 | $13 | $37,50,000 | $1,50,000 | $0 | $37,50,000 | $1,50,000 | $0 | $0 |
| Total | 23,74,20,488 | 25,44,20,288 | 29,86,29,803.26 | ||||||
| COGS | 13,77,03,882.75 | 15,01,07,970.07 | 17,91,77,881.95 | ||||||
| Gross Profit @45% | 9,97,16,604.75 | 10,43,12,318.18 | 11,94,51,921.30 | ||||||
| Depreciation | 10,90,000.00 | 10,90,000.00 | 10,90,000.00 | ||||||
| Fixed Cost | – | – | 2,67,53,750.00 | – | – | 2,67,53,750.00 | – | – | 2,67,53,750.00 |
| NET PROFIT | – | – | 7,18,72,854.75 | – | – | 7,64,68,568.18 | – | – | 9,16,08,171.30 |
Working notes:
| Fixed Cost Calculation | |||
| FC= | EBIT/GM*100 | ||
| 33%= | EBIT/84375000*100 | ||
| EBIT= | 87375000*33% | ||
| 2,78,43,750.00 | |||
| Fixed Assets Calculation – Depreciation | |||
| Million | Average useful Life | ||
| Cost | 10.9 | 10 years | |
| Depreciation Per year | 10.9 | ||
| Balance life | 5 years | ||
| Accumulated Depreciation | 54.5 | ||
| Balance Depreciation | 54.5 | ||
| Fixed Asset | 54.5 | ||
| Depreciation of next five Years 2020-2024 | |||
| Old Assets | 10.9 | ||
| Capital Expenditure | 38.44 | 10 year | 3.84 |
| Assumption | |||
| TotalDep | 14.74 |
Statement to calculate the cash flow and other balancing for 2019
| Format for financial statements (all cash models must be same ending cash) | |
| (in millions of dollars) | |
| Cash flow models | |
| A. EBITDA model | |
| EBITDA | 57.62 |
| – Capital expenditures | -38.44 |
| – Interest paid | -1.57 |
| – Cash taxes | -16.71 |
| – Net working capital incr * | 0 |
| + External debt issued | 31.37 |
| – Dividends paid | -11.68 |
| Net cash flows for year | 20.59 |
| Opening cash | 3.10 |
| Ending cash | 23.69 |
| B. Net income Model | ||
| Net Income | 16.76 | |
| + Depreciation | 10.90 | |
| + Change in deferred taxes | 2.74 | |
| Change in working capital | ||
| Change in other operating items | ||
| Cash flow from operations (CFO) | 30.40 | |
| – Cash flow from investments (CFI) | 29.5 | Balancing Figure |
| Cash flow from financing (CFF): | ||
| – Dividends | -11.68 | |
| + External debt | 31.4 | |
| Total CFF | ||
| Net cash flows for year | 20.59 | |
| Opening cash | 3.1 | |
| Ending cash | 23.69 |
| C. Direct Method | |
| Net sales | 187.50 |
| Costs of sales | -103.13 |
| Fixed costs | -26.75 |
| – Interest expense | -1.57 |
| – Taxes | -16.71 |
| – Capital expenditures | -38.44 |
| + External debt | 31.37 |
| – Dividends | -11.68 |
| Net cash flows for the year | 20.59 |
| Opening cash | 3.1 |
| Ending cash | 23.69 |
On the basis of above Assumptions the following forecast is done for financial statement of the company, there are other calculations which are on the basis of the ratio given for 2019 and other also
| Current | Forecast | |||||
| Balance sheet | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 |
| Cash | 23.69 | 49.22 | 72.06 | 97.98 | 125.51 | 160.86 |
| Accounts receivable | 10.27 | 11.15 | 12.16 | 13.01 | 13.94 | 16.36 |
| Inventory | 4.24 | 4.68 | 5.20 | 5.66 | 6.17 | 7.36 |
| Other current assets | 0.95 | 1.00 | 1.05 | 1.10 | 1.16 | 1.22 |
| Total current assets | 39.14 | 66.05 | 90.46 | 117.75 | 146.77 | 185.80 |
| Capital assets | 54.50 | 54.50 | 54.50 | 54.50 | 54.50 | 54.50 |
| Other long term assets | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| Total assets | 93.65 | 120.55 | 144.96 | 172.25 | 201.27 | 240.30 |
| Accounts payable | 9.75 | 10.77 | 11.95 | 13.02 | 14.19 | 16.94 |
| Deferred tax liability | 2.00 | 5.32 | 4.83 | 5.40 | 5.80 | 6.22 |
| Other current liabilities | 3.71 | 3.90 | 4.09 | 4.29 | 4.51 | 4.74 |
| Total current liabilities | 15.46 | 19.98 | 20.87 | 22.71 | 24.50 | 27.89 |
| External debt | 31.37 | 40.35 | 49.79 | 60.00 | 70.92 | 85.22 |
| Total debt | 46.83 | 60.33 | 70.66 | 82.70 | 95.42 | 113.11 |
| Capital contributed | 40.00 | 40.00 | 40.00 | 40.00 | 40.00 | 40.00 |
| Retained earnings | 6.82 | 20.22 | 34.31 | 49.55 | 65.86 | 87.19 |
| Equity | 46.82 | 60.22 | 74.31 | 89.55 | 105.86 | 127.19 |
| Liabilities & Equity | 93.65 | 120.55 | 144.96 | 172.25 | 201.27 | 240.30 |
| Income statement | Current | Forecast | ||||
| Net sales | 187.50 | 203.40 | 221.85 | 237.42 | 254.42 | 298.63 |
| Cost of goods sold | 103.13 | 113.90 | 126.46 | 137.70 | 150.11 | 179.18 |
| Gross Margin | 84.38 | 89.50 | 95.40 | 99.72 | 104.31 | 119.45 |
| Fixed costs: | ||||||
| Depreciation | 10.90 | 14.74 | 14.74 | 14.74 | 14.74 | 14.74 |
| Other fixed costs | 26.75 | 26.75 | 26.75 | 26.75 | 26.75 | 26.75 |
| EBIT | 46.72 | 48.00 | 53.90 | 58.22 | 62.82 | 77.96 |
| Interest expense | 1.57 | 2.02 | 2.49 | 3.00 | 3.55 | 4.26 |
| EBT | 45.15 | 45.98 | 51.41 | 55.22 | 59.27 | 73.70 |
| Tax expense | 17.29 | 17.01 | 19.02 | 20.43 | 21.93 | 27.27 |
| Dividend Pay out | 11.10 | 6.71 | 8.91 | 9.39 | 10.16 | 10.87 |
| Net income | 16.76 | 22.27 | 23.48 | 25.40 | 27.18 | 35.56 |
| Dividend Declared | 6.71 | 8.91 | 9.39 | 10.16 | 10.87 | 14.22 |
Retained Earnings statement is used to carry forward the balances to Balance Sheet
| Statement of retained earnings | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 |
| Opening retained earnings | -3.2 | 6.86 | 20.22 | 34.31 | 49.55 | 65.86 |
| Net Income | 16.76 | 22.27 | 23.48 | 25.40 | 27.18 | 35.56 |
| Less Dividend | -6.71 | -8.91 | -9.39 | -10.16 | -10.87 | -14.22 |
| Ending retained earnings | 6.8575 | 20.22 | 34.31 | 49.55 | 65.86 | 87.19 |
Assumption is taken that Dividend Declared in given as payout in next financial year.
Working Capital Position of the company
Answer 3
The Company’s Working capital position has improved, as can be Seen in the Balance sheet, Company has grown because only the sales has increased but also the sales price of high end product i.e Mature has also increased which has resulted good inflow in the business and has resulted with working capital improvement
The company will accumulate or reduce cash over the forecast, why
Answer 4
The company is accumulating the cash , as currently there is no capital expenditure done due to no new product is launched and company is gaining from the Current product line, so it will be accumulating the cash for may be future investments .
Key ratios to the investor are deteriorating or improving, why
Answer 5
It is mixed, as Return on Equity has improved whereas the few of them have been static, but as the profit is going to increase and share price forecast is also that it will improve from investors point of view , it is good investment option. But Return on Assets has started reducing because of withdrawal of product Decline, so the asset utilization has also reduced
| Forecast | |||||||
| Ratios/Statistics | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 | Assumption /Guideline |
| Net profit margin | 8.94% | 10.95% | 10.59% | 10.70% | 10.68% | 11.91% | Calculate in subsequent years |
| Asset turnover (x:1) | 2 | 1.7 | 1.5 | 1.4 | 1.3 | 1.2 | Calculate in subsequent years |
| Return on assets | 17.88% | 18.47% | 16.20% | 14.74% | 13.51% | 14.80% | Calculate in subsequent years |
| Total debt to equity (x:1) | 1 | 1.00 | 0.95 | 0.92 | 0.90 | 0.89 | Calculate in subsequent years |
| Return on equity (ROE) | 35.80% | 18% | 16% | 15% | 14% | 15% | Calculate in subsequent years |
| Accounts payable days | 34.5 | 34.5 | 34.5 | 34.5 | 34.5 | 34.5 | Calculate in subsequent years |
| Gross Margin | 45.00% | 44% | 43.00% | 42.00% | 41.00% | 40.00% | Stays same all years, reduces by 1 % |
| External debt to equity | 67.00% | 67.00% | 67.00% | 67.00% | 67.00% | 67.00% | Calculate in subsequent years |
| Return on sales | 8.94% | 11% | 11% | 11% | 11% | 12% | Stays same all years |
| Receivable days | 20 | 20 | 20 | 20 | 20 | 20 | Stays same all years |
| Inventory days | 15 | 15 | 15 | 15 | 15 | 15 | Stays same all years |
| WC excluding cash (x:1) | 1 | 3.31 | 4.33 | 5.19 | 5.99 | 6.66 | Calculate in subsequent years |
| Assets to equity (x:1) | 2 | 2.00 | 1.95 | 1.92 | 1.90 | 1.89 | Calculate in subsequent years |
| Dupont ROE | 35.80% | 36.98% | 31.60% | 28.36% | 25.68% | 27.96% | Calculate in subsequent years |
| Normal Dividend payout | 40% | 40% | 40% | 40% | 40% | 40% | Stays same all years |
| Cost of debt | 5% | 5% | 5% | 5% | 5% | 5% | Stays same all years |
| Cash tax rate | 26.50% | 26.50% | 26.50% | 26.50% | 26.50% | 26.50% | Stays same all years |
| Accounting tax rate | 37.00% | 37.00% | 37.00% | 37.00% | 37.00% | 37.00% | Stays same all years |
| Market statistics: | |||||||
| Risk free rate | 4% | 4% | 4% | 4% | 4% | 4% | Stays same all years |
| Market rate expectation | 10% | 10% | 10% | 10% | 10% | 10% | Stays same all years |
| Beta | 1.6 | 1.6 | 1.6 | 1.6 | 1.6 | 1.6 | Stays same all years |
| Shares outstanding | 2,00,00,000 | 2,00,00,000 | 2,00,00,000 | 2,00,00,000 | 2,00,00,000 | 2,00,00,000 | Stays same all years |
| Capital Contributed (M$) | 40 | 40 | 40 | 40 | 40 | 40 | Stays same all years (in millions) |
| Sustainable growth rate | 3.00% | Long term mature company | |||||
| Cost of capital (WACC) | 9.40% | 9.40% | 9.40% | 9.40% | 9.40% | 9.40% | Stays same all years |
| Equity costs CAPM | 13.60% | 13.60% | 13.60% | 13.60% | 13.60% | 13.60% | Stays same all years |
| Share price (2019) | 13.53 | 20.4 | 12.3 | 16.3 | 17.2 | 18.6 | Do valuation |
The company is hold, buy or Sell
Answer
At this stage as per the forecast the company is growing and has a sustainable growth rate of 3% which indicates that one should sell it and in future it will give good returns. Secondly, investors can invest in the company but it extra –ordinary cash dividend of $50 M is paid then surely there is risk that in future if company has to invest in new products e.g Decline has already obsolete and it new product has to be launched for which company needs cash. It will have to borrow the funds increasing the cost of capital and debt equity ratio will also disturb which will affect the share price and return on equity. Ultimately it will impact the shareholder’s only.
ALUATION MODEL
A financial valuation model will include the analysis of the capital structure of the company, predicting the market value and the future earnings prospect. For this, the cash flow statements for the last five years, financial ratios and the market price of the stock. These information will be used for financial valuation.
Fundamental Analysis
In fundamental analysis, all fundamental macros and micros are taken into consideration in association with financial statement and balance sheet of a firm.
Constant perpetual growth model
In this model, it is assumed that dividend of a firm increases at a constant rate to perpetuity and pay-out ratio remains constant.
Value Per Share= Expected Rate of Return X Paid up Equity Value
Normal Rate of Return
Equity Value = Outstanding Shares X Current Share Price
= 2000000*13
= 2600000000
Stock Valuation (Basic) For 2020
| P0 = D 0 (1 + g)/(i – g) | ||
| D 0 | = Most recent per-share dividend | $11.10 |
| i | = Required return (discount rate) | 9.40% |
| g | = Rate of growth( Market rate Expectation) | 10% |
| P0 | = Value of one share of common stock= $11.10(1 + .10) /(.940 – .10) = $ | $20.35 |
Similarly for other financial Years from 2020 -2024 is also calculated as:-
2019 2020 2021 2022 2023 2024
| Share price | 13.53 | 20.4 | 12.3 | 16.3 | 17.2 | 18.6 |
Get solved or fresh solution on Economics financial calculation Assignment and many more. 24X7 help, plag free solution. Order online now!
