One major component of building a business plan model is the calculations associated with a CAPEX Plan.

Along with other components of a financial model, the capex workflow in a model is probably the first module to build as it captures the actual investment in long term (fixed) assets. 

This includes construction of buildings and factories in addition to real estate development assets.

This worksheet is a sub-module to be used by financial modelers to integrate into their business plan models. 

It will save them the time of creating many calculation tables, and can use this module to link its outputs to the rest of their business plan financial statements and analysis.

The entire model is built on one sheet to allow integration into a main business plan financial model.

By synchronizing the timeline of this module to your main model and linking the outputs to your financial statements and cash flow tables, you could save a lot of development time.

The impact of capital expenditure is critical in cash flow projections in project finance models as well as operating business plan models of corporations.

Capex is a major determinant of the feasibility and reliability of financial projections since it impacts all projected financial statements including projected income statements, balance sheets and cashflows.

Correct calculation of capex depreciation is essential for tax calculations and showing the net book value of assets on projected balance sheets.

Funding required for corporations or projects will depend on the capex plan since the disbursements made for capex comprise the major component in financing in addition to working capital.

Sample data of 8 asset classes and 18 sub-classes is used for demonstration.  Then based on those definitions, 13 assets were assumed to be purchased.

This same model could actually be used to run depreciation schedules for historical fixed asset purchases.

Tables 1 – 3 are dedicated for the inputs of the model which include defining the asset classes, asset sub-classes and plugging in a capex plan for asset purchases.

Tables 4 – 9 are monthly timeline sections where the core calculations are done to:

Spreading capex on the monthly timeline

Calculating depreciation for each asset on a monthly basis along with accumulated depreciation.

Capturing expected disposal values for each asset on the monthly timeline along with gain/losses on disposals.

Tables 10 – 12 are output tables for the carrying value of assets across all three timelines (monthly / quarterly / annual).  Those tables’ outputs should be linked to the balance sheet projections of the main business plan.

Tables 13 – 15 are dedicated to showing the depreciation per asset class across all timelines.  This section is an output to be linked to the income statement projections in the main business plan model.

Tables 16 – 18 spread the expected gain / loss on disposal of assets across all timelines, which should also be linked to the projected income statements.

Tables 19 – 21 consolidate the cash flow impact of capex across all timelines for linking to the projected cash flow statements.

The first step in table 1 is to define the main asset classes.

Sample data is already plugged in the model for 8 major asset classes, each with its expected life-span in months.

Up to 15 main asset classes are allowed.

The second step in table 2 is to create asset sub-classes and link them to the main asset classes created.

Up to 50 asset sub-classes are allowed.

Those two steps are necessary before any specific asset is added to the capex plan.

In table 3, the planned asset purchases are plugged in with up to 200 assets.

Each asset will have a specified date linking it to the monthly calculations.

Each asset will have to be linked to an asset sub-class to take its life assumptions coming from the main asset.

Other fields include stating a purpose for the purchase or description along with the expected acquisition cost, months to dispose the asset and the salvage value as a ratio from the purchase cost.