P&L Forecast Template (Free Template)

A template is only useful if it forces the right structure. Most downloadable forecast templates give you a grid of months and leave you to invent the rows, which is exactly the part that matters.

Here is the structure to build or look for, and how to populate it.

Sheet 1: Assumptions

This comes first, not last. Every driver in one place, each with a source or a note explaining it.

• Volume drivers: jobs, hours, units, customers, by month
• Price or average value per unit
• Cost of sales as a rate or per-unit cost
• Headcount by month, with fully loaded cost
• Fixed cost lines, from contracts you hold
• Growth or seasonality factors, with reasoning

Every figure on the forecast sheet should reference this sheet rather than being typed directly. That is what makes the model answer “what if” questions instead of requiring a rebuild.

Sheet 2: The forecast

• Months across, twelve columns minimum
• Revenue by category, calculated from volume and price
• Cost of sales, calculated from the revenue driver
• Gross profit and gross margin percentage
• Operating expenses, fixed and variable separated
• Operating profit
• A cumulative column, so year-to-date position is visible

Sheet 3: Actual versus forecast

Use the same row structure as the forecast, with columns for forecast, actual, and variance. Update it after each closed period so the model supports review as well as planning.

Map the row structure to the chart of accounts. When forecast categories and ledger categories differ, maintain a documented mapping so monthly variance analysis does not depend on repeated manual interpretation.

Sheet 4: Scenarios

Create separate downside and upside assumption sets and let the forecast reference the selected case. Preserve the base case and label which assumptions change in each scenario.

How to use it

• Populate assumptions first, with sources
• Check that the forecast calculates rather than contains typed numbers
• Sanity check gross margin against your recent actual margin
• Confirm cost growth accompanies revenue growth
• Update actuals monthly and write down why each variance happened

Copy-ready row structure

Use revenue by driver or product, direct cost by the same operating logic, gross profit, gross margin percentage, payroll by team, contractors, occupancy, software, marketing, professional fees, insurance, travel, other operating costs, depreciation, interest, taxes if included, and the selected profit subtotal. Match row names to the general ledger.

Use formulas tied to drivers

Examples include volume multiplied by price, headcount multiplied by fully loaded cost, units multiplied by direct cost, opening customers plus additions minus churn, and contract cost allocated by period. Keep drivers on the assumptions sheet and reference them. Avoid typing totals directly into forecast cells.

Add control checks

Include a chart-of-accounts mapping check, balance between detailed and summary rows, formula-consistency check across months, missing-assumption flag, sign check, gross-margin review, and a clear distinction between hardcoded inputs and formulas. Lock formula areas when others will edit the file.

Reconcile actuals before variance analysis

Import or map closed-period actuals only after the ledger is reconciled. Compare forecast and actual using both amount and percentage where meaningful. Explain variance by price, volume, mix, timing, headcount, classification, or one-time event, then assign an owner and next action.

Connect profit to cash

Create a separate cash forecast that starts with the P&L but adjusts for collection timing, supplier payments, payroll dates, taxes, debt, asset purchases, financing, owner activity, and opening cash. Do not treat forecast profit as forecast cash.

Monthly operating cycle

• Close and reconcile the books
• Load actuals without overwriting the approved forecast
• Explain material variances
• Refresh only future assumptions
• Compare base, downside, and upside cases
• Update the cash bridge and decision log
• Preserve the prior version and approval date

Formula examples to include

Service revenue can be modeled as billable capacity multiplied by utilization and average realized rate. Subscription revenue can use opening customers plus additions, expansion, contraction, and churn. Product revenue can use units multiplied by average price, with direct cost tied to units or product mix.

Use separate rows for assumptions and output. Preserve units, timing, and signs, and avoid circular references. If a driver cannot be measured reliably, label the estimate, record its source and owner, and test the effect under more than one scenario.

Version and approval log

Record forecast name, creation date, covered period, source actuals, scenario, preparer, reviewer, approval date, material assumption changes, and file location. Do not overwrite an approved version. A simple log makes later variance analysis traceable.

Quality review before use

Change one material assumption and confirm all expected outputs move. Test zero, delayed, and downside cases. Check subtotals, margins, signs, month boundaries, annual totals, scenario selection, and the cash bridge. Have a second person review formulas and assumptions for important decisions.