Excel Applications: Real-Time Inventory, Quality Score, GANTT, FMEA, Pareto, and Defect Tracking Management

Real-Time Inventory Tracking System

Project Overview
The Real-Time Inventory Tracking System is an Excel-based inventory management tool that allows users to record product inflows and outflows while viewing stock levels in real time. Fully based on formulas and control lists, the system eliminates manual calculations and stock control errors.

Project Objective
The main goal of this project is to systematically track product entries and exits, making stock levels immediately visible. This enables early detection of shortages or surpluses, allowing timely action to be taken.

Why Use It?
Eliminates errors caused by manual calculations.
Provides real-time visual alerts for stock shortages or surpluses.

As the number of tracked items increases, manual control becomes impractical, making digital tracking essential.
Regular stock monitoring reduces costs and improves efficiency.

How It Works
The system consists of two main sheets:

User Transaction Sheet

• Select transaction type (In / Out) from a dropdown list.
• Enter the transaction date manually.
• Select the product from a dropdown list.
• Enter the quantity for the transaction.

Real-Time Stock Sheet

• Automatically updates without user input.

For each product:

• Total inflow quantity
• Total outflow quantity
• Current stock level

Visual Alert System

Stock cells are color-coded based on levels:

• Red: Critical stock level – below the minimum threshold, urgent restocking required.
• Yellow: Moderate stock level – monitor closely.
• Green: Sufficient stock – no immediate action needed.

This color-coded system allows users to quickly assess stock status at a glance.

Use Cases

• Small and medium-sized enterprises
• Retail stores
• Warehouses and distribution centers
• Schools, offices, or laboratories where material tracking is important

Advantages

• Easy to set up and use thanks to Excel infrastructure.
• No additional software costs.
• Customizable structure allows adaptation to any sector.
• Real-time updates save time in stock management.

Real-Time Inventory Tracking System

For the video, click here ▷

Quality Score Tracking System

What is the Project?
The Quality Score Tracking System is an Excel-based evaluation and monitoring tool designed to standardize quality control in production processes and to make daily quality status measurable. 

The system operates using two separate tables: Table 1 calculates the daily quality score based on responses to quality criteria, while Table 2 visualizes product-specific quality scores over time.

Table 1 – Daily Quality Assessment Form
Each day, the user evaluates randomly selected samples from production according to the following seven quality criteria using Yes/No (1/0) responses:

• Are the physical properties of the product, such as dimensions, thickness, and weight, within the tolerances specified in the technical drawings?
• Does the surface quality meet the required visual standards?
• Does the product function as intended in functional tests?
• Are the parts correctly and securely assembled according to assembly requirements?
• Considering appropriate hardness, strength, or composition, is the correct material used?
• Do randomly selected daily samples pass the quality control processes as required?
• Is the number or rate of defective products in the production batch within acceptable limits?

Each "Yes" response is scored as 1 point, and each "No" response is scored as 0 points. The daily total quality score is calculated on a maximum of 7 points.

A score of 7 highlights the result cell in green, while scores below 7 are highlighted in red. This setup allows for rapid identification of quality deviations.

Table 2 – Date-Based Product Quality Score Tracking
Based on the daily quality scores obtained from Table 1, Table 2 presents the following for each product:

• Daily quality status is indicated with a green check (✔) or a red cross (✘).
• At the end of each day, the average quality score for each product is calculated as a percentage.
• Product-specific quality performance is evaluated over a defined period.

This structure enables both real-time and periodical quality management.

Why Use This System?

• Ensures systematic monitoring of production processes from a quality perspective.
• Provides objective evaluation through a numerical scoring system.
• Allows early detection of defective days or declining quality trends.
• Unlike manual forms, automatic scoring and color-coded alerts accelerate the audit process.

Applications

• Small and medium-sized enterprises engaged in mass production.
• Daily inspections in quality control departments.
• Compliance with quality management systems such as ISO 9001.
• Educational institutions as teaching material for quality assessment.

Advantages

• Enables software-free monitoring using Excel infrastructure.
• Offers practical data entry through a user-friendly interface.
• Integrates daily and periodical quality analyses in a single framework.
• Color-coded alert system allows immediate corrective action.

Quality Score Tracking System

For the video, click here ▷

Dynamic Gantt Chart Project Tracking System

Project Overview
This project is a fully dynamic Gantt chart application developed in Excel. When the start and end dates of project tasks are entered, the system automatically generates the project duration, current date, elapsed time, remaining time, task status, personnel assignment, and a graphical timeline in real time. All components update automatically based on user inputs.

How It Works
The user follows these steps:

• Define Tasks: Each work package is entered individually, e.g., "Task 1.1," "Task 2.4."
• Enter Dates: Start and end dates are provided for each task.
• Assign Personnel: Tasks are assigned to personnel via a dropdown list.
• Select Status: For each task, one of the following is chosen:

1. Completed (Value: 0 – Green)
2. In Progress (Value: 1 – Yellow)
3. Not Started (Value: 2 – Red)

Based on these selections:

• Task name cells are color-coded (green, yellow, or red).
• Gantt chart bars automatically display task duration in green.
• A purple line dynamically indicates the current date.
• Start, Finish, Elapsed Time, and Time Remaining fields are calculated automatically.

Status Analysis
Below the main chart, the average of task status values is calculated:

• 0 – 1 (Green) → Progress is good.
• 1 (Yellow) → Progress is moderate.
• 1 – 2 (Red) → Progress is problematic; intervention may be needed.

Cells are color-coded accordingly to provide a clear visual monitoring tool.

Dynamic Features

• All dates update automatically.
• The "Today" line (purple) refreshes daily based on the system date.
• Gantt bars are automatically positioned according to task start and end dates.
• Status colors and indicators adjust based on user input.
• Personnel assignments contribute to workforce planning for task management.

Why It Should Be Used

• Simplifies project management and provides a visual overview.
• Enables flexible planning in Excel without complex software.
• Daily progress tracking is easy.
• Project performance is visualized in real time.
• Facilitates task assignment and monitoring within teams.

Applications

• Engineering and production planning
• Software project management
• University project submission tracking
• Educational or R&D task distribution
• Weekly/monthly operational tracking in businesses

Dynamic Gantt Chart Project Tracking System

For the video, click here ▷

Failure Mode and Effects Analysis (FMEA) Tracking Tool

Project Description
This Excel-based tool enables systematic analysis of failure risks using the FMEA (Failure Mode and Effects Analysis) methodology. The system evaluates potential failure types, their effects, causes, and detectability by assigning scores, calculating a Risk Priority Number (RPN), and providing the user with the ability to plan necessary corrective actions.

How It Works

Process and Function Definition
The user first defines the function of the system to be analyzed, e.g., “Vibratory screen spring system.”

Potential Failure Modes
Possible failure types within the process are identified, e.g., “Overstress, deformation, or breakage of springs.”

Effects of Failure
The technical or operational consequences of each failure are listed.

Potential Causes
Likely causes of failure are specified, e.g., “Incorrect spring tension adjustment” or “use of low-quality material.”

Evaluation System
For each failure, the user assigns a score from 1 to 10 for the following criteria:

RPN (Risk Priority Number) Calculation
RPN = Severity × Occurrence × Detection

The calculated value is color-coded:

• Green → Acceptable risk
• Yellow → Medium risk, observation required
• Red → High risk, improvement required
• Purple → Critical risk, immediate action required
• All relevant cells are automatically color-coded based on user inputs to facilitate visual monitoring.

Corrective Actions and Improvement

• The user enters recommended preventive or corrective actions.
• After implementation, new values for Severity, Occurrence, and Detection are entered.
• The updated RPN is automatically calculated, and the color-coded risk level is refreshed.

Dynamic Features

• Automatic RPN calculation and color-coding
• Re-evaluation capability after corrective actions
• Color-coded warning system
• Guiding columns with explanatory notes
• User-friendly and streamlined interface

Applications

• Production and maintenance planning
• Quality engineering
• R&D process safety
• Risk analysis in project management
• Compliance with quality standards such as ISO 9001 and ISO/TS 16949

Criterion	Description
Severity (S)	The seriousness of the failure’s effect. 1 = negligible, 10 = catastrophic.
Occurrence (O)	The probability of the failure occurring. 1 = very low, 10 = very high.
Detection (D)	The likelihood of detecting the failure before it occurs. 1 = very easy, 10 = almost impossible.

Failure Mode and Effects Analysis (FMEA) Tracking Tool

For the video, click here ▷

Tool for Identifying Priority Issues Using Pareto Analysis

What is the Project?
This Excel-based analysis tool identifies the most critical downtime causes in the system based on the Pareto Principle (80/20 Rule). The objective is to operate under the premise that resolving a small number of main problems can eliminate the majority of issues. The user is only required to enter the problem causes and the number of downtimes for each. All other analysis and chart generation processes occur fully automatically.

How Does It Work?

Specifying Problem Causes
The user enters the potential downtime causes in the system. Example: “Vibration motor failure,” “Material accumulation under the sieve,” etc.

Entering Downtime Counts
The frequency of downtime caused by each reason is specified.

Automatically Performed Processes
Dynamic Ranking
Downtime counts are automatically sorted in descending order.

Cumulative Calculations
Each problem’s contribution to total downtime is calculated cumulatively.

Cumulative Percentage Values
The percentage of total downtimes represented by each problem is calculated.

Critical Threshold According to the 80/20 Rule
The 80% cumulative limit is automatically determined by the system. The problems up to which investigation should be conducted are clearly indicated in the row below (dark blue).

Automatic Pareto Chart

• Bars: Represent the downtime count of each problem.
• Line Chart: Shows the cumulative percentage.
• 80% Threshold: The critical threshold is automatically placed on the chart with a green line.

The user does not need to perform any action to generate the chart. All chart visualizations are automatically prepared by the system.

What is the Pareto Principle?
The Pareto Principle is based on the widely used business rule that “a small number of causes produce the majority of results.” 

Typically: 80% of problems arise from only 20% of causes.

In this analysis: The first three causes account for approximately 82% of total downtimes.

Thus, addressing only these three problems can eliminate the majority of system downtimes.

Benefits Provided by the Project

• Rapid and visual identification of critical problems
• Contribution to efficient use of resources
• Prioritization in maintenance, quality, and production planning
• Time savings in operational processes
• Direct usability for managerial reporting

Tool for Identifying Priority Issues Using Pareto Analysis

For the video, click here ▷

Defect Identification, Recording, and Analysis Tracking System

This Excel-based project aims to systematically analyze and record defects occurring in production processes. The project consists of two main sheets:

Sheet 1 – Defect Identification Panel
The user sequentially selects Process, Subcategory, and Defect Description through the relevant lists. These selections are interrelated:

• The subcategory selection consists only of options compatible with the chosen process.
• The defect description is restricted based on both the process and the subcategory.

This structure ensures that the user only sees valid and meaningful options. Based on the selections:

• Defect code,
• Defect description, and
• Criticality level

are automatically determined. The criticality degree is also visually supported:

• High critical: Red
• Medium critical: Yellow
• Low critical: Green

If there is a mismatch in the selections, these cells are automatically colored black, and the user is alerted about the incorrect entry.

Sheet 2 – Defect Recording Form
This sheet is developed to enable systematic recording of identified defects, facilitating defect management. Recording processes are supported by specific rules and automation:

• Detection date: Restricted to today and yesterday.
• Defect selection: Can be done from the list or manually. Based on the selection, the error code and criticality status are automatically populated.
• Defect recurrence: If the same defect is entered again, the system automatically detects it and highlights it in red with a “YES” warning.
• Action date: Restricted to today and tomorrow.
• Current status: Selected from a predefined list (e.g., Acceptable, Defective, Awaiting Revision…).
• Tracking number: Automatically generated by the system (e.g., 02062025-1).
• Responsible ID: Personnel perform actions using a 4-digit unique password. Based on the entered ID, the responsible person's name is automatically assigned.
• Approval/Rejection: The process is concluded based on the manager’s decision.

Contribution of the Project

• Ensures accurate classification and analysis of defects.
• Facilitates detection and prevention of recurring errors.
• Encourages prompt action and limits delays.
• Minimizes user errors through color-coded alerts and automatic validations.
• Provides traceability and standardization in defect management.

This structure enhances quality control in production processes while ensuring actions are managed transparently and efficiently.

Defect Identification, Recording, and Analysis Tracking System

For the video, click here ▷