Long-term debt 100 180
Common stock 500 500
Retained earnings 200 255
Net income 63.00$ 78.75$
1
2
3
4
5
6
7
8
9
10
11
12
13
20
21
22
23
28
29
30
31
32
33
38
39
41
A B C D E F G H I
16 Case model
INPUT DATA (for New World Chemicals Inc.)
TABLE IC16.1 Financial Statements and Other Data on NWC
(MILLIONS OF DOLLARS)
A. BALANCE SHEETS 2019 2020E
Assets
Cash and equivalents 20$ 25$
Liabilities and equity
Change 100
Accounts payable & accrued liabilities 100$ 125$
Notes payable 100 190
Total current liabilities 200$ 315$
B. INCOME STATEMENTS 2019 2020E
Sales 2,000.00$ 2,500.00$
Variable costs 1,200.00 1,500.00
Fixed costs 700.00 875.00
EBIT 100.00$ 125.00$
Dividends (30%) $18.90 $23.63
This spreadsheet model is designed to be used in conjunction with the chapter’s integrated case and the
related PowerPoint slide presentation.
9/12/2022 17:23
Chapter 16. Financial Planning and Forecasting
12/9/2018
Accounts receivable 240 300
Net fixed assets 500 625
Total assets 1,000$ 1,250$ NOWC 2019 400$
42
43
(A0* / S0) × (g × S0) −(L0* / S0) × (g × S0) − M × (S0)(1 + g) × (1 − Payout)
44
45
46
55
56
59
60
61
62
63
64
A B C D E F G H I
C. KEY RATIOS
NWC2019 NWC2020E INDUSTRY COMMENT
Basic earning power 10.00% 10.00% 20.00%
Profit margin 3.15% 3.15% 4.00%
Return on equity 9.00% 10.43% 15.60%
OTHER INPUT DATA
PART A
Calculations in millions of dollars:
AFN =
(A0* / S0) × (DS) −(L0* / S0) × (DS) −M × (S1) × (1 ‒ Payout)
Assume (1) that NWC was operating at full capacity in 2019 with respect to all assets, (2) that all assets must
grow at the same rate as sales, (3) that accounts payable and accrued liabilities also will grow at the same rate
as sales, and (4) that the 2019 profit margin and dividend payout will be maintained. Under those conditions,
what would the AFN equation predict the company’s financial requirements to be for the coming year?
69
70
71
72
73
74
Long-term debt 180 180
Common stock 500 500
Retained earnings 255 255
EBIT 125.00$ 125.00$
EBT 105.00$ 105.00$
Taxes 25% 26.25 26.25
Dividends (30%) $23.63 $23.63
Addition to Retained Earnings $55.13 $55.13
Return on Equity 10.43% 9.00% 15.60%
Inventory turnover 10.00 8.33 11.00
Fixed assets turnover 3.57 4.00 5.00
Total liabilities/Assets 39.60% 30.00% 36.00%
Times interest earned 6.25 6.25 9.40
Current ratio 1.75 2.50 3.00
Payout ratio 30.00% 30.00% 30.00%
75
76
77
78
79
84
85
86
87
88
93
94
95
96
97
106
107
108
109
110
111
A B C D E F G H I
PART B
Revised forecast based on managerial input …
Expected DSO (days) 34.00
New net fixed assets (in $M) $700
Expected inventory turnover 10.00
BALANCE SHEETS 2020E 2020R
Assets
Cash and equivalents 25$ 67$
Accounts receivable 300 233
Liabilities and equity
Accounts payable & accrued liab.
125$ 125$
Notes payable 190 190
Total current liabilities 315$ 315$
INCOME STATEMENTS 2020E 2020R
Sales 2,500.00$ 2,500.00$
Less: variable costs 1,500.00 1,500.00
Fixed costs 875.00 875.00
PART C
Calculate NWC’s forecasted ratios.
NWC2020R NWC2019 INDUSTRY
Basic earning power 10.00% 10.00% 20.00%
Profit margin 3.15% 3.15% 4.00%
Total current assets 625$ 550$
Net fixed assets 625 700
Total assets 1,250$ 1,250$
121
122
123
124
125
131
132
133
134
135
136
137
A B C D E F G H I
PART D
Calculate NWC‘s free cash flow for 2020. Is the initial forecast for FCF different from the revised one?
Initial Revised
Net investment in capital*
$225.00 $225.00
PART E
Full capacity sales = Actual sales / % of capacity for FA
Initially, some NWC managers questioned whether the new facility expansion was necessary, especially as it
results in increasing net fixed assets from $500 million to $700 million (a 40% increase). However, after
extensive discussions about NWC needing to position itself for future growth and being flexible and
competitive in today’s marketplace, NWC’s top managers agreed that the expansion was necessary. Among
the issues raised by opponents was that NWC’s fixed assets were being operated at only 85% of capacity.
Assuming that its fixed assets were operating at only 85% of capacity, by how much could sales have
increased, both in dollar terms and in percentage terms, before NWC reached full capacity?
The initial and revised forecast for FCF2020 is exactly the same because only the composition of NOWC and net
fixed assets are different.
EBIT (1 – T) $93.75 $93.75