13 questions in the attached word file. The excel file is attached to be used to assist in completing the questions.

finance_formulas_mba620_project_4.xlsm

mba_620_project_4.docx

Don't use plagiarized sources. Get Your Custom Essay on

Need help with 13 finance formula questions

Just from $13/Page

Unformatted Attachment Preview

Inputs

Single

Cash Payment

Finance Formu

Output

#1 FV of Cash Today

#2 PV of Cash in Future

Cash Today

Years

Yield

FV

1,000

5.00

3.00%

1,159

?? = ???? * (? + ?)?

Stream of

Constant

Cash Payments

#3 FV of Annuity

#4 PV of Annuity

Beg. Cash

Annuity

Pay/Year

Years

Yield

FV

?? = ( ? + ?

?

0

1,000

1

20

5.00%

33,066

– ?)/?) * ???

+ ???? * (? + ?)?

Stream of

Growing

Cash Payments

#6 PV: Growth Annuity

#7 FV: Growth Annuity

First Payment

Growth Rate

Pay/Year

Years

100

3.00%

1

10

Yield

5.00%

PV

875

?

?+?

?? = ???(

)(? –

?-?

?-?

Bond Price,

Yield, and

Duration

?

)

#9 Bond Price

Settle

Maturity

Coupon

Pay/Year

Yield

Price

?? = ?

?? =

#10 Yield to Maturity

3/1/2015

3/1/2025

5.00%

2

6.00%

92.56

Excel Price Function

Project

Valuation:

IRR, NPV, and

Payback

#13 IRR

Yr

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

CF0

CF1

CF2

CF3

CF4

CF5

CF6

CF7

CF8

CF9

CF10

CF11

CF12

CF13

CF14

CF15

CF16

CF17

CF18

CF19

CF20

IRR

#14 NPV

Yr

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

Discount Rate

(10,000)

500

1500

2000

3000

5000

3000

10.12%

Excel IRR Function

Mortgage

Analytics:

Amortizing

Loans

#16 Monthly Payment

or

Principal

Pay/Year

Years

Coupon Rate

Payment

PMT Function

#17 Mthly Pay w Balloon

20,000

12

5

5.00%

377

377

Pmt = ? / ( 1 – (1/1+r))? x Prin

Weighted

Average

Cost of Capital

#20 WACC

Equity

Market Value

Equity Cost

50

10.0%

MVe

Re

Debt

Debt Value

Debt Cost

50

6.0%

WACC

8.00%

???? = ??

MVd

Rd

???

???

+ ??

??? + ???

??? + ???

Finance Formulas

All Yields and Coupons are “annual rates”.

Yields are “bond equivalent yields”

Payments occur at the end of the period.

#2 PV of Cash in Future

Cash in Future

Years

Yield

PV

1,159

5.00

3.00%

1,000

Goal Seek:

To solve for an “Input” for a given “Output”, use Goal Seek.

Goal Seek is in the Data worksheet in the What If drop-down.

?? = ????/(? + ?)?

#4 PV of Annuity

#5 Annuity Payment

Annuity

Pay/Year

Years

Yield

Balloon Pay

PV

?

?+?

?-

?

?

1,000

1

20

5.00%

0

12,462

* ???

– Balloon/(? +

PV

Pay/Year

Years

Yield

Payment

PMT Function

?)?

#7 FV: Growth Annuity

100

5.00%

1

5

Yield

12.00%

= ???

?+?

?-

?

?+?

) * ??

?

First Payment

Growth Rate

Pay/Year

Yield

PV

100.00

5.00%

1

12.00%

1,429

694

?

– ? + ?)?

?-?

?? =

#10 Yield to Maturity

Price

Coupon

Pay/Year

Par Amount

Settle Date

Maturity

?

?=(

#8 PV: Perpetual Growth Annuity

First Payment

Growth Rate

Pay/Year

Years

FV

100,000

1

15

3.00%

8,377

8,377

???

?-?

#11 Modified Duration

92.56

5.00%

2

100

2/1/2015

2/1/2025

Settle

Maturity

Yield

Coupon

Pay/Year

Duration

3/1/2015

3/1/2025

5.00%

5.0%

2

7.79

6.00%

YTM

CF0

CF1

CF2

CF3

CF4

CF5

CF6

CF7

CF8

CF9

CF10

CF11

CF12

CF13

CF14

CF15

CF16

CF17

CF18

CF19

CF20

Discount Rate

NPV

(10,000)

500

1500

2000

3000

5000

3000

10.12%

0

Excel Duration Function

#15 PayBack

Yr

Cost

1

CF1

2

CF2

3

CF3

4

CF4

5

CF5

6

CF6

7

CF7

8

CF8

9

CF9

10

CF10

11

CF11

12

CF12

13

CF13

14

CF14

15

CF15

16

CF16

17

CF17

18

CF18

19

CF19

20

CF20

(10,000)

500

1500

2000

3000

5000

3000

Payback Term

4.6

NPV = s??=? ??? / (1+r)?

#17 Mthly Pay w Balloon

Principal

Pay/Year

Years

Coupon Rate

Balloon

PMT

#18 Balance Outstanding

100,000

12

10

5.00%

15,000

964

Excel Payment Formula

Principal

At Period t

Term

Pay/Year

Coupon Rate

Balance

100,000

60

30

12

5.00%

91,829

? = [(? – (? + ?)?-? )/(? – (? + ?)-? ]*P

Cum.

-9500

-8000

-6000

-3000

2000

5000

5000

5000

5000

5000

5000

5000

5000

5000

5000

5000

5000

5000

5000

5000

100.0

100.5

100.6

100.7

4.6

5.3

6.5

7.5

8.5

9.5

10.5

11.5

12.5

13.5

14.5

15.5

16.5

17.5

18.5

19.5

#19A Years to Maturity

#19A Periods to FV

Principal

Coupon

Payment

Pay/Year

Years

M=

100,000

5.00%

10,000

1

14.2

-??? ? – ? /

?

?

/[??? ? + ? , ??]

Present Value

Future Value

Annual Rate ( r )

Period/Yr

No. of Periods

100

200

5.00%

12

166.7

??? = ???? ???? + ????? ????? – ???? ???? ? (Assets/Equity)

FINANCIAL RATIO ANALYSIS

Inputs

Company Name:

BALANCE SHEET

ASSETS

Cash & Investments

Accounts Receivable

Inventories

Other

Total Current Assets

Long Term Assets

Total Assets

LIABILITIES & NW

Accounts Payable

Notes payable;

Other Current

Total current liabilities

Long Term Debt

Total Liabilities

Equity

Company # 1

Company # 2

ABC

XYZ

210

180

150

20

560

1200

1760

200

170

140

10

520

1180

1700

290

30

160

480

670

1150

610

280

20

150

450

750

1200

500

Financial Ratio Formulas

Profitability Ratios

Gross Margin: = Gross Profit/Sales

Operating Margin: = EBIT/Sales

Net Profit Margin: = Net income/Sales

Return on Equity (ROE): = Net Income/Book Value of Equity

Return on Assets (ROA) = Net Income / Book Value of Assets

Earnings per Share = Earnings / Shares Outstanding

Liquidity Ratios

Current Ratio: = Current Assets/Current Liabilities

Quick Ratio: = (Cash + Accounts Receivable)/Current liabilities

Cash Ratio: = Cash/Current Liabilities

Working Capital Ratios

Cash Conversion Cycle:

Accounts Receivable Days: = Accounts Receivable/Sales/365

Accounts Payable Days: = Accounts Payable/Sales/365

Inventory Days: = Inventory/Sales/365

Cash Conversion Cycle = AR Days + Inventory Days – AP Days

Turnover Ratios:

A.R. Turnover: = Sales/Accounts Receivable

A.P. Turnover: = Cost of Sales/Accounts Payable

Inventory Turnover: = Cost of Sales/Inventory

Enterprise Value

Market Capitalization = Market Price per share X Shares Oustanding

Total Debt

Cash & Investments

Enterprise Value = Market Cap + Debt – Cash & Invest.

Debt to Enterprise Value = Debt / Enterprise Value

Net Debt = Debt – Cash & Investments

Debt Coverage Ratios

EBIT/Interest Coverage: = EBIT/Interest

EBITDA/Interest Coverage: = EBITDA/Interest

Leverage Ratios

Asset Turnover: = Sales/Total Assets

Debt-Equity Ratio: = Long-Term Debt/Equity

Debt-to-Capital Ratio: = Total Debt/(Total Equity + Total Debt)

Equity Multiplier = Total Assets/Equity

Valuation Ratios

Market-to-Book Ratio: = Market Value of Equity/Book Value of Equity

Price-Earnings Ratio: = Share Price/Earnings per Share

Cash Flow per Share = Net Cash Flow / Shares Outstanding

DuPont Formula

Net Profit Margin = Net Income / Sales

Asset Turnover = Sales / Total Assets

Equity Multiplier = Total Assets / Equity

ROE = Net Profit Margin X Asset Turnover X Equity Multiplier

NCIAL RATIO ANALYSIS

Company # 1

ABC

Company # 2

XYZ

386

235

151

15

6

130

15

50

65

400

275

125

13

7

105

24

45

36

150

8

1200

650

200

5

1000

700

P&L STATEMENT

Sales

Cost of Sales

Gross Profit

Operating Expense

Depreciation

EBIT (Oper. Income)

Taxes

Interest Expense

Net Income

MARKET DATA

Shares Outstanding

Share Market Price

Market Capitalization

Market Value of Debt

Edit by deleting rows for all except 10 selected ratios

Copy-and-Paste sections below for the Exhibits in paper

Company

ABC

XYZ

ROA

Earnings per Share

39.1%

33.7%

16.8%

10.7%

3.7%

0.43

31.3%

26.3%

9.0%

7.2%

2.1%

0.18

Liquidity Ratios

Current Ratio

Quick Ratio

Cash Ratio

ABC

XYZ

1.17

0.81

0.44

1.16

0.82

0.44

Profitability Ratios

Gross Margin

Operating Margin

Net Profit margin

ROE

Working Capital Ratios

Cash Conversion Cycle:

Accounts Receivable Days

Accounts Payable Days

Inventory Days

Cash Conversion Cycle

Turnover Ratios:

Acct. Rec. Turnover

Acct. Pay. Turnover

Inventory Turnover

Enterprise Value

Market Capitalization

plus Total Debt

minus Cash and Investments

Enterprise Value

Debt/EV

Net Debt

Net Debt/EV

Coverage Ratios

EBIT/Interest Coverage

EBITDA/Interest Coverage

Leverage Ratios

Asset Turnover

Debt-Equity Ratio

Debt-Capital Ratio

Equity Multiplier

Valuation Ratios

Market-to-Book Ratio

Price-Earnings Ratio

Cash Flow / Share

DuPont Formula

Net Profit Margin

Asset Turnover

Equity Multiplier

ROE

ABC

XYZ

170.2

155.1

274.2

141.8

255.5

127.8

37.82

27.38

2.1

0.8

1.6

2.4

1.0

2.0

ABC

XYZ

1,200.0

1,150.0

210.0

2,140.0

0.54

940.0

0.44

1,000.0

1,200.0

200.0

2,000.0

0.60

1,000.0

0.50

ABC

XYZ

2.60

2.72

2.33

2.49

ABC

XYZ

0.22

1.10

0.52

2.89

0.24

1.50

0.60

3.40

ABC

XYZ

1.97

0.1

0.39

2.00

0.1

0.16

ABC

XYZ

16.84%

0.22

2.89

9.00%

0.24

3.40

10.66%

7.20%

Company # 1

ABC

Company # 2

XYZ

65

36

4

2

5

2

(10)

(20)

(5)

20

56

(15)

(50)

(4)

10

(16)

20

15

5

5

45

15

10

5

10

40

(20)

30

(50)

(2)

(42)

59

151

210

(20)

30

0

(3)

7

31

169

200

CASH FLOW

Cash from Operations:

Net Income

Non-Cash Expenses

Depreciation

Amortization and other

Changes in Working Capital

Accounts Receivable Increase

Inventories Increase

Prepaid Expenses Increase

Accounts Payable Increase

Cash from Operations

Cash from Investing:

Securites: Buys

Securities: Maturing

Securities: Sales

Plant & Equipment Buys

Cash from Investing

Cash from Financing

Debt Paydown

Notes Payable Increase

Repurchase of Common Stock

Dividends Paid

Cash from Financing

Net Cash Flow

Cash: Beginning of Year

Cash: End of Year

As the manager of the pension fund, considering different investment options will help you make

better decisions for your company and your clients. Please respond to the following questions,

providing supporting data and showing your calculations.

Before starting your calculations, review the following materials:

time value of money analysis and The Time Value of Money

valuing perpetuities and annuities and Discounted Cash Flow Applications

amortizing a loan

Question 1: If the pension plan invests $95 million today in 10-year US Treasury bonds (riskless

investment with guaranteed return) at an interest rate of 3.5 percent a year, how much will it have by

the end of year 10?

Question 2: If the pension plan needs to accumulate $14 million in 13 years, how much must it

invest today in an asset that pays an annual interest rate of 4 percent?

Question 3: How many years will it take for $197 million to grow to be $554 million if it is invested in

an account with a quoted annual interest rate of 5 percent with monthly compounding of interest?

Question 4: The pension plan also invests in physical assets. It is considering the purchase of an

office building today with the expectation that the price will rise to $20 million at the end of 10 years.

Given the risk of this investment, there should be a yield of 10 percent annually on this investment.

The asking price for the lot is $12 million. What is the annual yield (internal rate of return) of the

investment if the purchase price is $12 million today and the sale price 10 years later is $20 million?

Should the pension plan buy the office building given its required rate of return?

Question 5a: The pension plan is also considering investing $70 million of its cash today at a 3.5

percent annual interest for five years with a commercial bank. How much will the $70 million grow to

at the end of 5 years?

Question 5b: Now take the amount of your answer in Ques 5a, and assume this money is invested

in an annuity due with the first payment made at the beginning of the 6th year. The annuity due

makes a total of 15 yearly (equal) payments. How much will the annual payments be from years 6 to

20, if the rate at which these payments are discounted is also 3.5 percent?

Question 6: The pension plan is about to take out a 10-year fixed-rate loan for the purchase of an

information management system for its operations. The terms of the loan specify an initial principal

balance (the amount borrowed) of $4 million and an APR of 3.75 percent. Payments will be made

monthly. What will be the monthly payment? How much of the first payment will be interest, and how

much will be principal? Use the Excel PMT function to provide the answers to these questions.

Submit your Time Value of Money Report and Calculations to the dropbox below. Be sure to show

your calculations in Excel and provide a narrative analysis in PowerPoint. Your narrative analysis

should summarize the results of your analysis and make recommendations for the benefit of the

company.

As the manager of the pension fund, you are frequently targeted by software companies peddling

investment simulation software. You have finally narrowed down your choice to two applications.

You need to analyze the options by calculating NPV, IRR, and Payback Period based on their

purchase price and savings to your company over time. Your staff has prepared a cash-flow table to

help you. Year zero shows the purchase price of each application, and the figures listed for years 13 represent the savings to the company in successive years.

Year

Application I

Application II

Year

Application I

Application II

0 (today)

-$1.5 million

-$1 million

1

$0.8 million

$0.5 million

2

$0.7 million

$0.24 million

3

$0.3 million

$0.6 million

You are considering three possible scenarios.

Question 7: If the payback period is two years, which application should be selected?

Question 8: If the required rate of return is 15 percent, which application should be selected?

Question 9: If the selection criterion is IRR, which application should be selected?

Respond to questions 7, 8, and 9 above by submitting a single, integrated report that shows your

supporting data and calculations. Finally, provide a recommendation and rationale for purchasing

either Application I or Application II.

Submit your Basic Capital Budget Analysis Report and Calculations to the dropbox below. Be sure to

show your calculations in Excel and provide a narrative analysis in PowerPoint. Your narrative

analysis should include your recommendation and rationale for purchasing either Application I or

Application II.

Another one of your responsibilities as CFO is to determine the suitability of new and current

products. Your CEO has asked you to evaluate Android01. That task will require you to combine

data from your production analysis from Project 2 with data from a consultant’s study that was done

last year. Information provided by the consultant is as follows:

initial investment: $120 million composed of $50 million for the plant and $70 million net

working capital (NWC)

yearly expenses from year 1 to year 3: $30 million

yearly revenues from year 1 to year 3: $0

yearly expenses from year 4 to year 10: $55 million

yearly expected revenues from year 4 to year 10: $95 million

yearly expenses from year 11 to year 15: $60 million

yearly expected revenues from year 11 to year 15: $105 million

You are to calculate NPV using the expected values. The actual cash flow may be variable

(risky) and that is the reason why the discount rate is greater than the riskless rate.

This concludes the information provided by the consultant.

You also have the following information:

Assume that both expenses and revenues for a year occur at the end of the year. NWC pays

the bills during the year, but has to be replenished at the end of the year.

Android01 is expected to cannibalize the sales of Processor01 while also reducing the

variable costs for the production of Processor01. From years 4 to 10, revenues are expected

to fall by $5M, whereas variable costs will go down by $1 million. Processor01 is to be

phased out at the end of the 10th year.

At the end of the 15th year, the plant will be scrapped for a salvage value of $10 million.

NWC will be recovered.

Question 10: Calculate the expected cash flows from the Android01 project based on the

information provided.

Question 11: Calculate the NPV for a required rate of return of 6.5 percent. Also calculate the IRR

and the Payback Period.

Before starting your calculations, review the following materials on NPV, IRR and Payback Period.

Also review:

Capital Budgeting

Cost of Capital

Submit your Cash Flow Report and Calculations to the dropbox below. Be sure to show your

calculations in Excel and provide a narrative analysis in PowerPoint. Your narrative analysis should

summarize the results of your analysis and make recommendations for the benefit of the company.

After you have submitted your Basic Captial Budget Analysis Report and Cash Flow Report, proceed

to the next step, where you will calculate the cost of capital and determine an appropriate capital

structure.

The firm decides to raise $30 million by selling equity and debt. The investment bankers hired by

your firm contact potential investors and come back with the following numbers:

Debt that pays $1 million coupons a year and $18 million maturity value after 10 years will

sell for $20 million.

Equity that pays expected dividends of $1.2 million starting next year and growing at a rate of

3 percent per year thereafter sells for $10 million.

Question 12: Calculate the cost of debt, equity, and the WACC.

Before starting your calculations, review the following materials:

cost of capital and choice of financing

equity, debt, and preferred stock

Submit your Cost of Debt Report and Calculations to the dropbox below. Be sure to show your

calculations in Excel and provide a narrative analysis in PowerPoint. Your narrative analysis should

summarize the results of your analysis and make recommendations for the benefit of company.

Finally, your firm has decided to spin off Android01 and Processor01 as a separate firm. The owners

of the new firm will be equity holders and debt holders. After speaking with potential investors,

investment banks have identified two possible capital structures (structure of equity and debt

ownership):

Debt holders receive debt that pays them coupons of $2 million a year, and $30 million after

20 years (these are expected values as the coupons and principal payments are not riskless,

the debt buyers realize the firms could default). They price the debt using a discount rate of 4

percent. Equity holders receive expected dividends of $3 million starting from year 5, and

growing at a rate of 4 percent per year (a growing perpetuity). They price the equity using a

discount rate of 7.5 percent.

Debt holders receive debt that pays them coupons of $1 million a year, and $12 million after

20 years (these are expected values as the coupons and principal payments are not riskless,

the debt buyers realize the firms could default). They price the debt using a discount rate of

3.5 percent. Equity holders receive expected dividends of $3.9 million starting from year 5,

and growing at a rate of 4.5 percent per year (a growing perpetuity). They price the equity

using a discount rate of 7 percent.

Your firm receives all the proceeds from the sale debt and equity. Since the firm is selling debt and

equity, it wants to sell using the capital structure that provides them with the most money (sum of

whatever debt and equity sells for).

Prepare a Capital Budgeting and Cost of Capital report that answers the following Question 13.

Question 13: Which particular capital structure should be chosen for the spin-off?

Here. the firm is the seller of a physical asset for which it gets all the money today. Therefore you do

not have to calculate NPV etc. It is not making an investment it is receiving money by selling the

subsidiary. You have to calculate the price at which debt sells and the price at which equity sells.

You have to calculate the price of debt using the annuity formula and the price of equity using the

growing perpetuity formula. Then add the two to get total money raised by selling subsidiary.

Whichever financing gives more total money should be the preferred financing.

Before starting your calculations, review the following materials:

cost of capital and choice of financing

equity, debt, and preferred stock

Submit your Capital Budgeting and Cost of Capital Report

…

Purchase answer to see full

attachment

Basic features

- Free title page and bibliography
- Unlimited revisions
- Plagiarism-free guarantee
- Money-back guarantee
- 24/7 support

On-demand options

- Writer’s samples
- Part-by-part delivery
- Overnight delivery
- Copies of used sources
- Expert Proofreading

Paper format

- 275 words per page
- 12 pt Arial/Times New Roman
- Double line spacing
- Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Delivering a high-quality product at a reasonable price is not enough anymore.

That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read moreEach paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read moreThanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more
The price is based on these factors:

Academic level

Number of pages

Urgency