Membuat Financial Modelling Excel

Financial Model Creation

Introduction to Financial Modeling

• The video begins with a greeting and an introduction to the topic of creating a simple financial model using specific data points, including annual sales, cost of capital, service value, growth, inflation, and initial investment.

Data Overview

• Key financial data is presented:

• Annual sales: Rp3,550,000

• Cost of capital: Rp12,000

• Service value: Rp10,000

• Growth rate: Rp15,000

• Inflation rate: Rp4,000

• Initial investment (initial outlay): Rp1.3 million over a project lifespan of 10 years.

Building the Financial Model Table

• The process starts by inputting growth information into a table format for each year up to Year 10. A formula is used to calculate growth at a rate of 5% per annum.

• Costs are calculated with an increase of 4% annually. This includes asset maintenance costs and profit calculations across multiple years.

Cash Flow Analysis

• Revenue and costs are analyzed to derive EBITDA figures. The net cash flow is calculated starting from the initial investment.

• Self-estimated value is determined as 10% of the initial investment and included in the final year’s calculations along with tax considerations.

Calculating Key Financial Metrics

• The calculation for cash flow results in significant values that will help assess project viability through metrics like YRF (Yearly Return Factor), IRR (Internal Rate of Return), and NPV (Net Present Value).

• YRF is computed using Excel formulas; it yields a result of approximately 14.3%. Further discussions on calculating NPV based on cost of capital follow.

Payback Period & ROI Calculation

• The payback period is manually identified as Year 6 but can also be calculated using Excel formulas for accuracy.

• ROI (Return on Investment) is derived from NPV divided by total investments made during the project lifecycle.

Final Insights on Profitability Index

• Profitability index calculations are discussed; it involves adding one to the ratio of NPV over total investments which provides insights into overall project profitability.

This structured approach allows viewers to understand how to create a financial model step-by-step while highlighting critical financial metrics essential for evaluating business projects effectively.