8/13/2019 UVA-S-F-1211 (Rosario)(2)

1/8

Model Facts

This spreadsheet supports STUDENTanalysis of the case, "Rosario

Acero S.A." (UVA-F-1211)

Please note:

This is a working model. Assumptions / Inputspresented can be

changed to vary the results. Please press F9 to recalculate results

after changing assumptions.

Revised March 7, 2006.

Copyright 1998, by the University of Virginia Darden School Foundation.

Page 1

8/13/2019 UVA-S-F-1211 (Rosario)(2)

2/8

Exh. 6

Exhibit 6

Rosario Acero S.A.

Forecast of Income Statement: Growth Financed with

The Privately-Placed Debt-and-Warrants Issue

Common Assumptions

Revenue Growth Rate = 10.30% Inventory/Revenues = 13.00%COGS/Revenues = 78.00% Other Curr. Assets/Revenues= 1.00%

SG&A/Revenues = 13.00% Gross Fixed Assets/Revenues= 48.00%

Tax Rate = 34.00% Accts. Payable/Revenues= 14.00%

Depreciation/Gross Fixed Assets = 5.60% Other. Curr. Liabs./Revenues= 7.00%

Cash/Revenues = 1.00% Interest Rate = 10.00%

Accts. Receivable/Revenues = 20.00% Change in Def'd Tax/Taxes = 25.00%

Base Lending Rate = 8.50% Primary Shares = 233,000

Fully-Diluted Shares = 273,000

Actual Projected

1996 1997 1998 1999 2000 2001 2002

Income Statement

Revenues $34.80 $38.38 $42.34 $46.70 $51.51 $56.81 $62.67

Cost of Goods Sold (27.65) (29.94) (33.02) (36.43) (40.18) (44.32) (48.88)

Selling, Gen'l, & Admin. (3.96) (4.99) (5.50) (6.07) (6.70) (7.39) (8.15)

Earnings Before Interest and Taxes 3.19 3.45 3.81 4.20 4.64 5.11 5.64

Interest (Notes and Old Loans) (1) (1.10) (0.73) (0.70) (0.65) (0.59) (0.51) (0.41)

Interest (New Loan @ 13%) (0.98) (0.98) (0.98) (0.98) (0.98) (0.98)

Profit Before Taxes 2.09 1.75 2.13 2.57 3.07 3.63 4.26

Taxes 0.00 (0.59) (0.72) (0.88) (1.04) (1.23) (1.45)

Profit After Taxes $2.09 $1.15 $1.41 $1.70 $2.03 $2.40 $2.81

Profit With Extraord. Item $1.76

Earnings per Share $7.57 $4.96 $6.04 $7.29 $8.70 $10.28 $12.06

Note 1: The firm is assumed to borrow at base rate plus two percent, and lend

at base rate less two percent.

Page 2

8/13/2019 UVA-S-F-1211 (Rosario)(2)

3/8

Exhibit 7

Rosario Acero S.A.

Forecast of Balance Sheets: Growth Financed with

The Privately-Placed Debt-and-Warrants Issue

Actual Projected

1996 1997 1998 1999 2000 2001 2002Balance Sheet

Cash $0.20 $0.38 $0.42 $0.47 $0.52 $0.57 $0.63

Accounts Receivable 6.80 7.68 8.47 9.34 10.30 11.36 12.53

Inventory 4.70 4.99 5.50 6.07 6.70 7.39 8.15

Other Current Assets 0.40 0.38 0.42 0.47 0.52 0.57 0.63

Total Current Assets 12.10 13.43 14.82 16.34 18.03 19.88 21.93

Gross Fixed Assets 16.70 18.42 20.32 22.42 24.72 27.27 30.08

Accumulated Depreciation (2.50) (3.53) (4.67) (5.93) (7.31) (8.84) (10.52)

Net Fixed Assets 14.20 14.89 15.65 16.49 17.41 18.43 19.56

Other Assets 0.10 0.10 0.10 0.10 0.10 0.10 0.10

Total Assets $26.40 $28.43 $30.57 $32.93 $35.54 $38.42 $41.59

Notes Payable (or Excess Cash) $4.80 ($0.76) ($0.84) ($0.81) ($1.00) ($1.44) ($2.17)

Accounts Payable 4.60 5.37 5.93 6.54 7.21 7.95 8.77

Other Current Liabilities 2.30 2.69 2.96 3.27 3.61 3.98 4.39

Total Current Liabilities 11.70 7.30 8.05 9.00 9.82 10.49 10.99

Old Long Term Debt 9.80 7.43 7.23 6.73 6.23 5.73 5.23

New Long Term Debt 7.50 7.50 7.50 7.50 7.50 7.50

Deferred Taxes 1.30 1.45 1.63 1.85 2.11 2.42 2.78

Total Liabilities 22.80 23.67 24.41 25.07 25.66 26.14 26.50

Common Stock 0.20 0.20 0.20 0.20 0.20 0.20 0.20

Paid-in Surplus 0.20 0.20 0.20 0.20 0.20 0.20 0.20

Retained Earnings 3.20 4.35 5.76 7.46 9.49 11.88 14.69

Total Liabilities and Equity $26.40 $28.43 $30.57 $32.93 $35.54 $38.42 $41.59

Comparative Ratios

EBIT/Interest 2.90 2.03 2.27 2.58 2.96 3.45 4.08

EBIT/(Interest + Amort.) 1.93 0.36 1.95 2.00 2.06 2.11 2.16

Liabilities/Equity 6.33 4.98 3.96 3.19 2.59 2.13 1.76

(Debt+Notes)/Equity 4.06 2.98 2.25 1.71 1.29 0.96 0.70

Profit/Revenues 5.1% 3.0% 3.3% 3.6% 3.9% 4.2% 4.5%

Profit/Equity 49.0% 24.3% 22.8% 21.6% 20.5% 19.5% 18.6%

8/13/2019 UVA-S-F-1211 (Rosario)(2)

4/8

Exhibit 8

Rosario Acero S.A.

Forecast of Free Cash Flows: Growth Financed with

The Privately-Placed Debt-and-Warrants Issue

Projected

1997 1998 1999 2000 2001 2002

Earnings Before Interest and Taxes 3.45 3.81 4.20 4.64 5.11 5.64

Taxes (1.17) (1.30) (1.43) (1.58) (1.74) (1.92)

Earnings Before Interest and After Taxes 2.28 2.51 2.77 3.06 3.37 3.72

Plus Depreciation 1.03 1.14 1.26 1.38 1.53 1.68

Less Capital Expenditures (1.72) (1.90) (2.09) (2.31) (2.55) (2.81)

Less Additions to Net Working Capital (0.17) (0.55) (0.61) (0.67) (0.74) (0.82)

Free Cash Flow 1.41 1.20 1.33 1.46 1.61 1.78

8/13/2019 UVA-S-F-1211 (Rosario)(2)

5/8

Exh.9

Exhibit 9

Rosario Acero S.A.

Forecast of Income Statement:

Growth Financed with Equity

Shares Are Sold at $9.00 Each

Common Assumptions

Revenue Growth Rate = 10.30% Inventory/Revenues = 13.00%

COGS/Revenues = 78.00% Other Curr. Assets/Revenues= 1.00%SG&A/Revenues = 13.00% Gross Fixed Assets/Revenues= 48.00%

Tax Rate = 34.00% Accts. Payable/Revenues= 14.00%

Depreciation/Gross Fixed Assets = 5.60% Other. Curr. Liabs./Revenues= 7.00%

Cash/Revenues = 1.00% Interest Rate = 10.00%

Accts. Receivable/Revenues = 20.00% Change in Def'd Tax/Taxes = 25.00%

Base Lending Rate = 8.50% 1996 Primary Shares = 233,000

1997+ Primary Shares = 1,066,333

Actual Projected

1996 1997 1998 1999 2000 2001 2002

Income Statement

Revenues $34.80 $38.38 $42.34 $46.70 $51.51 $56.81 $62.67

Cost of Goods Sold (27.65) (29.94) (33.02) (36.43) (40.18) (44.32) (48.88)

Selling, Gen'l, & Admin. (3.96) (4.99) (5.50) (6.07) (6.70) (7.39) (8.15)Earnings Before Interest and Taxes 3.19 3.45 3.81 4.20 4.64 5.11 5.64

Interest (on Notes and Old Loans) (1) (1.10) (0.68) (0.60) (0.50) (0.37) (0.23) (0.07)

Interest (New Loan @ 13%) 0.00 0.00 0.00 0.00 0.00 0.00

Profit Before Taxes 2.09 2.77 3.21 3.71 4.26 4.88 5.57

Taxes 0.00 (0.94) (1.09) (1.26) (1.45) (1.66) (1.89)

Profit After Taxes $2.09 $1.83 $2.12 $2.45 $2.81 $3.22 $3.68

Profit With Extraord. Item $1.76

Earnings per Share $7.57 $1.72 $1.99 $2.29 $2.64 $3.02 $3.45

Note 1: The firm is assumed to borrow at base rate plus two percent, and lend

at base rate less two percent.

Page 5

8/13/2019 UVA-S-F-1211 (Rosario)(2)

6/8

Exhibit 10

Rosario Acero S.A.

Forecast of Free Cash Flows:

Growth Financed with Equity

Shares Are Sold at $9.00 Each

Actual Projected1996 1997 1998 1999 2000 2001 2002

Balance Sheet

Cash $0.20 $0.38 $0.42 $0.47 $0.52 $0.57 $0.63

Accounts Receivable 6.80 7.68 8.47 9.34 10.30 11.36 12.53

Inventory 4.70 4.99 5.50 6.07 6.70 7.39 8.15

Other Current Assets 0.40 0.38 0.42 0.47 0.52 0.57 0.63

Total Current Assets 12.10 13.43 14.82 16.34 18.03 19.88 21.93

Gross Fixed Assets 16.70 18.42 20.32 22.42 24.72 27.27 30.08

Accumulated Depreciation (2.50) (3.53) (4.67) (5.93) (7.31) (8.84) (10.52)

Net Fixed Assets 14.20 14.89 15.65 16.49 17.41 18.43 19.56

Other Assets 0.10 0.10 0.10 0.10 0.10 0.10 0.10Total Assets 26.40 $28.43 $30.57 $32.93 $35.54 $38.42 $41.59

Notes Payable (or Excess Cash) 4.80 ($1.53) ($2.40) ($3.21) ($4.29) ($5.66) ($7.37)

Accounts Payable 4.60 5.37 5.93 6.54 7.21 7.95 8.77

Other Current Liabilities 2.30 2.69 2.96 3.27 3.61 3.98 4.39

Total Current Liabilities 11.70 6.54 6.49 6.59 6.53 6.27 5.79

Old Long Term Debt (Note 1) 9.80 7.43 7.23 6.73 6.23 5.73 5.23

New Long Term Debt 0.00 0.00 0.00 0.00 0.00 0.00

Deferred Taxes 1.30 1.54 1.81 2.12 2.49 2.90 3.37

Total Liabilities 22.80 15.50 15.52 15.44 15.24 14.89 14.39

Common Stock 0.20 1.20 1.20 1.20 1.20 1.20 1.20

Paid-in Surplus 0.20 6.70 6.70 6.70 6.70 6.70 6.70

Retained Earnings 3.20 5.03 7.15 9.59 12.41 15.63 19.30

Total Liabilities and Equity $26.40 $28.43 $30.57 $32.93 $35.54 $38.42 $41.59

Comparative Ratios

EBIT/Interest 2.90 5.08 6.32 8.45 12.37 21.95 81.14

EBIT/(Interest + Amort.) 1.93 0.37 2.27 2.32 2.38 2.43 2.48

Liabilities/Equity 6.33 1.20 1.03 0.88 0.75 0.63 0.53

(Debt+Notes)/Equity 4.06 0.46 0.32 0.20 0.10 0.00 -0.08

Profit/Revenues 5.1% 4.8% 5.0% 5.2% 5.5% 5.7% 5.9%

Profit/Equity 49.0% 14.2% 14.1% 14.0% 13.8% 13.7% 13.5%

8/13/2019 UVA-S-F-1211 (Rosario)(2)

7/8

Exhibit 11

Rosario Acero S.A.

Forecast of Free Cash Flows:

Growth Financed with Equity

Shares Are Sold at $9.00 Each

Projected

1997 1998 1999 2000 2001 2002

Earnings Before Interest and Taxes 3.45 3.81 4.20 4.64 5.11 5.64

Taxes (1.17) (1.30) (1.43) (1.58) (1.74) (1.92)Earnings Before Interest and After Taxes 2.28 2.51 2.77 3.06 3.37 3.72

Plus Depreciation 1.03 1.14 1.26 1.38 1.53 1.68

Less Capital Expenditures (1.72) (1.90) (2.09) (2.31) (2.55) (2.81)

Less Additions to Net Working Capital (0.17) (0.55) (0.61) (0.67) (0.74) (0.82)

Free Cash Flow 1.41 1.20 1.33 1.46 1.61 1.78

8/13/2019 UVA-S-F-1211 (Rosario)(2)

8/8

Exhibit 12

Rosario Acero

Selected 1995 Data on Publicly-Listed Peer Firms

AD CSA GA VAZ PI

Sales 381.0$ 362.8$ 397.4$ 755.2$ 117.1$Operating Margin 55.6 11.7 26 144.9 -1.1

Net Income 16.9 7.5 10.3 46.4 -5.1

Operating Margin/Sales 14.6% 3.2% 6.5% 19.2% -0.9%

Net Income/Sales 4.4% 2.1% 2.6% 6.1% -4.4%

LT Debt/Capital (Book) 40% 56% 22% 33% 42%

Total Debt/Capital (Book) 57% 66% 40% 37% 65%

Total Debt/Equity (Book) 1.33 1.94 0.67 0.59 1.86

Market Equity/Book Equity 1.85 1.25 1.50 2.10 0.90

Total Debt/Equity (Market) 0.72 1.55 0.44 0.28 2.06

Dividend Payout Ratio 27% 0% 15% 12% 0%

Dividend Yield 3% 0% 3% 1% 0%

P/E Ratio 9.5 15.9 11.3 15.0 7.3

Beta 1.35 1.05 1.00 1.15 2.50