Please find attached Assignment 6.
the assignment is as follows:
1) Project the Income Statement
2) Project the Calcs Tab
3) Project the Balance Sheet
Essentially, complete and unhighlight the areas that are filled green.
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
$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
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
$7,244
(222)
1,434
$5,588
$5,588
87
1,005
$4,670
$4,670
$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
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
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
Pr
8/31/25
Projected
8/31/26
8/31/27
8/31/28
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
$615,103
292,174
$322,929
$658,160
312,626
$345,534
$704,232
334,510
$369,722
$753,528
357,926
$395,602
Earnings per common share
Basic EPS
Diluted EPS
$5.13
$5.11
$4.93
$4.92
$4.86
$4.85
Shares used in per share calculations
Basic Shares Out
Diluted Shares Out
13,698
13,733
13,668
13,696
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 Balance Sheet
(USD in thousands, except per share data)
8/31/21
Historical
8/31/22
8/31/23
Assets:
Cash and ST Investments (REQUIRED)
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
$85,961
89,558
55,752
0
9,948
$241,219
70,145
95,869
7,244
8,824
858
6,044
$430,203
$37,843
89,930
104,101
0
17,766
$249,640
65,977
95,180
5,588
7,559
679
9,672
$434,295
$48,143
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
$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
800
494
$88,842
42,943
66,800
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
$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
$430,203
0.000
$434,295
0.000
$437,966
0.000
*
*
*
*
8/31/24
8/31/25
Projected
8/31/26
8/31/27
8/31/28