Description
You will need to find the quarterly balance sheet, income statement, cash flow statements for both companies so that you can insert into the Excel.Wells Fargo & coJo Morgan chase $ cospreadsheet posted in the G2G module. In the document that was below you have to fill out the information
Unformatted Attachment Preview
Congatulations on achieving the first step towards mastering Financial Management by reinforcing your foundational knowledge
Algebra, Numerology, and Financial Accounting. You are now well equipped to master a company’s important Financial Manag
functions: evaluating its financial performance, comparing its performance over time (trend analysis) and in comparison (compa
analysis) to other firms in the industry.
Why is this important? Many times you are enticed to invest in a company’s stock because there is a lot of buzz on social media
barber/hairdresser told you this is a sure deal ased on an inside tip! You will learn through your Business program that it is critica
possess this deep understanding of Financial Management, supplement it with data on the economy, industry and other conside
that allow you to rationally improve future decision-making.
We want to make sure you understand the synergies with the impressive work you have already done on the review of Accounti
concepts. This project is designed to show you how to build on this work to make important Financial Management decisions. Y
also supplement this analysis with data visulaization techniques that Excel allows you to do.
Once you understand these vital steps you will be well on the road to controlling the important inputs to keep creating and addin
for your chosen company to improve its long term performance. You can also use this type of analysis to make important investm
decisions, in terms of investing in stocks and bonds for the long haul based on fundamental analysis. Good luck!
Please start this exercise by including the Financial Statement information that you have already gathered from the Accounting R
section into the next tab labelled “Input”. Then check the results in the tabs following beginning with Liquidity etc.
r foundational knowledge in
mportant Financial Management
nd in comparison (comparative
t of buzz on social media or your
ss program that it is critical to
ndustry and other considerations
on the review of Accounting
Management decisions. You can
keep creating and adding value
to make important investment
Good luck!
red from the Accounting Review
uidity etc.
[Company Name]
Ratio Analysis
Input Worksheet
[Date]
Gray cells will be calculated for you. You do not need to enter anything into them. When inputting the Q1 to Q4 data ensure that it is chronologically accurate with the end of Q4 being the same
Enter amounts into the yellow cells only.
Be sure to enter industry averages for the ratio calculations on the other pages of the spreadsheet by replacing every cell which contains red
font. Use your algebraic skills you have already mastered to calculate the deviation or the variance of your company’s ratio with the
industry/peer company’s avarage.
Line Item
Beginning
of Year
Inventory
Total assets
Owners’ equity
Number of common shares
Line Item
Current assets
Fixed assets
Other assets
Total assets
Average total assets
Cash and cash equivalents
Inventory
Average inventory
Current liabilities
Total liabilities
Owners’ equity
Number of common shares
Average number of common shares
Average owners’ equity
Market price per share
Operating Cash flow
Cash flow per share
Dividends paid
Total sales
Cost of sales
Other Operating expenses
Operating income
Earnings before interest and taxes
Interest expense
Net income
Q1
Q2
Q3
Q4
Annual
0
0
0
0
0
0
0
0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
0
0
0
0
$0
$0
$0
$0
$0
$0
$0
#DIV/0!
$0
$0
$0
0
#DIV/0!
#DIV/0!
$0.00
$0
#DIV/0!
$0
$0
$0
$0
$0
$0
$0
$0
[Company Name]
Ratio Analysis
Q1
Liquidity Ratios
Q2
Q3
Q4
Annual
Definition:
Current Ratio =
Industry Average
Variance
Quarterly Growth/Decline
Quick Ratio =
Industry Average
Variance
Quarterly Growth/Decline
Current Assets
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
Current Liabilities
$0
$0
$0
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Current Assets – Inventory
Current Liabilities
Current Liabilities to =
Inventory Ratio
Industry Average
Variance
Quarterly Growth/Decline
Cash Ratio =
Industry Average
Variance
Quarterly Growth/Decline
Operating Ratio =
Industry Average
Variance
Quarterly Growth/Decline
=
$0
$0
$0
#DIV/0!
=
$0
$0
$0
#DIV/0!
=
$0
$0
#DIV/0!
=
$0
$0
$0
#DIV/0!
=
$0
$0
$0
#DIV/0!
=
$0
$0
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Current Liabilities
Inventory
#DIV/0!
=
$0
$0
#DIV/0!
=
$0
$0
#DIV/0!
=
$0
$0
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
=
$0
$0
#DIV/0!
#DIV/0!
#DIV/0!
$0
#DIV/0!
#DIV/0!
Insert the Industry Average for this ratio here. If#DIV/0!
you are unable to get reliable data for the Industry
Average please access the data for your company’s
closest competitor
#DIV/0!
#DIV/0!
Net Working Capital = Current Assets – Current Liabilities
Ratio
Total Assets
Industry Average
Variance
Quarterly Growth/Decline
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
=
$0
$0
$0
=
$0
#DIV/0!
Operating Expenses
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
Operating Income
$0
$0
$0
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
=
$0
$0
$0
$0
$0
$0
$0
#DIV/0!
=
$0
$0
$0
#DIV/0!
=
$0
$0
=
$0
$0
=
$0
$0
#DIV/0!
=
$0
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
=
#DIV/0!
#DIV/0!
#DIV/0!
Cash and Cash Equivalents
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
Current Liabilities
$0
$0
$0
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
$0
$0
#DIV/0!
#DIV/0!
#DIV/0!
$0
=
#DIV/0!
#DIV/0!
=
$0
$0
#DIV/0!
#DIV/0!
[Company Name]
Ratio Analysis
Q1
Asset Ratios
Q2
Q3
Q4
Annual
Definition:
Inventory Turnover =
Ratio
Industry Average
Variance
Quarter Growth/Decline
Fixed Assets Turnover =
Ratio
Industry Average
Variance
Quarter Growth/Decline
Total Assets Ratio =
Industry Average
Variance
Quarter Growth/Decline
Asset to Equity Ratio =
Industry Average
Variance
Quarter Growth/Decline
Total Cost of Sales
Average Inventory
#DIV/0!
=
$0
#DIV/0!
#DIV/0!
=
$0
#DIV/0!
#DIV/0!
=
$0
#DIV/0!
#DIV/0!
=
$0
#DIV/0!
#DIV/0!
=
$0
#DIV/0!
=
$0
$0
Total Sales
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0!
Total Assets
$0
$0
$0
$0
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
=
$0
$0
Total Assets
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0!
Owners’ Equity
$0
$0
$0
$0
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
=
$0
$0
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Total Sales
Fixed Assets
#DIV/0!
=
$0
$0
#DIV/0!
=
$0
$0
#DIV/0!
=
$0
$0
#DIV/0!
=
$0
$0
#DIV/0!
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
[Company Name]
Ratio Analysis
Q1
Profitability Ratios
Q2
Q3
Q4
Annual
Definition:
Return on Assets Ratio =
Industry Average
Variance
Quarter Growth/Decline
Return on Equity Ratio =
Industry Average
Variance
Quarter Growth/Decline
Profit Margin Ratio =
Industry Average
Variance
Quarter Growth/Decline
Basic Earnings =
Power Ratio
Net Income
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
Average Total Assets
$0
$0
$0
$0
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Net Income
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
Average Owners’ Equity
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Net Income
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
Total Sales
$0
$0
$0
$0
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Earnings Before Interest and Taxes
Total Assets
#DIV/0!
=
$0
$0
#DIV/0!
=
$0
$0
#DIV/0!
=
$0
$0
#DIV/0!
=
$0
$0
Industry Average
Variance
Quarter Growth/Decline
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Earnings per Share Ratio =
Net Income
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
Average Number of Common Shares
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
2.00 the data for your company’s2.00
Insert the Industry Average for this ratio here. If you 2.00
are unable to get reliable data for2.00
the Industry Average please access
closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Industry Average
Variance
Quarter Growth/Decline
=
$0
$0
=
$0
#DIV/0!
=
$0
$0
=
$0
$0
=
$0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
2.00
#DIV/0!
[Company Name]
Ratio Analysis
Q1
Debt Ratios
Q2
Q3
Q4
Annual
Definition:
Total Debt Ratio =
Total Liabilities
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0!
Total Assets
$0
$0
$0
$0
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
=
$0
$0
Interest Coverage Ratio =
Earnings Before Interest and Taxes
Interest Expense
=
$0
$0
Industry Average
Variance
Quarter Growth/Decline
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
=
$0
$0
Industry Average
Variance
Quarter Growth/Decline
Debt/Equity Ratio =
Industry Average
Variance
Quarter Growth/Decline
#DIV/0!
=
$0
$0
#DIV/0!
=
$0
$0
#DIV/0!
=
$0
$0
#DIV/0!
=
$0
$0
#DIV/0!
Total Liabilities
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0!
Owners’ Equity
$0
$0
$0
$0
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
[Company Name]
Ratio Analysis
Q1
Market Ratios
Q2
Q3
Q4
Annual
Definition:
Earnings per Share =
Net Income
(EPS) Ratio
Average Number of Common Shares
#DIV/0!
=
$0
#DIV/0!
#DIV/0!
=
$0
#DIV/0!
#DIV/0!
=
$0
#DIV/0!
#DIV/0!
=
$0
#DIV/0!
#DIV/0!
Industry Average
Variance
Quarter Growth/Decline
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Price to Earnings Ratio =
Market Price per Share
#DIV/0! =
$0.00
#DIV/0! =
$0.00
#DIV/0! =
$0.00
#DIV/0! =
$0.00
#DIV/0!
Earnings per Share
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Industry Average
Variance
Quarter Growth/Decline
Price to Cash =
Flow Ratio
Industry Average
Variance
Quarter Growth/Decline
Payout Ratio =
Industry Average
Variance
Quarter Growth/Decline
Market Price per Share
Cash Flow per Share
#DIV/0!
=
$0.00
#DIV/0!
#DIV/0!
=
$0.00
#DIV/0!
#DIV/0!
=
$0.00
#DIV/0!
#DIV/0!
=
$0.00
#DIV/0!
#DIV/0!
=
$0
#DIV/0!
=
$0.00
#DIV/0!
=
$0.00
#DIV/0!
=
$0
$0
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Dividends Paid
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0! =
$0
#DIV/0!
Net Income
$0
$0
$0
$0
Insert the Industry Average for this ratio here. If you are unable to get reliable data for the Industry Average please access the data for your company’s closest competitor
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Congratulations! You have now completed the numerical part of the assignment. Next you need to answer a very important que
“What does this all mean for investment purposes?” To do this well you need to critically examine what you have been able to a
and reflect on what it all means for decision-making. Therefore the next step is completing the AT-EASE reflection statement.
wer a very important question,
you have been able to achieve
E reflection statement.
The AT-EASE Reflection Statement
Congratulations on completing the numerical part of your assignment. Over the last few weeks, you
have been engaged in a very important activity necessary for developing your skills as a business
student. As you progress through your major, we hope that you will see the relevance of your activities.
It will be especially useful when you work on the capstone course in your Business major, MAN 4720,
Strategic Management.
This type of assignment is necessary for you to determine whether you should:
a) Invest in a company by purchasing its stock
b) Invest in a company by purchasing its bonds
c) As a banker, provide a loan/line of credit to a company.
Please use these headings (AT-EASE) to guide your reflection statement. Answer the questions directly
in each box on the next page. Save the page as a PDF file and submit it in the Assignment Dropbox
folder. We expect that you will provide your own thoughts which should be based on your own goals
and interpretation of the data that you have analyzed.
You can now add “Financial Detective” to your list of accomplishments!
1
NAME____________________________________PID____________________________
Answer the following question: What investment decision will you be making based on your analysis?
(Hint: a, b, and/or c from above)
Accessing
What research did you perform to determine the necessary data for successful
completion of this assignment? What resources did you access from your class?
What resources did you access from publicly available websites? What other
resources did you access to clarify the ideas/techniques that are confusing to you
and what you identified as necessary to accomplish your goals?
Trying
List two actions that you performed after you accessed the above resources. What
steps could you have taken to clarify the ideas/techniques that are confusing to
you?
Exploring
How did you implement your assignment effectively and efficiently? How did you
determine what kind of analysis was necessary to answer the above question and
make investment decisions This statement should include how you made the
decisions on what data was relevant for making an appropriate investment
decision. Did your techniques evolve or change over the course of your weekly
journey? If yes, why?
Accessing
What course concepts/skills are you now more comfortable with based on this
learning experience? What course outcomes/skills are still confusing/ unclear to
you at the end of the project?
Seeking
How can you use what you learned in the rest of your academic career? How can
you use what you learned for life’s challenges? Write two questions that would
help clarify the ideas/techniques that are still unclear to you.
Exploring
What did you actually learn about the course content, skills you personally
developed and about some of the issues facing the community? Give examples of
how you integrated new learning and familiar ideas and skills to develop new
ways of engaging learning. Give specific examples of when you learned these
things. Why does this learning matter – how does this relate to your course, major,
degree, personal goals, career objectives etc?)? Complete the statement: “In light
of this learning activity I will……………….”
(Be sure to make specific action oriented decisions).
2
The Decision Statement
NAME____________________________________PID____________________________
Dear Financial Detective,
Based on your Financial Ratio analysis of your chosen stock you are now in a position to make an
informed decision and not have to rely on questionable advice from others. Please answer the following
questions and provide your rationale. Your rationale should draw from Parts 1 and 2 of your
Assignment. For example, for each ratio you calculated consider if the company is improving (over the
four quarters) or is performing better than its competitors. This analysis should help in your decisionmaking.
a) Will you recommend a Buy/Hold/Sell rating on the publicly traded stock of this company? Why?
b) Will you purchase this company’s publicly listed bonds? Why?
What criteria did you use from your ratio analysis?
c) As a banker, will you provide a loan/line of credit to this company? Why?
What criteria did you use from your ratio analysis?
Investment bankers, financial analysts and many others who work in corporate finance make these kinds
of decisions every day and are well compensated when they make well informed decisions based on
good arguments. The best ones create their own models using Financial Analytics and other Augmented
Intelligence tools. This exercise provides a good introduction to this activity. We hope you enjoyed the
experience!
Sincerely,
Your Professor
Purchase answer to see full
attachment
