Question
The company needs you to help estimate its financing needs based on their target growth rate. As you work each step, you must link to
The company needs you to help estimate its financing needs based on their target growth rate. As you work each step, you must link to cells within the worksheet. Do not type numbers into your equations/answers. Assume all costs, current liabilities, and assets vary in proportion to sales, and the firm maintains a constant plowback ratio. Use the following information to:
Calculate the financial ratios listed below. a) 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). b) Name the corresponding financial ratios in Column D with the following names: TAT, PM, DE, ROA, ROE and SGR. Use the names to reference these cells in any equations c) 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 to prevent negative taxes.
Construct a pro forma balance sheet based on the company's sustainable growth rate (sheet will not balance since only costs, current liabilities, and assets are assumed to grow proportional to sales).
Use the pro forma balance sheet to calculate the external financing needs. a) Based on the assumptions of the SGR, what is the total growth in assets and the external financing needed? b) What is the internal equity financing and spontaneous (A/P) financing? NOTE: EFN plus the internal/spontanoues financing in this step should sum to the growth in assets. c) Based on the assumptions of the SGR, what is the external debt financing? I only need SGR, proforma income statement and balance sheet, External financing needed, Internal equity requirement.
Data Current Year
Sales $ 70,000
Total Costs (COGS & Admin.) 55,000
Cash 3,140
Accounts receivable 4,200
Inventory 6,500
Net plant and equipment 145,000
Accounts payable 2,600
Notes payable 5,700
Long-term debt 28,000
Common stock & Paid-in Capital 5,000
Retained earnings 117,540
Dividends Paid 4,200
Net income 11,850
Tax rate 21%
PLEASE show explanations in Excel and display the Excel formulas since this problem has to be formulated there.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started