Scenario and Objective

You are a partner at a venture capital firm, which investigates troubled small businesses and makes a bid to buyout the owners of the company. You have been asked to examine a small plumbing supply company in the outskirts of Atlanta the company has seen tremendous growth, but has continually experienced cash flow problems. Your job is to diagnose the major sources contributing to the companys cash flow problems, determine if the problem can be solved, and prepare an estimate of what you think the company is worth to make a bid to the owners.

Background

Jim Riley, the current owner of Highland Plumbing, believed that the North Atlanta suburbs of Cherokee County and Forsyth County were about to take off in the early 2020s so he started a plumbing and heating supply business to benefit from the expected growth. Fortunately, the targeted counties of Cherokee and Forsyth did experience significant growth over the next decade as the following population table indicates.

2007 Cherokee 112,971 Forsyth 61,483

2017 Cherokee 195,327 Forsyth 150,968

Despite the fact that profits were growing in tandem, the company experienced continued cash flow problems. As a result, Riley finds that an increasing amount of his time is being devoted to dealing with the cash flow problems. The company has normally relied on bank loan financing secured by accounts receivable and inventory. However, in 2017 the company was unable to reduce its bank loan during the seasonal slowdown period. Furthermore, the companys manufacturer suppliers were becoming unhappy. Some had even started to demand payment on delivery rather than offer the 2/10, n/30 terms standard in both the manufacturer and wholesaler markets. Riley is not sure what he should do. He expects that 2018 sales will be 30% higher than the prior year and that there will be continued strength in sales in the following years.

Prepare one cash flow statement (operating-investing-financing) for 2015-2016 and a second one for 2016-2017. Do not hard code numbers in Excel formulas. The ONLY worksheet with input numbers should be the first Do not aggregate any accounts.

Calculate next years Pro Forma, and then extend for the next 5 years Estimate growth at the continued 30% External Financing Requited due to growth will be met by additional bank loans Since the calculation of interest expense depends the amount of the bank loan, and the amount of the bank loan depends on the interest expense, a circular reference is formed Excel>File>Options>Formulas>Enable iterative calculation

Calculate the Common Size Income Statement and Balance Sheet Divide all of the income statement accounts by that years sales. Scale the balance sheet by total assets. For sample,Common-Size Salesfor the year 2018 would be 7,260/7,260 = 100%. 2018s Common-Size COGS is 5,765/7,260 = 80.1%, and so on.

Calculate the Financial Ratios

After examining the statement of cash flows, common size statements, and financial ratios, what is Highland Plumbings biggest problem? What is the primary cause? Prepare a back of the envelope valuation of the company, which is the price we will offer Riley for is EQUITY in the company.

Rate of Interest of Bank Loan is not given

2017 Balance Sheets December 31 (thousands of dollars 2015 2016 Cash 75 90 Accounts receivable 1.206 1.440 Inventory 1.377 1,683 Prepaids 36 60 Net fixed assets 285 321 Total Assets 2.979 3.594 66 1.875 2.091 102 360 4.494 Bank loan 750 831 Accounts payable 615 1.050 Misc. accruals 120 168 Mortgage 150 111 Equity 1,314 1,401 Total Liabilities and Equity 2.979 3,594 1.020 1.737 102 138 1.497 4.494 Income Statements December 31 (thousands of dollars) 2015 2016 2017 Sales 7.260 9.075 11.370 Cost of goods soll 5.874 7.440 9,438 Gross profit 1,386 1,633 1,932 Operating expenses Selling expense 567 696 813 Salaries 315 411 492 General expenses 110 173 201 Depreciation 45 36 48 Total Operating Expenses 1.067 1.316 1.584 EBIT 320 320 3.18 Interest 8:3 89 103 Earnings before tax 237 231 243 Tax 9:3 84 87 Nct income 1.14 117 156 13 2017 Balance Sheets December 31 (thousands of dollars 2015 2016 Cash 75 90 Accounts receivable 1.206 1.440 Inventory 1.377 1,683 Prepaids 36 60 Net fixed assets 285 321 Total Assets 2.979 3.594 66 1.875 2.091 102 360 4.494 Bank loan 750 831 Accounts payable 615 1.050 Misc. accruals 120 168 Mortgage 150 111 Equity 1,314 1,401 Total Liabilities and Equity 2.979 3,594 1.020 1.737 102 138 1.497 4.494 Income Statements December 31 (thousands of dollars) 2015 2016 2017 Sales 7.260 9.075 11.370 Cost of goods soll 5.874 7.440 9,438 Gross profit 1,386 1,633 1,932 Operating expenses Selling expense 567 696 813 Salaries 315 411 492 General expenses 110 173 201 Depreciation 45 36 48 Total Operating Expenses 1.067 1.316 1.584 EBIT 320 320 3.18 Interest 8:3 89 103 Earnings before tax 237 231 243 Tax 9:3 84 87 Nct income 1.14 117 156 13