Project #64370 - Finance: Financial forecasting

There is already a template set up for the assignment. As it states in the directions you will just be making changes to the excel spreadsheet and doing write ups about the changes that are being made. 





1. Provide the key ratios and measures of performance and key output values in the blue shaded areas of the provided spreadsheet.  Only enter formulas and cell references.


2. Complete the data tables included in the spreadsheet template.  Generate a XY graph (with connecting lines) for each data table, and label the axes.  Write a paragraph analyzing each table and describing the results.


3. In general, explain the relationship between sales growth rate and DFN?  Operating profit margin and DFN?  Interest rates and DFN?  Operating profit margin and ROE?


4. Explain why the base case leads to an expected decline in the current ratio.


5. Using the base case values, what is the firm’s sustainable growth rate?  What is the effect on the sustainable growth rate of an increase in the operating profit margin?  Demonstrate this by assuming operating profit margins from 25% to 50% in increments of 5%.  In each case, calculate the sustainable growth rate.  Show this relationship in tabular and graph form.


6. Using the base case values,

a. What must Google’s operating profit margin be for the firm:

·      to realize an ROE of 25%?

·      to achieve a stock price of $95.00?

·      to result in a DFN of exactly zero?


c. What growth rate in sales will result in free cash flow for 2015 of exactly zero?


7. The base case values lead to an estimate of free cash flow of -$490 million, and as the growth rate estimate is increased, free cash flow becomes increasingly negative.  Does this mean that growth is bad?  Explain why this negative relationship between growth and free cash flow exists.  Would this same relationship hold if the firm were operating well below capacity?  Explain with reference to your data table and graph.


8. Google’s debt convenants require it to maintain a TIE of at least 5.5x.  Referring to your data table and graph, what combinations of growth rate and debt financing percentage would be forecasted to cause the firm’s TIE to fall below 5.5x.  Explain why this is so.


9. According to the data tables, what combination of growth rate and operating profit margin results in the highest free cash flow?  Does this mean that the firm should avoid high rates of sales growth?  Explain.


10. Suppose that the firm’s discretionary financing consists of  80% debt financing and 20% equity financing.  

a. What growth rate in sales will result in the firm’s times interest earned ratio being equal to its current value (5.73)?  Do you think that the restriction on the firm’s TIE ratio is a matter of concern?  Explain.

b. What growth rate in sales will result in the firm’s debt ratio being equal to 50%?



a. What does it mean if a company has a DFN that is negative?

b. In the spreadsheet that is provided for this assignment, when the company has negative DFN, what are the extra funds used for?

Subject General
Due By (Pacific Time) 03/30/2015 11:00 pm
Report DMCA

Chat Now!

out of 1971 reviews

Chat Now!

out of 766 reviews

Chat Now!

out of 1164 reviews

Chat Now!

out of 721 reviews

Chat Now!

out of 1600 reviews

Chat Now!

out of 770 reviews

Chat Now!

out of 766 reviews

Chat Now!

out of 680 reviews
All Rights Reserved. Copyright by - Copyright Policy