Garden State Container Corporation ( Financial Analysis Forecasting )

CASE36M

Case 36 Student Model 42157.0
Group #
Names: 1
2
3
GARDEN STATE CONTAINTER CORPORATION
Financial Analysis and Forecasting
This case demonstrates the use of ratio analysis in evaluating the
current and future financial position of a firm. This case may also be
used to illustrate forecasting and the impact that various assumptions
and forecast errors have on the projected performance of a firm.
The model includes balance sheets, income statements, cash flow
statements, and ratio analysis. Historical and pro forma worksheets
have been combined to facilitate comparisons. Also, note that all
dollar figures in the model are in thousands. Clicking on the
ALTMAN/SALES tab will show a graph of the effect of sales variability
on Altman’s Z Score, while clicking on the CURRENT/SALES tab will show
the effect on the current ratio. Clicking on the ALTMAN/COGS tab shows
the effect of cost variability on Altman’s Z Score.
If you are using the student version of the model, the following cells
have been blanked out: F54, F55:G57, F59, F65, F67:G67, F71:G71, F80:G80,
G62:G63, F85:G85, F87:G87, F90:G90, F99, F101:F103, F107:G107, F109:F110,
F117:G118, F120:G120, F122:G122, F125:G125, F105, E135:E137, E151:E153,
E216:E219, E252:E270, E234:E238, D252:D274, F75:G75, F82:F83, F112:F113,
F115, G98:G99, G103, G109, G111, E180:E184, F127:G127, B260:F264,
B256:F256, and B270:F270.
= = = = = = =
INPUT DATA: KEY OUTPUT:
1993 1994 1992 Net cash flow $327,293
1992 Current ratio 0.00
Sales growth 10.00% 15.00% 1992 Quick ratio 0.00
As a % of sales: 1992 Debt ratio 0.00%
CGS 82.50% 80.00%
Admin expenses 8.00% 7.50%
Misc expenses 1.75% 1.25% 1993 Current ratio 0.00
ST int rate 12.00% 12.00% 1993 Quick ratio 0.00
Federal-plus- 1993 Debt ratio 0.00%
state tax rate 40.00% 40.00%
Dividend payout 0.00% 0.00%
Target DSO 32.00 32.00 1994 Current ratio 0.00
Target inventory 1994 Quick ratio 0.00
turnover (CGS) 5.70 5.70 1994 Debt ratio 0.00%
P/E ratio 12 14
New ST loan $12,750 $0 Altman’s Z score, 1992 0.00
ST loans repaid $0 $0 Altman’s Z score, 1993 0.00
% sales made 100.00% 100.00% Altman’s Z score, 1994 0.00
= = = = = = =
MODEL-GENERATED DATA:
Table 1: Historical and Pro Forma Balance Sheets:
Pro Forma
– –
1990 1991 1992 1993 1994
ASSETS
Cash and marketable
securities $9,930 $7,363 $6,550
Accounts receivable 34,196 36,924 58,714
Inventory 39,791 69,361 97,984
——- ——- ——- ——- ——–
Current assets $83,888 $113,647 $163,249 $0
Land, buildings, plant,
and equipment $34,634 $39,195 $44,604 $57,036
Accumulated depreciation (5,992) (9,308) (13,388) (18,234)
——- ——- ——- ——- ——–
Net fixed assets $28,642 $29,887 $31,216 $0
——- ——- ——- ——- ——–
Total assets $112,530 $143,534 $194,465
======= ======= ======= ======= ========
LIABILITIES AND EQUITIES 1990 1991 1992 1993 1994
Short-term bank loans $6,376 $10,200 $36,466
Accounts payable 13,528 21,012 39,996 31,990 33,590
Accruals 6,886 10,200 14,662 18,602 23,252
——- ——- ——- ——- ——–
Current liabilities $26,790 $41,412 $91,124
Long-term bank loans $13,388 $20,082 $20,082 $20,082 $20,082
Mortgage 5,738 5,202 4,680 4,208 3,788
——- ——- ——– ——- ——–
Long-term debt $19,126 $25,284 $24,762
——- ——- ——– ——- ——–
Total liabilities $45,916 $66,696 $115,886 $0
Com stk (7.0 million sh) $46,538 $46,538 $46,538 $46,538 $46,538
Retained earnings 20,076 30,300 32,041
——- ——- ——– ——- ——–
Total equity $66,614 $76,838 $78,579
——- ——- ——– ——- ——–
Total liabilities
and equity $112,530 $143,534 $194,465
======= ======= ======== ======= ========
|::
Table 2: Historical and Pro Forma Income Statements:
Pro Forma
– –
1990 1991 1992 1993 1994
Net sales $350,546 $378,549 $401,251 $441,376
Cost of goods sold 282,252 311,110 342,016
——– ——– ——– ——- ——–
Gross profit $68,294 $67,439 $59,235 $0
Admin and selling exp $25,580 $30,690 $33,762
Depreciation 3,188 3,316 4,080 4,846 3,646
Miscellaneous expenses 4,054 7,114 11,450 0
——– ——– ——– ——- ——–
Total operating exp $32,822 $41,120 $49,292
——– ——– ——– ——- ——–
EBIT $35,472 $26,319 $9,943
Interest on ST loans $638 $1,122 $3,647 $5,906
Interest on LT loans 1,339 2,008 2,008 1,912
Interest on mortgage 516 468 421 341
——– ——– ——– ——- ——–
Total interest $2,493 $3,598 $6,076 $2,253
——– ——– ——– ——- ——–
Before-tax earnings $32,979 $22,721 $3,867
Taxes 13,192 9,088 1,547
——– ——– ——– ——- ——–
Net income $19,787 $13,632 $2,320
======== ======== ======== ======= ========
Dividends on stock 4,947 3,408 580
——– ——– ——– ——- ——–
Additions to
retained earnings $14,841 $10,224 $1,740
======== ======== ======== ======= ========
EPS (7,000,000 shares) $2.83 $1.95 $0.33
======== ======== ======== ======= ========
Table 3: Common Size Balance Sheets:
1990 1991 1992
ASSETS
Cash and marketable
securities 8.82% 5.13%
Accounts receivable 30.39% 25.72%
Inventory 35.36% 48.32%
——- ——- ——-
Current assets 74.55% 79.18% 83.95%
Land, buildings, plant,
and equipment 30.78% 27.31% 22.94%
Accumulated depreciation -5.32% -6.48% -6.88%
——- ——- ——-
Net fixed assets 25.45% 20.82% 16.05%
——- ——- ——-
Total assets 100.00% 100.00% 100.00%
======= ======= =======
LIABILITIES AND EQUITIES
Short-term bank loans 5.67% 7.11%
Accounts payable 12.02% 14.64%
Accruals 6.12% 7.11%
——- ——- ——-
Current liabilities 23.81% 28.85% 46.86%
Long-term bank loans 11.90% 13.99% 10.33%
Mortgage 5.10% 3.62% 2.41%
——- ——- ——-
Long-term debt 17.00% 17.62% 12.73%
——- ——- ——-
Total liabilities 40.80% 46.47% 59.59%
Common stock 41.36% 32.42% 23.93%
Retained earnings 17.84% 21.11% 16.48%
——- ——- ——-
Total equity 59.20% 53.53% 40.41%
——- ——- ——-
Total liabilities
and equity 100.00% 100.00% 100.00%
======= ======= =======
Table 4: Common Size Income Statements:
1990 1991 1992
Net sales 100.00% 100.00% 100.00%
Cost of goods sold 80.52% 82.18% 85.24%
——- ——- ——-
Gross profit 19.48% 17.82%
Admin and selling exp 7.30% 8.11%
Depreciation 0.91% 0.88%
Miscellaneous expenses 1.16% 1.88%
——- ——- ——-
Total operating exp 9.36% 10.86% 12.28%
——- ——- ——-
EBIT 10.12% 6.95% 2.48%
Interest on ST loans 0.18% 0.30% 0.91%
Interest on LT loans 0.38% 0.53% 0.50%
Interest on mortgage 0.15% 0.12% 0.10%
——- ——- ——-
Total interest 0.71% 0.95% 1.51%
——- ——- ——-
Before-tax earnings 9.41% 6.00% 0.96%
Taxes 3.76% 2.40% 0.39%
——- ——- ——-
Net income 5.64% 3.60% 0.58%
======= ======= =======
Dividends on stock 1.41% 0.90% 0.14%
——- ——- ——-
Additions to
retained earnings 4.23% 2.70% 0.43%
======= ======= =======
Table 5: Historical Statements of Cash Flows:
1991 1992 /gnu{right}~q
CASH FLOW FROM OPERATIONS:
Sales $378,549 $401,251 /gnu{left}~q
Increase in receivables (2,728) (21,790)
——– ——– /gnu~q
Cash sales $375,821 $379,461
——– ——–
Cost of goods sold (311,110)
Increase in inventories (29,570)
Increase in accts payable 7,484
Increase in accruals 3,314
——– ——–
Cash cost of goods ($329,882) $0
——– ——–
Cash margin $45,939 $379,461
Admin and selling exp (30,690) (33,762)
Miscellaneous expenses (7,114) (11,450)
Taxes (9,088) (1,547)
——– ——–
Net CF from operations ($953) $332,702
——– ——–
CF FROM FIXED ASSET INVESTMENT:
Investment in fixed assets ($4,561) ($5,409)
——– ——–
CF FROM FINANCING ACTIVITIES:
Increase in short-term debt $3,824
Increase in long-term debt 6,694
Repayment of mortgage (536)
Interest expense (3,598)
Common dividends (3,408)
——– ——–
Net CF from fin act $2,976 $0
——– ——–
Increase (decrease) in cash
and marketable securities ($2,539) $327,293
======== ========
Table 6: Historical and Pro Forma Ratio Analysis:
Pro Forma
– – Industry
1990 1991 1992 1993 1994 Average
LIQUIDITY RATIOS:
Current ratio 3.13 2.74 0.00 2.50
Quick ratio 1.65 1.07 0.00 1.00
LEVERAGE RATIOS:
Debt ratio 50.00%
TIE coverage 14.23 7.31 0.00 7.70
ASSET MGT RATIOS:
Inv turn (cost) 5.70
Inv turn (sale) 7.00
FA turnover 12.00
TA turnover 3.00
DSO (ACP) 32.00
PROFITABILITY RATIOS:
Profit margin 5.64% 3.60% 0.00% 2.90%
Gross PM 19.48% 17.82% 0.00% 18.00%
Return on TA 17.58% 9.50% 0.00% 8.80%
ROE 17.50%
OTHER RATIOS:
Altman Z factor 6.64 4.75 0.00 0.00 4.65
Payout ratio 25.00% 25.00% 0.00% 0.00% 20.00%
SENSITIVITY ANALYSES:
ALTMAN’S Z-SCORE, 1994:
’94 % Sales Made
’93 % Sales Made —– – – – – –
0 40.0% 60.0% 80.0% 100.0% 120.0% 140.0%
40.0% 0.96 1.42 1.87 2.32 2.76 3.20
60.0% 1.44 2.11 2.77 3.41 4.04 4.66
80.0% 1.91 2.78 3.62 4.44 5.24 6.03
100.0% 2.36 3.41 4.44 5.42 6.38 7.32
120.0% 2.79 4.03 5.21 6.36 7.47 8.54
140.0% 3.21 4.61 5.96 7.25 8.50 9.71
CURRENT RATIO, 1994:
’94 % Sales Made
’93 % Sales Made —– – – – – –
0 40.0% 60.0% 80.0% 100.0% 120.0% 140.0%
40.0% 1.61 1.63 1.66 1.68 1.71 1.74
60.0% 1.67 1.71 1.75 1.79 1.83 1.87
80.0% 1.74 1.79 1.84 1.89 1.94 1.99
100.0% 1.80 1.87 1.93 1.99 2.06 2.12
120.0% 1.87 1.94 2.02 2.10 2.18 2.25
140.0% 1.93 2.02 2.11 2.20 2.29 2.38
1993 COGS 1993 Altman’s 1994 COGS 1994 Altman’s Critical
(% of Sales) Z Score (% of Sales) s) Z Score Z Score
0 0
77.5% 5.00 77.5% 6.05 3.00
80.0% 4.48 80.0% 5.42 3.00
82.5% 3.95 82.5% 4.79 3.00
85.0% 3.41 85.0% 4.15 3.00
87.5% 2.86 87.5% 3.50 3.00
90.0% 2.30 90.0% 2.84 3.00
92.5% 1.73 92.5% 2.16 3.00
= = = = = = =
END
STUDENT VERSION INSTRUCTIONS

Recovered_Sheet1

1.6068906412 1.6327344774 1.6585783136 1.6844221498 1.710265986 1.7361098222
1.671437807 1.7102035613 1.7489693156 1.7877350699 1.8265008243 1.8652665786
1.7359849728 1.7876726452 1.8393603176 1.8910479901 1.9427356625 1.9944233349
1.8005321386 1.8651417291 1.9297513197 1.9943609102 2.0589705007 2.1235800912
1.8650793044 1.942610813 2.0201423217 2.0976738303 2.1752053389 2.2527368476
1.9296264702 2.0200798969 2.1105333237 2.2009867504 2.2914401772 2.3818936039
40% ’93 Made
60% ’93 Made
80% ’93 Made
100% ’93 Made
120% ’93 Made
140% ’93 Made
Percentage ’92 Sales Made
’93 CURRENT RATIO
EFFECT OF PERCENTAGE SALES MADE ON ’93 CURRENT RATIO

Recovered_Sheet2

0.9560844219 1.4183120193 1.8731962427 2.3210172772 2.762041233 3.1965210182
1.4423767313 2.1138003157 2.7702241827 3.4124652649 4.0412822286 4.6573805781
1.9095187178 2.7781104893 3.6223974847 4.4440632058 5.2446391834 6.025521747
2.3592366687 3.4145172172 4.4350571579 5.4237317406 6.3831074465 7.3154823515
2.7930554379 4.0258520975 5.2127286185 6.3580529221 7.4656559855 8.5389118892
3.212326994 4.6145761358 5.9592716451 7.2525415282 8.4996736396 9.7052548918
40% ’93 Made
60% ’93 Made
80% ’93 Made
100% ’93 Made
120% ’93 Made
140% ’93 Made
Percentage ’92 Sales Made
’93 Altman’s Z Score
EFFECT OF PERCENTAGE SALES MADE ON ’93 ALTMAN’S Z SCORE

Recovered_Sheet3

6.0478251512 3
5.4237317406 3
4.7912991044 3
4.1497102887 3
3.4980380268 3
2.8352254693 3
2.1600627281 3
93 ALTMAN’S Z SCORE
Critical Z Score
’93 Cost of Goods Sold (% of Sales)
’93 Altman’s Z Score
THE EFFECT OF ’93 COGS (% OF SALES) ON ’93 ALTMAN’S Z SCORE

Leave a Reply

Your email address will not be published. Required fields are marked *