Build a Model

Posted by:Prof. Lexx Posted on:May 6,2016
Chapter14.  Ch 14-10 Build a Model
Cumberland Industries’ financial planners must forecast the company’s financial results for the coming year.  The forecast for many items will be based on sales, and any additional funds needed will be obtained as notes payable.
a.  Assuming the historical trend continues, what will sales be in 2008?  Base your forecast on a spreadsheet regression analysis of the 2002-2007 sales data above, and include the summary output of the regression in your answer.  By what percentage are sales predicted to increase in 2008 over 2007?  Is the sales growth rate increasing or decreasing?
Here are the company’s historical sales.  Hint: Use the Trend function to forecast sales for 2008.
Year Sales Growth Rate
2002 129,215,000
2003 180,901,000
2004 235,252,000
2005 294,065,000
2006 396,692,000
2007 455,150,000
 % Increase in Predicted Sales for 2008 over 2007:
2007 Sales 455,150,000
2008 Sales
% increase      Note: This growth rate has been declining over time.
b.   Cumberland’s management believes that the firm will actually experience a 20 percent increase in sales during 2008.  Construct 2008 pro forma financial statements.  Cumberland will not issue any new stock or long-term bonds.  Assume Cumberland will carry forward its current amounts of short-term investments and notes payable, prior to calculating AFN.  Assume that any Additional Funds Needed (AFN) will be raised as notes payable (if AFN is negative, Cumberland will purchase additional short-term investments).  Use an interest rate of 9 percent for short-term debt (and for the interest income on short-term investments) and a rate of 11 percent for long-term debt.  No interest is earned on cash.  Use the beginning of year debt balances to calculate net interest expense.  Assume that dividends grow at an 8 percent rate.
Key Input Data: Used in the
Tax rate 40%
Dividend growth rate 8%
S-T rd 9%
L-T rd 11%
December 31 Income Statements:
(in thousands of dollars)
Forecasting 2007 2008 2008
2007 basis Ratios Inputs Forecast
Sales $455,150 Growth
Expenses (excluding depr. & amort.) $386,878 % of sales
  EBITDA $68,273
Depreciation and Amortization $7,388 % of fixed assets
  EBIT $60,885
Net Interest Expense $8,575 Interest rate x beginning of year debt
  EBT $52,310
Taxes (40%) $20,924
  Net Income $31,386
Common dividends $12,554 Growth
Addition to retained earnings (DRE) $18,832
Cumberland Industries December 31 Balance Sheets
(in thousands of dollars)
Forecasting 2007 2008 2008
2007 basis Ratios Inputs Without AFN AFN
Cash and cash equivalents $91,450 % of sales
Short-term investments $11,400 Previous
Accounts Receivable $103,365 % of sales
Inventories $38,444 % of sales
  Total current assets $244,659
  Fixed assets $67,165 % of sales
Total assets $311,824
Liabilities and equity
Accounts payable $30,761 % of sales
Accruals $30,477 % of sales
Notes payable $16,717 Previous
  Total current liabilities $77,955
Long-term debt $76,264 Previous
  Total liabilities $154,219
Common stock $100,000 Previous
Retained Earnings $57,605 Previous + DRE
  Total common equity $157,605
Total liabilities and equity $311,824
Required assets =
Specified sources of financing =
Additional funds needed (AFN) =
Required additional notes payable =
Additional short-term investments =
c.   Now create a graph depicting the sensitivity of AFN for the coming year to the sales growth rate.  To make this graph, compare the AFN at sales growth rates of 5%, 10%, 15%, 20%, 25%, and 30%.
We can use a data table to answer this question:
Sales 2008 AFN
Growth rate $0
d.   Calculate the Net Operating Working Capital (NOWC), Total Operating Capital, and NOPAT for 2007  and 2008.  Also, calculate the FCF for 2008.
Net Operating Working Capital
NOWC07 = Operating CA Operating CL
NOWC08 = Operating CA Operating CL
Total Operating Capital
TOC07      = NOWC + Fixed assets
= +
TOC08  = NOWC + Fixed assets
= +
Net Operating Profit After Taxes
NOPAT07 = EBIT x ( 1 – T )
= x
NOPAT08 = EBIT x ( 1 – T )
= x
Free Cash Flow
FCF08 = NOPAT Increase in TOC
e. Suppose Cumberland can reduce its inventory to sales ratio to 5 percent and its cost to sales ratio to 83 percent.  What happens to AFN and FCF?
Input Base Case New Scenario
Inv. / Sales 0.0% 5.0%    Note: we used the Scenario Manager.
Costs / Sales 0.0% 83.0%

Prof. Lexx

Overview I have a Bachelor of Arts, majoring in English and minoring in History. I have worked in various fields ranging from academic research to freelance translating to editing to customer support and data entry. These include editing my old university's newspaper as well as co-leading their creative writing team; serving as a junior member in the history department's research network; publishing music and film reviews for several different magazines and webzines; and translating papers and books for numerous researchers in various languages, including Russian, Spanish, and Romanian. I am also a skilled typist, with a rate of at least 70 words per minute, and have myself digitized dozens of books and essays for both private and commercial use. I have a very strong work ethic, and make sure to prioritize the task I am given so that it is completed as quickly as possible. I am organized and disciplined, ensuring a job done professionally and efficiently.

    I have a background of 8 years in writing profession and currently pursuing also as an editor and proofreader. I have a knack for writing and thus, it was obvious to enhance my skills and serve others. Currently joined ‘Member of Association of Professional Writers and Editors’ a

    Pro Alex