I need help with Data analysis accounting, please check files
project instructions will give you guidance
review file have professor note for my work done to this point
excel sheet have the data need to be used for homework
Students as Financial Analysts:
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. (3 pts)
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 (3 pts). 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) (3pts)
d. Explain how you caculated at the projected stock price, and your metric of choice (for c) (3pts)
e. Provide the projected returns / ROI for your recommendations. (3 pts)
f. Submit before the deadline (25 pts).
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
https://dataprivacylab.org/courses/popd/lab2/ExcelLine.pdf
Appendix C – Word document
Contains basic information about some relevant Excel functions.
They have a magnificent team. These people are always kind and willing to listen to your concerns or issues. Better yet, your assignment is always ready before the time, they usually send you a draft to double-check before they finalize your paper.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.
Read moreEach paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.
Read moreThanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.
Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.
Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.
Read more