Start with the partial model in the file Ch12 P11 Build a Model.xls on the textbook’s Web site, which shows Matthews Industries’s most recent balance sheet, income statement, and other data. Matthews Industries’s financial planners must forecast the company’s financial results for the coming year. The forecast will be based on the forecasted financial statement method, and any additional funds needed will be obtained by using notes payable. Complete the partial model and answer the following questions.

a. Assume that the firm’s 2010 profit margin, payout ratio, capital intensity ratio, and spontaneous liabilities-to-sales ratio remain constant. If sales grow by 10% in 2011, what is the required external capital the firm will need in 2011 as calculated by the AFN equation?
b. If 2010 ratios remain constant, what is Matthews’s self-supporting growth rate? Describe how the self-supporting growth rate will change in response to each of the following: (1) the profit margin declines, (2) the payout ratio increases; and (3) the capital intensity ratio declines.
c. Matthews’s management has reviewed its financial statements and arrived at two possible scenarios for 2011. The first scenario assumes a steady state while the second scenario, the target scenario, shows some improvement in ratios toward industry average values. Forecasted values for the scenarios are shown in the partially completed file Ch12 P11 Build a Model.xls. If Matthews assumes that external financing is achieved through notes payable and that financing feedbacks are not considered because the new notes payable are added at the end of the year, then what are the firm’s forecasted AFN, EPS, DPS, and year-end stock price under each scenario?
d. Matthews’s management realizes that interest for additional notes payable should be included in the analysis. Assume that notes will be issued midway through the year, so that interest on these notes is incurred for only half the year. If Matthews assumes now that external financing is achieved through notes payable and if financing feedbacks are considered, then what are the firm’s forecasted AFN, EPS, DPS, and year-end stock price under each scenario?