Stock control reconcile

Job Task: Create Historical Stock Control Report (2022-2026) Objective Create a stock control report that tracks inventory movement for all 8 stock items from 2022 to 2026, providing visibility on: Stock purchased Stock sold Stock on hand Annual stock movement trends Identification of any stock discrepancies The report will use data sourced from sales records and stock purchase orders. Key Responsibilities 1. Gather Data Collect all stock purchase order records from 2022-2026. Collect all sales records relating to the 8 tracked stock items from 2022-2026. Verify data completeness and identify any missing information. Ensure item names and stock codes are consistent across all records. 2. Create Master Inventory Database Develop a consolidated spreadsheet/database containing: Item Name Item Code (if applicable) Purchase Date Quantity Purchased Sales Date Quantity Sold Supplier Customer (if required) 3. Calculate Annual Stock Movements For each of the 8 stock items, calculate: Opening Stock (start of each year) Total Units Purchased Total Units Sold Closing Stock (end of each year) Formula: Closing Stock = Opening Stock + Purchases - Sales Track annually for: 2022 2023 2024 2025 2026 (YTD) 4. Reconcile Stock Balances Compare calculated stock balances against any available physical stock counts. Investigate and document any discrepancies. Record assumptions made where historical data is incomplete. 5. Develop Stock Control Report Create a report showing: By Item Opening Stock Quantity Purchased Quantity Sold Closing Stock Current Stock on Hand By Year Total Inventory Purchased Total Inventory Sold Stock Remaining Year-on-Year Stock Movement Analysis Best-selling items Slow-moving items Stock turnover trends Potential overstock or stock shortage risks 6. Create Dashboard (Optional) Develop an Excel dashboard including: Annual stock movement charts Sales by item Current stock levels Inventory trend analysis Stock balance summary Deliverables Master inventory spreadsheet containing all purchase and sales transactions from 2022-2026. Annual stock control report for each year. Summary dashboard showing inventory trends and current stock levels. Reconciliation report highlighting stock discrepancies. Documentation of methodology, formulas, and assumptions. Expected Outcome Produce a complete and auditable inventory history for 8 stock items, showing purchases, sales, and stock on hand from 2022-2026. The report should provide accurate inventory visibility, support financial reporting, and enable improved stock management and forecasting.