What goes into building a custom financial model

When a client commissions a financial model, they often imagine the process going something like this: a specialist opens Excel, plugs in numbers, and delivers a finished file in a couple of days. In reality, building a financial model is analytical work that begins long before the first formula and doesn't end when the file is sent — it ends after the client fully understands the structure.

In this article, I'll walk through every stage of development — from the initial project conversation to final delivery. This will help you understand what you're paying for, how the process works, and what you can do on your end to make the result as accurate as possible.

Initial project review

Everything starts with understanding exactly what needs to be modeled. This is not a formality — the quality of the initial review determines all subsequent work.

At this stage, the developer digs into the essence of the project:

• What kind of business is it? Type of activity, industry, geography, stage of development (startup, operating business, new division within an existing company).
• What is the model's purpose? Raising investment, securing a loan, internal planning, evaluating project payback, building a case for the board of directors.
• Who will use the model? The entrepreneur themselves, a bank analyst, an investment committee, a development fund. The audience determines the level of detail, presentation format, and set of metrics.
• What is the planning horizon? 1 year, 3 years, 5 years, 10 years — depends on the project type and objective.

This typically involves one or two calls or a written brief. A good brief saves time for both sides. A poor brief — or none at all — leads to iterations where the model has to be reworked because the task was misunderstood from the start.

Collecting source data

A financial model is built on data. The more accurate the inputs, the more accurate the result. The principle is simple: garbage in — garbage out.

What data is typically needed:

• For a new project: a business plan or concept description, preliminary agreements or supplier quotes, pricing data for rent and equipment, staffing plan, marketing plan.
• For an existing business: management reporting for the past 1–3 years, trial balance, sales data broken down by product and channel, cost structure, loan agreements.

In practice, the client almost never has all the data ready at once. Some information has to be gathered along the way: verifying prices with suppliers, requesting quotes, researching market rates. The model developer helps determine which data is critical and which can be estimated based on expert judgment.

Data collection is often the longest stage. Not because it's technically difficult, but because it depends on the client. A model built on assumptions instead of real data will be a beautiful spreadsheet, but not a reliable tool.

Analyzing business logic

Before writing formulas, you need to understand how the business works. This is analytical work that determines the model's architecture.

The developer figures out:

• How revenue is generated: how many products or services there are, how pricing works, whether there's a subscription model, which sales channels exist.
• What the operating cost structure looks like: what counts as fixed costs, what's variable, what scales with sales volume.
• How the production or operational cycle works: how long from raw material purchase to receiving payment, what stages an order goes through.
• What dependencies exist between elements: sales growth requires more staff, more staff requires more office space, more office space means capital expenditures.

The result of this stage is a model blueprint: which blocks will exist, how they connect, and what input parameters each block requires. This is the architecture on which everything else depends.

Revenue modeling

The revenue block is usually the most complex and most important. This is where the greatest uncertainty lies and where mistakes are made most often.

A professional model builds revenue bottom-up — from specific drivers, not from a desired result. Here's how this works across different business types:

Retail: daily visitors (traffic) × conversion rate × average transaction value. Traffic depends on location and marketing, conversion — on assortment and service, average transaction value — on pricing strategy. Each parameter is modeled separately, accounting for seasonality and trends.

Manufacturing: production volume × unit price. Volume is constrained by equipment capacity and the ramp-up schedule. Price depends on the market and may change over time.

SaaS / subscription: number of customers × average subscription fee. Customer count is modeled through new acquisition and churn. Average revenue per user can grow through upselling.

Project-based business: number of projects per year × average project value. Project count is limited by team capacity; average value depends on project type and client profile.

Each business has its own logic. The developer's job is to build a revenue model that reflects the actual mechanisms of income generation, rather than simply showing 20% annual growth.

Operating expense modeling

Expenses in the model fall into two broad categories: variable (dependent on sales volume) and fixed (independent or weakly dependent).

Variable expenses are tied to revenue or production volume:

• Cost of goods — purchase cost, logistics, customs duties
• Raw materials — for manufacturing
• Commissions — payment processing, marketplace fees, agent commissions
• Performance-based payroll — sales commissions for managers

Fixed expenses do not depend on sales volume (up to a certain threshold):

• Rent — office, warehouse, production facilities
• Payroll — salaries, payroll taxes
• Administrative costs — accounting, legal, software, telecom
• Marketing — can also be variable, depending on strategy

An important point: fixed costs are only fixed within a certain range. As the business grows, you need to hire people, expand facilities, and upgrade infrastructure. A good model accounts for these thresholds: up to X in sales volume, one warehouse is enough; beyond X, a second is needed — creating a step-function increase in costs.

Payroll typically accounts for 30–60% of all operating expenses in most businesses, so this block is modeled with particular care: by position, with a hiring timeline, and including all applicable employment taxes and contributions.

Investment and launch cost modeling

The investment block describes how much money needs to be invested before operations begin and during the growth phase.

Typical capital expenditure items:

• Construction or renovation — design, construction work, finishing
• Equipment — procurement, delivery, installation, commissioning
• Intangible assets — licenses, patents, software development
• Working capital for launch — initial inventory, prepayments, expenses before reaching breakeven

The investment drawdown schedule is critically important — when exactly the money is needed. Construction isn't paid in a single lump sum, equipment is delivered in batches, and working capital builds up gradually. The model must show month-by-month funding requirements so that the investor or lender understands the investment timeline.

A common mistake is forgetting about working capital. A project can be profitable on the P&L but still require constant working capital injections as it grows. Without this calculation, the model will show profit while the business actually has no cash to purchase inventory.

Taxes, loans, leasing, and subsidies

This block accounts for all external financial factors that affect cash flow and profitability.

Taxes. The model calculates the tax burden based on the applicable tax regime and jurisdiction. This includes corporate income tax, value-added tax, payroll taxes, property taxes, and any sector-specific levies. For some projects, multiple tax structures are compared to find the optimal one.

Loans. If the project is financed with debt, the model calculates the repayment schedule (annuity or straight-line), interest expenses, and outstanding principal. For bank-oriented models, DSCR — the debt service coverage ratio — is calculated separately to show whether cash flow is sufficient to service the loan.

Leasing. Leasing is a common instrument for acquiring equipment and vehicles. The model accounts for lease payments, down payment, residual value, and the tax implications (lease payments reduce the taxable base differently than depreciation of owned assets).

Subsidies and grants. For projects eligible for government or institutional support, the model factors in interest rate subsidies, equipment grants, tax incentives for special economic zones, and small business support programs.

Cash flow, profit, and balance sheet

These are the three core output statements of a financial model, each showing the result from a different angle.

Income statement (P&L) shows economic efficiency: revenue minus expenses equals profit. But profit is an accounting metric. You can be profitable and still have no cash in the bank.

Cash flow statement shows the actual movement of money: how much came in, how much went out, how much is left. Cash flow accounts for payment delays, capital expenditures, and debt service — everything that doesn't appear on the P&L. It's the cash flow statement that reveals potential cash gaps and when additional financing is needed.

Balance sheet shows the structure of assets and liabilities at each reporting date. Not every project needs a full balance sheet, but for bank models and serious investment presentations, it's mandatory.

All three statements must be interconnected. Profit from the P&L flows into the balance sheet; the cash flow reconciles with the cash position on the balance sheet. If the statements don't tie out, the model has an error.

KPIs: NPV, IRR, payback, profitability

Key performance indicators are often the entire reason the model is built. They answer the main question: is it worth investing in this project?

NPV (Net Present Value) shows what the project is worth in today's dollars, accounting for the cost of capital. If NPV is positive, the project creates value. If negative, it destroys value. NPV requires choosing a discount rate, which is a separate task: the rate should reflect the cost of capital and project risks.

IRR (Internal Rate of Return) shows the project's annualized return. If IRR exceeds the cost of capital, the project is worthwhile. IRR is convenient for comparing projects against each other and against alternative investments.

Payback period — simple and discounted. The simple version shows how many years until cumulative cash flow covers the investment. The discounted version does the same but accounts for the time value of money.

Profitability margins — gross, operating, and net. These show efficiency at each level: how much remains after deducting direct costs, operating expenses, and taxes.

Beyond standard KPIs, the model can calculate project-specific metrics: DSCR for lenders, ROE and ROIC for investors, LTV/CAC for digital businesses.

Scenarios and sensitivity

One of the greatest values of a professional model is the ability to work with scenarios. Any forecast is a set of assumptions, and any assumption can turn out to be wrong.

Scenario analysis involves calculating several possible outcomes:

• Base case — the most likely outcome, based on realistic assumptions
• Optimistic case — if everything goes better than expected: higher sales, lower costs, faster launch
• Pessimistic case — if key parameters come in worse: lower sales, higher costs, longer launch
• Stress test — an extreme scenario: what happens in the event of a sharp demand drop or sudden cost increase

Sensitivity analysis is a more refined tool. It shows how the result (NPV, profit, payback period) changes when a single parameter is varied. For example: if the average transaction value drops by 10%, NPV falls by 40% — meaning the project is highly sensitive to pricing, and that's a key risk.

Sensitivity analysis results are typically presented as a table (tornado chart or data table) that clearly shows which parameters have the greatest impact on the outcome.

Scenario analysis isn't about predicting the future — it's about understanding the margin of safety. If a project is profitable only in the optimistic case, that's a warning sign. If it's profitable even in the pessimistic case, it's a robust project.

Logic and formula verification

The finished model undergoes verification — a check for errors and logical consistency. This is a critical stage that distinguishes professional work from amateur work.

What gets checked:

• Statement reconciliation. The P&L, cash flow, and balance sheet must be consistent with each other. Period profit must correctly flow into the balance sheet. The cash balance in the cash flow statement must match cash on the balance sheet.
• Formula accuracy. Every formula is checked for correct references, proper ranges, and the absence of circular references (or their controlled use).
• Boundary conditions. What does the model show at zero sales? During rapid growth? With negative cash flow? Formulas must work correctly under all conditions.
• Sanity checks. Metrics are checked for reasonableness: profitability can't be 90% in retail, cost of goods can't be zero for a manufacturer, payroll can't be 5% of expenses at a service company.

After verification, the model is tested against scenarios: when key parameters change, the result should change logically and predictably.

Delivering the model and explaining the structure

The final stage is delivering the finished model and explaining how to use it. This stage is no less important than the development itself: a model the client doesn't understand is useless.

What delivery includes:

• The model file — an Excel workbook with clear structure, labeled sheets and cells, and highlighted input parameters (usually color-coded or bordered).
• Structure overview — which blocks the model contains, how they're connected, which sheets are for data input, which are calculation sheets, and which are summary outputs.
• Assumptions documentation — what the key parameters are based on: why a particular conversion rate was chosen, where the rental rate comes from, how capacity utilization was calculated.
• Instructions for working with scenarios — how to change input parameters, how to switch between scenarios, which cells can be edited, and which cannot.

A call or meeting is held where the developer walks through the model with the client, answers questions, and demonstrates how to work with the file. After delivery, consulting support is provided for 6–12 months, during which questions about the model can be addressed or adjustments made within the agreed scope.

A good model is one the client can use independently after delivery. If every change requires calling the developer, the model was either built poorly or not properly explained.

Development timelines depend on project complexity and the completeness of the data provided. Specific timelines are stated in the proposal. The main factor affecting duration is how quickly the client provides data. The model is built on your project's real data, and the faster that data is available, the faster the result is ready.