Build a 5-Year Projection in Google Sheets

Download the editable projection workbook (.xlsx). It contains the two tabs, formulas, three scenarios, reconciliation checks, and illustrative values used in this guide. No sign-up is required.

To use it in Google Sheets, open a new spreadsheet, choose File > Import > Upload, select the downloaded workbook, and import it as a new spreadsheet. Keep the original download as a reference copy. Google documents the supported import options in its Excel-to-Sheets guide.

Start with the Base case. Confirm FY2031 EBITDA of $202,371.06, ending cash of $838,262.18, and all 15 check cells showing OK before changing the editable input cells. Then select Optimistic and Conservative in Assumptions!B2 and compare the scenario totals shown below. If imported formulas or dates differ, stop and inspect them before using the output.

This is a compact operating projection, not an investor-ready three-statement forecast. Its cash row deliberately equals EBITDA before excluded cash items; do not use that row as a real cash-flow forecast until those components are modeled.

A five-year financial projection template should separate inputs from calculations and clearly define its forecast periods. Whether you use an existing template or build one, check that it has:

• A dedicated assumptions area
• Actual date values for each forecast year
• Revenue, COGS, gross profit, operating expenses, and EBITDA
• A cash roll-forward
• Base, optimistic, and conservative scenarios
• Formulas that can be inspected
• Checks tying subtotals and cash movements together

Don’t select a template solely because it has polished formatting. First, determine whether its formulas, units, period definitions, and scenario mechanics are visible.

For this example, create exactly 2 tabs:

1. Assumptions
2. Projection

On the Assumptions tab, enter these labels and illustrative values:

Next, enter the scenario table in A13:D16:

Revenue and OpEx growth can legitimately exceed 100%; 150% growth multiplies the prior amount by 2.5. This worked template deliberately limits growth to -100% through 100% as an illustrative input policy, not as a mathematical bound on growth. If your forecast needs a higher growth rate, change the upper limit consistently in data validation, the selected-rate formulas in Assumptions!B8:B9, and the Projection growth guards, then rerun the boundary and reconciliation checks. The 0% through 100% gross-margin limit is a different constraint and remains in place.

The scenario names must use exactly the capitalization shown.

Apply these data-validation rules through Data > Data validation > Add rule:

• B2: dropdown containing Base, Optimistic, and Conservative; reject invalid entries.
• B4:B6: number greater than or equal to 0.
• B14:C16: number between -100% and 100%.
• D14:D16: number between 0% and 100%.

Format B4:B6 as USD and B14:D16 as percentages. Also format Assumptions!B8:B10 as percentages; these calculated cells do not automatically inherit the scenario-table format. The formulas below also check data types and permitted ranges, keeping invalid assumptions visible when the source cells don’t contain valid numbers.

The selected-rate formulas must validate both the scenario name and its corresponding rate cell. This prevents a blank scenario-rate cell from being treated as 0%.

Enter this formula in Assumptions!B8:

=IF(AND(SUMPRODUCT(--EXACT($B$2,$A$14:$A$16))=1,SUMPRODUCT(--EXACT($B$2,$A$14:$A$16),--ISNUMBER($B$14:$B$16),--($B$14:$B$16>=-100%),--($B$14:$B$16<=100%))=1),INDEX($B$14:$B$16,MATCH(TRUE,EXACT($B$2,$A$14:$A$16),0)),NA())

With B2 set to Base, the expected result is 10%.

Enter this formula in Assumptions!B9:

=IF(AND(SUMPRODUCT(--EXACT($B$2,$A$14:$A$16))=1,SUMPRODUCT(--EXACT($B$2,$A$14:$A$16),--ISNUMBER($C$14:$C$16),--($C$14:$C$16>=-100%),--($C$14:$C$16<=100%))=1),INDEX($C$14:$C$16,MATCH(TRUE,EXACT($B$2,$A$14:$A$16),0)),NA())

The expected Base result is 5%.

Enter this formula in Assumptions!B10:

=IF(AND(SUMPRODUCT(--EXACT($B$2,$A$14:$A$16))=1,SUMPRODUCT(--EXACT($B$2,$A$14:$A$16),--ISNUMBER($D$14:$D$16),--($D$14:$D$16>=0),--($D$14:$D$16<=100%))=1),INDEX($D$14:$D$16,MATCH(TRUE,EXACT($B$2,$A$14:$A$16),0)),NA())

The expected Base result is 60%.

Together, these formulas require exactly one case-sensitive scenario match and a numeric rate within the applicable bounds. A blank selection, lowercase base, unknown scenario, duplicate exact scenario name, blank rate, text rate, or out-of-range rate returns #N/A.

On the Projection tab, enter:

Then enter actual dates across row 1:

B1: =DATE(2027,12,31)
C1: =DATE(2028,12,31)
D1: =DATE(2029,12,31)
E1: =DATE(2030,12,31)
F1: =DATE(2031,12,31)

Each column covers the inclusive calendar year from January 1 through December 31. Apply the custom number format "FY"yyyy so the cells display as FY2027 through FY2031 while preserving the underlying date values.

Before moving on, confirm that there are no skipped or duplicate years and that opening cash in Assumptions!B6 is measured at December 31, 2026.

Start by entering the first-year revenue formula in Projection!B3:

=IF(AND(ISNUMBER(Assumptions!$B$4),Assumptions!$B$4>=0,ISNUMBER(Assumptions!$B$8),Assumptions!$B$8>=-100%,Assumptions!$B$8<=100%),Assumptions!$B$4*(1+Assumptions!$B$8),NA())

With FY2026 revenue of 500000 and Base growth of 10%, the expected result is:

500000 × 1.10 = 550000

Enter this formula in Projection!C3 and copy it through F3:

=IF(AND(ISNUMBER(B3),B3>=0,ISNUMBER(Assumptions!$B$8),Assumptions!$B$8>=-100%,Assumptions!$B$8<=100%),B3*(1+Assumptions!$B$8),NA())

Next, enter the COGS formula in Projection!B4 and copy it through F4:

=IF(AND(ISNUMBER(B3),B3>=0,ISNUMBER(Assumptions!$B$10),Assumptions!$B$10>=0,Assumptions!$B$10<=100%),B3*(1-Assumptions!$B$10),NA())

For FY2027, expected COGS is:

550000 × (1 - 60%) = 220000

Enter the gross-profit formula in Projection!B5 and copy it through F5:

=IF(AND(ISNUMBER(B3),ISNUMBER(B4)),B3-B4,NA())

Expected FY2027 gross profit is:

550000 - 220000 = 330000

Now enter the first-year operating-expense formula in Projection!B6:

=IF(AND(ISNUMBER(Assumptions!$B$5),Assumptions!$B$5>=0,ISNUMBER(Assumptions!$B$9),Assumptions!$B$9>=-100%,Assumptions!$B$9<=100%),Assumptions!$B$5*(1+Assumptions!$B$9),NA())

Expected FY2027 operating expenses are:

220000 × 1.05 = 231000

Enter this formula in Projection!C6 and copy it through F6:

=IF(AND(ISNUMBER(B6),B6>=0,ISNUMBER(Assumptions!$B$9),Assumptions!$B$9>=-100%,Assumptions!$B$9<=100%),B6*(1+Assumptions!$B$9),NA())

Finally, enter the EBITDA formula in Projection!B7 and copy it through F7:

=IF(AND(ISNUMBER(B5),ISNUMBER(B6)),B5-B6,NA())

Expected FY2027 EBITDA is:

330000 - 231000 = 99000

Negative EBITDA is allowed and should remain visible. It’s a forecast result, not an invalid input.

In this limited example, EBITDA equals net cash change. Enter this formula in Projection!B8 and copy it through F8:

=IF(ISNUMBER(B7),B7,NA())

For the first year, enter the ending-cash formula in Projection!B9:

=IF(AND(ISNUMBER(Assumptions!$B$6),Assumptions!$B$6>=0,ISNUMBER(B8)),Assumptions!$B$6+B8,NA())

Expected FY2027 ending cash is:

100000 + 99000 = 199000

Enter this formula in Projection!C9 and copy it through F9:

=IF(AND(ISNUMBER(B9),ISNUMBER(C8)),B9+C8,NA())

For FY2028, the expected calculation is:

199000 + 120450 = 319450

A negative cash change can produce negative ending cash. Keep that result visible for review instead of converting it to zero.

Format B3:F9 as USD with 2 decimal places. The formulas retain their underlying precision even when fewer decimals appear.

With Assumptions!B2 set to Base, the exact expected values before display rounding are:

With 2-decimal formatting, FY2031 EBITDA should display as $202,371.06, and ending cash should display as $838,262.18.

Next, change Assumptions!B2 to Optimistic. The expected FY2027 results are:

Then change B2 to Conservative. The expected FY2027 results are:

Only the selected rates and their dependent projection cells should change. The original scenario-table inputs should remain fixed.

Add formula-based checks so broken identities are visible in the workbook.

Enter this revenue check in Projection!B11 and copy it through F11:

=IF(AND(ISNUMBER(B3),ISNUMBER(B4),ISNUMBER(B5)),IF(ROUND(B3-B4-B5,2)=0,"OK","CHECK"),"CHECK")

Enter this EBITDA check in Projection!B12 and copy it through F12:

=IF(AND(ISNUMBER(B5),ISNUMBER(B6),ISNUMBER(B7)),IF(ROUND(B7-(B5-B6),2)=0,"OK","CHECK"),"CHECK")

For FY2027, enter the cash check in Projection!B13:

=IF(AND(ISNUMBER(Assumptions!$B$6),ISNUMBER(B8),ISNUMBER(B9)),IF(ROUND(B9-(Assumptions!$B$6+B8),2)=0,"OK","CHECK"),"CHECK")

Enter this formula in Projection!C13 and copy it through F13:

=IF(AND(ISNUMBER(B9),ISNUMBER(C8),ISNUMBER(C9)),IF(ROUND(C9-(B9+C8),2)=0,"OK","CHECK"),"CHECK")

These checks test the following identities to the nearest cent:

Revenue = COGS + Gross profit
EBITDA = Gross profit - Operating expenses
Ending cash = prior cash + Net cash change

For FY2027, prior cash is Assumptions!B6. From FY2028 onward, it’s the preceding year’s ending cash. With the supplied Base, Optimistic, or Conservative assumptions, every cell in Projection!B11:F13 should display OK.

The downloadable Excel workbook was recalculated in LibreOffice 26.8 across 28 scenario, invalid-input, and boundary cases. Base, Optimistic, and Conservative produced the stated totals, with all 15 checks showing OK. Invalid assumptions produced visible #N/A errors and CHECK controls. These tests did not include a Google Sheets import. After importing or customizing, rerun the controls below in your spreadsheet application; a successful desktop recalculation does not establish that an imported copy behaves identically:

These controls test arithmetic consistency. They don’t establish that the assumptions are commercially reasonable or that the simplified cash treatment is suitable for a real planning decision.

If you add more opening entries while customizing the workbook, reconcile them manually first. The formulas don’t detect or exclude duplicate opening balances automatically.

Google Sheets locales that use semicolons as formula separators may require replacing each comma in the formulas with a semicolon. Check Google’s locale and calculation settings and function reference when adapting the formulas.

Replace the illustrative inputs only after the Base case matches the expected results and every reconciliation cell displays OK.

For a specific business or finance use case:

• Document whether every amount is USD, another currency, units, or a percentage.
• State whether periods are calendar or fiscal years.
• Replace generic growth rates with business-specific drivers only after defining their units.
• Keep scenario inputs separate from projection formulas.
• Preserve the 3 reconciliation checks.
• Extend net cash change only when you’re ready to model the excluded cash-flow components explicitly.
• Retest Base, Optimistic, and Conservative after changing the structure.

If you start from an existing template, map its cells to the same input, calculation, and control categories before relying on its outputs.

ModelMonkey is an AI assistant for Google Sheets and Excel. In Google Sheets, it can use the active spreadsheet, sheet, and selected-range context to read ranges, inspect formula errors, organize content, assist with formulas, analyze outputs, and create or adjust charts.

For this workbook, select Projection!B3:F13 and ask ModelMonkey:

Inspect this five-year projection for formula errors. Check whether the Revenue, EBITDA, and Cash control rows agree with their underlying formulas in every year. List any discrepancies without changing cells.

Review its findings against the expected results in this guide. AI assistance doesn’t replace checking the workbook’s formulas or business assumptions.

To present the forecast, create a line chart using:

• X-axis: Projection!B1:F1
• Revenue series: Projection!B3:F3
• EBITDA series: Projection!B7:F7
• Ending-cash series: Projection!B9:F9

You can ask ModelMonkey to help create or adjust this chart. When its workflow presents proposed spreadsheet changes for review, inspect them before proceeding. For more guidance on checking AI-generated formulas, see Google Sheets AI Formula Tools for FP&A.