Excel Template for Inventory Control with Income Statement (Personal Use)

This comprehensive Excel template is specifically designed for personal use individuals managing small inventories while tracking financial performance through an income statement. Combining Inventory Control and Income Statement functionalities, this template provides a streamlined approach to monitor stock levels, calculate cost of goods sold (COGS), and generate profit reports—all within a single, user-friendly workbook.

Overview

The template is ideal for freelancers, small business owners, artisans, resellers (e.g., on Etsy or eBay), and hobbyists who maintain physical inventory but need basic financial tracking. It supports personal use only—no commercial redistribution is allowed—and emphasizes simplicity without sacrificing accuracy.

Sheet Names

• 1. Inventory Ledger: Tracks all incoming and outgoing inventory items.
• 2. Income Statement (Profit & Loss): Calculates revenue, expenses, COGS, and net profit.
• 3. Transaction Log: Detailed log of every sale, purchase, or adjustment with timestamps.
• 4. Dashboard Overview: Visual summary of key metrics using charts and KPIs.
• 5. Instructions & Tips: User guide with guidance on setup and best practices.

Table Structures and Columns (Data Types)

1. Inventory Ledger (Sheet: Inventory Ledger)

This is the core of the Inventory Control system. It maintains a real-time count of inventory items.

2. Income Statement (Sheet: Income Statement)

This sheet consolidates financial data from the inventory and transaction logs to generate a personal Income Statement.

The line items include:

• Revenue (from sales)
• Cost of Goods Sold (COGS) - calculated using inventory movements
• Gross Profit
• Operating Expenses (e.g., packaging, shipping, fees)
• Net Profit

3. Transaction Log (Sheet: Transaction Log)

Formulas Required

• COUNTIFS in Inventory Ledger:
  To update "Current Quantity" dynamically:
  =IF([@TransactionType]="Purchase", [@[Quantity]] + CurrentQty, [@[Quantity]] - CurrentQty)
  (Note: This requires a helper formula or use of structured tables with named ranges.)
• COGS Calculation (Income Statement):
  =SUMIFS(TransactionLog[Total Value], TransactionLog[Transaction Type], "Sale")
  This totals all sales values from the log.
• Gross Profit:
  =Revenue - COGS
• Net Profit:
  =Gross Profit - SUM(Operating Expenses)

Conditional Formatting

• Highlight "Reorder Status" in red if status is "Low Stock" or "Out of Stock".
• Color code cells in the Income Statement: green for revenue, red for expenses.
• Apply data bars to monthly sales in the Dashboard to visualize trends.

Instructions for the User (Personal Use Only)

1. Download & Open: Save this file and open in Excel or compatible software (e.g., Google Sheets).
2. Add Items: Enter products in the "Inventory Ledger" tab with names, categories, costs, and minimum stock levels.
3. Add Transactions: Use the "Transaction Log" to record every sale, purchase, or adjustment. The template auto-updates inventory counts.
4. Review Income Statement: All financial calculations are automatically populated from transaction data.
5. Use Dashboard: Check performance trends and stock status at a glance using charts and KPIs.
6. Note: This template is for personal, non-commercial use only. Do not redistribute or sell this file.

Example Rows

In Inventory Ledger:

In Transaction Log:

Recommended Charts & Dashboards (Dashboard Overview)

• Monthly Sales Trend Line Chart: Shows revenue growth over time.
• Inventory Health Pie Chart: Visualizes stock levels by category (e.g., "Jewelry: 40%, Home Decor: 60%").
• COGS vs. Revenue Bar Chart: Compares cost of goods sold to income.
• KPI Cards: Display current Net Profit, Total Inventory Value, and Number of Low-Stock Items.

This template seamlessly integrates Inventory Control, Income Statement, and personal finance tracking—all in a single, easy-to-use Excel workbook. Designed for individual users needing simple yet powerful insights into their inventory-based business or hobby operations.