finance questions

Question 1

The CEO of Orange, Inc.  is evaluating a proposal for outsourcing all their internal part production and focusing internal operations only on assembly of the parts.    The cost to convert to outsourcing ,forecast at $350,000, is not depreciable and will be expensed (paid for) in year 1 in S.G.& A.

The proposal is forecasted to reduce the unit cost of the parts by 15% in year 1 and then the cost will be constant at the reduced level in the future.  The additional purchasing effort is expected to increase S.G.& A. expense by 8% in year 1 and hold constant at the the new level in the future.  

This sales price of their single product will be reduced by 5% in year 1 and then held constant at this level in the future.  The price decrease is forecasted to increase sales quantities as shown below.  Without the proposal the  sales price, costs to produce, S.G.& A., and sales are all expected to stay the same as present. 

Construct the Income statement part of a financial analysis that could be used to evalaute the proposal.  The time horizon for this proposal is 5 years.  Show all subtotals in the income statement.

Year

1

2

3

4

5

Sales Quantity- with proposal

126,000

132,300

138,915

145,861

153,154

Sales Quantity - without proposal

120,000

120,000

120,000

120,000

120,000

Present

one-time change

Proposed

Sales Price

$18.99

5%

less

Machined parts cost per unit

$4.40

15%

less

Assembly cost per unit

$5.60

0%

no change

S.G. & A

$150,000

8%

 increase

Cost of proposal

$350,000

Proposal life span

5

years

Income tax

14%

annually

MARR

10%

annually

Modern Medical Mechanics (M³) has to replace a production machine used to produce a single part. They need an evalaution of two alternative machine models.  The forecasted demand for the part is shown below. The choice of machine will not affect this demand. 

Both of the new machines will last four years and then be replaced with the salvage values at the end of 4 years that is listed below.   Depreciation will be 5-year MACRS The two machines have the prices, production cost per part, and salvage value in year 4 as shown below.  All setup of the new machines is part of the purchase price.

Using a 4-year time horizon, document and make a recommendation of which machine should be adopted from a financial perspective.  Assume that there will not  be any change in S.G. & A or working capital resulting from this decision.

Machine Platinum

Machine Titanium

Purchase price

$188,000

$223,000

Salvage value

$50,000

$60,000

Production cost per part

$14.88

$12.45

Years

0

1

2

3

4

Annual part usage

0

55,000

60,000

62,500

95,000

Time horizon

4

years

Income tax rate

15.00%

Capital gains tax rate

15.00%

MARR

12.50%

Depreciation

5

MACRS years

0

1

2

3

4

5

6

MACRS

20.00%

32.00%

19.20%

11.52%

11.52%

5.76%

$0

Josephine has been through several start up situations and has the time, wealth and connections to do another one.  She is now planning the next startup that looks very promising with sales expected to double every year. Following is the information that has been collected and the expected income statement.  The investments, depreciation and book value results are provided below and are to be used as they are presented.

Help Josephine with her 5-year plan by preparing a cash flow statement and determine the present worth and future worth of this proposal.

Revenue in year 0

$1,500,000

Company worth at end of year 5

$125,000,000

Income Tax rate

20%

Capital Gains Tax rate

12%

MARR

20%

Year

0

1

2

3

4

5

Annual Investment

$5,000,000

$10,000,000

$20,000,000

$30,000,000

$30,000,000

$0

Annual Depreciation

$1,000,000

$5,200,000

$11,040,000

$17,776,000

$24,048,000

Book Value

$5,000,000

$14,000,000

$28,800,000

$47,760,000

$59,984,000

$35,936,000

Working Capital

0

1

2

3

4

5

Accounts Receivable

$0

$250,000

$400,000

$550,000

$700,000

$850,000

Inventory

$10,000

$100,000

$150,000

$200,000

$250,000

$300,000

Accounts Payable

($20,000)

($180,000)

($180,000)

($180,000)

($180,000)

($180,000)

Wages Payable

($5,000)

($50,000)

($65,000)

($80,000)

($95,000)

($110,000)

Total

($15,000)

$120,000

$305,000

$490,000

$675,000

$860,000

Change in Working Capital

$135,000

$185,000

$185,000

$185,000

$185,000

Income Statement

0

1

2

3

4

5

Revenue

$3,000,000

$6,000,000

$12,000,000

$24,000,000

$48,000,000

COGS

($900,000)

($1,800,000)

($3,600,000)

($7,200,000)

($14,400,000)

Gross Margin

$2,100,000

$4,200,000

$8,400,000

$16,800,000

$33,600,000

SG&A

($180,000)

($315,000)

($551,250)

($964,688)

($1,688,203)

Depreciation

($1,000,000)

($5,200,000)

($11,040,000)

($17,776,000)

($24,048,000)

EBIT

$920,000

($1,315,000)

($3,191,250)

($1,940,688)

$7,863,797

Tax

($184,000)

$263,000

$638,250

$388,138

($1,572,759)

Net Income

$736,000

($1,052,000)

($2,553,000)

($1,552,550)

$6,291,038

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%

The nonprofit Center for Original Oxygen  (CO2)  has decided to concentrate its efforts to a particular means of reducing carbon dioxide thereby enabling a return to the original proportion of oxygen in the environment.  Several alternatives have been researched and the cost and benefits have been determined. They have determined that a good representative measure (surrogate) of benefits is the expected cost savings from lower expenditures on respiratory disease medical expenses.  The next step is to evaluate alternative methods of reducing carbon dioxide and choose one to support.  The data below has been gathered to assist in a choice.  (Data are completely fictitious)

Perform a financial analysis for CO2 to determine which one should be chosen from a financial perspective.

Time span

10

years

MARR

7%

Investment

Annual Gross Benefits

Annual Cost of Operations

Electric Cars

$80,000

$80,000

$50,000

Home Fuel Cells

$150,000

$100,000

$28,000

Coal conversion

$100,000

$70,000

$35,000

Oil conversion

$90,000

$80,000

$30,000

CO2 capture and storage

$120,000

$80,000

$46,000

The city bidge inspection team had noted 4 bridges that need replacement. Resources are available to do one of these.  Proposals to repair them have been received as follows.    Also shown are the number of vehicles per year that use each bridge that is an indicator of the size of the problem if the bridge would fail.   Using a 10 year time span and a discount rate of 7%, which one should be chosen.

Proposal

Costs to Replace

Cars per year

Oak Street Bridge

$19,000,000

14,500,000

2nd Ave. Bridge

$20,000,000

14,000,000

River Street Bridge

$21,500,000

16,000,000

Lincon Avenue Bridge

$22,000,000

16,500,000