Originally published: 01/05/2021 08:18
Last version published: 07/05/2021 19:34
Publication number: ELQ-89870-2
View all versions & Certificate
Last version published: 07/05/2021 19:34
Publication number: ELQ-89870-2
View all versions & Certificate
Loan for Use (Comodatum) Feasibility Analysis Model
Feasibility analysis model for a loan for use arrangement supported by additional product sales
Description
PURPOSE OF TOOL
User-friendly Excel model intended for the preparation of a feasibility analysis on equipment provided to a client on a loan for use (commodatum) basis which is supported by the sale of accompanying products to that client. The feasibility model has a monthly timeline across a 10-year period and includes 3 scenarios for sales volumes.
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 a number of key appraisal metrics for proposed project or investment including the following for each of the 3 scenarios:
- Total Profit or Loss across loan period
- Average additional sales per annum
- Internal Rate of Return (IRR)
- Payback years
- Breakeven Month
Results also include a summary table for each of the 3 scenarios showing the overall costs and benefits on a:
- Cash basis – presents actual cash impacts of proposed loan and product sales
- Profit & Loss (P&L) basis – presents P&L impacts of proposed loan and product sales (e.g. Depreciation for loaned equipment rather than cash impact)
The Results tab also includes charts showing the cumulative net cash flow over time and loan costs vs profit margin on additional sales.
KEY INPUTS
Inputs are split into general inputs, loan item assumptions and Sale product assumptions
General inputs include:
- Loan name
- Currency
- Loan inception month and year
- Loan duration
Loan item inputs include:
- Value at inception and maturity date
- Initial transaction costs (up to 5 cost categories)
- Ongoing annual costs (up to 5 cost categories)
Sales product assumptions include:
- Name of sales products (up to 5 product categories)
- Fully loaded cost price per product
- Sales price per product
- Sales volume on minimum, expected and best-case basis (3 scenarios)
MODEL STRUCTURE
The model contains, 5 tabs split into input ('i_'), calculation ('c_'), output ('o_’) and system tabs. The only tab to be populated by the user is the 'i_Assumptions’ tab. The calculation tabs uses the user-defined inputs to calculate and produce the template outputs presented in 'o_Results'.
System tabs include the following:
- 'Front Sheet' containing a disclaimer, instructions and contents;
- Checks dashboard containing a summary of checks by tab.
KEY FEATURES
Other key features of this model include the following:
- The model follows good practice financial modelling guidelines and includes instructions, line item explanations, checks and input validations;
- The model contains a flexible timeline across a 10-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;
- Cost and Revenue descriptions are fully customisable;
- Loan name, currency and starting projection period 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.
MODIFICATIONS
If you require any be-spoke modifications to the tool, we are more than happy to assist with this. Please send us a message through the Eloquens site or contact us on: [email protected]
ABOUT PROJECTIFY
We are a small team of financial modelling professionals with experience working in Big 4 Business Modelling teams and strong experience supporting businesses with their financial planning and decision support needs. Our aim is to provide robust and easy-to-use tools that follow best practice financial modelling guidelines and assist individuals and businesses with common financial planning and analysis processes.
We are keen to make sure our customers are satisfied with the tools / models they purchase and will be more than happy to assist with any questions or support required following or in advance of purchase.
We are also always keen to receive feedback so please do let us know what you think of our products/offering by sending us a message or submitting a review.
PURPOSE OF TOOL
User-friendly Excel model intended for the preparation of a feasibility analysis on equipment provided to a client on a loan for use (commodatum) basis which is supported by the sale of accompanying products to that client. The feasibility model has a monthly timeline across a 10-year period and includes 3 scenarios for sales volumes.
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 a number of key appraisal metrics for proposed project or investment including the following for each of the 3 scenarios:
- Total Profit or Loss across loan period
- Average additional sales per annum
- Internal Rate of Return (IRR)
- Payback years
- Breakeven Month
Results also include a summary table for each of the 3 scenarios showing the overall costs and benefits on a:
- Cash basis – presents actual cash impacts of proposed loan and product sales
- Profit & Loss (P&L) basis – presents P&L impacts of proposed loan and product sales (e.g. Depreciation for loaned equipment rather than cash impact)
The Results tab also includes charts showing the cumulative net cash flow over time and loan costs vs profit margin on additional sales.
KEY INPUTS
Inputs are split into general inputs, loan item assumptions and Sale product assumptions
General inputs include:
- Loan name
- Currency
- Loan inception month and year
- Loan duration
Loan item inputs include:
- Value at inception and maturity date
- Initial transaction costs (up to 5 cost categories)
- Ongoing annual costs (up to 5 cost categories)
Sales product assumptions include:
- Name of sales products (up to 5 product categories)
- Fully loaded cost price per product
- Sales price per product
- Sales volume on minimum, expected and best-case basis (3 scenarios)
MODEL STRUCTURE
The model contains, 5 tabs split into input ('i_'), calculation ('c_'), output ('o_’) and system tabs. The only tab to be populated by the user is the 'i_Assumptions’ tab. The calculation tabs uses the user-defined inputs to calculate and produce the template outputs presented in 'o_Results'.
System tabs include the following:
- 'Front Sheet' containing a disclaimer, instructions and contents;
- Checks dashboard containing a summary of checks by tab.
KEY FEATURES
Other key features of this model include the following:
- The model follows good practice financial modelling guidelines and includes instructions, line item explanations, checks and input validations;
- The model contains a flexible timeline across a 10-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;
- Cost and Revenue descriptions are fully customisable;
- Loan name, currency and starting projection period 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.
MODIFICATIONS
If you require any be-spoke modifications to the tool, we are more than happy to assist with this. Please send us a message through the Eloquens site or contact us on: [email protected]
ABOUT PROJECTIFY
We are a small team of financial modelling professionals with experience working in Big 4 Business Modelling teams and strong experience supporting businesses with their financial planning and decision support needs. Our aim is to provide robust and easy-to-use tools that follow best practice financial modelling guidelines and assist individuals and businesses with common financial planning and analysis processes.
We are keen to make sure our customers are satisfied with the tools / models they purchase and will be more than happy to assist with any questions or support required following or in advance of purchase.
We are also always keen to receive feedback so please do let us know what you think of our products/offering by sending us a message or submitting a review.
This Best Practice includes
1 Excel Model
Further information
Understanding viability of providing a loan for use (comodatum)