Information about Dak’s Cowboy Emporium is provided below. The company needs you to help estimate the external financing needs based on their target growth rate. Assume that only costs and assets vary in proportion to sales, and the firm maintains a constant plowback ratio. As you work each step, you must link to cells within the worksheet. Do not type numbers into your equations/answers.
Use the given information to:
Construct the company’s current balance sheet. I made the yellow cells in the balance sheet slightly lighter so it is easier to tell where all you need to enter a value.
Calculate the financial ratios listed in the spreadsheet.
Use the Name Manager to point the named cell NetIncome (currently pointing to sales revenue) to the net income amount for the current year provided in Column D. Use the name to reference this cell in any equations (NOTE: do not name cells unless instructed).
Name the corresponding financial ratios in Column D with the following names: TAT, PM, DE, ROA, ROE, SGR, and IGR. Use these names to reference these cells in any equations, but do not name other cells.
Calculate the financial ratios listed in Column D. Use the DuPont Identity to calculate ROA and ROE.
Construct a pro forma income statement using the company’s sustainable growth rate as the projected growth in sales. Use the MAX function in the equation to prevent negative taxes.
Construct a pro forma balance sheet based on the company’s sustainable growth rate. Only assume that costs and assets grow proportional to sales.
Use the pro forma balance sheet to calculate the external financing needs.
Based on the this growth rate, what is the total growth in assets?
How much is financed by internal equity?
What is the external financing needed?
Based on the assumptions of the SGR, what is the amount of external debt financing?
Assume the company wants to grow at this rate (the SGR you calculated), but without using any external financing.
What dividend ($ amount) would allow the company to grow at this rate this year, but without using any external financing?
What would the new payout ratio be based on that dividend?
Assume the ROE does not change but the payout policy does. Given the new payout ratio, what would the new SGR be? Hint: SGR will be higher than before.
