Evaluation of Capital ProjectsP
kimdevore
cf_capital_budgeting.xlsxProjectA
| Project A | Major Equipment Purchase | |||||||||
| Purchasing cost | $10,000,000 | |||||||||
| Life of project in years | 8 | |||||||||
| Reduction in cost per year | 5% | |||||||||
| Salvage value | $500,000 | |||||||||
| Required rate of return | 8% | |||||||||
| Depreciation | MACRS-7 years | |||||||||
| Annual sales | $20,000,000 | |||||||||
| Earlier Cost of sales (60% of sales)
: 60% of Annual Sales i.e of 20 million =C9*0.6 |
$12,000,000 | |||||||||
| Tax rate | 25% | |||||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | |
| Purchasing Cost | $10,000,000 | |||||||||
| Annual Sales | $20,000,000 | $20,000,000 | $20,000,000 | $20,000,000 | $20,000,000 | $20,000,000 | $20,000,000 | $20,000,000 | ||
| Cost of the goods sold
: Cost of sales reducing by 5% per year for 8 years |
$11,000,000 | $10,000,000 | $9,000,000 | $8,000,000 | $7,000,000 | $6,000,000 | $5,000,000 | $4,000,000 | ||
| MACRS - 7 years rates | 14.29% | 24.49% | 17.49% | 12.49% | 8.93% | 8.92% | 8.93% | 4.46% | ||
| Annual Depreciation | $1,429,000 | $2,449,000 | $1,749,000 | $1,249,000 | $893,000 | $892,000 | $893,000 | $446,000 | ||
| Earnings before interest and taxes (EBIT) / Gross Income
: EBIT = Sales - Cost of Sales - Operating Expenses https://www.investopedia.com/terms/e/ebit.asp |
$7,571,000 | $7,551,000 | $9,251,000 | $10,751,000 | $12,107,000 | $13,108,000 | $14,107,000 | $15,554,000 | ||
| Taxes (25%) / Income Taxes
: EBIT * Tax rate of 25% |
$ 1,892,750 | $ 1,887,750 | $ 2,312,750 | $ 2,687,750 | $ 3,026,750 | $ 3,277,000 | $ 3,526,750 | $ 3,888,500 | ||
| Earnings after taxes / Net Income
: EBIT - Taxes |
$5,678,250 | $5,663,250 | $6,938,250 | $8,063,250 | $9,080,250 | $9,831,000 | $10,580,250 | $11,665,500 | ||
| Add Depreciation | $1,429,000 | $2,449,000 | $1,749,000 | $1,249,000 | $893,000 | $892,000 | $893,000 | $446,000 | ||
| Add: After tax salvage value
: Salvage value on 8th year - (Salvage Value * Tax rate 25%) |
: 60% of Annual Sales i.e of 20 million =C9*0.6 |
$375,000 | ||||||||
| Cash Flows | ($10,000,000) | $7,107,250 | $8,112,250 | $8,687,250 | $9,312,250 | $9,973,250 | $10,723,000 | $11,473,250 | $12,486,500 | |
| Cummulative Cash Flows | ($10,000,000) | ($2,892,750) | $5,219,500 | $13,906,750 | $23,219,000 | $33,192,250 | $43,915,250 | $55,388,500 | $67,875,000 | |
| Present Value factor (8%)
: PV https://www.accountingtools.com/articles/what-is-the-present-value-factor.html |
1 | 0.93 | 0.86 | 0.79 | 0.74 | 0.68 | 0.63 | 0.58 | 0.54 | |
| Present value of cash flows
: Ref: Bradford, R.S.W.R.J.J. J. (2017). Corporate Finance: Core Principles and Applications. [Capella]. Retrieved from https://capella.vitalsource.com/#/books/1260384357/ Page 115 (Ch4), 197 (Ch7) |
: Cost of sales reducing by 5% per year for 8 years |
($10,000,000) | $6,580,787 | $6,954,947 | $6,896,219 | $6,844,782 | $6,787,626 | $6,757,309 | $6,694,531 | $6,746,067 |
| Present value of cash inflows | $54,262,269 | |||||||||
| Present value cash outflows | $10,000,000 | |||||||||
| Net present value = Present value of cash inflows - Present value of cash outflows | $44,262,269 | NPV Formula: | $44,262,269 | |||||||
| Internal rate of return (IRR) | IRR: | 79.79% | ||||||||
| Payback period
: https://www.investopedia.com/ask/answers/051315/how-do-you-calculate-payback-period-using-excel.asp https://www.elearnmarkets.com/blog/calculation-of-payback-period/ |
1.36 | |||||||||
| profitability Index (PI) + present value of cash inflows/ present value of cash outflows | 5.43 |
ProjectB
| Project B: Expansion Into Three Additional States | Expansion Into Three Additional States | |||||
| Start up Cost | $7,000,000 | |||||
| Life of a project in years | 5 | |||||
| Annual Depreciation (using straightline)
: Using straight line depreciation Annual Depreciation= (Asset Cost - Salvage Value)/Asset Life https://corporatefinanceinstitute.com/resources/knowledge/accounting/straight-line-depreciation/ |
$1,400,000 | |||||
| Net working capital | $1,000,000 | |||||
| Required rate of Return | 12% | |||||
| Earlier annual sales | $20,000,000 | |||||
| Earlier Cost of sales (60% of sales) | $12,000,000 | |||||
| Increase in sales and revenue per year | 10% | |||||
| Tax rate | 25% | |||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 |
| Purchasing Cost | $7,000,000 | |||||
| Annual Sales | $22,000,000 | $24,200,000 | $26,620,000 | $29,282,000 | $32,210,200 | |
| Cost of goods sold | $13,200,000 | $14,520,000 | $15,972,000 | $17,569,200 | $19,326,120 | |
| Annual Depreciation | $1,400,000 | $1,400,000 | $1,400,000 | $1,400,000 | $1,400,000 | |
| Earnings before interest and taxes (EBIT) | $7,400,000 | $8,280,000 | $9,248,000 | $10,312,800 | $11,484,080 | |
| Tax (30%) | $1,850,000 | $2,070,000 | $2,312,000 | $2,578,200 | $2,871,020 | |
| Earnings after tax | $5,550,000 | $6,210,000 | $6,936,000 | $7,734,600 | $8,613,060 | |
| Plus Depreciation | $1,400,000 | $1,400,000 | $1,400,000 | $1,400,000 | $1,400,000 | |
| Net working capital | ($1,000,000) | $1,000,000 | ||||
| Cash flows | ($8,000,000) | $6,950,000 | $7,610,000 | $8,336,000 | $9,134,600 | $11,013,060 |
| Cummulative Cash flows | ($8,000,000) | ($1,050,000) | $6,560,000 | $14,896,000 | $24,030,600 | $35,043,660 |
| Present value factor (12%) | 1.00 | 0.89 | 0.80 | 0.71 | 0.64 | 0.57 |
| Present value of cash flows | ($8,000,000) | $6,205,357 | $6,066,645 | $5,933,400 | $5,805,203 | $6,249,106 |
| Present value of cash inflows | $30,259,712 | |||||
| Present Value of Cash outflows | $8,000,000 | |||||
| Net Present Value = Present Value of cash inflows - Present Value of cash outflows | $22,259,712 | NPV Formula: | $22,259,712 | |||
| Internal rate of return | IRR: | 91.48% | ||||
| payback period | 1.14 | |||||
| Profitability Index (PI) | 3.78 |
ProjectC
| Project C | Marketuing/ Advertising Campaign | ||||||
| Annual cost | $2,000,000 | ||||||
| Life of project in years | 6 | ||||||
| Required rate of return | 10% | ||||||
| Earlier Annual sales | $20,000,000 | ||||||
| Earlier Cost of Sales (60% of sales) | $12,000,000 | ||||||
| Increase in sales and revenue per year | 15% | ||||||
| Tax rate | 25% | ||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
| Marketing / Advertising cost | $2,000,000 | $2,000,000 | $2,000,000 | $2,000,000 | $2,000,000 | $2,000,000 | |
| Present value of Annual marketing cost | $8,710,521 | ||||||
| Annual sales | $23,000,000 | $26,450,000 | $30,417,500 | $34,980,125 | $40,227,144 | $46,261,215 | |
| Cost of the Goods sold | $13,800,000 | $15,870,000 | $18,250,500 | $20,988,075 | $24,136,286 | $27,756,729 | |
| Earnings before interest and taxes (EBIT) | $9,200,000 | $10,580,000 | $12,167,000 | $13,992,050 | $16,090,858 | $18,504,486 | |
| Taxes (25%) | $2,300,000 | $2,645,000 | $3,041,750 | $3,498,013 | $4,022,714 | $4,626,122 | |
| Earnings after taxes | $6,900,000 | $7,935,000 | $9,125,250 | $10,494,038 | $12,068,143 | $13,878,365 | |
| Cash Flows | ($8,710,521) | $6,900,000 | $7,935,000 | $9,125,250 | $10,494,038 | $12,068,143 | $13,878,365 |
| Present value factor (10%) | 1.00 | 0.91 | 0.83 | 0.75 | 0.68 | 0.62 | 0.56 |
| Present value of cash flows | ($8,710,521) | $6,272,727 | $6,557,851 | $6,855,935 | $7,167,569 | $7,493,367 | $7,833,975 |
| Cummulative Cash flows | ($8,710,521) | ($1,810,521) | $6,124,479 | $15,249,729 | $25,743,766 | $37,811,909 | $51,690,274 |
| Present value of cash inflows | $42,181,425 | ||||||
| Present value of cash outflows | $8,710,521 | ||||||
| Net Present Value = Present Value of cash inflows - Present value of cash outflows | $33,470,904 | NPV Formula: | $33,470,904 | ||||
| internal rate of return | IRR: | 90.36% | |||||
| Payback periodd | 1.23 | ||||||
| Profitability Index (PI) = present value of cash inflows/ present value of cash outflows | 4.84 |
Comparison
| Projects | Net Present Value | Payback Period | Profitability Index | Internal Rate of Return |
| Project A: Major Equipment Purchase | $44,262,268.65 | 1.36 | 5.43 | 79.79% |
| Project B: Expansion Three Additional States | $22,259,712.14 | 1.14 | 3.78 | 91.48% |
| Project C: Marketing or Advertising Campaign | $33,470,903.72 | 1.23 | 4.84 | 90.36% |
Combined_EvaluateCapProjects
| Project A | Major Equipment Purchase | ||||||||
| Purchasing cost | $10,000,000 | ||||||||
| Life of project in years | 8 | ||||||||
| Reduction in cost per year | 5% | ||||||||
| Salvage value | $500,000 | ||||||||
| Required rate of return | 8% | ||||||||
| Depreciation | MACRS-7 years | ||||||||
| Annual sales | $20,000,000 | ||||||||
| Earlier Cost of sales (60% of sales)
: 60% of Annual Sales i.e of 20 million =C9*0.6 |
$12,000,000 | ||||||||
| Tax rate | 25% | ||||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
| Purchasing Cost | $10,000,000 | ||||||||
| Annual Sales | $20,000,000 | $20,000,000 | $20,000,000 | $20,000,000 | $20,000,000 | $20,000,000 | $20,000,000 | $20,000,000 | |
| Cost of the goods sold
: Cost of sales reducing by 5% per year for 8 years |
$11,000,000 | $10,000,000 | $9,000,000 | $8,000,000 | $7,000,000 | $6,000,000 | $5,000,000 | $4,000,000 | |
| MACRS - 7 years rates | 14.29% | 24.49% | 17.49% | 12.49% | 8.93% | 8.92% | 8.93% | 4.46% | |
| Annual Depreciation | $1,429,000 | $2,449,000 | $1,749,000 | $1,249,000 | $893,000 | $892,000 | $893,000 | $446,000 | |
| Earnings before interest and taxes (EBIT) / Gross Income
: EBIT = Sales - Cost of Sales - Operating Expenses https://www.investopedia.com/terms/e/ebit.asp |
$7,571,000 | $7,551,000 | $9,251,000 | $10,751,000 | $12,107,000 | $13,108,000 | $14,107,000 | $15,554,000 | |
| Taxes (25%) / Income Taxes
: EBIT * Tax rate of 25% |
$ 1,892,750 | $ 1,887,750 | $ 2,312,750 | $ 2,687,750 | $ 3,026,750 | $ 3,277,000 | $ 3,526,750 | $ 3,888,500 | |
| Earnings after taxes / Net Income
: EBIT - Taxes |
$5,678,250 | $5,663,250 | $6,938,250 | $8,063,250 | $9,080,250 | $9,831,000 | $10,580,250 | $11,665,500 | |
| Add Depreciation | $1,429,000 | $2,449,000 | $1,749,000 | $1,249,000 | $893,000 | $892,000 | $893,000 | $446,000 | |
| Add: After tax salvage value
: Salvage value on 8th year - (Salvage Value * Tax rate 25%) |
$375,000 | ||||||||
| Cash Flows | ($10,000,000) | $7,107,250 | $8,112,250 | $8,687,250 | $9,312,250 | $9,973,250 | $10,723,000 | $11,473,250 | $12,486,500 |
| Cummulative Cash Flows | ($10,000,000) | ($2,892,750) | $5,219,500 | $13,906,750 | $23,219,000 | $33,192,250 | $43,915,250 | $55,388,500 | $67,875,000 |
| Present Value factor (8%)
: PV https://www.accountingtools.com/articles/what-is-the-present-value-factor.html |
1 | 0.93 | 0.86 | 0.79 | 0.74 | 0.68 | 0.63 | 0.58 | 0.54 |
| Present value of cash flows
: Ref: Bradford, R.S.W.R.J.J. J. (2017). Corporate Finance: Core Principles and Applications. [Capella]. Retrieved from https://capella.vitalsource.com/#/books/1260384357/ Page 115 (Ch4), 197 (Ch7) |
($10,000,000) | $6,580,787 | $6,954,947 | $6,896,219 | $6,844,782 | $6,787,626 | $6,757,309 | $6,694,531 | $6,746,067 |
| Present value of cash inflows | $54,262,269 | ||||||||
| Present value cash outflows | $10,000,000 | ||||||||
| Net present value = Present value of cash inflows - Present value of cash outflows | $44,262,269 | NPV Formula: | $44,262,269 | ||||||
| Internal rate of return (IRR) | IRR: | 79.79% | |||||||
| Payback period
: https://www.investopedia.com/ask/answers/051315/how-do-you-calculate-payback-period-using-excel.asp https://www.elearnmarkets.com/blog/calculation-of-payback-period/ |
1.36 | ||||||||
| profitability Index (PI) + present value of cash inflows/ present value of cash outflows | 5.43 | ||||||||
| Project B | Expansion into Europe | ||||||||
| Start up Cost | $7,000,000 | ||||||||
| Life of a project in years | 5 | ||||||||
| Annual Depreciation (using straightline)
: Using straight line depreciation Annual Depreciation= (Asset Cost - Salvage Value)/Asset Life https://corporatefinanceinstitute.com/resources/knowledge/accounting/straight-line-depreciation/ |
: 60% of Annual Sales i.e of 20 million =C9*0.6 |
: PV https://www.accountingtools.com/articles/what-is-the-present-value-factor.html |
: Ref: Bradford, R.S.W.R.J.J. J. (2017). Corporate Finance: Core Principles and Applications. [Capella]. Retrieved from https://capella.vitalsource.com/#/books/1260384357/ Page 115 (Ch4), 197 (Ch7) |
: Cost of sales reducing by 5% per year for 8 years |
: https://www.investopedia.com/ask/answers/051315/how-do-you-calculate-payback-period-using-excel.asp https://www.elearnmarkets.com/blog/calculation-of-payback-period/ |
$1,400,000 | |||
| Net working capital | $1,000,000 | ||||||||
| Required rate of Return | 12% | ||||||||
| Earlier annual sales | $20,000,000 | ||||||||
| Earlier Cost of sales (60% of sales) | $12,000,000 | ||||||||
| Increase in sales and revenue per year | 10% | ||||||||
| Tax rate | 30% | ||||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | |||
| Purchasing Cost | $7,000,000 | ||||||||
| Annual Sales | $22,000,000 | $24,200,000 | $26,620,000 | $29,282,000 | $32,210,200 | ||||
| Cost of goods sold | $13,200,000 | $14,520,000 | $15,972,000 | $17,569,200 | $19,326,120 | ||||
| Annual Depreciation | $1,400,000 | $1,400,000 | $1,400,000 | $1,400,000 | $1,400,000 | ||||
| Earnings before interest and taxes (EBIT) | $7,400,000 | $8,280,000 | $9,248,000 | $10,312,800 | $11,484,080 | ||||
| Tax (30%) | $2,220,000 | $2,484,000 | $2,774,400 | $3,093,840 | $3,445,224 | ||||
| Earnings after tax | $5,180,000 | $5,796,000 | $6,473,600 | $7,218,960 | $8,038,856 | ||||
| Plus Depreciation | $1,400,000 | $1,400,000 | $1,400,000 | $1,400,000 | $1,400,000 | ||||
| Net working capital | ($1,000,000) | $1,000,000 | |||||||
| Cash flows | ($8,000,000) | $6,580,000 | $7,196,000 | $7,873,600 | $8,618,960 | $10,438,856 | |||
| Cummulative Cash flows | ($8,000,000) | ($1,420,000) | $5,776,000 | $13,649,600 | $22,268,560 | $32,707,416 | |||
| Present value factor (12%) | 1.00 | 0.89 | 0.80 | 0.71 | 0.64 | 0.57 | |||
| Present value of cash flows | ($8,000,000) | $5,875,000 | $5,736,607 | $5,604,273 | $5,477,505 | $5,923,287 | |||
| Present value of cash inflows | $28,616,672 | ||||||||
| Present Value of Cash outflows | $8,000,000 | ||||||||
| Net Present Value = Present Value of cash inflows - Present Value of cash outflows | $20,616,672 | NPV Formula: | $20,616,672 | ||||||
| Internal rate of return | IRR: | 86.34% | |||||||
| payback period | 1.20 | ||||||||
| Profitability Index (PI) | 3.58 | ||||||||
| Project C | Marketuing/ Advertising Campaign | ||||||||
| Annual cost | $2,000,000 | ||||||||
| Life of project in years | 6 | ||||||||
| Required rate of return | 10% | ||||||||
| Earlier Annual sales | $20,000,000 | ||||||||
| Earlier Cost of Sales (60% of sales) | $12,000,000 | ||||||||
| Increase in sales and revenue per year | 15% | ||||||||
| Tax rate | 25% | ||||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | ||
| Marketing / Advertising cost | $2,000,000 | $2,000,000 | $2,000,000 | $2,000,000 | $2,000,000 | $2,000,000 | |||
| Present value of Annual marketing cost | $8,710,521 | ||||||||
| Annual sales | $23,000,000 | $26,450,000 | $30,417,500 | $34,980,125 | $40,227,144 | $46,261,215 | |||
| Cost of the Goods sold | $13,800,000 | $15,870,000 | $18,250,500 | $20,988,075 | $24,136,286 | $27,756,729 | |||
| Earnings before interest and taxes (EBIT) | $9,200,000 | $10,580,000 | $12,167,000 | $13,992,050 | $16,090,858 | $18,504,486 | |||
| Taxes (25%) | $2,300,000 | $2,645,000 | $3,041,750 | $3,498,013 | $4,022,714 | $4,626,122 | |||
| Earnings after taxes | $6,900,000 | $7,935,000 | $9,125,250 | $10,494,038 | $12,068,143 | $13,878,365 | |||
| Cash Flows | ($8,710,521) | $6,900,000 | $7,935,000 | $9,125,250 | $10,494,038 | $12,068,143 | $13,878,365 | ||
| Present value factor (10%) | 1.00 | 0.91 | 0.83 | 0.75 | 0.68 | 0.62 | 0.56 | ||
| Present value of cash flows | ($8,710,521) | $6,272,727 | $6,557,851 | $6,855,935 | $7,167,569 | $7,493,367 | $7,833,975 | ||
| Cummulative Cash flows | ($8,710,521) | ($1,810,521) | $6,124,479 | $15,249,729 | $25,743,766 | $37,811,909 | $51,690,274 | ||
| Present value of cash inflows | $42,181,425 | ||||||||
| Present value of cash outflows | $8,710,521 | ||||||||
| Net Present Value = Present Value of cash inflows - Present value of cash outflows | $33,470,904 | NPV Formula: | $33,470,904 | ||||||
| internal rate of return | IRR: | 90.36% | |||||||
| Payback periodd | 1.23 | ||||||||
| Profitability Index (PI) = present value of cash inflows/ present value of cash outflows | 4.84 | ||||||||
| Projects | Net Present Value | Payback Period | Profitability Index | Internal Rate of Return | |||||
| Project A: Major Equipment Purchase | $44,262,268.65 | 1.36 | 5.43 | 79.79% | |||||
| Project B: Expansion into Europe | $20,616,672.24 | 1.20 | 3.58 | 86.34% | |||||
| Project C: Marketing or Advertising Campaign | $33,470,903.72 | 1.23 | 4.84 | 90.36% | |||||
