Use the case study "Cartwright Lumber Company" to answer the questions-
1.
|
Year
|
2004
|
2004
|
2005
|
2006
|
2007
|
2008
|
|
Net Sales
|
$2,694
|
|
|
|
|
|
|
Growth rate in net sales
|
|
33.6%
|
20%
|
20%
|
20%
|
20%
|
|
Cost of goods sold/net sales
|
|
72.4%
|
72.4%
|
72.4%
|
72.4%
|
72.4%
|
|
GS&A expenses/net sales
|
|
24.4%
|
24.4%
|
24.4%
|
24.4%
|
24.4%
|
|
Long-term debt
|
|
$ 50
|
$ 43
|
$ 37
|
$ 30
|
$ 23
|
|
Current portion long-term debt
|
|
$ 7
|
$ 7
|
$ 7
|
$ 7
|
$ 7
|
|
Interest rate
|
|
8.0%
|
8.0%
|
8.0%
|
8.0%
|
8.0%
|
|
Tax rate
|
|
35.0%
|
35.0%
|
35.0%
|
35.0%
|
35.0%
|
|
Dividend/earnings after tax
|
|
0.0%
|
0.0%
|
0.0%
|
0.0%
|
0.0%
|
|
Current assets/net sales
|
|
28.8%
|
28.8%
|
28.8%
|
28.8%
|
28.8%
|
|
Net fixed assets
|
|
$ 157
|
$ 160
|
$ 163
|
$ 166
|
$ 168
|
|
Current liabilities/ net sales
|
|
11.2%
|
11.2%
|
11.2%
|
11.2%
|
11.2%
|
|
Owner's equity (net worth)
|
$348
|
|
|
|
|
|
|
INCOME STATEMENT
|
|
|
|
|
|
|
|
|
Equations
|
Forecast
|
|
|
|
|
|
Year
|
2004
|
2004
|
2005
|
2006
|
2007
|
2008
|
|
Net sales
|
=B3+(B3*C4)
|
$3,600
|
$4,320
|
$5,184
|
$6,220
|
$7,464
|
|
Cost of goods sold
|
=C5*C20
|
2,606
|
3,127
|
3,752
|
4,502
|
5,403
|
|
Gross profit
|
=C20-C21
|
994
|
1,193
|
1,432
|
1,718
|
2,061
|
|
GSA expense
|
=C6*C20
|
879
|
1,055
|
1,266
|
1,519
|
1,823
|
|
EBIT
|
=C22-C23
|
115
|
138
|
165
|
199
|
238
|
|
Interest expense
|
=(C7+C8)*C9
|
5
|
4
|
4
|
3
|
2
|
|
Earnings before tax
|
=C24-C25
|
110
|
134
|
162
|
196
|
236
|
|
Tax
|
=C10*C26
|
39
|
47
|
57
|
68
|
83
|
|
Earnings after tax
|
=C26-C27
|
72
|
87
|
105
|
127
|
153
|
|
Dividends paid
|
=C11*C28
|
0
|
0
|
0
|
0
|
0
|
|
Additions to retained earnings
|
=C28-C29
|
72
|
87
|
105
|
127
|
153
|
|
|
|
|
|
|
|
|
|
BALANCE SHEET
|
|
|
|
|
|
|
|
Current assets
|
=C12*C20
|
1,037
|
1,244
|
1,493
|
1,792
|
2,150
|
|
Net fixed assets
|
=C13
|
157
|
160
|
163
|
166
|
168
|
|
Total assets
|
=C33+C34
|
1,194
|
1,404
|
1,656
|
1,958
|
2,318
|
|
|
|
|
|
|
|
|
|
Current liabilities
|
=C14*C20
|
404
|
484
|
581
|
697
|
837
|
|
Long-term debt
|
=C7
|
50
|
43
|
37
|
30
|
22
|
|
Equity
|
=B15+C30
|
420
|
507
|
612
|
739
|
893
|
|
Total liabilities and
|
=C37+C38+C39
|
873
|
1,034
|
1,230
|
1,466
|
1,751
|
|
shareholder's equity
|
|
|
|
|
|
|
|
EXTERNAL FUNDING REQUIRED
|
=C35-C40
|
$321
|
$370
|
$426
|
$491
|
$567
|
Q1a. Discuss how the interest expense (row 25) calculation works and whether or not it includes the short term borrowing on the Cartwright balance sheet. HINT: What is the source of the data used in the ratio on row 14?
Q1b. How much does Cartwright need to borrow and when? Explain by citing specifics from the forecast.
Q1c. Does Cartwright have the ability to pay the interest expense? Explain by citing specifics from the forecast.
Q1d. Does Cartwright have the ability to repay the loan principal? Explain by citing specifics from the forecast.
2.
|
Year
|
2004
|
2004
|
2005
|
2006
|
2007
|
2008
|
|
Net Sales
|
$2,694
|
|
|
|
|
|
|
Growth rate in net sales
|
|
0.0%
|
0%
|
0%
|
0%
|
0%
|
|
Cost of goods sold/net sales
|
|
0.0%
|
0.0%
|
0.0%
|
0.0%
|
0.0%
|
|
GS&A expenses/net sales
|
|
0.0%
|
0.0%
|
0.0%
|
0.0%
|
0.0%
|
|
Long-term debt
|
|
$ 50
|
$ 43
|
$ 37
|
$ 30
|
$ 23
|
|
Current portion long-term debt
|
|
$ 7
|
$ 7
|
$ 7
|
$ 7
|
$ 7
|
|
Interest rate
|
|
0.0%
|
0.0%
|
0.0%
|
0.0%
|
0.0%
|
|
Tax rate
|
|
35.0%
|
35.0%
|
35.0%
|
35.0%
|
35.0%
|
|
Dividend/earnings after tax
|
|
0.0%
|
0.0%
|
0.0%
|
0.0%
|
0.0%
|
|
Current assets/net sales
|
|
0.0%
|
0.0%
|
0.0%
|
0.0%
|
0.0%
|
|
Net fixed assets
|
|
$ 157
|
$ 160
|
$ 163
|
$ 166
|
$ 168
|
|
Current liabilities/ net sales
|
|
0.0%
|
0.0%
|
0.0%
|
0.0%
|
0.0%
|
|
Owner's equity (net worth)
|
$348
|
|
|
|
|
|
|
INCOME STATEMENT
|
|
|
|
|
|
|
|
|
Equations
|
Forecast
|
|
|
|
|
|
Year
|
2004
|
2004
|
2005
|
2006
|
2007
|
2008
|
|
Net sales
|
=B3+(B3*C4)
|
$2,694
|
$2,694
|
$2,694
|
$2,694
|
$2,694
|
|
Cost of goods sold
|
=C5*C20
|
0
|
0
|
0
|
0
|
0
|
|
Gross profit
|
=C20-C21
|
2,694
|
2,694
|
2,694
|
2,694
|
2,694
|
|
GSA expense
|
=C6*C20
|
0
|
0
|
0
|
0
|
0
|
|
EBIT
|
=C22-C23
|
2,694
|
2,694
|
2,694
|
2,694
|
2,694
|
|
Interest expense
|
=(C7+C8)*C9
|
0
|
0
|
0
|
0
|
0
|
|
Earnings before tax
|
=C24-C25
|
2,694
|
2,694
|
2,694
|
2,694
|
2,694
|
|
Tax
|
=C10*C26
|
943
|
943
|
943
|
943
|
943
|
|
Earnings after tax
|
=C26-C27
|
1,751
|
1,751
|
1,751
|
1,751
|
1,751
|
|
Dividends paid
|
=C11*C28
|
0
|
0
|
0
|
0
|
0
|
|
Additions to retained earnings
|
=C28-C29
|
1,751
|
1,751
|
1,751
|
1,751
|
1,751
|
|
|
|
|
|
|
|
|
|
BALANCE SHEET
|
|
|
|
|
|
|
|
Current assets
|
=C12*C20
|
0
|
0
|
0
|
0
|
0
|
|
Net fixed assets
|
=C13
|
157
|
160
|
163
|
166
|
168
|
|
Total assets
|
=C33+C34
|
157
|
160
|
163
|
166
|
168
|
|
|
|
|
|
|
|
|
|
Current liabilities
|
=C14*C20
|
0
|
0
|
0
|
0
|
0
|
|
Long-term debt
|
=C7
|
50
|
43
|
37
|
30
|
22
|
|
Equity
|
=B15+C30
|
2,099
|
3,850
|
5,601
|
7,352
|
9,104
|
|
Total liabilities and
|
=C37+C38+C39
|
2,149
|
3,893
|
5,638
|
7,382
|
9,126
|
|
shareholder's equity
|
|
|
|
|
|
|
|
EXTERNAL FUNDING REQUIRED
|
=C35-C40
|
($1,992)
|
($3,733)
|
($5,475)
|
($7,216)
|
($8,958)
|
|
FROM P 45 IN COHEN FINANCE WORKBOOK:
|
|
|
|
|
|
|
|
|
|
FOR EACH $1 CHANGE IN REVENUE:
|
|
|
|
|
|
|
|
|
|
Assumptions:
|
|
|
|
|
|
|
|
|
|
revenue
|
|
2694
|
|
|
|
|
|
|
|
net profit margin
|
|
1.6%
|
|
|
|
|
|
|
|
CHANGE IN ASSETS (USES OF FUNDS)
|
|
|
|
|
CHANGE IN LIABILITIES+EQUITY (SOURCES OF FUNDS)
|
|
|
|
|
|
|
|
cents
|
|
|
|
|
cents
|
|
CURRENT ASSETS
|
|
|
|
|
CURRENT LIABILITIES
|
|
|
|
|
|
RECEIVABLES
|
317
|
|
|
|
PAYABLES
|
256
|
|
|
|
INVENTORY
|
418
|
|
|
|
OTHER ACCRUALS
|
39
|
|
|
CA/SALES
|
|
|
27.3
|
|
CL/SALES
|
|
|
11.0
|
|
|
|
|
|
|
|
|
|
|
|
FIXED ASSETS
|
|
|
|
|
LONG-TERM DEBT
|
|
|
0
|
|
|
PLANT
|
|
|
|
|
|
|
|
|
|
PROPERTY
|
|
|
|
EQUITY
|
|
|
|
|
|
EQUIPMENT
|
|
|
|
|
INCR IN RET EARN/SALES
|
|
1.6
|
|
FA/SALES
|
|
|
0.0
|
|
|
|
|
|
|
|
|
|
|
|
TOTAL FORECASTED SOURCES
|
|
|
12.6
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
EXTERNAL FINANCING NEEDED
|
|
|
14.7
|
|
|
|
|
|
|
|
|
|
|
|
TOTAL FORECASTED USES
|
27.3
|
|
ADJUSTED TOTAL FORECASTED SOURCES
|
27.3
|
Q2a. Explain how the p 45 table from the Cohen Finance Workbook, shown above starting on row 47, works and its significance to Cartwright's (and most other businesses too) external financing needs problem.
Q2b. Revise the short-form forecast model from Q1, using the 'input cells zeroed' model at the top of this tab.
As the banker, assume a lower growth rate in sales, and explain, showing specifics from the revised forecast, how it helps Cartwright solve his external financing needed problem. Enter revised data in the blue cells, using your judgment.