An inventory purchasing budget translates your sales forecast and stock targets into a week-by-week or month-by-month schedule of how much stock to buy and what it will cost. You build it in Google Sheets by combining opening stock, expected sales, desired closing stock, and unit costs, then layer in supplier payment terms to convert quantities into a cash outflow plan. This guide walks you through every formula and shows you how to publish the model as an interactive web calculator using GSheetPress.
Forecast purchases using the formula: Purchases = Sales + Closing Stock – Opening Stock • Multiply purchase quantities by unit cost and stagger payments across periods using supplier payment terms • Flag shortages (negative closing stock) and excess inventory automatically with conditional formulas • Build a live web calculator so your team can run scenarios without touching the spreadsheet • Publish the calculator publicly and test it before sharing the link
Build an Inventory Purchasing Budget in Google Sheets

Photo by Rubaitul Azad on Unsplash

Key terms explained

  • Opening stock: The quantity of a product you hold at the start of a period (week or month).
  • Closing stock: The quantity remaining at the end of a period; it becomes the next period's opening stock.
  • Target closing stock: The minimum quantity you want to hold at period end, often expressed as a number of days' cover.
  • Purchases required: Units you must order to meet sales and hit the target closing stock, calculated as Sales + Closing Stock Target – Opening Stock.
  • Lead time: The number of days or weeks between placing an order and receiving the goods.
  • Unit cost (COGS cost): The price you pay your supplier per unit, excluding VAT or sales tax unless stated.
  • Purchasing budget: The total cash value of planned stock purchases, spread across periods.
  • Payment terms: The delay between receiving goods and paying the invoice; for example, Net 30 means payment is due 30 days after delivery.
  • Cash outflow: The actual money leaving your bank account in a given period to settle supplier invoices.
  • Stock shortage: A situation where calculated closing stock falls below zero, meaning you cannot fulfil demand without additional ordering.
  • Excess inventory: Closing stock substantially above your target, tying up cash unnecessarily.
  • Days cover: How many days of average sales your current stock can support before running out.

What this guide covers and what it excludes

This guide builds a single-product, multi-period purchasing budget for demonstration purposes. It covers quantity planning, cash-value conversion, and payment-term staggering. It does not cover multi-location warehouse management, automated supplier ordering, VAT recovery timing, or currency hedging. All unit costs, payment terms, and safety-stock levels used here are illustrative; replace them with your own verified figures before making real purchasing decisions.

Prerequisites

  • A Google account with access to Google Sheets.
  • A GSheetPress account with the Calculator entitlement active. If you are not sure which plan includes Calculator, check the GSheetPress pricing page.
  • The GS2Web SaaS add-on installed in Google Sheets (Extensions menu → Add-ons → Get add-ons, search GS2Web).
  • A dedicated sample workbook created solely for this exercise. Do not use a file containing private or production data.
  • Basic comfort with entering values and formulas in Google Sheets. No prior spreadsheet expertise is required; every formula is provided in full.

Step 1 — Set up your workbook structure

Open a new Google Sheets file and rename the default tab Budget. Add a second tab called Calculator (the add-on will use this tab later). All work in Steps 2–6 happens on the Budget tab.

Step 2 — Build the period header row

Row 1 of the Budget tab holds your period labels. Enter the following values:

CellValuePurpose
A1ItemRow label column
B1Month 1First planning period
C1Month 2Second planning period
D1Month 3Third planning period
E1Month 4Fourth planning period
F1Month 5Fifth planning period
G1Month 6Sixth planning period

You can replace Month 1 through Month 6 with real month names such as January, February, and so on. Keep column A as the label column throughout.

Step 3 — Enter assumptions and sales forecast

Rows 2 to 6 hold your assumptions. Enter labels in column A and hard-coded numbers in columns B through G.

Cell A (Label)RowSample values (B through G)
Unit cost (£)212, 12, 12, 13, 13, 13
Payment terms (months)31, 1, 1, 1, 1, 1
Target closing stock (units)4200, 200, 220, 220, 250, 250
Opening stock (units) — Month 1 only5150 in B5 only; leave C5:G5 blank
Forecast sales (units)6400, 450, 420, 480, 500, 460

Payment terms note: Enter 1 to mean the invoice is paid one month after the goods arrive, 2 for two months, and so on. A value of 0 means payment on delivery.

Step 4 — Build the quantity planning rows

These rows calculate how many units to purchase each month.

Row 7 — Opening stock (dynamic)

Opening stock for Month 1 is your manually entered figure. For Month 2 onwards, it equals the previous month's closing stock.

  • B7: enter = B5 (pulls the Month 1 hard-coded opening stock)
  • C7: enter the formula below and copy it across to G7:
= D8

Wait — we need to build Row 8 first; come back to C7 after completing Row 8.

Row 8 — Closing stock

Closing stock = Opening stock + Purchases – Sales. Enter this formula in B8 and copy across to G8:

= B7 + B10 - B6

Row 10 (Purchases required) is built next. Google Sheets will show a zero or error temporarily; it resolves once Row 10 is complete.

Row 9 — Target closing stock

Row 9 simply references Row 4 so the planning model uses the same target as the assumptions block.

Enter in B9 and copy to G9:

= B4

Row 10 — Purchases required (units)

This is the core purchasing formula: Purchases = Sales + Target Closing Stock – Opening Stock. Enter in B10 and copy to G10:

= MAX(0, B6 + B9 - B7)

The MAX(0, ...) wrapper prevents negative purchase quantities. A result of zero means your existing stock is sufficient and no order is needed that month.

Complete C7 now

Return to C7 and enter:

= B8

Copy C7 across to G7. Each month's opening stock now automatically equals the prior month's closing stock, creating a rolling chain.

Row 11 — Shortage flag

A shortage occurs when closing stock before any new purchase would be negative. This formula shows a warning message rather than hiding the problem:

= IF(B7 - B6 < 0, "SHORTAGE: order urgently", "OK")

Enter in B11 and copy to G11. The < symbol is the less-than operator. If opening stock minus forecast sales is negative, fulfilment is impossible without stock already on order or an emergency purchase.

Step 5 — Convert quantities to cash values

Row 12 — Purchase value (£)

Multiply units to purchase by the unit cost for that month:

= B10 * B2

Enter in B12 and copy to G12. This is the gross invoice value before any payment delay.

Row 13 — Cash outflow (£)

Cash outflow in a period is the invoice from the period defined by the payment terms. A payment-terms value of 1 means you pay this month's invoices next month, so the outflow in Month 2 equals Month 1's purchase value. Use the OFFSET function to look back the correct number of periods:

= IF(COLUMN(B13) - COLUMN($A13) > $B$3, OFFSET(B12, 0, -$B$3), 0)

Enter this in B13. Copy it across to G13.

How it works: COLUMN(B13) - COLUMN($A13) gives the period number (1 for Month 1, 2 for Month 2, and so on). If that number is greater than the payment terms (stored in B3), it looks back B3 columns to find the correct invoice. If not enough periods have elapsed, it returns 0, meaning the invoice has not yet fallen due within the model window.

Important assumption: This formula uses the payment-terms value from B3 as a fixed number for all periods. If your payment terms vary by supplier or period, replace $B$3 with the relevant cell reference for each month.

Row 14 — Cumulative cash outflow (£)

Useful for cash-flow planning, this running total shows total spend to date:

= SUM($B$13:B13)

Enter in B14. Copy to G14. The mixed reference $B$13 anchors the start of the range while B13 expands as you copy right.

Row 15 — Excess inventory flag

Excess inventory is defined here as closing stock more than 50% above target. Adjust the threshold to suit your business:

= IF(B8 > B9 * 1.5, "EXCESS: review next order", "OK")

Enter in B15 and copy to G15.

Step 6 — Label all rows clearly

In column A, enter these labels so the model is self-documenting:

CellLabel
A2Unit cost (£)
A3Payment terms (months)
A4Target closing stock (units)
A5Opening stock Month 1 (units)
A6Forecast sales (units)
A7Opening stock (units)
A8Closing stock (units)
A9Target closing stock (units)
A10Purchases required (units)
A11Shortage flag
A12Purchase value (£)
A13Cash outflow (£)
A14Cumulative cash outflow (£)
A15Excess inventory flag

Step 7 — Worked example and expected results

Using the sample figures from Step 3, your Budget tab should produce the following values in rows 7 to 14. Check your own sheet against these to confirm the formulas are working correctly.

Row / ItemMonth 1Month 2Month 3
Opening stock (Row 7)150200200
Forecast sales (Row 6)400450420
Purchases required (Row 10)450450440
Closing stock (Row 8)200200220
Purchase value £ (Row 12)5,4005,4005,280
Cash outflow £ (Row 13)05,4005,400
Cumulative outflow £ (Row 14)05,40010,800

Month 1 cash outflow is 0 because payment terms are 1 month; the invoice for Month 1 purchases is paid in Month 2. Month 2 closing stock equals the Month 3 opening stock, confirming the rolling chain is correct.

Alternative scenario: Change the Month 1 opening stock (B5) to 50 units. Purchases required in Month 1 rise to 550, the shortage flag in B11 shows SHORTAGE because 50 − 400 = −350, and the Month 2 cash outflow rises to £6,600. This confirms the shortage detection and cash-conversion logic both respond correctly.

FAQs and troubleshooting

Frequently asked questions

Q: Can I use this model for multiple products?
Yes. Duplicate the Budget tab for each product and create a summary tab that pulls the purchase-value rows from each product tab using simple cell references or SUMIF. Keep the structure identical across tabs so formulas copy cleanly. For a more structured approach, the GSheetPress table tool can help you display multi-product summaries on a web page.

Q: How do I convert monthly periods to weekly periods?
Replace the month labels in Row 1 with week numbers or dates. Change the payment terms in Row 3 from months to weeks (for example, Net 30 days becomes approximately 4 weeks). All formulas remain identical because they reference relative column positions, not calendar logic.

Q: What if my unit cost changes mid-year due to a supplier price increase?
Enter different unit costs in each column of Row 2 rather than a single value. The purchase-value formula in Row 12 already references the same-column unit cost (B2, C2, and so on), so it will automatically use the correct price for each period. Google Sheets named ranges can make these assumption cells easier to manage in larger models.

Troubleshooting

  • Circular reference error: Rows 7 and 8 reference each other (closing stock feeds next month's opening stock). Google Sheets shows a circular reference warning if you accidentally reference the same column in both. Check that C7 references B8, not C8.
  • Row 13 always shows zero: Confirm that B3 contains a number, not text. If you typed the word "one" instead of 1, the OFFSET logic will not work. Also confirm you copied the formula horizontally, not vertically.
  • Purchases required shows very large numbers: Check that Row 7 (dynamic opening stock) correctly chains from the previous period's closing stock. If C7 still contains the manual = B5 formula from before you updated it, opening stock will be understated every month.
  • Shortage flag shows SHORTAGE even though stock looks fine: The flag checks opening stock minus sales before any new purchases arrive. This is intentional; it highlights when you need goods already in transit or an emergency order. It does not mean your model is wrong.
  • Cumulative outflow does not grow smoothly: The SUM($B$13:B13) formula relies on being copied from B14 rightward. If you entered it manually in each cell rather than copying, the anchor $B$13 may have drifted. Re-enter in B14 and copy right.

Build the inventory purchasing calculator

The Budget tab holds your full multi-period model. This section adds an interactive web calculator so colleagues can test a single-period scenario (opening stock, forecast sales, unit cost, target closing stock, payment terms, and unit cost change flag) without editing the spreadsheet directly. For a broader introduction to the Calculator feature, see the GSheetPress Calculator overview.

Account and entitlement check

Open your GSheetPress account and confirm your plan includes the Calculator entitlement. Then open your dedicated sample workbook in Google Sheets and verify the GS2Web SaaS add-on is listed under Extensions. If it is missing, install it from the Google Workspace Marketplace before continuing.

Create the calculator

  1. Click Extensions in the Google Sheets menu bar.
  2. Click GS2Web SaaS (or the installed add-on name).
  3. Click Calculator, then New Calculator.
  4. When prompted, enter the name: Inventory Purchasing Budget.
  5. When asked for the number of inputs, enter 5. This is the count of input fields only, not including the Calculate button.
  6. When asked whether to include graphs, choose No for this model.
  7. Click Create. The add-on inserts a new tab named Calculator (or the name you specified) with headers auto-generated in row 1: Labels, Input Type, Required, Data, Default, ToolTips, Inputs, Rules in columns A through H.

Do not rename or delete these headers. Do not type anything in row 1 yourself.

Input row setup

With 5 inputs, rows 2 through 6 are input rows, row 7 is the auto-generated Calculate button, row 8 is the merged Results separator, row 9 holds result headings, and results begin at row 10.

Row 2 — Opening stock

  • A2 — Label: Opening stock (units)
  • B2 — Input Type: Number
  • C2 — Required: tick the checkbox (check it on)
  • D2 — Data: leave blank
  • E2 — Default: 150
  • F2 — ToolTips: Units held at the start of the period
  • H2 — Rules: leave blank

Row 3 — Forecast sales

  • A3 — Label: Forecast sales (units)
  • B3 — Input Type: Number
  • C3 — Required: tick the checkbox
  • D3 — Data: leave blank
  • E3 — Default: 400
  • F3 — ToolTips: Expected unit sales in this period
  • H3 — Rules: leave blank

Row 4 — Target closing stock

  • A4 — Label: Target closing stock (units)
  • B4 — Input Type: Number
  • C4 — Required: tick the checkbox
  • D4 — Data: leave blank
  • E4 — Default: 200
  • F4 — ToolTips: Minimum units to hold at period end
  • H4 — Rules: leave blank

Row 5 — Unit cost

  • A5 — Label: Unit cost (£)
  • B5 — Input Type: Number
  • C5 — Required: tick the checkbox
  • D5 — Data: leave blank
  • E5 — Default: 12
  • F5 — ToolTips: Supplier price per unit excluding tax
  • H5 — Rules: leave blank

Row 6 — Payment terms

  • A6 — Label: Payment terms (periods)
  • B6 — Input Type: Number
  • C6 — Required: tick the checkbox
  • D6 — Data: leave blank
  • E6 — Default: 1
  • F6 — ToolTips: Number of periods after delivery before payment is due (0 = pay on delivery)
  • H6 — Rules: leave blank

Row 7 — Calculate button (auto-generated, do not delete)

The add-on places the Calculate button in row 7 automatically. Confirm that B7 contains Button and D7 contains main. Do not change these values. The label shown in A7 (often Calculate or Submit) is what users see on the web form; you may change the A7 text to Calculate budget if you wish, but leave B7 and D7 exactly as generated.

Row 8 is the auto-generated merged Results separator. Leave it untouched.

Result headings — Row 9

Enter these headings in row 9:

  • A9: Result
  • B9: Value

Result formulas — Rows 10 onwards

All input-dependent formulas reference column G, which holds the values submitted from the web form. Column E holds the form defaults and is used for pre-publication testing. Never store result formulas in column G; G is overwritten by each web submission.

Before publishing, test your formulas using the defaults: select G2:G6, copy them, then paste using Edit → Paste special → Values only into G2:G6. This places the default numbers into G so your result formulas calculate immediately without a web submission.

Row 10 — Purchases required (units)

Click cell A10 and type:

Purchases required (units)

Click cell B10, type the leading = sign, then type the rest of the formula and press Enter:

= MAX(0, G3 + G4 - G2)

This is Sales (G3) + Target closing stock (G4) – Opening stock (G2), floored at zero.

Row 11 — Purchase value (£)

A11: Purchase value (£)

B11:

= B10 * G5

Purchases required multiplied by unit cost.

Row 12 — Cash outflow this period (£)

A12: Cash outflow this period (£)

B12:

= IF(G6 = 0, B11, 0)

If payment terms are zero (pay on delivery), cash leaves the account this period. If terms are 1 or more, the outflow is deferred and shows as 0 in the current single-period view. For a multi-period cash schedule, use the full Budget tab model.

Row 13 — Closing stock (units)

A13: Closing stock (units)

B13:

= G2 + B10 - G3

Opening stock + Purchases – Sales.

Row 14 — Shortage warning

A14: Shortage warning

B14:

= IF(G2 - G3 < 0, "SHORTAGE: insufficient opening stock", "Stock sufficient")

Row 15 — Excess inventory warning

A15: Excess inventory warning

B15:

= IF(B13 > G4 * 1.5, "EXCESS: closing stock > 150% of target", "Within target range")

Verify results using the defaults

With the default values (Opening stock 150, Sales 400, Target closing 200, Unit cost £12, Payment terms 1) your result cells should show:

CellLabelExpected value
B10Purchases required450
B11Purchase value5400
B12Cash outflow this period0
B13Closing stock200
B14Shortage warningStock sufficient
B15Excess inventory warningWithin target range

If B14 shows SHORTAGE with these defaults, re-check that G2 contains 150 and G3 contains 400. If B10 shows a negative number, the MAX wrapper is missing; re-enter the formula in full.

For further help configuring your calculator or troubleshooting the add-on, visit the GSheetPress support page.

Publish and test your web calculator

  1. Share the spreadsheet so the add-on can publish it. Click Share (top-right), set General access to Anyone with the link, set the role to Editor, and click Done. Important: this setting means anyone who obtains the spreadsheet link can edit it directly. Use only your dedicated sample workbook for this step; never apply these permissions to a file containing real business or personal data.
  2. Click the Calculator tab (the one the add-on created) to select it.
  3. Click Extensions → GS2Web SaaS → Calculator → Publish Calculator.
  4. Wait for the confirmation message before proceeding.
  5. In the add-on panel, click Open Calculator Link. A sheet listing your published calculators opens.
  6. Find the row where the Sheet Name matches Inventory Purchasing Budget.
  7. Open the URL in column C (Link), not the URL in column B (Edit Link). Column C is the public-facing calculator.
  8. If the list looks outdated, click Get Links to refresh it.
  9. On the public calculator page, enter the test values: Opening stock 150, Forecast sales 400, Target closing stock 200, Unit cost 12, Payment terms 1. Click Calculate budget.
  10. Confirm the results match the expected values from the table above. If they differ, return to the Calculator tab, correct the relevant formula in column B, and republish. Republishing the same calculator retains its existing public link.
  11. Optionally, copy the Iframe code from column D of the links sheet and paste it into an HTML block on your website to embed the calculator inline.

Your inventory purchasing budget calculator is now live. Share the column C link with colleagues who need to run purchasing scenarios. Because all calculation logic lives in column B of the Calculator tab, results are always based on the current formulas; no one needs to open or edit the spreadsheet to use the tool.