2. Where is the cash going? a. You will note that Exhibit 1 does not include a cash flow statement. Construct cash flow statements for 2013, 2014, and 2015. See Problem 14 from Chapter 1 of Higgins for an example of how to construct a cash flow statement. This problem is in the tab called 1.14. b. Reinforce the conclusion from part a by computing the Cash Conversion Cycle for 2012, 2013, 2014, and 2015. 2B. Will strong business performance in 2016 improve the cash position? a. Extend the financial statements through 2016, assuming that Bob Brown grows revenues by 30% Note: Make cash to be the plug. Calculate it as: Current liabilities + Net worth - Accounts receivable - Inventory - Other current assts - Net fixed assets Include a cash flow statement for 2016. b. Assume that the minimum cash balance is Maggie's 8% of revenue. Include a new account called "Long-term Debt" between Current Liabilities and Net Worth. Make sure the balance sheet balances and that no accounts are negative. Use the same "Trial Plug" and "Final Plug" methodology as in the Body Shop case. Chapter 1 Problem 14 a. Sources and Uses Statement: a. Prepare a sources and uses statement for Whistler Corp. for fiscal year 2017. b. Prepare a cash flow statement for Whistler Corp. for fiscal year 2017 WHISTLER CORP. BALANCE SHEET 2016 $47,500 Changes 29,200 43.100 (12,500) Sources of cash Reduction in inventories Increase in accumulated depreciation Increase in taxes payable Increase in long-term debt Increase in common stock Increase in retained earnings Total sources of cash 12,500 1,400 10,700 33,900 52.000 47,200 157,700 Cash Accounts receivable Inventories Total current assets Land Buildings Equipment Patents Less: Accumulated depreciation Total assets 49,000 96,500 15,800 103,600 63,200 5,200 10,800 273,500 2017 $76,700 43,100 36,500 156,300 15,800 164,600 65,500 5,200 12,200 395,200 61,000 2,300 1,400 Uses of cash Increase in cash Increase in accounts receivable Increase in buildings Increase in equipment Reduction in accounts payable Total uses of cash 29,200 43,100 61.000 2.300 22,100 157,700 (22,100) 10,700 48,000 0 48,000 100,100 148,100 25,900 10,700 36,600 134,000 170,600 b. Cash flow statement: 33.900 Accounts payable to suppliers Income taxes payable Total current liabilities Long term debt Total liabilities Shareholders' equity Common stock Retained earnings Total shareholders' equity Total liabilities and shareholders' equity 125,400 52.000 47,200 177,400 47,200 224,600 395,200 Operating activities Net income Add back depreciation (Increase) decrease in receivables (Increase) decrease in inventories Increase (decrease) in accounts payable Increase (decrease) in income taxes payable Cash flow from operations 125,400 273,500 69,200 1,400 (43,100) 12,500 (22,100) 10,700 28,600 INCOME STATEMENT INCOME STATEMENT 28,600 2017 Sales (61,000) (2,300) (63,300) Cost of goods sold Depreciation expense Net interest expense Other expense Income before taxes Provision for income taxes Net income $165,300 46,200 1,400 2,000 5,600 110,100 40,900 69,200 Cash flow from operations Investing activities (Increase) decrease in building (Increase) decrease in equipment Cash flow from investing Financing activities Increase (decrease) in long-term debt Increase (decrease) in common stock Subtract dividends paid Cash flow from financing 33,900 52,000 (22,000) 63,900 Cash flow from operations + investing + financing 29,200 Dividends paid Addition to retained earnings 22,000 47,200 Beginning cash balance 2017 | Ending cash balance 2017 Change in cash balance 2017 47,500 76,700 29,200 Exhibit 1 HORNIMAN HORTICULTURE Projected Horniman Horticulture Financial Summary (in thousands of dollars) Cash flow statement 2012 2013 2014 2015 2016F 2013 2014 2015 2016F 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 807.6 428.8 378.8 302.0 38.4 38.4 13.1 25.3 908.2 437.7 470.5 356.0 36.3 78.2 26.2 52.0 1,048.8 503.4 545.4 404.5 40.9 100.0 39.2 60.8 Cash flow from operating activities: Net income Depreciation (Increase) decrease in accounts receivable (Increase) decrease in inventory (Increase) decrease in other CA Increase (decrease) in accounts payable Increase (decrease) in wages payable Increase (decrease) in other payables Net cash flow from operating activities Plug Cash flow from investing activities Capital expenditures Balance sheet Cash Accounts receivable Inventory Other current assets2 Current assets Net fixed assets Total assets 120.1 90.6 468.3 20.9 699.9 332.1 1,032.0 105.2 99.5 507.6 19.3 731.6 332.5 1.064.1 66.8 119.5 523.4 22.6 732.3 384. 3 1,116.6 9.4 146.4 656.9 20.9 833.6 347.9 1,181.5 Net cash flow from investing activities Cash flow from financing activities Issuance (repayment) of LT debt Cash dividends paid 6.0 5.3 4.5 5.0 Accounts payable Wages payable Other payables 22.0 22.1 Net cash flow from financing activities 19.7 10.2 24.4 17.9 15.4 16.6 Sum of CF from operating, investing, and financing activities 35.9 42.7 43.2 47.3 Plug Current liabilities Long-term Debt Net worth Total liabilities & net worth 996.1 1,032.0 1,021.4 1,064.1 1,073.4 1,116.6 1,134.2 1,181.5 Cash at beginning of year Cash at end of year Change in cash balance Trial assets Trial liabilities & net worth Trial plug Minimum cash balance Final plug (=Trial plug + Min cash balance) Supplemental figures: Capital expenditure Purchasest Cash dividends 22.0 140.8 0.0 38.8 145.2 0.0 88.1 161.2 0.0 4.5 185.1 0.0 2012 2013 2014 2015 Average Forecast Historical ratios Growth rate in Revenues COGS / Revs SG&A / Revs Depreciation / Net fixed assets Tax rate Cash / Revs Accts Rec / Revs Inv/ Revs Other CA/ Revs Cap Ex/ Revs Accts pay / Revs Wages payable / Revs Other payables / Revs Dividend payout ratio 2012 2013 2014 2015 2016F Sustainable growth rate analyg Profit margin, P Retention ratio, R Asset turnover, A Financial leverage, T-hat g* Actual (or projected) growth in revs Actual (or projected) growth in assets 1 Inventory investment was valued at the lower of cost or market. The cost of inventory was determined by accumulating the costs associated with preparing the plants for sale. Costs that were typically capitalized as inventory included direct 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. * Purchases represented the annual amount paid to suppliers. Exhibit 2 HORNIMAN HORTICULTURE Financial Ratio Analysis and Benchmarking 2012 2013 2014 2015 Benchmark Revenue growth Gross margin (Gross profit / Revenue) Operating margin (Op. profit / Revenue) Net profit margin (Net profit / Revenue) 2.9% 48.9% 6.4% 4.1% 2.4% 46.9% 4.8% 3.1% 12.5% 51.8% 8.6% 5.7% 15.5% 52.0% 9.5% 5.8% (1.8)% 48.9% 7.6% 2.8% 2.9% Return on assets (Net profit / Total assets) Return on capital (Net profit / Total capital) 3.2% 3.3% 2.4% 2.5% 4.7% 4.8% 5.1% 5.4% 4.0% Receivable days (AR/ Revenue * 365) Inventory days (Inventory / COGS * 365) Payable days (AP/ Purchases * 365) NFA turnover (Revenue / NFA) 41.9 424.2 15.6 45.0 432.1 13.3 2.4. 48.0 436.5 10.2 50.9 476.3 9.9 21.8 386.3 26.9 2.7 9.9 2.4 2.4 3.0 Benchmark figures are based on 2014 financial ratios of publicly traded horticulture producers Cash conversion cycle 2. Where is the cash going? a. You will note that Exhibit 1 does not include a cash flow statement. Construct cash flow statements for 2013, 2014, and 2015. See Problem 14 from Chapter 1 of Higgins for an example of how to construct a cash flow statement. This problem is in the tab called 1.14. b. Reinforce the conclusion from part a by computing the Cash Conversion Cycle for 2012, 2013, 2014, and 2015. 2B. Will strong business performance in 2016 improve the cash position? a. Extend the financial statements through 2016, assuming that Bob Brown grows revenues by 30% Note: Make cash to be the plug. Calculate it as: Current liabilities + Net worth - Accounts receivable - Inventory - Other current assts - Net fixed assets Include a cash flow statement for 2016. b. Assume that the minimum cash balance is Maggie's 8% of revenue. Include a new account called "Long-term Debt" between Current Liabilities and Net Worth. Make sure the balance sheet balances and that no accounts are negative. Use the same "Trial Plug" and "Final Plug" methodology as in the Body Shop case. Chapter 1 Problem 14 a. Sources and Uses Statement: a. Prepare a sources and uses statement for Whistler Corp. for fiscal year 2017. b. Prepare a cash flow statement for Whistler Corp. for fiscal year 2017 WHISTLER CORP. BALANCE SHEET 2016 $47,500 Changes 29,200 43.100 (12,500) Sources of cash Reduction in inventories Increase in accumulated depreciation Increase in taxes payable Increase in long-term debt Increase in common stock Increase in retained earnings Total sources of cash 12,500 1,400 10,700 33,900 52.000 47,200 157,700 Cash Accounts receivable Inventories Total current assets Land Buildings Equipment Patents Less: Accumulated depreciation Total assets 49,000 96,500 15,800 103,600 63,200 5,200 10,800 273,500 2017 $76,700 43,100 36,500 156,300 15,800 164,600 65,500 5,200 12,200 395,200 61,000 2,300 1,400 Uses of cash Increase in cash Increase in accounts receivable Increase in buildings Increase in equipment Reduction in accounts payable Total uses of cash 29,200 43,100 61.000 2.300 22,100 157,700 (22,100) 10,700 48,000 0 48,000 100,100 148,100 25,900 10,700 36,600 134,000 170,600 b. Cash flow statement: 33.900 Accounts payable to suppliers Income taxes payable Total current liabilities Long term debt Total liabilities Shareholders' equity Common stock Retained earnings Total shareholders' equity Total liabilities and shareholders' equity 125,400 52.000 47,200 177,400 47,200 224,600 395,200 Operating activities Net income Add back depreciation (Increase) decrease in receivables (Increase) decrease in inventories Increase (decrease) in accounts payable Increase (decrease) in income taxes payable Cash flow from operations 125,400 273,500 69,200 1,400 (43,100) 12,500 (22,100) 10,700 28,600 INCOME STATEMENT INCOME STATEMENT 28,600 2017 Sales (61,000) (2,300) (63,300) Cost of goods sold Depreciation expense Net interest expense Other expense Income before taxes Provision for income taxes Net income $165,300 46,200 1,400 2,000 5,600 110,100 40,900 69,200 Cash flow from operations Investing activities (Increase) decrease in building (Increase) decrease in equipment Cash flow from investing Financing activities Increase (decrease) in long-term debt Increase (decrease) in common stock Subtract dividends paid Cash flow from financing 33,900 52,000 (22,000) 63,900 Cash flow from operations + investing + financing 29,200 Dividends paid Addition to retained earnings 22,000 47,200 Beginning cash balance 2017 | Ending cash balance 2017 Change in cash balance 2017 47,500 76,700 29,200 Exhibit 1 HORNIMAN HORTICULTURE Projected Horniman Horticulture Financial Summary (in thousands of dollars) Cash flow statement 2012 2013 2014 2015 2016F 2013 2014 2015 2016F 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 807.6 428.8 378.8 302.0 38.4 38.4 13.1 25.3 908.2 437.7 470.5 356.0 36.3 78.2 26.2 52.0 1,048.8 503.4 545.4 404.5 40.9 100.0 39.2 60.8 Cash flow from operating activities: Net income Depreciation (Increase) decrease in accounts receivable (Increase) decrease in inventory (Increase) decrease in other CA Increase (decrease) in accounts payable Increase (decrease) in wages payable Increase (decrease) in other payables Net cash flow from operating activities Plug Cash flow from investing activities Capital expenditures Balance sheet Cash Accounts receivable Inventory Other current assets2 Current assets Net fixed assets Total assets 120.1 90.6 468.3 20.9 699.9 332.1 1,032.0 105.2 99.5 507.6 19.3 731.6 332.5 1.064.1 66.8 119.5 523.4 22.6 732.3 384. 3 1,116.6 9.4 146.4 656.9 20.9 833.6 347.9 1,181.5 Net cash flow from investing activities Cash flow from financing activities Issuance (repayment) of LT debt Cash dividends paid 6.0 5.3 4.5 5.0 Accounts payable Wages payable Other payables 22.0 22.1 Net cash flow from financing activities 19.7 10.2 24.4 17.9 15.4 16.6 Sum of CF from operating, investing, and financing activities 35.9 42.7 43.2 47.3 Plug Current liabilities Long-term Debt Net worth Total liabilities & net worth 996.1 1,032.0 1,021.4 1,064.1 1,073.4 1,116.6 1,134.2 1,181.5 Cash at beginning of year Cash at end of year Change in cash balance Trial assets Trial liabilities & net worth Trial plug Minimum cash balance Final plug (=Trial plug + Min cash balance) Supplemental figures: Capital expenditure Purchasest Cash dividends 22.0 140.8 0.0 38.8 145.2 0.0 88.1 161.2 0.0 4.5 185.1 0.0 2012 2013 2014 2015 Average Forecast Historical ratios Growth rate in Revenues COGS / Revs SG&A / Revs Depreciation / Net fixed assets Tax rate Cash / Revs Accts Rec / Revs Inv/ Revs Other CA/ Revs Cap Ex/ Revs Accts pay / Revs Wages payable / Revs Other payables / Revs Dividend payout ratio 2012 2013 2014 2015 2016F Sustainable growth rate analyg Profit margin, P Retention ratio, R Asset turnover, A Financial leverage, T-hat g* Actual (or projected) growth in revs Actual (or projected) growth in assets 1 Inventory investment was valued at the lower of cost or market. The cost of inventory was determined by accumulating the costs associated with preparing the plants for sale. Costs that were typically capitalized as inventory included direct 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. * Purchases represented the annual amount paid to suppliers. Exhibit 2 HORNIMAN HORTICULTURE Financial Ratio Analysis and Benchmarking 2012 2013 2014 2015 Benchmark Revenue growth Gross margin (Gross profit / Revenue) Operating margin (Op. profit / Revenue) Net profit margin (Net profit / Revenue) 2.9% 48.9% 6.4% 4.1% 2.4% 46.9% 4.8% 3.1% 12.5% 51.8% 8.6% 5.7% 15.5% 52.0% 9.5% 5.8% (1.8)% 48.9% 7.6% 2.8% 2.9% Return on assets (Net profit / Total assets) Return on capital (Net profit / Total capital) 3.2% 3.3% 2.4% 2.5% 4.7% 4.8% 5.1% 5.4% 4.0% Receivable days (AR/ Revenue * 365) Inventory days (Inventory / COGS * 365) Payable days (AP/ Purchases * 365) NFA turnover (Revenue / NFA) 41.9 424.2 15.6 45.0 432.1 13.3 2.4. 48.0 436.5 10.2 50.9 476.3 9.9 21.8 386.3 26.9 2.7 9.9 2.4 2.4 3.0 Benchmark figures are based on 2014 financial ratios of publicly traded horticulture producers Cash conversion cycle