Circular iterations and using the max function.

Satterfield

New Member
Joined
Feb 10, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
ABCDEFGHI
1WK Corp
2Do not insert, delete, move or name any rows, columns, or range.Save your work often.
3
4Do all calculations in the cells of the provided items. Do not add any additional items.
5
6Set calculation to Automatic and Enable Iteration (File, Options, Formulas) to handle circular references.
7
8To save time, build your formulas for 20x4 so that you can copy them over to 20x5.
9Note that totals for actual results may appear off due to rounding.
10
11ISINCOME STATEMENT
12(in thousands of $s)ActualActualActualForecastForecast
13For the years endedAssumptions20x120x220x320x420x5
14
15Net revenuesSales Growth6.50%8,816,2139,551,4159,883,078$ 10,525,478.00$ 11,209,634.07
16Cost of Goods Sold% of Sales42.40%3,725,5294,088,7314,168,000$ 4,462,802.67$ 4,752,884.85
17Gross profit5,090,6845,462,6835,715,078$ 6,062,675.33$ 6,456,749.23
18
19Selling, general & administration expense% of Sales19.80%1,887,0051,756,9062,039,037$ 2,084,044.64$ 2,219,507.55
20Research & development expense% growth8.10%726,623747,580866,232$ 936,396.81$ 1,012,244.95
21Advertising expenses% of Sales11.70%1,057,2841,178,8671,282,797$ 1,231,480.93$ 1,311,527.19
22Amortization of intangiblessee Additional Assumptions30,20826,53628,13033,20036,700
23Goodwill impairment expensesee Additional Assumptions166,7870128,63823,5000
24Restructuring expenseFixed6,50005,4646,1876,5006,500
25Other operating expense (income)Fixed34,00031,56829,49934,22534,00034,000
26Operating earnings (EBIT)1,191,2091,717,8321,329,8311,713,5531,836,270
27
28Interest expense% of avg Debt for the year5.90%220,491215,793207,044205,774.03211,144.99
29Interest income% of avg Cash for the year0.50%4,1054,3974,6533851.3119334075.333514
30Foreign exchange losses (gains)% of Sales0.27%22,99124,06427,058$ 28,418.79$ 30,266.01
31Other income (expense), netFixed12,50014,25612,98712,50112,50012,500
32Earning before taxes (EBT)966,0891,495,3591,112,883$ 1,495,711.45$ 1,611,433.88
33
34Income taxesTax Rate28.80%230,272346,084255,091$ 430,765$ 464,093
35Net Income (Loss)735,8171,149,274857,792$ 1,064,946.55$ 1,147,340.92
36
37Common stock dividends declared% growth5.20%504,546538,081579,536$ 609,672.31$ 641,375.27
38
39
40BSBALANCE SHEET
41(in thousands of $s)ActualActualActualForecastForecast
4220x120x220x320x420x5
43ASSETSAssumptions
44Cash & cash equivalentsplug, min. % of Sales7.50%670,032725,908751,114$ 789,410.85$ 840,722.56
45Accounts receivable, net% of Sales19.70%1,794,1521,958,5941,829,121$ 2,073,519.17$ 2,208,297.91
46Inventories% COGS25.70%894,8651,017,971953,830$ 1,146,940.29$ 1,221,491.41
47Prepaid expenses% of Sales8.20%733,156767,017924,819$ 863,089.20$ 919,189.99
48Other current assets% growth6.80%507,613544,299580,148$ 619,598.47$ 661,731.17
49Total current assets4,599,8185,013,7885,039,0325,492,5585,851,433
50
51Property, plant & equipment, netsee Additional Assumptions1,332,3401,382,9811,412,8501,503,924.481,601,301.81
52Goodwillsee Additional Assumptions983,169950,210868,208851,508859,508
53Other intangibles, netsee Additional Assumptions254,589239,009216,525189,325174,625
54Investments in unconsolidated affiliatesFixed520,000429,030498,446506,641520,000520,000
55Other fixed assets% of Sales8.30%700,281699,221858,849$ 873,614.67$ 930,399.63
56
57Total assets8,299,2268,783,6558,902,1059,430,930.689,937,268.02
58
59LIABILITIES
60Short-term borrowingsplug430,852246,917428,112$ 707,265.15$ 1,089,178.05
61Commercial paperfixed250,000254,160258,480260,400250,000250,000
62Current portion of long-term debtsee Additional Assumptions138,000285,300218,600350,000250,000
63Accounts payable% of COGS8.80%371,029346,066413,612$ 392,726.64$ 418,253.87
64Accrued liabilities% of Sales11.25%954,1351,045,3641,105,798$ 1,184,116.28$ 1,261,083.83
65Total current liabilities2,148,1762,182,1272,426,5212,884,108.063,268,515.75
66
67Long-term debtsee Additional Assumptions2,855,5072,755,5072,505,5072,255,5072,005,507
68Other liabilitiesFixed286,646264,574286,646286,646286,646286,646
69Total liabilities5,268,2575,224,2805,218,6745,426,2615,560,669
70
71EQUITY
72Common stock at parFixed278,207278,207278,207278,207278,207278,207
73Additional paid-in capitalprior year + SEO (see Add. Assump.)1,221,8211,282,9121,297,0251,297,0251,297,025
74Retained earnings2,651,4963,262,6903,540,946$ 3,996,220$ 4,502,185.92
75Accumulated other comprehensive earnings (loss)prior year + Other (see Add. Assump.)-240,894-233,486-231,728-247,412-263,096
76Treasury stock, at costprior year + Stock Repur. (see Add. Assump.)879,6611,030,9481,201,0181,319,3701,437,722
77Total shareholders' equity3,030,9693,559,3753,683,431$ 4,004,669.62$ 4,376,599.27
78
79Total Liabilities and Equity8,299,2268,783,6558,902,1059,430,9319,937,268.02
80
81
82ActualActualActualForecastForecast
83Add. Assump.Additional Assumptions20x120x220x320x420x5
84PP&E Assumptions
85Capital Expenditures% of Sales3.75%$ 394,705.43$ 420,361.28
86Depreciation% of prior year PP&E net21.25%300,230.54319,583.95
87Sales of PP&E (book value)Fixed3,4003,4003,400
88
89Goodwill
90Addition to Goodwill from current year acquisitionsFixed18,80023,500
91Reduction in Goodwill from current year divestituresFixed12,00015,500
92Goodwill impairment expense10-K23,5000
93
94Other intangibles, net
95Amortization of other intangibles10-K33,20036,700
96New Other intangilble assetsFixed20,50022,000
97Sales of Other intangibles (book value)Fixed14,5000
98
99Long-Term Debt Assumptions
100Current portion of long-term debt10-K350,000250,000
101Issuance of new long-term debt250,0000
102Early repayment of long-term debt150,0000
103
104Common Equity Assumptions
105Seasoned Equity Offerings (SEO)00
106Stock RepurchasesBased on prior year118,352118,352
107Other (e.g., stock-based compensation, other gain (loss))Based on prior year-15,684-15,684
108
109
110Balancing
111Trial Assets, excluding Asset plug (Cash)$ 8,641,519.83$ 9,096,545.46
112Mininum Cash$ 789,410.85$ 840,722.56
113Total Trial Assets$ 9,430,930.68$ 9,937,268.02
114Trial Liabilities & Equity, excluding Liab. Plug (Short-term borrowings)8,723,6668,848,090
115Plug for balancing$ 707,265.15$ 1,089,178.05
116
117
118
119Notes
120Note 1: Interest bearing debt includes Short-term borrowings, Commercial paper, Current portion of long-term debt, and Long-term debt.
121
122
123SENSITIVITY ANALYSIS
124How do the financing needs, as measured by Short-Term Borrowings, vary with Sales Growth & COGS Percentage?
proforma
Cell Formulas
RangeFormula
H15:I15,H48:I48,H37:I37,H20:I20H15=G15*(1+$D15)
H16:I16,H85:I85,H64:I64,H55:I55,H45:I45,H47:I47,H30:I30,H19:I19,H21:I21H16=H$15*$D16
H17:I17,H115:I115H17=H15-H16
H22:I22H22=H$95
H23:I23H23=H92
H24:I25,H72:I72,H68:I68,H61:I61,H54:I54,H31:I31H24=$D24
H26:I26H26=H15-H16-H19-H20-H21-H22-H23-H24-H25
H28:I28H28=(SUM(G60:H62,G67:H67)/2)*$D$28
H29:I29H29=(G44+H44)/2*$D$29
H32:I32H32=H26-H28+H29-H30+H31
H34:I34H34=H$32*$D34
H35:I35H35=H32-H34
H44:I44H44=MAX(H112,H112-H115)
H46:I46,H63:I63H46=$D46*H$16
H49:I49,H65:I65H49=SUM(H44:H48)
H51:I51H51=G51-H86+H85-H87
H52H52=$G52+H$90-H$91-H$92
I52I52=H52+I90-I91-I92
H53:I53H53=G$53+H$96-H$97-H$95
H57:I57H57=H49+SUM(H51:H55)
H60:I60H60=MAX(0,H115)
H62:I62H62=H$100
H67:I67H67=G67-H100+H101-H102
H69:I69H69=SUM(H65:H68)
H73:I73H73=G73+H105
H74:I74H74=G74+H35-H37
H75:I75H75=G$75+H$107
H76:I76H76=G$76+H$106
H77:I77H77=SUM(H72:H75)-H76
H79:I79H79=H77+H69
H86:I86H86=G$51*$D86
H111:I111H111=SUM(H45:H48,H51:H55)
H112:I112H112=$D$44*H15
H113:I113H113=SUM(H111:H112)
H114:I114H114=SUM(H61:H64,H67:H68,H77)
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,224,463
Messages
6,178,818
Members
452,881
Latest member
motivationgyan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top