Can I ask for you assistance again in reference to the company Vonage?

ANALYSIS OF CAPITAL BUDGETING PROJECT LENGTH OF PROJECT 1. What is the length, in years, of this project 5.00 Years DISCOUNT RATE FOR PROJECT 1. What is the discount rate / hurdle rate for project 10.00% This is the project's discount rate, hurdle rate, or required rate of return TAX RATES 1. What is the firm's marginal income tax rate 2. What is the firm's capital gains tax rate 34.00% 34.00% Ordinarily this should be 15% DISPOSAL OF EXISTING PLANT AND/OR EQUIPMENT (Complete if any capital assets are to be sold) 1. Sale price of assets to be sold $0 If no assets are to be sold, enter $0 for the sale price or book value of assets 2. Book value of assets to be sold $0 3. Tax on gain, tax credit on loss (using capital gains tax rate) $0 4. Free cash flow from sale of assets $0 Automatically calculates the FCF for the disposition of any assets that are sold at beginning of project COST OF NEW PLANT AND/OR EQUIPMENT (If project involves capital expenditures, $0 if none) 1. Cost of new land, if any (non-depreciable) $0 $0 Estimated sale price or salvage value 2. Cost of new plant and equipment $9,700,000 $0 Estimated sale price or salvage value 3. Shipping and installation costs $300,000 $10,000,000 Depreciable Base $2,000,000 Depreciation per Year 4. Number of years assets to be depreciated 5.00 Maximum 20 years 5. Year that all assets are to be sold 5.00 th Year Enter the acquisition costs of assets and estimated liquidation values Calculation of After-tax Proceeds of Sale of Assets $0 Proceeds from Land Sale $0 Proceeds from Plant & Equipment Sale $0.00 Book Value of Plant and Equipment $0 Tax on Sale of Plant & Equip. at Capital Gains Rate $0 After-tax Proceeds of Sale The model automatically calculates depreciation for new assets purchased for the project - land is not depreciated The model automatically calculates the FCF for the sale of assets at end of project REVENUES TO BE GENERATED BY PROJECT (If project involves selling goods or services) Year 1 2 3 4 5 6 7 8 9 10 Units Sold 50,000 100,000 100,000 70,000 50,000 0 0 0 0 0 Sales price Variable Cost Annual Fixed per Unit per Unit Costs $150 $80 $500,000 $150 $80 $500,000 $150 $80 $500,000 $150 $80 $500,000 $130 $80 $500,000 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Year 11 12 13 14 15 16 17 18 19 20 Units Sold 0 0 0 0 0 0 0 0 0 0 Sales price Variable Cost Annual Fixed per Unit per Unit Costs $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 COST SAVINGS TO BE GENERATED BY PROJECT (If project involves cost savings instead of new revenues) Year 1 2 3 4 5 6 7 Cost Savings per Year $0 $0 $0 $0 $0 $0 $0 WORKING CAPITAL REQUIREMENTS 1. Working capital requirement in Year 1 2. Ongoing working capital requirement as a percentage of revenues or cost savings Year 8 9 10 11 12 13 14 Cost Savings per Year $0 $0 $0 $0 $0 $0 $0 $100,000 10.00% Year 15 16 17 18 19 20 Cost Savings per Year $0 $0 $0 $0 $0 $0 Model is set up to accommodate capital budgeting projects that principally involve development of new projects or services Generally, model should use one approach or the other Model can accommodate projects that essentially involve cost reduction strategies Use this section to estimate the additional working capital requirements. Note that the ongoing working capital requirements are calculated as a percentage of revenues or cost reduction savings. 0 4 70,000 $150 5 50,000 $130 Sales Revenue Less Variable Costs: Less Fixed Costs: Annual Cost Reduction Equals: EBDIT Less Depreciation: Equals: EBIT Taxes @ Marginal Income Tax Rate $7,500,000 $15,000,000 $15,000,000 $10,500,000 $4,000,000 $8,000,000 $8,000,000 $5,600,000 $500,000 $500,000 $500,000 $500,000 $0 $0 $0 $0 $3,000,000 $6,500,000 $6,500,000 $4,400,000 $2,000,000 $2,000,000 $2,000,000 $2,000,000 $1,000,000 $4,500,000 $4,500,000 $2,400,000 $340,000 $1,530,000 $1,530,000 $816,000 Calculate Operating Cash Flow EBIT Minus: Taxes Plus: Depreciation Equals: Operating Cash Flow $1,000,000 $340,000 $2,000,000 $2,660,000 Units Sold Sale Price Calculate Net Working Capital Revenue Cost Reductions Initial Working Cap. Investment Working Capital Needs Change in Working Capital Liquidation of Working Capital Calculate Free Cash Flow Operating Cash Flow FCF from Sale of Assets Sold Minus: Change in NWC Minus: Change in Capital Spending Sale of Assets Purchased for Project Free Cash Flow 1 50,000 $150 2 100,000 $150 3 100,000 $150 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 $6,500,000 $4,000,000 $500,000 $0 $2,000,000 $2,000,000 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $2,400,000 $816,000 $2,000,000 $3,584,000 $0 $0 $2,000,000 $2,000,000 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $7,500,000 $15,000,000 $15,000,000 $10,500,000 $0 $0 $0 $0 $6,500,000 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $4,500,000 $1,530,000 $2,000,000 $4,970,000 $4,500,000 $1,530,000 $2,000,000 $4,970,000 $100,000 $100,000 $0 ($100,000) ($10,000,000) ($10,100,000) $750,000 $650,000 $0 $1,500,000 $750,000 $0 $1,500,000 $0 $0 $1,050,000 ($450,000) $0 $650,000 ($400,000) $650,000 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $2,660,000 $4,970,000 $4,970,000 $3,584,000 $2,000,000 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 ($650,000) $0 $0 $2,010,000 ($750,000) $0 $0 $4,220,000 $0 $0 $0 $4,970,000 $450,000 $0 $0 $4,034,000 $1,050,000 $0 $0 $3,050,000 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 ANALYSIS OF CAPITAL BUDGETING PROJECT NPV $3,597,997 =NPV(E4,D87:W87)+C87 IRR 22.30% =IRR(C87:W87,0.01) MIRR 11.69% =MIRR(C87:W87,E4,E4) PI 1.36=NPV(E4,D87:W87)/-C87 ACCEPT THE PROJECT Model automatically calculates the Free Cash Flows Model can accommodate projects up to 20 years Use this line to include any additional capital spending that occurs during project. If it is an expenditure, or outflow, use a minus sign before the amount Model automatically calculates NPV, IRR, MIRR and PI using Excel functions Model automatically indicates the Accept / Reject decision based on NPV Capital Budgeting Investment, Model, and Analysis Use the company that was selected for the class assignments. This assignment involves development of an Excel-based capital budgeting model and an analysis of a proposed capital budgeting project. Prepare Capital Budgeting Model and Analysis Describe a capital budgeting project (i.e., an investment in fixed assets) that might be undertaken by the company that you have selected. Make sure that the project has an initial investment in Year 0, followed by a series of annual cash flows for at least seven (7) years. In addition, determine the discount rate, or hurdle rate, that is appropriate for this project and explain the determination of that rate. Develop your own Excel spreadsheet model that can be used to determine the Net Present Value (NPV), Internal Rate of Return (IRR), Modified Internal Rate of Return (MIRR), and Profitability Index (PI). The Excel spreadsheet that you develop must use Excel's automated financial functions for determining the NPV, IRR, MIRR, and PI. Following the completion of the spreadsheet analysis, explain whether, or not, the project should be implemented? Also, discuss what the various indicators (i.e., NPV, IRR, MIRR and PI) mean? For this assignment, it is necessary to develop your own Excel spreadsheet; it needs to be submitted. The analysis needs to include: 1. Description of Proposed Capital Budgeting Project - Describe a capital budgeting project (i.e., an investment in fixed assets) that might be undertaken by the company that you have selected. Make sure that the project has an initial investment in Year 0, followed by a series of annual cash flows for at least seven (7) years. In addition, determine the discount rate, or hurdle rate, that is appropriate for this project and explain the determination of that rate. 2. Explanation of the Excel Capital Budgeting Model - Develop your own Excel spreadsheet model that can be used to determine the Net Present Value (NPV), Internal Rate of Return (IRR), Modified Internal Rate of Return (MIRR), and Profitability Index (PI). Explain how the model is constructed and the required inputs. Discuss the outputs of the model. 3. Explanation and Interpretation of Capital Budgeting Criteria - Prepare a written analysis of the results including NPV,IRR, MIRR, PI, and Profitability Analysis. 4. Recommendation About Implementation of Capital Budgeting Project - Develop and defend a recommendation about whether the proposed project should be implemented or not. Writing Instructions The discussion portion of the analysis should be three to five pages in length, double spaced, and should employ APA style and format for reference citations. Supporting data (e.g., figures, tables, etc.) and references should be submitted limited to four separate attachments in an appendix after the written portion of the paper. The paper should begin with a short introduction and then proceed to examine the four topics outlined in the previous section. The subheadings used in the paper should be: 1. Introduction 2. Description of Proposed Capital Budgeting Project 3. Explanation of the Excel Capital Budgeting Model 4. Explanation and Interpretation of Capital Budgeting Criteria 5. Recommendation About Implementation of Capital Budgeting Project Completeness of analysis: The analysis must demonstrate a solid understanding of capital budgeting and the analysis of corporate investments. All assumptions used in preparing the projections for the project need to be thoroughly explained. Organization: The paper should be well-organized and follow a logical pattern of analysis and discussion. Presentation: Papers should meet professional business standards and meet APA formatting requirements. Spelling, punctuation, and grammar: There should be few errors in grammar and punctuation. All sentences must be complete and well-structured. ANALYSIS OF CAPITAL BUDGETING PROJECT LENGTH OF PROJECT 1. What is the length, in years, of this project 7.00 Years DISCOUNT RATE FOR PROJECT 1. What is the discount rate / hurdle rate for project 10.00% This is the project's discount rate, hurdle rate, or required rate of return TAX RATES 1. What is the firm's marginal income tax rate 2. What is the firm's capital gains tax rate 40.00% 20.00% Ordinarily this should be 15% DISPOSAL OF EXISTING PLANT AND/OR EQUIPMENT (Complete if any capital assets are to be sold) 1. Sale price of assets to be sold $7 If no assets are to be sold, enter $0 for the sale price or book value of assets 2. Book value of assets to be sold $3 3. Tax on gain, tax credit on loss (using capital gains tax rate) $1 4. Free cash flow from sale of assets $6 Automatically calculates the FCF for the disposition of any assets that are sold at beginning of project COST OF NEW PLANT AND/OR EQUIPMENT (If project involves capital expenditures, $0 if none) 1. Cost of new land, if any (non-depreciable) $0 $0 Estimated sale price or salvage value 2. Cost of new plant and equipment $950,000 $0 Estimated sale price or salvage value 3. Shipping and installation costs $613,000 $1,563,000 Depreciable Base $223,286 Depreciation per Year 4. Number of years assets to be depreciated 10.00 Maximum 20 years 5. Year that all assets are to be sold 3.00 th Year Enter the acquisition costs of assets and estimated liquidation values Calculation of After-tax Proceeds of Sale of Assets $0 Proceeds from Land Sale $0 Proceeds from Plant & Equipment Sale ($669,857.14) Book Value of Plant and Equipment $133,971 Tax on Sale of Plant & Equip. at Capital Gains Rate ($133,971) After-tax Proceeds of Sale The model automatically calculates depreciation for new assets purchased for the project - land is not depreciated The model automatically calculates the FCF for the sale of assets at end of project REVENUES TO BE GENERATED BY PROJECT (If project involves selling goods or services) Year 1 2 3 4 5 6 7 8 9 10 Units Sold 680,000 590,000 565,000 585,000 570,000 590,000 520,000 0 0 0 Sales price Variable Cost Annual Fixed per Unit per Unit Costs $37.00 $13.75 $400,000 $37.93 $13.75 $400,000 $38.95 $13.75 $400,000 $40.16 $13.75 $400,000 $41.48 $13.75 $400,000 $42.93 $13.75 $400,000 $44.56 $13.75 $400,000 $0 $0 $0 $0 $0 $0 $0 $0 $0 Year 11 12 13 14 15 16 17 18 19 20 Units Sold 0 0 0 0 0 0 0 0 0 0 Sales price Variable Cost per Unit per Unit $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Annual Fixed Costs $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 COST SAVINGS TO BE GENERATED BY PROJECT (If project involves cost savings instead of new revenues) Year 1 2 3 4 5 6 7 Cost Savings per Year $0 $0 $0 $0 $0 $0 $0 WORKING CAPITAL REQUIREMENTS 1. Working capital requirement in Year 1 2. Ongoing working capital requirement as a percentage of revenues or cost savings Year 8 9 10 11 12 13 14 Cost Savings per Year $0 $0 $0 $0 $0 $0 $0 $99,786 7.40% Year 15 16 17 18 19 20 Cost Savings per Year $0 $0 $0 $0 $0 $0 Use this section to estimate the additional working capital requirements. Note that the ongoing working capital requirements are calculated as a percentage of revenues or cost reduction savings. Model is set up to accommodate capital budgeting projects that principally involve development of new projects or services Generally, model should use one approach or the other Model can accommodate projects that essentially involve cost reduction strategies 0 Units Sold Sale Price 1 680,000 $37 2 590,000 $38 3 565,000 $39 4 585,000 $40 5 570,000 $41 6 590,000 $43 7 8 9 10 11 12 13 14 15 16 17 18 19 20 520,000 $45 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 Sales Revenue Less Variable Costs: Less Fixed Costs: Annual Cost Reduction Equals: EBDIT Less Depreciation: Equals: EBIT Taxes @ Marginal Income Tax Rate $25,160,000 $22,375,750 $22,006,171 $23,491,490 $23,644,486 $25,330,711 $9,350,000 $8,112,500 $7,768,750 $8,043,750 $7,837,500 $8,112,500 $400,000 $400,000 $400,000 $400,000 $400,000 $400,000 $0 $0 $0 $0 $0 $0 $15,410,000 $13,863,250 $13,837,421 $15,047,740 $15,406,986 $16,818,211 $223,286 $223,286 $223,286 $223,286 $223,286 $223,286 $15,186,714 $13,639,964 $13,614,135 $14,824,454 $15,183,700 $16,594,925 $6,074,686 $5,455,986 $5,445,654 $5,929,782 $6,073,480 $6,637,970 $23,173,737 $7,150,000 $400,000 $0 $15,623,737 $223,286 $15,400,451 $6,160,180 $0 $0 $0 $0 $0 $223,286 ($223,286) ($89,314) $0 $0 $0 $0 $0 $223,286 ($223,286) ($89,314) $0 $0 $0 $0 $0 $223,286 ($223,286) ($89,314) $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Calculate Operating Cash Flow EBIT Minus: Taxes Plus: Depreciation Equals: Operating Cash Flow $15,186,714 $13,639,964 $13,614,135 $14,824,454 $15,183,700 $16,594,925 $6,074,686 $5,455,986 $5,445,654 $5,929,782 $6,073,480 $6,637,970 $223,286 $223,286 $223,286 $223,286 $223,286 $223,286 $9,335,314 $8,407,264 $8,391,767 $9,117,958 $9,333,506 $10,180,241 $15,400,451 $6,160,180 $223,286 $9,463,556 ($223,286) ($89,314) $223,286 $89,314 ($223,286) ($89,314) $223,286 $89,314 ($223,286) ($89,314) $223,286 $89,314 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $25,160,000 $22,375,750 $22,006,171 $23,491,490 $23,644,486 $25,330,711 $0 $0 $0 $0 $0 $0 $23,173,737 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Calculate Net Working Capital Revenue Cost Reductions Initial Working Cap. Investment Working Capital Needs Change in Working Capital Liquidation of Working Capital Calculate Free Cash Flow Operating Cash Flow FCF from Sale of Assets Sold Minus: Change in NWC Minus: Change in Capital Spending Sale of Assets Purchased for Project Free Cash Flow $99,786 $1,861,840 $1,762,054 $0 $1,655,806 ($206,035) $0 $1,628,457 ($27,349) $0 $1,738,370 $109,914 $0 $1,749,692 $11,322 $0 $1,874,473 $124,781 $0 $1,714,857 ($159,616) $1,714,857 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $9,335,314 $6 ($99,786) ($1,762,054) ($1,563,000) $0 $0 ($1,662,780) $7,573,260 $8,407,264 $8,391,767 $9,117,958 $9,333,506 $10,180,241 $9,463,556 $89,314 $89,314 $89,314 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $206,035 $0 $0 $8,613,299 $27,349 $0 ($133,971) $8,419,116 ($109,914) $0 $0 $9,008,045 ($11,322) ($124,781) $0 $0 $0 $0 $9,322,184 $10,055,460 $1,874,473 $0 $0 $11,338,029 $0 $0 $0 $89,314 $0 $0 $0 $89,314 $0 $0 $0 $89,314 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $99,786 ANALYSIS OF CAPITAL BUDGETING PROJECT NPV $42,215,021 =NPV(E4,D87:W87)+C87 IRR 466.36% =IRR(C87:W87,0.01) MIRR 29.56% =MIRR(C87:W87,E4,E4) PI 26.39=NPV(E4,D87:W87)/-C87 ACCEPT THE PROJECT Model automatically calculates the Free Cash Flows Model can accommodate projects up to 20 years Use this line to include any additional capital spending that occurs during project. If it is an expenditure, or outflow, use a minus sign before the amount Model automatically calculates NPV, IRR, MIRR and PI using Excel functions Model automatically indicates the Accept / Reject decision based on NPV Marginal Tax Rate http://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/countrytaxrate.htm Capital Gains Tax Rate http://www.taxpolicycenter.org/briefing-book/how-are-capital-gains-taxed ANALYSIS OF CAPITAL BUDGETING PROJECT LENGTH OF PROJECT 1. What is the length, in years, of this project 7.00 Years DISCOUNT RATE FOR PROJECT 1. What is the discount rate / hurdle rate for project 8.4200% This is the project's discount rate, hurdle rate, or required rate of return TAX RATES 1. What is the firm's marginal income tax rate 2. What is the firm's capital gains tax rate 40.00% 20.00% Ordinarily this should be 15% DISPOSAL OF EXISTING PLANT AND/OR EQUIPMENT (Complete if any capital assets are to be sold) 1. Sale price of assets to be sold $200,000 If no assets are to be sold, enter $0 for the sale price or book value of assets 2. Book value of assets to be sold $171,428.6 3. Tax on gain, tax credit on loss (using capital gains tax rate) $5,714 4. Free cash flow from sale of assets $194,286 Automatically calculates the FCF for the disposition of any assets that are so COST OF NEW PLANT AND/OR EQUIPMENT (If project involves capital expenditures, $0 if none) 1. Cost of new land, if any (non-depreciable) $0 $0 Estimated sale price or salvage value 2. Cost of new plant and equipment $1,000,000 $0 Estimated sale price or salvage value 3. Shipping and installation costs $200,000 $1,200,000 Depreciable Base $171,429 Depreciation per Year 4. Number of years assets to be depreciated 12.00 Maximum 20 years 5. Year that all assets are to be sold 5.00 th Year Calculation of After-tax Proceeds of Sale of As $0 Proceeds from Land $0 Proceeds from Plant ($857,142.86) Book Value of Plant $171,429 Tax on Sale of Plant ($171,429) After-tax Proceeds o REVENUES TO BE GENERATED BY PROJECT (If project involves selling goods or services) Year 1 2 3 4 5 6 7 8 9 10 Units Sold 150,000 150,000 125,000 100,000 100,000 80,000 60,000 0 0 0 Sales price per Variable Cost Annual Fixed Unit per Unit Costs $27.00 $15.25 $420,000 $27.68 $15.63 $420,000 $28.42 $16.02 $420,000 $29.30 $16.42 $420,000 $30.27 $16.83 $420,000 $31.33 $17.25 $420,000 $32.52 $17.69 $420,000 $0 $0 $0 $0 $0 $0 $0 $0 $0 Year 11 12 13 14 15 16 17 18 19 20 Units Sold 0 0 0 0 0 0 0 0 0 0 Sales price Variable Cost per Unit per Unit $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 COST SAVINGS TO BE GENERATED BY PROJECT (If project involves cost savings instead of new revenues) Annual Fixed Costs $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Year 1 2 3 4 5 6 7 Cost Savings per Year $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 WORKING CAPITAL REQUIREMENTS 1. Working capital requirement in Year 1 2. Ongoing working capital requirement as a percentage of revenues or cost savings Year 8 9 10 11 12 13 14 Cost Savings per Year $0 $0 $0 $0 $0 $0 $0 $100,000 20.00% Year 15 16 17 18 19 20 Cost Savings per Year $0 $0 $0 $0 $0 $0 Use this section to estimate the additional working capital requirements. Note that the ongoing working capital requirements are calculated as a percentage of revenues or cost reduction savings. 0 1 150,000 $27 2 150,000 $28 3 125,000 $28 4 100,000 $29 5 100,000 $30 6 80,000 $31 Sales Revenue Less Variable Costs: Less Fixed Costs: Annual Cost Reduction Equals: EBDIT Less Depreciation: Equals: EBIT Taxes @ Marginal Income Tax Rate $4,050,000 $2,287,500 $420,000 $10,000 $1,352,500 $171,429 $1,181,071 $472,429 $4,151,250 $2,344,688 $420,000 $10,000 $1,396,563 $171,429 $1,225,134 $490,054 $3,552,778 $2,002,754 $420,000 $10,000 $1,140,024 $171,429 $968,596 $387,438 $2,930,331 $1,642,258 $420,000 $10,000 $878,073 $171,429 $706,645 $282,658 $3,027,032 $1,683,315 $420,000 $10,000 $933,718 $171,429 $762,289 $304,916 Calculate Operating Cash Flow EBIT Minus: Taxes Plus: Depreciation Equals: Operating Cash Flow $1,181,071 $472,429 $171,429 $880,071 $1,225,134 $490,054 $171,429 $906,509 $968,596 $387,438 $171,429 $752,586 $706,645 $282,658 $171,429 $595,415 $4,050,000 $10,000 $4,151,250 $10,000 $3,552,778 $10,000 $812,000 $712,000 $0 $832,250 $20,250 $0 $880,071 ($712,000) $0 $0 $168,071 Units Sold Sale Price Calculate Net Working Capital Revenue Cost Reductions Initial Working Cap. Investment Working Capital Needs Change in Working Capital Liquidation of Working Capital Calculate Free Cash Flow Operating Cash Flow FCF from Sale of Assets Sold Minus: Change in NWC Minus: Change in Capital Spending Sale of Assets Purchased for Project Free Cash Flow 7 8 60,000 $33 0 $0 $2,506,383 $1,380,318 $420,000 $10,000 $716,065 $171,429 $544,636 $217,854 $1,951,219 $1,061,119 $420,000 $10,000 $480,100 $171,429 $308,671 $123,468 $0 $0 $0 $0 $0 $171,429 ($171,429) ($68,571) $762,289 $304,916 $171,429 $628,802 $544,636 $217,854 $171,429 $498,210 $308,671 $123,468 $171,429 $356,631 ($171,429) ($68,571) $171,429 $68,571 $2,930,331 $10,000 $3,027,032 $10,000 $2,506,383 $10,000 $1,951,219 $10,000 $0 $0 $712,556 ($119,694) $0 $588,066 ($124,489) $0 $607,406 $19,340 $0 $503,277 ($104,130) $0 $392,244 ($111,033) $392,244 $0 $0 $0 $906,509 $752,586 $595,415 $628,802 $498,210 $356,631 $68,571 ($20,250) $0 $0 $886,259 $119,694 $0 $0 $872,280 $124,489 $0 $0 $719,905 ($19,340) $0 ($171,429) $609,462 $104,130 $0 $0 $602,340 $503,277 $0 $0 $859,908 $0 $0 $0 $68,571 $100,000 $100,000 $194,286 ($100,000) ($1,200,000) ($1,105,714) ANALYSIS OF CAPITAL BUDGETING PROJECT NPV $2,428,400 =NPV(E4,D87:W87)+C87 IRR 49.62% =IRR(C87:W87,0.01) MIRR 14.91% =MIRR(C87:W87,E4,E4) PI 3.20=NPV(E4,D87:W87)/-C87 ACCEPT THE PROJECT Model automatically calculates the Free Cash Flows Use this line to include any additional capital spending that occurs during p If it is an expenditure, or outflow, use a minus sign before the amount Model automatically calculates NPV, IRR, MIRR and PI using Excel functions Model automatically indicates the Accept / Reject decision based on NPV that are sold at beginning of project Enter the acquisition costs of assets and estimated liquidation values Sale of Assets from Land Sale from Plant & Equipment Sale ue of Plant and Equipment le of Plant & Equip. at Capital Gains Rate Proceeds of Sale The model automatically calculates depreciation for new assets purchased for the project - land is not depreciated The model automatically calculates the FCF for the sale of assets at end of project Model is set up to accommodate capital budgeting projects that principally involve development of new projects or services Generally, model should use one approach or the other Model can accommodate projects that essentially involve cost reduction strategies 9 10 11 12 13 14 15 16 17 18 19 20 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 0 $0 $0 $0 $0 $0 $0 $171,429 ($171,429) ($68,571) $0 $0 $0 $0 $0 $171,429 ($171,429) ($68,571) $0 $0 $0 $0 $0 $171,429 ($171,429) ($68,571) $0 $0 $0 $0 $0 $171,429 ($171,429) ($68,571) $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 ($171,429) ($68,571) $171,429 $68,571 ($171,429) ($68,571) $171,429 $68,571 ($171,429) ($68,571) $171,429 $68,571 ($171,429) ($68,571) $171,429 $68,571 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $68,571 $68,571 $68,571 $68,571 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $68,571 $0 $0 $0 $68,571 $0 $0 $0 $68,571 $0 $0 $0 $68,571 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 s during project. amount Model can accommodate projects up to 20 years Marginal Tax Rate http://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/countrytaxrate.htm Capital Gains Tax Rate http://www.taxpolicycenter.org/briefing-book/how-are-capital-gains-taxed