Using the data below, forecast the June 30, 2011, income statement and balance s
ID: 2776573 • Letter: U
Question
Using the data below, forecast the June 30, 2011, income statement and balance sheet for Procter & Gamble. Use the percent of sales method and the following assumptions: (1) Sales in FY 2011 will be $81,000; (2) The tax rate will be 27.26%; (3) Each item that changes with sales will be the five-year average percentage of sales; (4) The preferred dividend will be 219; and (5) The common dividend payout ratio will be 42% of income available to common stockholders.
a. calculate the percentage of sales income statement, but this time use the TREND function to forecast other income and interest expense.
b. calculate the percentage of sales balance sheet, but this time use the TREND function to forecast cash, gross property plant and equipment, gross intangibles, and other long-term assets.
c. Do these new values appear to be more realistic than the original values? Does this technique make sense for each of these items? Might other income statement or balance sheet items be forecasted in this way?
The Procter & Gamble Company Pro-Forma Annual Income Statement Jun-10 Jun-09 Jun-08 Jun-07 Jun-06 Total Revenue 78,938 76,694 79,257 72,441 64,416 Cost of Goods Sold 37,919 38,690 39,261 35,376 31,867 Gross Profit 41,019 38,004 39,996 37,065 32,549 Selling / General / Administrative Expense 24,998 22,630 24,017 22,580 19,998 Net Operating Income 16,021 15,374 15,979 14,485 12,551 Interest Expense 946 1,358 1,467 1,304 1,120 Other Non-Operating Expense (Income) 28 -397 -373 -481 -221 Earnings Before Taxes 15,047 14,413 14,885 13,662 11,652 Income Taxes 4,101 3,733 3,594 4,000 3,465 Income Before Extraordinary Items 10,946 10,680 11,291 9,662 8,187 Total Extraordinary Items 1,790 2,756 784 678 497 Net Income 12,736 13,436 12,075 10,340 8,684 Preferred Dividends 219 192 176 161 148 Income Available to Common Stocks 12,517 13,244 11,899 10,179 8,536 Basic Weighted Average Shares 2,900.80 2,952.20 3,080.80 3,159.00 3,112.93 Basic EPS Excluding Extraordinary Items 3.70 3.55 3.61 3.01 2.58 Basic EPS Including Extraordinary Items 4.32 4.49 3.86 3.22 2.74 Common Dividends per Share 1.80 1.64 1.45 1.28 1.15 Gross Dividends - Common Stock 5,239.00 4,852.00 4,479.00 4,048.00 3,555.00Explanation / Answer
(a) June 10 June 09 June 08 June 07 June 06
Sale percentage to interest exp 946/78938 1358/76694 1467/79257 1304/72441 1120/64416
1.20% 1.77% 1.85% 1.80% 1.74%
Average of all 5year % of interst expenses = (1.20 + 1.77 + 1.85 + 1.80 + 1.74 ) / 5
=1.67%
Forecasted interest expense for 2011 = 81000 * 1.67% = 1352.7
June 10 June 09 June 08 June 07 June 06
sales pecentage to other income (28) / 78938 397/ 76694 373/79257 481/ 72441 221/64416
( 0.04%) 0 .52% 0.47% 0.66% 0.34%
average of all 5 year of other income =
(0.04)+ 0.52 + 0.47 + 0.66 + 0.34 ) / 5 = 0.39%
forecasted other income for 2011 = 81000 * .39% = 315.9
(b) cash forecasted for 2011 = 3.65% + 6.23% + 4.18% + 7.39% + 10.39% / 5 = 6.37% * 81000 = 5159.7
plant and machinery = 46.90 % + 47.79% + 48.05%+ 47.93%+ 49.49% / 5 = 48.03% * 81000 = 38904.3 intangible = 44.58% + 46.69%+ 46.56% + 49.06%+ 54.34% / 5 = 48.25% * 81000 = 39082.5
other long term assets = 4.53%+ 5% +5.31% +7.11% + 6.94% / 5 = 5.78% * 81000 = 4681.8
Note : % calculation is same as done in part(a) answer
average of all 5 year of other income =
(0.04)+ 0.52 + 0.47 + 0.66 + 0.34 ) / 5 = 0.39%
forecasted other income for 2011 = 81000 * .39% = 315.9
(b) cash forecasted for 2011 = 3.65% + 6.23% + 4.18% + 7.39% + 10.39% / 5 = 6.37% * 81000 = 5159.7
plant and machinery = 46.90 % + 47.79% + 48.05%+ 47.93%+ 49.49% / 5 = 48.03% * 81000 = 38904.3 intangible = 44.58% + 46.69%+ 46.56% + 49.06%+ 54.34% / 5 = 48.25% * 81000 = 39082.5
other long term assets = 4.53%+ 5% +5.31% +7.11% + 6.94% / 5 = 5.78% * 81000 = 4681.8
Note : % calculation is same as done in part(a) answer
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.