# 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.

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.

Step 1a. An example of a Capital Investment and Addressable Market Forecast. The addressable market grows in line with the deployment of the initial capex. You can download the complete Excel template here.

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

Step 1b. An example of a Market Reach and Customer Growth forecast. The template multiplies the addressable market by a market reach assumption to calculate the customer number. You can download the entire spreadsheet here.

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

Step 2. An example of calculating Customer Revenue in a Financial Model. The revenue is linked directly with the customer number and average fee assumptions. You can download the complete Excel template here.

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.

Step 3. An example of calculating Variable Opex in a Capital Investment Plan. The spreadsheet links the customer support and acquisition costs with the customer numbers. On the other hand, the maintenance is correlated with the capex spend and the payment processing cost with the revenues. You can download the entire Excel file here.

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.

Step 4a. An example of a Fixed Resource Plan in Microsoft Excel. The template multiplies the headcount projections with the average monthly cost to calculate the total staff expenditure. You can download the entire spreadsheet here.

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.

Step 4b. An example of a Variable Staff Forecast in Microsoft Excel. The spreadsheet links the customer service staff with the customer numbers. Then, it applies average monthly assumptions to calculate the resulting cost. On the other hand, the maintenance staff cost is linked with the capital expenditure. You can download the complete Excel template here.

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.

Step 5a. An example of a Profit and Loss Statement. The spreadsheet links the interest expense and debt fees with the total amount of debt. You can download the entire Excel file here.

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.

Step 5b. An example of calculating depreciation in Microsoft Excel using a SUMPRODUCT formula. You can download the complete template here.

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:

2. Add non-cash items, such as depreciation,

Step 6a. An example of Cash Flow from Operations. The spreadsheet adjusts the net income for non-cash expenses such as depreciation. You can download the complete Excel template here.

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 ].

Step 6b. An example of Cash Flow from Investment. The template links this section to the capital expenditure projections. You can download the entire Excel file here.

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.

Step 6c. An example of a Cash Flow from Financing. The model links the debt injections to the capex forecast and estimates the equity required to fund any cash deficit. You can download the complete spreadsheet here.

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

Step 7a. An example of calculating assets in a Balance Sheet. The template links the long-term assets to the capex projections and offsets it with the depreciation. You can download the entire Excel file here.

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

Step 7b. An example of forecasting Balance Sheet’s liabilities in a Capital Investment Plan. The long-term liabilities reflect the debt injections offset by the principal repayments. You can download the complete spreadsheet here.

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.

Step 8a. An example of Capital Investment Plan’s annual summary in Microsoft Excel. Placing assumptions next to the table makes testing the impact of input changes easy. You can download the entire spreadsheet here.

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.

Step 8b. A graph showing annual Revenue, EBITA, Capital Investment and resulting Cumulative Cash Flow. You can download the entire Excel template here.

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.

Step 9. An example of calculating Return on Capital Investment in a Financial Model. The model takes EBITDA and its multiplier to calculate the exit proceeds. In addition, it uses Microsoft Excel’s NPV and IRR functions to estimate the returns. You can download the full template here.

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.

Step 10. An example of using Microsoft Excel data tables to stress test a Capital Investment Plan. Sensitivity #1 (left) shows the IRR impact of Market Reach and Cost per Acquisition. In addition, Sensitivity #2 (right) analyses the relationship between Capital Investment and Addressable Market. You can download the full template here.

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.

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].

*Order risk-free with a 30-day Money Back Guarantee

*Order risk-free with a 30-day Money Back Guarantee

## 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.

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.