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

profilewaaay_flyy

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. Show or explain how you obtained each answer. Please Provide Excel Sheet with formulas

  

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.99

  

$35.99

   
 

COGS each

$12.50

  

$12.50

   
 

S.G.& A.

$800,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

 

50,000

60,000

72,000

86,400

103,680

124,416

 

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

 

$1,799,500

$2,159,400

$2,591,280

$3,109,536

$3,731,443

$4,477,732

 

Cost of goods sold

 

($625,000)

($750,000)

($900,000)

($1,080,000)

($1,296,000)

($1,555,200)

 

Gross Margin

 

$1,174,500

$1,409,400

$1,691,280

$2,029,536

$2,435,443

$2,922,532

 

General, Sales and Admin.

 

($800,000)

($800,000)

($800,000)

($800,000)

($800,000)

($800,000)

 

Depreciation

 

($400,000)

($640,000)

($384,000)

($230,400)

($115,200)

($57,600)

 

EBIT

 

($25,500)

($30,600)

$507,280

$999,136

$1,520,243

$2,064,932

 

Income tax

 

$8,925

$10,710

($177,548)

($349,698)

($532,085)

($722,726)

 

Net income

 

($16,575)

($19,890)

$329,732

$649,438

$988,158

$1,342,206

         
 

Cash Flow Statement

       
 

Net Income

 

($16,575)

($19,890)

$329,732

$649,438

$988,158

$1,342,206

 

Add depreciation

 

$400,000

$640,000

$384,000

$230,400

$115,200

$57,600

 

Investment

(2,000,000)

      
 

Change in Working Capital

 

($179,950)

($35,990)

($43,188)

($51,826)

($62,191)

($74,629)

 

Salvage

      

$100,000

 

Tax on gain

      

$15,000

 

Cash flow

($2,000,000)

$203,475

$584,120

$670,544

$828,013

$1,041,167

$1,440,177

         
  

Present Worth =

IRR

     
  

$673,198

23.95%

    
    • 7 years ago
    • 8
    Answer(0)
    Bids(0)