How to Create a Capital Investment Plan

This tutorial shows how to create a Capital Investment Plan in Microsoft Excel.

First, it discusses setting up a financial model, calculating revenue and cost, and forecasting cash flow. Then, it covers adding Profit and Loss, Cash Flow and Balance Sheet Statements. Finally, it explains how to calculate Returns on Investment and stress test the Capital Investment Plan.

Download my Capital Investment Model Template to follow the examples used.

You can also watch the video version of the tutorial at the end of this post.

Step 1. Set up a Capital Investment Forecast

Open a new spreadsheet and use the first rows to show the capital investment’s phasing. 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.

Also, 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).

As a result, 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 Microsoft 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.

Firstly, 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).

Also, remember to add an inflation assumption to your financial model 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. On the other hand, 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.

As a result, 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 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. In addition, 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.

Finally, list the role names with associated headcount beneath them and 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.

Also, 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 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.

Hence, 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. In short, it sums the capital expenditure over the preceding period and divides it by the useful life input.

Also, the spreadsheet uses Microsoft 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 for more Profit and Loss forecast examples 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:

1. Start with the Net Income,
2. Add non-cash items, such as depreciation,
3. In addition, add payable and receivable items.

Then, to summarise the Cash Flow from Investment, link the capital expenditure calculations from your financial model.

If a business sells any assets, show the proceeds here. However, note that you 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.

Consequently, the invested equity should reflect any amount required to fill any Cash flow Operations and Debt Financing deficit. 

My template includes the interest expenses in the net income calculations. Hence, the cost is part of the Cash Flow from Operations.

Learn More: Visit my tutorial on How to Create a Real Estate Investment Model for more Cash Flow Statement calculation examples. 

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, suppose the cumulative capital expenditure by Year 2 equals $2.0m, and the cumulative depreciation is $0.2m. Consequently, 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).

Finally, include equity injections as part of the Owner’s Equity.

Learn More: Visit my tutorial on How to Create a 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 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, the spreadsheet 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, calculate the Return on Investment using Microsoft Excel’s IRR and NPV formulas.

Each industry’s multiplier will differ, and you can get it by looking at historical market transactions (such as acquisitions or IPOs) in recent periods.

Learn more: Visit my tutorial on How to Build a Cash Flow Forecast in Microsoft Excel or How to Create a Real Estate Investment Model for examples of other valuation methods, such as DCF.

Step 10. How to Stress Test a Financial Model in Microsoft 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 data tables to test scenarios and their impact on the financial model’s impact. It links the variables from the table’s columns and rows to the relevant assumptions and lets Microsoft 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 or How to Create a Telecom Investment Model 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. Also, 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. In addition, 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.

The Excel spreadsheet combines capital investment with addressable market assumptions to forecast revenue, variable and fixed costs, 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. Additionally, it includes separate calculations of headcount and resource costs.
• Summary: an annual view of the capital investment plan with the main assumptions and financial metrics. It also 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. Also, it 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].

Video: How to Use the Capital Investment Plan Template

For more details and a step-by-step explanation of using and creating the Capital Investment Plan Template in Microsoft Excel, watch my video tutorial below:

 

Get in Touch

Hi, my name is Jacek, and I love spreadsheets! I hope you’ve enjoyed reading this tutorial as much as I did writing it. If you have any questions about creating a Capital Investment Plan in particular or financial analysis in general, don’t hesitate to get in touch.

Explore my other tutorials to learn more about financial modelling or data analysis. If you need further support, find out about my One-to-One Training and Financial Modelling Services.

Please note that this tutorial is provided for general informational and educational purposes only and is not a substitute for professional advice.

Learn More

How to Build a Cash Flow Forecast in Microsoft Excel – another example of creating a forecast for market reach, customer revenues and costs, including discounted cash flow (DCF) valuation.

Financial Model with Debt Financing – explore how to offset a capital investment requirement with loan financing, using the present value of future revenues to calculate debt.

Merger and Acquisition Model – learn how to use debt as a source of capital for acquiring a company and the impact of the consolidation on the balance sheet.

How to Create a Project Finance Model – discover how to consolidate multiple projects into one cash flow forecast using revenues from one entity to fund others.

Monthly Budget Forecast – learn how to convert actuals and a short-term plan into a long-term forecast.

Real Estate Investment Model – an example of a cash flow forecast for the rental and sales of properties, including valuation for the real estate sector.

Telecom Investment Model – learn more about combining an infrastructure project with a customer revenue forecast and examples of the financial model’s stress testing and valuation.

Subscription Model with Churn Calculation – combine customer revenue and growth forecast with churn and calculate a customer’s lifetime value (CLV).

Marketing Investment Plan – learn how to use marketing data to predict future expenditures and customer growth.

Your First Steps in Microsoft Excel – Beginner’s Crash Tutorial – refresh the basics of using spreadsheets with this overview of the main functionalities and formulas.