Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all

you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be

preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical

data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the

blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas,

usually the Given Data section.

Sora Industries has 60 million outstanding shares, $120 million in debt, $40 million in cash, and the following projected free cash flow for

the next four years:

Year

0

1

2

3

4

Earnings and FCF Forecast ($ millions)

Sales

433.0

468.0

516.0

547.0

574.3

Growth versus Prior Year

Cost of Goods Sold

Gross Profit

Selling, General, and Administrative

Depreciation

8.1%

10.3%

-345.7

170.3

-103.2

-7.5

6.0%

5.0%

-313.6

154.4

-93.6

-7.0

-366.5

180.5

-109.4

-9.0

-384.8

189.5

-114.9

-9.5

EBIT

53.8

-21.5

7.0

-7.7

-6.3

59.6

-23.8

7.5

-10.0

-8.6

24.6

62.1

-24.8

9.0

-9.9

-5.6

65.2

-26.1

9.5

-10.4

-4.9

33.3

Less: Income Tax at 40%

Plus: Depreciation

Less: Capital Expenditures

Less: Increase in NWC

Free Cash Flow

25.3

30.8

a. Suppose Sora’s revenue and free cash flow are expected to grow at a 5% rate beyond year 4. If Sora’s weighted average cost of

capital is 10%, what is the value of Sora’s stock based on this information?

b. Sora’s cost of goods sold was assumed to be 67% of sales. If its cost of goods sold is actually 70% of sales, how would the

estimate of the stock’s value change?

c. Let’s return to the assumptions of part (a) and suppose Sora can maintain its cost of goods sold at 67% of sales. However, now

suppose Sora reduces its selling, general, and administrative expenses from 20% of sales to 16% of sales. What stock price would

you estimate now? (Assume no other expenses, except taxes, are affected.)

d. Sora’s net working capital needs were estimated to be 18% of sales (which is their current level in year 0). If Sora can reduce this

requirement to 12% of sales starting in year 1, but all other assumptions remain as in part (a), what stock price do you estimate for

Sora? (Hint : This change will have the largest impact on Sora’s free cash flow in year 1.)

Shares outstanding (millions)

Debt (millions)

Cash (millions)

60

$120

$40

Tax rate

40%

a. Suppose Sora’s revenue and free cash flow are expected to grow at a 5% rate beyond year 4. If Sora’s weighted average cost of

capital is 10%, what is the value of Sora’s stock based on this information?

Long-run growth rate

Cost of capital

5%

10%

Year

0

1

2

3

4

Terminal value (millions)

Total cash flow (millions)

$0.0

$25.3

$24.6

$30.8

Enterprise value (millions)

Stock price

b. Sora’s cost of goods sold was assumed to be 67% of sales. If its cost of goods sold is actually 70% of sales, how would the

estimate of the stock’s value change?

Assumed COGS/sales

Actual COGS/sales

67%

70%

Year

0

1

2

3

4

Earnings and FCF Forecast (millions)

Sales

$433.0

$468.0

8.1%

$516.0

10.3%

$547.0

6.0%

$574.3

5.0%

Growth vs. Prior Year

Cost of Goods Sold

Gross Profit

Selling, General, and Administrative

Depreciation

-$93.6

-$7.0

-$103.2

-$7.5

-$109.4

-$9.0

-$114.9

-$9.5

EBIT

Less: Income Tax at 40%

Plus: Depreciation

Less: Capital Expenditures

Less: Increase in NWC

Free Cash Flow

$7.0

-$7.7

-$6.3

$7.5

-$10.0

-$8.6

$9.0

-$9.9

-$5.6

$9.5

-$10.4

-$4.9

Terminal value (millions)

Total cash flow (millions)

$0.0

Enterprise value (millions)

Stock price

c. Let’s return to the assumptions of part (a) and suppose Sora can maintain its cost of goods sold at 67% of sales. However, now

suppose Sora reduces its selling, general, and administrative expenses from 20% of sales to 16% of sales. What stock price would

you estimate now? (Assume no other expenses, except taxes, are affected.)

Assumed SG&A expenses/sales

Actual SG&A expenses/sales

20%

16%

Year

0

1

2

3

4

Earnings and FCF Forecast (millions)

Sales

$433.0

$468.0

$516.0

$547.0

$574.3

Growth vs. Prior Year

Cost of Goods Sold

Gross Profit

8.1%

-$313.6

$154.4

10.3%

-$345.7

$170.3

6.0%

-$366.5

$180.5

5.0%

-$384.8

$189.5

Selling, General, and Administrative

Depreciation

-$7.0

-$7.5

-$9.0

-$9.5

EBIT

Less: Income Tax at 40%

Plus: Depreciation

Less: Capital Expenditures

Less: Increase in NWC

Free Cash Flow

$7.0

-$7.7

-$6.3

$7.5

-$10.0

-$8.6

$9.0

-$9.9

-$5.6

$9.5

-$10.4

-$4.9

Terminal value (millions)

Total cash flow (millions)

$0.0

Enterprise value (millions)

Stock price

d. Sora’s net working capital needs were estimated to be 18% of sales (which is their current level in year 0). If Sora can reduce this

requirement to 12% of sales starting in year 1, but all other assumptions remain as in part (a), what stock price do you estimate for

Sora? (Hint: This change will have the largest impact on Sora’s free cash flow in year 1.)

Assumed NWC/sales

Actual NWC/sales

18%

12%

Initial value for NWC (millions)

Year

0

1

2

3

4

Earnings and FCF Forecast (millions)

Sales

$433.0

$468.0

$516.0

$547.0

$574.3

Growth vs. Prior Year

Cost of Goods Sold

Gross Profit

Selling, General, and Administrative

Depreciation

8.1%

-$313.6

$154.4

-$93.6

-$7.0

10.3%

-$345.7

$170.3

-$103.2

-$7.5

6.0%

-$366.5

$180.5

-$109.4

-$9.0

5.0%

-$384.8

$189.5

-$114.9

-$9.5

EBIT

$53.8

$59.6

$62.1

$65.2

Less: Income Tax at 40%

Plus: Depreciation

-$21.5

$7.0

-$23.8

$7.5

-$24.8

$9.0

-$26.1

$9.5

Less: Capital Expenditures

Less: Increase in NWC

Free Cash Flow

-$7.7

-$10.0

-$9.9

-$10.4

Terminal value (millions)

Total cash flow (millions)

$0.0

Enterprise value (millions)

Stock price

Requirements

1. Start Excel - completed.

2. In cell H36, by using cell references, calculate the terminal value of the company in year 4 (1 pt.).

3. In cell H37, by using cell references, calculate the total cash flow for year 4 (1 pt.).

4. In cell D39, by using cell references and the function NPV, calculate the enterprise value of the company under baseline

information (1 pt.).

5. In cell D40, by using cell references, calculate the price per share under baseline information (1 pt.).

6. In cells E51:H51, by using cell references, calculate the cost of goods sold for years 1:4, respectively (4 pt.).

Note: The outputs of the expression or function you typed in these cells are expected as negative numbers.

7. In cells E52:H52, by using cell references, calculate the gross profit for years 1:4, respectively (4 pt.).

8. In cells E55:H55, by using cell references, calculate the EBIT for years 1:4, respectively (4 pt.).

9. In cells E56:H56, by using cell references, calculate the income tax for years 1:4, respectively (4 pt.).

Note: The outputs of the expression or function you typed in these cells are expected as negative numbers.

10. In cells E60:H60, by using cell references, calculate the free cash flow for years 1:4, respectively (4 pt.).

11. In cell H61, by using cell references, calculate the terminal value of the company in year 4 (1 pt.).

12. In cells E62:H62, by using cell references, calculate the total cash flow for years 1:4, respectively (4 pt.).

13. In cell D64, by using cell references and the function NPV, calculate the enterprise value of the company under new information

(1) (1 pt.).

14. In cell D65, by using cell references, calculate the price per share under new information (1) (1 pt.).

15. In cells E78:H78, by using cell references, calculate the selling, general, and administrative expenses for years 1:4, respectively (4

pt.).

Note: The outputs of the expression or function you typed in these cells are expected as negative numbers.

16. In cells E80:H80, by using cell references, calculate the EBIT for years 1:4, respectively (4 pt.).

17. In cells E81:H81, by using cell references, calculate the income tax for years 1:4, respectively (4 pt.).

Note: The outputs of the expression or function you typed in these cells are expected as negative numbers.

18. In cells E85:H85, by using cell references, calculate the free cash flow for years 1:4, respectively (4 pt.).

19. In cell H86, by using cell references, calculate the terminal value of the company in year 4 (1 pt.).

20. In cells E87:H87, by using cell references, calculate the total cash flow for years 1:4, respectively (4 pt.).

21. In cell D89, by using cell references and the function NPV, calculate the enterprise value of the company under new information

(2) (1 pt.).

22. In cell D90, by using cell references, calculate the price per share under new information (2) (1 pt.).

23. In cell D97, by using cell references, calculate the initial value for the net working capital under new information (3) (1 pt.).

^24. In cells E111:H111, by using cell references, calculate the increase in net working capital for years 1:4, respectively (4 pt.).

25. In cells E112:H112, by using cell references, calculate the free cash flow for years 1:4, respectively (4 pt.).

26. In cell H113, by using cell references, calculate the terminal value of the company in year 4 (1 pt.).

27. In cells E114:H114, by using cell references, calculate the total cash flow for years 1:4, respectively (4 pt.).

28. In cell D116, by using cell references and the function NPV, calculate the enterprise value of the company under new information

(3) (1 pt.).

29. In cell D117, by using cell references, calculate the price per share under new information (3) (1 pt.).

30. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

Problem 9-12

?omplete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To

copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel

function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a

reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise

directed, use the earliest appearance of the data in your formulas, usually the Given Data section.

Procter and Gamble (PG) paid an annual dividend of $2.87 in 2018. You expect PG to increase its dividends by 8% per year for the next five years (through

2023), and thereafter by 3% per year. If the appropriate equity cost of capital for Procter and Gamble is 8% per year, use the dividend-discount model to

estimate its value per share at the end of 2018.

Annual dividend

Number of periods (years)

$2.87

5

Expected growth rate through 2023 (equal

to Cost of capital)

Expected growth rate thereafter 2023

8%

3%

PV of dividends through 2023

PV of the rest of the dividends in 2023

PV of the rest of the dividends in 2018

Value per share of P&G

Requirements

1. Start Excel – completed.

2. In cell E11, by using cell references, calculate the present value of dividends through 2023 (1 pt.).

3. In cell E12, by using cell references, calculate the present value of the rest of the dividends in 2023 (1 pt.).

4. In cell E13, by using cell references and the function PV, calculate the present value of the rest of the dividends in 2018 (1 pt.).

5. In cell E14, by using cell references, calculate the value per share of Procter and Gamble at the end of 2018 (1 pt.).

6. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

Problem 9-3

Suppose Acap Corporation will pay a dividend of $2.80 per share at the end of this year and a dividend of $3 per

share next year. You expect Acap’s stock price to be $52 in two years. Assume that Acap’s equity cost of capital

is 10%.

Complete the steps below using cell references to given data or previous calculations. In some cases, a

simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute

cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the

directions will specify the use of that function. Do not type in numerical data into a cell or function.

Instead, make a reference to the cell in which the data is found. Make your computations only in the blue

cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in

your formulas, usually the Given Data section.

a. What price would you be willing to pay for a share of Acap stock today, if you planned to hold the stock for

two years?

b. Suppose instead you plan to hold the stock for one year. What price would you expect to be able to sell a

share of Acap stock for in one year?

c. Given your answer in part (b), what price would you be willing to pay for a share of Acap stock today, if

you planned to hold the stock for one year? How does this price compare to your answer in part (a)?

Dividend in 1 year

Dividend in 2 years

Share price in 2 years

Equity cost of capital

$2.80

$3.00

$52.00

10%

a. What price would you be willing to pay for a share of Acap stock today, if you planned to hold the stock for

two years?

Holding period (years)

2

Price per share

b. Suppose instead you plan to hold the stock for one year. What price would you expect to be able to sell a

share of Acap stock for in one year?

Holding period (years)

1

Price per share

c. Given your answer in part (b), what price would you be willing to pay for a share of Acap stock today, if

you planned to hold the stock for one year? How does this price compare to your answer in part (a)?

Price per share

The price you would pay

affected by the amount of time you hold the stock.

Requirements

1. Start Excel - completed.

2. In cell D18, by using cell references, calculate the price that you would be willing to pay for the stock if

you plan to hold it for two years (1 pt.).

3. In cell D24, by using cell references, calculate the price at which you would be able to sell the stock in one

year (1 pt.).

4. In cell D28, by using cell references, calculate the price that you would be willing to pay for the stock today

if you plan to hold it for one year (1 pt.).

5. In cell D29, select whether the price you would pay is or is not affected by the holding period (1 pt.).

6. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

Problem 6-13

Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell

reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed

cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that

function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is

found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the

earliest appearance of the data in your formulas, usually the Given Data section.

Consider the following bonds:

Coupon Rate

(annual payments)

Maturity

(years)

15

Bond

A

B

C

D

0%

0%

4%

8%

10

15

10

a. What is the percentage change in the price of each bond if its yield to maturity falls from 6% to 5%?

b.

Which of the bonds A–D is most sensitive to a 1% drop in interest rates from 6% to 5%? Which bond is the least sensitive?

Par value

Old YTM

New YTM

$1,000

6%

5%

a. What is the percentage change in the price of each bond if its yield to maturity falls from 6% to 5%?

Coupon

Rate

Annual

Coupon

Bond

A

B

C

D

Maturity

Old Price

New Price

Change

Rank

0%

15

10

15

10

0%

4%

8%

b.

Which of the bonds A–D is most sensitive to a 1% drop in interest rates from 6% to 5%? Which bond is the least sensitive?

Bond

Bond

is the most sensitive to changes in bond yields.

is the least sensitive to changes in bond yields.

Requirements

1. Start Excel – completed.

2. In cell F23, by using cell references, calculate the annual coupon payment of bond A (1 pt.).

3. To calculate the annual coupon payment of bonds B thru D, copy cell F23 and paste it onto cells F24:F26 (1 pt.).

4. In cell G23, by using cell references and the function PV, calculate the price of bond A before the fall in the yield to

maturity (1 pt.).

Note: The output of the expression or function you typed in this cell is expected as a positive number. Use cell reference to

the annual coupon payment from Step 2 in your calculations.

5. To calculate the price of bonds B thru D before the fall in the yield to maturity, copy cell G23 and paste it onto cells

G24:G26 (1 pt.).

6. In cell H23, by using cell references and the function PV, calculate the price of bond A after the fall in the yield to maturity

(1 pt.).

Note: The output of the expression or function you typed in this cell is expected as a positive number. Use cell reference to

the annual coupon payment from Step 2 in your calculations.

7. To calculate the price of bonds B thru D after the fall in the yield to maturity, copy cell H23 and paste it onto cells

H24:H26 (1 pt.).

8. In cell I23, by using cell references, calculate the percentage change in price due to the drop in the yield to maturity of bond

A (1 pt.).

Note: Use cell references to the price of bonds before and after the fall in the yield to maturity from Steps 4 and 6 in your

calculations.

9. To calculate the percentage change in price due to the drop in the yield to maturity of bonds B thru D, copy cell I23 and

paste it onto cells I24:I26 (1 pt.).

Note: Use cell references to the price of bonds before and after the fall in the yield to maturity from Steps 5 and 7 in your

calculations.

10. In J23, by using cell references and the function RANK.EQ, rank the change in price due to the drop in the yield to

maturity of bond A (1 pt.).

Note: The function RANK.EQ returns the rank of a number in a list of numbers. Its size is relative to other values in the

list; if more than one value has the same rank, the top rank of that set of values is returned.

11. To rank the percentage change in price due to the drop in the yield to maturity of bonds B thru D, copy cell J23 and paste it

onto cell J24:J26 (1 pt.).

12. In cell D30, find the bond that is most sensitive to changes in the bond yields. Select A, B, C, or D. (1 pt.).

13. In cell D31, find the bond that is least sensitive to changes in the bond yields. Select A, B, C, or D. (1 pt.).

14. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

Problem 6-6

Suppose a 10-year, $1,000 bond with an 8% coupon rate and semiannual coupons is trading for

$1,034.74.

Complete the steps below using cell references to given data or previous calculations. In some

cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a

column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel

function is to be used, the directions will specify the use of that function. Do not type in numerical

data into a cell or function. Instead, make a reference to the cell in which the data is found. Make

your computations only in the blue cells highlighted below. In all cases, unless otherwise directed,

use the earliest appearance of the data in your formulas, usually the Given Data section.

a. What is the bond’s yield to maturity (expressed as an APR with semiannual compounding)?

b. If the bond’s yield to maturity changes to 9% APR, what will the bond’s price be?

Maturity (years)

Face value

Coupon rate

Bond price

10

$1,000.00

8%

$1,034.74

^a. What is the bond’s yield to maturity (expressed as an APR with semiannual compounding)?

Coupon

Number of periods

Yield to maturity

b. If the bond’s yield to maturity changes to 9% APR, what will the bond’s price be?

Yield to maturity

9%

Semiannual yield

Bond price

Requirements

1. Start Excel - completed.

2. In cell D15, by using cell references, calculate the coupon payment of the bond (1 pt.).

3. In cell D16, by using cell references, calculate the number of periods until maturity (1 pt.).

4. In cell D17, by using cell references and the function RATE, calculate the yield to maturity of

the bond (1 pt.).

Note: Do not enter any value for the Guess argument of the function RATE.

5. In cell D23, by using cell references, calculate the semiannual yield to maturity of the bond (1

6. In cell D24, by using cell references and the function PV, calculate the new price of the bond (1

pt.).

Note: The output of the expression or function you typed in this cell is expected as a positive

number.

7. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

Problem 6-3

Complete the steps below using cell references to given data or previous calculations. In some cases,

a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an

absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to

be used, the directions will specify the use of that function. Do not type in numerical data into a cell

or function. Instead, make a reference to the cell in which the data is found. Make your

computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the

earliest appearance of the data in your formulas, usually the Given Data section.

The table below summarizes prices (per $100 face value) of various default-free, zero-coupon bonds

(expressed as a percentage of face value):

a. Compute the yield to maturity for each bond.

b. Plot the zero-coupon yield curve (for the first five years).

c. Is the yield curve upward sloping, downward sloping, or flat?

Maturity

Face value

$100.00

(years)

Price

$95.51

$91.05

$86.38

$81.65

$76.51

1

2

3

4

5

a. Compute the yield to maturity for each bond.

YTM 1-year bond

YTM 2-year bond

YTM 3-year bond

YTM 4-year bond

YTM 5-year bond

b. Plot the zero-coupon yield curve (for the first five years).

c. Is the yield curve upward sloping, downward sloping, or flat?

The yield curve is:

Requirements

1. Start Excel - completed.

2. In cells E19:E23, by using cell references and the function RATE, calculate the yield to maturity

for the bonds with maturity 1:5 years, respectively (5 pt.).

Note: Do not enter any value for the Guess argument of the function RATE.

^3. In cells C27:F38, insert a Line with Markers Chart to show the zero-coupon yield curve for the

first five years.

Inserting a Chart

On the Insert tab, in the Charts group, click the arrow next to Insert Line or Area Chart and

choose Line with Markers Chart.

Selecting Data Series

Then in Select Data Source window, delete any series created automatically.

Add new series for the zero-coupon yield curve using cells E19:E23 for the series values. Do not

name the series.

Edit Chart Elements

Add a chart title and choose the Above Chart option. Replace Chart Title with Yield Curve. Add

axis titles. Replace Axis Title for the horizontal axis with Maturity (years) and Axis Title for the

vertical axis with YTM (3 pt.).

4. In cell E42, select either upward sloping, downward sloping, or flat depending on the shape of

the yield curve (1 pt.).

5. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.