Question

How do you set up a capital budget projection on a fictional company using Excel? Estimating output, output prices, revenues. When will expenses be paid? Chose a discount rate and calculate the PV of future cash flows.

Answer 1

Capital budgeting may involves purchasing raw material, assets and arranging required services or anything which has monitory cost associated. Companies usually take projects which have positive margin over the cost.

In NPV approach we discount the after-tax cash flows by the weighted average cost of capital and if derived value is greater then the incurred cost then project considered to be profitable.

To set up a budget for any project, first we should calculate the associated implementation, recurring fixed and variable cost.

Expenses will be considered as an when they are occurred and will be part of cost for the project to the company.

After calculating the cost we should calculate the total revenue and deduct incurred cost and taxes, it will give us the net cash flow and discounting it with the cost of capital will give us Net present value of Cash Flow.

Below is the excel formula to calculate PV:

=PV (rate, nper, pmt, fv)

• rate – Cost of capital.
• nper - The total number of payment periods.
• pmt - The payment made each period.
• fv – Total cash flow

If NPV is positive the project is profitable and company may consider it.

Below is the calculation for present value of the future cash flows on the excel sheet.

Year	0	1	2	3	4
Per Unit Cost		10	10	15	10
Number   of Unites		5000	6000	2000	3000
Price per unit		25	25	15	40
Incremental Revenue		125000	150000	30000	120000
Incremental Variable cost /Expenses		50000	60000	30000	30000
Incremental Fixed cost /Expenses		2000	2000	2000	2000
Implementation Cost (fixed one time)	3500	0	0	0	0
Total Cost	3500	52000	62000	32000	32000
Total Cash Flow	-3500	73000	88000	-2000	88000
Tax	0	400	500	0	500
Net Cash Flow after Tax	-3500	72600	87500	-2000	87500
Cost of Capital	8%	8%	8%	8%	8%
PV of Cash Flow	₹ -3,500.00	₹ 67,222.22	₹ 75,017.15	₹ -1,587.66	₹ 64,315.11
NPV	₹ 2,01,466.82

Here is the worksheet with showing formula in the cell.

Capital Budgeting
Year	0	1	2	3	4
Per Unit Cost		10	10	15	10
Number   of Unites		5000	6000	2000	3000
Price per unit		25	25	15	40
Incremental Revenue		=C5*C4	=D5*D4	=E5*E4	=F5*F4
Incremental Veriable cost /Expenses		=C3*C4	=D3*D4	=E3*E4	=F3*F4
Incremental Fixed cost /Expenses		2000	2000	2000	2000
Implimentation Cost (fixed one time)	3500	0	0	0	0
Total Cost	=B10+B11+B12	=C10+C11+C12	=D10+D11+D12	=E10+E11+E12	=F10+F11+F12
Total Cash Flow	=B8-B13	=C8-C13	=D8-D13	=E8-E13	=F8-F13
Tax	0	400	500	0	500
Net Cash Flow after Tax	=B16-B17	=C16-C17	=D16-D17	=E16-E17	=F16-F17
Cost of Capital	0.08	0.08	0.08	0.08	0.08
PV of Cash Flow	=-PV(B19,B2,0,B18)	=-PV(C19,C2,0,C18)	=-PV(D19,D2,0,D18)	=-PV(E19,E2,0,E18)	=-PV(F19,F2,0,F18)
NPV	=SUM(B21:F21)