Financial Forecasting in Excel
Using the data in te student spreadsheet file P&G/xlsx (to find the student spreadsheets for Financial Analysis with Microsoft Excel, sixth edition, go to www.cengage.com/finance/mayes) forecast the June 30, 2011, income statement and balance sheet for Procter & Gamble. Use the % 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 itme that changes with sales will be the 5-year average % 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. Asumme that the DFN will be absorbed by long-term debt and tht the total interest rate is 4.5% 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 % 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 thre methods to show why or why not.