financial case question

profilerealCJY
CopyofAlliance1.xlsx
Home>Business & Finance homework help>financial case question

Title

This spreadsheet supports STUDENT analysis of the case “Alliance Concrete” UVA-F-1527).
This spreadsheet was prepared by Associate Professor Marc Lipson. Copyright © 2007 by the University of Virginia Darden School Foundation, Charlottesville, VA. All rights reserved. For customer service inquiries, send an e-mail [email protected]. No part of this publication may be reproduced, stored in a retrieval system, posted to the Internet, or transmitted in any form or by any means—electronic, mechanical, photocopying, recording, or otherwise—without the permission of the Darden School Foundation.
Rev. Nov. 3, 2010.

Exhibit 1

Exhibit 1
ALLIANCE CONCRETE
Income Statements
2002 2003 2004 2005
Income Statement (in thousands of dollars)
Revenue 128,978 143,560 161,863 185,815
Cost of goods sold 100,857 111,203 125,756 144,594
Gross margin 28,121 32,357 36,107 41,221
General and administrative 12,482 13,685 18,131 17,327
Earnings before interest and taxes 15,639 18,672 17,976 23,894
Interest 4,537 6,150 5,964 5,695
11,102 12,522 12,012 18,199
Tax 3,882 4,288 4,312 6,210
Net income 7,220 8,234 7,700 11,989
Additional Data
Yards sold (in thousands) 1,751 1,850 1,957 2,085
Average price per yard (in dollars) 73.66 77.60 82.71 89.12
Average cost per yard (in dollars) 57.60 60.11 64.26 69.35
Depreciation (in thosands of dollars) 5,436 5,762 5,983 6,439
Source: Created by case writer.

Exhibit 2

Exhibit 2
ALLIANCE CONCRETE
Balance Sheets
2002 2003 2004 2005
Balance Sheet ($1,000)
Cash 2,837 3,330 3,043 4,180
Accounts receivable 18,092 19,823 23,104 28,203
Inventory 3,549 4,238 4,233 4,615
Current assets 24,478 27,391 30,380 36,998
Plant and equipment 87,534 91,392 93,569 97,476
112,012 118,783 123,949 134,474
Accounts payable 8,891 9,609 11,067 13,534
Other accrued expenses 5,313 5,713 6,490 7,897
Current liabilities 14,204 15,322 17,557 21,431
Long-term debt 55,000 75,000 71,000 67,000
Owners' equity 42,808 28,461 35,392 46,043
112,012 118,783 123,949 134,474
Financial Statement Relations
Margins and Returns
Gross margin 21.80% 22.54% 22.31% 22.18%
Net margin 5.60% 5.74% 4.76% 6.45%
Return on book assets 6.45% 6.93% 6.21% 8.92%
Return on book equity 16.87% 28.93% 21.76% 26.04%
Asset Ratios
Days receivables 51.20 50.40 52.10 55.40
Days inventory 12.84 13.91 12.29 11.65
Days payables 32.18 31.54 32.12 34.16
Total asset turnover 1.15 1.21 1.31 1.38
Fixed asset turnover 1.47 1.57 1.73 1.91
Leverage Ratios
Debt to prior year EBITDA 2.98 3.56 2.91 2.80
Debt to total value (book) 49.10% 63.14% 57.28% 49.82%
Interest coverage 3.45 3.04 3.01 4.20
Source: Created by case writer.

Class Worksheet

2002 2003 2004 2005 Forecast Assumptions
Yards sold (in thousands) 1,751 1,850 1,957 2,085 2,200 Given in Case
Average price per yard (in dollars) 73.66 77.60 82.71 89.12 95.36 7% Increased per case
Average cost per yard (in dollars) 57.60 60.11 64.26 69.35 75.59 9% Increased per case
Income Statement ($1,000)
Revenue 128,978 143,560 161,863 185,815 209,788 Calculated Avg. Price* Yard Sold
Cost of goods sold 100,857 111,203 125,756 144,594 166,300 Calculated Avg. Cost* Yard Sold
Gross margin 28,121 32,357 36,107 41,221 43,488 Calculated
General and administrative 12,482 13,685 18,131 17,327 19,954
0.097 0.095 0.112 0.093 0.095 Average excluding 2004 outlier % of Revenue
Earnings before interest and taxes 15,639 18,672 17,976 23,894 23,533 Calculated
Interest 4,537 6,150 5,964 5,695 5,695 same as last year
Taxable Income 11,102 12,522 12,012 18,199 17,838 Calculated
Tax 3,882 4,288 4,312 6,210 6,209 Tax Rate
0.350 0.342 0.359 0.341 0.348 Average of 4 years Tax rate
Net income 7,220 8,234 7,700 11,989 11,629 Calculated
Dividend 3,000
Addition to Retained Earnings 8,629
Capital Expenditure 16,000
Depreciation 7,500
Balance Sheet ($1,000)
Cash 2,837 3,330 3,043 4,180 4,733
0.022 0.023 0.019 0.022 0.023 Average excluding 2004 outlier % of Revenue
Accounts receivable 18,092 19,823 23,104 28,203 30,045
0.140 0.138 0.143 0.152 0.143 Average of 4 years % of Revenue
Inventory 3,549 4,238 4,233 4,615 5,774
0.035 0.038 0.034 0.032 0.035 Average of 4 years % of COGS
Current assets 24,478 27,391 30,380 36,998 40,553
Plant and equipment 87,534 91,392 93,569 97,476 105,976 PP&E(prior)+CapEx-Depreciation
Total Assets 112,012 118,783 123,949 134,474 146,529 Calculated
Accounts payable 8,891 9,609 11,067 13,534 14,896
2.51 2.27 2.61 2.93 2.58 Average of 4 years % of Inventory
Other accrued expenses 5,313 5,713 6,490 7,897 8,579
0.041 0.040 0.040 0.042 0.041 Average of 4 years % of Revenue
Current liabilities 14,204 15,322 17,557 21,431 23,475 Calculated
Long-term debt 55,000 75,000 71,000 67,000 68,381 Calculated (Goal Seek)
Owners' equity 42,808 28,461 35,392 46,043 54,672 Last year Equity+ Additonal Retained Earning
112,012 118,783 123,949 134,474 146,529 Calculated
0.00
Financial Statement Relations
Margins and Returns
Gross margin 21.80% 22.54% 22.31% 22.18% 20.73%
Net margin 5.60% 5.74% 4.76% 6.45% 5.54%
Return on book assets 6.45% 6.93% 6.21% 8.92% 7.94%
Return on book equity 16.87% 28.93% 21.76% 26.04% 21.27%
Asset Ratios
Days receivables 51.20 50.40 52.10 55.40 52.27
Days inventory 12.84 13.91 12.29 11.65 12.67
Days payables 32.18 31.54 32.12 34.16 32.69
Total asset turnover 1.15 1.21 1.31 1.38 1.43
Fixed asset turnover 1.47 1.57 1.73 1.91 1.98
Leverage Ratios
Debt to prior year EBITDA 2.98 3.56 2.91 2.80 2.25
Debt to total value (book) 49% 63% 57% 50% 47%
Interest coverage 3.45 3.04 3.01 4.20 4.13
Source: Created by case writer.