How to Create a Capital Investment Plan in Excel

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.

capital investment addressable market forecast template

Step 1a. An example of a Capital Investment and Addressable Market Forecast. Download the complete Excel template here.

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

market reach excel forecast template

Step 1b. An example of a Market Reach and Customer Growth forecast. Download the full Excel spreadsheet here.

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

customer revenue financial model forecast template

Step 2. An example of calculating Customer Revenue in a Financial Model. Download the complete Excel template here.

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.

variable opex financial model excel template

Step 3. An example of calculating Variable Opex in a Capital Investment Plan. Download the entire Excel Financial Model here.

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.

capital investment fixed resource plan template

Step 4a. An example of a Fixed Resource Plan in Excel. 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.

variable staff excel forecast

Step 4b. An example of a Variable Staff Forecast in Excel. Download the full Financial Model template here.

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.

profit and loss example excel

Step 5a. An example of a Profit and Loss Statement in Excel. Download the entire template here.

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.

depreciation calculation sumproduct excel template

Step 5b. An example of calculating depreciation in Excel using a SUMPRODUCT formula. Download the entire Financial Model template here.

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:

  1. Start with the Net Income from the Profit and Loss Statement.
  2. Add non-cash items, such as depreciation, from your financial model’s calculations.
  3. In addition, add payable and receivable items.
cash flow operations capital investment plan

Step 6a. An example of operating cash flow in a Capital Investment Model. Download the full Excel template here.

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

cash flow capital investment plan excel

Step 6b. An example of Cash Flow from Investment in Excel. Download the entire financial model here.

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.

capital investment financing cash flow excel

Step 6c. An example of a Cash Flow from Financing in a Capital Investment Plan. Download the full Excel template here.

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

balance sheet assets forecast excel

Step 7a. An example of calculating assets in a Balance Sheet. Download the entire Excel spreadsheet 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).

capital investment plan balance sheet liabilities

Step 7b. An example of forecasting Balance Sheet’s liabilities in a Capital Investment Plan. Download the full Excel template here.

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.

capital investment plan summary

Step 8a. An example of Capital Investment Plan’s annual summary. Download the full Excel spreadsheet here.

Finally, link the output to graphs, which you can copy and paste into your presentations.

revenue cash flow forecast excel graph

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

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.

return on capital investment excel template

Step 9. An example of calculating Return on Capital Investment in a Financial Model. Download the full Excel 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 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.

scenario analysis data table example excel

Step 10. An example of using Excel data tables to stress test a Capital Investment Plan. The left table shows the IRR impact of Market Reach and Cost per Acquisition. The right table analyses the relationship between Capital Investment and Addressable Market. Download the full Excel 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 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

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

Learn More:

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.

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.

Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.

, ,
Menu

Order Risk-Free with a 30-Day Money Back Guarantee