A Spreadsheet Model for Forecasting Dividends An analyst is preparing a forecast of dividends for Hoshino Distributors for the next five years. He uses a spreadsheet model with the following assumptions: Sales are $100 million in Year 1. They grow by 20 percent in Year 2, 15 percent in Year 3, and 10 percent in Years 4 and 5. Operating profits (EBIT 5 earnings before interest and taxes) are 20 percent of sales in Years 1 and 2, 18 percent of sales in Year 3, and 16 percent of sales in Years 4 and 5. Interest expenses are 10 percent of total debt for the current year. The income tax rate is 40 percent. Hoshino pays out 20 percent of earnings in Years 1 and 2, 30 percent in Year 3, 40 percent in Year 4, and 50 percent in Year 5. Retained earnings are added to equity in the next year. Total assets are 80 percent of the current year's sales in all years. In Year 1, debt is $40 million and shareholders' equity is $40 million. Debt equals total assets minus shareholders' equity. Shareholders equity will equal the previous year's shareholders' equity plus the addition to retained earnings from the previous year Hoshino has 4 million shares outstanding. The discount rate is 15 percent, and the value of the company at the end of Year 5 will be 10.0 times earnings. The analyst wishes to estimate the current value per share of Hoshino. Table 14 adheres to the modeling assumptions above. Total div- idends and earnings are found at the bottom of the income statement. A Spreadsheet Model for Forecasting Dividends An analyst is preparing a forecast of dividends for Hoshino Distributors for the next five years. He uses a spreadsheet model with the following assumptions: Sales are $100 million in Year 1. They grow by 20 percent in Year 2, 15 percent in Year 3, and 10 percent in Years 4 and 5. Operating profits (EBIT 5 earnings before interest and taxes) are 20 percent of sales in Years 1 and 2, 18 percent of sales in Year 3, and 16 percent of sales in Years 4 and 5. Interest expenses are 10 percent of total debt for the current year. The income tax rate is 40 percent. Hoshino pays out 20 percent of earnings in Years 1 and 2, 30 percent in Year 3, 40 percent in Year 4, and 50 percent in Year 5. Retained earnings are added to equity in the next year. Total assets are 80 percent of the current year's sales in all years. In Year 1, debt is $40 million and shareholders' equity is $40 million. Debt equals total assets minus shareholders' equity. Shareholders equity will equal the previous year's shareholders' equity plus the addition to retained earnings from the previous year Hoshino has 4 million shares outstanding. The discount rate is 15 percent, and the value of the company at the end of Year 5 will be 10.0 times earnings. The analyst wishes to estimate the current value per share of Hoshino. Table 14 adheres to the modeling assumptions above. Total div- idends and earnings are found at the bottom of the income statement
