Toggle DrawerOverviewComplete a series of four problems in which you calculate the prices of dividends and stocks for hypothetical companies.Note: The assessments in this course build upon each other, so you are strongly encouraged to complete them in sequence.SHOW LESSBy successfully completing this assessment, you will demonstrate your proficiency in the following course competencies and assessment objectives:Competency 1: Apply the theories, models, and practices of finance to the financial management of the firm.Calculate accurately the ex-dividend price of a stock.Calculate accurately the price of a stock after a stock split, stock dividend, and reverse stock split.Calculate accurately the future value of a dividend.Competency MapCHECK YOUR PROGRESSUse this online tool to track your performance and progress through your course.Toggle DrawerQuestions to ConsiderTo deepen your understanding, you are encouraged to consider the questions below and discuss them with a fellow learner, a work associate, an interested friend, or a member of the business community.SHOW MOREToggle DrawerResourcesSuggested ResourcesThe resources provided here are optional. You may use other resources of your choice to prepare for this assessment; however, you will need to ensure that they are appropriate, credible, and valid. They provide helpful information about the topics in this unit. The MBA-FP6016 Finance and Value Creation Library Guide can help direct your research. The Supplemental Resources and Research Resources, both linked from the left navigation menu in your courseroom, provide additional resources to help support you.The following resources will provide assistance to complete the assessment.Assessment Problems Helpful Tips [DOCX].Excel Examples [XLS].The following texts are designed to assist learners to master core concepts, solve financial problems, and analyze results.Boundless. (n.d.). Boundless finance. Retrieved from https://www.boundless.com/finance/textbooks/boundl…Chapter 15, “Introduction to Dividends”.SHOW MOREAssessment InstructionsDemonstrate your understanding of financial concepts by completing the following problems. Where appropriate, show or explain your work. You may use Excel to work on the problems.Problem 1. Dividends and taxes: Midland Incorporated has declared a $6.50 per share dividend and the stock is about to go ex-dividend. Calculate the ex-dividend price based on the following data:Capital gains are not taxed.There is a 12 percent tax rate on dividends.Taxes must be withheld at the time the dividend is paid, according to new IRS regulations.Midland sells for $76 per share.Problem 2. Stock splits and stock dividends: Billings Corporation (BC) currently has 365,000 shares of stock outstanding that sell for $72 per share. Calculate the share price for each of the following scenarios:After BC has a five-for-three stock split.After BC has a 12 percent stock dividend.After BC has a 45 percent stock dividend.After BC has a five-for-seven reverse stock split.Problem 3. Dividend smoothing: The Deck Company just paid a dividend of $1.55 per share of stock. Its target payout ratio is 45 percent. In one year, the company expects to have earnings per share of $7.10. If the adjustment rate is .4, as defined in the Lintner model, what will the dividend be one year from now?Problem 4. Stock dividends: The market value balance sheet for Outland Manufacturing is shown in the table below. Outland has declared a 20 percent stock dividend. The stock goes ex-dividend tomorrow. (Chronology for a stock dividend is similar to that for a cash dividend.) There are 45,000 shares of stock outstanding. Calculate the ex-dividend price.Problem 4. Stock Dividends: Market Value Balance SheetCash: $165,000Debt: $177,000Fixed Assets: $658,000Equity: $646,000Total: $823,000Total: $823,000Dividends and Stocks Scoring GuideVIEW SCORING GUIDEUse the scoring guide to enhance your learning.Dividends and Stocks Scoring GuideCRITERIANON-PERFORMANCEBASICPROFICIENTDISTINGUISHEDCalculate accurately the ex-dividend price of a stock.Does not calculate the ex-dividend price of a stock.Calculates the ex-dividend price of a stock; however, there are errors with the calculations.Calculates accurately the ex-dividend price of a stock.Calculates the ex-dividend price of a stock; uses the appropriate methods to arrive at accurate calculations for all of the problems.Calculate accurately the price of a stock after a stock split, stock dividend, and reverse stock split.Does not calculate the price of a stock after a stock split, stock dividend, and reverse stock split.Calculates the price of a stock after a stock split, stock dividend, and reverse stock split; however, there are errors with some or all of the calculations.Calculates the price of a stock after a stock split, stock dividend, and reverse stock split.Calculates accurately the price of a stock after a stock split, stock dividend, and reverse stock split; uses the appropriate methods to arrive at accurate calculations for all of the problems.Calculate accurately the future value of a dividend.Does not calculate the future value of a dividend.Calculates the future value of a dividend; however, there are errors with the calculations.Calculates accurately the future value of a dividend.Calculates the future value of a dividend; uses the appropriate methods to arrive at accurate calculations for all of the problems.

assessment_7_excel_examples.xls

assessment_problems_helpful_tips__assessment_3.docx

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

Dividends and Stocks

Just from $13/Page

Unformatted Attachment Preview

Excel Examples

EXAMPLE 1:

Discount Rate

Year 0

Year 1

Year 2

Year 3

Year 4

Year 5

ANSWER: NPV

EXAMPLE 2:

Year 0

Year 1

Year 2

Year 3

Year 4

Year 5

ANSWER

EXAMPLE 3:

Finance Rate

Reinvestment Rate

ANSWER

Beginning in column A, there are 16 examples of how to calculate basic accounting and math equations in Excel. Column C includes annotations for some of the examples.

NET PRESENT VALUE

12%

-65

10

20

40

65

-20

$18.30

Don’t include the year 0 cash flow of -$65 (cell B6) because the payments occur at the

BEGINNING of the first period.

INTERNAL RATE OF RETURN (IRR)

-65

10

20

40

65

-20

22.41%

MODIFIED INTERNAL RATE OF RETURN (MIRR)

12%

15%

18.12%

EXAMPLE 4:

Year 0

Year 1

Year 2

Year 3

Year 4

Year 5

ANSWER

PRESENT VALUE

EXAMPLE 5:

Year 0

Year 1

Year 2

Year 3

Year 4

Year 5

ANSWER

FUTURE VALUE

12%

5

100

Note: Use the yearly data from Example 2.

Discount $100 back 5 years at a 12% discount rate.

(Discount Rate)

(# Periods or years being discounted)

(FV)

$56.74

12%

5

100

$176.23

Compound $100 up 5 years at a 12% discount rate.

(Discount Rate)

(# Periods or years being compounded)

(PV)

EXAMPLE 6:

Present Value =

Future Value =

Discount Rate

ANSWER

FINDING N (NPER) NUMBER OF PERIODS (OR YEARS)

$50

$100

12%

6.12

How long would it take to compound $50 up to $100 using a 12% discount rate?

Or 6.12 years. Note that you have to make either the Future Value or Present Value input

negative for the formula to work.

How long would it take to discount $100 down to $25 using a 12% discount rate?

Present Value =

Future Value =

Discount Rate

$100

$25

12%

ANSWER

-12.23

EXAMPLE 7:

Present Value =

Future Value =

N (Nper)

ANSWER

Payment (PMT) =

Future Value =

N (Nper)

ANSWER

EXAMPLE 8:

Present Value =

Future Value =

N (Nper)

Interest Rate

ANSWER

EXAMPLE 9:

SUM

AVERAGE

VARIANCE

STANDARD DEVIATION

CORRELATION

COVARIANCE

FINDING I (INTEREST RATE)

$100

$200

5

14.87%

$100

$750

5

20.40%

Or 12.23 years. Note that years cannot be negative. You have to make either the Future Value

or Present Value input negative for the formula to work.

If you start with $100 and end with $200 after 5 years, what was the annual interest rate

earned?

Or 14.87%. You must keep either the Present Value or Future Value input negative.

If you receive payments of $100 each year for 5 years and end up with $750 after 5 years,

what was the annual interest rate earned?

Or 20.40%. Note that the Payment input or Future Value input must be negative for the

formula to work.

FINDING THE PAYMENT AMOUNT (PMT) OR ANNUITY AMOUNT

$0

$100,000

20

12%

$1,387.88

SUM, AVERAGE, VARIANCE, STANDARD DEVIATION,

CORRELATION, COVARIANCE

0.12

0.15

0.08

0.06

0.08

0.4900

0.0980

0.0013

0.0363

0.3928

0.0012

What would have to be the annual payment amount (or annuity amount) to have $100,000

after 20 years with a 12% discount rate?

Note: You want the Future Value input to be negative so your answer comes out positive.

0.09

0.11

0.15

0.03

-0.12

EXAMPLE 10:

CALCULATING A BOND’S PRICE

Suppose we have a bond with 22 years to maturity, a coupon rate of 8 percent, and a yield to maturity of 9 percent. If the bond makes semiannual payments, what is its price today?

Settlement

Maturity

Rate

YTM

1/1/00

1/1/22

0.08

0.09

Redemption

100

Frequency

Basis

Bond Price

2

0

90.49

Multiply by 10

904.91

Think of Settlement as the beginning of the duration of the bond.

Think of Maturity as the end of the duration of the bond.

(Coupon Rate)

(Yield to Maturity or Required Rate of Return)

(Bond’s Face Value, Par Value, or Fair Price)

Note that it is $100, not $1,000. You make the adjustments by multiplying the answer by 10.

Coupon payments are semiannual, so you put in a 2. If they are annual, then you input a 1.

Always leave it blank.

The answer, but you need to multiply it by 10 to get the actual bond price.

Note: Excel gives the bond price in 2 digits like in cell B109. You need to multiply it by 10 to get

the actual bond price.)

(ANSWER = 904.91)

EXAMPLE 11:

CALCULATING A BOND’S YIELD TO MATURITY

Suppose we have a bond with 22 years to maturity, a coupon rate of 8 percent and a price of $960.17. If the bond makes semiannual payments, what is its yield to maturity?

Settlement

Maturity

Rate

Pr

1/1/00

1/1/22

0.08

96.017

Redemption

100

Frequency

Basis

Yield to Maturity

2

0

8.40%

Think of Settlement as the beginning of the duration of the bond.

Think of Maturity as the end of the duration of the bond.

(Coupon Rate)

The bond’s price per $100 face value.

(Bond’s Face Value, Par Value, or Fair Price)

Note that it is $100, not $1,000.

Coupon payments are semiannual, so you put in a 2. If they are annual, then you input a 1.

Always leave it blank.

(ANSWER = 8.40%)

EXAMPLE 12:

CALCULATING THE EFFECTIVE ANNUAL INTEREST RATE

Suppose you have a Nominal Interest Rate of 5.25% that is compounded quarterly (4 times) during the year. What is the Effective Annual Interest Rate?

Nominal Interest Rate

Npery

Effective Annual Interest Rate

5.25%

4

5.3543%

(Number of compounding periods per year)

Note: The EAR is always higher than the Nominal Rate as long as there is more than 1

compounding period per year. If you increase the compounding periods per year, the Effective

Annual Rate will increase, but at a decreasing rate.

(ANSWER = 5.35%)

EXAMPLE 13:

CALCULATING THE ANNUAL NOMINAL INTEREST RATE

Suppose you have an Effective Annual Interest Rate of 5.35% that is compounded quarterly (4 times) during the year. What is the Nominal Annual Interest Rate?

Effective Annual Interest Rate

Npery

Nominal Annual Interest Rate

5.35%

4

5.2459%

(Number of compounding periods per year)

(ANSWER = 5.25%)

CALCULATING THE INTEREST RATE PER PERIOD OF A LOAN

OR AN INVESTMENT

EXAMPLE 14:

If you make monthly payments of $200 on an $8,000 loan over 4 years, what is the Annual Interest Rate of the loan?

4

-200

8000

Years of the Loan

Monthly Payment

Amount of the Loan

Monthly Interest Rate of the Loan

0.77%

Annual Interest Rate of the Loan

9.24%

Note: Multiply the years of the loan by 12 months for the monthly rate.

(ANSWER = .77%)

Note: Multiply the Monthly Interest Rate by 12 to get the annual rate.

(ANSWER = 9.24%)

EXAMPLE 15:

CALCULATING THE GEOMETRIC AVERAGE RETURN (OR MEAN)

A stock has produced returns of 14.6 percent, 5.3 percent, 17.6 percent, and -4.7 percent over the past four years, respectively. What is the geometric average return?

Year 1

Year 2

Year 3

1.146

1.053

1.176

0.953

7.84%

EXAMPLE 16:

Adding cell B163 to cell B164:

Subtracting cell B163 from cell B164:

Multiplying cell B163 by cell B164:

Dividing cell B164 by cell B163:

Using Parentheses: Multiplying cell B163

by (cell B164 + cell B165):

Calculating cell B163 to the power of cell

B164:

Calculating the Square Root of cell B171:

Calculating the Natural Logarithm of cell

B171:

End of worksheet

SIMPLE MATH CALCULATIONS

2

2

5

4

0

4

1

14

4

2

1.3863

Add 1 to all positive returns.

Add 1 to all positive returns.

Add 1 to all positive returns.

For negative returns, subtract it from 1. You have to do this to keep all data positive.

Note: Place a minus 1 after the formula to get rid of the whole number.

(ANSWER = 7.84%)

MBA-FP6016

Assessment Problems Helpful Tips

Assessment 2 Financial Statements

Use the inputs provided in the Excel spreadsheet to come up with formula inputs.

Basic accounting equations and formulas:

?????? – ??????????? = ??????. This formula can be manipulated with simple algebra to

place any of the three inputs separately on one side of the equation.

The equity multiplier formula starts out as ????? ??????/????? ??????, but it is derived

into the following formula: 1 + ???? – ?????? ?????.

The return on equity formula is ??? ??????/????? ??????, but it can be derived into the

following formula: ??? × ?????? ??????????.

Assessment 3 Calculating Financial Values

For all of the problems, be sure to use the correct built-in Excel formulas to derive your

answers. Do not use algebra.

Assessment 4 Calculating Payback and Profitability

Problem 1: When you calculate NPV using the built-in NPV formula in Excel, be sure to place

the year 0 cash outflow outside of the parentheses in the formula because the payment

occurs at the beginning of the first period.

Problem 2: The payback period formula is the amount of time it takes to recover the cost of

the project or investment. The formula to use is ???? ?? ???????/??????? ???h ???????.

Problem 4: When you find the NPV as the first step of calculating the profitability index, be

sure to exclude using the year 0 cash outflow (initial cost), using the built-in NPV formula in

Excel. When calculating the profitability index, make sure the year 0 initial cost is a positive

number.

Problem 5: When calculating the operating cash flow, use this formula:

??? = (????? – ?????) × (1 – ??? ????) + ???????????? × ??? ????

First, you will have to multiply a few of the inputs given in the assessment to come up with

some of the formula inputs. For the first part of the formula, you need to multiply the number of

servings produced per year by the price of each ice cream serving to derive the sales, which

are not given in their entirety in the problem. You need to find this in order to derive the costs

and depreciation.

Assessment 6 Calculating Risks and Returns

Problem 8: Under CAPM, the cost of equity (or expected return on equity) formula is:

???? ???? ???? + ???? × (???????? ?????? ?? ?h? ?????? – ???? ???? ????)

1

MBA-FP6016

Assessment 7 Dividends and Stocks

Problem 2: If the stock split increases the number of shares, then the stock price has to be

lower than it originally was. If it is a reverse stock split, where number of shares decrease,

then the stock price must be higher than it originally was.

Problem 3: For this problem, rearrange what is known as the Lintner formula. You want to

isolate Div1 (the dividend one year from now) so the formula should look like the following:

Div1 = Div0 + s*(t *EPS1 Div0) (Note: The * indicates multiplying.)

???1 = ???0 + ?????????? ????(?????? ????? × ???1 – ???0)

Assessment 8 Megaware Case Study

Problem 5: Use what you know about the situation and the theory to come up with an answer

that seems appropriate according to the theory. Do not focus on having all exact numbers to

accomplish everything. The final number is not what is important. What does each factor

stand for and what happens if one chooses one of the possibilities for each factor individually?

?0 = ?1(1 – ?)

?? = ??? × ?

What each variable stands for is known in problem 5:

b = retention ratio.

E1 = earnings next year.

ROE = return on equity.

Dividend-payout ratio = 1 minus b.

P0 = dividend next year; the earnings next year times 1 minus the retention ratio.

Rs = sustainable growth rate; the return on equity times the retention ratio.

Eight years in from the start of the company, the profit is $42 million from the sale of a fixed

asset.

What happens to P0 and ROE if all other factors remain steady and b goes up or

down?

What effect does the one-time influx of $42 million have on the formula?

Assessment 9 Financing and Exchange Rates

Problem 1: When calculating the cash for this problem, use the following given data to derive

the answer:

Net worth.

Long-term debt.

Net working capital (excluding cash).

Fixed assets.

Problem 2:

For the first calculation, just find the dollar value of the current shares outstanding and

add that to the value for the rights offering using the given data.

For the second calculation, you will use the number of shares outstanding and number

of new shares outstanding in the future (rights offering) to derive the answer.

2

MBA-FP6016

For the third calculation, you will use the new market value of the company, the

number of shares outstanding, and the number of new shares outstanding in the future

(rights offering) to derive the answer.

For the last calculation, you will use the current stock price and ex-rights price to

derive the answer.

3

…

Purchase answer to see full

attachment

Why Work with Us

Top Quality and Well-Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional and Experienced Academic Writers

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

Free Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.

Prompt Delivery and 100% Money-Back-Guarantee

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Admissions

Admission Essays & Business Writing Help

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Reviews

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Reviews

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.