Assignment #2: Forecasting the Income Statement Submit this assignment electronically to the instructor by the due date Income Statement Using Excel, create an earnings forecast for the three years. Use data from www.sec.gov or company earnings releases. Stockval data is not complete and is not acceptable. Key in 5 years of historical data (see 10-K’s). Calculate all expense items as a % of sales Project the entire income statement out for the upcoming 3 years. Forecast EPS for the upcoming 3 years. o Check your sales and EPS estimates vs. consensus estimates. Please place consensus sales and EPS estimates in the spreadsheet. Balance Sheet Calculate inventory as a % of sales for the past 5 years. How has your company done managing inventory? Using your best estimates, forecast inventory for the next 3 years. Calculate accounts receivable as a % of sales for the past 5 years. Using your best estimates, forecast accounts receivable for the next 3 years. Calculate accounts payable as a % of sales for the past 5 years. Using your best estimates, forecast accounts payable for the next 3 years. Does anything about the balance sheet catch your eye (positive or negative)? Cash Flow Using Excel, create a cash flow statement using the past 5 years of historical data. Do you see any cash flow concerns? Calculate Capital Expenditures (Prop, Plant & Equip) as a % of sales. Using your best estimates, forecast capex for the next 3 years. Calculate Depreciation & Amortization as a % of sales. Using your best estimates, forecast D&A for the next 3 years. From the Reading… What are the eight common DCF Errors?
Assignment #2
1