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

Reference no: EM131365150

Using the data in the 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 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?

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:

Reference no: EM131365150

Questions Cloud

Create a common-size income statement : Fill in the blanks on the financial statements for Winter Park Web Design with formulas. Some of the formulas will require links to data given in other worksheets in the workbook.

What was the height when the engines turned off : In the first 10 sec. what is the net acceleration the spaceship experienced. What was the height when the engines turned off? What was the acceleration did the spaceship's engines apply for the first 10 sec. (Different answer then question 1) If the ..

Summarize the general characteristics of your organization : The purpose of this discussion question is to share with your classmates the nature of your Scenario Generator Report, and for you to have the opportunity to compare ideas and understanding with your colleagues.Attach a copy of your Scenario Gener..

Calculate the maximum compression that spring reaches : There is no friction on the floor. Calculate the maximum compression that the spring reaches at its relaxed position if you fire a 0.5 kg weight at it with a velocity of 20 meters per second.

Create a chart of cash vs. sales and add a linear trend line : 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.

What is the work done on the gas : A gas is compressed at a constant pressure of 0.800 atm from 8.00 L to 1.00 L. In the process, 400 J of energy leaves the gas by heat. (a) What is the work done on the gas? (b) What is the change in its internal energy?

Examine four federal equal employment opportunity laws : Examine four (4) federal equal employment opportunity laws. Suggest the primary manner in which each law influences fair employment practices within the organization where you currently work or an organization for which you have worked in the past

What language that differentiates them from most canadians : Language is frequently a key component of identity for many people. The Quebecois, an ethnic group in Quebec, speak what language that differentiates them from most other Canadians?

How would you describe the difference to a friend : If health and wellness are more than the absence of disease, how are these terms defined? How would you describe the difference to a friend?Studies indicate Americans are less healthy than their comparable peers in other countries. What are the reas..

User Account

All Pages