Condensed Balance Sheet Forecasting Example 2014.11.17

	B	C	D	E	F	G	H
1	Frank's Vault				Revision	2014.11.17
2	Condensed Balance Sheet Forecasting Example				Author	Frank Luengo
3
4	Income Statement Assumptions
5	Sales Growth (by year)	2%
6	EBITDA Rate (As % of sales)	22%
7	Tax Rate	35%
8	EBIT Rate (As % of sales)	14.00%
9	Blended Pre Tax Cost of Debt	4.00%
10
11	Balance Sheet Assumptions
12	Net Working Capital (As % of sales)	7.00%
13	Net Long Term Assets Growth	2.00%
14	Payout Ratio (% of current earnings)	65.00%
15
16	Sales		$22,750.00	$23,205.00	$23,669.10	$24,142.48	<== F14*(1+$C$5)
17	Net Income		$1,781.00	$1,822.34	$1,864.51	$1,907.52	<== (G16$C$8-$C$9(G36+G35))*(1-$C$7)
18
19	Net Working Capital	% of Sales	12/31/2014	12/31/2015	12/31/2016	12/31/2017
20	Trade receivables	4.40%	$1,000.00	$1,020.00	$1,040.40	$1,061.21	<== $C21*G$17
21	+ Inventories	3.50%	$800.00	$816.00	$832.32	$848.97	<== $C22*G$17
22	+ Other Current Assets	1.30%	$300.00	$306.00	$312.12	$318.36	<== $C23*G$17
23	- Trade Payables	1.80%	$400.00	$408.00	$416.16	$424.48	<== $C24*G$17
24	- Other Payables	0.40%	$100.00	$102.00	$104.04	$106.12	<== $C25*G$17
25	- Other Current Liabilities	0.00%	$ -	$ -	$ -	$ -	<== $C25*G$17
26	=Net Working Capital	7.00%	$1,600.00	$1,632.00	$1,664.64	$1,697.93	<== SUM(G21:G23)-SUM(G24:G26)
27	Net Non-Current Assets	0.00%
28	Tangible Long Term Assets	70.30%	$16,000.00	$16,320.00	$16,646.40	$16,979.33	<== F29*(1+$C$13)
29	+ Intangible Long Term Assets	8.80%	$2,000.00	$2,000.00	$2,000.00	$2,000.00
30	+ Other Long Term Assets	0.00%	$ -
31	- Other Non-Current Liabilities (non-interest-bearing)	0.00%	$ -
32	=Net Non-current Assets	79.10%	$18,000.00	$18,320.00	$18,646.40	$18,979.30	<== G29+G30+G31-G32
33	=Net ASSETS	86.20%	$19,600.00	$19,952.00	$20,311.00	$20,677.30	<== G33+G27
34	Net Debt	0.00%
35	Current Debt	0.50%	$125.00	$127.50	$130.05	$132.65	<== G17*$C$36
36	+Long Term Debt	48.40%	$11,000.00	$11,000.00	$11,000.00	$11,000.00	<== Policy variable
37	-Cash	3.50%	$800.00	$1,088.32	$1,384.41	$1,688.42
38	=Net Debt	45.40%	$10,325.00	$10,039.20	$9,745.60	$9,444.20	<== G36+G37-G38
39	+Shareholders' Equity	40.80%	$9,275.00	$9,912.82	$10,565.40	$11,233.03	<== F40+G18*(1-$C$15)
40	=Total Net Capital	86.20%	$19,600.00	$19,952.00	$20,311.04	$20,677.26	<== G34
41
42	Cash Plug			$0.00	$0.00	$0.00	<== G41-(G39+G40)
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100