Finance Question

Below is an Income and cash flow statements that management has approved.  (If there are errors or oversights, that is their problem, not yours).  Start each question from the original data.  Cells F14:F22 contain the original values in case you need to get back to them. 

a

Determine the unit price that would achieve a cash flow in year 6 of $500,000

b

Determine the sensitivity of the internal rate of return to a 10%, 25% and 50% increase in investment? (start with original values)

c

Determine the expected present worth for the following data where Forecast 1 is the current values. (start with original values)

Forecast 1 (current Values)

Forecast 2

Forecast 3

cell

Unit Price

$35.99

$40.00

$35.00

C14

COGS each

$12.50

$15.00

$11.50

C15

S.G. & A.

$800,000

$1,000,000

$900,000

C16

Sales Quantity Forecast year 1

50,000

40,000

60,000

D23

Probability

50%

30%

20%

Original Values

Unit Price

$35.00

$35.99

COGS each

$11.50

$12.50

S.G.& A.

$900,000

$800,000

salvage

$100,000

in year 6

$100,000

Income tax rate

35%

35%

Capital Gains Tax rate

15%

15%

Working capital

no change

no change

MARR

15%

15%

Investment

$2,000,000

$2,000,000

Sales Quantity Forecast

60,000

72,000

86,400

103,680

124,416

149,299

Depreciation MACRS

5

20.00%

32.00%

19.20%

11.52%

11.52%

5.76%

Income Statement

0

1

2

3

4

5

6

Sales revenue

$2,100,000

$2,520,000

$3,024,000

$3,628,800

$4,354,560

$5,225,472

Cost of goods sold

($690,000)

($828,000)

($993,600)

($1,192,320)

($1,430,784)

($1,716,941)

Gross Margin

$1,410,000

$1,692,000

$2,030,400

$2,436,480

$2,923,776

$3,508,531

General, Sales and Admin.

($900,000)

($900,000)

($900,000)

($900,000)

($900,000)

($900,000)

Depreciation

($400,000)

($640,000)

($384,000)

($230,400)

($115,200)

($57,600)

EBIT

$110,000

$152,000

$746,400

$1,306,080

$1,908,576

$2,550,931

Income tax

($38,500)

($53,200)

($261,240)

($457,128)

($668,002)

($892,826)

Net income

$71,500

$98,800

$485,160

$848,952

$1,240,574

$1,658,105

Cash Flow Statement

Net Income

$71,500

$98,800

$485,160

$848,952

$1,240,574

$1,658,105

Add depreciation

$400,000

$640,000

$384,000

$230,400

$115,200

$57,600

Investment

(2,000,000)

Change in Working Capital

($210,000)

($42,000)

($50,400)

($60,480)

($72,576)

($87,091)

Salvage

$100,000

Tax on gain

$15,000

Cash flow

($2,000,000)

$261,500

$696,800

$818,760

$1,018,872

$1,283,198

$1,743,614

Present Worth =

IRR

$1,266,952

30.88%