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 00 The Procter Gamble Company Pro Forma Annual Balance Sheet Jun 10 Jun 09 Jun 08 Jun 07 Jun 06 Assets Cash Equivalents 2,879 4,781 3,313 5,354 6,693 Short Term Investments 0 0 228 202 1,133 Accounts Receivable 5,335 5,836 6,761 6,629 5,725 Inventory 6,384 6,880 8,416 6,819 6,291 Prepaid Expenses 3,194 3,199 3,785 3,300 2,876 Other Current Assets, Total 990 1,209 2,012 1,727 1,611 Total Current Assets 18,782 21,905 24,515 24,031 24,329 Gross Plant and Equipment 37,012 36,651 38,086 34,721 31,881 Accumulated Depreciation, Total 17,768 17,189 17,446 15,181 13,111 Net Plant and Equipment 19,244 19,462 20,640 19,540 18,770 Goodwill, Net 54,012 56,512 59,767 56,552 55,306 Intangibles Gross 35,194 35,812 36,904 35,537 35,004 Accumulated Intangible Amortization 3,558 3,206 2,671 1,911 1,283 Intangibles, Net 31,636 32,606 34,233 33,626 33,721 Other Long Term Assets 4,498 4,348 4,837 4,265 3,569 Total Assets 128,172 134,833 143,992 138,014 135,695 Liabilities Accounts Payable 7,251 5,980 6,775 5,710 4,910 Accrued Expenses 4,679 4,361 4,544 9,586 9,587 Notes Payable Short Term Debt 7,908 9,379 11,338 9,495 198 Current Portion of Long Term Debt Capital Leases 564 6,941 1,746 2,544 1,930 Income Taxes Payable 622 722 945 3,382 3,360 Other Current Liabilities 3,258 3,518 5,610 0 0 Total Current Liabilities 24,282 30,901 30,958 30,717 19,985 Long Term Debt 21,360 20,652 23,581 23,375 35,976 Deferred Income Tax 10,902 10,752 11,805 12,015 12,354 Minority Interest 324 283 290 0 0 Pension Benefits Underfunded 6,616 5,314 3,658 0 0 Other Long Term Liabilities 3,573 3,832 4,206 5,147 4,472 Total Long Term Liabilities 42,775 40,833 43,540 40,537 52,802 Total Liabilities 67,057 71,734 74,498 71,254 72,787 Owner's Equity Redeemable Convertible Preferred Stock 1,277 1,324 1,366 1,406 1,451 Common Stock 4,008 4,007 4,002 3,990 3,976 Additional Paid In Capital 61,697 61,118 60,307 59,030 57,856 Retained Earnings 64,614 57,309 48,986 41,797 35,666 Less Treasury Stock and Other 70,481 60,659 45,167 39,463 36,041 Total Equity 61,115 63,099 69,494 66,760 62,908 Total Liabilities Shareholders' Equity 128,172 134,833 143,992 138,014 135,695 Total Common Shares Outstanding 2,843 47 2,917 04 3,032 72 3,131 95 3,178 84

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Aug 13, 2024

Using the data below, forecast the June 30, 2011, income statement and balance sheet for Procter & Gamble. Use the percent of sales method and

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.00

The Procter & Gamble Company
Pro-Forma Annual Balance Sheet
	Jun-10	Jun-09	Jun-08	Jun-07	Jun-06
Assets
Cash & Equivalents	2,879	4,781	3,313	5,354	6,693
Short Term Investments	0	0	228	202	1,133
Accounts Receivable	5,335	5,836	6,761	6,629	5,725
Inventory	6,384	6,880	8,416	6,819	6,291
Prepaid Expenses	3,194	3,199	3,785	3,300	2,876
Other Current Assets, Total	990	1,209	2,012	1,727	1,611
*Total Current Assets*	*18,782*	*21,905*	*24,515*	*24,031*	*24,329*

Gross Plant and Equipment	37,012	36,651	38,086	34,721	31,881
Accumulated Depreciation, Total	17,768	17,189	17,446	15,181	13,111
*Net Plant and Equipment*	*19,244*	*19,462*	*20,640*	*19,540*	*18,770*
Goodwill, Net	54,012	56,512	59,767	56,552	55,306
Intangibles - Gross	35,194	35,812	36,904	35,537	35,004
Accumulated Intangible Amortization	3,558	3,206	2,671	1,911	1,283
*Intangibles, Net*	*31,636*	*32,606*	*34,233*	*33,626*	*33,721*
Other Long Term Assets	4,498	4,348	4,837	4,265	3,569
*Total Assets*	*128,172*	*134,833*	*143,992*	*138,014*	*135,695*

Liabilities
Accounts Payable	7,251	5,980	6,775	5,710	4,910
Accrued Expenses	4,679	4,361	4,544	9,586	9,587
Notes Payable / Short Term Debt	7,908	9,379	11,338	9,495	198
Current Portion of Long Term Debt / Capital Leases	564	6,941	1,746	2,544	1,930
Income Taxes Payable	622	722	945	3,382	3,360
Other Current Liabilities	3,258	3,518	5,610	0	0
*Total Current Liabilities*	*24,282*	*30,901*	*30,958*	*30,717*	*19,985*
Long Term Debt	21,360	20,652	23,581	23,375	35,976
Deferred Income Tax	10,902	10,752	11,805	12,015	12,354
Minority Interest	324	283	290	0	0
Pension Benefits - Underfunded	6,616	5,314	3,658	0	0
Other Long Term Liabilities	3,573	3,832	4,206	5,147	4,472
*Total Long Term Liabilities*	*42,775*	*40,833*	*43,540*	*40,537*	*52,802*
*Total Liabilities*	*67,057*	*71,734*	*74,498*	*71,254*	*72,787*
Owner's Equity
Redeemable Convertible Preferred Stock	1,277	1,324	1,366	1,406	1,451
Common Stock	4,008	4,007	4,002	3,990	3,976
Additional Paid-In Capital	61,697	61,118	60,307	59,030	57,856
Retained Earnings	64,614	57,309	48,986	41,797	35,666
Less: Treasury Stock and Other	70,481	60,659	45,167	39,463	36,041
*Total Equity*	*61,115*	*63,099*	*69,494*	*66,760*	*62,908*
*Total Liabilities & Shareholders' Equity*	*128,172*	*134,833*	*143,992*	*138,014*	*135,695*
Total Common Shares Outstanding	2,843.47	2,917.04	3,032.72	3,131.95	3,178.84