Assignment: Analyzing firm fundamentals and providing investment insights
INTRODUCTION
This project provides an opportunity for students of Intermediate Accounting to utilize financial accounting information in a simulation of a real-world setting. In addition, this practice serves to alert students to the foundational skills that they would need for future coursework in intermediate accounting, as well as familiarize students with the usage of spreadsheet software. This practice set involves the analysis of fundamental financial accounting information in a setting that is more realistic than typical textbook practice sets. In addition, it also goes beyond the introductory level of principles of accounting by having students study the resultant financial data and provide investment strategies for their firm.
BACKGROUND
You have just started your career in a local bank's investment department. The department is new and does not have much experience participating in the stock markets. Your manager has assigned you do conduct some financial analysis regarding the behavior of stock prices on the stock market and provide recommendations as to which stocks to invest.
Your firm is interested in purchasing some stock with the intention of holding for 1 year and selling them at the end of the year, thus benefiting from capital gains. You are given the task of analyzing the relationship between the earnings of a firm and its stock price to provide insight to help the department decide on which firms to invest in. Your manager has provided you with the stock price and earnings information for a cross-section of firms in the market this year for use in the analysis (Appendix A). Each firm is assigned a firm unique firm number. Use this firm number when recommending firms for investment.
INSTRUCTIONS
As part of this assignment, you will need to utilize some simple formulas in excel to calculate the required variables. Some excel guidance is provided in the appendices. You should look up tutorials if you run into Excel problems, or are unfamiliar with basic Excel functions.
You may work in groups to process the data and help one another if you encounter any difficulties. However, each student is responsible for turning in the final memo individually.
You are required to submit a soft-copy of the report as well as the excel file used in the report as described in point 8 of the requirements section below by the assignment deadline.
REQUIREMENTS
1. Utilize excel to calculate the following for each firm:
a. Permanent Earnings ratio (Perm EPS / Total EPS)
2. Find the relationship between the various variables and stock price by plotting stock price against these variables in excel and using a straight line fit. Enable excel to display the equation of the best-fit line.
a. Stock price (Y axis) vs Earnings per share (EPS) (X axis)
3. Comment on the relationships above
a. Is it a positive or negative relationship, or no relationship?
b. How does stock price change for a unit change in EPS?
4. Using excel, split the data up into two separate datasets - 1) firms with permanent earnings ratios that are higher than the median for the entire 100 firm sample (high permanent earnings firms) and 2) firms permanent earnings ratios that are lower than the median for the entire 100 firm sample (low permanent earnings firms). Utilize the =IF() function in excel. Assign the firm to be in the high median group if the firm's permanent earnings ratio is greater than (>) the median.
5. Utilize excel to plot the Stock price (Y axis) vs. EPS (X axis) for both samples. Enable excel to display the equation for the best-fit line.
a. Comment on the whether and how the relationship between stock price and EPS differs between high permanent earnings firms and low permanent earnings firms.
b. Comment on why there may be such a difference in the relationships between the stock prices of high permanent earnings firms and low permanent earnings firms
6. Using the Projected next year Earnings, and the gradients for the best fit line equations, calculate the projected stock prices for high permanent earnings firms and low permanent earnings firms (use the 2 separate price-to-earnings-ratios). Subsequently:
a. Calculate the Projected returns (for buying and holding the stock for one year) for each stock.
b. Calculate the Projected Return on Investment (ROI) for investing in each high permanent earnings and each low permanent earnings firm
7. Based on Projected ROI or Projected Returns, recommend the top 10 most profitable firms for investment. Be sure to analyze both high permanent earnings firms and low permanent earnings firms.
8. Prepare a memo detailing all your findings (points 1 to 7) for presentation to management. Be sure to explain the relationships using plain English, and include the best fit graphs as tools to supplement your written explanations. You may use simple equations to illustrate your explanations. Include an appendix where you provide all the previously calculated variables for these 10 recommended firms. Limit your memo to 4 double-spaced pages. In your memo, be sure to include the following graded items:
a. Comment on the general Price-to-earnings ratio for full sample. Accuracy of the ratio will be considered.
b. Indicate how the Price-to-earnings ratio differs for the above median permanent earnings ratio group vs the below median permanent earnings ratio group. Explain why the price-to-earnings ratio of the two subsamples is better for use for projections instead of the full sample (hint: R2).
c. Explain why you selected the 10 firm that you selected (Hint: either projected highest ROI or highest projected return)
d. Explain how you caculated at the projected stock price, and your metric of choice (for c)
e. Provide the projected returns / ROI for your recommendations.
f. Submit before the deadline.
Definitions:
1. Total EPS = Permanent EPS + transitory EPS
2. Earnings per share (EPS) = total earnings / number of outstanding shares
3. Price-to-earnings ratio = Stock price / EPS
4. Permanent earnings ratio = Permanent EPS / total EPS
5. Projected Returns = Projected Stock Price - Stock Price
6. Projected ROI = Projected Returns / Stock Price
Appendices:
Appendix A - Excel spreadsheet
Data file containing information about firms
Appendix B - Pdf file
Excel guidance: How to make a straight line fit (regression line) using Excel
Appendix C - Word document
Contains basic information about some relevant Excel functions.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.
Attachment:- Project-Instruction.rar