butler excel sheets (group 2)
TRANSCRIPT
Butler Lumber Company
BUTLER LUMBER COMPANYEXHIBIT 1Operating Expenses for Years Ending December 31, 1988-1990, andfor First Quarter 1991 (thousands of dollars)
1st Qtr1988 1989 1990 1991
Net sales $1,697 $2,013 $2,694 $718 Cost of goods sold: Beginning inventory $183 $239 $326 $418 Purchases $1,278 $1,524 $2,042 $660
$1,461 $1,763 $2,368 $1,078 Ending inventory $239 $326 $418 $556 Total cost of goods sold $1,222 $1,437 $1,950 $522 Gross Profit $475 $576 $744 $196 Operating expenses $425 $515 $658 $175 Interest expense $13 $20 $33 $10 Net income before income taxes $37 $41 $53 $11 Provision for income taxes $6 $7 $9 $2 Net income $31 $34 $44 $9
a. In the first quarter of 1990 sales were $698,000 and net income was $7,000.
b. Operating expenses include a cash salary for Mr. Butler of $75,000 in 1988, $85,000in 1989, $95,000 in 1990, and $22,000 in the first quarter of 1991. Mr. Butler alsoreceived some of the perquisites commonly taken by owners of privately heldbusinesses.
Butler Lumber Company
BUTLER LUMBER COMPANYEXHIBIT 2Balance Sheets at December 31, 1988-1990, and March 31, 1991 (thousands of dollars)
1st Qrtr1988 1989 1990 1991
Cash $58 $49 $41 $31 Accounts receivable, net $171 $222 $317 $345 Inventory $239 $325 $418 $556 Current assets $468 $596 $776 $932 Property, net $126 $140 $157 $162 Total assets $594 $736 $933 $1,094
Notes payable, bank $0 $146 $233 $247 Notes payable, Mr. Stark $105 $0 $0 $0 Notes payable, trade $0 $0 $0 $157 Accounts payable $124 $192 $256 $243 Accrued expenses $24 $30 $39 $36 Long-term debt, current portion $7 $7 $7 $7 Current liabilities $260 $375 $535 $690 Long-term debt $64 $57 $50 $47 Total liabilities $324 $432 $585 $737 Net worth $270 $304 $348 $357 Total liabilities & net worth $594 $736 $933 $1,094
BUTLER LUMBER COMPANYSelected statistics: 1988 through 1990
1988 1989 1990 Average
Percent of sales Purchases 75.3% 75.7% 75.8% 75.6% Cost of goods sold 72.0% 71.4% 72.4% 71.9% Operating expenses 25.0% 25.6% 24.4% 25.0% Cash 3.4% 2.4% 1.5% 2.5% Accounts receivable 10.1% 11.0% 11.8% 11.0% Inventory 14.1% 16.1% 15.5% 15.2% Fixed assets (net) 7.4% 7.0% 5.8% 6.7% Total assets 35.0% 36.6% 34.6% 35.4%
Percent of total assets Current liabilities 43.8% 51.0% 57.3% 50.7% Long-term liabilities 10.8% 7.7% 5.4% 8.0% Equity 45.5% 41.3% 37.3% 41.4%
Current ratio 1.80 1.59 1.45 1.61 Return on sales (margin) 1.8% 1.7% 1.6% 1.7%Return on assets 5.2% 4.6% 4.7% 4.9%Return on equity 11.5% 11.2% 12.6% 11.8%
Sustainable Growth:Margin 1.83% 1.69% 1.63%Asset Turnover 2.86 2.74 2.89 Leverage (using bop equity) 2.73 3.07 Retention ratio 1.00 1.00 G-star 12.59% 14.47%Actual Growth (Sales) 18.62% 33.83%Actual Growth (Assets) 23.91% 26.77%
BUTLER LUMBER COMPANY
1988 1989 1990 Average
Percent of sales Purchases 75.3% 75.7% 75.8% 75.6% Cost of goods sold 72.0% 71.4% 72.4% 71.9% Operating expenses 25.0% 25.6% 24.4% 25.0% Cash 3.4% 2.4% 1.5% 2.5% Accounts receivable 10.1% 11.0% 11.8% 11.0% Inventory 14.1% 16.1% 15.5% 15.2% Fixed assets (net) 7.4% 7.0% 5.8% 6.7% Total assets 35.0% 36.6% 34.6% 35.4%
Percent of total assets Current liabilities 43.8% 51.0% 57.3% 50.7% Long-term liabilities 10.8% 7.7% 5.4% 8.0% Equity 45.5% 41.3% 37.3% 41.4%
Current ratio 1.80 1.59 1.45 1.61 Quick Ratio 0.88 0.72 0.67 0.76 Cash Ratio 0.22 0.13 0.08 0.14
Return on sales (margin) 1.8% 1.7% 1.6% 1.7%Return on assets 5.2% 4.6% 4.7% 4.9%Return on equity 11.5% 11.2% 12.6% 11.8%
Sustainable Growth:Margin 1.83% 1.69% 1.63%Asset Turnover 2.86 2.74 2.89 Leverage (using bop equity) 2.73 3.07 Retention ratio 1.00 1.00 G-star 12.59% 14.47%Actual Growth (Sales) 18.62% 33.83%Actual Growth (Assets) 23.91% 26.77%
BUTLER LUMBER CO.
Projected income statement for 1991 (thousands of dollars) Assumptions1991 Value
Net sales $ 3,600 $ 3,600 Cost of goods sold: Beginning inventory $418 Purchases $2,736 76%
$3,154 Ending inventory $562 Total cost of goods sold $2,592 72%Gross Profit $1,008 Operating expenses $900 25% Operating Profit $108 Purchase Discounts* $42 2% Interest expense** $53 10.50%Net income before income taxes $97 Provision for income taxes $21 34%Net income $76
Projected balance sheet for December 31, 1991 (thousands of dollars)1991
Assets:Cash $54 1.50%Accounts recievable, net (12% of sales) $432 12%Inventory $562 Current Assets $1,048 Property, net $216 6% Total Assets $1,264
Liabilities:Accounts payable $75 10Accrued expenses $54 1.50%Long-term debt, current portion $ 7 $ 7 Bank note payable (plug) $661 Current Liabilities $797 Long-term debt $43 Total Liabilities $840 Net worth $424 Total Liabilities plus net worth $1,264
Assumptions
ROA 5.99%ROE 17.87%ROBE 21.76%
BUTLER LUMBER CO.
Projected income statement for 1991 (thousands of dollars) Assumptions1991 Value
Net sales $ 3,084 1990 Growth adjusted for Sustainability Cost of goods sold: Beginning inventory $418 Purchases $2,344 76%
$2,762 Ending inventory $541 Total cost of goods sold $2,220 72%Gross Profit $864 Operating expenses $771 25% Operating Profit $93 Purchase Discounts* $34 2% Interest expense** $48 10.50%Net income before income taxes $78 Provision for income taxes $15 34%Net income $63
Projected balance sheet for December 31, 1991 (thousands of dollars)1991
Assets:Cash $46 1.50%Accounts recievable, net (12% of sales) $370 12%Inventory $541 Current Assets $958 Property, net $185 6% Total Assets $1,143
Liabilities:Accounts payable $64 10Accrued expenses $46 1.50%Long-term debt, current portion $ 7 $ 7 Bank note payable (plug) $571 Current Liabilities $688 Long-term debt $43 Total Liabilities $731 Net worth $411 Total Liabilities plus net worth $1,143
Assumptions
1990 Growth adjusted for Sustainability
ROA 5.56%ROE 15.43%ROBE 18.24%
BUTLER LUMBER CO.
Projected income statement for 1991 (thousands of dollars) Assumptions1991 Value
Net sales $ 4,000 Assuming growth over expected level Cost of goods sold: Beginning inventory $418 Purchases $3,040 76%
$3,458 Ending inventory $578 Total cost of goods sold $2,880 72%Gross Profit $1,120 Operating expenses $1,000 25% Operating Profit $120 Purchase Discounts* $48 2% Interest expense** $56 10.50%Net income before income taxes $111 Provision for income taxes $26 34%Net income $85
Projected balance sheet for December 31, 1991 (thousands of dollars)1991
Assets:Cash $60 1.50%Accounts recievable, net (12% of sales) $480 12%Inventory $578 Current Assets $1,118 Property, net $240 6% Total Assets $1,358
Liabilities:Accounts payable $83 10Accrued expenses $60 1.50%Long-term debt, current portion $ 7 $ 7 Bank note payable (plug) $731 Current Liabilities $882 Long-term debt $43 Total Liabilities $925 Net worth $433 Total Liabilities plus net worth $1,358
Assumptions
Assuming growth over expected level
ROA 6.28%ROE 19.67%ROBE 24.49%
BUTLER LUMBER CO.
Projected income statement for 1991 (thousands of dollars) Assumptions1991 Value
Net sales $ 2,694 $ 3,600 Cost of goods sold: Beginning inventory $418 Purchases $2,047 76%
$2,465 Ending inventory $526 Total cost of goods sold $1,940 72%Gross Profit $754 Operating expenses $674 25% Operating Profit $81 Purchase Discounts* $28 2% Interest expense** $44 10.50%Net income before income taxes $64 Provision for income taxes $11 34%Net income $53
Projected balance sheet for December 31, 1991 (thousands of dollars)1991
Assets:Cash $40 1.50%Accounts recievable, net (12% of sales) $323 12%Inventory $526 Current Assets $889 Property, net $162 6% Total Assets $1,051
Liabilities:Accounts payable $56 10Accrued expenses $40 1.50%Long-term debt, current portion $ 7 $ 7 Bank note payable (plug) $503 Current Liabilities $607 Long-term debt $43 Total Liabilities $650 Net worth $401 Total Liabilities plus net worth $1,051
Growth RateAssumptions 0.00%
ROA 5.06%ROE 13.26%ROBE 15.29%