Description
the assignment is to complete the 3-statement model. Every cell filled Green in the model should be completed. Remove the Green areas as you complete the model. Link the interest income and interest expense LAST. The model must balance and if it does not, go back through and check all of the original green areas to see where you might be off.
Unformatted Attachment Preview
WD-40 Financial Model
Balance Sheet
Income Statement
PAGE
1
2
WD-40 Balance Sheet
(USD in thousands, except per share data)
8/31/13
8/31/14
8/31/15
8/31/16
8/31/17
8/31/18
8/31/19
8/31/20
8/31/21
8/31/22
8/31/23
Assets:
Cash and cash equivalents
Short-term investments
Accounts receivable, net
Inventories
Current deferred tax assets, net
Other current assets
Total current assets
Property and equipment, net
Goodwill
Other intangible assets, net
Operating lease right-of-use assets
Deferred tax assets, net
Other assets
Total assets
$53,434
37,516
56,878
32,433
5,672
6,210
$192,143
8,535
95,236
24,292
0
0
2,858
$323,064
$57,803
45,050
63,618
34,989
5,855
8,339
$215,654
9,702
95,499
23,671
0
0
3,154
$347,680
$53,896
48,603
58,750
32,052
5,824
6,127
$205,252
11,376
96,409
22,961
0
0
3,259
$339,257
$50,891
57,633
64,680
31,793
0
4,475
$209,472
11,545
95,649
19,191
0
621
3,190
$339,668
$37,082
80,166
64,259
35,340
0
8,007
$224,854
29,439
95,597
16,244
0
495
3,088
$369,717
$48,866
219
69,025
36,536
0
13,337
$167,983
36,357
95,621
13,513
0
511
3,074
$317,059
$27,233
0
72,864
40,682
0
7,216
$147,995
45,076
95,347
10,652
0
403
3,189
$302,662
$56,462
0
80,672
41,264
0
6,756
$185,154
60,759
95,731
8,633
8,168
464
3,728
$362,637
$85,961
0
89,558
55,752
0
9,948
$241,219
70,145
95,869
7,244
8,824
858
6,044
$430,203
$37,843
0
89,930
104,101
0
17,766
$249,640
65,977
95,180
5,588
7,559
679
9,672
$434,295
$48,143
0
98,039
86,522
0
15,821
$248,525
66,791
95,505
4,670
7,820
1,201
13,454
$437,966
*
Liabilities and Shareholders’ Equity:
Accounts payable
Accrued liabilities
Accrued payroll and related expenses
Revolving credit facility, current portion
Current portion of long-term debt and short-term borrowings
Income taxes payable
Total current liabilities
Revolving credit facility
Long-term debt
Deferred tax liabilities, net
Long-term operating lease liabilities
Other long-term liabilities
Total liabilities
$19,693
16,562
17,244
63,000
0
1,146
$117,645
0
0
24,011
0
1,901
$143,557
$18,031
18,382
15,969
98,000
0
1,529
$151,911
0
0
24,253
0
2,101
$178,265
$17,128
15,200
13,357
0
0
2,287
$47,972
108,000
0
23,145
0
2,282
$181,399
$18,690
15,757
20,866
0
0
3,381
$58,694
122,000
0
16,365
0
2,214
$199,273
$20,898
18,997
14,222
20,000
0
1,306
$75,423
134,000
0
18,949
0
1,958
$230,330
$19,115
26,240
14,823
22,800
800
2,125
$85,903
44,000
18,800
11,050
0
1,817
$161,570
$18,727
18,513
15,301
20,405
800
844
$74,590
42,221
18,000
11,688
0
10,688
$157,187
$21,676
21,660
14,767
0
800
1,213
$60,116
95,898
17,200
11,291
6,520
11,299
$202,324
$33,499
25,658
25,662
0
800
317
$85,936
46,540
68,400
10,401
7,062
11,482
$229,821
$32,852
27,161
11,583
38,373
800
51
$110,820
39,539
67,600
10,528
5,999
11,185
$245,671
$30,826
30,000
16,722
10,000 Debt Schedule
800 Debt Schedule
494
$88,842
42,943 Debt Schedule
66,800 Debt Schedule
10,305
5,832
13,066
$227,788
*
Shareholders’ equity:
Common Stock
Additional paid-in capital
Retained earnings
Accumulated other comprehensive income (loss)
Treasury Stock
Total shareholders’ equity
$19
133,239
214,034
(5,043)
(162,742)
$179,507
$19
136,212
237,596
1,103
(205,515)
$169,415
$20
141,651
260,683
(8,722)
(235,774)
$157,858
$20
145,936
289,642
(27,298)
(267,905)
$140,395
$20
150,692
315,764
(28,075)
(299,014)
$139,387
$20
153,469
351,266
(27,636)
(321,630)
$155,489
$20
155,132
374,060
(32,482)
(351,255)
$145,475
$20
157,850
398,731
(28,208)
(368,080)
$160,313
$20
163,737
430,735
(26,030)
(368,080)
$200,382
$20
165,973
456,076
(36,209)
(397,236)
$188,624
$20
171,546
477,488
(31,206)
(407,670)
$210,178
Total liabilities and shareholders’ equity
Check
$323,064
0.000
$347,680
0.000
$339,257
0.000
$339,668
0.000
$369,717
0.000
$317,059
0.000
$302,662
0.000
$362,637
0.000
$430,203
0.000
$434,295
0.000
$437,966
0.000
*
*
*
*
WD-40 Assumptions
8/31/23
Income Statement (non-debt items)
Net sales (growth in sales)
Clean COGS (as % of sales)
Clean SG&A (as % of sales)
Advertising and sales promotion (as % of sales)
Amortization (as % of sales)
Other income (expense), net (as % of sales)
Provision for income taxes (Taxes / EBT)
3.6%
48.1%
28.3%
5.4%
0.2%
0.2%
22.5%
10 yr AVG
4.0%
45.7%
28.8%
5.6%
0.6%
0.1%
24.1%
5 yr AVG
5.9%
46.5%
28.4%
5.4%
0.4%
0.1%
22.3%
3 yr AVG
9.8%
47.8%
28.0%
5.5%
0.3%
0.0%
20.4%
Gross profit margin
EBITDA margin
EBIT margin
Net income margin
51.9%
18.2%
16.7%
12.3%
54.3%
19.9%
18.2%
13.5%
53.5%
19.7%
18.0%
13.5%
52.2%
18.8%
17.2%
13.2%
Shareholder Info
Dividend Payout Ratio (Dividends / Net Income)
67.6%
53.8%
60.5%
61.4%
Balance Sheet (Current Assets)
Cash and ST Investments (REQUIRED) as % of sales
Accounts receivable, net as % of sales
Inventories as % of COGS
Other current assets as % of sales
9.0%
18.2%
33.5%
2.9%
17.9%
17.4%
24.2%
2.2%
10.8%
18.2%
28.6%
2.4%
11.3%
18.0%
32.9%
2.8%
Balance Sheet (Non-Current Assets)
Capital Expenditures (as % of sales)
Depreciation (as % of sales)
GOODWILL (ACTUAL)
NEW Intangibles (NEW PURCHASES)
Operating lease right-of-use assets (ACTUAL)
Deferred tax assets, net (ACTUAL)
Other assets as % of sales
1.3%
1.3%
95,505
87
7,820
1,201
2.5%
2.6%
1.1%
#N/A
#N/A
#N/A
#N/A
1.1%
2.8%
1.3%
#N/A
#N/A
#N/A
#N/A
1.5%
2.0%
1.3%
#N/A
#N/A
#N/A
#N/A
1.9%
Balance Sheet (NON-Debt Current Liabilities)
Accounts payable as % of COGS
Accrued liabilities as % of sales
Accrued payroll & related expenses as % of sales
Income taxes payable as % of sales
11.9%
5.6%
3.1%
0.1%
11.6%
5.0%
3.8%
0.3%
12.3%
5.2%
3.6%
0.1%
13.2%
5.4%
3.5%
0.1%
Balance Sheet (NON-Debt Non-Current Liabilities)
Deferred tax liabilities, net (ACTUAL)
Long-term operating lease liabilities (ACTUAL)
Other long-term liabilities as % of sales
10,305
5,832
2.4%
#N/A
#N/A
1.5%
#N/A
#N/A
2.4%
#N/A
#N/A
2.3%
Shareholders’ equity
Common Stock
Additional paid-in capital
Accumulated other comprehensive income (loss)
Treasury Stock
Interest
Revolving Credit Facility
Series A Notes – 3.39% fixed rate
Series B Notes – 2.50% fixed rate
Series C Notes – 2.69% fixed rate
Cash
$20
171,546
(31,206)
(10,434)
Don’t Calc Anything Here
Don’t Calc Anything Here
Don’t Calc Anything Here
Don’t Calc Anything Here
DRIVER
7.0%
47.5%
28.5%
5.5%
0.3%
0.0%
21.0%
8/31/24
7.0%
47.5%
28.5%
5.5%
0.3%
0.0%
21.0%
8/31/25
7.0%
47.5%
28.5%
5.5%
0.3%
0.0%
21.0%
Projected
8/31/26
7.0%
47.5%
28.5%
5.5%
0.3%
0.0%
21.0%
8/31/27
7.0%
47.5%
28.5%
5.5%
0.3%
0.0%
21.0%
8/31/28
7.0%
47.5%
28.5%
5.5%
0.3%
0.0%
21.0%
53.8%
53.8%
53.8%
53.8%
53.8%
53.8%
11.0%
17.5%
25.0%
2.8%
11.0%
17.5%
25.0%
2.8%
11.0%
17.5%
25.0%
2.8%
11.0%
17.5%
25.0%
2.8%
11.0%
17.5%
25.0%
2.8%
11.0%
17.5%
25.0%
2.8%
2.0%
1.3%
95,505
0
7,820
1,201
1.9%
2.0%
1.3%
95,505
0
7,820
1,201
1.9%
2.0%
1.3%
95,505
0
7,820
1,201
1.9%
2.0%
1.3%
95,505
0
7,820
1,201
1.9%
2.0%
1.3%
95,505
0
7,820
1,201
1.9%
2.0%
1.3%
95,505
0
7,820
1,201
1.9%
11.0%
5.5%
3.5%
0.1%
11.0%
5.5%
3.5%
0.1%
11.0%
5.5%
3.5%
0.1%
11.0%
5.5%
3.5%
0.1%
11.0%
5.5%
3.5%
0.1%
11.0%
5.5%
3.5%
0.1%
10,305
5,832
2.5%
10,305
5,832
2.5%
10,305
5,832
2.5%
10,305
5,832
2.5%
10,305
5,832
2.5%
10,305
5,832
2.5%
$20
171,546
(31,206)
($18,800)
$20
171,546
(31,206)
(18,800)
$20
171,546
(31,206)
(18,800)
$20
171,546
(31,206)
0
$20
171,546
(31,206)
0
$20
171,546
(31,206)
0
8.50%
3.39%
2.50%
2.69%
5.25%
8.50%
3.39%
2.50%
2.69%
5.25%
8.50%
3.39%
2.50%
2.69%
5.25%
8.50%
3.39%
2.50%
2.69%
5.25%
8.50%
3.39%
2.50%
2.69%
5.25%
WD-40 Debt Schedule
(USD in thousands, except per share data)
8/31/21
SHORT-TERM DEBT
NEW REVOLVER
Historical
8/31/22
8/31/23
8/31/24
$0
$4,440
Interest Rate
8.50%
Average Balance
Interest Expense
$2,220
189
LONG-TERM DEBT
OLD REVOLVER (Beginning)
Paydown (2 years, 9/30/25)
OLD REVOLVER (Ending)
$52,943
26,472
$26,472
$52,943
Interest Rate
8.50%
Average Balance
Interest Expense
$39,707
3,375
Series A Notes (Beginning)
Paydown (11/15/2032)
Series A Notes (Ending)
$15,600
800
$14,800
$15,600
Interest Rate
3.39%
Average Balance
Interest Expense
$15,200
515
Series B Notes (Beginning)
Paydown (11/15/27; BULLET)
Series B Notes (Ending)
$26,000
0
$26,000
$26,000
Interest Rate
2.50%
Average Balance
Interest Expense
$26,000
650
Series C Notes (Beginning)
Paydown (11/15/30; BULLET)
Series C Notes (Ending)
$26,000
0
$26,000
$26,000
8/31/25
Projected
8/31/26
Interest Rate
2.69%
Average Balance
Interest Expense
$26,000
699
Summary
ST Debt
LT Debt
Total Debt
$0
120,543
$120,543
Interest Expense Summary
ST Debt Interest
LT Debt Interest
Total Interest Expense
CASH
Required CASH
Excess CASH
Total CASH
Interest Rate
Average Balance
Interest Income
$4,440
93,272
$97,712
$189
5,240
$5,429
$85,961
$37,843
$48,143
$48,143
$63,235
0
$63,235
5.25%
$55,689
2,924
Projected
8/31/27
8/31/28
WD-40 Calculations
(USD in thousands, except per share data)
Historical
8/31/22
8/31/23
8/31/24
$430,735
67,329
41,988
0
$456,076
456,076
0.000
$456,076
65,993
44,581
0
$477,488
477,488
0.000
$477,488
76,750
41,291
0
$512,946
$7,244
(222)
1,434
$5,588
$5,588
87
1,005
$4,670
$4,670
0
1,725
$2,945
$60,759
15,059
5,570
$70,145
$70,145
8,303
6,860
$65,977
$65,977
6,871
7,146
$66,791
$66,791
11,497
7,473
$70,815
Depreciation & Amortization (from CFS)
Amortization of definite-lived intangible assets
Depreciation (D&A – A)
$7,019
1,449
$5,570
$8,294
1,434
$6,860
$8,151
1,005
$7,146
Cost of products sold (ACTUAL)
Selling, general and administrative (ACTUAL)
Total Combined
224,370
145,493
$369,863
264,055
138,658
$402,713
263,035
154,684
$417,719
% COGS
% SG&A
60.7%
39.3%
65.6%
34.4%
63.0%
37.0%
Depreciation in COGS
Depreciation in SG&A
Total Depreciation
$3,379
2,191
$5,570
$4,498
2,362
$6,860
$4,500
2,646
$7,146
$220,991
143,302
$259,557
136,296
$258,535
152,038
$89,558
55,752
0
$89,930
104,101
0
$98,039
86,522
0
8/31/21
Retained Earnings
Beginning Balance (Previous Ending)
$398,731
Net Income
70,229
Dividends Paid (from CFS)
38,225
Cumulative effect of change in accounting principle
0
Ending Balance (CALCULATED)
$430,735
Check (Actual from Balance Sheet
430,735
0.000
Intangibles
Beginning Balance (Previous Ending)
$8,633
NEW Intangibles
60
Amortization
1,449
Ending Balance (Actual from B/S)
$7,244
PP&E (Property, Plant & Equipment)
Beginning Balance (Previous Ending)
Capital Expenditures (Actual from CFS)
Depreciation (Calculated Below)
Ending Balance (Actual from B/S)
Clean COGS
Clean SG&A
Operating Working Capital ((CA – Cash) – (CL – ST Debt))
Accounts receivable, net
Inventories
Current deferred tax assets, net
Pr
$100,601
68,265
0
Other current assets
Total Operating Assets
Accounts payable
Accrued liabilities
Accrued payroll and related expenses
Income taxes payable
Total Operating Liabilities
9,948
$155,258
33,499
25,658
25,662
317
$85,136
17,766
$211,797
32,852
27,161
11,583
51
$71,647
15,821
$200,382
30,826
30,000
16,722
494
$78,042
16,096
$184,962
30,037
31,617
20,120
575
$82,349
Operating Working Capital
$70,122
$140,150
$122,340
$102,613
Operating Assets (Cash Flow)
Operating Liabilities (Cash Flow)
Operating Working Capital (Cash Flow)
($26,566)
25,820
($746)
($56,539)
(13,489)
($70,028)
$11,415
6,395
$17,810
$15,420
4,307
$19,727
8/31/25
Projected
8/31/26
8/31/27
8/31/28
$512,946
82,122
44,182
0
$550,887
$550,887
88,562
47,646
0
$591,803
$591,803
95,691
51,482
0
$636,012
$636,012
102,389
55,085
0
$683,316
$2,945
0
1,845
$1,100
$1,100
0
1,100
$0
$0
0
0
$0
$0
0
0
$0
$70,815
12,302
7,996
$75,121
$75,121
13,163
8,556
$79,728
$79,728
14,085
9,155
$84,658
$84,658
15,071
9,796
$89,932
WD-40 Income Statement
(USD in thousands, except per share data)
*
*
*
*
*
8/31/21
Historical
8/31/22
8/31/23
8/31/24
8/31/25
Projected
8/31/26
8/31/27
8/31/28
$488,109
220,991
$267,118
143,302
27,956
$95,860
5,570
1,449
0
$88,841
81
(2,395)
(28)
$86,499
16,270
$70,229
$518,820
259,557
$259,263
136,296
27,343
$95,624
6,860
1,434
0
$87,330
102
(2,742)
(582)
$84,108
16,779
$67,329
$537,255
258,535
$278,720
152,038
28,807
$97,875
7,146
1,005
0
$89,724
231
(5,614)
822
$85,163
19,170
$65,993
$574,863
273,060
$301,803
163,836
31,617
$106,350
7,473
1,725
0
$97,152
$615,103
292,174
$322,929
175,304
33,831
$113,794
7,996
1,845
0
$103,952
$658,160
312,626
$345,534
187,576
36,199
$121,760
8,556
1,100
0
$112,104
$704,232
334,510
$369,722
200,706
38,733
$130,283
9,155
0
0
$121,128
$753,528
357,926
$395,602
214,755
41,444
$139,403
9,796
0
0
$129,607
0
$97,152
20,402
$76,750
0
$103,952
21,830
$82,122
0
$112,104
23,542
$88,562
0
$121,128
25,437
$95,691
0
$129,607
27,217
$102,389
Earnings per common share
Basic EPS
Diluted EPS
$5.13
$5.11
$4.93
$4.92
$4.86
$4.85
$5.65
$5.64
$6.05
$6.04
$6.52
$6.51
$7.05
$7.03
$7.54
$7.53
Shares used in per share calculations
Basic Shares Out
Diluted Shares Out
13,698
13,733
13,668
13,696
13,578
13,604
13,578
13,604
13,578
13,604
13,578
13,604
13,578
13,604
13,578
13,604
Net sales
Clean COGS
Gross profit
Clean SG&A
Advertising and sales promotion
EBITDA
Depreciation
Amortization of definite-lived intangible assets
Impairment of definite-lived intangible assets
Income from operations (EBIT)
Interest income
Interest expense
Other income (expense), net
Income before income taxes (EBT)
Provision for income taxes
Net income
WD-40 Cash Flow Statement
(USD in thousands, except per share data)
8/31/21
Historical
8/31/22
Projected
8/31/23
8/31/24
Cash Flow from Operations
Net Income
Depreciation
Amortization
D in Operating Working Capital
Total Cash Flow from Operations
$76,750
7,473
1,725
19,727
$105,675
Cash Flow from Investing
Capital Expenditures
D in Other Non-Current Assets
D in Other Non-Current Liabilities
Total Cash Flow from Investing
($11,497)
2,532
1,306
($7,660)
Cash Flow from Financing
OLD REVOLVER, current
Short-Term Debt, current
Revolving credit facility
Series A Notes – 3.39% fixed rate
Series B Notes – 2.50% fixed rate
Series C Notes – 2.69% fixed rate
Common Stock
Additional paid-in capital
Accumulated other
Share repurchases
Dividends
Total Cash Flow from Financing
$0
0
(26,472)
(800)
0
0
0
0
0
(18,800)
(41,291)
($87,363)
Net Cash Flow (D in Cash)
$10,652
Cash Analysis
Beginning Cash
Change in Cash from CFS
Net Cash Available at end of year
$48,143
10,652
$58,795
Required CASH
Excess CASH / (REVOLVER)
$48,143
$63,235
(4,440)
8/31/25
Projected
8/31/26
8/31/27
8/31/28
WD-40 Balance Sheet
(USD in thousands, except per share data)
*
*
Assets:
Cash and ST Investments (REQUIRED)
Excess CASH
Accounts receivable, net
Inventories
Current deferred tax assets, net
Other current assets
Total current assets
Property and equipment, net
Goodwill
Other intangible assets, net
Operating lease right-of-use assets
Deferred tax assets, net
Other assets
Total assets
8/31/21
Historical
8/31/22
8/31/23
8/31/24
8/31/25
Projected
8/31/26
8/31/27
8/31/28
$85,961
$37,843
$48,143
$67,661
$72,398
$77,465
$82,888
89,558
55,752
0
9,948
$241,219
70,145
95,869
7,244
8,824
858
6,044
$430,203
89,930
104,101
0
17,766
$249,640
65,977
95,180
5,588
7,559
679
9,672
$434,295
98,039
86,522
0
15,821
$248,525
66,791
95,505
4,670
7,820
1,201
13,454
$437,966
$63,235
0
100,601
68,265
0
16,096
$248,197
70,815
95,505
2,945
7,820
1,201
10,922
$437,406
107,643
73,044
0
17,223
$265,571
75,121
95,505
1,100
7,820
1,201
11,687
$458,005
115,178
78,157
0
18,428
$284,161
79,728
95,505
0
7,820
1,201
12,505
$480,920
123,241
83,628
0
19,718
$304,052
84,658
95,505
0
7,820
1,201
13,380
$506,616
131,867
89,481
0
21,099
$325,336
89,932
95,505
0
7,820
1,201
14,317
$534,111
$32,139
33,831
21,529
$34,389
36,199
23,036
$36,796
38,733
24,648
$39,372
41,444
26,373
$33,499
25,658
25,662
0
0
$32,852
27,161
11,583
0
0
$30,826
30,000
16,722
0
0
*
Liabilities and Shareholders’ Equity:
Accounts payable
Accrued liabilities
Accrued payroll and related expenses
Revolving credit facility, current portion
Current portion of long-term debt and short-term borrowings
NEW REVOLVER
Income taxes payable
Total current liabilities
Revolving credit facility
Series A Notes – 3.39% fixed rate
Series B Notes – 2.50% fixed rate
Series C Notes – 2.69% fixed rate
Deferred tax liabilities, net
Long-term operating lease liabilities
Other long-term liabilities
Total liabilities
317
$85,136
46,540
17,200
26,000
26,000
10,401
7,062
11,482
$229,821
51
$71,647
77,912
16,400
26,000
26,000
10,528
5,999
11,185
$245,671
*
Shareholders’ equity:
Common Stock
Additional paid-in capital
Retained earnings
Accumulated other comprehensive income (loss)
Treasury Stock
Total shareholders’ equity
$20
163,737
430,735
(26,030)
(368,080)
$200,382
Total liabilities and shareholders’ equity
Check
$430,203
0.000
*
*
494
$78,042
52,943
15,600
26,000
26,000
10,305
5,832
13,066
$227,788
$30,037
31,617
20,120
0
0
4,440
575
$86,789
26,472
14,800
26,000
26,000
10,305
5,832
14,372
$210,569
615
$88,114
658
$94,281
704
$100,881
754
$107,943
10,305
5,832
15,378
$119,628
10,305
5,832
16,454
$126,873
10,305
5,832
17,606
$134,624
10,305
5,832
18,838
$142,918
$20
165,973
456,076
(36,209)
(397,236)
$188,624
$20
171,546
477,488
(31,206)
(407,670)
$210,178
$20
171,546
512,946
(31,206)
(426,470)
$226,836
$20
171,546
550,887
(31,206)
(445,270)
$245,977
$20
171,546
591,803
(31,206)
(445,270)
$286,893
$20
171,546
636,012
(31,206)
(445,270)
$331,102
$20
171,546
683,316
(31,206)
(445,270)
$378,406
$434,295
0.000
$437,966
0.000
$437,406
$365,605
$413,765
$465,726
$521,324
0.000 92,399.496 67,154.648 40,890.150 12,787.136
Purchase answer to see full
attachment
