This This spre spread adsh shee eett was was prepa prepared red Char Charlo lotte ttesv svill ille, e, VA. VA. All All right rightss publication may be reproduce mechanical, photocopying, rec Rev. Nov. 3, 2010.
This spreadsheet supports STUDENT analysis of the case “Alliance Concrete” UVA-F-1527).
by Associate Professor Marc Lipson. Copyright © 2007 by the University of Virginia Darden School Foundation, reserved. For customer service inquiries, send an e-mail to
[email protected]. No part of this d, stored in a retrieval system, posted to the Internet, or transmitted in any form or by any means—electronic, rding, or otherwise—without the permission of the Darden School Foundation.
Exhibit 1 ALLIANCE CONCRETE Income Statements
2002 Income Statement (in thousands of dollars) Revenue Cost of goods sold Gross margin General and administrative Earnings before interest and taxes Interest
Tax Net income Additional Data Yards sold (in thousands) Average price per yard (in dollars) Average cost per yard (in dollars) Depreciation (in thosands of dollars) Source: Created by case writer.
2003
2004
128,978 143,560 100,857 111,203 28,121 32,357 12,482 13,685 15,639 18,672 4,537 6,150 11,102 12,522 3,882 4,288 7,220 8,234
161,863 125,756 36,107 18,131 17,976 5,964 12,012 4,312 7,700
1,751 73.66 57.60 5,436
1,850 77.60 60.11 5,762
1,957 82.71 64.26 5,983
2005 185,815 144,594 41,221 17,327 23,894 5,695 18,199 6,210 11,989
2,085 89.12 69.35 6,439
Exhibit 2 ALLIANCE CONCRETE Balance Sheets
Balance Sheet ($1,000) Cash Accounts receivable Inventory Current assets Plant and equipment
Accounts payable Other accrued expenses Current liabilities Long-term debt Owners' equity
Financial Statement Relations Margins and Returns Gross margin Net margin Return on book assets Return on book equity Asset Ratios Days receivables Days inventory Days payables Total asset turnover Fixed asset turnover Leverage Ratios Debt to prior year EBITDA Debt to total value (book) Interest coverage
2002
2003
2004
2005
2,837 18,092 3,549 24,478 87,534 112,012
3,330 19,823 4,238 27,391 91,392 118,783
3,043 23,104 4,233 30,380 93,569 123,949
4,180 28,203 4,615 36,998 97,476 134,474
8,891 5,313 14,204 55,000 42,808 112,012
9,609 5,713 15,322 75,000 28,461 118,783
11,067 6,490 17,557 71,000 35,392 123,949
13,534 7,897 21,431 67,000 46,043 134,474
21.80% 5.60% 6.45% 16.87%
22.54% 5.74% 6.93% 28.93%
22.31% 4.76% 6.21% 21.76%
22.18% 6.45% 8.92% 26.04%
51.20 12.84 32.18 1.15 1.47
50.40 13.91 31.54 1.21 1.57
52.10 12.29 32.12 1.31 1.73
55.40 11.65 34.16 1.38 1.91
2.98 49.10% 3.45
3.56 63.14% 3.04
2.91 57.28% 3.01
2.80 49.82% 4.20
Source: Created by case writer.
Yards sold (in thousands) Average price per yard (in dollars) Average cost per yard (in dollars) Capital Expenditure Depreciation Income Statement ($1,000) Revenue Cost of goods sold Gross margin General and administrative Earnings before interest and taxes Interest Taxable Income Tax Net income
2005
Forecast
2,085 89.12 69.35
2,200 94.02 76.28 16,000 7,500
185,815 144,594 41,221 17,327 9.32% 23,894 5,695 18,199 6,210 34.81% 11,989
Dividends Addition to Retained Earnings
206,847 167,826 39,021 19,288 19,733 6,179 13,554 4,718 8,836 3,000 5,836
Balance Sheet ($1,000) Cash Accounts receivable Inventory Current assets Plant and equipment Total Assets
4,180 2.25% 28,203 15.18% 4,615 2.48% 36,998 97,476 134,474
4,653 31,395 5,137 41,186 97,476 138,662
Accounts payable Other accrued expenses Current liabilities Long-term debt Owners' equity Total Liabilites & Owner's Equity
13,534 7,897 21,431 67,000 46,043 134,474
15,066 8,791 23,857 63,000 51,879 138,736
7.28% 4.25%
Financial Statement Relations Margins and Returns Gross margin Net margin Return on book assets Return on book equity Asset Ratios Days receivables Days inventory Days payables Total asset turnover Fixed asset turnover Leverage Ratios Debt to prior year EBITDA Debt to total value (book) Interest coverage Source: Created by case writer.
22.18% 6.45% 8.92% 26.04%
18.86% 4.27% 6.37% 17.03%
55.40 11.65 34.16 1.38 1.91
55.40 11.17 32.77 1.49 2.12
2.80 50% 4.20
2.31 45% 3.19
Assumptions It is given that expected sales are to increase by 2.2 million cubic yards. Used the average of worst case scenario price increase(4%) and the best case scenario price increase(7%) Used the 10% increase in cost to give company more room incase of unexpected cost increase. Planned expenditure given in the case. The depreciation expected is also given in the case.
Forcasted revenue is calculated by the yards expected to sell by the expected price. Multiply the expected revenue to the percentage of 2005's COGS/Revenue. Difference from Revenue and COGS. Multiply the expected revenue to the percentage of 2005's G&A/Revenue. Difference from Gross Margin - G&A. EBIT multiply by 8.5%, interest is given in the case. Difference from EBIT - Interest. To be conservative, I used the average percentage from the previous years instead of only last year's beca Difference from Taxable Income and Tax. The new owners (National) want to increase the dividend payout for 2006 to 3 million dollars. Difference from Net Income and Dividend Payout
Expected cash will be 2.25% of expected revenue. The 2.25% comes from 2005's Cash/Revenue. Expected Accounts Receivable will be 15.18% of expected revenue. The 15.18% comes from 2005's AR The forecasted Inventory will be 2.48% of sales. 2.48% is from 2005's Inventory/Revenue. The forecasted Plant and Equipment will stay the same.
Accounts Payable/Revenue = 7.28%, forecasted AP will be 7.28% of forecasted Revenue. Other accrued expenses/Revenue = 4.25%, forecasted Other Expenses wil be 4.25% of Revenue. Total of current liabilities. Given information that previous owners will pay 4 million dollarsa year for each of the next 5 years fro 2005's Owner's Rquity + Addition to Retained Earnings for Projected year. Total of Liabilities and Owner's Equity.
, which is 5.5%.
se last year's was the lowest.
Revenue.
2003.
Yards sold (in thousands) Average price per yard (in dollars) Average cost per yard (in dollars) Capital Expenditure Depreciation Income Statement ($1,000) Revenue Cost of goods sold Gross margin General and administrative Earnings before interest and taxes Interest Taxable Income Tax Net income
2005
No Dividends
2,085 89.12 69.35
2,200 94.02 76.28 16,000 7,500
185,815 144,594 41,221 17,327 9.32% 23,894 5,695 18,199 6,210 34.81% 11,989
Dividends Addition to Retained Earnings
206,847 167,826 39,021 19,288 19,733 6,179 13,554 4,718 8,836 0 8,836
Balance Sheet ($1,000) Cash Accounts receivable Inventory Current assets Plant and equipment Total Assets
4,180 2.25% 28,203 15.18% 4,615 2.48% 36,998 97,476 134,474
4,653 31,395 5,137 41,186 105,976 147,162
Accounts payable Other accrued expenses Current liabilities Long-term debt Owners' equity Total Liabilites & Owner's Equity
13,534 7,897 21,431 67,000 46,043 134,474
15,066 8,791 23,857 63,000 54,879 141,736
7.28% 4.25%
Financial Statement Relations Margins and Returns Gross margin Net margin Return on book assets Return on book equity Asset Ratios Days receivables Days inventory Days payables Total asset turnover Fixed asset turnover Leverage Ratios Debt to prior year EBITDA Debt to total value (book) Interest coverage Source: Created by case writer.
22.18% 6.45% 8.92% 26.04%
18.86% 4.27% 6.00% 16.10%
55.40 11.65 34.16 1.38 1.91
55.40 11.17 32.77 1.41 1.95
2.80 50% 4.20
2.31 44% 3.19
Assumptions It is given that expected sales are to increase by 2.2 million cubic yards. Used the average of worst case scenario price increase(4%) and the best case scenario price increase(7%) Used the 10% increase in cost to give company more room incase of unexpected cost increase. Planned expenditure given in the case. The depreciation expected is also given in the case.
Forcasted revenue is calculated by the yards expected to sell by the expected price. Multiply the expected revenue to the percentage of 2005's COGS/Revenue. Difference from Revenue and COGS. Multiply the expected revenue to the percentage of 2005's G&A/Revenue. Difference from Gross Margin - G&A. EBIT multiply by 8.5%, interest is given in the case. Difference from EBIT - Interest. To be conservative, I used the average percentage from the previous years instead of only last year's beca Difference from Taxable Income and Tax. Executed the option to not payout dividends Difference from Net Income and Dividend Payout
Expected cash will be 2.25% of expected revenue. The 2.25% comes from 2005's Cash/Revenue. Expected Accounts Receivable will be 15.18% of expected revenue. The 15.18% comes from 2005's AR The forecasted Inventory will be 2.48% of sales. 2.48% is from 2005's Inventory/Revenue. The forecasted Plant and Equipment includes the capital expenditure.
Accounts Payable/Revenue = 7.28%, forecasted AP will be 7.28% of forecasted Revenue. Other accrued expenses/Revenue = 4.25%, forecasted Other Expenses wil be 4.25% of Revenue. Total of current liabilities. Given information that previous owners will pay 4 million dollarsa year for each of the next 5 years fro 2005's Owner's Rquity + Addition to Retained Earnings for Projected year. Total of Liabilities and Owner's Equity.
, which is 5.5%.
se last year's was the lowest.
Revenue.
2003.
