Question

Doredo Steel Company is a wholesale steel distributor which purchase steel in carload lots and sells to several thousand steel users. The nature of the steel business requires that the company maintain large inventories to take care of customer requirements in the event of mill strikes or other delays. In examining records from 1992 to 2018, the company found consistent relationships among the following accounts as a percent of sales: Current assets 20% Net fixed assets 70% if Sales <$53 million 80%, otherwise. Accounts payable 8% Other current liab. 6% Profit margin 15% 2018 The company's sales for 2017 were $40 million. The company expects to grow by $6 million per year over the next 5 years. The company wants to project its financial requirements for each of the next 5 years, assuming the projected sales levels are achieved. Assume further that the company pays out 25 percent of earnings as dividends. Construct pro forma balance sheets for the end of each of the next 5 years (from 2019 to 2023), assuming that 20 percent by issuing long-term debt, and 80 percent by selling new common stock.

hey, this is a practice assignment that requires excel. If you could help me solve it, I'd be so stoked. Thanks!

Answer 1

Year	2018	2019	2020	2021	2022	2023
					(Thousands of dollars)
Sales	40000	46000	52000	58000	64000	70000
Current Asset	10000	9200	10400	11600	12800	14000
Fixed Asset	26000	32200	36400	46400	51200	56000
Total	36000	41400	46800	58000	64000	70000
Acct Payable	5000	3680	4160	4640	5120	5600
Other cur Liability	2500	2760	3120	3480	3840	4200
L/t debt	9000	4857	4599	5366	4958	4415
Common stock	14000	19428	18396	21464	19832	17660
Retained	5500	10675	16525	23050	30250	38125
Total	36000	41400	46800	58000	64000	70000
Profit Margin		6900	7800	8700	9600	10500
Payout		1725	1950	2175	2400	2625
Add to Retained Earning		5175	5850	6525	7200	7875