Read attachments.
Sheet1 Burton Sensors, Inc., Student Spreadsheet Supplement Harvard Business School Brief Case 9-918-539 Courseware 9-918-541 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. Created6/27/18 Exhibit 1 Exhibit 1 Burton Sensors Consolidated Balance Sheets (Actual/Projected), 2014-2021 (Fiscal years ending December 31; U.S. $000s) 42004.042369.042735.043100.043465.043830.044196.044561.0 Cash and equivalents279.7251.3304.3371.3359.7368.6437.5484.6 Accounts receivable1,552.51,894.02,315.42,824.93,276.93,539.13,751.43,976.5(24.5% of projected sales) Inventory1,672.92,096.72,436.32,997.93,477.63,755.83,981.14,220.0(26% of projected sales) Other current assets253.5311.8371.9461.2535.0577.8612.5649.2(4% of projected sales) Current assets3,758.64,553.85,427.86,655.37,649.28,241.38,782.59,330.3 Net PP&E3,874.04,293.34,654.14,847.84,938.84,875.24,789.54,698.6 Total assets7,632.68,847.210,082.011,503.212,588.013,116.513,572.014,028.9 Accounts payable773.1935.31,153.01,383.61,605.01,733.41,837.41,947.7(12% of projected sales) Accrued expenses418.2491.0567.2714.9829.3895.6949.31,006.3(6.2% of projected sales) Bank loans3,020.03,880.04,580.05,080.05,230.04,930.04,530.04,030.0 Deferred taxes351.9360.7406.2527.9590.1624.0658.3702.5 Long-term debt, current portion150.0150.0150.0150.0150.0150.0150.0150.0 Current Liabilities4,713.25,817.06,856.57,856.48,404.48,333.18,125.17,836.5 Long-term debt1,980.01,830.01,680.01,530.01,380.01,230.01,080.0930.0 Total liabilities6,693.27,647.08,536.59,386.49,784.49,563.19,205.18,766.5 Shareholders' equity939.41,200.11,545.52,116.82,803.63,553.44,366.95,262.4 Total liabilities and equity7,632.68,847.210,082.011,503.212,588.013,116.513,572.014,028.9 Bank loan / (receivables + inventory)0.940.970.960.870.770.680.590.49 Liabilities / book equity7.1x6.4x5.5x4.4x3.5x2.7x2.1x1.7x Total interest-bearing debt / book equity5.5x4.9x4.1x3.2x2.4x1.8x1.3x1.0x Tables below are not shown in case Working Capital Assumptions (U.S. $ thousands) 42004.042369.042735.043100.043465.043830.044196.044561.0 Income Statement Metrics Revenue6,336.97,794.49,298.711,530.413,375.314,445.315,312.016,230.7 Cost of Goods Sold2,686.83,359.44,035.64,958.15,751.46,211.56,584.26,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 Current 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.042369.042735.043100.043465.043830.044196.044561.0 PP&E (Beginning)3,618.03,874.04,293.34,654.14,847.84,938.84,875.24,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&E354.9522.2483.5345.9267.5144.5153.1162.3 PP&E (Ending)3,874.04,293.34,654.14,847.84,938.84,875.24,789.54,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.042369.042735.043100.043465.043830.044196.044561.0 Beginning balance (with current portion)2,280.02,130.01,980.01,830.01,680.01,530.01,380.01,230.0 Add: issuances0.00.00.00.00.00.00.00.0 Less: retirements(150.0)(150.0)(150.0)(150.0)(150.0)(150.0)(150.0)(150.0) Ending balance (with current portion)2,130.01,980.01,830.01,680.01,530.01,380.01,230.01,080.0 Ending balance (without current portion)1,980.01,830.01,680.01,530.01,380.01,230.01,080.0930.0 Current portion of long-term debt150.0150.0150.0150.0150.0150.0150.0150.0 Interest rate as % of beginning balance5.5%5.5%5.5%5.5%5.5%5.5%5.5%5.5% Interest payment247.5283.3322.3352.6371.8371.8347.1316.8 Deferred Tax Schedule (U.S. $ thousands) 42004.042369.042735.043100.043465.043830.044196.044561.0 Beg. accrued taxes515.8351.9360.7406.2527.9590.1624.0658.3 Accrual of taxes131.6140.4186.0307.6369.8403.8438.0482.2 Tax payments295.5131.6140.4186.0307.6369.8403.8438.0 End. accrued taxes351.9360.7406.2527.9590.1624.0658.3702.5 Tax payments as % of previous year's EBT35.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), 2014-2021 (Fiscal years ending December 31; U.S. $000s) 42004.042369.042735.043100.043465.043830.044196.044561.0 Net sales6,336.97,794.49,298.711,530.413,375.314,445.315,312.016,230.7 COGS2,686.83,359.44,035.64,958.15,751.46,211.56,584.26,979.2(43% of projected sales) Gross profit3,650.14,435.05,263.16,572.37,623.98,233.88,727.89,251.5 SG&A expense2,230.62,790.43,263.84,035.64,681.35,055.85,359.25,680.7(35% of projected sales) R&D expense697.1857.41,022.91,153.01,337.51,444.51,531.21,623.1(10% of projected sales) Depreciation and amortization98.9102.9122.7152.2176.6208.0238.9253.2 Net interest expense (income)*247.5283.3322.3352.6371.8371.8347.1316.8 Pretax income (loss)376.1401.1531.3878.91,056.71,153.61,251.51,377.7 Income taxes131.6140.4186.0307.6369.8403.8438.0482.2(35% of projected EBT) Net income244.4260.7345.4571.3686.8749.9813.5895.5 Number of common shares (thousands)1,5001,5001,5001,5001,5001,5001,5001,500 Earnings per share0.160.170.230.380.460.500.540.60 Cash dividend per share00000000 * Burton pays an average of 5.5% interest on all interest bearing debt. Tables below are not shown in case Assumptions 42004.042369.042735.043100.043465.043830.044196.044561.0 Revenue growth23.0%19.3%24.0%16.0%8.0%6.0%6.0% Items as a % of Revenue COGS42.4%43.1%43.4%43.0%43.0%43.0%43.0%43.0% SG&A expense35.2%35.8%35.1%35.0%35.0%35.0%35.0%35.0% R&D expense11.0%11.0%11.0%10.0%10.0%10.0%10.0%10.0% Tax rate35.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), 2014-2021 (Fiscal years ending December 31; U.S. $000s) 42004.042369.042735.043100.043465.043830.044196.044561.0 Operating Activities Net income244.4260.7345.4571.3686.8749.9813.5895.5 Depreciation and amortization98.9102.9122.7152.2176.6208.0238.9253.2 Less: increase (decrease) in accounts receivable320.0341.5421.3509.6452.0262.2212.3225.1 Less: increase (decrease) in inventory289.8423.7339.6561.6479.7278.2225.3238.9 Less: increase (decrease) in other current assets55.658.360.289.373.842.834.736.7 Add: increase (decrease) in accounts payable120.3162.2217.7230.6221.4128.4104.0110.2 Add: increase (decrease) in accrued expenses30.272.876.2147.7114.466.353.757.0 Add: increase (decrease) in deferred taxes(163.9)8.845.6121.762.233.934.344.2 Operating activities - net cash flow(335.5)(216.1)(13.5)62.9255.9603.4772.0859.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 borrowings800.0860.0700.0500.0150.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 stock0.00.00.00.00.00.00.00.0 Less: cash dividend0.00.00.00.00.00.00.00.0 Financing activities - net cash flow650.0710.0550.0350.00.0(450.0)(550.0)(650.0) Total net cash flow(40.3)(28.4)53.067.0(11.6)8.968.947.1 Beginning cash320.0279.7251.3304.3371.3359.7368.6437.5 Change in cash(40.3)(28.4)53.067.0(11.6)8.968.947.1 Ending cash279.7251.3304.3371.3359.7368.6437.5484.6 Exhibit 4 Exhibit 4 Comparative Data on Four Publically Traded Sensor Manufacturers, 2014-2016 (U.S. $ millions) TE Connectivity (TEL)Ametek (AME)Opsens (OPS)Cyberoptics (CYBE) 201420152016201420152016201420152016201420152016 Net sales12,233.0012,238.0013,113.003,974.303,840.094,300.175.219.6017.7541.1366.2453.33 Operating profit1,992.001,936.002,201.00944.32841.40931.89(5.13)(8.49)(6.38)(2.20)6.151.02 Net income2,420.002,009.001,683.00590.86512.16681.47(2.88)(9.28)(6.54)(2.10)11.562.00 Daniel Cheung: Daniel Cheung: Originially 1.31, make PE more realistic Assets20,589.0017,608.0019,403.006,660.457,100.677,796.0612.7616.8627.6144.7459.5859.50 Total debt3,884.004,070.004,344.001,866.122,062.641,866.173.695.585.30--- Total liabilities11,004.009,123.009,652.003,405.823,844.163,768.436.879.559.648.6810.687.22 Shareholders' equity9,585.008,485.009,751.003,254.633,256.514,027.635.897.3117.9736.0648.9052.28 Operating margin0.160.160.170.240.220.22(0.99)(0.88)(0.36)(0.05)0.090.02 Profit margin0.200.160.130.150.130.16(0.55)(0.97)(0.37)(0.05)0.170.04 Shares outstanding405.00366.00355.00239.91232.59230.2360.1866.7480.956.716.836.95 Market capitalization24,959.1424,618.7733,490.9212,748.2715,280.0016,940.3851.75108.1190.6751.30191.30114.61 Equity beta*1.301.261.241.291.161.251.321.171.020.820.800.85 EPS5.985.494.742.462.202.96(0.05)(0.14)(0.08)(0.31)1.690.29 P/E10.3112.2519.9021.5829.8324.86NANANANA16.5557.30 D/Marketcap0.160.170.130.150.130.110.070.050.06 *Based on previous five years Exhibit 5 Exhibit 5 Electro-Engineering Consolidated Balance Sheets (Actual), 2014-2016 (Fiscal years ending December 31; U.S. $000s) 201420152016 Cash and equivalents379.1350.2359.8 Accounts receivable485.9592.0680.1 Inventory279.8413.7480.6 Prepaid expenses27.85.216.9 Current assets1,172.51,361.11,537.4 Net PP&E249.1339.3479.8 Total assets1,421.61,700.32,017.1 Bank loans340.6340.6310.6 Accounts payable126.6153.4175.1 Accrued expenses104.977.0101.8 Total Liabilities572.1570.9587.5 Shareholders' equity849.51,129.41,429.6 Total liabilities and equity1,421.61,700.32,017.1 Exhibit 6 Exhibit 6 Electro-Engineering Consolidated Income Statements (Actual), 2014-2016 (Fiscal years ending December 31; U.S. $000s) 201420152016 Net sales2,944.93,566.34,072.7 COGS1,525.01,811.72,085.2 Gross profit1,420.01,754.61,987.5 SG&A expense814.9930.11,080.9 R&D expense153.1178.3219.9 Depreciation and amortization142.2196.9206.1 Interest expense18.718.718.7 Pretax income (loss)291.0430.6461.8 Income taxes101.8150.7161.6 Net income189.1279.9300.2 Number of common shares (thousands)1,1001,1001,100 Earnings per share0.170.250.27 Cash dividend per share0.00.00.0 Exhibit 7 Exhibit 7 Electro-Engineering Consolidated Statement of Cash Flows (Actual),