Create a chart of cash vs sales and add a linear trend

Question

Problem One:

Using the data in the student spreadsheet file P&G.xlsx forecast the June 30, 2011, income statement and balance sheet for Procter and 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.

(5)  The common dividend payout ratio will be 42% of income available to common stockholders.

Questions:

a.  What is the discretionary financing needed in 2011?  Is this a surplus or deficit?

b.  Assume that the DFN will be absorbed by long-term debt and that the total interest rate is 4.50% of LTD.  Set up an iterative worksheet to eliminate it.

c.  Create a chart of cash vs. sales and add a linear trend line.  Is the cash balance a consistent percentage of sales?  Does the relationship fit your expectations?

d.  Use the regression tool to verify your results from part c.  Is the trend statistically significant?  Use at least three methods to show why or why not.

e.  Turn off iteration, and use the Scenario Manager to set up three scenarios:

1.)  Best Case - Sales are 5% higher than expected.

2.)  Base Case - Sales are exactly as expected.

3.)  Worst Case - Sales are 5% less than expected.

What is the DFN under each scenario?

	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

Problem Two

Using the same data as in Problem 1:

Questions:

a. Recalculate the percentage of sales income statement, but this time use the TREND function to forecast other income and interest expense.

b. Recalculate 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 forecast in this way?