Question

create a proforma balance sheet and incomes statement; and use them to estimate external funds needed.

Can please put it in excel if possible

Sales		5,000,000
Cost of Goods Sold		2,000,000
Gross Profit		3,000,000
Operating Expenses		1,000,000
Depreciation		500,000
EBIT		1,500,000
Interest Expense		100,000
EBT		1,400,000
Taxes		420,000
Net Income		980,000
Dividends		588,000
Additional Retained		392,000
Current Assets
Cash		500,000
Account Receivable		600,000
Inventory		1,000,000
Total		2,100,000
Net Fixed Assets		3,000,000
Total Assets		5,100,000
Current Liabilities
Accounts Payable		250,000
Accruals		350,000
Notes Payable		900,000
Total		1,500,000
Long-term Debt		1,200,000
Total Liabilities		2,700,000
Common Stock		1,700,000
Retained Earnings		700,000
Total Equity		2,400,000
Total Liabilities and Equity		5,100,000
Assumptions:
Sales growth = 20%
COGS as a percentage of sales increases by 200 bps
Operating Expenses as a percentage of sales decreases by 100 bps
Cash, Accountes Receivable, Inventory and net fixed assets remain the same percent of sales
Accounts Payable and Accruals remain the same percent of sales
The tax rate and dividend payout rate do not change
No change in notes payable, long-term debt or common stock
Depreciation increases by 5%

Answer 1

create a proforma balance sheet and incomes statement; and use them to estimate external funds needed.

EFN = 6120000 - 2820000 -2880200 = 419800

working:

document model

INCOME STATEMENT	Current Year	Basis for projections	Projections before raising AFN
Sales	5000000	+20%	6000000
COGS (2/5) = 40%	2000000	40% + 200bps of sales = 42%	2520000
Gross Profit	3000000		3480000
Operating Expenses (1/5 = 0.2 = 20%)	1000000	20% - 100bps of sales = 19%	1140000
Depreciation	500000	+5%	525000
EBIT	1500000		1815000
Interest Expense	100000		100000
EBT	1400000		1715000
Taxes @30%	420000		514500
Net Income	980000		1200500
Dividends (588000/980000= 60%)	588000		720300
Additional Retained =40%	392000		480200
BALANCE SHEET
Total Current assets	2100000	2100000/5000000 = 0.42 = 42% of sales	2520000
Fixed assets-Net	3000000	3000000/5000000 =60% of sales	3600000
Total assets	5100000		6120000
Current liabilities:
Accounts payable	250000	250000/5000000 =5% of sales	300000
Accruals	350000	350000/5000000 =7% of sales	420000
Note payable	900000		900000
Total	1500000		1620000
Long term debt	1200000		1200000
Total Liabilities	2700000		2820000
Common stock	1700000		1700000
Retained earnings	700000	+480200	1180200
Total Equity	2400000		2880200
		EFN = 6120000 - 2820000 -2880200	419800
Total Liabilities and Equity	5100000		6120000

EFN = 6120000 - 2820000 -2880200 = 419800