Fin 321 For this assignment 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 only one of you will need to upload the document and indicate who you are working with in the comments section.
Hi there! Click one of our representatives below and we will get back to you as soon as possible.