How to Build a Financial Forecast Model in Excel: A Step-by-Step FP&A Example

I have a complicated relationship with financial models. I love a spreadsheet where I can change one assumption and immediately see what happens to revenue, profit, and cash. Give me 37 tabs, hidden rows, and a formula referencing somebody’s old laptop, and I become considerably less enthusiastic.

The problem isn’t Excel. It’s that we’ve gotten very good at making financial models look sophisticated without always making them useful. A forecast should tell us what happens when we sell more, what happens when costs rise, and whether we will have enough cash to pay for any of it.
To sanity-check a growth assumption before building the full workbook, use my revenue growth and forecast calculator. It shows the compounding effect, not whether the sales team can deliver it.

What is a financial forecast model?

A financial forecast model estimates future performance from operating assumptions such as units sold, pricing, staffing, supplier costs, and customer payment timing. A useful model connects those assumptions to projected earnings and cash flow. A full three-statement model also rolls forward the balance sheet.

If you need help selecting the right inputs first, see how to find the right business drivers. Here, we’re going to put the numbers to work.

☆ Bookmark this page — keep this forecast example handy.

Step 1: Start with assumptions you can explain

Let’s use a hypothetical product business. It sells 1,000 units a month for $200 each. Variable cost is $80 per unit, fixed cash operating expenses are $70,000, depreciation is $5,000, interest is $2,000, and the illustrative tax rate is 25%. Beginning cash is $50,000. These are teaching assumptions, not industry benchmarks.

In Excel, I’d separate assumptions, revenue, expenses, working capital, and financial statements into clear sections or worksheets. Enter each assumption once and link the calculations to it. Hope is not a financial control.

Step 2: Calculate revenue and operating profit

Revenue is units multiplied by selling price: 1,000 × $200 = $200,000. Variable costs are 1,000 × $80 = $80,000. That leaves $120,000 in gross profit. After $70,000 of fixed cash expenses, EBITDA is $50,000. Subtract $5,000 depreciation and $2,000 interest and pretax income is $43,000. At an illustrative 25% tax expense, net income is $32,250.

The Excel logic is simple: =Units*Price for revenue, =Units*UnitCost for variable costs, and =Revenue-VariableCosts-FixedCashExpenses for EBITDA. The model becomes useful when we change the assumptions rather than hardcoding the answer.

Step 3: Remember that profit is not cash

Suppose accounts receivable increases by $20,000, inventory increases by $8,000, and accounts payable increases by $6,000. These are illustrative changes, not values automatically calculated from sales. Using the indirect method, operating cash flow is $32,250 net income plus $5,000 depreciation, less $20,000 receivables, less $8,000 inventory, plus $6,000 payables: $15,250.

Now subtract $12,000 in capital expenditures and $4,000 in scheduled debt principal repayments. Net cash changes by negative $750, leaving $49,250 from a $50,000 opening balance. We have a profitable business whose cash balance fell. That’s why I care about both numbers.

For a deeper look at customer payments, read how to forecast accounts receivable collections.

Step 4: Try the interactive financial forecast calculator

Change the assumptions below. The calculator recalculates monthly revenue, net income, operating cash flow, and ending cash. It is a simplified demonstration, not a substitute for an integrated financial model or tax advice.

Try increasing sales volume. The additional revenue improves profit under our fixed-cost assumptions. But if receivables and inventory also grow, the cash result can be less exciting. This is the point where I stop admiring the spreadsheet and start asking management questions.

☆ Bookmark this page — keep this forecast example handy.

Step 5: Extend the forecast over 12 months

Once the first month works, extend the model across the year. Reflect seasonality, price changes, planned hiring, supplier terms, tax-payment dates, and capital expenditures rather than copying January twelve times. Each month’s ending cash must become the next month’s beginning cash. A production model should also roll forward receivables, inventory, payables, debt, and fixed assets, then check that the balance sheet balances.

If you want the broader planning workflow, see the FP&A forecasting process.

Step 6: Build checks before you trust the forecast

At minimum, I want a balance-sheet check (assets minus liabilities minus equity equals zero), a cash roll-forward, a debt roll-forward, and a fixed-asset roll-forward. I also want input cells distinguished from formulas. The person opening the workbook six months from now should be able to understand it without summoning its creator.

Step 7: Use the model to make decisions

What if sales fall 10%? What if customers pay 15 days later? What if supplier prices rise 5%? A good model lets us test combinations, not just admire one attractive forecast. I separate what management wants to happen from what we realistically expect to happen. Sometimes those match. Sometimes the difference requires a meeting nobody is especially excited to attend.

What a useful financial forecast model should tell you

The goal is not the most tabs or the prettiest dashboard. It’s understanding what drives revenue, what consumes cash, which assumptions matter, and what happens when reality refuses to cooperate. I would rather have an uncomfortable conversation with management today than a more uncomfortable conversation with the bank next month.

If your business needs a forecast that connects decisions to profit and cash, Sarah can help you build one that supports real decisions.

Expand on This With Sarah →

If you already have a model but aren’t confident in it, start withhow to review a financial model before you trust it.