finance assignment

profileLeila@
attachments_20161116.zip
Home>Business & Finance homework help>finance assignment

capital_budgeting_project_0.xlsx

Part 1

a)
Initial Investment
Cost of land 1,000,000
Building and equipment, 2,000,000
Organizational expenses - 0
Initial net working capital 200,000
Total Investment Year (0) 3,200,000
b)
Assumptions
Selling Price 100
Direct material % of Sale 40%
Labor % of Sale 10%
Annual Admin Cost (Fixed) 50,000.00
Tax rate 35%
Infltion (annual) Cost and Prices 3%
Annual Sales Growth (Units) 10%
Depreciation Marcs Line 10 Years
Project Life 10 Years
Discount Rate 10%
Additional WC requirement % of Sales for 3 yearsthen constant 5%
Year Annual Unit sales Monthly sales in Units
1 12,000 1,000
2 13,200 1,100
3 14,520 1,210
Working
Year 1 2 3 4 5 6 7 8 9 10 11 Total
MARCS Depreciation rate 10% 18% 14% 12% 9% 7% 7% 7% 7% 7% 3% 100%
Depreciation 200,000 360,000 288,000 230,400 184,400 147,400 131,000 131,000 131,200 131,000 65,600 2,000,000
Depriation Monthly 16,667 30,000 24,000 19,200 15,367 12,283 10,917 10,917 10,933 10,917 5,467
Solution
Operating Cash Flow = EBT + Depreciation - Tax - Additional Working Capital
Month Sales Direct Mat Dir Lab Admin Cost Depreciation Profit before Tax Tax Working Capital Additional Operating Cash Flow
0 100,000 40,000 10,000 4,167 16,667 29,167 8,750 5,000
1 100,000 40,000 10,000 4,167 16,667 29,167 10,208 5,000 30,625
2 100,000 40,000 10,000 4,167 16,667 29,167 10,208 5,000 30,625
3 100,000 40,000 10,000 4,167 16,667 29,167 10,208 5,000 30,625
4 100,000 40,000 10,000 4,167 16,667 29,167 10,208 5,000 30,625
5 100,000 40,000 10,000 4,167 16,667 29,167 10,208 5,000 30,625
6 100,000 40,000 10,000 4,167 16,667 29,167 10,208 5,000 30,625
7 100,000 40,000 10,000 4,167 16,667 29,167 10,208 5,000 30,625
8 100,000 40,000 10,000 4,167 16,667 29,167 10,208 5,000 30,625
9 100,000 40,000 10,000 4,167 16,667 29,167 10,208 5,000 30,625
10 100,000 40,000 10,000 4,167 16,667 29,167 10,208 5,000 30,625
11 100,000 40,000 10,000 4,167 16,667 29,167 10,208 5,000 30,625
12 100,000 40,000 10,000 4,167 16,667 29,167 10,208 5,000 30,625
13 113,300 45,320 11,330 4,292 30,000 22,358 7,825 5,665 38,868
14 113,300 45,320 11,330 4,292 30,000 22,358 7,825 5,665 38,868
15 113,300 45,320 11,330 4,292 30,000 22,358 7,825 5,665 38,868
16 113,300 45,320 11,330 4,292 30,000 22,358 7,825 5,665 38,868
17 113,300 45,320 11,330 4,292 30,000 22,358 7,825 5,665 38,868
18 113,300 45,320 11,330 4,292 30,000 22,358 7,825 5,665 38,868
19 113,300 45,320 11,330 4,292 30,000 22,358 7,825 5,665 38,868
20 113,300 45,320 11,330 4,292 30,000 22,358 7,825 5,665 38,868
21 113,300 45,320 11,330 4,292 30,000 22,358 7,825 5,665 38,868
22 113,300 45,320 11,330 4,292 30,000 22,358 7,825 5,665 38,868
23 113,300 45,320 11,330 4,292 30,000 22,358 7,825 5,665 38,868
24 113,300 45,320 11,330 4,292 30,000 22,358 7,825 5,665 38,868
25 128,369 51,348 12,837 4,420 24,000 35,764 12,517 6,418 40,828
26 128,369 51,348 12,837 4,420 24,000 35,764 12,517 6,418 40,828
27 128,369 51,348 12,837 4,420 24,000 35,764 12,517 6,418 40,828
28 128,369 51,348 12,837 4,420 24,000 35,764 12,517 6,418 40,828
29 128,369 51,348 12,837 4,420 24,000 35,764 12,517 6,418 40,828
30 128,369 51,348 12,837 4,420 24,000 35,764 12,517 6,418 40,828
31 128,369 51,348 12,837 4,420 24,000 35,764 12,517 6,418 40,828
32 128,369 51,348 12,837 4,420 24,000 35,764 12,517 6,418 40,828
33 128,369 51,348 12,837 4,420 24,000 35,764 12,517 6,418 40,828
34 128,369 51,348 12,837 4,420 24,000 35,764 12,517 6,418 40,828
35 128,369 51,348 12,837 4,420 24,000 35,764 12,517 6,418 40,828
36 128,369 51,348 12,837 4,420 24,000 35,764 12,517 6,418 40,828
c)
Terminal Year Cash flow
Assumptions
Sale of Equipment 100,000
Sale of Land 1,200,000
Working
Total sale proceeds 1,300,000
NBV of Land and equipment 1,065,600
gain 234,400
Tax on gain 82,040
Inflow from sale of land & equipment 1,217,960
Inflow from WC recapture 405,001
Total Inflow 1,622,961
Solution
Method 1
Annual Cash flows remain constant at Year 3 level 489,938
Year Cash Flows Discount Factor @ 10% Discounted Cash Flows
1 489,938 1.10 445,398
2 489,938 1.21 404,908
3 489,938 1.33 368,098
4 489,938 1.46 334,634
5 489,938 1.61 304,213
6 489,938 1.77 276,557
7 2,112,899 1.95 1,084,252
Value of Business at end of Year 3 3,218,060
Method 2
Operating Cash flows grow at a contant rate of 3%
Year Cash Flows Discount Factor @ 10% Discounted Cash Flows
1 504,636 1.10 458,760
2 519,775 1.21 429,566
3 535,369 1.33 402,230
4 551,430 1.46 376,634
5 567,973 1.61 352,666
6 585,012 1.77 330,224
7 2,225,523 1.95 1,142,045
Value of Business at end of Year 3 3,492,126

Part 2

Assumptions
Industry Food Manufacturing
Comparable Company Tyson Foods Inc
a)
Working
D/E 63%
1-E/E 63%
E 61%
D 39%
Beta 0.32
Tax 35%
Subject Company Beta
Bu= Bl / (1+(1-T)x (D/E)
1-T (D/E) (1+(1-T)x (D/E) Bu
0.41015 1.41015 0.23
Rf 0.63% 1 Year T bills
Rm 2.09% 1 Year S&P returns
S&P 500
Oct 27 2016 2,133.04
Oct 29 2015 2,089.41
Solution
RE = Rf +Beta (rm-rf)
RE 0.96%
b)
Working
Year Sales Direct Mat Dir Lab Admin Cost Depreciation Profit before Tax Tax Working Capital Additional Operating Cash Flow
1 1,200,000 480,000.00 120,000.00 50,000.00 200000 350,000.00 122,500.00 60,000 367,500.00
2 1,359,600 543,840.00 135,960.00 50,000.00 360000 269,800.00 94,430.00 67,980 467,390.00
3 1,540,427 616,170.72 154,042.68 50,000.00 288000 432,213.40 151,274.69 77,021 491,917.37
4 1,745,304 698,121.43 174,530.36 50,000.00 230400 592,251.78 207,288.12 0 615,363.66
5 1,977,429 790,971.58 197,742.89 50,000.00 184400 754,314.47 264,010.06 0 674,704.41
6 2,240,427 896,170.79 224,042.70 50,000.00 147400 922,813.49 322,984.72 0 747,228.77
7 2,538,404 1,015,361.51 253,840.38 50,000.00 131000 1,088,201.89 380,870.66 0 838,331.23
8 2,876,011 1,150,404.59 287,601.15 50,000.00 131000 1,257,005.74 439,952.01 0 948,053.73
9 3,258,521 1,303,408.40 325,852.10 50,000.00 131200 1,448,060.50 506,821.18 0 1,072,439.33
10 3,691,904 1,476,761.72 369,190.43 50,000.00 131000 1,664,952.15 582,733.25 0 1,213,218.90
Solution
Year Cash Flows Disc Factor PV Cumulative Cash Flows
0 (3,200,000.00) 1.00 (3,200,000.00) -3200000
1 367,500.00 1.01 364,002.33 (2,832,500.00)
2 467,390.00 1.02 458,535.60 (2,365,110.00)
3 491,917.37 1.03 478,005.20 (1,873,192.63)
4 615,363.66 1.04 592,269.17 (1,257,828.97)
5 674,704.41 1.05 643,202.38 (583,124.57) 9
6 747,228.77 1.06 705,560.90 164,104.20
7 838,331.23 1.07 784,049.32 1,002,435.43
8 948,053.73 1.08 878,228.47 1,950,489.16
9 1,072,439.33 1.09 983,997.76 3,022,928.49
10 2,836,180.24 1.10 2,577,519.88 5,859,108.73
NPV 5,265,371
IRR 17.57%
Payback period 5 Year 9 Months

Screen Shot 2016-11-15 at 12.38.32 PM.png

Screen Shot 2016-11-15 at 12.39.40 PM.png

capital_budgeting_2.docx

Capital Budgeting

a) Background

The project used for analysis is the installation of a new packaged food manufacturing plant by XYZ Company in County X. The said project will have a useful life of 10 years after which the plant will be sold for USD 200,000.

b) Assumptions

The major assumptions underlying the project include:

· The project will require an initial investment of $ 2 million in equipment and $ 1 million in land.

· An initial working capital investment of $200,000 will be needed in Year 0.

· 12000 units will be produced in the first year of the project after which the units sold will increase by 10%.

· Selling price for the products is $100 which will increase in line with inflation.

· Direct material and labor are 40% and 10% of the sale price.

· Administrative costs remain fixed at $50,000 per year.

· The equipment is depreciated using the MARCS 10 year depreciation.

· Additional working capital of 3% of sales is required in the first three years of the project after which working capital becomes constant.

· The project is financed entirely by equity and cost of equity is calculated using Capital Asset Pricing Model.

· At the end of year 10 land and equipment are sold for $ 1.2 million and $ 100,000 respectively.

· Tax rate applicable in country X is 35%.

c) Sources of information

· Annual S&P 500 returns are a proxy for market returns.

· USD 1 year T-bills represent the risk free return.

d) Results

i) Cost of equity.

To determine the cost of equity comparable company beta was unlevered and used in CAPM analysis. The comparable company used for the said analysis was Tyson Foods Inc.

Working for CAPM is presented below:

Table 1: Comparable company data

D/E

63%

1-E/E

63%

E

61%

D

39%

Beta

0.32

Tax

35%

Bu= Bl / (1+(1-T)x (D/E)

1-T (D/E)

(1+(1-T)x (D/E)

Bu

0.41015

1.41015

0.23

Rf

0.63%

Rm

2.09%

RE = Rf +Beta (rm-rf) = 0.96%

ii) NPV, IRR and Payback Period

Year

Cash Flows

Disc Factor

PV

Cumulative Cash Flows

0

(3,200,000.00)

1.00

(3,200,000.00)

-3200000

1

367,500.00

1.01

364,002.33

(2,832,500.00)

2

467,390.00

1.02

458,535.60

(2,365,110.00)

3

491,917.37

1.03

478,005.20

(1,873,192.63)

4

615,363.66

1.04

592,269.17

(1,257,828.97)

5

674,704.41

1.05

643,202.38

(583,124.57)

6

747,228.77

1.06

705,560.90

164,104.20

7

838,331.23

1.07

784,049.32

1,002,435.43

8

948,053.73

1.08

878,228.47

1,950,489.16

9

1,072,439.33

1.09

983,997.76

3,022,928.49

10

2,836,180.24

1.10

2,577,519.88

5,859,108.73

NPV

5,265,371

IRR

17.57%

Payback period

5 Year 9 Months

iii) Conclusion

The project should be pursued by the company as:

· It has a positive NPV;

· The IRR is greater than the hurdle rate.

References:

https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yield   https://finance.yahoo.com/quote/%5EGSPC/history?p=%5EGSPC  https://finance.yahoo.com/quote/TSN?ltr=1 

Page 2