468
Comprehensive/Spreadsheet Problem
Chapter 16: Financial Planning and Forecasting
b.
Part I. Inputs
2019 2020
Growth rate, g NA 25% Tax rate (T) 25%
Operating costs / Sales 85.51% 82.00% Interest rate 11.00%
Fixed Inputs
Adjustable Inputs
Part II. Income Statements 2019 Change 2020
Sales $36,000.0 (1+ g) $45,000.0
Operating costs (includes depreciation) 30,783.0 0.820 36,900.0
Earnings before interest and taxes (EBIT) $5,217.0 $8,100.0
Part III. Balance Sheets 2019 Change 2020
Assets
Cash $1,800 (1+ g) $2,250.0
Accounts receivable 10,800 0.3000 13,500.0
Liabilities and Equity
Payables + accruals (both grow with sales) $9,720.0 (1+ g) $12,150.0
Short-term bank loans 3,472.0 See notes 3,553.2
Chapter 16: Financial Planning and Forecasting
Comprehensive/Spreadsheet Problem
469
Part IV. Ratios and EPS 2019 2020E
Operating costs/Sales 85.51% 82.00%
Receivables/Sales 30.00% 30.00%
Days sales outstanding (DSO) 109.50 days 109.50 days
Total assets turnover 0.77 ×0.85 ×
Assets/Equity (equity multiplier) 1.64 ×1.72 ×
Times interest earned (TIE) 5.13 ×7.25 ×
Profit margin 8.75% 11.64%
470
Comprehensive/Spreadsheet Problem
Chapter 16: Financial Planning and Forecasting
Part V. Notes on Calculations
Full capacity sales $48,000.00
Target Fixed assets/Sales 45.00%
Required level of fixed assets $20,250.00
Chapter 16: Financial Planning and Forecasting
Integrated Case
471
Integrated Case
16-16
New World Chemicals Inc.
Financial Forecasting
Sue Wilson, the new financial manager of New World Chemicals (NWC), a
California producer of specialized chemicals for use in fruit orchards, must
prepare a formal financial forecast for 2020. NWC’s 2019 sales were $2
billion, and the marketing department is forecasting a 25% increase for 2020.
Wilson thinks the company was operating at full capacity in 2019, but she is
Table IC 16.1. Financial Statements and Other Data on NWC (Millions of Dollars)
A. Balance Sheets
2019 2020E
Cash and equivalents $ 20 $ 25
Accounts receivable 240 300
472
Integrated Case
Chapter 16: Financial Planning and Forecasting
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
C. Key Ratios
NWC (2019) NWC (2020E) 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
Assume that you were recently hired as Wilson’s assistant and that your
first major task is to help her develop the formal financial forecast. She asks
you to begin by answering the following questions.
A. 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?
Chapter 16: Financial Planning and Forecasting
Integrated Case
473
Answer: [Show S16-1 through S16-8 here.] NWC will need $169.9 million.
Here is the AFN equation:
AFN = (A0*/S0)S – (L0*/S0)S – M(S1)(1 – Payout)
B. Consultations with several key managers within NWC, including
production, inventory, and receivable managers, have yielded some
very useful information.
(1) NWC’s high DSO is largely due to one significant customer who
battled through some hardships the past 2 years but who
(2) NWC was operating slightly below capacity, but its forecasted
(3) A relatively new inventory management system (installed last
year) has taken some time to catch on and to operate efficiently.
NWC’s inventory turnover improved slightly last year, but this
year NWC expects even more improvement as inventories
474
Integrated Case
Chapter 16: Financial Planning and Forecasting
Answer: [Show S16-9 through S16-11 here.]
Balance Sheets (In Millions of Dollars)
1st Pass Final Forecast
2019 2020 2020
Cash and equivalents $ 20 $ 25 $ 67e
Accounts receivable 240 300 233a
Total assets $1,000 $1,250 $1,250
Accounts payable and accr. liab. $ 100 $ 125 $ 125
Notes payable 100 190 190
Total current liabilities $ 200 $ 315 $ 315
Notes:
a DSO will be reduced to 34 days, without adversely affecting sales. Sales = $2,500;
DSO = 34; AR = ?
DSO = AR/Sales/365
34 = AR/$2,500/365
b Given in problem that forecasted growth will require a new facility, which will
increase the firm’s net fixed assets to $700 million.
c A new inventory management system will increase its inventory turnover to 10.
Sales = $2,500; Inv. TO = 10; Inv. = ?
Inv. TO = Sales/Inv.
d Total assets do not change; TA = $1,250.
Total CA = Total assets – Net FA
e Cash and equivalents = Total CA – AR – Inv.
The final forecasted Income Statement is the same as the initial
forecast.
Chapter 16: Financial Planning and Forecasting
Integrated Case
475
C. Calculate NWC’s forecasted ratios based on its final forecast and
compare them with the company’s 2019 historical ratios, the 2020
initial forecast ratios, and the industry averages. How does NWC
compare with the average firm in its industry, and is the company’s
financial position expected to improve during the coming year?
Explain.
Answer: [Show S16-12 here.]
Key Ratios
1st Pass
Final
2019
2020
2020
Industry
Comment
Basic earning power
10.00%
10.00%
10.00%
20.00%
Low
Profit margin
3.15
3.15
3.15
4.00
Low
Return on equity
9.00
10.43
10.43
15.60
Low
DSO (365 days)
43.80 days
34.00 days
32.00 days
Inventory turnover
Fixed assets turnover
4.00
5.00
Low
Total assets turnover
2.00
2.50
Total liabilities/Assets
30.00%
39.60%
39.60%
36.00%
Times interest earned
Low
Current ratio
2.50
3.00
Low
Compared with industry averages, the firm’s inventory
turnover and total assets turnover are slightly low. Its payout ratio
is identical to the industry average. The firm’s DSO is close to the
industry average. All other ratios compare poorly to industry
averages.
476
Integrated Case
Chapter 16: Financial Planning and Forecasting
D. Based on the final forecast, calculate NWC’s free cash flow for 2020.
How does this FCF differ from the FCF forecasted by NWC’s initial
“business as usual” forecast?
Answer: [Show S16-13 and S16-14 here.]
FCF = EBIT(1 – T) + Depreciation –
+
NOWC
esexpenditur
capital Gross
However,
+−
NOWConDepreciati
esexpenditur
capital Gross
=
capital in
investmentNet
.
So, you can rewrite the FCF equation as:
FCF = EBIT(1 – T) – Net investment in capital.
2019 1st Pass 2020 Final 2020
*Note: All cash is used in operations.
Chapter 16: Financial Planning and Forecasting
Integrated Case
477
E. 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?
Answer: [Show S16-15 and S16-16 here.]
Full capacity sales =
operated FA were
at whichcapacity of %
sales Actual
478
Integrated Case
Chapter 16: Financial Planning and Forecasting
F. How would changes in the following items affect the AFN?
(Consider each item separately and hold all other things constant.)
(1) The dividend payout ratio.
Answer: [Show S16-17 and S16–18 here.] If the payout ratio were reduced,
then more earnings would be retained, and this would reduce the
need for external financing, or AFN. Note that if the firm is profitable
F. (2) The profit margin.
Answer: If the profit margin increases, then both total and additions to
retained earnings will increase, and this will reduce the amount of
AFN.
F. (3) The capital intensity ratio.
Answer: The capital intensity ratio is defined as the ratio of required assets
to total sales, or A0*/S0. Put another way, it represents the dollars
Chapter 16: Financial Planning and Forecasting
Integrated Case
479
F. (4) NWC beginning to buy from its suppliers on terms that permit it to
pay after 60 days rather than after 30 days.
Answer: If NWC’s payment terms were increased from 30 to 60 days, accounts