Assignment #1 ? Cumberland IndustriesChapter 14 ? Financial Planning & Forecasting Pro Forma Financial StatementsCumberland 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 Rate2002 129,215,0002003 180,901,0002004 235,252,0002005 294,065,0002006 396,692,0002007 455,150,0002008=TREND(C17:C22,B17:B22,B23)% Increase in Predicted Sales for 2008 over 2007:2007 Sales 455,150,0002008 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.Used in theKey Input Data: forecastTax 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 20082007 basis Ratios Inputs ForecastSales $455,150 GrowthExpenses (excluding depr. & amort.) $386,878 % of salesEBITDA $68,273Depreciation and Amortization $7,388 % of fixed assetsEBIT $60,885Net Interest Expense $8,575 Interest rate x beginning of year debtEBT $52,310Taxes (40%) $20,924Net Income $31,386Common dividends $12,554 GrowthAddition to retained earnings (DRE) $18,832Cumberland Industries December 31 Balance Sheets(in thousands of dollars)Forecasting 2007 2008 20082007 basis Ratios Inputs Without AFN AFN With AFNAssets:Cash and cash equivalents $91,450 % of salesShort-term investments $11,400 PreviousAccounts Receivable $103,365 % of salesInventories $38,444 % of salesTotal current assets $244,659Fixed assets $67,165 % of salesTotal assets $311,824Liabilities and equityAccounts payable $30,761 % of salesAccruals $30,477 % of salesNotes payable $16,717 PreviousTotal current liabilities $77,955Long-term debt $76,264 PreviousTotal liabilities $154,219Common stock $100,000 PreviousRetained Earnings $57,605 Previous + DRETotal common equity $157,605Total liabilities and equity $311,824Required 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 AFNGrowth rate $0d. 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 CapitalNOWC07 = Operating CA ? Operating CL= ?=NOWC08 = Operating CA ? Operating CL= ?=Total Operating CapitalTOC07 = NOWC + Fixed assets= +=TOC08 = NOWC + Fixed assets= +=Net Operating Profit After TaxesNOPAT07 = EBIT x ( 1 ? T )= x=NOPAT08 = EBIT x ( 1 ? T )= x=Free Cash FlowFCF08 = 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 ScenarioInv. / Sales 0.0% 5.0%Costs / Sales 0.0% 83.0%FCFAFN!
Our group of high quality writers are ready to help you with a similar paper within a specified deadline. Just click ORDER NOW and provide us with your assignment details, contact information and make payments. You will get periodic updates on order progress in your email.
USE THE CALCULATOR BELOW TO GET THE QUOTE