# Project, Fin 321

### Question Description:

Project, Fin 321

For this project each student will select a company whose ticker symbol begins with the same letter as

their last name. You will be accessing performance data via the SEC website EDGAR. You can work in

groups of two for this project.

1. In a Word or PDF document describe the company you have been assigned including their ticker symbol,

fiscal year, major line(s) of business, history, financial projections for the future, corporate

insiders/executives, and any conglomerate, horizontal, or vertical affiliations currently existing. Make

sure to include a works cited page indicating the source of your information. Good sources include Yahoo

finance and EDGAR but you are free to use others as well. This question should be answered in no more

than 2 pages of narration.

2. Using the 10-K reports create an Excel spreadsheet (label 2 to indicate the sheet corresponds to question

2) with a column for Fiscal Years (i.e. 2015, 2014, etc.), Fiscal Year Sales, and Fiscal Year Net Income.

You will need this data to complete question 3.

3. Using the 10-Q reports create an Excel spreadsheet (label 3 to indicate the sheet corresponds to question

3) with a column for Quarters, (i.e. Q1-2015, Q2-2015, etc.), Quarter Number (40, 39, 38, etc.), Quarterly

Sales, and Quarterly Net Income. Complete a total of 40 quarters (10 years of data) with the last quarter

of 2015 representing quarter 40. Since the 10-Q reports are not released for the 4th quarter of each year

you will need to use the information from question 2 to derive 4th quarter data (e.g. if you find that for

fiscal year 2012 ABC Inc. made 1.5 million in net income and you calculate that the sum of quarters 1, 2,

& 3 net income equals 1.2 million, then it stands to reason that the 4th quarter net income must be 300K).

4. Using Excel’s scatterplot function, estimate two linear trend equations [similar to Y = α + β t], one for

Sales and one for Net Income (label 4 to indicate the sheet corresponds to question 4). Plot each time

series data (two separate graphs): X-axis = Quarters (lowest to highest); Y-Axis: Sales or Net Income;

show the trend line and regression metrics in each graph. Given the equations for sales and net income,

forecast Sales and Net Income for Q= 41 (Q1-2016).

5. Using Excel’s Data Analysis Regression function create a spreadsheet that provides the computergenerated statistical details (i.e., ANOVA: R2, t-Stat, SS, etc.) (label 5 to indicate the sheet corresponds to

question 5). The resulting coefficients should mirror those calculated in question 4 with the scatterplot. If

they don’t then you may have reversed your dependent and independent variables so you will need to

correct this before proceeding.

6. Create a new Excel spreadsheet (label 6 to indicate the sheet corresponds to question 6) and use each

trend equation from questions 4 or 5 (should be the same) to derive the predicted values for Q = 40, 39,

38, 37….1); in a table create a column with the actual values of sales and compare to the predicted values

based on the trend equation. In another adjacent table do the same for Net Income.

7. Using both Excel’s Scatter Plot and Data Analysis Regression functions, estimate the relationship

between Net Income and Sales by using a regression:

Net Income = α + β Sales (i.e., Y=α + β X).

Forecast Net Income for the 1st quarter of 2016 based on this equation (for X41 use the sales

trend forecast you obtained in part “4” above).

8. In the Word or PDF document you started in question 1, briefly summarize your results for each question

above. As for the regression metrics, you only need to explain R-Square, coefficients, t-stats, and the 95%

confidence interval for your results. Once completed upload one excel and one Word/PDF file into the

appropriate Blackboard portal. If you are working with a partner both of you will need to upload the same

document and indicate confirm who you are working with in the comments section.