Table of Contents

I. Overview............................................................................................................................. 2
II. Basic Features .................................................................................................................... 2
III. Advanced Features ............................................................................................................. 4
IV. Module development Lifecycle ........................................................................................... 4
V. Module’s Schema ............................................................................................................... 7
VI. Module’s Layout .............................................................................................................. 12
VII. Login or Welcome page ................................................................................................... 12

1
FEYO TRADING PLC

INVENTORY MANAGEMENT MODULE

I. Overview
The inventory management module will be Excel based applicaGon with VBA-driven forms with
data validaGon layer and Macros for automaGon.

The module’s funcGonaliGes is summarized as follows:

o Ingest Data: Record stock receipts (from suppliers), issues (to fulfill sales), and
adjustments (e.g., damages, theW ) via forms with validaGon.
o Store Data: Maintain inventory transacGons and stock levels in structured tables.
o Query and Report: Generate reports (e.g., current stock, low stock alerts) and
visualizaGons (e.g., stock trends).
o Integrate: Export inventory data (e.g., stock valuaGon) to the financial model via VBA.

II. Basic Features

They ensure the module tracks the inventory level and movement effecGvely, supports
OperaGons Department and integrated with other modules within the financial model.

1. Data Inges5on Forms with Valida5on:

o Stock Receipt Form: Record stock received from suppliers (increases inventory).
o Stock Issue Form: Record stock issued for sales (decreases inventory).
o Stock Adjustment Form: Adjust stock for discrepancies (e.g., losses, returns).
o Product Form (shared with sales/cost modules): Maintain product list with
inventory-specific fields.
2. Data Storage:
Excel Tables:
o Products : Product details (shared with sales/cost modules)

2
 o Stock_Receipts : Records stock received from suppliers.
o Stock_Issues : Records stock issued for sales.
o Stock_Adjustments : Records stock correcGons.
o Stock_Balances : Tracks current stock levels per product SKU (calculated or
updated).
3. Query and repor5ng:
VBA Query Form: filters inventory data by date range(daily/weekly, monthly), Product
SKU, Stock status (e.g., Below order level) etc.
Standard Reports: the report will be customized as follows
o Stock Status: Current quanGty, value, and reorder status per product SKU.
o Stock Movement: Receipts, issues, and adjustments by product SKU/period.
o Low Stock Alerts: Products with quanGty ≤ reorder level.
o Inventory Valua5on: Total stock value (quanGty * unit Cost).
4. Visualiza5on
Charts:
o Bar chart: Stock levels by product SKU/category.
o Line chart: Stock movement trends (e.g., monthly receipts vs issues).
o Pie chart: Inventory value by category.
VBA Integra5on: Bubon to generate/update charts dynamically.
5. Basic Automa5on
o Auto-calculate Total Cost in Stock Receipt Form.
o Auto-generate unique IDs (e.g., “REC-2025-001” for receipts). To be decided!
o Auto-update Stock_Balances aWer each receipt, issue, or adjustment. Error
handling (e.g., “Insufficient stock” for issues).
o Low stock alerts via Message-box/pop up when saving receipts/issues.
6. Integra5on
The module’s data structure will be designed to store inventory data and linked to other
modules such as sales, cost , revenue projecGon modules in the financial model.

3
FEYO TRADING PLC

III. Advanced Features

These opFonal features enhance scalability, analyGcs, and automaGon. To be decided!

o Automated Reorder Triggers: Generate purchase orders (via VBA) when stock falls
below reorder level.
o Demand Forecas5ng: Predict stock needs based on sales data (e.g., moving average,
seasonal trends the likes of rainy season).
o Dynamic Dashboard: InteracGve Pivot table-based dashboard with slicers for product
SKU or date range.

IV. Module development Lifecycle

The module’s development will be done phase by phase basis , where each phase includes the
respecGve task list and descripGon, the deliverable/output and esGmated Gmeline.

Phase 1: Design Phase (2.5 days)

Objec5ve : Plan the module’s structure, data model, and UI.

Task Descrip5on Output

List must-have features, stock movements (receipts,

Define issues, adjustments), and reports. Requirements
Requirements Plan integraGon with sales, cost and other modules. document

Plan tables ( Products , Stock_Receipts , Stock_Issues ,

Design Data Stock_Adjustments , Stock_Balances ) with columns Data model
Model and relaGonships. schema (Excel)

Sketch Stock Receipt, Issue, Adjustment, and Product

Design VBA Form wireframes
forms with fields, bubons, and validaGons.
Forms (Excel mockups)

4
 Plan Reports and Specify reports (Stock Status, Movement, Low Stock, Report/chart
Visuals ValuaGon) and charts (bar, line, pie). specificaGons

Design Define export format (e.g., CSV with Product,

Integra5on QuanGty, Value) and VBA logic for financial model. IntegraGon plan
Plan links to sales/cost modules etc
Create Project Project plan
Outline tasks, milestones, and dependencies.
Plan (Excel)

Phase 2: Development (2 days)

Objec5ve: Build forms, data storage, queries, reports, and visuals.

Task Descrip5on Output

Create workbook with sheets for Products ,

Set Up Workbook Stock_Receipts , Stock_Issues , Stock_Adjustments , Workbook with
Stock_Balances , and Reports. tables
IniGalize tables.
Develop Product Code Form for product data with reorder fields. FuncGonal
Form Save to Products (shared with sales/cost). Product form

Code Form for receipts with mulG-SKU ListBox, linked

Develop Stock to Purchases . FuncGonal
Receipt Form Save to Stock_Receipts and update Stock_Balances . Receipt form

Code Form for issues, linked to Sales_Orders .Validate

Develop Stock stock availability. Save to Stock_Issues and update FuncGonal Issue
Issue Form Stock_Balances . form

5
 FEYO TRADING PLC

Develop Code form for adjustments with reason dropdown. FuncGonal

Adjustment Form Save to Stock_Adjustments and update Adjustment form
Stock_Balances .
Implement Query Write VBA to filter inventory data by date, product Query
Logic SKU, or status. Output to temporary sheet. funcGonality

Code VBA for Stock Status, Movement, Low Stock,

Four reports with
Develop Reports and ValuaGon reports.
export
Add CSV/PDF export.
Create Generate bar, line, and pie charts via VBA. Embed in
Visualiza5ons Reports sheet. Three charts

Implement auto-ID generaGon, stock balance Automated

Add Automa5on* updates, low stock alerts, and error handling. features

Debug forms, queries, and visuals during Stable module

Tes5ng development. components

Phase 3: Tes<ng (0.5 day)

Objec5ve: Validate funcGonality, performance, and integraGon readiness

Task Descrip5on Output

Test each form (Product, Receipt, Issue, Adjustment) for

data entry, validaGon, and saving.
Unit Tes5ng Bug-free forms
Verify stock balance updates.

Test reports for accuracy with sample data Accurate reports

Query Tes5ng

Visualiza5on Tes5ng Verify charts update correctly and scale with data. FuncGonal charts

6
 Test links to sales ( Sales_Orders ) and cost
Integra5on Tes5ng*
( Purchases ) modules. Working integraGon

Test export to financial model.

User Acceptance Tes5ng Get feedback from test user UAT report with fixes
(UAT)

Finalized module
Final Fixes Address bugs or feedback (e.g., Form UI tweaks).

V. Module’s Schema
As indicated in the earlier secGon, the inventory management module requires 5 excel tables to
store all relevant data:- Products, stock receipts, stock issues, stock adjustment, and stock
balances.

Each table will be designed as an excel file with :

o Unique IDs as primary keys

o Timestamps for audit trails*** (opGonal)
o Rela5onships to link data (e.g Stock_ Receipts references Suppliers and Products )
o Valida5on rules to ensure data integrity via VBA.
o Calculated fields where applicable (e.g., Stock Balance)

1. Products table
The table stores product details for inventory tracking, shared with sales and cost modules,
extended with inventory-specific fields (e.g reorder Level).
Its populated via Product Form; used in Receipt, Issue, and Adjustment forms for SKU
selecGon.
The schema:

7
 FEYO TRADING PLC

Column Name Data Descrip5on Valida5on/Notes

Type
Unique idenGfier
Product_ID Text Primary key, auto-
generated, unique

Product name (e.g., “Rich

Product_Name Text Powder”) Not null, max 100
chars

Stock Keeping Unit (e.g., 5L)

SKU Text Not null, unique, max
20 chars

Not null, predefined

Category* Text Product category (opGonal) list (e.g., dropdown)

Selling price per unit (for OpGonal, posiGve,

Unit_Price Number reference) decimal (2 places)

Minimum stock level before

Not null, posiGve
Reorder_Level Number reorder (to be decided!)
integer

Reorder_QuanGty Number QuanGty to reorder when low Not null, posiGve

integer

Created_At* DateTime Timestamp of record creaGon Auto-set to Now()

Updated_At* DateTime Timestamp of last update Auto-updated on

changes

2. Stock receipts table

The table tracks stock received from suppliers, increasing inventory levels.

It Populated via Stock Receipt Form; updates Stock balances .

The schema:

8
Column Name Data Type Descrip5on Valida5on/Notes

Unique idenGfier (e.g., REC-2025-

001)
Receipt_ID Text Primary key, auto-
generated

References

Supplier_ID Text Suppliers.Supplier_ID Not null, must exist in

Suppliers

References

Product_ID Text Products.Product_ID Not null, must exist in

Products

Receipt_Date Date Date of receipt Not null, valid date

QuanGty Number Number of units received Not null, posiGve integer

Unit_Cost Number Cost per unit (from supplier) Not null, posiGve, decimal
(2 places)

Total_Cost Number QuanGty * Unit_Cost Calculated, decimal (2

places)

References OpGonal, must exist in

Purchases if provided
Purchase_ID Text Purchases.Purchase_ID

Created_At* DateTime Timestamp of record creaGon Auto-set to Now()

Updated_At* DateTime Timestamp of last update Auto-updated on changes

3. Stock issue Table

The table tracks stock issued for sales, decreasing inventory levels.

It is populated via Stock Issue Form; updates Stock balances .

The schema:

9
 FEYO TRADING PLC

Column Name Data Type Descrip5on Valida5on/Notes

Unique idenGfier (e.g., ISS-2025-

001)
Issue_ID Text Primary key, auto-generated

References

Product_ID Text Products.Product_ID Not null, must exist in Products

Issue_Date Date Date of issue Not null, valid date

Not null, posiGve integer, ≤ available

stock
QuanGty Number Number of units issued

References OpGonal, must exist in

Sales_Order_ID Text Sales_Orders.Order_ID Sales_Orders if provided

Created_At* DateTime Timestamp of record creaGon Auto-set to Now()

Updated_At* DateTime Timestamp of last update Auto-updated on changes

4. Stock adjustments table

The table tracks stock correcGons such as damages, returns etc increasing or decreasing
inventory.

Its populated via Stock Adjustment Form; updates Stock balances

The schema:

Column Name Data Type Descrip5on Valida5on/Notes

Unique idenGfier (e.g., ADJ-

Adjustment_ID Text Primary key, auto-generated
2025- 001)

References

Product_ID Text Products.Product_ID Not null, must exist in Products

10
Adjustment_Date Date Date of adjustment Not null, valid date

Change in stock Not null, non-zero integer, result ≥

(posiGve/negaGve) 0 stock
QuanGty Number

Reason for adjustment Not null, predefined list

(e.g., damage,theW) (dropdown)
Reason Text

DescripGon Text AddiGonal details OpGonal, max 200 chars

Created_At* DateTime Timestamp of record Auto-set to Now()

creaGon

Updated_At* DateTime Timestamp of last update Auto-updated on changes

5. Stock balances table

The table tracks current stock levels and value per product SKU, updates dynamically

Its updated by VBA aWer each receipt, issue, or adjustment; used for reports such as Stock
Status, ValuaGon etc.

The schema:

Column Name Data Type

Descrip5on Valida5on/Notes

References
Primary key, must exist in
Product_ID Text Products.Product_ID Products

QuanGty Number Current stock level Calculated, non-negaGve integer

Calculated, posiGve, decimal (2

Weighted average cost per
Unit_Cost places)
Number unit

Total_Value Number QuanGty * Unit_Cost Calculated, decimal (2 places)

Last_Updated DateTime Timestamp of last update Auto-updated on changes

11
FEYO TRADING PLC

VI. Module’s Layout

The inventory management module’s workbook structure will be as followed:

File Name: FMCG_Inventory_Management.xlsx

Sheets:

o Home: main interface for users with navigaGon and control bubons.
o Products: Contains Products table (shared with sales/cost modules).
o Stock_Receipts: Contains Stock_Receipts table.
o Stock_Issues: Contains Stock_Issues table.
o Stock_Adjustments: Contains Stock_Adjustments table.
o Stock_Balances: Contains Stock_Balances table.
o Reports: Displays query results in tabular format and charts - Bar (stock levels), Line
(movement trends), Pie (value by category)
o Se\ngs: Stores predefined lists (e.g Categories/SKU, Reasons) and configuraGon that
will be used in forms and reports.

---------------//----------------------------

VII. Login or Welcome page

The login page will be displayed when the workbook opens, requiring users such as Office
Assistant, DSR, Sales Manager etc to enter their credenGals before accessing the module. The
credenGals will be stored in Users table , and the login form will validate against this table.
The User Interface(UI) will be designed in visually appealing manner(branding, colors, fonts)
to enhance the users experience.

Basic features of the login page includes:

o Welcome message
o Role based access
o Login abempt limit: 3 failed abempt before closing the module/model

12
Advanced features include:****

o Password recovery: forgot password bubon linking to a form where Admin can reset
passwords.
o Session management: tracking users sessions and auto login aWer inacGvity

Note: The login page is extra feature, and will be developed post-implementaGon aWer
discussion.

13