Assessment 2: Evaluation of Capital Projects
bfied0404
cf_capital_budgeting.xlsxExport Summary
| This document was exported from Numbers. Each table was converted to an Excel worksheet. All other objects on each Numbers sheet were placed on separate worksheets. Please be aware that formula calculations may differ in Excel. | |||
| Numbers Sheet Name | Numbers Table Name | Excel Worksheet Name | |
| ProjectA | |||
| Table 1 | ProjectA | ||
| ProjectB | |||
| Table 1 | ProjectB | ||
| ProjectC | |||
| Table 1 | ProjectC | ||
| Comparison | |||
| Table 1 | Comparison | ||
| Combined_EvaluateCapProjects | |||
| Table 1 | Combined_EvaluateCapProjects |
ProjectA
| 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) Imported Author: Imported Author: 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 Imported Author: Imported Author: 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 Imported Author: Imported Author: 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 Imported Author: Imported Author: 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 Imported Author: Imported Author: 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 Imported Author: Imported Author: Salvage value on 8th year - (Salvage Value * Tax rate 25%) |
Imported Author: Imported Author: 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%) Imported Author: Imported Author: PV https://www.accountingtools.com/articles/what-is-the-present-value-factor.html | 100% | 0.93 | 0.86 | 0.79 | 0.74 | 0.68 | 0.63 | 0.58 | 0.54 | |
| Present value of cash flows Imported Author: Imported Author: 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) |
Imported Author: Imported Author: 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 Imported Author: Imported Author: 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 |
&"Helvetica Neue,Regular"&12&K000000&P
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) Imported Author: Imported Author: 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 |
&"Helvetica Neue,Regular"&12&K000000&P
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 |
&"Helvetica Neue,Regular"&12&K000000&P
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% |
&"Helvetica Neue,Regular"&12&K000000&P
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) Imported Author: Imported Author: 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 Imported Author: Imported Author: 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 Imported Author: Imported Author: 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 Imported Author: Imported Author: 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 Imported Author: Imported Author: 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 Imported Author: Imported Author: 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%) Imported Author: Imported Author: PV https://www.accountingtools.com/articles/what-is-the-present-value-factor.html | 100% | 0.93 | 0.86 | 0.79 | 0.74 | 0.68 | 0.63 | 0.58 | 0.54 |
| Present value of cash flows Imported Author: Imported Author: 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 Imported Author: Imported Author: 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) Imported Author: Imported Author: Using straight line depreciation Annual Depreciation= (Asset Cost - Salvage Value)/Asset Life https://corporatefinanceinstitute.com/resources/knowledge/accounting/straight-line-depreciation/ |
Imported Author: Imported Author: 60% of Annual Sales i.e of 20 million =C9*0.6 |
Imported Author: Imported Author: PV https://www.accountingtools.com/articles/what-is-the-present-value-factor.html |
Imported Author: Imported Author: 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) |
Imported Author: Imported Author: Cost of sales reducing by 5% per year for 8 years |
Imported Author: Imported Author: 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% | |||||
&"Helvetica Neue,Regular"&12&K000000&P
