Please complete Excel Sheet. Trying to check my answers.
New Heritage Doll Company: Capital Budgeting Exhibit 2 Selected Operating Projections for Design Your Own Doll 2010 2014 2017 2018 2011 0 2012 6,000 2013 14,360 139.3% 2015 21,435 6.0% 20,222 40.8% 2019 27,061 2016 22,721 6.0% 24,084 6.0% 25,529 6.0% 2020 28,685 6.0% 6.0% 0 1.822 Revenue Revenue Growth Production Costs Fixed Production Expense (excl depreciation) Variable Production Costs Depreciation Total Production Costs Selling, General & Administrative Total Operating Expenses 1,858 14,736 1,933 16,712 0 0 584 1,650 2.250 310 4,210 1,240 5,450 1,683 7,651 310 9,644 2.922 12,566 1,717 11,427 310 13,454 4,044 17,498 1,751 12,182 436 14,369 4,287 18,656 1,786 12.983 462 15,231 4,544 19,775 13,833 490 16,145 4,817 20,962 0 1,895 15,694 551 18,140 5,412 23,553 520 17,113 5,106 22,219 0 1,201 1,201 19,229 5,737 24,966 0 Operating Profit () (1,201) 0 550 1,794 2,724 2,779 2.946 3,123 3,310 3,509 3,719 3.0% 3.0% 3.0% 3.0% Working Capital Assumptions: Minimum Cash Balance as % of Sales Days Sales Outstanding Inventory Turnover (prod. cost/ending inv.) Days Payable Outstanding (based on tot. op. exp.) 3.0% 59.2x 3.0% 59.2x 59.2x 3.0% 59.2x 12.7x 59.2x 12.2x 59.2x 59.2x 12.6x 3.0% 59.2x 12.7x 33.9x 3.0% 59.2x 12.7x 33.9x 12.3x 12.7x 12.7x 12.7x 33.9x 33.7x 33.8x 33.9x 33.9x 33.9x 33.9x Capital Expenditures 4,610 0 310 310 2,192 826 875 928 983 1,043 1,105 Net Working Capital (NWC) Cash Account Receivable Inventory Account Payable Net Working Capital Change in NWC Scenario I: No Continu ation Value: Assuming the project will be terminated. There will be no salvage value and net working capital will be recovered in 2021 Free Cash Flows Operating Cash Flows: EBIT (1-1) + Depreciation Cash Flow from NWC: - change in NWC Cash Flow from CapExp: - CapEx Total Cash Flow Cumulative cash flow Discount Rate 8.40% NPV IRR Payback Scenario II: Assuming the project will last fore ever and free cash flow will grow at 3% per year and NWC will stay at 2,429 after 2020 Free Cash Flows Operating Cash Flows: EBIT(1-1) + Depreciation Cash Flow from NWC: - change in NWC Cash Flow from CapExp: - CapEx Total Cash Flow Continuation value Total cash flow plus continuation value Discount Rate 8.40% NPV IRR Payback Scenario III: Assuming the project will last fore ever and NWC will stay at 2,429 after 2020. The continuation value is calculated using EV/EBITDA Free Cash Flows Operating Cash Flows: EBIT(1-1) + Depreciation Cash Flow from NWC: - change in NWC Cash Flow from CapExp: - CapEx Total Cash Flow Continuation value Total cash flow plus continuation value Discount Rate 8.40% Under Recreation: EV/EBITDA 22.59 NPV IRR Payback New Heritage Doll Company: Capital Budgeting Exhibit 2 Selected Operating Projections for Design Your Own Doll 2010 2014 2017 2018 2011 0 2012 6,000 2013 14,360 139.3% 2015 21,435 6.0% 20,222 40.8% 2019 27,061 2016 22,721 6.0% 24,084 6.0% 25,529 6.0% 2020 28,685 6.0% 6.0% 0 1.822 Revenue Revenue Growth Production Costs Fixed Production Expense (excl depreciation) Variable Production Costs Depreciation Total Production Costs Selling, General & Administrative Total Operating Expenses 1,858 14,736 1,933 16,712 0 0 584 1,650 2.250 310 4,210 1,240 5,450 1,683 7,651 310 9,644 2.922 12,566 1,717 11,427 310 13,454 4,044 17,498 1,751 12,182 436 14,369 4,287 18,656 1,786 12.983 462 15,231 4,544 19,775 13,833 490 16,145 4,817 20,962 0 1,895 15,694 551 18,140 5,412 23,553 520 17,113 5,106 22,219 0 1,201 1,201 19,229 5,737 24,966 0 Operating Profit () (1,201) 0 550 1,794 2,724 2,779 2.946 3,123 3,310 3,509 3,719 3.0% 3.0% 3.0% 3.0% Working Capital Assumptions: Minimum Cash Balance as % of Sales Days Sales Outstanding Inventory Turnover (prod. cost/ending inv.) Days Payable Outstanding (based on tot. op. exp.) 3.0% 59.2x 3.0% 59.2x 59.2x 3.0% 59.2x 12.7x 59.2x 12.2x 59.2x 59.2x 12.6x 3.0% 59.2x 12.7x 33.9x 3.0% 59.2x 12.7x 33.9x 12.3x 12.7x 12.7x 12.7x 33.9x 33.7x 33.8x 33.9x 33.9x 33.9x 33.9x Capital Expenditures 4,610 0 310 310 2,192 826 875 928 983 1,043 1,105 Net Working Capital (NWC) Cash Account Receivable Inventory Account Payable Net Working Capital Change in NWC Scenario I: No Continu ation Value: Assuming the project will be terminated. There will be no salvage value and net working capital will be recovered in 2021 Free Cash Flows Operating Cash Flows: EBIT (1-1) + Depreciation Cash Flow from NWC: - change in NWC Cash Flow from CapExp: - CapEx Total Cash Flow Cumulative cash flow Discount Rate 8.40% NPV IRR Payback Scenario II: Assuming the project will last fore ever and free cash flow will grow at 3% per year and NWC will stay at 2,429 after 2020 Free Cash Flows Operating Cash Flows: EBIT(1-1) + Depreciation Cash Flow from NWC: - change in NWC Cash Flow from CapExp: - CapEx Total Cash Flow Continuation value Total cash flow plus continuation value Discount Rate 8.40% NPV IRR Payback Scenario III: Assuming the project will last fore ever and NWC will stay at 2,429 after 2020. The continuation value is calculated using EV/EBITDA Free Cash Flows Operating Cash Flows: EBIT(1-1) + Depreciation Cash Flow from NWC: - change in NWC Cash Flow from CapExp: - CapEx Total Cash Flow Continuation value Total cash flow plus continuation value Discount Rate 8.40% Under Recreation: EV/EBITDA 22.59 NPV IRR Payback
