Capital Budgeting — Microsoft Excel

Section F

18 min readMay 4, 2024

In this article, we will walk you through the core concept of learning capital budgeting in finance and along with that we also understanding how capital budgeting is important in corporate environments and why we choose these budgeting method. Capital budgeting is the need of hour for buying assets and investing in projects by creating cases of best, base and worst case. We will be able to Analysis with the help of IRR and NPV about Capital Budgeting. We will understand what discounted

cash flow and free cash flow are. We will have knowledge of leverage and its types and will be able to analyze whether to acquire a company with leverage buyout

modeling.

Capital Budgeting

Capital budgeting is a term relating to the decision making process companies go through when determining whether to invest in a long term initiative or in assets. In addition, capital budgeting is used as a tool for company executives to compare

alternatives when making strategic decisions.

The investment in a new production plant.
The acquisition of an old company.
Expanding the existing warehouse.
Purchase or lease of a new vehicle.

Decisions that have a significant impact on a company’s future for an extended period, involving substantial investments and long term importance.. The topic

of discussion in the first team meeting will be the amount of capital to invest, the project timeline and the financial returns.

Let’s dive straight into preparing a capital budget.

We need some drivers (Input) to start preparation.

The sheet is named Drivers.

S. No. 1 is the Selected Case, we can select the Best, Base and Worst Cases from Cell

C2.
S. No. 2,3 and 4 are Installed Capacity in MW for the Best, Base and Worst Case,

respectively.
S. No. 5 is the capacity factor, S. No. 6 is the Days in one year considered, and S. No.

7 is the hours per day count of work.
S. No. 8 is the inflation rate. S. No. 9, 10 and 11 are Price per MWh for the Best, Base

and Worst Case, respectively.
S. No. 12,13 and 14 are OpEx as a % of Revenue for all three cases.
S. No 18 is the useful life of Asset, and S. No. 19 is Capex after year 1.
S. No. 20, 21 and 22 are Loan Amount, Rate and period of financing, while S. No. 23

is the Repayment Schedule.
S. No. 20, 21 and 22 are Loan Amount, Rate and period of financing, while S. No. 23

is the Repayment Schedule.
S. No. 24 is the tax rate in India.
S. No. 25 is the residual value of the project/asset.
S. No. 26, 27 and 28 are the peer companies’ Debt Equity Ratio and Beta.
S. No. 29 is the company’s own beta.
S. No. 30 and 31 are market risk premium and risk free rate for CAPM Calculation.
First, we will calculate revenue

Add a new sheet, and rename it as revenue and format it as shown in the figure below, add the input of this sheet from Drivers Worksheet.

As we can see in the above figure, we have the format of revenue calculations.
In the above figure the S. No. 32, 33, 34, 35, 36, 37,38 and 43 are given in Drivers Sheet, we take these figures from there itself.

As we can see in the above figure, shows the Excel formula used in Revenue Calculation Format.

S. No. 32 is for the selection of Best, Base and Worst, which is equal to Drivers S. No.1. A selection drop down has been established on the Drivers Sheet, limiting case selection solely to that sheet.
S. No. 32 to 35 are equal to S. No. 2 to 4, respectively, for all three scenarios.
S. No. 36 to 38 and 43 are equal to S. No. 5 to 7 and 8 for various metrics given in the Drivers Sheet.
We will calculate S. No. 39 to 41 for Energy production MWh, S. No. 44 to 46 for Price per MWh, and S. No. 48 to 50 for Revenue.
We will calculate S. No. 39 to 41 for Energy production MWh, S. No. 44 to 46 for Price per MWh, and S. No. 48 to 50 for Revenue.
While S. No. 42, 47 and 51 will be selected case based on Best, Base and Worst Case.
Let’s understand the VLOOKUP formula used for the selected cases cells in S. No.

42, 47 and 51. =VLOOKUP($D$2,$C14:$M16,COLUMN()-2,0)
The above mentioned is the formula of cell D17. We know that the $D$2 is the lookupvalue, $C14:$M16 is the table array and COLUMN()-2 defines the column number.

The result of COLUMN()-2 in D Column is 2 because:

COLUMN() in D Column = 4;
COLUMN()-2 in D Column = 2
COLUMN() in A Column = 1;
COLUMN()-2 in A Column = -1
COLUMN() in B Column = 2;
COLUMN()-2 in B Column = 0
COLUMN() in M Column = 12;
COLUMN()-2 in M Column = 10

So we can consider the formula in D column as follows:

=VLOOKUP($D$2,$C14:$M16,2,0)
And for M column as follows,
=VLOOKUP($D$2,$C14:$M16,2,0), And for M column.

In the above figure, shows the Energy Production MWh, Price per MWh and Revenue calculated.

Energy Production MWh = Installed Capacity x Capacity Factor x Days in a year x Hours

per Day
So Base Case = Base Case Installed Capacity (220) x 35% x 365 x 24 = 6,74,520
and worst case and best case calculation for others as well.
Price per MWh for the first year as mentioned in Drivers Sheet that is 360 and 340

and 320 as given in Drivers Sheet S. No. 9 and 10 and 11, respectively.

From the second year onward, Price per MWh = ROUND (Rate of Previous Year x (Inflation Rate + 1)). So, the second year calculations for base case = ROUND (Rate of Previous Year (340) x (Inflation Rate 1.10% +1)) = 344, Revenue = Energy Production MWh x Price per MWh, Revenue for base case in year 1 = 674520 x 340 = 229336800/-

Formula used in Excel :

As we can see in the above figure, we have the Excel Formula used for getting values in the above figure in cells for Energy, Production MWh, Price per MWh and Revenue.

The next step is to calculate Opex, add a new sheet, rename it as Opex and format it as shown in the figure below.

As we can see in the above figure, we have got the Opex % from Drivers Sheet as given in Drivers Sheet. S. No 53, 54 and 55 are equal to S. No. 12, 13 and 14, respectively.
S. No. 52 is for the selection of the Best, Base and Worst cases, which is equal to

Drivers S. No. 1. A selection drop down has been established on the Drivers Sheet, limiting case selection solely to that sheet.

As we can see in the figure below, we have completed OpEx calculation.
OpEx = Revenue (From Revenue Sheet) x OpEx %

For First Year Best Case, OpEx = 229336800 x (20%) = (4,58,67,360)

The Excel formula used for Opex calculation is given in the figure below.

As we can see in the above figure, shows the Excel Formula for OpEx calculations.
S. No. 56 to 58 are the Best, Base and Worst Case, respectively.
S. No. 59 is the selected case as per selection.

Now, we move further to Fixed Asset Roll Forward.

Add a new sheet, rename it as FA, and format it as shown in the figure below.

S. No. 60 is for the selection of Best, Base and Worst, which is equal to Drivers S. No.1. A selection drop down has been established on the Drivers Sheet, limiting case selection solely to that sheet.
S. No. 61, 62 and 63 are for the Best, Base and Worst Cases from Estimated Initial

Investment, as given in S. No. 15, 16 and 17.
S. No. 64 is useful life, which is given in S. No. 18.
S. No. 65 is Capex %, which is equal to S. No. 19 from the Drivers Sheet. Initial

Investment on Year 0 will be 100% and Year 1 to 10 are same as S. No. 19.
S. No. 66, 67, 68 and 69 are the calculation of Opening FA, Capex, D&A and Closing

FA.
S. No. 70 to 79 are the calculation of D&A(Depreciation) every year, and S. No. 80 is Total D&A.
Now, let’s fill the S. No. 66 to S. No. 80.
First, we must fill Closing FA of Year 0 in cell no. D18, S. No. 69.
=VLOOKUP(D2,C5:D7,2,0) is the Excel formula where D2 is the case scenario of

the Best, Base and Worst Case; and C5:C7 is the table for initial payment of Case Scenario., 2 column no.

If we select the best case, we will get 34000000.

As we can see in the above figure, we get the value of INR 36,00,000/- for Closing FA, which is the base case.

S. No. 66 is the Opening FA, which is equal to the Closing FA of Previous year.
Cell E15 = D18
S. No. 67 is Capex of Initial investment.
In our example, 3% of Initial Investment of INR 36,00,00,000/- = 1,08,00,000/-.
S. No. 68 is the D&A, which is equal to S. No. 80.
S. No. 69 is the Closing FA = Opening FA(S. No. 66) + Capex(S. No. 67) – D&A (S.

No. 68).

As we can see in the above figure, we have the calculation of FA.

S. No. 70 to 79 are D&A calculations year-wise, and S. No. 80 is the total of S. No. 70

to 79.
D&A Year 1 (S. No. 70) is 10% Depreciation of Initial Investment Opening FA for 10

Years, which is 3,60,00,000/- (Cell No. E21 to N21) every year in the base case for the

first year.
D&A Year 2 (S. No. 71) is 3,60,00,000/- for the first year (S. No. 70) plus D&A of

additional capex as per S. No. 67. The depreciation will start from the second year

for the first year capex.
Capex = 1,08,00,000/- in Year 1 ( Cell No. E16)
Now, we will calculate this for the next 9 years, starting from Year 2 (Cell No. F22 to

N22), which will be 10,80,000/- every year for the next 9 years.
Year 2 has addition 1,08,00,000/- capex added, and depreciation will start from year 3.
For the following eight years, the D&A Year 3 (S No. 72) amounting to 10,80,000/-

will remain constant and be recorded in cells G23 to N23.
The same method is applied for D&A Year 4 to D&A Year 10 (S. No. 73 to S. No. 79).
All the preceding calculations are done with the Base Case selection in the Drivers

Sheet.
We considered the depreciation rate @ 10%.

As we can see in the above figure, we use the formula of Excel to complete the Fixed Assets Roller Forward. The next step is Financing.

Add a new sheet, rename it as Financing, format it as shown in the figure below and fill the data given in Driver Sheet.

As we can see in the above figure, we have entered S. No. 81 to 86, while S. No. 87 to 93 are blank, we will calculate them now.

S. No. 81 is for the selection of Best, Base and Worst cases, which is equal to Drivers.
S. No. 1. A selection drop-down has been established on the Drivers Sheet, limiting case selection solely to that sheet.
S. No. 82 is Investment in year 0, which is equal to the initial investment we calculated

in FA Year 0 S. No. 69 Cell No. FA!D18
S. No. 83 to 85 are Principal, Interest Rate and Period (in years), which are equal to

S. No. 20,21 and 22 of Drivers Sheet.
S. No. 86 is the repayment schedule, which is equal to S. No. 23 of Drivers Sheet.
Now, we fill the blank cells of S. No. 87 to 91, as shown in the figure below.

As we can see in the above figure, we have Closing Debts and Interest Expenses every year.

S. No. 87 is the Opening Debt, which is equal to the Principal as per S. No. 83, next year onward, the previous year’s closing debt will be equal to the current year’s opening debt.
S. No. 89 is equal to S. No. 86 repayment %.
S. No. 88 is equal to repayment, which is Initial Principal (S. No. 83) x Repayment %

(S. No. 89).
S. No. 90 is Closing Debts = Opening Debts – Repayment ( S. No. 87-88).
In Year 0, we took the loan; year 1 repayment is 0%, so Opening and Closing are the

same in both years. Repayment of 5% in year 2 comes up to 1,20,00,000/-, so the closing debt is equal to (24,00,00,000-1,20,00,000) = 22,80,00,000/- and so on.
S. No. 91 is Interest Expenses, which start from Year 1; it is equal to 8% of 24,00,00,000/-

(Opening Debts of Year 1) = 1,92,00,000/-.
S. No. 92 will be calculated later.
S. No. 93 is Equity For Year 0 decided by the amount of funds are required for

initial investment and how much debt we can take. For example, in Base Case, we

need ₹36,00,00,000/-, and we are taking debt of ₹24,00,00,000/-, so we will need ₹12,00,00,000/- for equity, it will be the same for the next 10 years.

In the above figure, shows the Excel Formula used in Financing.

Now, we will calculate the P&L statement (Income Statement).

Add a new sheet, rename it as P&L and format it as shown in the figure below.

The above figure, shows the format of P&L statement. S. No. 94 is for the selection of the Best, Base and Worst cases, which is equal to

Drivers S. No. 1. Selection Drop Down is created on Drivers Sheet only can select the case form drivers sheet only.
Now, we will fill the blank cells of the P&L statement.

As we can see in the above figure, Tax Rate in S. No. 95 is taken from Drivers Sheet, which is equal to S. No. 24. S. No. 96 is Revenue, which is taken from Revenue Sheet S. No. 51.
S. No. 97 is Opex from Opex Sheet S. No. 59.
S. No. 98 is D&A from FA Sheet S. No. 68.

S. No. 99 is EBIT, which is equal to Revenue – Opex -D&A (96-97-98).

S. No. 100 is Interest from Financing Sheet S. No. 91.

S. No. 101 is EBT, which is equal to EBIT – Interest (99-100).
S. No. 102 is taxes, which is equal to EBT * Tax Rate% (101*95).
S. No. 103 is Net Income, which is equal to EBT – Taxes (102-101).

The Excel formula used in the P&L statement is shown in the below figure.

As we can see in the above figure, we used the Excel formula for P&L.
The next step is to create cash flow.
Add a new sheet, rename it as CF, create the format shown in the figure below and insert a value we have.

In the above figure, shows the Format of Cash Flow.
S. No. 103 is for the selection of Best, Base and Worst, which is equal to Drivers S. No. 1. Selection Drop Down is created on Drivers Sheet only can select the case form

drivers sheet only.
S. No. 104 is the residual value, which is equal to S. No. 25.
Now, we fill the blank cells of Cash Flow.

As shown in the above figure, we have prepared Cash Flow.
S. No. 105 – Revenue, S. No. 106 – Opex, S. No. 107 – Taxes & S. No. 111 – Interest Expenses are taken from P&L, which are equal to S. No. 96, S. No. 97, S. No. 102 and S. No. 100, respectively.
S. No. 108 is Capex, which is on Year 0 (Initial Investment) is equal to Closing FA S.No. 69 (Cell No. FA!C18) for Year 0 only. From Year 1, this is equal to S. No. 67 of FA. S. No. 109 is the Operating Cash Flow, which is equal to Revenue – Opex – Taxes –

Capex (105 -106-107-108).

S. No. 110 is debt repayment, which is taken from Financing S. No. 88.
S. No. 112 is increase of equity, which is equal to Equity Current Year – Equity

Previous Year.
For year 1 (Equity in Year 1 – Equity in Year 0). For Year 2 (Equity in Year 2 – Equity

in Year 1), S. No. 93 is the Equity every year.
S. No. 113 Net Cash Flow, which is equal to Operating Cash Flow – Debt Repayment

– Interest Expenses – Increase of Equity ( 109-110-111-112).
In S. No. 113 end value in Residual Value, which is given in the Drivers Sheet, which is equal to S. No. 104 of CF.

The Excel formulas used in Cash Flow are shown in the figure below.

In the above figure, shows the Excel formulas used in Cash Flow Statement.

S. No. 92 is Net Cash Flow, which is equal to S. No. 113 of CF, as shown in the figure below.

As we can see in the above figure, we have added Net Cash Flow.

As we can see in the above figure, we have completed the Financing Sheet.
The next step is to calculate the WACC.

Insert a new sheet, rename it as WACC, format it and insert the values given in the figure below.

As we can see in the above figure, we are going to calculate WACC.

S. No. 114 is for the selection of Best, Base and Worst, which is equal to Drivers S. No.1. A selection drop-down has been established on the Drivers Sheet, limiting case selection solely to that sheet. S. No. 115 – Cost of Debt, S. No. 116 – Risk Free Rate, S. No. 117 – Tax Rate, S. No.

118 – Market risk premium, which are given in Drivers Sheet and are equal to S. No. 21 – Interest Rate, S. No. 31, S. No. 24, S. No. 30, respectively.

S. No. 119 to 121 are Leverage and Beta of Company A, B & C, which are equal to S.
No. 26 to 28, respectively.

Let’s fill the blank cell of WACC:

As we can see in the above figure, we find the WACC for our Financing.
S. No. 122 is average leverage and beta of Company A, B & C.S. No. 123 is beta unlevered.

Formula to Calculate Beta unlevered:

Where,

Average Beta = Average of all peer companies’ beta, Tax Rate = Rate of Tax,
Average Leverage = Average of all peer companies leverages.

In our example:

Average Leverage = 0.042/(1+(1-0.3)*0.072 = 0.28.

Excel Formula =E13/((1+(1-D6)*(D13))).

S. No. 124 is debt financing, which is equal to S. No. 87.

S. No. 125 is equity financing, which is equal to S. No. 93.

S No. 126 is total equity, which is equal to the total of Debt and Equity (S . No. 124 +

S. No. 125).
S. No. 127 is debt financing percentage over total financing (S. No. 124/S. No. 126).

S. No. 128 is equity financing percentage over total financing (S. No. 125/S. No. 126).
S. No. 129 is the total of debt and equity percentages (S. No. 127 +128).

S. No. 130 is the ratio of debt over equity (Leverage – S. No. 124/S. No. 125).
S. No. 131 is project beta.

Formula for Project Beta:

In our example, for Year 0 and Year 1:

Project Beta = 0.28*(1+(1-0.3)*2.0 = 0.94

Excel Formula = ROUND($E$14*(1+(1-$D$6))*D25,2)

Cost of Equity = CAPM = Risk Free Rate + Beta x Market risk premium.

In our example for Year 0 and Year 1:

Cost of Equity = 0.06 + 0.94*0.065 = 12.1%

Excel Formula = ROUND($D$5+$D$7*D26,3)

S. No. 133 is WACC.

WACC = Debt Financing % x (1-tax rate) x Cost of Debt + Equity Financing % * Cost

of Equity

In our example, for Year 0 and Year 1:

WACC = 0.667*(1-0.3)*0.08 + 0.333*0.121 = 7.8%.

Excel Formulas used in WACC are shown in the figure below.

As we can see in Figure 8.26, we have Excel Formula for calculating WACC.
The next and final step is Discounted Cash Flow.

Create a new sheet, rename it as DCF and format it as shown in the figure below.

As we can see in the above figure, We have created have format of DCF. S. No. 134 is for the selection of Best, Base and Worst, which is equal to Drivers S. No.1. A selection drop.down has been established on the Drivers Sheet, limiting case selection solely to that sheet.

Now we will fill the blank cells, as shown in the figure below.

As we can see in the above figure, we have DCF.
S. No. 135 is the residual value of project, which is equal to the difference between

the residual value of FA and Closing Debt of Year 10, residual value can be taken from Net Cash Flow S. No. 104, and Closing Debt can be taken from Financing Sheet. Closing Debt is S. No. 90 and Year No. 10 (Cell No. Financing!N19).
S. No. 136 is Net Cash Flow, which is equal to S. No. 113.
S. No. 137 is Residual Value, which we have calculated in S. No. 135 of DCF.
S. No. 138 is the Net Cash Flow with Residual Value (S. No. 136 – S. No. 137).
S. No. 139 is Cost of Equity, which is to be taken from WACC S. No. 132.
S. No. 140 is Discounted Cash Flow.

Discounted Cash Flow Formula:

Where, DCF = Discounted Cash Flow, NCF = Net Cash Flow, Cost of Equity = Cost of Equity, n = Year No. for that year where it is to be calculated, Let’s take the examples of Year 1 and Year 3.

For Year 1:

DCF = 117469440/(1+0.0121)1 = 104789866

For Year 3:

DCF = 96586368/(1+0.119)3 = 68932745

S. No. 140 = S. No. 136/(1 + S. No. 139)n

S. No. 141 is present value of residual value, the formula is same as DCF as mentioned below and Year No. will be 10.

PV of RV = 34000000/(1 + 0.076)10 = 16343919

S. No. 142 is the difference between the Discounted Cash Flow and the Present Value

of Residual Value. ( S. No. 140 – S. No. 141). The Excel Formulas used in DCF are shown in the figure below.

As we can see in the above figure, we have Excel Formula for DCF.

Now that our Modelling Part is completed, the next step is analyzing.
We will check the NPV and IRR of DCF.

As we can see in the above figure, NPV is 33,00,68,640/- and IRR = 16%.
In both parameters, Project is Positive, so we can work on the Base Case Basis.
Let’s check with the Best Case and then the Base Case.
We will select Best Case in the Drivers Sheet Drop Down list of Scenarios and see the

difference.

As we can see in the above figure, NPV and IRR are in positive and higher than in the Base

Case. Now, we will move to the Worst Case.

As we can see in the above figure, NPV and IRR are still positive, so it means we can go ahead with this project, even in the worst case scenario.

Conclusion

Finally, we will take a deep drive of learning capital budgeting in finance and along with that we also understanding how and why we adopt capital budgeting in corporate environments for re-managing and observing after normal budget report and once it observed by the finance professionals, it will approved for other financial projects of a company.