Question

*PLEASE PROVIDE ALL SOLUTIONS USING MICROSOFT EXCEL WITH ANY RELEVANT FORMULAS, thank you!*

Financial information on AAA Ltd. is shown below.

AAA Ltd. Income Statement
For the Year Ended December 31st,
	2018	2017
Sales	4,215,750	3,850,000
Cost Of Goods Sold	2,178,700	2,016,320
Other Expenses	1,005,200	986,500
Depreciation	9,800	8,550
Earnings Before Interest and Taxes	1,022,050	838,630
Interest Expense	56,735	46,870
Earnings Before Taxes	965,315	791,760
Taxes (30%)	289,595	237,528
Net Income	$ 675,721	$ 554,232

AAA Ltd. Balance Sheet
As at December 31st,
ASSETS	2018	2017
Cash & Equivalent	50,750	23,180
Short-term investments	202,834	186,014
Inventories	1,458,841	1,355,047
Accounts Receivable	218,500	131,221
Total Current Assets	1,930,925	1,695,462
Prop, Plant & Equip - Net	2,154,301	1,940,594
Total Assets	$ 4,085,226	$ 3,636,056
LIABILITIES & EQUITY
Accounts Payable	267,566	258,151
Notes Payable	82,823	62,310
Accruals	58,559	31,210
Short-term Debt	314,469	317,416
Total Current Liabilities	723,417	669,087
Long-Term Debt	833,547	844,139
Total Liabilities	1,556,964	1,513,226
Common Share Capital	650,000	650,000
Retained Earnings	1,878,263	1,472,830
Total Equity	2,528,263	2,122,830
Total Liabilities and Equity	$ 4,085,226	$ 3,636,056

In addition to the AAA Ltd. financial statements, you are given more information as follows.

Sales are forecast to increase by 10% in 2019.

Notes Payable, short-term Debt, Long-term Debt, and Common Share Capital will not change. Net Plant and Equipment is forecasted to be $2,500,000 next year. Short-term investments are expected to be $250,000.

In 2019, the company’s dividend payout ratio will be 40%.

In 2019, cost of goods sold is expected to be 50% of sales. Other expenses will be 25% of sales. Depreciation expense in 2019 is expected to be $11,000.

Cash is expected to be 1% of sales, and inventories will be 40% of sales. Accounts receivable will be 4% of sales. Accounts payable will be 4% of sales. Accruals will be 1% of sales.

The company is expected to pay 6% per year compounded annually on its short-term debt and 8% per year compounded annually on its long-term debt. The interest expense on the short-term debt in 2019 is calculated as interest rate on short-term debt * amount of short-term debt outstanding at the end of 2018. The interest expense on the long-term debt is calculated as interest rate on long-term debt * amount of long-term debt outstanding at the end of 2018.

The company’s tax rate is 30%.

Based on the information provided you are to:

1. Complete the pro-forma income statement and balance sheet for 2019.
2. Calculate the amount of Additional Funds Needed in 2019.

Answer 1

a)		2018	Basis for projections	PROFORMA 2019
	INCOME STATEMENT
	Sales	4215750	+10%	4637325
	Cost of goods sold	2178700	50% of sales	2318663
	Other expenses	1005200	25% of sales	1159331
	Depreciation	9800		11000
	EBIT	1022050		1148331
	Interest expense	56735	314469*6%+833547*8%	85552
	Earnings before taxes	965315		1062779
	Taxes at 30%	289595		318834
	Net income	675721		743946
	Dividends		40%	297578
	Addition to retained earnings			446367
	BALANCE SHEET
	Cash & Equivalent	50,750	1.0% of sales	46373
	Short term investments	2,02,834		250000
	Accounts Receivable	2,18,500	4.0% of sales	185493
	Inventories	14,58,841	40.0% of sales	1854930
	Total current assets	19,30,925		2086796
	PP& E Net	21,54,301		2500000
	Total assets	40,85,226		4586796
	Accounts payable	2,67,566	4.0% of sales	185493
	Notes payable	82,823		82823
	Accruals	58,559	1.0% of sales	46373
	Short term debt	3,14,469		314469
	Total current liabilities	7,23,417		629158
	Long term debt	8,33,547		833547
	Total liabilities	15,56,964		1462705
	Common share capital	6,50,000		650000
	Retained earnings	18,78,263	+446367	2324630
	Total equity	25,28,263		2974630
	Total liabilities and Equity	40,85,227		4437336
b)			ADDITIONAL FUNDS NEEDED	149461