An Inventory Management and Profit Analysis Dashboard in Google Sheets lets a small business record every purchase and sale in one file and see, for any date range, how much it sold, how much it bought, what profit it made and how much stock is left. This guide explains a ready-to-use template built on four tabs: Product Master, Purchase, Sales and Dashboard.
It is meant for shop owners, traders, small distributors and online sellers who track stock in a spreadsheet today and want a cleaner system with automatic lookups and a live summary. You will see what each tab holds, the formulas behind the key columns (subtraction, multiplication and VLOOKUP), a worked example with the sample numbers, and how to set it up with your own products.
🎥 Watch the Video Tutorial
Get the Inventory and Profit Analysis Dashboard
What Is Inside the Inventory Management and Profit Analysis Dashboard
| Tab | What you do there | Key columns or visuals |
|---|---|---|
| Product Master | List every product once with its prices | Product Id, Product Name, Purchase Price, Sales Price, Profit Per Unit |
| Purchase | Log every purchase from suppliers | Date, Product ID, Product Name, Purchase Price, Qty, Purchase Value |
| Sales | Log every sale | Date, Product ID, Product Name, Sale Price, Qty, Sale Value, Profit |
| Dashboard | Pick a date range and read the results | Start Date, End Date, Sale, Purchase, Profit, Inventory (Qty), Inventory Value, Profit% gauge, Sales by Dates, Profit by Products |
Tab 1: Product Master
This is the reference list for the whole file. Each product has a Product Id (P1 to P10 in the sample), a Product Name (Prod-1 to Prod-10), a Purchase Price and a Sales Price. The last column is a formula:
Profit Per Unit = Sales Price − Purchase PriceFor P1: 45.4 − 34.1 = 11.3. For P9: 128.2 − 77.2 = 51.0, the highest margin per unit in the list.
Tab 2: Purchase
Whenever you buy stock from a supplier, add a row with the Date, Product ID and Qty. The Product Name and Purchase Price are pulled from the Product Master with VLOOKUP, and the value is Purchase Price × Qty. Formulas along these lines do the job (cell references in your copy may differ):
Product Name =VLOOKUP(B2, 'Product Master'!A:E, 2, FALSE)
Purchase Price =VLOOKUP(B2, 'Product Master'!A:E, 3, FALSE)
Purchase Value =D2 * E2Worked example from row 2: P1 bought on 1/1/2024, 97 units at 34.1. Purchase Value = 34.1 × 97 = 3,307.7, exactly as the sheet shows.
Tab 3: Sales
Each sale gets a row with Date, Product ID and Qty. Product Name and Sale Price come from the Product Master with VLOOKUP. Two columns are calculated:
Sale Value = Sale Price × Qty
Profit = Profit Per Unit (from Product Master) × QtyRow 2 sells 7 units of P1 for a Sale Value of 317.5 and a Profit of 78.8. If you multiply the rounded prices by hand (45.4 × 7 = 317.8) you get a slightly different figure, because the prices are stored with more decimals than the sheet displays. That is normal; the sheet uses the full values.
Tab 4: The Dashboard
The top row works like a small report header:
- Start Date and End Date – double-click to pick dates. Every other number uses this range.
- Sale – total sale value in the range (15,099.58 in the screenshot).
- Purchase – total purchase value in the range (24,210.40).
- Profit – total profit in the range (4,861.28).
- Inventory (Qty) – units in stock for all products as of the End Date (1,644).
- Inventory Value – value of that stock as of the End Date (61,028.03).
Profit% Gauge
The gauge shows profit as a percentage of sales:
Profit% = Profit ÷ Sale = 4,861.28 ÷ 15,099.58 = 32.19%Sales by Dates
An area chart of sale value for each date between the Start Date and End Date. In the sample, sales climb from about 1,000 on 1/1/2024 to over 4,000 around 1/6/2024.
Profit by Products
A 3D column chart of profit per product in the range. Prod-9 (1,273.8) and Prod-8 (1,097.1) lead, while Prod-2 (138.6) and Prod-7 (151.2) contribute the least.
Formulas You Can Use to Build Similar Totals
If you want to add your own cards, these patterns calculate date-range totals and stock as of a date (Start Date in B5, End Date in D5):
Sale in range
=SUMIFS(Sales!F:F, Sales!A:A, ">="&B5, Sales!A:A, "<="&D5)
Stock (Qty) as of End Date
=SUMIFS(Purchase!E:E, Purchase!A:A, "<="&D5) - SUMIFS(Sales!E:E, Sales!A:A, "<="&D5)Our guide to SUMIFS in Google Sheets explains the conditions in detail, and the gauge chart guide shows how a Profit% dial like this one is made.
How to Use It with Your Own Data
- Make a copy of the file in your Google Drive.
- Replace the sample rows in Product Master with your products, IDs and prices. Keep the IDs unique.
- Clear the sample rows in Purchase and enter your opening stock as purchases dated on your start day.
- From then on, add a Purchase row for every delivery and a Sales row for every sale. Type only Date, Product ID and Qty.
- On the Dashboard, set the Start Date and End Date for the period you want to review.
- Read the cards, then check the Profit by Products chart for your strongest and weakest lines.
Real-World Use Cases
- Retail shop: review each week’s sales and profit and see stock left before placing the next order.
- Online seller: compare which products earn the most profit, not just the most sales.
- Small wholesaler: track purchases against sales for a month to see how much cash is sitting in stock.
Best Practices
- Change prices only in the Product Master, so every new row picks up the right price.
- Enter purchases and sales on the day they happen. Late entries distort the date-range figures.
- Do not delete old rows. The stock figure depends on the full history up to the End Date.
- Check the Inventory (Qty) figure against a physical count once a month.
Limitations: Who Should Not Use It
This is a spreadsheet template, not a full inventory system. It tracks quantities and values for one stock pool, so it is not designed for multiple warehouses, batch or expiry tracking, barcode scanning or purchase orders. Because prices come from the Product Master, changing a price later can also change how older rows calculate. If you need those features, look at a dedicated system or our multi-location inventory management sheet.
Related reading: the Inventory Management System in Google Sheets, tracking stock levels and reorder status and the Inventory Management Dashboard in Google Sheets. Related templates: Inventory Management Dashboard in Google Sheets and Inventory Management Dashboard in Excel.
If you want to go further with structured lessons on building dashboards like this, the Excel Pivot Tables and Dashboards course on NextGenTemplates Academy is a good next step.
Frequently Asked Questions
How does the dashboard calculate inventory?
Inventory (Qty) is the stock available for all products as of the End Date, based on everything purchased minus everything sold up to that date.
What does the Profit% gauge show?
Profit as a share of sales for the selected range. In the sample, 4,861.28 profit on 15,099.58 sales gives 32.19%.
Do I need to type product names and prices on every row?
No. Enter the Product ID and the name and price are looked up from the Product Master with VLOOKUP.
How many products can I add?
The sample has ten, but you can add more rows to the Product Master. Make sure each new product has a unique ID.
Can several people use it at once?
Yes. It is a Google Sheets file, so you can share it and enter purchases and sales from different devices.
Why is Purchase higher than Sale for the week?
Because more stock was bought than sold in that range. The difference stays in inventory, which is why Inventory Value is shown alongside.
Wrapping Up
This template keeps inventory and profit in one place: a product list, two simple logs and a dashboard that answers “what did we sell, what did we buy, what did we earn and what is left” for any date range. Watch the video, load your products and start logging your next purchase.
Get the Inventory and Profit Analysis Dashboard
📅 Last updated: October 2026
