How to build forecast sheets in Google Sheets & Excel

If you run a sales team, agency, or e‑commerce brand, you already live inside Google Sheets and Excel. Forecast sheets are how you decide hiring plans, ad budgets, and inventory. Let’s walk through practical ways to build them — from classic manual approaches to fully automated AI-agent workflows — so you can choose the right level of automation for your team.### 1. Manual & traditional ways to build forecast sheets#### 1.1 Google Sheets with the FORECAST functionUse this when your data roughly follows a straight-line trend.**Step-by-step:**1. **Prepare your data** - Column A: independent variable (e.g., months: 1, 2, 3… or dates). - Column B: dependent variable (e.g., monthly revenue).2. **Insert the forecast formula** - In a new cell where you want the forecast (say B13), enter: `=FORECAST(A13, B2:B12, A2:A12)` - `A13` is the future x-value you’re forecasting for. - `B2:B12` is your historical y-values (e.g., revenue). - `A2:A12` is your historical x-values (e.g., months).3. **Copy the formula down** for additional future periods.4. **Visualize** - Select your historical and forecast ranges. - Insert → Chart → Line chart.Official reference: Google’s FORECAST help center article — https://support.google.com/docs/answer/3094000**Pros:** Simple, quick, native to Sheets. **Cons:** Linear regression only; weak for strong seasonality (e.g., retail spikes, holiday traffic).#### 1.2 Google Sheets with the ForecastSheets add-onWhen your data is clearly seasonal (weekly, monthly, yearly cycles), this is a big upgrade.**Step-by-step:**1. Install the add-on from the Google Workspace Marketplace: https://workspace.google.com/marketplace/app/forecastsheets/9174359020052. In your Sheet, ensure you have: - Column A: dates (daily, weekly, monthly). - Column B: metric (sales, signups, traffic).3. Extensions → **ForecastSheets** → Open. 4. In the sidebar, select: - Input data range (e.g., `A2:B2922`). - Output range for predictions. - Season length (e.g., `365` for daily data with yearly seasonality).5. Click **Submit** and let it generate your forecast column.6. Overlay the forecast on your existing chart to visually validate.**Pros:** Handles seasonality via Holt–Winters; stays inside Sheets; no coding. **Cons:** Another tool to configure; parameters may need experimentation.#### 1.3 Excel Forecast Sheet (ETS-based)Excel’s Forecast Sheet is powerful for time-based data with trends and seasonality.**Step-by-step:**1. **Organize data** - Column A: dates/times at consistent intervals (e.g., first of each month). - Column B: historical metric.2. **Select any cell** in your data range.3. Go to **Data → Forecast → Forecast Sheet**.4. Choose a **Line** or **Column** chart in the preview.5. Set **Forecast End** date (e.g., 12 months ahead).6. Click **Options** if you want to tweak: - Seasonality (let Excel auto-detect or set manually). - Confidence interval (default 95%). - Handling of missing points and duplicates. 7. Click **Create**. Excel generates: - A new sheet with historical + predicted values. - Upper and lower confidence intervals.Official guide: https://support.microsoft.com/en-us/office/create-a-forecast-in-excel-for-windows-22c500da-6da7-45e5-bfdc-60a7062329fd**Pros:** Handles seasonality; produces robust tables and charts; no formulas required. **Cons:** Still a manual operation; easy to forget to rerun; tied to desktop Excel.#### 1.4 Excel FORECAST.ETS functionFor more granular control in formulas:1. Use `FORECAST.ETS` with your timeline (dates), values, and desired target date. 2. For deeper stats (error metrics, smoothing coefficients), use `FORECAST.ETS.STAT`.Official reference: https://support.microsoft.com/en-us/office/forecasting-functions-reference-897a2fe9-6595-4680-a0b0-93e0308d5f6e**Pros:** Formula-based and flexible; good for power users. **Cons:** More complex; easy to misconfigure.---### 2. No-code automation to keep forecast sheets freshManual forecasting works until you need to redo it every week. No-code automations help you keep Sheets and Excel updated without living in them.#### 2.1 Auto-refresh inputs for Google Sheets forecastsImagine an agency owner pulling daily ad spend from multiple platforms.**Workflow idea:**- Use tools like Zapier, Make, or native connectors to push daily metrics (e.g., from Google Ads, Facebook Ads, Stripe) into a raw data tab in Google Sheets. - Your forecast tab uses `FORECAST` or ForecastSheets on this ever-growing dataset. - Schedule the automation to run every night so your forecast lines extend automatically.**Pros:** No more CSV uploads; near real-time forecasts. **Cons:** You still own the logic (ranges, formulas, charts) and must fix errors.#### 2.2 Auto-refresh inputs for Excel forecastsIf your organization relies on Excel:- Use Power Query or no-code integration tools to import data from databases, CRM, or CSV exports into an Excel data table. - Structure a macro or simple checklist so that, once data is updated, you click Data → Forecast Sheet and refresh the forecast.**Pros:** Strong for teams already standardized on Excel. **Cons:** Still requires human clicks unless paired with macros or an agent.#### 2.3 Scheduled reportingOnce forecasts are generated (in either app), set up:- Scheduled emails with PDF exports of forecast charts. - Links from dashboards (e.g., Looker Studio, Power BI) that reference the forecast ranges.This alone turns your forecast sheets from a “pull” resource (“Open the file and check”) into a “push” system (“Forecasts land in your inbox every Monday”).---### 3. Scaling forecasts with AI computer agentsThis is where Simular-style AI computer agents change the game. Instead of gluing tools together, you delegate the entire process: opening apps, updating data, running forecasts, validating outputs, and publishing results.#### 3.1 Agent-driven Google Sheets forecasting**Story:** A DTC founder wants a 90‑day rolling revenue forecast updated daily.**What the agent does:**1. Opens Google Sheets and navigates to the forecasting workbook. 2. Pulls fresh sales exports from Shopify or a data dashboard in the browser. 3. Pastes or imports new rows into the raw-data tab. 4. Triggers ForecastSheets or recalculates `FORECAST` formulas. 5. Checks that new forecast rows were added (e.g., last date matches today + 90 days). 6. Updates charts and saves a snapshot to Google Drive. 7. Posts a summary to Slack or email: “Updated 90‑day revenue forecast; median projection $X, high $Y, low $Z.”**Pros:** Zero spreadsheet babysitting; multi-step, cross-app workflow handled end-to-end. **Cons:** Requires initial setup and testing of the agent’s steps.#### 3.2 Agent-driven Excel Forecast Sheet runs**Story:** A B2B SaaS VP of Sales needs a board-ready bookings forecast every month.**What the agent does:**1. Opens Excel on desktop. 2. Downloads or syncs latest CRM export. 3. Refreshes Power Query or data connections feeding the forecast sheet. 4. Opens the Data tab and runs **Forecast Sheet** with a consistent set of options (forecast horizon, seasonality). 5. Verifies the new worksheet was created and that confidence intervals look sane (e.g., flags absurd spikes). 6. Exports the forecast chart and table to PDF and PowerPoint for the board deck.**Pros:** Production-grade reliability across many Excel steps; no more “who ran the forecast this month?” Slack messages. **Cons:** Needs Windows or macOS environment configured for the agent.#### 3.3 Agents as forecasting orchestratorsFor agencies and multi-brand operators, an AI computer agent can:1. Loop through a list of client workbooks (Sheets or Excel). 2. For each client, update data, rerun forecasts, and log key metrics into a master control sheet. 3. Trigger webhooks back into your CRM or project management tool when thresholds are hit (e.g., forecasted stockout, pipeline gap).**Pros:** True "set it and scale it" forecasting across dozens of brands or territories. **Cons:** Initial design takes time — but once built, the marginal cost of another forecast is effectively zero.