How to Create a Capital Investment Plan

Last Updated on May 29, 2025

In this tutorial, I’ll walk you through setting up a full capital investment forecast in Microsoft Excel – from modelling capex injections to building Profit and Loss, Cash Flow, and Balance Sheet Statements.

You’ll also learn how to link your capital investment to customer growth, calculate returns on investment, and stress test key assumptions in your financial model.

To follow along, you can download my Capital Investment Plan Template. For a full walkthrough, watch the video tutorial at the end of this post.

Step 1. Set Up Capital Investment and Forecast Market Growth

Start your Capital Investment Plan by laying out the capital investment’s phasing. Open a new spreadsheet and use the first rows to show the deployment of funds across the project timeline. Link these injections directly to your customer growth assumptions.

For example, you could spread the funding into equal monthly tranches and assume that each new investment increases the overall addressable market — the number of customers you aim to serve over time.

Next, apply a market reach assumption to forecast how many customers will connect to your services.

For example, if the addressable market is 100,000 customers and your market reach assumption is 35%, you would expect 35,000 active customers (100,000 × 35%).

Customer and sales estimates will become the foundation for forecasting revenues, service costs, and capital needs throughout the plan.

📈 If you’re also planning how customer reach turns into revenue, you might find my Marketing Investment Plan tutorial helpful — it connects market expansion to projected sales.

Step 2. Forecast Revenues from Customer Growth

With your customer growth forecast in place, the next step is projecting customer revenues based on service pricing assumptions.

You can calculate the revenue by multiplying the number of users or sales and the average revenue per user or unit sold.

For example, if you have 100 active users and each spends $25 per month, your estimated monthly revenue would be $2,500 (100 × $25).

Be sure to include an inflation adjustment to account for future price increases – even small pricing uplifts can significantly impact long-term cash flow forecasts.

Step 3. Model Scalable Variable and Fixed Operating Costs

Once you have your revenue forecast, the next step is to model variable and fixed costs and capture how expenses evolve with business growth.

Here are two types of costs you can forecast in your model:

• Variable Costs – expenses that scale with customer numbers, revenues, or project size (e.g., marketing acquisition costs linked to new customers),
• Fixed Costs – stable expenses that remain consistent regardless of growth (e.g. central operating costs, basic infrastructure maintenance).

For example, in my Capital Investment Plan Template marketing expenses are calculated by multiplying the number of new customers and the average cost per acquisition. You could link maintenance expenses to the total amount of capital invested and calculate payment processing fees as a percentage of revenue.

Building a mixed structure of variable and fixed costs allows your financial model to scale dynamically and provide more accurate long-term forecasts.

📊 See my tutorial on Project Finance Modeling that explains how to separate fixed and variable costs across larger forecasting structures, which is useful if your expenses scale with investment.

Step 4. Forecast Headcount and Staff Costs Based on Growth Drivers

Accurately forecasting headcount and staffing costs is a key part of your Capital Investment Plan. Start by separating job functions into fixed and variable categories:

• Fixed Staff — roles like executive leadership or finance teams that stay relatively stable regardless of customer growth,
• Variable Staff — roles such as customer service, sales, or maintenance teams that grow alongside customer or asset expansion.

You can group headcount estimates by general role or department, with each group’s size multiplied by an average monthly salary to calculate total staffing costs.

Tie variable staffing to key growth drivers. For example, my financial model links maintenance headcount to capital investment and increase customer support staff with the number of active users or sales.

Include employee overheads (such as taxes, bonuses, and pension costs) and apply an inflation factor to account for rising salary expenses over time.

Step 5. Model Depreciation of Capital Investments in the P&L

With your revenue and cost forecasts in place, the next step is to build a Profit and Loss Statement and account for depreciation of capital investments.

Create a Profit and Loss Statement and link:

• Revenue forecasts to customer growth and pricing assumptions,
• Operating costs to fixed and variable cost structures,
• Debt costs to interest expenses and loan fees,
• Depreciation to capital expenditure timelines and asset lifespans.

Depreciation is calculated separately by summing up the capital investments from prior periods and dividing them by the useful life assumptions for each asset group.

For example, if cumulative capital expenditures by Year 2 are $2.0 million and cumulative depreciation is $0.2 million, the resulting Long-Term Asset value would be $1.8 million ($2.0m—$0.2m).

📥 Depreciation schedules are already built into my template. You can download the entire spreadsheet to see how it tracks capex usage and calculates depreciation over time – no manual formulas needed.

Step 6. Build a Linked Cash Flow Statement

The next step is to build a complete Cash Flow Statement by organizing your financial model’s outputs into standard categories:

• Cash Flow from Operations — revenues, operating costs, and non-cash adjustments (like depreciation),
• Cash Flow from Investment — capital expenditures and any asset sales,
• Cash Flow from Financing — debt injections, equity contributions, and repayments.

To create the Cash Flow from Operations statement, start with Net Income and adjust it for non-cash items such as depreciation and changes in working capital (payables and receivables).

Cash Flow from Investment links directly to capital expenditure forecasts. If asset sales occur, record the proceeds here, but remember that gains or losses from the sale affect the operating cash flow [read more – external link].

Cash Flow from Financing ties debt and equity movements together.

Step 7. Integrate Capex and Financing into the Balance Sheet

Once your Cash Flow and Profit and Loss Statements are complete, the next step is to integrate the capital investment into the Balance Sheet so that all financial statements remain connected.

Link the cumulative capital expenditures from the Cash Flow Statement into the Long-Term Assets section of the Balance Sheet. Then, offset the amount by cumulative depreciation from the Profit and Loss Statement to calculate the assets’ net book value.

For example, if cumulative capital expenditures by Year 2 are $2.0 million and cumulative depreciation is $0.2 million, the resulting Long-Term Asset value would be $1.8 million ($2.0m minus $0.2m).

Similarly, debt financing movements flow into the Balance Sheet’s liabilities section. Record the cumulative debt drawn and offset the amount by principal repayments to show the outstanding long-term debt balance.

For example, if the forecast calculates $1.5 million of debt by Year 2 and $0.3 million has been repaid by then, the Long-Term Debt Liability will show $1.2 million outstanding ($1.5m minus $0.3m).

Finally, any equity injections required to cover funding gaps are added to the Owner’s Equity section of the Balance Sheet, ensuring a complete and balanced view of the project’s financial position.

🧾 If you’re linking investments and financing across statements, my Financial Model with Debt Financing tutorial shows how debt and equity flow through into the balance sheet.

Step 8. Summarize Your Capital Investment Plan with Key Assumptions

After completing the detailed monthly forecasts, summarize your Capital Investment Plan into an annual view. The summary will allow you to easily spot financial trends and understand the bigger picture of revenue, costs, and cash flow movements over time.

Display the key outputs from the model side-by-side with the main input assumptions. This layout makes it simple to test the sensitivity of results when changing key drivers such as customer growth or capital investment rates.

Finally, link the summary’s output with graphs and charts to visualize revenues, expenses, capital investment, and cumulative cash flow trends. For example, show the relationships between revenue growth, capital spending, and the resulting cash flow position.

You will use this summarized view later to calculate the Return on Investment and run stress tests on the model’s assumptions.

Step 9. Calculate ROI Using IRR, NPV, and Terminal Value

After summarizing your Capital Investment Plan, the next step is calculating the Return on Investment (ROI) by estimating equity contributions and the project’s terminal value.

Start by forecasting the amount of equity needed to keep the cash balance positive across the forecast period. If the cash flow shows a deficit at any point, inject equity to cover the gap.

Next, calculate the terminal value. One valuation method is to take the exit year’s EBITDA and apply a multiplier.

For example, if the final year’s EBITDA is $1 million and the valuation multiple is 10x, the terminal value would be $10 million (1 × 10 = $10m).

Finally, use Microsoft Excel’s IRR and NPV formulas to calculate the Internal Rate of Return and Net Present Value, which will provide a complete picture of the project’s potential returns.

Each industry will have different appropriate valuation multiples based on historical acquisitions, IPOs, or investment benchmarks, so choose one aligned with your sector.

Step 10. Stress Test the Investment Model with Scenario Analysis

After completing your base forecast and return calculations, it’s essential to understand its sensitivity to key assumptions.

You can use Microsoft Excel’s data tables and test how changes in key variables, such as capital investment levels, market reach, or customer acquisition costs, affect the Internal Rate of Return (IRR) and overall cash flow outcomes.

For example, analyze how an increase in capital expenditure or a higher marketing spend would impact your required equity injections and project returns.

Stress testing your Capital Investment Model will help you uncover potential risk points, identify the most sensitive assumptions, and build contingency plans for unexpected developments.

🔬For another look at stress testing in complex projects, my Telecom Investment Model tutorial walks through scenario analysis tied to infrastructure and customer rollout plans.

📌 Recap: 10 Steps to Build a Capital Investment Plan

Here’s a quick recap of the steps we covered to build a complete Capital Investment Plan:

1. Set Up Capital Investment and Market Growth. Link capital injections to the expansion of the addressable market.
2. Forecast Customer Revenues. Tie customer growth to service pricing assumptions to project income.
3. Model Variable and Fixed Costs. Separate costs into scalable and fixed components to forecast expenses.
4. Forecast Headcount and Staffing Costs. Project staffing needs based on growth drivers and apply salary assumptions.
5. Model Depreciation of Capital Investments. Spread capex spending over asset useful lives using depreciation calculations.
6. Build a Cash Flow Statement. Organize the cash flows into operating, investing, and financing sections.
7. Integrate Capital Investment into the Balance Sheet. Link assets, liabilities, and equity movements across the forecast.
8. Summarize Your Capital Investment Plan. Consolidate monthly forecasts into an annual view with input assumptions.
9. Calculate Return on Investment (ROI). Estimate IRR, NPV, equity injections, and the project’s terminal value.
10. Stress Test the Capital Investment Model. Analyze how changes in key assumptions impact financial outcomes.

Following these steps, you’ll build a dynamic Capital Investment Plan ready for real-world forecasting, valuation, and investment analysis.

📥 Download My Capital Investment Plan Template

Download the Excel template I use in this tutorial — it’s pre-built to mirror the full process outlined above, from capex planning and customer growth to ROI calculations and stress testing. Each section is linked and automated, so you can jump straight into modeling without starting from scratch.

This spreadsheet includes everything you need to build your Capital Investment Plan, including:

• Capital Investment and Market Growth Forecasts: linking capex injections to addressable market expansion.
• Customer Revenue Projections: based on pricing assumptions and customer growth.
• Variable and Fixed Cost Modeling: tied to operational drivers and scalability.
• Headcount and Staffing Cost Forecasts: linking salaries to business growth.
• Profit & Loss, Cash Flow, and Balance Sheet Statements: fully integrated and dynamic.
• Equity Funding and Terminal Value Calculations: with built-in IRR and NPV metrics.
• Stress Testing Framework: using Excel data tables for scenario analysis.

The template is in Excel format and easily convertible to Google Sheets if needed.

👁️ View full template details

Video Tutorial on Building a Capital Investment Plan

Follow along step-by-step in my complete video tutorial, where I show you how to create and customize a Capital Investment Plan using Microsoft Excel. In the video, you’ll learn:

• How to link capital injections to market growth and customer forecasts.
• How to project customer revenues and model variable and fixed operating costs.
• How to build integrated Profit & Loss, Cash Flow, and Balance Sheet statements.
• How to calculate return on investment and stress test key financial assumptions.

By the end of the tutorial, you’ll know how to use my Capital Investment Plan Template to build dynamic forecasts and analyze investment outcomes.

▶️ Watch on YouTube

 

Get in Touch

Hi, I’m Jacek. I’m passionate about spreadsheets and financial models, and I hope this tutorial gave you a solid foundation for building your Capital Investment Plan.

If you have any questions about financial modelling, forecasting, or Excel techniques, feel free to reach out.

You can also explore my other tutorials for more hands-on guides or check out my One-to-One Training and Financial Modelling Services for personalized support.

Disclaimer: This tutorial is for informational and educational purposes only and should not be considered professional advice.

Explore More Tutorials

Want to dive deeper into financial modelling? Here are more tutorials you might find helpful:

• Financial Model with Debt Financing — Explore how to add debt financing to a project and calculate the impact on funding and returns.
• Merger and Acquisition Model — Discover how to consolidate two businesses and forecast the financial impact of acquisitions.
• How to Create a Project Finance Model — Learn how to model multiple projects under one umbrella cash flow and optimize financing structures.
• Telecom Investment Model — Combine infrastructure deployment with customer revenue forecasts and stress testing techniques.
• Your First Steps in Microsoft Excel – Beginner’s Crash Tutorial — Refresh essential spreadsheet formulas and functionalities to build stronger financial models.

→ View all tutorials