you must link to cells within the worksheet. Do not type numbers into your equations/answers. Use the given information to: 1. Construct the company's current balance sheet. I made the vellow cells in the balance sheet slightly lighter so it is easier to tell where all you need to enter a value. 2) Calculate the financial ratios listed in the spreadsheet. Calculate the financial ratios listed in Column D. Use the DuPont Identity to calculate ROA and ROE. 4) Construct a pro forma balance sheet based on the company's sustainable growth rate. Only assume that costs and assets grow proportional to sales. 5) 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? 6) 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 ony 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. Output areas: Dak's Cowboy Emporium Dak's Cowboy Emporium Growh in assets (Answer =$24,800 Intemal equity financing Extemal financing needed Extemal debt (excludes A/P) New dividend (Answer about \$7,500) New payout ratio New SGR you must link to cells within the worksheet. Do not type numbers into your equations/answers. Use the given information to: 1. Construct the company's current balance sheet. I made the vellow cells in the balance sheet slightly lighter so it is easier to tell where all you need to enter a value. 2) Calculate the financial ratios listed in the spreadsheet. Calculate the financial ratios listed in Column D. Use the DuPont Identity to calculate ROA and ROE. 4) Construct a pro forma balance sheet based on the company's sustainable growth rate. Only assume that costs and assets grow proportional to sales. 5) 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? 6) 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 ony 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. Output areas: Dak's Cowboy Emporium Dak's Cowboy Emporium Growh in assets (Answer =$24,800 Intemal equity financing Extemal financing needed Extemal debt (excludes A/P) New dividend (Answer about \$7,500) New payout ratio New SGR
