Comprehensive Inventory Management System

MS Access / Excel workbook and database combination to do inventory inspection, purchase offers, warehouse tracking, and selling offers. Access should control tables and relationships for all necessary data. Excel should be used to provide charts, slices, pivots of information for dashboard views. The architecture of the process is generally as follows. 1. User-Facing Sheets (Visible)

1. Dashboard
Key Performance Indicators (stock levels, sales, purchases, pending offers)
Charts (species breakdown, monthly sales trend)
Buttons (macros) to navigate to key functions

2. Buying Inspection Form
Structured input table (supplier details, curing date, species, grade, length, cut, state, barcode)
(Hidden) Inspection records table with selectable status, revisions, suppliers etc. functions
Buttons: New inspection, save inspection, recall inspection

3. Buying Offer Form
Structured input table from Inspection Form with pricing (supplier details, curing date, species, grade, length, cut, state, barcode).
(Hidden) Buying Offer records table with selectable status, revisions, suppliers etc. functions
Buttons: New offer, save offer, recall offer, Generate Supplier Offer PDF

4. Crate Management
Manage inventory warehouse crates from current stock & accepted Buying Offers (crate no., capacity, contents)
Export crates (linked to confirmed sales orders)
(Hidden) Inventory records table with history of all stock and transaction details
Buttons: Allocate to Crates / Move to Export Crate

5. Sales Enquiry Form
Input buyer requirements (species, grade, length range, qty)
(Hidden) Sales Offer records table with selectable status, revisions, customers etc. functions
Buttons: New sales offer, save offer, recall offer, Generate Offer PDF

6. Reports
Stock list (all available skins)
Traceability report (crate → barcode → CITES document)
Transaction summary (buying offers, sales orders)

2. Data Tables (Back-End Sheets)

1. Suppliers
SupplierID, Name, Contact details, Notes

2. Customers
BuyerID, Name, License, Contact

3. Inventory Master
Unique ID (Barcode)
Species, Grade, Length, Cut, Curing Date, State
Crate Number, Location (Warehouse/Export)
Status (Available, Reserved, Sold)

4. Crates
CrateID, Type (Warehouse/Export)
Date Created, Assigned Sales Order
Linked list of skin barcodes inside

5. Buying Offers Log
OfferID, SupplierID, Date, Summary (species/grade breakdown), Total Value
Linked list of included barcodes

6. Sales Offers Log
OfferID, BuyerID, Date, Summary, Status (Pending/Confirmed/Rejected)
Linked list of included barcodes

7. Sales Orders
OrderID, BuyerID, Date, Assigned Export Crates

3. Output Templates (For PDF Generation)

1. Supplier Offer Template
Header with Supplier Name/Date
Table of species/grade/size summary with prices.
Auto-filled from Buying Inspection

2. Sales Offer Template
Header with Buyer Name/Date
Table of selected stock items, quantities, pricing

3. Sales Order / Packing List Template
Buyer info, CITES doc details
Crate numbers and contents (barcode list)

4. Process Flow (Data Movement)

1. Buying Inspection Form → logs items into Inventory Master (status = “Pending”).
2. Generate Supplier Offer → PDF via template, record in Buying Offers Log.
3. When supplier accepts → move items to “Available” and assign Warehouse Crates.
4. Sales Enquiry Form → searches Inventory Master (Available stock).
5. Generate Sales Offer → PDF via template, record in Sales Offers Log.
6. If confirmed → allocate items from warehouse crates → export crates, update Inventory Master.
7. Sales Order created → linked to Export Crates and logged.
8. Dashboard pulls summary data from all logs + inventory.