Scribd
Upload
8 views2 pages

Income Statement Forecasting Assignment

The document outlines an assignment to forecast financial statements for a company over the next three years. Students are instructed to obtain historical income statement, balance sheet, and cash flow data from SEC filings or earnings releases for the past five years. They must then calculate key line items as percentages of sales and use these to project the income statement, balance sheet, and cash flow statement items for the next three years. Common mistakes made on this type of assignment are also outlined.

Uploaded by

zordan rizvy
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
8 views2 pages

Income Statement Forecasting Assignment

The document outlines an assignment to forecast financial statements for a company over the next three years. Students are instructed to obtain historical income statement, balance sheet, and cash flow data from SEC filings or earnings releases for the past five years. They must then calculate key line items as percentages of sales and use these to project the income statement, balance sheet, and cash flow statement items for the next three years. Common mistakes made on this type of assignment are also outlined.

Uploaded by

zordan rizvy
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd

Assignment #3: 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 [Link] or
company earnings releases. Bloomberg/Capital IQ data is not complete and is not acceptable.
Use actual data reported by the company
Using 10-K data, key in 5 years of annual (my examples are quarterly) historical data.
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.
o 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.
Calculate change to Working Capital
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.

Assignment #3 4/10/14 1
Common Mistakes when working on this assignment:

Formulas should not be hard coded. This means that someone using the model should be
able to easily modify assumptions (e.g. gross margin, sales growth, etc) without going
into an individual cell and modifying the formula.
Tax Rate: The tax rate should be calculated by taking income tax provision divided by
income before taxes. DO NOT CALCULATE TAX RATE AS A % of SALES.
Forecast your sales and compare to consensus. Your sales forecast should be within a
couple of percentage points of consensus.
Compare your EPS to consensus. Your EPS forecast should be somewhat similar to
consensus.
Use diluted share count to calculate EPS.
If the company gives detailed segment sales data, use the segment data to forecast sales.
If the company gives detailed segment operating income information, forecast operating
income at the segment level.
If the company does NOT give detailed segment information, all line items on the income
statement must be forecast (i.e. dont skip from sales to operating incomeall expense
items must be forecast).
The Change in Working Capital calculation is often incorrect. I will use years 2007 and
2008 as an example. Changes to working capital will likely result in a negative number.
Working capital calculation:

(2007 Inv 2008 Inv) + (2007 A/R 2008 A/R) + (2008 A/P-2007A/P)

Notice that for Inv and A/R we take 2007-2008 but for A/P, take 2008-2007.

Assignment #3 4/10/14 2

You might also like