View the step-by-step solution to:

FINANCIAL FORECASTING 1. Using the data in the student spreadsheet file P&G.xlsx (to find the student spreadsheets for Financial Analysis with...

1. Using the data in the student spreadsheet file P&G.xlsx (to find the student spreadsheets for Financial Analysis with Microsoft Excel, fifth edition, go to www.cengage.com/finance/mayes) 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. 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 expectation?
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?

2. Use the same data as in Problem 1.
a. Recalculate the percentage of sales income statement, but this time use the TREND function to forecast depreciation expense, other income, and interest expense.
b. Recalculate the percentage of sales balance sheet, but this time use the TREND function to forecast cash, property plant and equipment (gross), 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?

FINANCIAL FORECASTING 1. Using the data in the student spreadsheet file P&G.xlsx (to find the student spreadsheets for Financial Analysis with Microsoft Excel, fifth edition, go to www.cengage.com/finance/mayes) 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. 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 expectation? 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? 2. Use the same data as in Problem 1. a. Recalculate the percentage of sales income statement, but this time use the TREND function to forecast depreciation expense, other income, and interest expense. b. Recalculate the percentage of sales balance sheet, but this time use the TREND function to forecast cash, property plant and equipment (gross), 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?
Background image of page 1
The Procter & Gamble Company 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 Source: Procter & Gamble
Background image of page 1
The Procter & Gamble Company 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 Lease 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 Source: Procter & Gamble
Background image of page 2
Sign up to view the entire interaction

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question