Forecast Subscription Revenue & Cash in Google Sheets

Photo by Aidan Tottori on Unsplash

What this guide covers

This article teaches you to build a subscription revenue and cash-flow forecast calculator in Google Sheets using the GSheetPress GS2Web add-on. The finished calculator accepts monthly assumptions from a web form, computes monthly recurring revenue, estimates customer acquisition costs and collection timing, and flags a potential cash shortfall. It is designed for SaaS founders, finance leads, and operators who want a repeatable model without a finance background.

What is excluded: This model uses simplified, single-tier monthly assumptions. It does not handle multi-currency billing, annual prepayment amortisation, tax, contract length variability, or enterprise deal complexity. All assumption values need local verification before any business decision is made.

Key terms explained

• MRR (Monthly Recurring Revenue): The predictable revenue a subscription business expects to collect every month from active subscribers.
• Churn: The percentage of existing customers who cancel their subscription each month. A 5% monthly churn rate means 5 out of every 100 customers leave.
• Churn rate: Expressed as a decimal in formulas (5% = 0.05). You enter a whole number such as 5, and the formula divides by 100.
• CAC (Customer Acquisition Cost): The total marketing and sales spend needed to win one new paying customer.
• Opening customers: The number of active subscribers at the very start of the forecast month.
• New subscriptions: Customers who sign up for the first time during the month.
• Cancellations: Customers who leave during the month, calculated from the churn rate applied to opening customers.
• Upgrades: Existing customers who move to a higher-priced plan, increasing the average revenue per user.
• ARPU (Average Revenue Per User): Total MRR divided by the number of active customers. Upgrades push this number up.
• Cash shortfall: The point where cash outflows exceed the cash collected, leaving insufficient funds to cover costs.
• Collection timing: The lag between issuing an invoice and receiving payment. A 30-day payment term means revenue earned in Month 1 arrives as cash in Month 2.
• Operating expenses (OpEx): Fixed and variable costs such as hosting, salaries, and support tools that recur each month.
• Net cash position: Opening cash balance plus collections minus all expenses in the period.
• Calculator entitlement: The GSheetPress account permission that unlocks the Calculator feature inside the GS2Web add-on.

Prerequisites

• A Google account with access to Google Sheets.
• A GSheetPress account with a plan that includes Calculator entitlement. Check your plan at gsheetpress.com/pricing or sign up at gsheetpress.com/admin/signup.
• The GS2Web SaaS add-on installed from the Google Workspace Marketplace. After installation it appears under Extensions in the Sheets menu.
• A dedicated sample workbook created only for this calculator. Do not use a workbook that contains private financial data, because publishing requires the sheet to be shared publicly.

Monthly model assumptions

Before building the calculator, decide what one forecast scenario represents. The inputs below correspond to a single forecast month. Running the calculator repeatedly with updated opening-customer numbers lets you chain months together manually.

Collection lag affects when cash arrives. If your lag is 30 days, MRR earned this month is not available as cash until next month. The calculator approximates this: a lag of 0–14 days treats revenue as collected this month; 15 or more days defers it fully to the next period, reducing the current-month net cash position. Verify the threshold that suits your billing cycle before using the results in any financial decision.

Build the subscription forecast calculator

Step 1 — Create a dedicated sample workbook

1. Open Google Sheets and create a new blank spreadsheet.
2. Name it something clear, for example Subscription Forecast Calculator – Sample.
3. Leave all cells empty. The add-on generates the template structure for you.

Step 2 — Open the GS2Web Calculator wizard

1. Click Extensions in the top menu.
2. Hover over GS2Web SaaS (the installed add-on name).
3. Click Calculator, then New Calculator.
4. In the dialog that appears, enter a name for your calculator, for example Subscription Revenue Forecast.
5. Set the number of inputs to 9. This count excludes the Calculate button; the wizard places the button automatically.
6. When asked whether to include graphs, choose according to your preference. This guide assumes No graphs to keep the layout simple.
7. Click the confirm button to generate the template. The add-on writes headers in row 1 across columns A to H: Labels, Input Type, Required, Data, Default, ToolTips, Inputs, Rules.

Step 3 — Understand the generated layout

With 9 inputs the worksheet is structured as follows. Do not insert or delete rows in this area; the add-on relies on the exact positions.

About columns E and G: Column E holds the default values displayed on the web form before a visitor submits anything. Column G holds the values submitted at runtime; web submissions overwrite G automatically. To test your formulas locally before publishing, copy E2:E10 and paste into G2:G10 using Paste special → Values only. Never write result formulas into column G.

Step 4 — Configure each input row

Fill columns A, B, C, D, E, F, and H for rows 2 through 10. Column G must stay blank during setup. Work through each row in order.

Row 2 — Opening customers

• A2 — Label: Opening Customers
• B2 — Input Type: Number
• C2 — Required: Check the required checkbox (tick it on)
• D2 — Data: Leave blank
• E2 — Default: 200
• F2 — ToolTips: Number of active subscribers at the start of this month
• H2 — Rules: Leave blank

Row 3 — New subscriptions

• A3 — Label: New Subscriptions
• B3 — Input Type: Number
• C3 — Required: Check the required checkbox
• D3 — Data: Leave blank
• E3 — Default: 30
• F3 — ToolTips: Expected new sign-ups this month
• H3 — Rules: Leave blank

Row 4 — Monthly churn rate

• A4 — Label: Monthly Churn Rate (%)
• B4 — Input Type: Number
• C4 — Required: Check the required checkbox
• D4 — Data: Leave blank
• E4 — Default: 5
• F4 — ToolTips: Percentage of customers who cancel each month. Enter 5 for 5%.
• H4 — Rules: Leave blank

Row 5 — Upgrade revenue added

• A5 — Label: Upgrade Revenue Added
• B5 — Input Type: Number
• C5 — Required: Check the required checkbox
• D5 — Data: Leave blank
• E5 — Default: 500
• F5 — ToolTips: Extra MRR this month from customers upgrading to higher-priced plans
• H5 — Rules: Leave blank

Row 6 — ARPU

• A6 — Label: Average Revenue Per User (ARPU)
• B6 — Input Type: Number
• C6 — Required: Check the required checkbox
• D6 — Data: Leave blank
• E6 — Default: 49
• F6 — ToolTips: Average monthly subscription fee per active customer
• H6 — Rules: Leave blank

Row 7 — CAC

• A7 — Label: Customer Acquisition Cost (CAC)
• B7 — Input Type: Number
• C7 — Required: Check the required checkbox
• D7 — Data: Leave blank
• E7 — Default: 120
• F7 — ToolTips: Total cost to acquire one new customer, including marketing and sales spend
• H7 — Rules: Leave blank

Row 8 — Monthly operating expenses

• A8 — Label: Monthly Operating Expenses
• B8 — Input Type: Number
• C8 — Required: Check the required checkbox
• D8 — Data: Leave blank
• E8 — Default: 4000
• F8 — ToolTips: Fixed monthly costs such as hosting, salaries, and software tools
• H8 — Rules: Leave blank

Row 9 — Opening cash balance

• A9 — Label: Opening Cash Balance
• B9 — Input Type: Number
• C9 — Required: Check the required checkbox
• D9 — Data: Leave blank
• E9 — Default: 15000
• F9 — ToolTips: Cash available at the very start of the month
• H9 — Rules: Leave blank

Row 10 — Collection lag

• A10 — Label: Collection Lag (Days)
• B10 — Input Type: Number
• C10 — Required: Check the required checkbox
• D10 — Data: Leave blank
• E10 — Default: 30
• F10 — ToolTips: Days between invoicing a customer and receiving cash. Enter 0 if customers pay immediately.
• H10 — Rules: Leave blank

Row 11 — The Calculate button (generated)

Row 11 is written by the wizard. You will see values similar to the following; leave them exactly as generated:

• A11: Calculate (the button label — do not change)
• B11: Button (the input type — do not change)
• D11: main (the form target — do not change)
• E11: Reset or blank as generated — do not change

Do not delete or move row 11. It is required for the web form to submit values into column G.

Step 5 — Write helper formulas in column I

Column I is not shown to web visitors. It holds intermediate calculations that keep the result formulas in column A of the results area concise and easy to check. Click each cell, type the formula exactly as shown beginning with =, then press Enter.

I2 — Cancellations this month (churn rate applied to opening customers, rounded to a whole person):

=ROUND(G2*(G4/100),0)

I3 — Closing customers (opening plus new sign-ups minus cancellations):

=G2+G3-I2

I4 — Monthly Recurring Revenue (closing customers multiplied by ARPU, plus upgrade revenue):

=(I3*G6)+G5

I5 — Total acquisition cost (new subscriptions multiplied by CAC):

=G3*G7

I6 — Total cash outflows (acquisition cost plus operating expenses):

=I5+G8

I7 — Cash collected this month (if collection lag is under 15 days, MRR is treated as collected this month; otherwise zero is collected this month because it arrives next period):

=IF(G10<15,I4,0)

I8 — Net cash position (opening cash plus collections minus outflows):

=G9+I7-I6

I9 — Shortfall flag text (returns a plain-English warning when net cash is negative):

=IF(I8<0,"WARNING: Cash shortfall — outflows exceed cash collected this month","Cash position is positive")

Using IF with an explicit message instead of a blanket IFERROR ensures you can see exactly what is happening rather than silently hiding a problem.

Step 6 — Add result labels and display formulas

Result labels go in column A starting at row 14 (after the generated separator at row 12 and heading row at row 13). The corresponding display formula goes in column B of the same row. Click each cell and enter the value shown.

Worked example with the default inputs

Copy the values in E2:E10 and paste them into G2:G10 using Paste special → Values only. The defaults are: Opening customers 200, New subscriptions 30, Churn rate 5%, Upgrade revenue 500, ARPU 49, CAC 120, Operating expenses 4000, Opening cash 15000, Collection lag 30 days.

Worked calculations for transparency:

• Cancellations: ROUND(200 × (5 / 100), 0) = ROUND(10, 0) = 10
• Closing customers: 200 + 30 − 10 = 220
• MRR: (220 × 49) + 500 = 10,780 + 500 = 11,280
• Total acquisition cost: 30 × 120 = 3,600
• Total outflows: 3,600 + 4,000 = 7,600
• Cash collected: collection lag is 30 days (≥ 15), so 0 is collected this month
• Net cash: 15,000 + 0 − 7,600 = 7,400 (positive, but the alert fires because MRR was not collected)

The alert fires because cash collected this month is zero — not because the business is losing money. The 11,280 in MRR will arrive next month. This is the key insight the collection lag input surfaces: a growing SaaS business can be profitable on paper but temporarily short of cash due to invoice timing.

Alternative scenario — immediate payment

Change G10 to 0 (collection lag 0 days) while keeping all other defaults. Expected results:

Net cash: 15,000 + 11,280 − 7,600 = 18,680. The alert clears because collections now exceed outflows.

Understanding the outputs

The model intentionally uses a binary collection lag rule (under 15 days = collected; 15 or more = deferred) to keep the formula transparent. Real billing may have partial collections or payment plans. Adapt the IF threshold in I7 if your average payment terms differ. For further reading on subscription metrics, the U.S. Small Business Administration's finance management guidance provides a useful grounding in cash-flow principles applicable to any recurring-revenue business.

You can also explore how GSheetPress handles more complex interactive outputs on the calculator details page and review how data tables can complement your forecast on the table details page.

Frequently asked questions

Can I run this model for multiple months at once?

This single-month calculator is designed to be run once per forecast period. To model multiple months, record the Closing Customers result from month one, enter it as the Opening Customers for month two, and run the calculator again. A full multi-month rolling model would require additional rows or a separate linked sheet, which is outside the scope of this single-tab calculator.

Why does the cash shortfall alert fire even when MRR is high?

The alert compares cash actually collected this month against cash outflows this month. When collection lag is 15 days or more, the formula in I7 returns zero for cash collected — meaning MRR earned this month is not treated as received until next period. High MRR does not prevent a short-term cash shortfall if customers have not yet paid. Reducing collection lag, requiring upfront payment, or maintaining a larger opening cash buffer all reduce this risk.

How do I account for customers who downgrade instead of upgrading?

Enter a negative value in the Upgrade Revenue Added field. For example, if net MRR from plan changes is a loss of 200, enter −200. The formula in I4 adds this value to closing-customer MRR, effectively reducing it. The current model does not distinguish upgrades from downgrades as separate inputs; they are treated as a single net expansion or contraction figure.

Troubleshooting

Results show zero or a blank even after entering defaults

The most common cause is that G2:G10 are still empty. The result formulas reference column G (runtime values), not column E (defaults). Copy E2:E10, click G2, and paste using Paste special → Values only. Do not use a regular paste, which copies the cell format and may cause type mismatches.

The Calculate button does not appear on the published form

Check that row 11 is intact and that B11 contains Button and D11 contains main exactly as generated. If you accidentally deleted or edited this row, use Extensions → GS2Web SaaS → Calculator → New Calculator on a fresh sheet and recreate the inputs. Do not attempt to type a button row manually.

The shortfall alert always reads WARNING even when I expect a positive position

Verify the value in G10 (Collection Lag). If it is 15 or greater, cash collected returns zero regardless of how large MRR is. Either reduce G10 below 15 to test the immediate-payment scenario, or accept that with a 30-day lag the alert will correctly fire each month until you model next-month receipt separately.

Closing customers is a decimal number

The ROUND function in I2 prevents fractional cancellations, so I3 should always be a whole number. If you see a decimal in B15, check that I2 contains the ROUND formula and not a raw multiplication. Also confirm that G2 and G4 contain numbers and not text strings — text imported from another sheet can look like a number but behave differently in arithmetic.

The add-on does not show Calculator in its menu

Calculator is a feature that requires a specific account entitlement. If the menu item is missing or greyed out, your current plan may not include it. Visit the pricing page to review plan features, or contact GSheetPress support for account assistance.

Publish and test your web calculator

Step 1 — Share the sample workbook

Publishing requires the spreadsheet to be accessible to anyone who holds its link. Use only your dedicated sample workbook for this step — not a file that contains any private or sensitive data.

1. Click Share in the top-right corner of Google Sheets.
2. Under General access, open the dropdown and choose Anyone with the link.
3. Set the role to Editor.
4. Click Done.

Important: Setting access to Editor means anyone who obtains the spreadsheet URL can edit the sheet directly. This is why a dedicated sample workbook is essential. Never apply this setting to a workbook containing real financial data or private customer information.

Step 2 — Select the calculator tab

1. Click the sheet tab at the bottom of the workbook that contains your calculator layout (the tab where you built the inputs and result formulas).
2. Make sure this tab is active (selected) before proceeding.

Step 3 — Publish the calculator

1. Click Extensions in the top menu.
2. Hover over GS2Web SaaS.
3. Click Calculator, then Publish Calculator.
4. Wait for the confirmation message. The add-on processes the sheet and generates a public calculator link.

Step 4 — Retrieve and verify the public link

1. After confirmation, open the Calculator Link sheet that the add-on creates or updates in your workbook.
2. Find the row that matches your Sheet Name (the tab you published).
3. Open the link in column C, which is the public Link. Do not use column B (the Edit Link), which opens the sheet editor rather than the public calculator form.
4. If the list appears out of date, click Get Links inside the add-on to refresh it.

Step 5 — Test with the worked example

1. On the public calculator page, enter the default worked-example values: Opening customers 200, New subscriptions 30, Churn rate 5, Upgrade revenue 500, ARPU 49, CAC 120, Operating expenses 4000, Opening cash 15000, Collection lag 30.
2. Click the Calculate button on the form.
3. Compare the displayed results against the expected values in the worked example table above: Closing customers 220, MRR 11,280, Net cash position 7,400, shortfall alert active.
4. Then change Collection lag to 0 and recalculate. Confirm that Net cash position becomes 18,680 and the shortfall alert clears.

Step 6 — Republish and embed (optional)

If you update input rows or result formulas after the first publication, return to Extensions → GS2Web SaaS → Calculator → Publish Calculator on the same tab. Republishing the same existing calculator retains its original public link; you do not need to update any website embeds. To embed the calculator in a website, copy the iframe code from column D of the Calculator Link sheet and paste it into an HTML block on your page. For details on embedding options, see the GSheetPress shop details page.