Lcc

  • July 2020
  • PDF

This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA


Overview

Download & View Lcc as PDF for free.

More details

  • Words: 1,891
  • Pages: 17
Life Cycle Cost Spreadsheet Version 1.9, September, 2006, Copyright 2004 by Barringer & Associates, Inc., Humble, TX 77347, USA

For information contact Paul Barringer, P.E., Barringer & Associates, Inc. http://www.barringer1.com [email protected]

This sheet is prepared for simple engineering calculations as an aid for making engineering decisions. Check with your local accountant for specific details before making financial decisions of great importance. Paul Barringer, P.E. Ph: 281-852-6810 FAX: 281-852-3749 Email: [email protected] Website: http://www.barringer1.com

LCC Worksheet is a simple net present value calculation sheet for your use. LCC Example 1 is a short problem using Excel Solver (Tools/Solver) to find the maximum allowed capital expendit LCC Example 2 is a typical worksheet example for a single alternative.

USA

[email protected]

m allowed capital expenditure.

Life Cycle Cost Worksheet Discount Rate (%)--> 12% $0

Capital Costs:

<--Yellow Boxes Are For Data Input Project Life (35 yrs max)--> 20 Tax Provision (%)--> <--Net Present Value <--Internal Rate Of Return 0

1

2

3

Capital Acquisition Costs

Acquisition Costs: Program Management Costs Engineering Design Costs Engineering Data Costs Spare Parts & Logistics Costs Facilities & Construction Costs Initial Training Costs Technical Data Costs Documentation Costs Annual recuring costs Other periodic costs Disposal Costs

Savings: Annual Savings (use positive #s)

NPV & IRR Calculations:

0 1 2 3 Capital equipment $0 Costs $0 $0 $0 $0 Savings $0 $0 $0 Straight Line Depreciation $0 $0 $0 Profit Before Taxes $0 $0 $0 $0 Tax Provision @ 38% Of Profit Before Tax $0 $0 $0 $0 Net Income can be profit or loss $0 $0 $0 $0 Add Back Depreciation $0 $0 $0 Cash Flow (Net Income + Depreciation) $0 $0 $0 $0 Discount Factors @ 12% 1.0000 0.8929 0.7972 0.7118 Present Value $0 $0 $0 $0 Net Present Value $0 Internal Rate Return <--Requires at least one positive and one negative number in the present value ro Year-->

0

1

2

3

Are For Data Input Tax Provision (%)--> Internal Rate Of Return

38%

4

5

6

7

8

9

10

4

5

6

7

8

9

10

$0 $0 $0 $0 $0 $0 $0 $0 0.6355 $0

$0 $0 $0 $0 $0 $0 $0 $0 0.5674 $0

$0 $0 $0 $0 $0 $0 $0 $0 0.5066 $0

$0 $0 $0 $0 $0 $0 $0 $0 0.4523 $0

$0 $0 $0 $0 $0 $0 $0 $0 0.4039 $0

$0 $0 $0 $0 $0 $0 $0 $0 0.3606 $0

$0 $0 $0 $0 $0 $0 $0 $0 0.3220 $0

e number in the present value row 32 4

5

6

7

8

9

10

11

12

13

14

15

16

17

11

12

13

14

15

16

17

$0 $0 $0 $0 $0 $0 $0 $0 0.2875 $0

11

$0 $0 $0 $0 $0 $0 $0 $0 0.2567 $0

12

$0 $0 $0 $0 $0 $0 $0 $0 0.2292 $0

13

$0 $0 $0 $0 $0 $0 $0 $0 0.2046 $0

14

$0 $0 $0 $0 $0 $0 $0 $0 0.1827 $0

15

$0 $0 $0 $0 $0 $0 $0 $0 0.1631 $0

16

$0 $0 $0 $0 $0 $0 $0 $0 0.1456 $0

17

18

19

20

18

19

20

$0 $0 $0 $0 $0 $0 $0 $0 0.1300 $0

18

$0 $0 $0 $0 $0 $0 $0 $0 0.1161 $0

19

Exceeds Project Life

Exceeds Project Life

Exceeds Project Life

Exceeds Project Life

21

22

23

24

$0 $0 $0 $0 $0 $0 $0 $0 0.1037 $0

20

Exceeds Project Life

Exceeds Project Life

Exceeds Project Life

Exceeds Project Life

Exceeds Project Life

Exceeds Project Life

Exceeds Project Life

25

26

27

28

29

30

31

Exceeds Project Life

Exceeds Project Life

Exceeds Project Life

Exceeds Project Life

$0

32

33

34

35

Life Cycle Cost Worksheet Discount Rate (%)--> 12% $3,109,654

Capital Costs: Capital Acquisition Costs

<--Yellow Boxes Are For Data Input Project Life (35 yrs max)--> 20 Tax Provision (%)--> <--Net Present Value 25.00% <--Internal Rate Of Return 0 $3,497,367

1

2

3

4

Acquisition Costs: Program Management Costs Engineering Design Costs Engineering Data Costs Spare Parts & Logistics Costs Facilities & Construction Costs Initial Training Costs Technical Data Costs Documentation Costs Annual recuring costs Other periodic costs Disposal Costs

Savings: Annual Savings (use positive #s)

$1,319,500

$1,319,500

$1,319,500

$1,319,500

NPV & IRR Calculations:

0 1 2 3 4 Capital equipment $3,497,367 Question: How much capital can we afford to Costs $0 $0 $0 a 20 year $0 $0 discount rate Given project life, 12% Savings $1,319,500 $1,319,500 $1,319,500 $1,319,500 Hint: Straight Line Depreciation $174,868 $174,868 $174,868 1) Input annual savings$174,868 of $1,319,500 in the y Profit Before Taxes $0 $1,144,632 $1,144,632 $1,144,632 $1,144,632 2) Put $1 in cell D5 for initializing the calculati Tax Provision @ 38% Of Profit Before Tax $0 -$434,960 -$434,960 -$434,960 -$434,960 Set the iterative process for Goal Seek to Net Income can be profit or loss $0 $709,672 $709,672 $709,672 $709,672 change calculations to Automatic, place c Add Back Depreciation $174,868 $174,868 $174,868 3) Click on$174,868 cell F3 for IRR and notice that it re Cash Flow (Net Income + Depreciation) -$3,497,367 $884,540 $884,540 $884,540 $884,540 Excel needs to work on a formula where a Discount Factors @ 12% 1.0000 0.8929 0.7972 0.7118click on 0.6355 4) Click on Tools, Goal Seek, Present Value -$3,497,367 $789,768 $705,150 $629,598 $562,141 this is where set cell: C34 <--remember to value: 25% <--you can also use 0.25 Net Present Value $3,109,654 bynegative changing cell: D5. start withrow $1 Internal Rate Return 25.00% <--Requires at least one positive and one number in the<--we present value

Click OK and Goal Seek will adjust cell By convention, the IRR is based on cash flow

38%

te Of Return 5

$1,319,500 5

6

$1,319,500 6

7

$1,319,500 7

8

$1,319,500 8

9

$1,319,500 9

10

$1,319,500 10

11

$1,319,500

12

$1,319,500

11

h capital can we afford to spend on a project if we save $1,319,500/year? $0rate, and the $0 internal requirment $0 $025% internal $0 rate of return? $0 ect life, 12% discount$0 rate, 38% tax for a

12

$0 $1,319,500 $1,319,500 $1,319,500 $1,319,500 $1,319,500 $1,319,500 $1,319,500 $1,319,500 $174,868 $174,868 $174,868 $174,868 $174,868 $174,868 ngs of $1,319,500 in the yellow boxes of $174,868 row 19 for each of the 20$174,868 years. $1,144,632 $1,144,632 $1,144,632 $1,144,632 $1,144,632 $1,144,632 $1,144,632 $1,144,632 for initializing the calculations using Excel Goal Seek Tool to find how much capital we can afford. -$434,960 -$434,960 -$434,960 by-$434,960 -$434,960 -$434,960 -$434,960 -$434,960 process for Goal Seek to perform more accurately clicking on Tools, Options, Calculation, and $709,672 $709,672 $709,672 $709,672 $709,672 $709,672 $709,672 $709,672 ons to Automatic, place check mark in Iterations, change Max Iterations = 1000: Max Change = 0.0001 $174,868 $174,868 $174,868 $174,868 $174,868 $174,868 $174,868 $174,868 or IRR and notice that it refers to the formula in cell C34--this is an important clue because $884,540 $884,540 $884,540 $884,540 $884,540 $884,540 $884,540 $884,540 work on a formula where as cell F3 only reports the results. 0.5674 0.5066 0.4523 0.4039 0.3606 0.3220 0.2875 0.2567 ick on Goal Seek, $501,912 $448,135 $357,251 $318,974 $284,798 $254,284 $227,039 <--remember this is where the formula is $400,121 located

<--you can also use 0.25 l: D5. start withrow $132 to begin the iterative calculations er in the<--we present value Goal Seek will adjust cell D5 = ~$3,497,367 to meet the IRR = 25% with NPV = ~$3,109,654. RR is based on cash flow (row 30) rather than the present values (row 32).

n?

001

13

$1,319,500 13 $0 $1,319,500 $174,868 $1,144,632 -$434,960 $709,672 $174,868 $884,540 0.2292 $202,714

14

$1,319,500 14 $0 $1,319,500 $174,868 $1,144,632 -$434,960 $709,672 $174,868 $884,540 0.2046 $180,994

15

$1,319,500 15 $0 $1,319,500 $174,868 $1,144,632 -$434,960 $709,672 $174,868 $884,540 0.1827 $161,602

16

$1,319,500 16 $0 $1,319,500 $174,868 $1,144,632 -$434,960 $709,672 $174,868 $884,540 0.1631 $144,288

17

$1,319,500 17 $0 $1,319,500 $174,868 $1,144,632 -$434,960 $709,672 $174,868 $884,540 0.1456 $128,828

18

$1,319,500 18 $0 $1,319,500 $174,868 $1,144,632 -$434,960 $709,672 $174,868 $884,540 0.1300 $115,025

19

$1,319,500 19 $0 $1,319,500 $174,868 $1,144,632 -$434,960 $709,672 $174,868 $884,540 0.1161 $102,701

20

$1,319,500 20 $0 $1,319,500 $174,868 $1,144,632 -$434,960 $709,672 $174,868 $884,540 0.1037 $91,697

###

Life Cycle Cost Worksheet <--Yellow Boxes Are For Data Input Discount Rate (%)--> 12% Project Life (yrs)--> 20 Tax Provision (%)--> 38% -$125,465 <--Net Present Value <--Internal Rate Of Return

Capital Costs: Capital Acquisition Costs

Acquisition Costs: Program Management Costs Engineering Design Costs Engineering Data Costs Spare Parts & Logistics Costs Facilities & Construction Costs Initial Training Costs Technical Data Costs Documentation Costs Annual recuring costs Other periodic costs Disposal Costs

0 $75,000

1

2

3

4

5

Given a 20 year project life, 12% discount rate, and 38% tax rate. Equipment cost $75,000. Annual sustaining cost = $12,264/yr. Overhaul cos costs = $5,000 in year 20.

What is the net present value of the project? NPV=($125,465) and must b to find the most favorable NPV (this often means selection of the LEAST nega have two negative NPV's from two alternatives, you would select the least neg positive NPV "delta" between the alternatives.

$12,264

$12,264

$12,264

$12,264

$12,264

Savings: Annual Savings (use positive #s)

NPV & IRR Calculations:

0 1 2 3 4 5 Capital equipment $75,000 Costs $0 $12,264 $12,264 $12,264 $12,264 $12,264 Savings $0 $0 $0 $0 $0 Straight Line Depreciation $3,750 $3,750 $3,750 $3,750 $3,750 Profit Before Taxes $0 -$16,014 -$16,014 -$16,014 -$16,014 -$16,014 Tax Provision @ 38% Of Profit Before Tax $0 $6,085 $6,085 $6,085 $6,085 $6,085 Net Income can be profit or loss $0 -$9,929 -$9,929 -$9,929 -$9,929 -$9,929 Add Back Depreciation $3,750 $3,750 $3,750 $3,750 $3,750 Cash Flow (Net Income + Depreciation) -$75,000 -$6,179 -$6,179 -$6,179 -$6,179 -$6,179 Discount Factors @ 12% 1.0000 0.8929 0.7972 0.7118 0.6355 0.5674 Present Value -$75,000 -$5,517 -$4,926 -$4,398 -$3,927 -$3,506 Net Present Value -$125,465 Internal Rate Return <--Requires at least one positive and one negative number in the present value row 32

6

7

8

9

10

11

12

13

14

15

t rate, and 38% tax rate. ng cost = $12,264/yr. Overhaul cost = $20,000 in year 10. Disposal

oject? NPV=($125,465) and must be compared to other alternatives means selection of the LEAST negative NPV). Remember when you atives, you would select the least negative, and you will then have a ves.

$12,264

6 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.5066 -$3,130

$12,264

7 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.4523 -$2,795

mber in the present value row 32

$12,264

8 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.4039 -$2,495

$12,264

9 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.3606 -$2,228

$12,264 $20,000

10 $32,264 $0 $3,750 -$36,014 $13,685 -$22,329 $3,750 -$18,579 0.3220 -$5,982

$12,264

11 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.2875 -$1,776

$12,264

12 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.2567 -$1,586

$12,264

13 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.2292 -$1,416

$12,264

14 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.2046 -$1,264

$12,264

15 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.1827 -$1,129

16

$12,264

17

$12,264

18

$12,264

19

$12,264

20

$12,264 $5,000

16 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.1631 -$1,008

17 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.1456 -$900

18 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.1300 -$803

19 $12,264 $0 $3,750 -$16,014 $6,085 -$9,929 $3,750 -$6,179 0.1161 -$717

20 $17,264 $0 $3,750 -$21,014 $7,985 -$13,029 $3,750 -$9,279 0.1037 -$962

Related Documents

Lcc
April 2020 34
Lcc
July 2020 22
Lcc
May 2020 20
Lcc Kebudayaan.docx
April 2020 18
Lcc Thesis_online Games
December 2019 20