Overview

For the final project, you will use the scenario provided below. You will use financial information from your selected company to prepare a financial analysis report. Your analysis should include the background calculations and managerial analysis for each topic: time value of money, stock and bond valuation, and capital budgeting. You will also discuss macroeconomic variables (i.e., interest rates, inflation, unemployment, and so forth) that might impact your selected company's financial decision-making and strategic objectives. These topics will be covered over four milestones to be submitted throughout the course before you submit the final project. While these elements may seem separate and unrelated, they will present a well-rounded view of your selected company's finances.

In this milestone, using the financial information you prepared from the company you selected from the first tab of the Final Project Student Spreadsheet, you will submit a draft of the Macroeconomic Items section of the final project, along with your supporting explanations.

Scenario

In this project, you will step into the role of a financial analyst tasked with conducting a comprehensive financial analysis for a potential corporate investment opportunity. Your goal is to create a detailed financial analysis report that will guide the investment decision-making process for the corporation you select from the Final Project Student Spreadsheet, which is linked in the What to Submit section.

You have been hired by a prominent financial consulting firm known for its expertise in providing data-driven insights to corporations seeking to make informed investment decisions. Your firm has a reputation for delivering accurate financial analyses that aid companies in identifying profitable investment opportunities.

Prompt

Provide an explanation of the impact of external factors on the financial position of your selected company. Use the Interest Rates Implications tab in the Final Project Student Spreadsheet to demonstrate the implications of interest rate changes on at least one of the calculations you performed in Milestone One.

Specifically, the followingcritical elementsmust be addressed:

1. 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 the CEO otherwise based on the following:
   1. Analyze theimplicationsof interest rate changes on any of your calculations. Support your claims.
   2. Determine how an issue in the overallstock market?negative or positive?might impact the company's stock valuation numbers, other financial variables, or its overall portfolio management. Support your response with evidence through research, references, and citations using proper APA style.
   3. Analyze the impact of anyexternal factor(i.e., external to the company) discussed throughout the course on the company's financial position. Technological factors, competitive factors, and global factors are examples of external factors. Justify your reasoning through research, references, and citations using proper APA style.

Milestone One: Time Value of Money (Fill in the yellow cells)					Instructions and Explanations
* Dollar amounts are in millions $
Question 1.									Free cash flows (FCF), for this exercise is the 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 free cash flows shown for your selected company on the List of Companies tab of this Excel workbook. Use 5% as the interest rate for these questions.
	Interest Rate	5%	Most Recent Year
	FCF - Years
	FCF in dollars*		$ -	$ -	$ -
	Pv*		$0.00	$0.00	$0.00
	Total Pv*		$ -
	*In millions
Question 2.	Interest Rate	5%	Most Recent Year
	FCF - Years						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%?
	FCF in dollars*		$ -	$ -	$ -
	Pv*		$0.00	$0.00	$0.00
	Total Pv*		$ -
	*In millions
Question 3.	Interest Rate	9%	Required Rate of Return for Risk Associated With Projected Future Three Year's Free Cash Flows.	5%
			Most Recent Year's Cash Flow fom Queston 1.
	FCF - Years							Your selected company isprojecting that free cash flows for the next three (3) years will increase by 3% annually. Using the Most Recent Year's free cash flows from Question 1 above, increase each of the future free cash flows by 3%(will self-populate in the yellow highlighted cells). 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 9%. What price would the potential buyer be willing to pay based upon the known free cash flows and the future projected free cash flows?
	FCF in dollars*		$ -	$ -	$ -	$ -
	Pv*		$ -	$ -	$ -
	*In millions
	Total Pv*		$ -	This is the Company Value - Selling Price

Milestone Two: Stock Valuation and Bond Issuance (Fill in the yellow cells)				Instructions and Explanations
PART I: STOCK VALUATION								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 Equity (in millions)	Stock Price	Total Shares Outstanding (millions of shares)				Note: Use and enter the fiscal or year-end financial data for yourselected company and enter the appropriate data into each yellow highlighted cell.
		$ -	0%	$ -	$ -	0
		$ -	0%	$ -	$ -	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 ($) +1.75	Dividend Yield	Stockholder's Equity (in millions)	Stock Price	Total Shares Outstanding (millions of shares)				Note: Stock price for your selected company is found in your selected company's spreadsheet.
		$ -	#DIV/0!	0	$ -	0
		$ -	#DIV/0!	0	$ -	0
		$ -	#DIV/0!	0	$ -	0
Question 3. The Dividend Yield, Cash Dividend Per Share, Stock Price, and Total Shares Outstanding After the Stock Split Occurs
	Use the numbers from Question 1 to calculate these questions.
	Year	Cash Div/Share ($)	Dividend Yield	Stockholder's Equity (in millions)	Stock Price	Total Shares Outstanding (millions of shares)				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. Shareholder Equity is also known as Stockholder Equity and can be found for your selected company in the List of Companies tab in this spreadsheet.
		$ -	0.00%	0	$ -	0
		$ -	0.00%	0	$ -	0
		$ -	0.00%	0	$ -	0
										Return on Investment (ROI) : Using the formula: ROI = ((Dividend ) + (New Price - Old Price) / Old Price))
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!	ROI cell is formulated
		$ -	$ -	#DIV/0!	ROI cell is formulated
PART II: BOND ISSUANCE								PART II: BOND ISSUANCE
Newly issued 10-year bond.	Calculate the present value in the four scenarios below.					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.
1. The present value of the bond at issuance					For purposes of this exercise, assume that your selected company issued a new 10-year bond for $300,000 on October 1, 2023. The bond will mature on October 1, 2033. The future value of this bond is $300,000. The bond was issued at the latest 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 four scenarios in Part II: Bond Issuance?
	Present Value	PV	$ -
	Periods	N	0	Number of semi-annual payments made over 10 years =	(10 X 2)
	Interest	I	%	Annual interest rate at issuance paid semi-annually (Annual Interest Rate / 2)
	Payments	PMT	$ -	This bond makes regular semi-annual payments of interest (in dollars). Annual Interest Payment / 2).
	Future Value	FV	$ -	Future value in 10 years?enter as a positive number (Always the Future or Face Value of the Bond)	Bonds = Debt....................Bondholders = Lenders
2. The present value of the bond if overall rates in the market increased by 2% annually				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.
	Present Value	PV	$ -							For the purposes of this exercise, assume that the new market rates occur one (1) day after the initial bond is issued.
	Periods	N	0	Number of semi-annual payments made over 10 years =	(10 X 2)
	Interest	I	%	New annual market interest rate paid semi-annually = (New Annual Rate divided by 2)
	Payments	PMT	$ -	This bond makes regular semi-annual payments of interest (in dollars) = (Dollars Paid Annually divided by 2)	You can either use Excel formulas or the web-based financial calculator provided at the link below.
	Future Value	FV	$ -	Future value in 10 years?enter as a positive number = (Always the Future or Face Value of the Bond and Never Changes)
										Arachnoid Finance Calculator
3. The present value of the bond if overall rates in the market decreased by 2% annually
	Present Value	PV	$ -							Once you have completed these calculations, proceed to write your written analysis
	Periods	N	0	Number of semi-annual payments made over 10 years =	(10 X 2)		on your Word Document
	Interest	I	%	New annual market interest rate paid semi-annually = (New Annual Rate divided by 2)
	Payments	PMT	$ -	This bond makes regular semi-annual payments of interest (in dollars) = (Dollars Paid Annually divided by 2 and Never Changes)
	Future Value	FV	$ -	Future value in 10 years?enter as a positive number = ( Always the Future or Face Value of the Bond and Never Changes)
4. The present value of the bond if overall rates in the market remained the same as at issuance
	Present Value	PV	$ -
	Periods	N	0	Number of semi-annual payments made over 10 years	(10 X 2)
	Interest	I	%	Annual market interest rate remains the same as Question 1, paid semi-annually (Annual Rate divided by 2)
	Payments	PMT	$ -	This bond makes regular semi-annual payments of interest (in dollars) (Dollars Paid Annually divided by 2 and Never Changes)
	Future Value	FV	$ -	Future value in 10 years?enter as a positive number (Always the Future or Face Value of the Bond and Never Changes)

Milestone Three: Capital Budgeting Data (Fill in yellow cells)

Instructions and Explanations

WACC

For this milestone, you will assume that your selected company is

As Shown in the Description

considering a potential capital investment for new equipment.

description under Instructions and Explanations

Choose oneof the three investment options listed below and solve. You will eitherACCEPT orREJECT the proposal.

Initial Outlay

CF1

CF2

CF3

CF4

CF5

$0

1. Capital Budgeting Example Setup

2. Capital Budgeting Example Setup

3. Capital Budgeting Example Setup

Cash Flows (Sales)

$ -

$ -

$ -

$ -

$ -

Initial investment $17,000,000

Initial investment $65,000,000

Initial investment $85,000,000

- Operating Costs (excluding Depreciation)

$ -

$ -

$ -

$ -

$ -

Straight-line depreciation of 20%

Straight-line depreciation of 20%

Straight-line depreciation of 20%

- Depreciation Rate of 20% (5-Years)

$ -

$ -

$ -

$ -

$ -

Income tax rate = 20%

Income tax rate = 20%

Income tax rate = 20%

Operating Income (EBIT)

$ -

$ -

$ -

$ -

$ -

WACC: use 5%

WACC: use 9%

WACC: use 9%

- Income Tax (use 20%)

$ -

$ -

$ -

$ -

$ -

Cash Flows (sales revenue based upon this

Cash Flows (sales revenue based upon this

Cash Flows (sales revenue based upon this

After-Tax EBIT

$ -

$ -

$ -

$ -

$ -

purchase of new equipment, are projected

purchase of new equipment, are projected

purchase of new equipment, are projected

+ Depreciation

$ -

$ -

$ -

$ -

$ -

to be as follows):

to be as follows):

to be as follows):

Cash Flows

$0

$ -

$ -

$ -

$ -

$ -

CF1: $4,390,000

CF1: $15,000,000

CF1: $50,000,000

CF2: $4,200,000

CF2: $17,000,000

CF2: $45,000,000

Select from drop-down:

CF3: $4,500,000

CF3: $18,000,000

CF3: $35,000,000

NPV

$0

CF4: $5,000,000

CF4: $19,000,000

CF4: $40,000,000

CF5: $4,700,000

CF5: $18,000,000

CF5: $35,000,000

IRR

#NUM!

Operating Costs

Operating Costs

Operating Costs

CF1: $700,000

CF1: $500,000

CF1: $15,000,000

CF2: $700,000

CF2: $500,000

CF2: $12,000,000

CF3: $225,000

CF3: $600,000

CF3: $11,000,000

CF4: $350,000

CF4: $500,000

CF4: $13,000,000

CF5: $400,000

CF5: $500,000

CF5: $13,000,000

Initial Investment?always entered as a negative number. 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.

Weighted Average Cost of Capital (Use 5%) 5% 5% 5% 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 2022 2021 2020 Infor Enter Years Net Cash Provided by Operating Activities 6, 190,000.00 $ 6,815,000.00 $ 4,664,000.00 Mergent Intellect Capital Expenditures 2,614,000.00 $ 2,865,000.00 $ 3,128,000.00 Mergent Intellect Free Cash Flows 3,576,000.00 $ 3,950,000.00 $ 1,536,000.00 Formulated Cell Will Self-P Cash Dividends per Share A 0.94 $ 0.78 $ 0.68 Mergent Intellect Dividend Yield 1.91% 2.00% 2.11% Formulated Cell Will Self-P Diluted Earnings per Common Share 2.78 $ 3.42 $ 2.03 Mergent Intellect Shareholder's Equity 9,452,000.00 $ 9,576,000.00 $ 8,602,000.00 Mergent Intellect Total Common Shares Outstanding 727000.00 758000.00 788000.00 Yahoo Finance - Stock Mark Common Stock Closing Price per Share 49.09 39.02 $ 32.21 Yahoo Finance - Stock Mark Effective Tax Rate (Use 20%) 20% 20% 20% Weighted Average Cost of Capital (Use 5%) 5% 5% 5%