Profit and Loss Statement Template in Google Sheets

Five tabs, one directional data flow. Every number in the model traces back to a single source - GL_Raw for actuals, Budget for plan, Assumptions for rates and drivers. Nothing gets hard-coded in the P&L itself.

The Assumptions tab is the only place in the model where humans type numbers. Everything else calculates.

GL_Raw is your actuals source. It gets overwritten every close cycle. The P&L reads from it; nothing writes back to it.

Here's where the model connects. Each P&L line pulls its monthly actuals from GL_Raw using SUMIFS referencing across tabs. According to Google's SUMIFS documentation (last updated 2025), the function accepts up to 127 criteria range/criteria pairs - more than enough for account code + department + month filtering.

Revenue for January 2026 in cell C5 of the P&L tab:

=SUMIFS(
  GL_Raw!$E:$E,
  GL_Raw!$B:$B, 'P&L'!$A5,
  GL_Raw!$D:$D, 'P&L'!C$2
)

Column A on the P&L holds account codes. Row 2 holds period-end dates (=EOMONTH("2026-01-01",0) through December). GL_Raw column E holds amounts, column B holds account codes, column D holds the EOMONTH helper from Step 3.

For multi-department aggregations - total COGS across manufacturing and logistics accounts that share a "4xxx" prefix:

=SUMIFS('GL_Raw'!$E:$E, 'GL_Raw'!$B:$B, "4*",
  'GL_Raw'!$D:$D, 'P&L'!C$2)
+ SUMIFS('GL_Raw'!$E:$E, 'GL_Raw'!$B:$B, "5*",
  'GL_Raw'!$D:$D, 'P&L'!C$2)

Lock column A with $A5 and row 2 with C$2. Copy across all 12 months once January validates.

With actuals flowing from GL_Raw, the income statement calculates top-to-bottom. Every subtotal is a formula referencing the lines above - never a re-SUM from scratch that could drift out of sync.

Standard structure for a $15-25M revenue company:

Revenue                   =SUMIFS(GL_Raw actuals, revenue accounts 1xxx)
  (less) COGS             =SUMIFS(GL_Raw actuals, COGS accounts 4xxx-5xxx)
Gross Profit              =Revenue - COGS
  Gross Margin %          =Gross Profit / Revenue           [target: 38.5%]

  (less) S&M              =SUMIFS(...)
  (less) R&D              =SUMIFS(...)
  (less) G&A              =SUMIFS(...)
EBITDA                    =Gross Profit - OpEx
  EBITDA Margin %         =EBITDA / Revenue

  (less) D&A              =Assumptions!DA_Annual / 12       [$880K / 12]
EBIT                      =EBITDA - D&A

  (less) Interest Expense =Debt * SOFR_Rate / 12
EBT                       =EBIT - Interest Expense

  (less) Taxes            =MAX(EBT,0) * EffectiveTaxRate    [25%]
Net Income                =EBT - Taxes

Conditional format the margin rows: red if gross margin drops below 35%, yellow between 35-38%, green above. It takes 2 minutes and saves you from scanning a 12-column view looking for the bad month.

The Variance tab is where this model earns its keep in the quarterly board pack. Budget sits in its own tab; the Variance tab compares it to P&L actuals line by line, automatically.

In the Variance tab, for January: column C (actuals), column D (budget), column E (dollar delta), column F (percent delta):

=P&L!C5 - Budget!C5           [dollar variance, negative = miss]
=(P&L!C5 / Budget!C5) - 1     [percent variance]

For a $18.7M revenue base with 12% budgeted growth, results might look like this:

A -4.7% revenue variance is worth discussing. A -10.9% EBITDA variance on $2.3M EBITDA ($251K miss) is the one that generates the follow-up email. Conditional format the percent column red below -5%, yellow between -5% and -2%. The -$50K/-5% thresholds are calibrated for a $2-5M EBITDA business; scale the absolute dollar floor proportionally.

The bridge converts your P&L into deal math: EBITDA multiple, enterprise value, implied equity value. If this model feeds a bank syndicate DCF or an LP update, this section is what gets screenshotted.

In a Returns section (bottom of the P&L tab or a separate tab):

LTM EBITDA           =SUM of the EBITDA row across 12 months     [$2.3M]
EV / EBITDA Multiple =Assumptions!EV_Multiple                    [14.2x]
Enterprise Value     =LTM_EBITDA * EV_Multiple                   [$32.6M]
  (less) Net Debt    =TotalDebt - CashAndEquivalents
Equity Value         =EnterpriseValue - NetDebt

Wrap the multiple in a two-variable data table for sensitivity: row input as EV/EBITDA multiple (10x to 18x), column input as EBITDA margin (33% to 44%). A 9-column, 6-row table gives you 54 equity value scenarios without touching a model formula. On a $2.3M EBITDA base, the spread between 10x and 18x is a $18.4M swing in enterprise value - that range belongs in the board deck, not buried in a scenario toggle.