What is the FCF and NPV if Bob and Maggie do the $75,000 acquisition?

This spreadsheet supports STUDENT analysis of the case \"Horniman Horticulture\" (Case 11). This spreadsheet was prepared by Michael J. Schill, Robert F. Vandell Research Associate Professor of Business Administration. Copyright 2011 by the University of Virginia Darden School Foundation, Charlottesville, VA. All rights reserved. For customer service inquiries, send an e-mail to sales@dardenbusinesspublishing.com. No part of this publication may be reproduced, stored in a retrieval system, posted to the Internet, or transmitted in any form or by any meanselectronic, mechanical, photocopying, recording, or otherwisewithout the permission of the Darden School Foundation. April 15, 2011 Exhibit 1 HORNIMAN HORTICULTURE ojected Horniman Horticulture Financial Summary (in thousands of dollars) 2002 Profit and loss statement Revenue Cost of goods sold Gross profit SG&A expense Depreciation Operating profit Taxes Net profit 788.5 402.9 385.6 301.2 34.2 50.2 17.6 32.6 2003 807.6 428.8 378.8 302.0 38.4 38.4 13.1 25.3 2004 908.2 437.7 470.5 356.0 36.3 78.2 26.2 52.0 1 999000 52 greenhouses 2005 GROWTH RATES 2003 2004 1048.8 Revenue 2% 12% 503.4 Cost of goods sol 6% 2% 545.4 Gross profit -2% 24% 404.5 SG&A expense 0% 18% 40.9 Depreciation 12% -5% 100.0 Operating profit -24% 104% 39.2 Taxes -26% 100% 60.8 Net profit -22% 106% 2005 15% 15% 16% 14% 13% 28% 50% 17% 2 3 4 COGS IS INCREASING HIGHER THAN REVENUE INCREASED WAGES INCREASED COGS 0.2 Interest Rate Loan Amount 2006 2006 2007 2008 2009 0.065 90000 5850 30% 1363.44 100000 30% 654.42 120000 709.0 30% 525.85 12% 46 30% 137.2 39% 0 Acres 137.2 Profit Per Acre 40 pocf Balance sheet Cash Accounts receivable Inventory1 Other current assets2 Current assets Net fixed assets3 Total assets Accounts payable Wages payable Other payables Current liabilities Net worth Cash Flow Statements 52 2002 120.1 90.6 468.3 20.9 699.9 332.1 1032.0 105.2 99.5 507.6 19.3 731.6 332.5 1064.1 66.8 119.5 523.4 22.6 732.3 384.3 1116.6 Balance sheet 9.4 Cash 146.4 Accounts receivabl 656.9 Inventory1 20.9 Other current asset 833.6 Current assets 347.9 Net fixed assets3 1181.5 Total assets 6.0 19.7 10.2 35.9 996.1 5.3 22.0 15.4 42.7 1021.4 4.5 22.1 16.6 43.2 1073.4 5.0 Accounts payable 24.4 Wages payable 17.9 Other payables 47.3 Current liabilities 1134.2 Net worth Net Profit per Acr -12% 10% 8% -8% 5% 0% 3% -37% 20% 3% 17% 0% 16% 5% -86% 23% 26% -8% 14% -9% 6% -12% 12% 51% 19% 3% -15% 0% 8% 1% 5% 11% 10% 8% 9% 6% 0.815 30% 190.32 30% 854.0 20.9 30% 1083.68 347.9 60% 39.04 30% 23.27 30% 61.49 30% 1474.46 22.0 140.8 44.8 38.8 145.2 24.9 88.1 161.2 0.2 4.5 Capital expenditur 185.1 Purchases4 97.2 235.9 76% 3% 127% 11% -95% 15% 30% 75 30% 240.63 0.3 2003 2004 2005 2006 0.8 0.6 1.3 1.5 2007 2008 3.4 Net Profit 0.9 0.8 2009 2010 Cash flow from operations Net income to common Depreciation Receivables - Total (Net) Inventories - Total Prepaid Expenses Current Assets - Other 2011 Accounts Payable Wages Payables Other Payables Accrued Expenses Taxes Payable Other Current Liabilities Cash Flow from Investing Plant, Property & Equip (Gross) Investments at Equity Investments and Advances - Other Intangibles Deferred Charges Assets - Other Profit Per Acre f(x) = + 0.815 R = 0 0.7 Capital expenditure Purchases4 change nwc Statement of Cash Flows 0.6 Profit Per Acre Cash Flow from Financing Linear (Profit Per Acre) Notes Payable 0.5 0.4 0.3 0.2 fcf 0 0 0 0.1 0 193 0 labor, materials (soil, water, containers, stakes, labels, chemicals), scrap, and overhead. 2 Other current assets included consigned inventory, prepaid expenses, and assets held for sale. 3 Net fixed assets included land, buildings and improvements, equipment, and software. 4 Purchases represented the annual amount paid to suppliers. Free Cash Flows Operating profit Taxes OP margin Depreciation OP CF Capital expenditure change nwc FCF 2002 50.2 17.6 32.6 34.2 66.8 22 44.8 0 2003 2004 38.4 78.2 13.1 26.2 25.3 52 38.4 36.3 63.7 88.3 38.8 88.1 24.9 0.2 0 5.97E-014 2005 100 39.2 60.8 40.9 101.7 4.5 97.2 0 Debt (Long-Term) Due In One Year 0.5 1 1.5 2 2.5 3 3.5 4 4.5 Long Term Debt Deferred Taxes (Balance Sheet) Investment Tax Credit Liabilities - Other 2006 137.2 0 137.2 46 183.17 75 235.9 -127.72 Net Cash Flow For the Year Beg Cash Balance End Cash Balance 2003 25.3 4.2 8.9 39.3 -1.6 -0.7 2.3 5.2 Exhibit 2 HORNIMAN HORTICULTURE Financial Ratio Analysis and Benchmarking 2002 Revenue growth Gross margin (Gross profit / Revenue) Operating margin (Op. profit / Revenue Net profit margin (Net profit / Revenue) 2003 2004 2005 Benchmark1 2.9% 2.4% 12.5% 15.5% 48.9% 46.9% 51.8% 52.0% 6.4% 4.8% 8.6% 9.5% 4.1% 3.1% 5.7% 5.8% 2006 2007 2008 (1.8)% 48.9% 7.6% 2.8% 30% 52% 60% 55% 51% #DIV/0! #DIV/0! 10% #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 6% 11% 8% 5% #DIV/0! #DIV/0! Return on assets (Net profit / Total asset 3.2% Return on capital (Net profit / Total capi 3.3% 2.4% 2.5% 4.7% 4.8% 5.1% 5.4% 2.9% 4.0% 3% #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 3% 7% 5% 4% #DIV/0! #DIV/0! Receivable days (AR / Revenue * 365) Inventory days (Inventory / COGS * 36 Payable days (AP / Purchases * 365) NFA turnover (Revenue / NFA) 45.0 432.1 13.3 2.4 48.0 436.5 10.2 2.4 50.9 476.3 9.9 3.0 21.8 386.3 26.9 2.7 54.09 - #DIV/0! #DIV/0! 476.298 0 0 0 #DIV/0! #DIV/0! 0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 3.01466 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 41.9 424.2 15.6 2.4 Benchmark figures are based on 2004 financial ratios of publicly traded horticulture producers. 1 Exhibit 1 HORNIMAN HORTICULTURE ed Horniman Horticulture Financial Summary (in thousands of dollars) 2002 Profit and loss statement Revenue 788.5 Cost of goods so 402.9 Gross profit 385.6 SG&A expense 301.2 Depreciation 34.2 Operating profit 50.2 Taxes 17.6 Net profit 32.6 pocf Balance sheet Cash 120.1 Accounts receivab 90.6 1 Inventory 468.3 Other current assets 20.9 Current assets 699.9 3 Net fixed assets 332.1 Total assets 1032.0 105.2 99.5 507.6 19.3 731.6 332.5 1064.1 Accounts payable Wages payable Other payables Current liabilitie Net worth 5.3 22.0 15.4 42.7 1021.4 2004 999000 52 greenhouses GROWTH RATES 2003 2004 1048.8 Revenue 2% 12% 503.4 Cost of goods s 6% 2% 545.4 Gross profit -2% 24% 404.5 SG&A expense 0% 18% 40.9 Depreciation 12% -5% 100.0 Operating profit -24% 104% 39.2 Taxes -26% 100% 60.8 Net profit -22% 106% 2005 15% 15% 16% 14% 13% 28% 50% 17% 66.8 119.5 523.4 22.6 732.3 384.3 1116.6 Balance sheet 9.4 Cash 146.4 Accounts receiva 656.9 Inventory1 20.9 Other current ass 833.6 Current assets 347.9 Net fixed assets3 1181.5 Total assets -12% 10% 8% -8% 5% 0% 3% -37% 20% 3% 17% 0% 16% 5% -86% 23% 26% -8% 14% -9% 6% 4.5 22.1 16.6 43.2 1073.4 5.0 Accounts payabl 24.4 Wages payable 17.9 Other payables 47.3 Current liabiliti 1134.2 Net worth -12% 12% 51% 19% 3% -15% 0% 8% 1% 5% 22.0 38.8 88.1 4.5 Capital expendit 140.8 145.2 161.2 185.1 Purchases4 44.8 24.9 0.2 97.2 235.9 0 0 0 0 193 23.7 20.2 1134.2 688.9 24.9 water, containers, stakes, inventory included direct labor, materials (soil, 76% 3% 127% 11% 6.0 19.7 10.2 35.9 996.1 2003 1 807.6 428.8 378.8 302.0 38.4 38.4 13.1 25.3 2005 908.2 437.7 470.5 356.0 36.3 78.2 26.2 52.0 Capital expenditur Purchases4 change nwc fcf nw 2547.8 labels, chemicals), scrap, and overhead. inventory, prepaid expenses, and Other current assets included consigned assets held for sale. 3 Net fixed assets included land, buildings and improvements, equipment, and software. 4 Purchases represented the annual amount paid to suppliers. 2002 2003 2004 50.2 38.4 78.2 17.6 13.1 26.2 32.6 25.3 52 34.2 38.4 36.3 66.8 63.7 88.3 22 38.8 88.1 44.8 24.9 0.2 0 1.28E-013 -5.7E-014 2005 100 39.2 60.8 40.9 101.7 4.5 97.2 0 NPV sam mark john matt dad 2006 2007 2008 2009 137.2 299.0 246.9 185.1 53.5 116.6 96.3 72.2 83.7 182.4 150.6 112.9 46 51.735941 58.187122 65.442729 129.6737 234.0967 208.82118 178.36818 75 -2251.0 226.1 ? 2305.63638 8.0040171 208.82118 178.36818 $2,326.98 4 0.38 0.3 Statement of Cash Flows Cash Flow Statements 2003 25.3 4.2 8.9 39.3 Cash flow from operations Net income to common Depreciation Receivables - Total (Net) Inventories - Total Prepaid Expenses Current Assets - Other Accounts Payable Wages Payables Other Payables -1.6 -0.7 2.3 5.2 202.032 33872.908 46744.613 19310.792 -33735.7 -46445.7 853.97 0.3 27.17 0.3 1083.172 0.3 452.27 -24.7431039 2618.6 0.815 Profit Per Acre 2003.0 0.6 2004 2005 2006 also increases 1.3 1.52 -843.3934 1.3 Average 1.066875 -43856.5 0.08 109.0752 1042 30.5 70.8 2547.8 0.3 0.3 0.3 0.3 2880.8 2993.8 75 23.27 61.49 1474.46 0.3 0.3 2730.2 75 240.63 67.6 Deferred Charges Assets - Other Cash Flow from Financing Notes Payable Debt (Long-Term) Due In One Year Long Term Debt Deferred Taxes (Balance Sheet) Investment Tax Credit 1329.9 2 Free Cash Flows Operating profit Taxes OP margin Depreciation OP CF Capital expenditur change nwc FCF 3 COGS IS INCREASING HIGHER THAN REVENUE INCREASED WAGES INCREASED COGS 0.3 0.2 0.15 0.1 Interest Rate Loan Amount 2006 2006 2007 2008 2009 0.065 90000 5850 30% 1363.44 1636.128 1881.5472 2069.7019 100000 30% 654.42 654.42 850.746 1020.8952 120000 709.0 981.7 1030.8 1048.8 30% 525.85 631.02 725.673 798.2403 12% 46 51.735941 58.187122 65.442729 137.2 299.0 246.9 185.1 0.39 53.5 116.6 96.3 72.2 83.7 182.4 150.6 112.9 83.904 109.1 11% 0.6 10% 25% 8% 9% 23.47932 6% 1217.9 -95% 15% 2 Liabilities - Other Net Cash Flow For the Year Beg Cash Balance End Cash Balance robust business: percentages change at same rate as they have in previous years es risk Loan Analysis Table Inputs Hypothetical Numbers Required DSCR Required LTV Net Operating Income Cap Rate Interest Rate Amortization Period (months) 1.15 80% $ From I/S 8.00% 6.50% 120 10 years Total Expenses Net Operating Income Debt Service Coverage Ratio Loan Amt (10 Years, 6.5%) $ $ 2,002.00 2004 2005 2006 2007 2008 2009 50,200.00 $38,400.00 $78,200.00 ### ### ### ### ### Debt Service Coverage Ratio Debt PMT Amt (Month Per Year 100000 1956.61 23479.32 $ 2.14 $ 150000 1703.22 20438.64 $ 2.46 $ 200000 $ - capex accountability Maximum Loan Calculation Available for Debt Service (NOI/DSCR) $ Max Loan Based on DSCR - $0 Value Based on Cap Rate $ - Max Loan Using LTV $ - Max Loan (Lesser of DSCR and LTV approache $0 simple scenario analysis pull out direct numbers in sheet like savings 2003 but loan pmt 1.64 $ 1.88 $ 3.33 $ 3.83 $ 4.26 $ 4.89 $ 5.84 $ 6.71 $ 12.73 $ 14.63 $ 10.52 $ 12.08 $ 7.88 9.06