Sheet1
Burton Sensors, Inc., Student Spreadsheet Supplement
Harvard Business School Brief Case XXXXXXXXXX
Courseware XXXXXXXXXX
This courseware was prepared solely as the basis for class discussion. Cases are not intended to serve as endorsements, sources of primary data, or illustrations of effective or ineffective management. Copyright © 2018 President and Fellows of Harvard College. All rights reserved. No part of this product may be reproduced, stored in a retrieval system, used in a spreadsheet, or transmitted in any form or by any means—electronic, mechanical, photocopying, recording or otherwise—without the permission of Harvard Business School.
Created 6/27/18
Exhibit 1
Exhibit 1
Burton Sensors Consolidated Balance Sheets (Actual/Projected), XXXXXXXXXXFiscal years ending December 31; U.S. $000s)
42004.0 42369.0 42735.0 43100.0 43465.0 43830.0 44196.0 44561.0
Cash and equivalents 279.7 251.3 304.3 371.3 359.7 368.6 437.5 484.6
Accounts receivable 1,552.5 1,894.0 2,315.4 2,824.9 3,276.9 3,539.1 3,751.4 3,976.5 (24.5% of projected sales)
Inventory 1,672.9 2,096.7 2,436.3 2,997.9 3,477.6 3,755.8 3,981.1 4,220.0 (26% of projected sales)
Other cu
ent assets 253.5 311.8 371.9 461.2 535.0 577.8 612.5 649.2 (4% of projected sales)
Cu
ent assets 3,758.6 4,553.8 5,427.8 6,655.3 7,649.2 8,241.3 8,782.5 9,330.3
Net PP&E 3,874.0 4,293.3 4,654.1 4,847.8 4,938.8 4,875.2 4,789.5 4,698.6
Total assets 7,632.6 8,847.2 10,082.0 11,503.2 12,588.0 13,116.5 13,572.0 14,028.9
Accounts payable 773.1 935.3 1,153.0 1,383.6 1,605.0 1,733.4 1,837.4 1,947.7 (12% of projected sales)
Accrued expenses 418.2 491.0 567.2 714.9 829.3 895.6 949.3 1,006.3 (6.2% of projected sales)
Bank loans 3,020.0 3,880.0 4,580.0 5,080.0 5,230.0 4,930.0 4,530.0 4,030.0
Defe
ed taxes 351.9 360.7 406.2 527.9 590.1 624.0 658.3 702.5
Long-term debt, cu
ent portion 150.0 150.0 150.0 150.0 150.0 150.0 150.0 150.0
Cu
ent Liabilities 4,713.2 5,817.0 6,856.5 7,856.4 8,404.4 8,333.1 8,125.1 7,836.5
Long-term debt 1,980.0 1,830.0 1,680.0 1,530.0 1,380.0 1,230.0 1,080.0 930.0
Total liabilities 6,693.2 7,647.0 8,536.5 9,386.4 9,784.4 9,563.1 9,205.1 8,766.5
Shareholders' equity 939.4 1,200.1 1,545.5 2,116.8 2,803.6 3,553.4 4,366.9 5,262.4
Total liabilities and equity 7,632.6 8,847.2 10,082.0 11,503.2 12,588.0 13,116.5 13,572.0 14,028.9
Bank loan / (receivables + inventory) 0.94 0.97 0.96 0.87 0.77 0.68 0.59 0.49
Liabilities / book equity 7.1x 6.4x 5.5x 4.4x 3.5x 2.7x 2.1x 1.7x
Total interest-bearing debt / book equity 5.5x 4.9x 4.1x 3.2x 2.4x 1.8x 1.3x 1.0x
Tables below are not shown in case
Working Capital Assumptions (U.S. $ thousands)
42004.0 42369.0 42735.0 43100.0 43465.0 43830.0 44196.0 44561.0
Income Statement Metrics
Revenue 6,336.9 7,794.4 9,298.7 11,530.4 13,375.3 14,445.3 15,312.0 16,230.7
Cost of Goods Sold 2,686.8 3,359.4 4,035.6 4,958.1 5,751.4 6,211.5 6,584.2 6,979.2
Working Capital Ratios
Accounts receivable (% Sales) 24.5% 24.3% 24.9% 24.5% 24.5% 24.5% 24.5% 24.5%
Inventory (% Sales) 26.4% 26.9% 26.2% 26.0% 26.0% 26.0% 26.0% 26.0%
Accounts payable (% Sales) 12.2% 12.0% 12.4% 12.0% 12.0% 12.0% 12.0% 12.0%
Other Activity Ratios
Other Cu
ent Assets (% Sales) 4.0% 4.0% 4.0% 4.0% 4.0% 4.0% 4.0% 4.0%
Accrued Expenses (% Sales) 6.6% 6.3% 6.1% 6.2% 6.2% 6.2% 6.2% 6.2%
42004.0 42369.0 42735.0 43100.0 43465.0 43830.0 44196.0 44561.0
PP&E (Beginning) 3,618.0 3,874.0 4,293.3 4,654.1 4,847.8 4,938.8 4,875.2 4,789.5
Less: Depreciation (98.9) (102.9) (122.7) (152.2) (176.6) (208.0) (238.9) (253.2)
Add: CapEx & Purchase of PP&E 354.9 522.2 483.5 345.9 267.5 144.5 153.1 162.3
PP&E (Ending) 3,874.0 4,293.3 4,654.1 4,847.8 4,938.8 4,875.2 4,789.5 4,698.6
Depreciation (% Sales) 1.6% 1.3% 1.3% 1.3% 1.3% 1.4% 1.6% 1.6%
CapEx (% Sales) 5.6% 6.7% 5.2% 3.0% 2.0% 1.0% 1.0% 1.0%
Long-Term Debt Schedule (U.S. $ thousands)
42004.0 42369.0 42735.0 43100.0 43465.0 43830.0 44196.0 44561.0
Beginning balance (with cu
ent portion) 2,280.0 2,130.0 1,980.0 1,830.0 1,680.0 1,530.0 1,380.0 1,230.0
Add: issuances 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Less: retirements (150.0) (150.0) (150.0) (150.0) (150.0) (150.0) (150.0) (150.0)
Ending balance (with cu
ent portion) 2,130.0 1,980.0 1,830.0 1,680.0 1,530.0 1,380.0 1,230.0 1,080.0
Ending balance (without cu
ent portion) 1,980.0 1,830.0 1,680.0 1,530.0 1,380.0 1,230.0 1,080.0 930.0
Cu
ent portion of long-term debt 150.0 150.0 150.0 150.0 150.0 150.0 150.0 150.0
Interest rate as % of beginning balance 5.5% 5.5% 5.5% 5.5% 5.5% 5.5% 5.5% 5.5%
Interest payment 247.5 283.3 322.3 352.6 371.8 371.8 347.1 316.8
Defe
ed Tax Schedule (U.S. $ thousands)
42004.0 42369.0 42735.0 43100.0 43465.0 43830.0 44196.0 44561.0
Beg. accrued taxes 515.8 351.9 360.7 406.2 527.9 590.1 624.0 658.3
Accrual of taxes 131.6 140.4 186.0 307.6 369.8 403.8 438.0 482.2
Tax payments 295.5 131.6 140.4 186.0 307.6 369.8 403.8 438.0
End. accrued taxes 351.9 360.7 406.2 527.9 590.1 624.0 658.3 702.5
Tax payments as % of previous year's EBT 35.0% 35.0% 35.0% 35.0% 35.0% 35.0% 35.0% 35.0%
Exhibit 2
Exhibit 2
Burton Sensors Consolidated Income Statements (Actual/Projected), XXXXXXXXXXFiscal years ending December 31; U.S. $000s)
42004.0 42369.0 42735.0 43100.0 43465.0 43830.0 44196.0 44561.0
Net sales 6,336.9 7,794.4 9,298.7 11,530.4 13,375.3 14,445.3 15,312.0 16,230.7
COGS 2,686.8 3,359.4 4,035.6 4,958.1 5,751.4 6,211.5 6,584.2 6,979.2 (43% of projected sales)
Gross profit 3,650.1 4,435.0 5,263.1 6,572.3 7,623.9 8,233.8 8,727.8 9,251.5
SG&A expense 2,230.6 2,790.4 3,263.8 4,035.6 4,681.3 5,055.8 5,359.2 5,680.7 (35% of projected sales)
R&D expense 697.1 857.4 1,022.9 1,153.0 1,337.5 1,444.5 1,531.2 1,623.1 (10% of projected sales)
Depreciation and amortization 98.9 102.9 122.7 152.2 176.6 208.0 238.9 253.2
Net interest expense (income)* 247.5 283.3 322.3 352.6 371.8 371.8 347.1 316.8
Pretax income (loss) 376.1 401.1 531.3 878.9 1,056.7 1,153.6 1,251.5 1,377.7
Income taxes 131.6 140.4 186.0 307.6 369.8 403.8 438.0 482.2 (35% of projected EBT)
Net income 244.4 260.7 345.4 571.3 686.8 749.9 813.5 895.5
Number of common shares (thousands) 1,500 1,500 1,500 1,500 1,500 1,500 1,500 1,500
Earnings per share 0.16 0.17 0.23 0.38 0.46 0.50 0.54 0.60
Cash dividend per share 0 0 0 0 0 0 0 0
* Burton pays an average of 5.5% interest on all interest bearing debt.
Tables below are not shown in case
Assumptions
42004.0 42369.0 42735.0 43100.0 43465.0 43830.0 44196.0 44561.0
Revenue growth 23.0% 19.3% 24.0% 16.0% 8.0% 6.0% 6.0%
Items as a % of Revenue
COGS 42.4% 43.1% 43.4% 43.0% 43.0% 43.0% 43.0% 43.0%
SG&A expense 35.2% 35.8% 35.1% 35.0% 35.0% 35.0% 35.0% 35.0%
R&D expense 11.0% 11.0% 11.0% 10.0% 10.0% 10.0% 10.0% 10.0%
Tax rate 35.0% 35.0% 35.0% 35.0% 35.0% 35.0% 35.0% 35.0%
Payout ratio - - -
Exhibit 3
Exhibit 3
Burton Sensors Consolidated Statement of Cash Flows (Actual/Projected), XXXXXXXXXXFiscal years ending December 31; U.S. $000s)
42004.0 42369.0 42735.0 43100.0 43465.0 43830.0 44196.0 44561.0
Operating Activities
Net income 244.4 260.7 345.4 571.3 686.8 749.9 813.5 895.5
Depreciation and amortization 98.9 102.9 122.7 152.2 176.6 208.0 238.9 253.2
Less: increase (decrease) in accounts receivable 320.0 341.5 421.3 509.6 452.0 262.2 212.3 225.1
Less: increase (decrease) in inventory 289.8 423.7 339.6 561.6 479.7 278.2 225.3 238.9
Less: increase (decrease) in other cu
ent assets 55.6 58.3 60.2 89.3 73.8 42.8 34.7 36.7
Add: increase (decrease) in accounts payable 120.3 162.2 217.7 230.6 221.4 128.4 104.0 110.2
Add: increase (decrease) in accrued expenses 30.2 72.8 76.2 147.7 114.4 66.3 53.7 57.0
Add: increase (decrease) in defe
ed taxes (163.9) 8.8 45.6 121.7 62.2 33.9 34.3 44.2
Operating activities - net cash flow (335.5) (216.1) (13.5) 62.9 255.9 603.4 772.0 859.4
Investing Activities
Less: capex (354.9) (522.2) (483.5) (345.9) (267.5) (144.5) (153.1) (162.3)
Investing activities - net cash flow (354.9) (522.2) (483.5) (345.9) (267.5) (144.5) (153.1) (162.3)
Financing Activities
Add: changes in bank bo
owings 800.0 860.0 700.0 500.0 150.0 (300.0) (400.0) (500.0)
Add: long-term debt net issuance (150.0) (150.0) (150.0) (150.0) (150.0) (150.0) (150.0) (150.0)
Add: net issuance of common stock 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Less: cash dividend 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Financing activities - net cash flow 650.0 710.0 550.0 350.0 0.0 (450.0) (550.0) (650.0)
Total net cash flow (40.3) (28.4) 53.0 67.0 (11.6) 8.9 68.9 47.1
Beginning cash 320.0 279.7 251.3 304.3 371.3 359.7 368.6 437.5
Change in cash (40.3) (28.4) 53.0 67.0 (11.6) 8.9 68.9 47.1
Ending cash 279.7 251.3 304.3 371.3 359.7 368.6 437.5 484.6
Exhibit 4
Exhibit 4
Comparative Data on Four Publically Traded Sensor Manufacturers, XXXXXXXXXXU.S. $ millions)
TE Connectivity (TEL) Ametek (AME) Opsens (OPS) Cyberoptics (CYBE)
2014 2015 2016 2014 2015 2016 2014 2015 2016 2014 2015 2016
Net sales 12,233.00 12,238.00 13,113.00 3,974.30 3,840.09 4,300.17 5.21 9.60 17.75 41.13 66.24 53.33
Operating profit 1,992.00 1,936.00 2,201.00 944.32 841.40 931.89 (5.13) (8.49) (6.38) (2.20) 6.15 1.02
Net income 2,420.00 2,009.00 1,683.00 590.86 512.16 681.47 (2.88) (9.28) (6.54) (2.10) 11.56 2.00
Daniel Cheung: Daniel Cheung:
Originially 1.31, make PE more realistic
Assets 20,589.00 17,608.00 19,403.00 6,660.45 7,100.67 7,796.06 12.76 16.86 27.61 44.74 59.58 59.50
Total debt 3,884.00 4,070.00 4,344.00 1,866.12 2,062.64 1,866.17 3.69 5.58 5.30 - - -
Total liabilities 11,004.00 9,123.00 9,652.00 3,405.82 3,844.16 3,768.43 6.87 9.55 9.64 8.68 10.68 7.22
Shareholders' equity 9,585.00 8,485.00 9,751.00 3,254.63 3,256.51 4,027.63 5.89 7.31 17.97 36.06 48.90 52.28
Operating margin 0.16 0.16 0.17 0.24 0.22 0.22 (0.99) (0.88) (0.36) (0.05) 0.09 0.02
Profit margin 0.20 0.16 0.13 0.15 0.13 0.16 (0.55) (0.97) (0.37) (0.05) 0.17 0.04
Shares outstanding 405.00 366.00 355.00 239.91 232.59 230.23 60.18 66.74 80.95 6.71 6.83 6.95
Market capitalization 24,959.14 24,618.77 33,490.92 12,748.27 15,280.00 16,940.38 51.75 108.11 90.67 51.30 191.30 114.61
Equity beta* 1.30 1.26 1.24 1.29 1.16 1.25 1.32 1.17 1.02 0.82 0.80 0.85
EPS 5.98 5.49 4.74 2.46 2.20 2.96 (0.05) (0.14) (0.08) (0.31) 1.69 0.29
P/E 10.31 12.25 19.90 21.58 29.83 24.86 NA NA NA NA 16.55 57.30
D/Marketcap 0.16 0.17 0.13 0.15 0.13 0.11 0.07 0.05 0.06
*Based on previous five years
Exhibit 5
Exhibit 5
Electro-Engineering Consolidated Balance Sheets (Actual), XXXXXXXXXXFiscal years ending December 31; U.S. $000s)
2014 2015 2016
Cash and equivalents 379.1 350.2 359.8
Accounts receivable 485.9 592.0 680.1
Inventory 279.8 413.7 480.6
Prepaid expenses 27.8 5.2 16.9
Cu
ent assets 1,172.5 1,361.1 1,537.4
Net PP&E 249.1 339.3 479.8
Total assets 1,421.6 1,700.3 2,017.1
Bank loans 340.6 340.6 310.6
Accounts payable 126.6 153.4 175.1
Accrued expenses 104.9 77.0 101.8
Total Liabilities 572.1 570.9 587.5
Shareholders' equity 849.5 1,129.4 1,429.6
Total liabilities and equity 1,421.6 1,700.3 2,017.1
Exhibit 6
Exhibit 6
Electro-Engineering Consolidated Income Statements (Actual), XXXXXXXXXXFiscal years ending December 31; U.S. $000s)
2014 2015 2016
Net sales 2,944.9 3,566.3 4,072.7
COGS 1,525.0 1,811.7 2,085.2
Gross profit 1,420.0 1,754.6 1,987.5
SG&A expense 814.9 930.1 1,080.9
R&D expense 153.1 178.3 219.9
Depreciation and amortization 142.2 196.9 206.1
Interest expense 18.7 18.7 18.7
Pretax income (loss) 291.0 430.6 461.8
Income taxes 101.8 150.7 161.6
Net income 189.1 279.9 300.2
Number of common shares (thousands) 1,100 1,100 1,100
Earnings per share 0.17 0.25 0.27
Cash dividend per share 0.0 0.0 0.0
Exhibit 7
Exhibit 7
Electro-Engineering Consolidated Statement of Cash Flows (Actual),