Accounting Project Final -- AWARDED
Lew Core
Module4CapitalBudgetingTemplate.xlsProject Capital Budget and BE
| Recommended Capital Budgeting Template Used in MS6010 Course Project. You can use another template if desired. | |||||||||||||
| Enter a complete set of financial statements for your company in the other tab. | |||||||||||||
| For this tab, complete only the yellow boxes; everything else is done by formula. I have added several rows below template for you to complete payback calculations, if desired. | |||||||||||||
| Use this template to provide the capital budgeting information on your course project. Change titles to work with your project as needed. | |||||||||||||
| Some items will not apply to your project and can be left blank. Template assumes equipment purchase. If you have purchases other than equipment | |||||||||||||
| you will need to adjust the depreciation rates to achieve correct depreciation | |||||||||||||
| Part 1. Key Input Data: For this project you get to make up reasonable numbers for the project idea you will recommend for the company you choose | |||||||||||||
| $ Equipment cost plus installation | Enter a reasonable price of recommended equipment purchase | ||||||||||||
| $ Increase in current assets | How much will your current assets increase as a result of this project | ||||||||||||
| $ Increase in current liabilities | How much will your current liabilities increase as a result of this project | Using some of the data from the left, what is the break even in units? | |||||||||||
| Unit sales | What are you unit sales each year | Enter in your formula here so that the correct B/E units are shown. | |||||||||||
| $ Sales price per unit | How much will you sell each item for? | What is the B/E in dollars? | |||||||||||
| % Variable cost per unit | What is the variable cost per each item sold as a percentage? | ||||||||||||
| $ Variable cost per unit | $ - 0 | ||||||||||||
| $ Fixed costs | What are the fixed costs for this project? | ||||||||||||
| Market value$ of equipment in Y5 | Enter in a reasonable market value in dollars at end of project | ||||||||||||
| Tax rate Percentage | Use the precentage as specified | ||||||||||||
| WACC or Discount Percentage | Use the precentage as specified | ||||||||||||
| Part 2. Depreciation Schedule if applicable. If you have equipment, there is always depreciation | Years | Accum'd | |||||||||||
| Year | Initial Cost | 1 | 2 | 3 | 4 | 5 | Deprn | ||||||
| % Equipment Deprn Rate | 0% | 0% | 0% | 0% | 0% | Enter in Depreciation %- straight line | |||||||
| Equipment Deprn, Dollars | $0 | $0 | $0 | $0 | $0 | $0 | |||||||
| Ending Bk Val: Cost - Accum'd Deprn | $0 | ||||||||||||
| Part 3. Net Salvage Values | Equipment | ||||||||||||
| Estimated Market Value in Year 5 | $0 | ||||||||||||
| Book Value in Y5 | 0 | ||||||||||||
| Expected Gain or Loss | 0 | ||||||||||||
| Taxes paid on gain at tax rate percentage | 0 | ||||||||||||
| Net cash flow from salvage | $0 | ||||||||||||
| Part 4. Projected Net Cash Flows (Time line of annual cash flows) | |||||||||||||
| Years, 1-4 basis | 0 | 1 | 2 | 3 | 4 | 5 | |||||||
| Years, actual year basis | 20xx | 20xx | 20xx | 20xx | 20xx | 20xx | |||||||
| Investment Outlays at Time Zero: | |||||||||||||
| Equipment | 0 | ||||||||||||
| Increase in Net Operating WC | 0 | ||||||||||||
| Operating Cash Flows over the Project's Life: | |||||||||||||
| Units sold | 0 | 0 | 0 | 0 | 0 | ||||||||
| Sales price | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | ||||||||
| Sales revenue | $0 | $0 | $0 | $0 | $0 | ||||||||
| Variable costs | 0 | 0 | 0 | 0 | 0 | ||||||||
| Fixed operating costs | 0 | 0 | 0 | 0 | 0 | ||||||||
| Depreciation (equipment) | 0 | 0 | 0 | 0 | 0 | ||||||||
| Oper. income before taxes (EBIT) | 0 | 0 | 0 | 0 | 0 | ||||||||
| Taxes on operating income | 0 | 0 | 0 | 0 | 0 | ||||||||
| Net Operating Profit After Taxes (NOPAT) | 0 | 0 | 0 | 0 | 0 | ||||||||
| Add back depreciation | 0 | 0 | 0 | 0 | 0 | ||||||||
| Operating cash flow | $0 | $0 | $0 | $0 | $0 | ||||||||
| Terminal Year Cash Flows: | |||||||||||||
| Return of net operating working capital | 0 | ||||||||||||
| After-tax salvage value | 0 | ||||||||||||
| Total termination cash flows | $0 | ||||||||||||
| Net Cash Flow (Time line of cash flows) | $0 | $0 | $0 | $0 | $0 | $0 | |||||||
| Part 5. Key Output: Appraisal of the Proposed Project | |||||||||||||
| Net Present Value | Create a formula using the NPV function as specified | ||||||||||||
| IRR | Create a formula using the IRR function as specified | ||||||||||||
| MIRR | Bonus: Create a formula using the MIRR function as specified | ||||||||||||
| Payback | Bonus: How would you calculate payback using Excel? | ||||||||||||
| Enter in any company information to explain project as required by instructions. How will this project help your company's bottom line? |
Doug Letsch:
Enter your initial cost of equipment here
Doug Letsch:
Hit the ? Or help key in the upper right corner of Excel to see how to use NPV function =NPV()
Doug Letsch:
Hit the ? Or help key in the upper right corner of Excel to see how to use IRR function =IRR()
Doug Letsch:
Hit the ? Or help key in the upper right corner of Excel to see how to use MIRR function =MIRR()
Explain Payback here: make calculations below
