This is my first post in this forum; though I come here often to have a look at the discussions going on individual stocks. Appreciate the quality of discussion and knowledge of the fellow boarders.
I tried to do the 5 year forecasting of a popular company - Rain Industries. More so because of its recent fall. Attached below is the link to the doc (I am unable to upload it since newbie):
Rain Industry forecast
I am a learner in this field and this is the first time I have tried to forecast and link the 3 financial statements. It goes without saying that I have failed in linking the statements despite the best of my efforts.
I would need help and feedback from all you in getting the sheet corrected and helping me learn.
The steps that I took and the issues I faced are as below:
Forecasted Income statement using Ratios:
- Sales Forecast:
a. Calculated scenarios – Historical and Latest as per company sales
b. Calculated Scenario where company sales will be equal to industry average. Competitors considered – Graphite India, Philips Carbon Black, Himadri Speciality Chemicals and BASF India. All companies are in Carbon production & Speciality chemicals field and are in related categories with Rain Industries.
- All other heads are forecasted based on Historical Average/ Latest/ Manual Figures.
- Depreciation is forecasted based on % on total Sales over years.
- Finance Cost is not forecasted as % of sales. It is derived from Balance Sheet.
a. Finance Cost is derived as % to Non Current Liability to factor in the effect of change in long term liability.
b. Non Current liability though is forecasted basis forecasted sales.
- Dividends are not directly forecasted in Income Statement. We derive these figures when we make the balance sheet.
What we see is that Profit (PAT) as a % of sales is inline with the latest trend.
Forecast Balance Sheet and tying with Income Statement:
- Equity Share capital is considered constant
- Reserves and Surplus is not directly forecasted.
a. It is derived using (Reserves & Surplus = Total Assets – Liability) Principle of Balance Sheet is TOTAL ASSET = TOTAL LIABILITY.
- Total Non Current Liability is forecasted as a % to Net Sales. Here too Historical/ Latest and Manual assumption mode is built in
a. Considered “latest” ratio to forecast Non Current liability since we don’t know how long term liability will shape up. Also huge reduction in Long Term borrowing in 2017. Expectedly from 2019, the expected long term loan will be low and inline with 2017
b. Individual items are Non Current Liability is not considered.
- Individual items in Current Liability is forecasted as a % to Net sales. Here too option of Historical average/ Latest Contribution and Manual option is given.
- Similarly Non Current Assets are forecasted as a whole since we would not need the forecast of individual items in Non Current Assets. (Only Change in Total Non Current Assets required in making of Cash Flow Statement)
a. Here too Non Current Assets as a % of Net Sales is calculated and Forecasted basis Historical Average/ Latest/ Manual fig.
- Current Assets figs cannot be directly forecasted since Cash and Cash equivalents cannot be directly forecasted. It is derived from Cash flow statement.
a. All other items under this head can be forecasted as a % Net Sales.
- We forecast Total Assets as a % of Total sales. Here too Historical/ Latest/ manual fig can be fed.
- Current Assets = Total Assets – Non Current Assets. This way Non Current Assets are forecasted.
- Retained Earnings = Total Assets – Current Liabilities – Non Current Liability – Share Capital
- Dividend to be paid (tax included) = Profit Generated – (Increase in Retained Earning)
Cash Flow Statement is constructed basis the forecasted Income Statement and Balance Sheets.
Current Assets figs not matching.
a. Cash is derived from Cash Flow Statement.
b. Cash and Cash Equivalent in Balance Sheet = Opening Cash + Total Cash Flow Generated in the year.
c. All Other contributors (Except Cash) are estimated as a % of Net Sales
d. Ideally if balanced, Summation of individual elements in Current Assets = Current Assets Forecasted as a % of Total Sales.
e. The above check is failing.
- Abnormal Dividend payout is happening.
- Also, in Dividend payout, how did the company arrive at the figs for year 2014-2017 is unclear. If we use the formula of (Dividend = PAT – change in retained earnings), then the figs are very much different than what is given in the annual statements.
Seeking help/ feedback from all valued contributors.