This tutorial shows how to create a Capital Investment Plan in Excel.
First, I will set up a financial model in Excel, calculate revenue and cost, and forecast cash flow. Then, I will add Profit and Loss, Cash Flow and Balance Sheet Statements to the financial model. Finally, I will explain how to calculate Returns on Investment and stress test the Capital Investment Plan.
Download my Capital Investment Model Template to follow the examples used.
Step 1. Set up a Capital Investment Forecast in Excel
Use the first rows of the Excel Financial Model to show the phasing of the capital investment. Then, link the funding to the overall customer growth.
For example, my Capital Investment Plan Template spreads the financing into equal monthly tranches. It then assumes that each injection increases the overall addressable market.
Then, use a market reach assumption and growth period to forecast the number of customers.
For example, if the addressable market is 100k customers and the market reach target equals 35%, the financial model will eventually show 35k active customers (i.e. 35 % x 100k).
The number of active users will drive your capital investment plan’s revenues and cost calculations.
Learn More: Visit my tutorial on How to Build a Cash Flow Forecast in Excel for more examples of creating financial models.
Step 2. Convert Capital Investment Plan into a Revenue Forecast
The number of users will become the primary driver of your financial model’s customer revenue and cost calculations.
To calculate the revenues, link the drivers to the pricing assumptions, such as the monthly subscription from each customer.
For example, with 100 connected users and a monthly charge of $25, your estimated monthly revenue will equal $2,500 (i.e. 100 x $25).
Remember to add an inflation assumption to your financial model to account for future price increases.
Learn More: Visit my tutorial on How to Create a Subscription Model with Churn Calculation for more examples of customer revenue calculations.
Step 3. Add Variable and Fixed Costs to a Financial Model
Separate the variable and fixed costs in your plan. The variable costs are more dynamic and may depend on customer numbers, revenues, or investment size. Fixed costs will be the more stable part of the investment plan.
For example, my Capital Investment Plan Template calculates marketing expenses by taking the number of new customers and multiplying it by an average acquisition cost. Therefore, the more customers a business adds, the higher the marketing expenses.
You can also link the cost forecast to other drivers. For example, my template calculates the maintenance costs as a % of the total capital invested.
Combining fixed and variable cost calculations gives a financial model more flexibility and a better ability to forecast business at a scale.
Learn More: Visit my tutorial on How to Create a Marketing Investment Plan in Excel for more examples of calculating variable costs.
Step 4. Include Headcount Forecast and Resource Plan
Group the employee forecast into functions and link the resulting headcount to average salary assumptions. Split the resource forecast into fixed and variable employees.
An example of fixed resources would be the management team, which is not greatly sensitive to the company’s growth and is predictable over time.
List the role names with associated headcount beneath them and then multiply them by the relevant salary assumptions.
For variable resource planning, assume they depend on the number of customers or any other driver in the financial model.
For example, my Capital Investment Plan Template forecast links the maintenance roles to the capital expenditure and operational functions to the customer numbers. Then, it multiplies them by the average salary assumptions to forecast the variable staff cost.
Remember to include overheads and other employee expenditures to estimate the total staff cost. Finally, add a pay cost index to account for future inflationary increases.
Learn More: Visit my tutorial on How to Create a Real Estate Investment Model in Excel for more examples of variable resource planning.
Step 5. Depreciate the Capital Investment in a Profit and Loss Statement
Add a Profit and Loss Statement to show the impact of your financial model’s calculations on the net income.
Link the revenue calculations directly to the Profit and Loss Statements. Also, include the variable and fixed operational and resourcing costs, interest expenses and tax calculations.
My Capital Investment Plan Template calculates the depreciation in a separate section of the financial model. It sums the capital expenditure over the preceding period and divides it by the useful life input.
Note that my template uses Excel’s SUMPRODUCT formula to adjust the depreciation to the useful life assumption. You can read more about combining SUMPRODUCT with conditional statements here [external link].
Learn More: Visit my tutorial on How to Create a Telecom Investment Model in Excel for more examples of Profit and Loss forecast and depreciation calculations.
Step 6. Show Capital Investment in a Cash Flow Statement
Group the output of your financial model calculations into a cash flow statement summary, clearly showing Cash Flow from Operations, Investment, and Financing.
To calculate the Cash Flow from Operations:
- Start with the Net Income from the Profit and Loss Statement.
- Add non-cash items, such as depreciation, from your financial model’s calculations.
- In addition, add payable and receivable items.
To summarise the Cash Flow from Investment, link the capital expenditure calculations from your financial model.
If you sell any assets, show the proceeds here. However, note that you will need to reflect any profit or loss resulting from the sale transaction in the cash flow from operations [ read more – external link ].
Finally, link the debt and equity injections and repayments to the Cash Flow from Financing.
The invested equity should reflect any amount required to fill any deficit resulting from Cash flow Operations and Debt Financing.
My template includes the interest expenses in the net income calculations. Hence, the cost is part of the Cash Flow from Operations.
A completed Cash Flow Statement gives you a good overview of how your financial model forecasts capital inflow and outflow over time.
Learn More: Visit my tutorial on How to Create a Real Estate Investment Model in Excel for more examples of Cash Flow Statement calculations.
Step 7. Add Capital Investment to a Balance Sheet
Link the items from the Cash Flow and Profit & Loss statements, so your financial model will automatically reflect any changes in the Balance Sheet.
My Capital Investment Plan Template links the cumulative capital expenditure from the cash flow calculation to the Long-Term Assets section of the Balance Sheet. Then, it offsets it with cumulative depreciation from the Profit and Loss Statement to finally calculate the assets’ book value.
For example, if the cumulative capital expenditure by Year 2 equals $2.0m, and the cumulative depreciation is $0.2m, the balance sheet will show the Long-Term Asset value as $1.8m (i.e. $2.0m expenditure less $0.2m depreciation).
Similarly, include the Cash Flow from Financing in the liability section of the Balance Sheet. First, show the cumulative amount of debt drawn. Then, offset it by the cumulative value of the principal repaid.
For example, if the total debt drawn by Year 2 equals $1.5m and the principal repayment equals $0.3m, the balance sheet will show the Long-Term Debt Liability of $1.2m (i.e. $1.5m debt drawn minus $0.3m repayment).
Note that my template includes equity injections as part of the Owner’s Equity.
Learn More: Visit my tutorial on How to Create an Excel Financial Model with Debt Financing for more examples of debt calculations.
Step 8. Create Capital Investment Plan’s Annual Summary
Aggregate the monthly cash flow forecast into an annual view. Then, list the main inputs next to the summary to see how a change in the assumptions affects the financial model.
Finally, link the output to graphs, which you can copy and paste into your presentations.
For example, my Capital Investment Plan Template visualises the main financial trends and shows their impact on the overall cash flow forecast.
You will use the summary output to calculate the Return on Investment and stress test the financial model.
Learn More: Visit my tutorial on How to Create a Project Finance Model in Excel for other examples of cash flow forecast models.
Step 9. Calculate the Return on Investment
To calculate the Return on Investment, you will need an estimate of equity injections and the exit or the terminal value.
For example, my Capital Investment Plan Template estimates the equity from the financial model’s cash flow forecast at the beginning of the year. If a deficit occurs, it injects enough investment to keep the cash balance positive.
Also, my template calculates the terminal value as a function of the EBITDA and a multiplier.
For example, if an EBITDA in the exit year equals $1m and the valuation multiplier is 10, the terminal value will be $10m (i.e. 10 x $1m EBITDA).
Finally, use Excel’s IRR and NPV formulas to calculate the Return on Investment.
Each industry’s multiplier will differ, and you can get it by looking at historical market transactions (such as acquisitions or IPOs) in the recent period.
Learn more: Visit my tutorial on How to Build a Cash Flow Forecast in Excel or How to Create a Real Estate Investment Model in Excel for examples of other methods of valuation, such as DCF.
Step 10. How to Stress Test a Financial Model in Excel
Estimate the financial model’s sensitivity by testing how a change in one input impacts the main metrics and output of the plan.
For example, you can check how an increase in capital expenditure or marketing expenses affects the cash flow forecast and the return on investment.
My Capital Investment Plan Template uses Excel’s data table to test scenarios and their impact on the financial model’s impact. Link the variables from the table’s columns and rows to the relevant assumptions, and let Excel do the rest.
Stress testing the model allows you to discover the breaking points of your investment plan and prepare for unexpected scenarios.
Learn More: Visit my tutorial on How to Create a Marketing Investment Plan in Excel or How to Create a Telecom Investment Model in Excel for other examples of stress-testing a financial model.
Summary: How to Create a Capital Investment Plan
To create a capital investment plan, start with a financial model showing the relationship between cash injections and the addressable market. Then, show how you will grow the user base to achieve a market reach over time.
Link the number of users or sales with the pricing assumptions to calculate the forecast revenue. Split the plan into variable and fixed costs to show the parts of the financial model dependent on customers’ growth.
Then, using the capital investment, revenue and costs forecast, create a Cash Flow, Profit and Loss and Balance Sheet.
Finally, estimate required equity additions and terminal value to calculate Return on Investment. Use metrics such as IRR or NPV to stress test the financial model and analyse the impact of selected assumptions on the Capital Investment Plan.
Download Capital Investment Plan Template
Click the button below to download my Capital Investment Plan Template in Excel.
The spreadsheet combines capital investment with addressable market assumptions to forecast revenue, variable and fixed cost, and overall cash flow. It also includes calculations of Return on Investment and sensitivity analysis.
The template contains the following tabs and sections:
- Financial Model: monthly cash flow forecast of the addressable market, customer reach, revenues and variable and fixed costs. Includes separate calculations of headcount and resource costs.
- Summary: an annual view of the capital investment plan with the main assumptions and financial metrics. Includes visualisation of yearly revenues, costs, EBITDA and cumulative cash flow.
- Profit and Loss: an annual statement which links the financial model’s revenue and cost estimates to the net income forecast. Includes calculations of capital investment’s depreciation.
- Cash Flow Statement: an annual view of Cash Flows from Operations, Investment, and Financing.
- Balance Sheet: a yearly summary of assets and liabilities, which includes debt and equity calculations.
- Return on Investment: forecast of equity requirement and terminal value with calculations of IRR and NPV.
- Sensitivity Analysis: data tables testing the impact of selected assumptions on the capital investment plan.
The file is in Excel format, but you can convert it to Google Sheets using the instructions here [external link].
Get in Touch
Hi, my name is Jacek, and I love Excel. I hope you’ve enjoyed reading this tutorial as much as I enjoyed writing it! If you have any questions about Capital Investment Plans and Financial Models in particular or financial analysis in general, don’t hesitate to get in touch.
Click here if you would like to book my One-to-One Financial Modelling Crash Course. For more Financial Modelling Tutorials, visit this page. If you are looking for help with financial modelling or cash flow forecasting, see my Financial Modelling and Forecasting Consulting services here.
Cash Flow Forecast in Excel – another example of creating a forecast for market reach, customer revenues and costs. Includes other valuation methods such as Discounted Cash Flow (DCF).
Financial Model with Debt Financing – this post explains how to offset a capital investment requirement with loan financing. It also explores how to use the present value of future revenues to calculate debt injections.
Merger and Acquisition Model in Excel – this tutorial is an example of using debt as a source of capital for acquiring a company. It also shows how a merger or acquisition impacts a consolidated balance sheet.
Project Finance Model in Excel – tutorial on using debt and equity in cash flow forecast. It also explains how to consolidate multiple projects into one view.
Real Estate Investment Model in Excel – this post shows how to create a cash flow forecast for the rental and sales of properties. It also includes examples of valuation related to the real estate sector.
Telecom Investment Model in Excel – learn more about combing an infrastructure project with a customer revenue forecast. The tutorial also includes examples of the financial model’s stress testing and valuation.
Subscription Model with Churn Calculation – this tutorial will introduce you to combining customer revenue and growth forecast with churn. It also includes a Customer’s Lifetime Value Calculator.
Marketing Investment Plan in Excel – another example of variable cost forecast. The tutorial uses marketing data to predict future expenditures and customer growth.
Your First Steps in Excel – Beginner’s Crash Tutorial – a quick refresher on the basics of Excel.