Explore BrainMass
Share

Explore BrainMass

    Cumberland Industries - AFN

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    I am having trouble setting up this spreadsheet for part B of this problem. Any help appreciated. Thanks.

    Cumberland Industries' financial planners must forecast the company's financial results for the coming year. The forecast will be based o the percent of sales method, and any additional funds needed will be obtained as notes payable.

    a. Assuming the historical trend continues, what will sales be in 2006? Base your forecast on a spreadsheet regression analysis of the 2000-2005 sales data above, and include the summary output of the regression in your answer. By what percentage are sales predicted to increase in 2006 over 2005? Is the sales growth rate increasing or decreasing?

    Do not have to do part A
    Here are the company's historical sales. Hint: Use the Trend function to forecast sales for 2006.

    Year Sales Growth Rate
    2000 129,215,000
    2001 180,901,000
    2002 235,252,000
    2003 294,065,000
    2004 396,692,000
    2005 455,150,000
    2006 515,465,267

    % Increase in Predicted Sales for 2006 over 2005:

    2005 Sales 455,150,000
    2006 Sales 515,465,267

    % increase 13.25% 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 2006. Construct 2006 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
    forecast
    Tax rate 40%
    Dividend growth rate 8%
    S-T rd 9%
    L-T rd 11%

    December 31 Income Statements:
    (in thousands of dollars)
    Forecasting 2005 2006 2006
    2005 basis Ratios Inputs Forecast
    Sales $455,150 Growth 20.00% $546,180
    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 2005 2006 2006
    2005 basis Ratios Inputs Without AFN AFN
    Assets:
    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%.

    Don't have to do C
    We can use a data table to answer this question:
    Sales 2006 AFN
    Growth rate $0

    d. Calculate the Net Operating Working Capital (NOWC), Total Operating Capital, and NOPAT for 2005 and 2006. Also, calculate the FCF for 2006.

    Don't have to do d
    Net Operating Working Capital

    NOWC05 = Operating CA - Operating CL
    = -
    =

    NOWC06 = Operating CA - Operating CL
    = -
    =

    Total Operating Capital

    TOC05 = NOWC + Fixed assets
    = +
    =

    TOC06 = NOWC + Fixed assets
    = +
    =

    Net Operating Profit After Taxes

    NOPAT05 = EBIT x ( 1 - T )
    = x
    =

    NOPAT06 = EBIT x ( 1 - T )
    = x
    =

    Free Cash Flow

    FCF06 = 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?

    don't have to do part E
    Input Base Case New Scenario
    Inv. / Sales 0.0% 5.0% Note: we used the Scenario Manager.
    Costs / Sales 0.0% 83.0%
    FCF
    AFN

    © BrainMass Inc. brainmass.com October 9, 2019, 8:43 pm ad1c9bdddf
    https://brainmass.com/business/finance/cumberland-industries-afn-160388

    Attachments

    Solution Preview

    Please see the attached file.

    Cumberland Industries' financial planners must forecast the company's financial results for the coming year. The forecast will be based o the percent of sales method, and any additional funds needed will be obtained as notes payable.

    a. Assuming the historical trend continues, what will sales be in 2006? Base your forecast on a spreadsheet regression analysis of the 2000-2005 sales data above, and include the summary output of the regression in your answer. By what percentage are sales predicted to increase in 2006 over 2005? Is the sales growth rate increasing or decreasing?
    Do not have to do part A
    Here are the company's historical sales. Hint: Use the Trend function to forecast sales for 2006.

    Year Sales Growth Rate
    2000 129,215,000
    2001 180,901,000 40.0%
    2002 235,252,000 30.0%
    2003 294,065,000 25.0%
    2004 396,692,000 34.9%
    2005 455,150,000 14.7%
    2006 515,465,267

    % Increase in Predicted Sales for 2006 over 2005:

    2005 Sales 455,150,000
    2006 ...

    Solution Summary

    The solution explains how to calculate the AFN for Cumberland Industries. ONLY PART B of the question is answered in the solution

    $2.19