I have finance work needs to be done

profileabaqazi
GroupProjectAuroraFin139.xlsx

Sheet1

Consolidated Income Statement Consolidated Balance Sheets for the Fiscal Years
1999 2000 2001 2002 Ending December, 31 1999-2002
Pounds shipped (000's) 187,673 190,473 151,893 144,116 ($ thousands)
Average selling price / lb 1.3103 1.2064 1.2045 1.0235
Conversion Cost / lb 0.4447 0.4421 0.4465 0.4296 1999 2000 2001 2002
Average Raw Material Cost / lb 0.7077 0.6429 0.6487 0.4509 Assets
Cash and cash equivalents $1,144 $5,508 $2,192 $1,973
Net Sales 245,908 229,787 182,955 147,503 Accounts receivable, net 17,322 11,663 20,390 26,068
Raw Material Cost 132,812 122,455 98,533 64,982 Inventories 34,778 33,155 31,313 33,278
Cost of Conversion 83,454 84,212 67,822 61,912 Other current assets 2,774 1,922 712 2,378
Gross Margin 29,641 23,114 16,597 20,609 Total Current Assets $56,018 $52,248 $54,607 $63,697
SG&A Expenses 14,603 14,218 11,635 10,305
Depreciation & Amortization 15,241 13,005 11,196 9,859 Property and Equipment
Operating Profit -203 -4,109 -6,234 445 Land 2,654 2,594 2,516 2,505
Interest Expense 6,777 6,773 5,130 3,440 Buildings 32,729 31,859 30,308 30,427
Other Income (Expense) 1,143 -1,232 -409 Machinery and equipment 230,759 220,615 197,889 190,410
Asset Impairments* 4,758 7,564 Gross PP&E 266,142 255,068 230,713 223,342
Earnings Before Income Tax Provision -6,980 -9,739 -17,354 -10,968 Less accumulated depreciation -147,891 -147,104 -146,302 -154,658
Income Tax Provision (Benefit) @ 36% tax rate -2,513 -3,506 -6,247 -3,949 Net PP&E 118,251 107,964 84,411 68,684
Net Earnings -$4,467 -$6,233 -$11,107 -$7,019 Goodwill 1,180 1,180 1,180 1,180
Other non-current assets 3,516 3,499 2,824 2,430
Total Assets $178,965 $164,890 $143,023 $135,991
Aurora Textile Company
Financial Ratios Analysis (1999–2002) Liabilities
Accounts payable 12,236 7,693 9,667 10,835
Accrued compensation and benefits 4,148 3,712 4,176 4,730
1999 2000 2001 2002 Accrued interest 1,830 1,090 961 929
Sales growth -6.56% -20.38% -19.38% Other accrued expenses 4,083 3,914 3,881 3,657
Raw materials/sales 54.01% 53.29% 53.86% 44.05% Current portion of long-term debt 1,009 1,730 0 0
Conversion cost/sales 33.94% 36.65% 37.07% 41.97% Total Current Liabilities $23,306 $18,139 $18,685 $20,151
Gross Margin/sales 12.05% 10.06% 9.07% 13.97% Long-term debt 66,991 66,991 58,000 58,000
SGA/sales 5.94% 6.19% 6.36% 6.99% Other long-term liabilities 16,566 14,081 11,776 10,297
Interest Expense 2.76% 2.95% 2.80% 2.33% Total Liabilities $106,863 $99,211 $88,461 $88,448
Operating Profit/sales -0.08% -1.79% -3.41% 0.30%
NI/sales -1.82% -2.71% -6.07% -4.76% Shareholder's equity
Common stock, par $0.01 50 50 50 50
Days sales outstanding 25.7 18.5 40.7 64.5 Capital surplus 15,868 15,678 15,668 15,668
Days inventory 95.6 98.8 116.0 186.9 Retained earnings 56,184 49,951 38,845 31,825
Asset turnover 1.37 1.39 1.28 1.08 Total Shareholders' Equity $72,102 $65,679 $54,563 $47,543
Total Liabilities and Shareholders' Equity $178,965 $164,890 $143,024 $135,991
Return on assets -2.50% -3.78% -7.77% -5.16%
Return on equity -6.20% -9.49% -20.36% -14.76%

Sheet3

Aurora Textile Company
Incremental Cash Flows and NPV Sensitivity of Zinser Machine Investment ($000)
Year 0 1 2 3 4 5 6 7 8 9 10
Existing spinning machine -$964 $1,367 $1,403 $1,440 $1,478 $1,337 $1,378 $1,419 $1,462 $1,506 $2,824
New Zinser -$7,852 $3,348 $3,440 $3,535 $3,633 $3,733 $3,837 $3,944 $4,054 $4,168 $5,170
Incremental cash flows -$6,888 $1,981 $2,037 $2,095 $2,155 $2,396 $2,459 $2,525 $2,592 $2,662 $2,346
Net present Value = -$6,888 $1,801 $1,683 $1,574 $1,472 $1,488 $1,388 $1,296 $1,209 $1,129 $904
-$5,087 -$3,404 -$1,830 -$358 $1,130 $2,518 $3,814 $5,023 $6,152 $7,056
Hurdle Rate/WACC 10%
NPV of the Machine
Project Life➔ 0 1 2 3 4 5 6 7 8 9 10
NPV (salvage effect ignored) -$5,088 -$3,405 -$1,831 -$359 $1,128 $2,517 $3,812 $5,021 $6,150 $7,054
NPV (zero salvage, 36% tax benefit on reported loss) -$2,658 -$1,441 -$269 $858 $2,050 $3,187 $4,269 $5,299 $6,276 $7,054
NPV (salvage = 25% book value, 36% tax benefit on reported loss) -$1,578 -$569 $425 $1,399 $2,460 $3,485 $4,473 $5,422 $6,332 $7,054