Using the data in the student spreadsheet file P&G. xIsx (to find the student spreadsheets for Financial Analysis with Microsoft Excel, sixth edition,) 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 expectations?