Circular iterations and using the max function.

[Satterfield]

Hey guys, I really don't know how this works but I have the project where we are using a circular reference in order to balance a balance sheet and I have a few questions as to how it works. I already have the answer/ the correct formulas but I dont know why they work.

basically I want to know how the purple highlighted cells are affecting the rest of the document

Copy of Fin Stmt Project Satterfield David Resubmit.xlsx
	A	B	C	D	E	F	G	H	I
1		WK Corp
2		Do not insert, delete, move or name any rows, columns, or range.				Save your work often.
3
4		Do all calculations in the cells of the provided items. Do not add any additional items.
5
6		Set calculation to Automatic and Enable Iteration (File, Options, Formulas) to handle circular references.
7
8		To save time, build your formulas for 20x4 so that you can copy them over to 20x5.
9		Note that totals for actual results may appear off due to rounding.
10
11	IS	INCOME STATEMENT
12		(in thousands of $s)			Actual	Actual	Actual	Forecast	Forecast
13		For the years ended	Assumptions		20x1	20x2	20x3	20x4	20x5
14
15		Net revenues	Sales Growth	6.50%	8,816,213	9,551,415	9,883,078	$ 10,525,478.00	$ 11,209,634.07
16		Cost of Goods Sold	% of Sales	42.40%	3,725,529	4,088,731	4,168,000	$ 4,462,802.67	$ 4,752,884.85
17		Gross profit			5,090,684	5,462,683	5,715,078	$ 6,062,675.33	$ 6,456,749.23
18
19		Selling, general & administration expense	% of Sales	19.80%	1,887,005	1,756,906	2,039,037	$ 2,084,044.64	$ 2,219,507.55
20		Research & development expense	% growth	8.10%	726,623	747,580	866,232	$ 936,396.81	$ 1,012,244.95
21		Advertising expenses	% of Sales	11.70%	1,057,284	1,178,867	1,282,797	$ 1,231,480.93	$ 1,311,527.19
22		Amortization of intangibles	see Additional Assumptions		30,208	26,536	28,130	33,200	36,700
23		Goodwill impairment expense	see Additional Assumptions		166,787	0	128,638	23,500	0
24		Restructuring expense	Fixed	6,500	0	5,464	6,187	6,500	6,500
25		Other operating expense (income)	Fixed	34,000	31,568	29,499	34,225	34,000	34,000
26		Operating earnings (EBIT)			1,191,209	1,717,832	1,329,831	1,713,553	1,836,270
27
28		Interest expense	% of avg Debt for the year	5.90%	220,491	215,793	207,044	205,774.03	211,144.99
29		Interest income	% of avg Cash for the year	0.50%	4,105	4,397	4,653	3851.311933	4075.333514
30		Foreign exchange losses (gains)	% of Sales	0.27%	22,991	24,064	27,058	$ 28,418.79	$ 30,266.01
31		Other income (expense), net	Fixed	12,500	14,256	12,987	12,501	12,500	12,500
32		Earning before taxes (EBT)			966,089	1,495,359	1,112,883	$ 1,495,711.45	$ 1,611,433.88
33
34		Income taxes	Tax Rate	28.80%	230,272	346,084	255,091	$ 430,765	$ 464,093
35		Net Income (Loss)			735,817	1,149,274	857,792	$ 1,064,946.55	$ 1,147,340.92
36
37		Common stock dividends declared	% growth	5.20%	504,546	538,081	579,536	$ 609,672.31	$ 641,375.27
38
39
40	BS	BALANCE SHEET
41		(in thousands of $s)			Actual	Actual	Actual	Forecast	Forecast
42					20x1	20x2	20x3	20x4	20x5
43		ASSETS	Assumptions
44		Cash & cash equivalents	plug, min. % of Sales	7.50%	670,032	725,908	751,114	$ 789,410.85	$ 840,722.56
45		Accounts receivable, net	% of Sales	19.70%	1,794,152	1,958,594	1,829,121	$ 2,073,519.17	$ 2,208,297.91
46		Inventories	% COGS	25.70%	894,865	1,017,971	953,830	$ 1,146,940.29	$ 1,221,491.41
47		Prepaid expenses	% of Sales	8.20%	733,156	767,017	924,819	$ 863,089.20	$ 919,189.99
48		Other current assets	% growth	6.80%	507,613	544,299	580,148	$ 619,598.47	$ 661,731.17
49		Total current assets			4,599,818	5,013,788	5,039,032	5,492,558	5,851,433
50
51		Property, plant & equipment, net	see Additional Assumptions		1,332,340	1,382,981	1,412,850	1,503,924.48	1,601,301.81
52		Goodwill	see Additional Assumptions		983,169	950,210	868,208	851,508	859,508
53		Other intangibles, net	see Additional Assumptions		254,589	239,009	216,525	189,325	174,625
54		Investments in unconsolidated affiliates	Fixed	520,000	429,030	498,446	506,641	520,000	520,000
55		Other fixed assets	% of Sales	8.30%	700,281	699,221	858,849	$ 873,614.67	$ 930,399.63
56
57		Total assets			8,299,226	8,783,655	8,902,105	9,430,930.68	9,937,268.02
58
59		LIABILITIES
60		Short-term borrowings	plug		430,852	246,917	428,112	$ 707,265.15	$ 1,089,178.05
61		Commercial paper	fixed	250,000	254,160	258,480	260,400	250,000	250,000
62		Current portion of long-term debt	see Additional Assumptions		138,000	285,300	218,600	350,000	250,000
63		Accounts payable	% of COGS	8.80%	371,029	346,066	413,612	$ 392,726.64	$ 418,253.87
64		Accrued liabilities	% of Sales	11.25%	954,135	1,045,364	1,105,798	$ 1,184,116.28	$ 1,261,083.83
65		Total current liabilities			2,148,176	2,182,127	2,426,521	2,884,108.06	3,268,515.75
66
67		Long-term debt	see Additional Assumptions		2,855,507	2,755,507	2,505,507	2,255,507	2,005,507
68		Other liabilities	Fixed	286,646	264,574	286,646	286,646	286,646	286,646
69		Total liabilities			5,268,257	5,224,280	5,218,674	5,426,261	5,560,669
70
71		EQUITY
72		Common stock at par	Fixed	278,207	278,207	278,207	278,207	278,207	278,207
73		Additional paid-in capital	prior year + SEO (see Add. Assump.)		1,221,821	1,282,912	1,297,025	1,297,025	1,297,025
74		Retained earnings			2,651,496	3,262,690	3,540,946	$ 3,996,220	$ 4,502,185.92
75		Accumulated other comprehensive earnings (loss)	prior year + Other (see Add. Assump.)		-240,894	-233,486	-231,728	-247,412	-263,096
76		Treasury stock, at cost	prior year + Stock Repur. (see Add. Assump.)		879,661	1,030,948	1,201,018	1,319,370	1,437,722
77		Total shareholders' equity			3,030,969	3,559,375	3,683,431	$ 4,004,669.62	$ 4,376,599.27
78
79		Total Liabilities and Equity			8,299,226	8,783,655	8,902,105	9,430,931	9,937,268.02
80
81
82					Actual	Actual	Actual	Forecast	Forecast
83	Add. Assump.		Additional Assumptions		20x1	20x2	20x3	20x4	20x5
84		PP&E Assumptions
85		Capital Expenditures	% of Sales	3.75%				$ 394,705.43	$ 420,361.28
86		Depreciation	% of prior year PP&E net	21.25%				300,230.54	319,583.95
87		Sales of PP&E (book value)	Fixed	3,400				3,400	3,400
88
89		Goodwill
90		Addition to Goodwill from current year acquisitions	Fixed					18,800	23,500
91		Reduction in Goodwill from current year divestitures	Fixed					12,000	15,500
92		Goodwill impairment expense	10-K					23,500	0
93
94		Other intangibles, net
95		Amortization of other intangibles	10-K					33,200	36,700
96		New Other intangilble assets	Fixed					20,500	22,000
97		Sales of Other intangibles (book value)	Fixed					14,500	0
98
99		Long-Term Debt Assumptions
100		Current portion of long-term debt	10-K					350,000	250,000
101		Issuance of new long-term debt						250,000	0
102		Early repayment of long-term debt						150,000	0
103
104		Common Equity Assumptions
105		Seasoned Equity Offerings (SEO)						0	0
106		Stock Repurchases	Based on prior year					118,352	118,352
107		Other (e.g., stock-based compensation, other gain (loss))	Based on prior year					-15,684	-15,684
108
109
110	Balancing
111		Trial Assets, excluding Asset plug (Cash)						$ 8,641,519.83	$ 9,096,545.46
112		Mininum Cash						$ 789,410.85	$ 840,722.56
113		Total Trial Assets						$ 9,430,930.68	$ 9,937,268.02
114		Trial Liabilities & Equity, excluding Liab. Plug (Short-term borrowings)						8,723,666	8,848,090
115		Plug for balancing						$ 707,265.15	$ 1,089,178.05
116
117
118
119	Notes
120		Note 1: Interest bearing debt includes Short-term borrowings, Commercial paper, Current portion of long-term debt, and Long-term debt.
121
122
123		SENSITIVITY ANALYSIS
124		How do the financing needs, as measured by Short-Term Borrowings, vary with Sales Growth & COGS Percentage?
proforma

Cell Formulas
Range	Formula
H15:I15,H48:I48,H37:I37,H20:I20	H15	=G15*(1+$D15)
H16:I16,H85:I85,H64:I64,H55:I55,H45:I45,H47:I47,H30:I30,H19:I19,H21:I21	H16	=H$15*$D16
H17:I17,H115:I115	H17	=H15-H16
H22:I22	H22	=H$95
H23:I23	H23	=H92
H24:I25,H72:I72,H68:I68,H61:I61,H54:I54,H31:I31	H24	=$D24
H26:I26	H26	=H15-H16-H19-H20-H21-H22-H23-H24-H25
H28:I28	H28	=(SUM(G60:H62,G67:H67)/2)*$D$28
H29:I29	H29	=(G44+H44)/2*$D$29
H32:I32	H32	=H26-H28+H29-H30+H31
H34:I34	H34	=H$32*$D34
H35:I35	H35	=H32-H34
H44:I44	H44	=MAX(H112,H112-H115)
H46:I46,H63:I63	H46	=$D46*H$16
H49:I49,H65:I65	H49	=SUM(H44:H48)
H51:I51	H51	=G51-H86+H85-H87
H52	H52	=$G52+H$90-H$91-H$92
I52	I52	=H52+I90-I91-I92
H53:I53	H53	=G$53+H$96-H$97-H$95
H57:I57	H57	=H49+SUM(H51:H55)
H60:I60	H60	=MAX(0,H115)
H62:I62	H62	=H$100
H67:I67	H67	=G67-H100+H101-H102
H69:I69	H69	=SUM(H65:H68)
H73:I73	H73	=G73+H105
H74:I74	H74	=G74+H35-H37
H75:I75	H75	=G$75+H$107
H76:I76	H76	=G$76+H$106
H77:I77	H77	=SUM(H72:H75)-H76
H79:I79	H79	=H77+H69
H86:I86	H86	=G$51*$D86
H111:I111	H111	=SUM(H45:H48,H51:H55)
H112:I112	H112	=$D$44*H15
H113:I113	H113	=SUM(H111:H112)
H114:I114	H114	=SUM(H61:H64,H67:H68,H77)