Description
For this milestone, submit a draft of the Time Value of Money section of the final project, along with your supporting explanations. Base your calculations on the data provided in the Final Project Excel Workbook, using your selected company’s financial data. Be sure to substantiate your claims with references and citations in proper APA style.Submit your calculations on the designated tab of the Final Project Excel Workbook and your supporting explanations as a Microsoft Word document. In your written analysis, be sure to substantiate your claims by using appropriate References and citations using proper APA style where required. This milestone will be used in your final project submission in Module Nine. Be sure to carefully review your instructor’s feedback and make any recommended changes before final submission in Module Nine. For additional details, please refer to the Final Project Guidelines and Rubric document and the Milestone One Guidelines and Rubric document.FIN 550 Final Project Guidelines and Rubric
Overview
Financial analysis involves examining historical data to gain information about the current and future financial health of a company. Financial analysis can be
applied in a wide variety of situations to give business managers the information they need to make critical decisions. The ability to understand financial data is
essential for any business manager.
The final project for this course is the creation of a financial analysis report. For this assessment, you will provide a financial analysis report for your selected
company. You will be asked to take the topics that you have covered throughout the course and display your mathematical and conceptual mastery of them. You
will conduct background calculations and provide managerial analysis for the following topics: time value of money, stock valuation, bond valuation, and capital
budgeting.
The project is divided into four milestones, which will be submitted at various points throughout the course to scaffold learning and ensure quality final
submissions. These milestones will be submitted in Modules Two, Four, Six, and Seven. The final project submission will be in Module Nine.
In this assignment, you will demonstrate your mastery of the following course outcomes:
•
•
•
•
•
Predict the effects of the time value of money on potential investments for ensuring an effective portfolio balance between risk and return
Assess the stock valuation process as a viable financing and investment option for maximizing shareholder value
Assess the bond issuance process for its viability as a financing option for raising adequate capital
Forecast the feasibility of corporate investment opportunities by utilizing capital budgeting estimates for ensuring effective decision making
Analyze macroeconomic variables that impact corporate financial decision making for ensuring alignment with strategic objectives
Prompt
Using the financial information you prepared for the company you selected from the first tab of the Final Project Excel Workbook, prepare a financial analysis
report. For your calculations, use the Final Project Excel Workbook which includes tabs specific to each milestone (the workbook is linked to in your course). Be
sure to include in your analysis the background calculations and managerial analysis for each of the following topics: time value of money, stock and bond
valuation, and capital budgeting. Also, include a discussion of macroeconomic variables that might impact the company’s financial decision making and strategic
objectives. Note that while these elements may seem separate and unrelated, together they will present a well-rounded view of the company’s financial position
with regard to the topics.
Specifically, you must address the critical elements listed below. Most of the critical elements align with a particular course outcome (shown in brackets).
I.
Time Value of Money
A. Using the Final Project Excel Workbook, calculate the following time value of money figures:
1. Calculate the present value of your selected company based on the given interest rate and free cash flows for the most current three (3)
fiscal or calendar year end values you entered into your selected company in the Final Project Excel Workbook.
2. Suppose the risk of your selected company changes based on an unanticipated decrease in the free cash flows by 10% annually during
the most current three (3) fiscal or calendar year end values you entered into the Final Project Excel Workbook. Recalculate the present
value of the company.
3. Suppose that a potential buyer has offered to buy this company in three years. Your selected company has projected that the free cash
flows will increase by 3% annually over the next three years. Based on the initial present value you calculated above in A1, and the
projected free cash flow increases mentioned above, what would be a reasonable amount for the potential buyer to pay for the
company? Note that the potential buyer has a required rate of return (discount rate) of 9% to account for the uncertainty that your
selected company’s potential 3% annual increases in free cash flows over the next three years does not occur. Enter your answer and
justify your reasoning in the Word document. In your written analysis, be sure to substantiate your claims by using appropriate
references and citations using proper APA style where required.
B. What are the implications of the change in present value based on risk? In other words, what does the change mean to your selected company,
and how would you, as a financial manager, interpret it? Enter your answer and justify your reasoning in the Word document. In your written
analysis, be sure to substantiate your claims by using appropriate references and citations using proper APA style where required.
C. Based on the present value of your selected company that you calculated, and being mindful of the need to effectively balance portfolio risk with
return, what recommendation would you make about purchasing the company as an investment at that price? Enter your answer and justify
your reasoning in the Word document. In your written analysis, be sure to substantiate your claims by using appropriate references and citations
using proper APA style where required.
II.
Stock Valuation
A. Based on the figures provided from the financial information you prepared for your selected company, calculate each of the following:
1. Fill in the yellow highlighted cells with the matching data prepared for your selected company.
2. The new dividend yields if the company increased its dividend per share by $1.75
3. The dividend yield if the firm doubled its outstanding shares using a stock split. Note that other numbers will be affected by a stock split
such as new stock price, actual dividends paid, and number of outstanding shares. Stockholders’ equity will not change.
4. Return on Investment—Calculate the rate of return on investment based on the data you calculated in Question 1.
B. What effect would you expect each of the calculations you performed to have in terms of shareholder value? In other words, suppose the
company’s goal is to maximize shareholder value. How will each of the situations support or inhibit that goal? Be sure to justify your reasoning
through research, references, and citations using proper APA style.
C. To what extent do you feel the company’s dividend policies support or hinder their strategies? For example, if the company is attempting to
grow, are they retaining and reinvesting their earnings rather than distributing them to investors through dividends? Be sure to substantiate your
claims through research, references, and citations using proper APA style.
III.
Bond Issuance
A. Your selected company issues a new bond For this exercise, certain assumptions are being made. Assume that your selected company issued a
new 10-year bond for $300,000 on October 1, 2021, that will mature on October 1, 2031. The future value of this bond is therefore $300,000.
The bond was issued at the current market rate of 5.0% fixed for 10 years, with interest payments paid semi-annually. What is the present value
of this bond using the three scenarios in Part II: Bond Issuance?
1. The present value of the bond at issuance
2. The new present value of the bond if overall rates in the market increased by 2%
3. The new present value of the bond if overall rates in the market decreased by 2%
4. The present value of the bond if overall rates in the market remains the same as at issuance
B. What effect would you expect each of the calculations you performed to have in terms of the company’s decision to raise capital in this manner?
In other words, for each situation, would you consider bond valuation to be a viable option for increasing capital? Be sure to justify your
reasoning through research, references, and citations using proper APA style.
C. To what extent do you feel the company’s bond issuance policies support or hinder their strategies? For example, if the company is attempting
to fund operating expenses, refinance old debt, or change its capital structure, are they issuing sufficient bonds to achieve these goals? Be sure
to substantiate your claims through research, references, and citations using proper APA style.
IV.
Capital Budgeting Data
A. Suppose that your selected company is considering a potential investment project to add to its portfolio. Choose one of the three (3) possible
investments shown in the Milestone Three Excel workbook and calculate the following items:
1. The net present value (NPV) of the project (Accept or Reject from the drop-down box)
2. The internal rate of return (IRR) of the project (Accept or Reject from the drop-down box)
B. What are the implications of these calculations? In other words, based on each of the calculations, and being mindful of the need to balance
portfolio risk with return, would you recommend that the company pursue the investment? Why or why not? Be sure to substantiate your
claims.
C. What is the difference between NPV and IRR? Which one would you choose for evaluating a potential investment and why? Be sure to support
your reasoning with evidence.
V.
Macroeconomic Items: The CEO of your selected company is convinced that financial analysis should hinge only on what is happening internally within
the company. Convince him otherwise based on the following:
A. Analyze the implications of interest rate changes on any of the calculations you performed. Be sure to substantiate your claims.
B. Explain how an issue (negative or positive) within the overall stock market might impact the company’s stock valuation numbers, other financial
variables, or its overall portfolio management. Be sure your response is supported by evidence through research, references, and citations using
proper APA style.
C. Analyze the impact of any external factor (i.e., external to the company) discussed throughout the course on the company’s financial position.
Be sure to justify your reasoning through research, references, and citations using proper APA style.
Milestones
Milestone One: Time Value of Money (Section I)
In Module Two, you will submit a draft of the Time Value of Money section of the final project, along with your supporting explanations. Submit your
calculations on the designated tab of the Final Project Student Workbook and your supporting explanations as a Microsoft Word document. This milestone will
be graded with the Milestone One Rubric.
Milestone Two: Stock Valuation and Bond Issuance (Sections II and III)
In Module Four, you will submit a draft of the Stock Valuation and Bond Issuance sections of the final project, along with your supporting explanations. Submit
your calculations on the designated tab of the Final Project Student Workbook and your supporting explanations as a Microsoft Word document. This milestone
will be graded with the Milestone Two Rubric.
Milestone Three: Capital Budgeting Data (Section IV)
In Module Six, you will submit a draft of the Capital Budgeting Data section of the final project, along with your supporting explanations. Submit your
calculations on the designated tab of the Final Project Student Workbook and your supporting explanations as a Microsoft Word document. This milestone will
be graded with the Milestone Three Rubric.
Milestone Four: Macroeconomic Items (Section V)
In Module Seven, you will submit a draft of the Macroeconomic Items section of the final project, along with your supporting explanations. Submit your
calculations on the designated tab of the Final Project Student Workbook and your supporting explanations as a Microsoft Word document. This milestone will
be graded with the Milestone Four Rubric.
Final Project Submission: Financial Analysis Report
In Module Nine, you will submit your financial analysis report along with your completed Final Project Student Workbook. It should be a complete, polished
artifact containing all of the critical elements of the final project. It should reflect the incorporation of feedback from your instructor provided throughout the
course. This submission will be graded with the Final Project Rubric.
Deliverables
Milestone
Deliverable
Module Due
Grading
One
Time Value of Money (Section I)
Two
Graded separately; Milestone One Rubric
Two
Stock Valuation and Bond Issuance (Sections II and III)
Four
Graded separately; Milestone Two Rubric
Three
Capital Budgeting Data (Section IV)
Six
Graded separately; Milestone Three Rubric
Milestone
Four
Deliverable
Module Due
Grading
Macroeconomic Items (Section V)
Seven
Graded separately; Milestone Four Rubric
Final Project Submission: Financial Analysis Report
Nine
Graded separately; Final Project Rubric
Final Project Rubric
Guidelines for Submission: Your financial analysis report should be 7 to 12 pages, not including a title page and references page. It should use 12-point Times New
Roman font, double spacing, and one-inch margins. All citations and references should be formatted according to APA style. Also submit your completed Final
Project Student Workbook.
Critical Elements
Time Value of Money:
Figures
[FIN-550-01]
Time Value of Money:
Implications
[FIN-550-01]
Time Value of Money:
Value
[FIN-550-01]
Stock Valuation:
Calculations
[FIN-550-02]
Stock Valuation:
Shareholder Value
[FIN-550-02]
Stock Valuation:
Dividend Policies
[FIN-550-02]
Exemplary
Proficient
Accurately calculates requested
figures (100%)
Needs Improvement
Calculates figures, but with gaps
in accuracy or detail (70%)
Not Evident
Does not calculate figures (0%)
Value
6.33
Meets “Proficient” criteria and
demonstrates keen insight into
the interrelationship between
risk and present value (100%)
Meets “Proficient” criteria and
demonstrates keen insight into
using time value of money for
recommending investments
(100%)
Analyzes implications of change
in present value based on risk,
justifying reasoning (90%)
Analyzes implications of change
in present value based on risk,
but response or reasoning is
cursory or illogical (70%)
Makes recommendation about
purchasing company, but
response or substantiation is
cursory or illogical (70%)
Does not analyze implications
of change in present value
based on risk (0%)
6.33
Does not make
recommendation about
purchasing company
(0%)
6.33
Accurately calculates requested
figures (100%)
Calculates figures, but with gaps
in accuracy or detail (70%)
Does not calculate figures (0%)
6.33
Analyzes the effects of each
calculation on shareholder
value, justifying reasoning (90%)
Analyzes the effects of each
calculation on shareholder
value, but response or reasoning
is cursory or illogical (70%)
Does not analyze the effects of
each calculation on
shareholder value (0%)
6.33
Assesses the extent to which
dividend policies support or
hinder company strategies,
justifying reasoning (90%)
Assesses the extent to which
dividend policies support or
hinder company strategies, but
response or reasoning is cursory
or illogical (70%)
Does not assess the extent to
which dividend policies
support or hinder company
strategies (0%)
6.33
Meets “Proficient” criteria and
demonstrates keen insight into
the effects of changing financial
variables on shareholder value
(100%)
Meets “Proficient” criteria and
demonstrates keen insight into
the relationship between
dividend policies and strategies
for increasing shareholder value
(100%)
Makes recommendation about
purchasing the company,
substantiating claims (90%)
Critical Elements
Bond Issuance: Bonds
[FIN-550-03]
Bond Issuance: Raising
Capital
[FIN-550-03]
Bond Issuance: Bond
Issuance Policies
[FIN-550-03]
Capital Budgeting
Data: Potential
Investment
[FIN-550-04]
Capital Budgeting
Data: Pursuing the
Investment
[FIN-550-04]
Exemplary
Meets “Proficient” criteria and
demonstrates keen insight into
the effects of changing market
conditions on decisions to raise
capital (100%)
Meets “Proficient” criteria and
demonstrates keen insight into
the relationship between bond
issuance policies and strategies
for raising capital (100%)
Proficient
Accurately calculates requested
figures (100%)
Analyzes the effects of each
calculation on the company’s
decision to raise capital,
justifying reasoning (90%)
Assesses the extent to which
bond issuance policies support
or hinder company strategies,
justifying reasoning (90%)
Accurately calculates requested
figures (100%)
Meets “Proficient” criteria and
demonstrates keen insight into
using NPV and IRR to judge
potential investment
opportunities (100%)
Analyzes the implications of
each calculation on the
recommendation to pursue the
investment, substantiating
claims (90%)
Capital Budgeting
Data: Difference
[FIN-550-04]
Meets “Proficient” criteria and
demonstrates keen insight into
using NPV and IRR to judge
potential investment
opportunities (100%)
Accurately characterizes the
difference between NPV and IRR
and explains which would be
chosen for evaluating a potential
investment and why, supporting
reasoning with evidence (90%)
Macroeconomic Items:
Implications
[FIN-550-05]
Meets “Proficient” criteria and
demonstrates keen insight into
the relationship between
interest rate changes and
financial variables in a company
(100%)
Analyzes implications of interest
rate changes, substantiating
claims (90%)
Needs Improvement
Calculates figures, but with gaps
in accuracy or detail (70%)
Analyzes the effects of each
calculation on the company’s
decision to raise capital, but
response or reasoning is cursory
or illogical (70%)
Assesses the extent to which
bond issuance policies support
or hinder company strategies,
but response or reasoning is
cursory or illogical (70%)
Calculates figures, but with gaps
in accuracy or detail (70%)
Not Evident
Does not calculate figures (0%)
Value
6.33
Does not analyze the effects of
each calculation on the
company’s decision to raise
capital (0%)
6.33
Does not assess the extent to
which bond issuance policies
support or hinder company
strategies (0%)
6.33
Does not calculate figures (0%)
6.33
Analyzes the implications of
each calculation on the
recommendation to pursue the
investment, but response or
substantiation is cursory or
illogical (70%)
Characterizes the difference
between NPV and IRR and
explains which would be chosen
for evaluating a potential
investment and why, but
response is cursory or inaccurate
or evidence is not supportive
(70%)
Analyzes implications of interest
rate changes, but response or
substantiation is cursory or
illogical (70%)
Does not analyze the
implications of each calculation
on the recommendation to
pursue the investment (0%)
6.33
Does not characterize the
difference between NPV and
IRR and does not explain which
would be chosen for evaluating
a potential investment and
why (0%)
6.33
Does not analyze implications
of interest rate changes (0%)
6.33
Critical Elements
Macroeconomic Items:
Stock Market
[FIN-550-05]
Exemplary
Meets “Proficient” criteria and
demonstrates keen insight into
the relationship between stock
market fluctuations and
financial variables in a company
(100%)
Proficient
Assesses the impact of an issue
within the overall stock market
on the company’s stock
valuation numbers or any other
financial variable, supporting
response with evidence (90%)
Macroeconomic Items:
External Factor
[FIN-550-05]
Meets “Proficient” criteria and
demonstrates keen insight into
the relationship between
external factors and a
company’s financial position
(100%)
Submission is free of errors
related to citations, grammar,
spelling, syntax, and
organization and is presented in
a professional and easy to read
format (100%)
Analyzes the impact of a factor
external to the company on the
company’s financial position,
justifying reasoning (90%)
Articulation of
Response
Submission has no major errors
related to citations, grammar,
spelling, syntax, or organization
(90%)
Needs Improvement
Assesses the impact of an issue
within the overall stock market
on the company’s stock
valuation numbers or any other
financial variable, but response
is cursory, illogical, or weakly
supported (70%)
Analyzes the impact of a factor
external to the company on the
company’s financial position, but
response is cursory, illogical, or
weakly supported (70%)
Not Evident
Does not assess the impact of
an issue within the overall
stock market on the company’s
stock valuation numbers or any
other financial variable (0%)
Value
6.33
Does not analyze the impact of
a factor external to the
company on the company’s
financial position (0%)
6.33
Submission has major errors
related to citations, grammar,
spelling, syntax, or organization
that negatively impact
readability and articulation of
main ideas (70%)
Submission has critical errors
related to citations, grammar,
spelling, syntax, or
organization that prevent
understanding of ideas (0%)
5.05
Total
100%
**CHOOSE ONE (1) OF THE COMPANIES LISTED BELOW FOR YOUR COURSE FINA
1. Insert your selected company name in the yellow highlighted cell:
2. Start your copy with the row where your company name appears.
3. Then scroll down to the row of the weighted average cost of capital. Then paste this information into rows 6 through 20 in
Lowe’s Companies Inc. (Stock Ticker: LOW) – (NAICS Code: 444110)
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 28%)
Weighted Average Cost of Capital (Use 5%)
Year
$
$
$
$
$
$
#DIV/0!
–
$
%
%
Johnson & Johnson (Stock Ticker: JNJ) – (NAICS Code: 325412)
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Year
$
–
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 28%)
Weighted Average Cost of Capital (Use 5%)
$
$
$
–
$
$
#DIV/0!
–
$
%
%
Target: (Stock Ticker: TGT) – (NAICS: Code 452210)
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 28%)
Weighted Average Cost of Capital (Use 5%)
Year
$
$
$
$
$
$
#VALUE!
–
$
%
%
Kroger Companies: (Stock Ticker: KR) – (NAICS Code: 445110)
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 28%)
Weighted Average Cost of Capital (Use 5%)
Year
$
$
$
$
#VALUE!
–
$
$
$
%
%
Chevron: (Stock Ticker: CVX) – (NAICS Code: 324110)
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Year
$
$
$
$
–
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 28%)
Weighted Average Cost of Capital (Use 5%)
$
$
#DIV/0!
–
$
%
%
Pepsico, Inc.: (Stock Ticker: PEP) – (NAICS Code: 312111)
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 28%)
Weighted Average Cost of Capital (Use 5%)
Year
$
$
$
$
$
$
#VALUE!
–
$
Southwest Airlines Co.: (Stock Ticker: LUV) (NAICS Code: 481111)
%
%
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 28%)
Weighted Average Cost of Capital (Use 5%)
Year
$
$
$
$
#VALUE!
–
$
$
$
%
%
General Electric Company: (Stock Ticker: GE) – (NAICS Code: 333611)
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Year
$
$
$
$
$
$
#VALUE!
–
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 28%)
Weighted Average Cost of Capital (Use 5%)
$
Notes: North American Industry Classification System Codes:
The North American Industry Calculation System or NAICS is a
classification of business establishments by type of economic activity (process of production).
%
%
R YOUR COURSE FINAL PROJECT**
Then, copy and paste the information for your selected company below as follows:
ion into rows 6 through 20 into Tab 2—Selected Co. for Project
Year
$
$
$
$
Year
$
$
$
$
$
$
#VALUE!
$
$
$
$
#VALUE!
%
%
%
%
Year
$
Year
–
$
–
$
$
$
–
$
$
$
–
$
$
#DIV/0!
$
$
#DIV/0!
–
$
–
–
$
%
%
%
%
Year
$
$
$
$
Year
$
$
$
$
$
$
#VALUE!
$
$
$
$
%
%
#VALUE!
%
%
Year
$
$
$
$
Year
$
$
$
$
$
$
#VALUE!
$
$
$
$
#VALUE!
%
%
%
%
Year
$
$
$
$
Year
–
$
$
$
$
–
#VALUE!
#VALUE!
$
$
$
$
$
$
%
%
%
%
Year
$
$
$
$
Year
$
$
$
$
$
$
#VALUE!
$
$
$
$
%
%
#VALUE!
%
%
Year
$
$
$
$
Year
$
$
$
$
$
$
#VALUE!
$
$
$
$
#VALUE!
%
%
Year
$
$
$
$
$
$
%
%
Year
$
$
$
$
#VALUE!
$
$
#VALUE!
$
$
%
%
%
selected company below as follows:
Information Location
Mergent INTELLECT
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Mergent INTELLECT
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Information Location
Mergent INTELLECT
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Mergent INTELLECT
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Information Location
Mergent INTELLECT
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Formulated Cell (Do Not Change)
Mergent INTELLECT
Mergent INTELLECT
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Information Location
Mergent INTELLECT
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Mergent INTELLECT
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Information Location
Mergent INTELLECT
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Mergent INTELLECT
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Information Location
Mergent INTELLECT
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Mergent INTELLECT
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Information Location
Mergent INTELLECT
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Mergent INTELLECT
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Information Location
Mergent INTELLECT
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Mergent INTELLECT
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Enter stock ticker symbol – Click Financials – Click Balance Sheet – Bottom line is Ordinary Shares Outstanding
Enter stock ticker symbol – Search – Historical Data – Chart – 5 Years – Use Calendar Year or Fiscal Year End Date
Enter stock ticker symbol – Click Financials – Click Balance Sheet – Bottom line is Ordinary Shares Outstanding
Enter stock ticker symbol – Search – Historical Data – Chart – 5 Years – Use Calendar Year or Fiscal Year End Date
Enter stock ticker symbol – Click Financials – Click Balance Sheet – Bottom line is Ordinary Shares Outstanding
Enter stock ticker symbol – Search – Historical Data – Chart – 5 Years – Use Calendar Year or Fiscal Year End Date
Enter stock ticker symbol – Click Financials – Click Balance Sheet – Bottom line is Ordinary Shares Outstanding
Enter stock ticker symbol – Search – Historical Data – Chart – 5 Years – Use Calendar Year or Fiscal Year End Date
Enter stock ticker symbol – Click Financials – Click Balance Sheet – Bottom line is Ordinary Shares Outstanding
Enter stock ticker symbol – Search – Historical Data – Chart – 5 Years – Use Calendar Year or Fiscal Year End Date
Enter stock ticker symbol – Click Financials – Click Balance Sheet – Bottom line is Ordinary Shares Outstanding
Enter stock ticker symbol – Search – Historical Data – Chart – 5 Years – Use Calendar Year or Fiscal Year End Date
Enter stock ticker symbol – Click Financials – Click Balance Sheet – Bottom line is Ordinary Shares Outstanding
Enter stock ticker symbol – Search – Historical Data – Chart – 5 Years – Use Calendar Year or Fiscal Year End Date
Enter stock ticker symbol – Click Financials – Click Balance Sheet – Bottom line is Ordinary Shares Outstanding
Enter stock ticker symbol – Search – Historical Data – Chart – 5 Years – Use Calendar Year or Fiscal Year End Date
Insert Selected Company Name Here
Using the links to each company’s financial data as your source, fill in all the yellow highlighted cells.
Use most recent three years’ annual calendar or fiscal year-end financial data of your selected company.
Now you are ready to go to work!
ed Company Name Here
ghlighted cells.
selected company.
Milestone One: Time Value of Money
(please fill in YELLOW cells)
* Dollar amounts are in millions $
Question 1.
Interest Rate
FCF – Years
FCF in dollars*
5%
$
–
$
–
$
–
Pv*
$
–
$
–
$
–
Total Pv*
*In millions
$
–
FCF in dollars*
$
–
$
–
$
–
Pv*
$
–
$
–
$
–
Total Pv*
$
–
Question 2. Interest Rate
5%
FCF – Years
*In millions
Question 3.
Interest Rate
7%
Required Rate of Return for Risk
Associated With Projected Future Three
Year’s Free Cash Flows.
5%
Required Rate of Return for Risk
Associated With Projected Future Three
Year’s Free Cash Flows.
FCF – Years
FCF in dollars*
$
–
$
–
$
–
$
–
Pv*
$
–
$
–
$
–
$
–
*In millions
Total Pv*
$
–
Instructions and Explanations
Free cash flows (FCF), for this exercise is difference between cash generated from
operating income minus capital expenses at the end of your company’s fiscal or calendar
year. The present value of free cash flows is one method of determining a company’s value
to a potential buyer.
Note: For Milestone One, please use the adjusted free cash flows calculated for your
selected company on Tab 2 (Selected Co. for Project) of this Excel workbook. Use 5% as
the interest rate for these questions.
For the purpose of this exercise, what will happen to the total PV if your selected
company’s free cash flows for each year reported in Question 1 were reduced by 10%?
Numbers from Q1
$
–
$
–
$
–
$
–
Your selected company is projecting that free cash flows for the next three (3) years
will increase by 3% annually. Using the free cash flows from Question 1 above,
increase each of the future free cash flows by 3% and enter these numbers in the
yellow highlighted areas. As there is a chance that these increases may not occur as
projected, a buyer who may be willing to purchase the company before the increases
occur, will account for this risk by using a required rate of return of 7%. What price
would the potential buyer be willing to pay based upon the known free cash flows and
the future projected free cash flows?
Once you have completed these calculations, proceed to write your written analysis.
xplanations
ence between cash generated from
end of your company’s fiscal or calendar
method of determining a company’s value
free cash flows calculated for your
ect) of this Excel workbook. Use 5% as
n to the total PV if your selected
in Question 1 were reduced by 10%?
ash flows for the next three (3) years
ash flows from Question 1 above,
% and enter these numbers in the
hat these increases may not occur as
hase the company before the increases
ired rate of return of 7%. What price
ed upon the known free cash flows and
oceed to write your written analysis.
Milestone Two: Stock Valuation and Bond Issuance (fill in the YELLOW cells)
PART I: STOCK VALUATION
Read the explanations to the right of the calculation cells for specific information on the data.
Question 1: Initial Stock Valuation
Year
Cash Div/Share ($) Dividend Yield
$
$
$
–
Stockholder’s
Stock Price
Equity (in millions)
0% $
0% $
0% $
–
$
$
$
Total Shares
Outstanding
(millions of
shares)
–
0
0
0
Question 2. Stock Valuation—The New Dividend Yield if the Company Increased Its Dividend Per Share by 1.75
Use the numbers from Question 1 as your basis to calculate these questions.
Year
Cash Div/Share ($)
Stockholder’s
Dividend Yield
Stock Price
+1.75
Equity (in millions)
$
$
$
–
#DIV/0!
#DIV/0!
#DIV/0!
0 $
0 $
0 $
Total Shares
Outstanding
(millions of
shares)
–
0
0
0
Question 3. The Dividend Yield, Cash Dividend Per Share, Stock Price, and Total Shares Outstanding After the Stock Split
Use the numbers from Question 1 to calculate these questions.
Year
Cash Div/Share ($) Dividend Yield
Stockholder’s
Stock Price
Equity (in millions)
Total Shares
Outstanding
(millions of
shares)
$
$
$
–
0,00%
0,00%
0,00%
0 $
0 $
0 $
–
0
0
0
Question 4. The Rate of Return on Investment Based on the Cash Div/Share ($) and Stock Price You Used in Question 1
Use the numbers from Question 1 to calculate these questions.
Year
Cash Div/Share ($)
$
$
$
–
Stock Price
$
$
$
–
Return on
Investment
CALCULATE ROI
#DIV/0!
#DIV/0!
ROI cell is formulated
ROI cell is formulated
PART II: BOND ISSUANCE
Newly issued 10-year bond. Calculate the present value in the four scenarios below.
1. The present value of the bond at issuance
Present Value PV
Periods
N
$
0 Number of semi-annual payments made over 10 years
Interest
Payments
Future Value
I
PMT
FV
$
$
% Annual interest rate at issuance paid semi-annually (Annual Interes
This bond makes regular semi-annual payments of interest (in doll
Future value in 10 years—enter as a positive number (Always the
2. The present value of the bond if overall rates in the market increased by 2% annually
Present Value
Periods
Interest
Payments
Future Value
PV
N
I
PMT
FV
$
–
$
$
0 Number of semi-annual payments made over 10 years
% New annual market interest rate paid semi-annually (New Annual R
This bond makes regular semi-annual payments of interest (in dolla
Future value in 10 years—enter as a positive number (Always the F
3. The present value of the bond if overall rates in the market decreased by 2% annually
Present Value
Periods
Interest
Payments
Future Value
PV
N
I
PMT
FV
$
–
$
$
0 Number of semi-annual payments made over 10 years
% New annual market interest rate paid semi-annually (New Annual R
This bond makes regular semi-annual payments of interest (in dolla
Future value in 10 years—enter as a positive number ( Always the
4. The present value of the bond if overall rates in the market remained the same as at issuance
Present Value
Periods
Interest
Payments
Future Value
PV
N
I
PMT
FV
$
–
$
$
0 Number of semi-annual payments made over 10 years
% Annual market interest rate remains the same as Question 1, paid se
This bond makes regular semi-annual payments of interest (in dolla
Future value in 10 years—enter as a positive number (Always the F
Instructions and Explanation
PART I: STOCK VALUATION
d in Question 1
(10 X 2)
ally (Annual Interest Rate / 2)
of interest (in dollars). Annual Interest Payment / 2).
mber (Always the Future or Face Value of the Bond)
(10 X 2)
ally (New Annual Rate divided by 2)
of interest (in dollars) (Dollars Paid Annually divided by 2)
mber (Always the Future or Face Value of the Bond and Never Changes)
(10 X 2)
ally (New Annual Rate divided by 2)
of interest (in dollars) (Dollars Paid Annually divided by 2 and Never Changes)
mber ( Always the Future or Face Value of the Bond and Never Changes)
(10 X 2)
Question 1, paid semi-annually (Annual Rate divided by 2)
of interest (in dollars) (Dollars Paid Annually divided by 2 and Never Changes)
mber (Always the Future or Face Value of the Bond and Never Changes)
Instructions and Explanations
PART I: STOCK VALUATION
Note: Use and enter the fiscal or year-end financial data that you calculated or found for your
selected company and enter the appropriate data into each yellow highlighted cell.
Note: Stock price for your selected company is found in your selected company’s spreadsheet.
Stockholder’s Equity = Assets – Liabilities. This represents the ownership of a corporations.
Owners are called “stockholders” because they hold stocks or shares of the company. The main
goal of every corporate manager is to generate shareholder value. This number will be found on
your selected company’s final project Excel workbook spreadsheet.
Stockholder’s Equity = Assets – Liabilities. This represents the ownership of a corporations.
Owners are called “stockholders” because they hold stocks or shares of the company. The main
goal of every corporate manager is to generate shareholder value. This number will be found on
your selected company’s final project Excel workbook spreadsheet.
Return on Investment (ROI) :
Using the formula: ROI = ((Dividend ) + (New Price – Old Price) / Old Price))
PART II: BOND ISSUANCE
Bonds are a long-term debt for corporations. By buying a bond, the bond-purchaser lends
money to the corporation. The borrower promises to pay a specified interest rate during
the bond’s lifetime and at maturity, payback the entire future value of the bond. In case of
bankruptcy, bondholders have priority over stockholders for any payment distributions.
For purposes of this exercise, certain assumptions are being made. Assume that your
selected company issued a new 10-year bond for $300,000 on October 1, 2021, that will
mature on October 1, 2031. The future value of this bond is therefore $300,000. The bond
was issued at the current market rate of 5.0% fixed for 10 years, with interest payments
made semi-annually. What is the present value of this bond using the three scenarios in
Part II: Bond Issuance?
Bonds = Debt………………..Bondholders = Lenders
NOTE: A simple rule to follow: When market rates change, nothing in the original bond’s
terms change, except you will enter the new market interest rate in place of the interest
rate stated at the bond’s issuance date. In other words, the future value remains the same,
payments remain the same, periods remain the same. When you change the interest rate
to reflect the new market rate, the present value of the bond will either increase or
decrease.
For the purposes of this exercise, assume that the new market rates occur one (1) day
after the initial bond is issued.
To calculate PV, you can use the Excel formula or the financial calculator provided.
Link is provided below.
https://www.arachnoid.com/finance
Once you have completed these calculations, proceed to write your written analysis.
ver Changes)
Milestone Three: Capital Budgeting Data (fill in YELLOW cells)
WACC
Initial Outlay CF1
$0
Cash Flows (Sales)
$
– Operating Costs (excluding Depreciation)
$
– Depreciation Rate of 20% (5-Years)
$
Operating Income (EBIT)
$
– Income Tax (Rate XX%)
$
After-Tax EBIT
$
+ Depreciation
$
Cash Flows
$0 $
CF2
–
$
$
$
$
$
$
$
$
CF3
–
Select from dropdown:
NPV
IRR
$0
#NUM!
$
$
$
$
$
$
$
$
CF4
–
$
$
$
$
$
$
$
$
–
Instructions and Explanations
For this milestone, you will assume that your selected company is
considering a potential capital investment for new equipment.
Choose one of the three investment options listed below and solve. You will either ACCEPT or REJECT
CF5
$
$
$
$
$
$
$
$
–
1. Capital Budgeting Example Setup
Initial investment $17,000,000
Straight-line depreciation of 20%
Income tax rate = 20%
WACC: use 5%
Cash Flows (sales revenue based upon this
purchase of new equipment, are projected
to be as follows):
CF1: $4,000,000
CF2: $4,200,000
2. Capital Budgeting Example Setup
Initial investment $65,000,000
Straight-line depreciation of 20%
Income tax rate = 20%
WACC: use 9%
Cash Flows (sales revenue based upon this
purchase of new equipment, are projected
to be as follows):
CF1: $15,000,000
CF2: $17,000,000
CF3: $4,500,000
CF4: $5,000,000
CF5: $4,700,000
Operating Costs
CF1: $400,000
CF2: $200,000
CF3: $225,000
CF4: $350,000
CF5: $400,000
CF3: $18,000,000
CF4: $19,000,000
CF5: $18,000,000
Operating Costs
CF1: $500,000
CF2: $500,000
CF3: $600,000
CF4: $500,000
CF5: $500,000
Initial Investment—always negative. Companies have to invest money (“payout”
funds) in order to gain the future benefit.
WACC—why do we use WACC rate for new projects? If the project’s IRR doesn’t
earn a return equal to or higher than WACC, the corporation should abandon the
project and invest money elsewhere.
Once you have completed these calculations, proceed to write your written analysis.
ll either ACCEPT or REJECT the proposal.
3. Capital Budgeting Example Setup
Initial investment $85,000,000
Straight-line depreciation of 20%
Income tax rate = 20%
WACC: use 9%
Cash Flows (sales revenue based upon this
purchase of new equipment, are projected
to be as follows):
CF1: $50,000,000
CF2: $45,000,000
CF3: $35,000,000
CF4: $30,000,000
CF5: $30,000,000
Operating Costs
CF1: $15,000,000
CF2: $12,000,000
CF3: $11,000,000
CF4: $13,000,000
CF5: $130000,000
analysis.
Milestone Four: Interest Rate Implication (fill in YELLOW cells)
1. Original Scenario From Milestone 1—Time Value of Money Using 8%
Current Market Interest Rate at 8%
Interest Rate
8,00%
Years
Amounts*
$
–
$
–
$
–
Pv*
$
–
$
–
$
–
Total Pv*
*In millions
$
–
$
–
$
–
2. Change in Interest Rate and Its Implications
Lower Current Market Interest Rate to 4%
Interest Rate
4,00%
Years
Amounts*
$
–
$
–
$
–
Pv*
$
–
$
–
$
–
Total Pv*
$
–
*In millions
3. Change in Interest Rate and Its Implications
3. Increase Current market Interest Rate to 12%
Interest Rate
12,00%
Years
Amounts*
$
–
$
–
$
–
Pv*
$
–
$
–
$
–
Total Pv*
*In millions
$
–
Instructions and Explanations
We will use your selected company’s free cash flows for Milestone Four analysis.
(Note that the interest rate changes for this milestone are different than those used in Milestone One.)
Three scenarios will be analyzed:
1. Current Market Interest Rate at 8%
2. Lower Current Market Interest Rate to 4%
3. Increase Current market Interest Rate to 12%
Once you have completed these calculations, proceed to write your written analysis.
ilestone One.)
ACCEPT
REJECT
RATIO ANALYSIS
Note: change the column names to your industry and selected companies
RATIOS CONSTRUCTION AND MINING MACHINERY INDUSTRY
CATERPILLAR Inc.
Profitability Ratios (%)
Gross Margin
29.40%
31.96%
EBITDA Margin
18.07%
21.05%
Operating Margin
13.56%
16.17%
Pre-Tax Margin
Effective Tax Rate
13.93%
16.27%
20.10%
24.00%
0.21
Financial Strength
Quick Ratio
Current Ratio
LT Debt to Equity
Total Debt to Equity
Interest Coverage
0.59
0.08
18.58
0.61
25.97
Price/Earnings Ratio
27.42
17.2
Price to Sales P/S
1.71
2.21
Price to Book P/B
5.21
7.99
Free Cash Flow per Share
17.01
-0.35
Return On Assets
5.24%
10.58%
Return On Investment
12.61%
14.13%
Return On Equity
20.95%
45.36%
Valuation Ratios
Management Effectiveness (%)
Efficiency
Receivable Turnover
7.55
6.63
Inventory Turnover
3.2
2.57
0.8
38,023
0.81
7,370
Total Asset Turnover
Free Cash Flow/Net Income
What is Ratio Analysis? Briefly explain and cite your resources: Ratio analysis is a mathematical technique for analyzing a com
about its liquidity, operational effectiveness, and profitability. Ratio analysis allows you to compare one firm to another withi
Based on the ratio analysis above, in which company would you be willing to invest and why? Caterpillar Inc is the better opti
have better revenue incoming and also higher profitability and a chance for growth (Team, 2022). CAT also has a higher retur
TEREX is easily liquid compared to CAT and can manage its assets and liabilities well and effectively. CAT has a higher income
Bloomenthal, A. (2022, September 8). Financial Ratio Analysis: Definition, types, examples, and how to use. Investopedia. Ret
Team, T. (2022, January 11). Should you buy Caterpillar stock over this industrial company? Forbes. Retrieved November 14, 2
TEREX CORPORATION
21.16%
8.49%
10.78%
7.34%
20.48%
0.35
0.72
0.73
7.37
13.22
0.76
3.01
-4.62
8.12%
11.84
22.33%
7.91
3.85
1.39
241
s a mathematical technique for analyzing a company’s financial documents, such as the balance sheet and income statement, to gather kn
ows you to compare one firm to another within the same industry or sector and track how one company has changed over time (Bloome
vest and why? Caterpillar Inc is the better option for investing. This is because, looking at the profitability ratios, specifically the EBITDA, C
owth (Team, 2022). CAT also has a higher return on investments compared to TEREX and therefore is worth the risk of investment. Anothe
well and effectively. CAT has a higher income than TEREX hence making it another reason why I would be willing to invest in it.
s, examples, and how to use. Investopedia. Retrieved November 14, 2022, from https://www.investopedia.com/terms/r/ratioanalysis.asp
al company? Forbes. Retrieved November 14, 2022, from https://www.forbes.com/sites/greatspeculations/2022/01/10/should-you-buy-
ANALYSIS
CAT Inc. is doing better than TEREX in terms of managing and paying for the direct costs of the company and
generating revenue.
CAT has a greater EBITDA margin meaning the company is able to make its profitability consistent. A low EBITDA
margin shows that TEREX is unable to balance between cashflow and maintaining profits.
Cat’s operating margin is higher compared to TEREX meaning it has more earnings from its products over the
year, excluding taxes and discounts.
This tells us the sales percentage that has been turned into profits for a company. CAT has a higher pre-tax profit
margin compared to TEREX hence making it more profitable.
TEREX paid a slightly higher tax rate from its earned income compared to CAT.
both companies have weak quick ratios but CAT’s ratio is weaker hence its assets cannot be easily converted to
cash to pay for short-term liabilities.
Again, both have weak debt to equity ratio hence might be unable to cover their long term debts. TEREX
however, has a higher ratio hence have a higher capability of covering their long-term debts.
Both have weak debt to equity ratio hence might be unable to cover term debts. TEREX however, has a higher
ratio hence have a higher capability of covering debts.
CAT has a higher interest coverage meaning compared to TEREX, it is more poised to pay its debts.
CAT has a higher P/E ratio meaning it has a higher worth compared to TEREX. It also shows that it has higher
growth chances and the investors expect higher earnings growth.
TEREX has a lower P/S ratio meaning investors are paying less for their stock than the market value. A higher P/S
ratio shows the company is strong and growing, as CAT’s is.
A P/B ratio shows the company’s market valuation compared to itsbook value. CAT has a higher book ratio which
could mean the company’s share prices depend heavily on intangible assets hence might not be good for the
company unless they are greatly confident in their stock and growth. The lower the P/B ratio, the better.
This shows a company’s financial flexibility and both companies have negative free cash flow per share and
hence are inflexible but TEREX has a lower number hence it is less flexible compared to CAT.
CAT has a higher ROA compared to TEREX. This means that it generates more profit from its assets compared to
TEREX
CAT has a higher ROI meaning that most of the investments it made were highly profitable compared to those
made by TEREX.
ROE shows how well the company manages capital invested by shareholders to make more revenue. CAT has a
higher ROE compared to TEREX meaning its capital was efficiently used.
This shows how the companies are managing the credit given to their customers. TEREX has a higher receivable
turnover meaning they manage their extended credit well during an accounting period, compared to CAT.
TEREX has a higher inventory turnover than CAT meaning they have more sales on their stock compared to CAT.
This shows the capability and efficiency of a company’s assets to generate profit. TEREX having a higher asset
turnover means that its assets can easily generate profit for it compared to the ones for CAT.
CAT has higher net income than TEREX meaning it is making more per year than TEREX.
e for analyzing a company’s financial documents, such as the balance sheet and income statement, to gather knowledge
irm to another within the same industry or sector and track how one company has changed over time (Bloomenthal, 2022).
Inc is the better option for investing. This is because, looking at the profitability ratios, specifically the EBITDA, CAT seems to
so has a higher return on investments compared to TEREX and therefore is worth the risk of investment. Another reason is
has a higher income than TEREX hence making it another reason why I would be willing to invest in it.
References
se. Investopedia. Retrieved November 14, 2022, from https://www.investopedia.com/terms/r/ratioanalysis.asp
eved November 14, 2022, from https://www.forbes.com/sites/greatspeculations/2022/01/10/should-you-buy-caterpillar-stock-over-this
erpillar-stock-over-this-industrial-company/?sh=77a2511669ad
FIN 550 Module One Activity Guidelines and Rubric
Overview
For this task, analyze the financial health of two competitors in the same industry based on their ratios, using the provided Excel spreadsheet template.
Use your selected company and another company of your choice in the same industry. Then, complete your analysis by writing a short synopsis of your
findings in the space provided with the analysis. Lastly, respond to the two questions listed below to complete the ratio analysis.
Prompt
Follow these steps to analyze the financial health of your selected company and one of its competitors. Use the Module One Activity Template to
complete this task. Complete the spreadsheet by providing the following:
●
●
●
●
Ratio Research: Use the Excel spreadsheet template to analyze the selected company ratios (profitability, financial strength, valuation,
management effectiveness, dividends, and efficiency) for both of the competitors. To complete this part, you can use the websites CIS Market or
Mergent INTELLECT to obtain the ratios. You can also use the SEC Edgar Company Filings resource to obtain the ratio from annual reports. Note:
The ratios have to be from the same time period, the same year for both competitors. For training on how to use Excel, visit the SNHU Atomic
Learning video training site or search YouTube to view appropriate Excel training videos.
Industry Ratios: In order to analyze ratios for the companies, you need to obtain the ratios for the industry the competitors operate in. Industry
values for the ratios can be found in the index column of the Morningstar Valuation page. If no index value is available, put the five-year
averages for both companies in the industry column and use these figures for the industry comparison of your ratio analysis.
Ratio Analysis: Compare the two companies based on their ratios. Use the last column in the template to detail how each company is doing in
relation to the ratios. Explain the significance of how the company ratios compare to the industry and each other.
Summary: Finish by answering the questions below the analysis section of your Excel spreadsheet.
o Briefly explain what a ratio analysis is in 1-2 paragraphs. Cite your resources.
o Based on the ratio analysis, in which company would you be willing to invest and why?
Note: This is a theoretical exercise. You should not be investing according to this analysis.
Rubric
Guidelines for Submission: Complete and submit the Excel Worksheet template provided.
Critical Elements
Ratio Research
Exemplary
Proficient
Provides accurate data for all the
required company ratios in the
categories of profitability, financial
strength, valuation, management
effectiveness, dividends, and
efficiency (100%)
Needs Improvement
Provides data for required company
ratios in the categories of profitability,
financial strength, valuation,
management effectiveness, dividends,
and efficiency, but some of the data is
missing or inaccurate (70%)
Not Evident
Does not provide data for
any of the required ratios in
the categories of
profitability, financial
strength, valuation,
management effectiveness,
dividends and efficiency (0%)
Value
10
Critical Elements
Industry Ratios
Exemplary
Proficient
Provides accurate data for all the
industry ratios in the categories of
profitability, financial strength,
valuation, management
effectiveness, dividends, and
efficiency (100%)
Needs Improvement
Provides data for the industry ratios in
the categories of profitability,
financial strength, valuation,
management effectiveness, dividends,
and efficiency, but some of the ratios
are missing or inaccurate (70%)
Not Evident
Does not provide data for
any of the industry ratios in
the categories of
profitability, financial
strength, valuation,
management effectiveness,
dividends and efficiency (0%)
Value
10
Ratio Analysis
Provides a detailed analysis for
each ratio that demonstrates a
complex grasp of each
company’s position when
compared to each other and
the industry (100%)
Provides a detailed analysis for
each ratio that provides an
accurate assessment of each
company’s position when
compared to each other and the
industry (90%)
Provides an analysis for each ratio,
but the analysis lacks detail, is
incomplete, or demonstrates an
inaccurate understanding of each
company’s position when compared
to each other and the industry (70%)
Does not provide a ratio
analysis (0%)
35
Summary
Provides an accurate and
complete definition of ratio
analysis as well as a thorough
and cogent, detailed
explanation of which company
they would invest in and why
that is supported by the
financial data (100%)
Provides an accurate and
complete definition of ratio
analysis as well as an appropriate
explanation of which company
they would invest in and why,
supported by the financial data
(90%)
Does not provide a definition
of financial analysis or a
selected investment and
explanation (0%)
35
Articulation of
Response
Submission is free of errors
related to citations, grammar,
spelling, syntax, and
organization and is presented
in a professional and easy to
read format (100%)
Submission has no major errors
related to citations, grammar,
spelling, syntax, or organization
(90%)
Provides a definition of ratio analysis
and an explanation of which
investment they would choose and
why, but the definition is inaccurate
or incomplete, or the explanation of
which investment they would choose
and why lacks detail, is incomplete or
inaccurate or not supported by the
financial data (70%)
Submission has major errors related
to citations, grammar, spelling,
syntax, or organization that negatively
impact readability and articulation of
main ideas (70%)
Submission has critical errors
related to citations,
grammar, spelling, syntax, or
organization that prevent
understanding of ideas (0%)
10
Total
100%
Purchase answer to see full
attachment
We offer the bestcustom writing paper services. We have done this question before, we can also do it for you.
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.
