Start with the partial model in the file Ch 05 Build a Model.xlsx from the textbook's website. Cumberland Industries' financial planners must forecast the company's financial results for the corning year. The forecast will be based on the forecasted financial statements method, and any additional funds needed will be obtained by using a mix of notes payable, long-term debt, and common stock. No preferred stock will be issued. Data for the problem, including Cumberland Industries' balance sheet and income statement, can be found in the spreadsheet problem for Chapter 2. Use these data to answer the following questions.

a. Cumberland Industries has had the following sales since 2010. Assuming the historical trend continues, what will sales be in 2016?

Year....................................................Sales
2010.............................................. $129,215,000
2011.............................................180,901,000
2012............................................. 235,252,000
2013............................................. 294,065,000
2014 ............................................396,692,000
2015 ............................................ 455,150,000

Base your forecast on a spreadsheet regression analysis of the 2010--2015 sales. By what percentage are sales predicted to increase in 2016 over 2015? Is the sales growth rate increasing or decreasing?

b. Cumberland's management believes that the firm will experience a 20% increase in sales during 2016. Construct the 2016 pro forma financial statements. Cumberland will not issue any new stock or long-term bonds. Assume that Cumberland will carry forward its current amounts of short-term investments and notes payable, prior to calculating additional funds needed (AFN). Assume that any AFN will be raised as notes payable (if AFN is negative, Cumberland will purchase additional short-term investments). Use an interest rate of 9% for short-term debt (and for the interest income on short-term investments) and a rate of 11% for long-term debt. No interest is earned on cash. Use the beginning-of-year debt balances to calculate net interest expense. Assume dividends grow at an 8% rate. 

c. Now create a graph that shows the sensitivity of AFN to the sales growth rate. To make this graph, compare the AFN at sales growth rates of 5%, 10%, 15%,20%,25%, and 30%. 

d. Calculate net operating working capital (NOWC), total operating capital, NOPAT, and operating cash flow (OCF) for 2015 and 2016. Also, calculate the free cash flow (FCF) for 2016.

e. Suppose Cumberland can reduce its inventory-to-sales ratio to 5% and its cost-to-sales ratio to 83%. What happens to AFN and FCF?