Capital Budgeting Workbook

Capital budgeting workbook is the process of analyzing a company’s contribution decisions such as investing in new equipment, machinery, plants, projects, and products. This process involves the estimation of the expected cash flows, the calculation of the Net Present Value (NPV) and the calculation of the Internal Rate of Return (IRR) of the investment. Net Present Value is defined as the present value of all cash inflows minus the present value of all cash outflows. If Net Present Value is positive, the contribution is making money and is thus applicable. Internal Rate of Return is defined as the discount rate that makes the Net Present Value zero. If the Internal Rate of Return is greater than the opportunity cost of capital then the investment is feasible. The greater value of Internal Rate of Return, the more feasible an investment is.

Capital Budgeting Workbook

There are two complications in this analyzing process. One involves the correct estimation of the expected cash flow. The other use of a correct discount rate (also known as the Project Cost of Capital). In some cases, it is possible to simply use the Company’ Weighted Average Cost of Capital (WACC) as the Project Cost of Capital. This is especially the case if the project has the similar cost structure to the company. In other cases, a separate estimation or assumption of the Project Cost of Capital is required.

Capital Budgeting Workbook

The Capital Budgeting workbook objective is to assist investors, managers or analysts incorrectly estimating the cash flow in different scenarios and accurately calculating the Net Present Value and Internal Rate of Return. This workbook also allows different contribution projects cash flow to be compared and the forecasting of the base case, worst case, and best case scenario.

Calculate NPV and IRR

The capital budgeting-ProjectCashFlow-NPV worksheet in the Capital Budgeting spreadsheet allows you to key in the assumptions and estimates of a project cash flow and will calculate the Net Present Value and Internal Rate of Return on the investment.

This worksheet performs capital budgeting analysis by making three basic assumptions. The assumptions are the Discount Rate to use in the investment project, the company’s Tax Rate and the estimated percentage of Net Working Capital over Sales.

Estimated Income

The total income of the project is calculated by using the following formula:

Total income = Earnings before Interest & Taxes – Taxes

where

EBIT = Net Sales – Total Variable Costs – Total Fixed Costs – Depreciation

Estimated Cash Flow

This section is where the estimated cash flows are calculated. The Operating cash flow is defined as follows:

Operating cash flow = EBIT + Depreciation + Taxes

Net Working Capital

The Net Working Capital at Year 0 can be entered directly into the spreadsheet. From Year 1 onwards, it is calculated as a function of Net Sales as follows:

Net Working Capital = Net Working Capital over Sales * Net Sales

Net Working Capital cash flow is calculated as follows:

Net Working Capital cash flow = -(Current Year Net Working Capital – Previous Year Net Working Capital) + NWC Recovery at the end.

Net Present Value and Internal Rate of Return

Net Present Value calculates the use of Excel NPV function on the Total project cash flow. Internal Rate of Return calculates the use of Excel IRR function on the Total project cash flow.

Leave a Reply

Your email address will not be published. Required fields are marked *