Cost Benefit Analysis Model with Scenario Analysis
Generic Financial Model to Analyze the financial viability of a project or investment across monthly 5-year (Monthly & Annual) timeline and 3 scenarios
DOWNLOAD DETAILS:
Excel Model - Populated €35.00
PDF Example
€0.00
User-friendly Excel model intended for the preparation of a Cost Benefit Analysis to measure the financial feasibility for a proposed project or investment. The Cost Benefit analysis model has a monthly timeline across a 5 year period and includes 3 scenarios.
​
The model follows good practice financial modelling principles and includes instructions, line item explanations, checks and input validations
KEY OUTPUTS
-
The model is generic and not industry-specific. It computes the following key investment appraisal metrics for a proposed project or investment for each of the 3 scenarios:
-
Total one-off costs
-
Average benefits per annum
-
Net present value (NPV)
-
Internal Rate of Return (IRR)
-
Payback years
-
Breakeven Quarter
-
Profitability Index
-
-
Results also include a summary table for each of the 3 scenarios showing the overall costs and benefits on a:
-
Cash View – presents actual cash impacts of proposed project/investments
-
Profit & Loss (P&L) View – presents P&L impacts of proposed project/investment (e.g. Depreciation for Fixed asset investments rather than cash impact)
-
-
The Results tab also includes charts showing the cumulative net cash flow over time and costs vs net benefits
KEY INPUTS
Inputs are split into one-off costs incurred during the development phase and changes in ongoing costs and revenues expected to result after implementation of the project investment (which make up the net benefits).
​
One-off costs are further split into:
-
Fixed asset investments (up to 5 categories) consisting of investments that are capitalizable rather than expenses when incurred.
-
Project Staff costs (up to 8 categories)
-
Other Project Expenses (up to 15 categories)
Net Benefits are further split into
-
Changes in ongoing revenues resulting from the project/investment (up to 5 categories)
-
Changes in ongoing staff costs resulting from the project/investment (up to 5 categories)
-
Changes in other ongoing expenses (up to 10 categories)
Other inputs include:
-
Cost of Capital (for NPV)
-
Inflation rate
-
Corporate tax rate (and tax payment assumptions)
MODEL STRUCTURE
The model contains, 10 tabs split into input ('i_'), calculation ('c_'), output ('o_’) and system tabs. The only tabs to be populated by the user are the input tabs ('i_Setup' and 'i_Assumptions’ – one for each scenario). The calculation tabs (1 for each scenario) uses the user-defined inputs to calculate and produce the template outputs presented in 'o_Results'.
​
System tabs include:
-
A 'Front Sheet' containing a disclaimer, instructions and contents;
-
A checks dashboard containing a summary of checks by tab.
KEY FEATURES
-
The model follows good practice financial modelling guidelines and includes instructions, line item explanations, checks and input validations;
-
The model contains a flexible timeline that allows for any mix of project development and project run periods across a 5-year period.
-
Timeline is split on a monthly basis and summarised on an annual basis;
-
The model is not password protected and can be modified as required following download;
-
The model allows for the following number of underlying categories for each line item (these can be easily expanded if required):
-
Scenarios – 3 scenarios
-
Project costs:
-
Fixed asset investments – 5 categories
-
Project staff costs – 8 categories
-
Project expenses – 15 expenses
-
-
Ongoing costs and revenues:
-
Revenue streams – 5 categories;
-
Variable costs – 5 categories (one per revenue stream)
-
Ongoing staff costs – 5 categories
-
Ongoing other expenses – 10 categories
-
-
-
Cost and Revenue descriptions are fully customisable;
-
Project name, currency, scenarios and starting projection period, development phase and run phase are customisable;
-
The model includes a checks dashboard which summarises all the checks included in the various tabs making it easier to identify any errors;
-
The model includes checks and input validations to help ensure input fields are populated accurately.