CASE 8 SPARTAN ROOFING LOAN EVALUATION "The Spartan Roofing Company makes excellent products!" So concluded a laudatory independent report commissioned by a bank in 1996. THE SPARTAN ROOFING COMPANY The Spartan Roofing Company was established in 1951 as a sole proprietorship in Ohio. The company is an innovative manufacturer of aluminum roofing products. Its standard products include the safeguard gravel stop system, the reglet and expansion joint system, and a wide variety of roofing panel systems. The safeguard system, which is patented, is designed to correct three major problems encountered by the roofing industry-water leakage of joints, tar drippage on the exterior of the finished building, and shrinkage. The company's products are designed to favor both building owner and con- tractor. The building owner benefits because of design technology that ensures Spartan's materials will last many years with little maintenance. The contractor benefits because the materials are simple to install. As a result the firm's prod- ucts are extremely popular with the building industry, and many architects and engineers specify Spartan materials by brand name. Market studies support this claim and also indicate that Spartan could increase its market share sub- stantially, given that the company has only a 5-percent share at present. Not surprisingly the firm is very strong in technical expertise; the engineering department is an important component of the business, and the development of new products is very much encouraged. Consequently, the company has been granted over eight patents in the last 20 years. While Spartan's technical expertise is unquestioned, the firm is a bit suspect in financial matters. Periodically it has had difficulties with such matters as inventory control and pricing. Historically, however, when the firm has experi- enced serious problems in an area it has been able to show improvement by the #2 50 PART II FINANCIAL ANALYSIS following year. All things considered, there is little doubt that Spartan has com- piled an impressive track record over the last 20 years. In fact, when one bank evaluated Spartan in 1991 it concluded that "this is one of the best-run compa- nies we have ever encountered in its size-sales category." In 1985, the firm relocated to Tennessce at the suggestion of Lawrence Wilson, the firm's CEO and son of the founder. The company's bank has been First City since the move South, but Tennessee National Bank (TNB) has aggressively sought Spartan's business. Since 1985 TNB has called on Wilson on more than 30 occasions and has made six different financing proposals. Wilson was always reluctant to switch, though he almost left First City for TNB in November 1993 because he was upset at First City for a reason never clear to TNB. JOHN PATTILLA In December 1994 John Pattilla of TNB had written a 12-page memorandum recommending that the bank make a specific financing proposal to Spartan. Pattilla's analysis was quite thorough and included projections of the firm's sit- uation using a "best guess" or "most likely" and "worst case" set of assump- tions. (See Exhibit 1 for excerpts from his 1994 report, Exhibit 2 for his net income projections, Exhibit 3 for his balance sheet projections, and Exhibit 4 for his "worst case" cash flow estimates.) Pattilla had a few conversations with Wilson to get information for the pro- jections. The final estimates, however, reflect Pattilla's assessment of the situa- tion, and Wilson has never seen these numbers. Pattilla had recommended that TNB extend the company up to $700,000 in loans. The money would be used to finance Spartan's working capital and fixed-asset needs for anticipated strong sales growth from an expanding economy and the introduction of a number of new products. The company was especially excited about its new macrocarbide product line. Macrocarbide is known for its unusually long life and is therefore very useful in the roofing industry. It is now early 1996 and Pattilla has to decide whether TNB should make another proposal to Lawrence Wilson, As Pattilla leans back in his chair, he leafs through information on Spartan, and a number of items catch his eye. He smiles as he notices the nearly 40-percent increase in sales for 1995, an increase he pre- dicted almost to the penny. Pattilla realizes, however, that Spartan must have rmously from the economic upturn, since the sales of its new products have not gone as well as expected. Especially disappointing were the sales of Spartan's macrocarbide product line. It appears to Pattilla that the sales growth is due more to an external factor outside the company's control the economy-than to internal factors under its control. He also notes that Wilson tapped the firm's line of credit with First City for over $500,000, a result not at all consistent with his 1994 projections. Pattilla knows that any proposal would involve a buy-out of First City, and TNB's offer should be at least the amount of Spartan's credit line with First City, or about $750,000. Pattilla had concluded CASE 8 SPARTAN ROOFING 51 his 1994 report by noting that "this represents an excellent opportunity for TNB." But now, nearly 14 months after he made that recommendation, Pattilla wonders if he will reach the same conclusions as he begins his evaluation of Spartan's financial records. QUESTIONS 1. (a) Complete the 1995 pro forma balance sheet listed in Exhibit 3. (b) Complete the "worst case" cash flow projection for 1997 listed in Exhibit 4. 2. Do you agree that TNB should have actively sought Spartan's business at the end of 1994? Fully support your answer. (Note: When this recommenda- tion was made, Pattilla only had financial information on the first three quarters of 1994. He did possess, however, a projection for the last quarter that was virtually identical to what actually occurred.) 3. Calculate the ratios listed in Exhibit 9 for the Spartan Roofing Company for 1995. 4. Evaluate the Spartan Roofing Company's situation at the present time (early 1996). What difficulties, if any, does your evaluation indicate? 5. Note that in 1995 Spartan's cash flow from operations (net income plus depreciation) was its highest during the 19921995 period. Yet its need for external funds, reflected in the large increase in its short-term bank loans, was also the greatest over the same period. Resolve this apparent paradox. 6. Use the format of Exhibit 4. (a) Prepare what you think is a "best guess" ("most likely") cash flow fore- cast for 1996. State your assumptions clearly. Do assume, however, that capital expense will be $120(000) and depreciation remains at $80(000). Defend the assumptions of your forecast. (b) Prepare what you think is a "worst case" cash flow forecast for 1996. Clearly state your assumptions. Do assume, however, that there is no change in sales from the 1995 level, depreciation remains at $80(000), and that the firm will postpone all expansion projects and incur only replacement capital expenses of $50(000). Defend the assumptions of your forecast. 7. Do a liquidation analysis on the firm for the end of 1995. That is, estimate its liquidation value and compare it to the amount of its debt obligations. It is customary in a situation like this for Pattilla to make the following perhaps conservative, assumptions: (1) all cash on the balance sheet is used for liqui- dation expenses; and (2) receivables can be converted into cash at 60 percent of book value, inventory at 30 percent, and net fixed assets at 25 percent. CASE 8 SPARTAN ROOFING 53 EXHIBIT 1 Excerpts from John Pattilla's 1994 Report on the Spartan Roofing Company "The company expects, and I agree, that sales growth will be 37 percent, 20 percent, and 20 percent over the next three years. This will result partly, from the economic growth of the overall economy but mainly from the sales generated by its new macrocarbide products." "Lawrence Wilson is exceptionally strong in the technical aspects of the busi ness and is very much involved in new product development. He has a B.S. and an M.S. in chemical engineering from MIT. On the other hand, being a technical sort, his financial expertise is somewhat limited. Nonetheless, Wilson's track record over the last 10 years speaks for itself, and we have had no reason to question his integrity." "One area of concern is the quality of the accounts receivable, given that the majority of the customers are contractors. It should be noted, however, that ontractors have been doing business with Spartan for a number of years and the company is familiar with their financial condition In fact, its bad debt expense has never exceeded 0.6 percent in any of the last 10 years, a solid statistic considering the customer base." "The main reason for the company's poor earnings in 1994 was the large expenses incurred by a former salesman of the company. Lawrence hired a salesman who was very expensive but did not produce. As a result, selling expenses increased sharply in 1994." "From 1992 to 1994, the period I analyzed most intensively, Spartan gener- ated sufficient cash flow from operations to meet its capital expenditure needs and its debt service. Adjusted working capital was a net source of funds for the company which partly reflects the attention given to the man- agement of working capital. The result of all this was a large positive cash flow (see Exhibit 8) which Spartan used to repay a number of relatively expensive term loans. Whether this was a wise financial move is debatable." "Due to the working capital requirements of the large expected sales growth and the fixed-asset needs of the company, the company will not be in a posi- tion to begin repaying any of the principal on the debt until 1997." "Even my 'worst case' cash flow forecast indicates that Spartan will have sufficient funds to repay sizeable chunks of any new debt beginning in 1997." (See Exhibit 4.) (continued) 54 PART II FINANCIAL ANALYSIS EXHIBIT 1 (Continued) "I recommend we finance Spartan's working capital and fixed asset needs. This will require up to $700,000. I also recommend that we do not amortize any of the loans for two years. The loans would be secured by the accounts receivable, inventory, and equipment of the company, and would be person- ally guaranteed by Lawrence." "I believe this to be an attractive opportunity for us, but it does involve several elements of risk. The company's sales and earnings are quite sensi- tive to overall economic conditions and the construction industry in particu- lar. In addition, the nature of the customer base (contractors) is suspect. On the other hand, given the company's track record, it appears that they have effectively managed these risks." EXHIBIT 2 Net Income Projections for the Spartan Roofing Company: 1995-1997 (000s) (Developed by John Pattilla for TNB in 1994) 1995 1996 1997 B.C. W.C. B.G. W.C. R.G. wc. Sales Net income 4,900 55,400 $135 5,000 $6,500 $162 5,300 80 $7,800 $195 75 74 B.G. is the "best gas" ("most likely") forecast and assumes sale will increase by 37 penent in 1995.20 percent in 1996, and 20 percent in 1997. Net income is assumed to be 2.5 precent of sales WC is a "worst case" forecast that assumes net income will be 1.5 percent of sales. EXHIBIT 3 Pro Forma Balance Sheets for the Spartan Roofing Company: 1995-1997 (000s) (Developed by John Pattilla for TNB in 1994) 1995 1996 1997 B.G. W.C. B.G. W.C. B.G. W.C. $10 $50 993 1,193 Assels Cash Receivables Inventory Current assets Gross fixed assets $40 833 694 $1,567 776 550 863 736 $1,669 1,309 931 S40 816 680 $1,536 1,339 $1,510 929 $1,819 1,309 $2,194 1,339 929 (continued) CASE 8 SPARTAN ROOFING 55 EXHIBIT 3 (Continued) 1995 1996 1997 B.G. W.C.) B.G. W.C. B.G. W.C. (537) (657) (657) (787) (787) 392 (537) 392 $1,959 652 652 552 552 $1,902 $2,471 $2,321 $2,736 $2,088 (Depreciation) Net fixed assets Total assets Liabilities and Equity Short-term debt due Accounts payable Accruals Current liabilities Term loans Cominon stock Retained earnings Total liabilities and equity $169 210 324 $326 194 $198 252 390 $8410 425 $308 206 318 300 5820 $166 303 468 $937 400 $66 190 294 703 155 155 50 50 50 $832 425 50 1,014 $2.321 50 $550 400 50 1.088 $2,088 994 $1.902 934 $1.959 1,156 $2,471 1,351 $2,738 B.G. return to a "best guess" ("most likely) furecast that incorporates these assumptions:(1) receivables will be 55 days of sales; (2) inventory turnover will be 8.4 (43 days of sales); 3) accounts payable will be 3.9 percent of sules (round your estimate down); (9) accruals will be percent of sales; and (5) capital expenditures will be $90 in 1995, 5380 in 1995, and $30 in 1997. "W.C. refers to a "worst case" forecast that incorporates these assumptions (1) receivables will be 60 days of sales; and (2) inventory turnover will be 7.2 (50 days of sales). Assumptions 3 to 5 in the "hest guess situation remain unchanged. EXHIBIT 4 Worst Case* Cash Flow Projections for the Spartan Roofing Company: 1995-1997 (000s) (Developed by John Pattilla in 1994) 1995 1996 1997 $75 $80 120 $200 $74 130 $204 Net income Depreciation Cash flow operations - Adjusted working capital needs - Capital expense - Dividends Cash flow $155 332 90 380 30 ($267) ($242) $257 (continued) 56 PART II FINANCIAL ANALYSIS EXHIBIT 4 (Continued) Adjusted Working Capital 1994 1995 1996 1997 5833 694 194 Accounts receivable Inventory - Accounts payable - Accruals Adjusted working capital Change achjusted working capital 5883 736 206 318 $1,095 $701 300 $1,033 332 62 This assures (1) net income will be 1.5 percent of sale: (2) receivable will be 60 days of sales: 3) inventory turnover will be 7.2 or 50 days of sales; and (4) accounts payable will be 3.9 percent of sales and accruals 6 percent (See Exhibits 2 and 3 for projecind net income and proforma balance sheets) EXHIBIT 5 Income Statements and Bad Debt Expense for the Spartan Roofing Company: 1992-1995 (000s) 1992 1993 1994 1995 53,978 2.884 $1,094 $3,902 2,872 $1,030 762 $3.946 2,959 $ 987 837 $5,399 4,173 $1,226 904 815 80 Sales Cost of goods Gross profit Administrative & selling expenses Depreciation EBIT Interest Earnings before taxes Taxes (40%) Net income Bad debt expense 53 $226 S207 595 5242 20 80 5160 $163 $162 $75 30 64 $45 $96 0.1% 598 0.6% $97 0.9% EXHIBIT 6 Normalized Income Statements for the Spartan Roofing Company: 1992-1995 1992 1993 1994 1995 100 100 72.5 100 73.6 75 100 773 227 26.4 25 21.2 19.5 1.6 1.3 Sales Cost of goods Gross profit Administrative & selling expenses Depreciation EBIT Interest Earnings before taxes Taxes (40%) Net income CASE 8 SPARTAN ROOFING 57 EXHIBIT 7 Balance Sheets for the Spartan Roofing Company: 1992-1995 (000) 1992 1993 1994 1995 5120 785 1,124 7773 585 707 476 $1,268 669 (251) 418 $1,686 $21 580 429 $1,029 757 (367) $1,351 698 $1,940 914 (312) 386 $1,737 390 Assets Cash Receivables Inventory Current assets Gross fixed assets (Accumulated depreciation) Net fixed assets Total assets Liabilities and Equity Notes payable-banks Accounts payable Accruals Current liabilities Term loans Cominon stock Retained earnings Total liabilities and equity _467 52,407 $1,419 S180 $162 $700 148 150 306 218 5546 374 $220 157 272 $654 219 15Z $469 310 $1,316 85 50 716 814 956 859 51,419 $1,686 $1,737 $2,407 EXHIBIT 8 Spartan's Cash Flow 1992-1994 (000s) 1992 1993 1994 596 $45 53 $149 598 61 $159 55 $100 Net income Depreciation Cash flow operations - Adjusted working capital needs -Capital expenditures -- Dividends Cash flow (80) (20) (96) 59 40 - O S189 $150 S137 Adjusted Workmg Capital 1991 1992 1993 1994 $785 Accounts receivable Inventory - Accounts payable -Accruals Adjusted working capital Change adjusted working capital S707 476 148 218 9817 (80) 157 277 $797 (20) $580 428 150 15Z $701 (96) 58 PART II FINANCIAL ANALYSIS EXHIBIT 9 Financial Ratios for the Spartan Roofing Company: 1992-1995 Indiestry Average 1992-1995 1992 1993 1994 1995 Liquidity Ratios Current 2.32 2.07 2.19 Quick 1.38 1.28 Leverage Ratios Debt (%) 36 Times interest earned Activity Ratios Inventory turnover (sales) 84 8.7 9.2 Fixed asset turnover 9.52 10.1 10.1 Total asset turnover 2.36 2.25 2.78 Average collection period 64 72 Profitability Ratios Gross margin (4) 28 26 25 Net profit margin (%) 24 Return on net worth (%) 125 11.3 Return on total assets (5) 5.7 56 3.2 10.4 5.8 "The three numbers for each ralio are computed in the following way. Ratios for all firms in the industry are arranged in what is considered a strongest-to-weakest order. The middle number represents the median ratio that is half the firms in the industry had ratio better than the median ratio and half had ratios that were worse. The top number represents the upper quartile figure, meaning 25 percent of the firm had ratios better than this. The lower number represents the lowest quartile; that is, 25 percent of the firms had ratios worse than this. CASE 8 SPARTAN ROOFING LOAN EVALUATION "The Spartan Roofing Company makes excellent products!" So concluded a laudatory independent report commissioned by a bank in 1996. THE SPARTAN ROOFING COMPANY The Spartan Roofing Company was established in 1951 as a sole proprietorship in Ohio. The company is an innovative manufacturer of aluminum roofing products. Its standard products include the safeguard gravel stop system, the reglet and expansion joint system, and a wide variety of roofing panel systems. The safeguard system, which is patented, is designed to correct three major problems encountered by the roofing industry-water leakage of joints, tar drippage on the exterior of the finished building, and shrinkage. The company's products are designed to favor both building owner and con- tractor. The building owner benefits because of design technology that ensures Spartan's materials will last many years with little maintenance. The contractor benefits because the materials are simple to install. As a result the firm's prod- ucts are extremely popular with the building industry, and many architects and engineers specify Spartan materials by brand name. Market studies support this claim and also indicate that Spartan could increase its market share sub- stantially, given that the company has only a 5-percent share at present. Not surprisingly the firm is very strong in technical expertise; the engineering department is an important component of the business, and the development of new products is very much encouraged. Consequently, the company has been granted over eight patents in the last 20 years. While Spartan's technical expertise is unquestioned, the firm is a bit suspect in financial matters. Periodically it has had difficulties with such matters as inventory control and pricing. Historically, however, when the firm has experi- enced serious problems in an area it has been able to show improvement by the #2 50 PART II FINANCIAL ANALYSIS following year. All things considered, there is little doubt that Spartan has com- piled an impressive track record over the last 20 years. In fact, when one bank evaluated Spartan in 1991 it concluded that "this is one of the best-run compa- nies we have ever encountered in its size-sales category." In 1985, the firm relocated to Tennessce at the suggestion of Lawrence Wilson, the firm's CEO and son of the founder. The company's bank has been First City since the move South, but Tennessee National Bank (TNB) has aggressively sought Spartan's business. Since 1985 TNB has called on Wilson on more than 30 occasions and has made six different financing proposals. Wilson was always reluctant to switch, though he almost left First City for TNB in November 1993 because he was upset at First City for a reason never clear to TNB. JOHN PATTILLA In December 1994 John Pattilla of TNB had written a 12-page memorandum recommending that the bank make a specific financing proposal to Spartan. Pattilla's analysis was quite thorough and included projections of the firm's sit- uation using a "best guess" or "most likely" and "worst case" set of assump- tions. (See Exhibit 1 for excerpts from his 1994 report, Exhibit 2 for his net income projections, Exhibit 3 for his balance sheet projections, and Exhibit 4 for his "worst case" cash flow estimates.) Pattilla had a few conversations with Wilson to get information for the pro- jections. The final estimates, however, reflect Pattilla's assessment of the situa- tion, and Wilson has never seen these numbers. Pattilla had recommended that TNB extend the company up to $700,000 in loans. The money would be used to finance Spartan's working capital and fixed-asset needs for anticipated strong sales growth from an expanding economy and the introduction of a number of new products. The company was especially excited about its new macrocarbide product line. Macrocarbide is known for its unusually long life and is therefore very useful in the roofing industry. It is now early 1996 and Pattilla has to decide whether TNB should make another proposal to Lawrence Wilson, As Pattilla leans back in his chair, he leafs through information on Spartan, and a number of items catch his eye. He smiles as he notices the nearly 40-percent increase in sales for 1995, an increase he pre- dicted almost to the penny. Pattilla realizes, however, that Spartan must have rmously from the economic upturn, since the sales of its new products have not gone as well as expected. Especially disappointing were the sales of Spartan's macrocarbide product line. It appears to Pattilla that the sales growth is due more to an external factor outside the company's control the economy-than to internal factors under its control. He also notes that Wilson tapped the firm's line of credit with First City for over $500,000, a result not at all consistent with his 1994 projections. Pattilla knows that any proposal would involve a buy-out of First City, and TNB's offer should be at least the amount of Spartan's credit line with First City, or about $750,000. Pattilla had concluded CASE 8 SPARTAN ROOFING 51 his 1994 report by noting that "this represents an excellent opportunity for TNB." But now, nearly 14 months after he made that recommendation, Pattilla wonders if he will reach the same conclusions as he begins his evaluation of Spartan's financial records. QUESTIONS 1. (a) Complete the 1995 pro forma balance sheet listed in Exhibit 3. (b) Complete the "worst case" cash flow projection for 1997 listed in Exhibit 4. 2. Do you agree that TNB should have actively sought Spartan's business at the end of 1994? Fully support your answer. (Note: When this recommenda- tion was made, Pattilla only had financial information on the first three quarters of 1994. He did possess, however, a projection for the last quarter that was virtually identical to what actually occurred.) 3. Calculate the ratios listed in Exhibit 9 for the Spartan Roofing Company for 1995. 4. Evaluate the Spartan Roofing Company's situation at the present time (early 1996). What difficulties, if any, does your evaluation indicate? 5. Note that in 1995 Spartan's cash flow from operations (net income plus depreciation) was its highest during the 19921995 period. Yet its need for external funds, reflected in the large increase in its short-term bank loans, was also the greatest over the same period. Resolve this apparent paradox. 6. Use the format of Exhibit 4. (a) Prepare what you think is a "best guess" ("most likely") cash flow fore- cast for 1996. State your assumptions clearly. Do assume, however, that capital expense will be $120(000) and depreciation remains at $80(000). Defend the assumptions of your forecast. (b) Prepare what you think is a "worst case" cash flow forecast for 1996. Clearly state your assumptions. Do assume, however, that there is no change in sales from the 1995 level, depreciation remains at $80(000), and that the firm will postpone all expansion projects and incur only replacement capital expenses of $50(000). Defend the assumptions of your forecast. 7. Do a liquidation analysis on the firm for the end of 1995. That is, estimate its liquidation value and compare it to the amount of its debt obligations. It is customary in a situation like this for Pattilla to make the following perhaps conservative, assumptions: (1) all cash on the balance sheet is used for liqui- dation expenses; and (2) receivables can be converted into cash at 60 percent of book value, inventory at 30 percent, and net fixed assets at 25 percent. CASE 8 SPARTAN ROOFING 53 EXHIBIT 1 Excerpts from John Pattilla's 1994 Report on the Spartan Roofing Company "The company expects, and I agree, that sales growth will be 37 percent, 20 percent, and 20 percent over the next three years. This will result partly, from the economic growth of the overall economy but mainly from the sales generated by its new macrocarbide products." "Lawrence Wilson is exceptionally strong in the technical aspects of the busi ness and is very much involved in new product development. He has a B.S. and an M.S. in chemical engineering from MIT. On the other hand, being a technical sort, his financial expertise is somewhat limited. Nonetheless, Wilson's track record over the last 10 years speaks for itself, and we have had no reason to question his integrity." "One area of concern is the quality of the accounts receivable, given that the majority of the customers are contractors. It should be noted, however, that ontractors have been doing business with Spartan for a number of years and the company is familiar with their financial condition In fact, its bad debt expense has never exceeded 0.6 percent in any of the last 10 years, a solid statistic considering the customer base." "The main reason for the company's poor earnings in 1994 was the large expenses incurred by a former salesman of the company. Lawrence hired a salesman who was very expensive but did not produce. As a result, selling expenses increased sharply in 1994." "From 1992 to 1994, the period I analyzed most intensively, Spartan gener- ated sufficient cash flow from operations to meet its capital expenditure needs and its debt service. Adjusted working capital was a net source of funds for the company which partly reflects the attention given to the man- agement of working capital. The result of all this was a large positive cash flow (see Exhibit 8) which Spartan used to repay a number of relatively expensive term loans. Whether this was a wise financial move is debatable." "Due to the working capital requirements of the large expected sales growth and the fixed-asset needs of the company, the company will not be in a posi- tion to begin repaying any of the principal on the debt until 1997." "Even my 'worst case' cash flow forecast indicates that Spartan will have sufficient funds to repay sizeable chunks of any new debt beginning in 1997." (See Exhibit 4.) (continued) 54 PART II FINANCIAL ANALYSIS EXHIBIT 1 (Continued) "I recommend we finance Spartan's working capital and fixed asset needs. This will require up to $700,000. I also recommend that we do not amortize any of the loans for two years. The loans would be secured by the accounts receivable, inventory, and equipment of the company, and would be person- ally guaranteed by Lawrence." "I believe this to be an attractive opportunity for us, but it does involve several elements of risk. The company's sales and earnings are quite sensi- tive to overall economic conditions and the construction industry in particu- lar. In addition, the nature of the customer base (contractors) is suspect. On the other hand, given the company's track record, it appears that they have effectively managed these risks." EXHIBIT 2 Net Income Projections for the Spartan Roofing Company: 1995-1997 (000s) (Developed by John Pattilla for TNB in 1994) 1995 1996 1997 B.C. W.C. B.G. W.C. R.G. wc. Sales Net income 4,900 55,400 $135 5,000 $6,500 $162 5,300 80 $7,800 $195 75 74 B.G. is the "best gas" ("most likely") forecast and assumes sale will increase by 37 penent in 1995.20 percent in 1996, and 20 percent in 1997. Net income is assumed to be 2.5 precent of sales WC is a "worst case" forecast that assumes net income will be 1.5 percent of sales. EXHIBIT 3 Pro Forma Balance Sheets for the Spartan Roofing Company: 1995-1997 (000s) (Developed by John Pattilla for TNB in 1994) 1995 1996 1997 B.G. W.C. B.G. W.C. B.G. W.C. $10 $50 993 1,193 Assels Cash Receivables Inventory Current assets Gross fixed assets $40 833 694 $1,567 776 550 863 736 $1,669 1,309 931 S40 816 680 $1,536 1,339 $1,510 929 $1,819 1,309 $2,194 1,339 929 (continued) CASE 8 SPARTAN ROOFING 55 EXHIBIT 3 (Continued) 1995 1996 1997 B.G. W.C.) B.G. W.C. B.G. W.C. (537) (657) (657) (787) (787) 392 (537) 392 $1,959 652 652 552 552 $1,902 $2,471 $2,321 $2,736 $2,088 (Depreciation) Net fixed assets Total assets Liabilities and Equity Short-term debt due Accounts payable Accruals Current liabilities Term loans Cominon stock Retained earnings Total liabilities and equity $169 210 324 $326 194 $198 252 390 $8410 425 $308 206 318 300 5820 $166 303 468 $937 400 $66 190 294 703 155 155 50 50 50 $832 425 50 1,014 $2.321 50 $550 400 50 1.088 $2,088 994 $1.902 934 $1.959 1,156 $2,471 1,351 $2,738 B.G. return to a "best guess" ("most likely) furecast that incorporates these assumptions:(1) receivables will be 55 days of sales; (2) inventory turnover will be 8.4 (43 days of sales); 3) accounts payable will be 3.9 percent of sules (round your estimate down); (9) accruals will be percent of sales; and (5) capital expenditures will be $90 in 1995, 5380 in 1995, and $30 in 1997. "W.C. refers to a "worst case" forecast that incorporates these assumptions (1) receivables will be 60 days of sales; and (2) inventory turnover will be 7.2 (50 days of sales). Assumptions 3 to 5 in the "hest guess situation remain unchanged. EXHIBIT 4 Worst Case* Cash Flow Projections for the Spartan Roofing Company: 1995-1997 (000s) (Developed by John Pattilla in 1994) 1995 1996 1997 $75 $80 120 $200 $74 130 $204 Net income Depreciation Cash flow operations - Adjusted working capital needs - Capital expense - Dividends Cash flow $155 332 90 380 30 ($267) ($242) $257 (continued) 56 PART II FINANCIAL ANALYSIS EXHIBIT 4 (Continued) Adjusted Working Capital 1994 1995 1996 1997 5833 694 194 Accounts receivable Inventory - Accounts payable - Accruals Adjusted working capital Change achjusted working capital 5883 736 206 318 $1,095 $701 300 $1,033 332 62 This assures (1) net income will be 1.5 percent of sale: (2) receivable will be 60 days of sales: 3) inventory turnover will be 7.2 or 50 days of sales; and (4) accounts payable will be 3.9 percent of sales and accruals 6 percent (See Exhibits 2 and 3 for projecind net income and proforma balance sheets) EXHIBIT 5 Income Statements and Bad Debt Expense for the Spartan Roofing Company: 1992-1995 (000s) 1992 1993 1994 1995 53,978 2.884 $1,094 $3,902 2,872 $1,030 762 $3.946 2,959 $ 987 837 $5,399 4,173 $1,226 904 815 80 Sales Cost of goods Gross profit Administrative & selling expenses Depreciation EBIT Interest Earnings before taxes Taxes (40%) Net income Bad debt expense 53 $226 S207 595 5242 20 80 5160 $163 $162 $75 30 64 $45 $96 0.1% 598 0.6% $97 0.9% EXHIBIT 6 Normalized Income Statements for the Spartan Roofing Company: 1992-1995 1992 1993 1994 1995 100 100 72.5 100 73.6 75 100 773 227 26.4 25 21.2 19.5 1.6 1.3 Sales Cost of goods Gross profit Administrative & selling expenses Depreciation EBIT Interest Earnings before taxes Taxes (40%) Net income CASE 8 SPARTAN ROOFING 57 EXHIBIT 7 Balance Sheets for the Spartan Roofing Company: 1992-1995 (000) 1992 1993 1994 1995 5120 785 1,124 7773 585 707 476 $1,268 669 (251) 418 $1,686 $21 580 429 $1,029 757 (367) $1,351 698 $1,940 914 (312) 386 $1,737 390 Assets Cash Receivables Inventory Current assets Gross fixed assets (Accumulated depreciation) Net fixed assets Total assets Liabilities and Equity Notes payable-banks Accounts payable Accruals Current liabilities Term loans Cominon stock Retained earnings Total liabilities and equity _467 52,407 $1,419 S180 $162 $700 148 150 306 218 5546 374 $220 157 272 $654 219 15Z $469 310 $1,316 85 50 716 814 956 859 51,419 $1,686 $1,737 $2,407 EXHIBIT 8 Spartan's Cash Flow 1992-1994 (000s) 1992 1993 1994 596 $45 53 $149 598 61 $159 55 $100 Net income Depreciation Cash flow operations - Adjusted working capital needs -Capital expenditures -- Dividends Cash flow (80) (20) (96) 59 40 - O S189 $150 S137 Adjusted Workmg Capital 1991 1992 1993 1994 $785 Accounts receivable Inventory - Accounts payable -Accruals Adjusted working capital Change adjusted working capital S707 476 148 218 9817 (80) 157 277 $797 (20) $580 428 150 15Z $701 (96) 58 PART II FINANCIAL ANALYSIS EXHIBIT 9 Financial Ratios for the Spartan Roofing Company: 1992-1995 Indiestry Average 1992-1995 1992 1993 1994 1995 Liquidity Ratios Current 2.32 2.07 2.19 Quick 1.38 1.28 Leverage Ratios Debt (%) 36 Times interest earned Activity Ratios Inventory turnover (sales) 84 8.7 9.2 Fixed asset turnover 9.52 10.1 10.1 Total asset turnover 2.36 2.25 2.78 Average collection period 64 72 Profitability Ratios Gross margin (4) 28 26 25 Net profit margin (%) 24 Return on net worth (%) 125 11.3 Return on total assets (5) 5.7 56 3.2 10.4 5.8 "The three numbers for each ralio are computed in the following way. Ratios for all firms in the industry are arranged in what is considered a strongest-to-weakest order. The middle number represents the median ratio that is half the firms in the industry had ratio better than the median ratio and half had ratios that were worse. The top number represents the upper quartile figure, meaning 25 percent of the firm had ratios better than this. The lower number represents the lowest quartile; that is, 25 percent of the firms had ratios worse than this