Project, Fin 321


Question Description:

250

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.

Answer

250