Question

12-7 Upton Computers makes bulk purchases of small computers, stocks them in conveniently located warehouses, ships them to its chain of retail stores, and has a staff to advise customers and help them set up their new computers. Upton’s balance sheet as of December 31, 2013, is shown here (millions of dollars):

Cash	$3.55		Accounts payable	11.75
Receivable	32.00		Notes payable	15.25
Inventories	54.00		Line of credit	0.00
Total current assets	89.55		Accruals	9.50
Net fixed assets	48.00		Total current liabilities	36.50
			mortgage	6.00
			Common stock	14.00
			Retained earnings	81.05
Total assets	137.55		Total liabilities and equity	137.55

Sales for 2013 were $367.00 million and net income for the year was $19.15 million, so the firm’s profit margin was 5.2180%. Upton paid dividends of $4.20 million to common stockholders, so its payout ratio was 41.50%. Its tax rate was 40%, and it operated at full capacity. Assume that all assets/sales ratios, spontaneous liabilities/sales ratios, the profit margin, and the payout ratio remain constant in 2014.

b. Using the AFN equation, determine Upton’s self-supporting growth rate. That is, what is the maximum growth rate the firm can achieve without having to employ nonspontaneous external funds?

Answer 1

Formula sheet

A	B	C	D	E	F	G	H	I	J	K
2
3		Additional fund needed (AFN) for firm working at full capacity can be calculated as follows:
4
5
6
7		Where,
8		A0 = Total assets if the firm is working at full capacity
9		?S/S0 = percentage increase in sales i.e. change in sales divided by current sales
10		Lo = current level of Spontaneous liabilities (account payables, accrued liaibilities etc.)
11		S1 = new level of sales
12		PM = profit margin
13		b = retention rate = 1 – payout rate
14
15		Goal seek function can be used to find the growth rate at which external fund needed is zero.
16		Current Net Sales, NS0	367
17		Total Assets, TA0	137.55
18		Account Payable, AP0	11.75	(As Debt by equity is 1 and assuming debt is accounts payables only)
19		Accrrued Liabilities, AL0	9.5
20		Net Income, NI0	19.15
21		Retention ratio, RR0	=1-41.5%	(1-Payout Ratio)
22
23		Next Period Sales Growth, g	0.106594130674209
24		Next year net sales, NS1	=D16*(1+D23)	=D16*(1+D23)
25		Change in net sales, ?NS = NS1-NS0	=D24-D16
26
27		Change in Total Assets, ?TA = (TA0 / NS0) * ?NS	=(D17/D16)*D25
28		Change in Spontaneous Liability, ?(AP + AL)= ((AP0 + AL0)/ NS0) * ?NS	=((D18+D19)/D16)*D25
29		Addition to retained earnings = (NS1 * (NI0 / NS0) * RR0)	=(D24*(D20/D16)*D21)
30
31		Additional Fund Needed	=(?TA) - ? (AP+AL) - (addition to Retained earnings)
32			=D27-D28-D29	=D27-D28-D29
33
34		Hence self-supporting growth rate is	=D23
35

Goal Seek Settings