Capital Budgeting Case
clabeh22
FIN3340CBCF20-3.xlsb.xlsxRubrick
| Grading Rubric | ||
| Points | ||
| Sales Estimation | 10 | |
| Depreciation Expense | 10 | |
| NWC Cash Flows | 10 | |
| Fixed Assets Cash Flows | 10 | |
| Operational Cash Flows | 10 | |
| Free Cash Flows | 10 | |
| Capital Budget Criteria (5 metrics) | 20 | |
| Analysis and Decision | 20 | |
| Your Total Grade | 100 | |
| Deadlines and Bonuses | ||
| Date | Bonus | |
| Earliest submission | 11/20/20 | 10 |
| Earlier Submission | 11/22/20 | 5 |
| Final | 11/24/20 | 0 |
| Late | 12/1/20 | -20 |
Links
| Net Present value NPV: |
| https://www.investopedia.com/terms/n/npv.asp |
| https://hbr.org/2014/11/a-refresher-on-net-present-value |
| https://courses.lumenlearning.com/boundless-finance/chapter/net-present-value/ |
| Internal Rate of Return (IRR): |
| https://www.investopedia.com/terms/i/irr.asp |
| https://hbr.org/2016/03/a-refresher-on-internal-rate-of-return |
| https://courses.lumenlearning.com/boundless-finance/chapter/internal-rate-of-return/ |
| Profitability Index (PI) |
| https://www.investopedia.com/terms/p/profitability.asp |
| Discounted Pay-Back |
| https://www.investopedia.com/terms/d/discounted-payback-period.asp#:~:text=The%20discounted%20payback%20period%20is,the%20time%20value%20of%20money. |
FCF - Criteria
| Capital Budgeting Case | |||||||
| Group (up to 3 students) | |||||||
| Last Name | Student 1 | Student 2 | Student 3 | ||||
| First Name | Student 1 | Student 2 | Student 3 | ||||
| Data | |||||||
| Sales year 2021 | Select a yearly amount between | $400,000 | and | $800,000 | |||
| Sales Growth Rate | Select a growth rate between | 4% | and | 8% | |||
| Fixed cost (% of sales) | Select a rate between | 80% | and | 90% | |||
| Tax Rate | Select a tax rate between | 22% | and | 28% | |||
| NWC as percentage of Sales | Select a tax rate between | 10% | and | 15% | |||
| Equipment Cost at t = 0 | ERROR:#VALUE! | ||||||
| Installation Costs at t = 0 | ERROR:#VALUE! | ||||||
| Resale Value at t = 5 | ERROR:#VALUE! | ||||||
| Ending Book Value | ERROR:#VALUE! | ||||||
| Sales Projection | 2021 | 2022 | 2023 | 2024 | 2025 | ||
| 0 | 1 | 2 | 3 | 4 | 5 | ||
| Sales Projection | $ - 0 | $ - 0 | $ - 0 | $ - 0 | |||
| Net Working Capital Cash Flows | 2021 | 2022 | 2023 | 2024 | 2025 | ||
| 0 | 1 | 2 | 3 | 4 | 5 | ||
| Required NWC | |||||||
| Change in NWC | |||||||
| Depreciation Table | 2021 | 2022 | 2023 | 2024 | 2025 | ||
| 0 | 1 | 2 | 3 | 4 | 5 | ||
| Annual Depreciation | |||||||
| Book Value | |||||||
| Fixed Assets Cash Flows | 2021 | 2022 | 2023 | 2024 | 2025 | ||
| 0 | 1 | 2 | 3 | 4 | 5 | ||
| Equipment | |||||||
| Installation Costs | |||||||
| Resale Value | |||||||
| Tax effect | |||||||
| Change in Fixed Assets | |||||||
| Free Cash Flow | 2021 | 2022 | 2023 | 2024 | 2025 | ||
| 0 | 1 | 2 | 3 | 4 | 5 | ||
| Sales | $ - 0 | ||||||
| - Fixed Costs | $ - 0 | ||||||
| - Depreciation | $ - 0 | ||||||
| EBIT | $ - 0 | ||||||
| - Taxes | $ - 0 | ||||||
| Net Income | $ - 0 | ||||||
| + Depreciation | $ - 0 | ||||||
| Operational Cash Flow | $ - 0 | ||||||
| - Change Net Working Capital | |||||||
| - Change Fixed Assets | $ - 0 | $ - 0 | $ - 0 | ||||
| FREE CASH FLOW | |||||||
| Cummulative Free Cash Flow | |||||||
| Present Value FCF | |||||||
| Cummulative Present Value FCF | |||||||
| Capital Budgeting Criteria | |||||||
| Discount rate | |||||||
| Pay_Back (years) | years | ERROR:#VALUE! | ERROR:#VALUE! | ||||
| Discounted Pay_Back (years) | years | ERROR:#VALUE! | ERROR:#VALUE! | ||||
| Profitability Index | ERROR:#VALUE! | ERROR:#VALUE! | |||||
| Net Present Value NPV | dollars | ERROR:#VALUE! | ERROR:#VALUE! | ||||
| Internal Rate of Return IRR | % | ERROR:#NUM! | ERROR:#VALUE! | ||||
The discount rate of return is the expected or required rate of return for an investment in a project or a business venture. Also known as the cost of capital, it is used to estimate the current value of an investment based on its expected future cash flows. The required rate of return differs from investor to investor. Select your own discount rate.
Please fill in all cells highlighted in green
t=0 indicates the beginning of 2021, t=1 indicates the ending of year 2021 and the beginning of year 2022, ...
Analysis and Takeways
Analysis and Takeways:
Example
| Capital Budgeting Case | ||||||
| Group (up to 3 students) | ||||||
| Last Name | Moon | Doe | Lopez | |||
| First Name | Ken | John | Mario | |||
| Data | ||||||
| Sales year 2021 | Select a yearly amount between | $400,000 | and | $800,000 | 520,000 | |
| Sales Growth Rate | Select a growth rate between | 4% | and | 8% | 6% | |
| Fixed cost (% of sales) | Select a rate between | 80% | and | 90% | 85% | |
| Tax Rate | Select a tax rate between | 22% | and | 28% | 25% | |
| NWC as percentage of Sales | Select a tax rate between | 10% | and | 15% | 12% | |
| Equipment Cost at t = 0 | $ 208,000 | |||||
| Installation Costs at t = 0 | $ 16,640 | |||||
| Resale Value at t = 5 | $ 41,600 | |||||
| Ending Book Value | $ 22,464 | |||||
| Sales Projection | 2021 | 2022 | 2023 | 2024 | 2025 | |
| 0 | 1 | 2 | 3 | 4 | 5 | |
| Sales Projection | $ 520,000 | $ 551,200 | $ 584,272 | $ 619,328 | $ 656,488 | |
| Net Working Capital Cash Flows | 2021 | 2022 | 2023 | 2024 | 2025 | |
| 0 | 1 | 2 | 3 | 4 | 5 | |
| Required NWC | $ 62,400 | $ 66,144 | $ 70,113 | $ 74,319 | $ 78,779 | |
| Change in NWC | $ 3,744 | $ 3,969 | $ 4,207 | $ 4,459 | $ (78,779) | |
| Depreciation Table | 2021 | 2022 | 2023 | 2024 | 2025 | |
| 0 | 1 | 2 | 3 | 4 | 5 | |
| Annual Depreciation | $ 40,435 | $ 40,435 | $ 40,435 | $ 40,435 | $ 40,435 | |
| Book Value | $ 224,640 | $ 184,205 | $ 143,770 | $ 103,334 | $ 62,899 | $ 22,464 |
| Fixed Assets Cash Flows | 2021 | 2022 | 2023 | 2024 | 2025 | |
| 0 | 1 | 2 | 3 | 4 | 5 | |
| Equipment | $ 208,000 | |||||
| Installation Costs | $ 16,640 | |||||
| Resale Value | $ (41,600.0) | |||||
| Tax effect | $ 4,784.0 | |||||
| Change in Fixed Assets | $ 224,640 | $ (36,816.0) | ||||
| Free Cash Flow | 2021 | 2022 | 2023 | 2024 | 2025 | |
| 0 | 1 | 2 | 3 | 4 | 5 | |
| Sales | $ - 0 | $ 520,000 | $ 551,200 | $ 584,272 | $ 619,328 | $ 656,488 |
| - Fixed Costs | $ - 0 | $ 442,000 | $ 468,520 | $ 496,631 | $ 526,429 | $ 558,015 |
| - Depreciation | $ - 0 | $ 40,435 | $ 40,435 | $ 40,435 | $ 40,435 | $ 40,435 |
| EBIT | $ - 0 | $ 37,565 | $ 42,245 | $ 47,206 | $ 52,464 | $ 58,038 |
| - Taxes | $ - 0 | $ 9,391 | $ 10,561 | $ 11,801 | $ 13,116 | $ 14,510 |
| Net Income | $ - 0 | $ 28,174 | $ 31,684 | $ 35,404 | $ 39,348 | $ 43,529 |
| + Depreciation | $ - 0 | $ 40,435 | $ 40,435 | $ 40,435 | $ 40,435 | $ 40,435 |
| Operational Cash Flow | $ - 0 | $ 68,609 | $ 72,119 | $ 75,839 | $ 79,783 | $ 83,964 |
| - Change Net Working Capital | $ 62,400 | $ 3,744 | $ 3,969 | $ 4,207 | $ 4,459 | $ (78,779) |
| - Change Fixed Assets | $ 224,640 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ (36,816) |
| FREE CASH FLOW | $ (287,040) | $ 64,865 | $ 68,150 | $ 71,633 | $ 75,324 | $ 199,558 |
| Cummulative Free Cash Flow | $ (287,040) | $ (222,175) | $ (154,025) | $ (82,392) | $ (7,068) | $ 192,490 |
| Present Value FCF | $ (287,040) | $ 58,968.00 | $ 56,322.45 | $ 53,818.66 | $ 51,447.35 | $ 123,909.98 |
| Cummulative Present Value FCF | $ (287,040) | $ (228,072.00) | $ (171,749.55) | $ (117,930.89) | $ (66,483.54) | $ 57,426.45 |
| Capital Budgeting Criteria | ||||||
| Discount rate | 10.00% | |||||
| Pay_Back (years) | 4.04 | years | 4.035419862579909 | |||
| Discounted Pay_Back (years) | 4.54 | years | 4.536547050085795 | |||
| Profitability Index | 1.20 | 1.200064264547039 | ||||
| Net Present Value NPV | 57426.45 | 57426.44649558206 | ||||
| Internal Rate of Return IRR | 0.16 | 0.16252811573365644 | ||||
