Building a Multi-Company Stock and Consumption Web App from Excel

Managing shared facilities across multiple entities creates constant recordkeeping friction. Facility administrators must track office supplies, pantry groceries, housekeeping chemicals, security guards and utility sub-meters across separate cost centers. Typically, these records sit inside monthly workbooks like Aug - 2026 ODMSLLP Monthly Consumption.xlsx, burdened by complex tabs and formula errors.

This tutorial forms Part 2 of our series on converting spreadsheets into browser applications using artificial intelligence. Following Part 1's admin task register, this guide builds a complete multi-company consumption manager, stock ledger and manpower tracker running entirely client-side without server costs.

The Multi-Company Excel Dilemma: Why Shared Workbooks Collapse

Tracking supplies across operating units in Excel creates recurring friction. One business buys paper supplies, another covers water bills and security agencies invoice by attendance. Three core issues arise:

• Formula Corruption: Thirty-one day columns across dozens of products slow calculation. Accidental edits overwrite carryover formulas, triggering inventory mismatches.
• Mismatched Tracking Modes: Consumables require depletion math, guards require attendance cycles and meters require dual-reading formulas. Forcing these into one table format creates confusion.
• Version Discrepancies: Emailing monthly sheets between warehouse staff and accounting causes version drift, obscuring real inventory balances.

Translating sheets, like our models for SOC Monthly Consumption Report in Excel and Ather Stock Allocation & Inventory, into a browser app delivers automated stock math, vendor filtering and structured synchronization.

Architectural Blueprint: Modeling Multi-Mode Data Structures

Before writing prompts or generating interface code, you must establish clear data structures. Unlike simple task logs, an industrial consumption manager must handle four distinct operational tracking behaviors within one centralized data store.

To communicate these requirements to an artificial intelligence model, specify explicit operational rules. Detail business relationships, master catalog tabs, status rules and layout structures in your prompt.

Core Interface Tour: From Secure Sign-In to Real-Time Dashboards

The web application, ODMSLLP ConsumPro, unites facility workflows in a single browser window. A client-side authentication modal prevents unauthorized edits to daily records.

After sign-in, the Monthly Summary Dashboard provides high-level metric cards and an itemized matrix. Executives quickly review active items, cycle days, order counts and total consumption value in Indian Rupees (₹).

The summary matrix groups items by mode. Consumables show unit counts, while security and housekeeping show attendance days. Every row calculates expenditure in Indian Rupees (₹), enabling immediate departmental budget checks.

The Daily Disbursement Ledger: Live Consumption and Stock Math

The Register view acts as the daily ledger. Supervisors select company, consumption category, month and vendor from the header filter bar.

The register incorporates four automated capabilities:

1. Automatic Stock Carryover: Opening stock imports the prior month closing balance, eliminating re-entry errors across billing cycles.
2. Interactive Day Columns: Staff log daily usage across thirty-one date cells. Total consumption and closing balances recalculate instantly.
3. Order Quantities Panel: Entering new stock arrivals in the bottom panel adds units directly to available inventory.
4. Financial Valuation: Contracted rates in Indian Rupees (₹), such as A4 Paper at ₹230 per box or Toner at ₹450 per unit, multiply by usage to compute monthly expense totals.

Managing Master Entities: Products, Manpower and User Permissions

Centralized master tables maintain operational consistency across nine dedicated panels: Companies, Consumption Types, Vendors, Items, Employees, Electricity Meters, Rates, Months and Users.

Configuring Product and Material Masters

Facility managers register new items with their commercial parameters: company entity, material category, vendor, brand, unit of measure, tax HSN code and default rupee rate.

Configuring Outsource Manpower and Guard Rosters

The Employee master panel connects guards, supervisors and cleaners to authorized vendor agencies like Gallent Security, OOPL Services or Imperial Services.

Each profile stores an employee ID, designation and day rate in Indian Rupees (₹), from ₹400 daily for cleaners to ₹700 for security supervisors. Clicking attendance cells cycles states: Present (P), Absent (A), Half Day (H) and Weekly Off (WO), automatically calculating agency invoice totals.

Role-Based Access Control and Granular Permissions

Floor staff should record daily usage without permissions to alter contractual rates. The Users panel provides granular controls for each profile.

Administrators configure six permissions: Dashboard, Register, Master, Sync, Data Entry and Orders. Staff accounts can be locked strictly to data entry, shielding master records from accidental edits.

Debugging AI-Generated Web Apps: Resolving Script and DOM Failures

Building single-file applications with generative artificial intelligence frequently produces subtle runtime bugs. During our initial build of ODMSLLP ConsumPro, the application halted due to JavaScript compilation errors in the browser console. Knowing how to diagnose and resolve these issues is essential when assembling AI software.

The most common failure in AI web tools stems from premature Document Object Model access. If an AI script places document.getElementById('reg-co').addEventListener(...) at the top level of a <script> block, the browser executes that line before the corresponding <select> tag is parsed. This immediately halts script execution. As documented in the official MDN Web Docs on Document Object Model (DOM), scripts must always defer element access until the document structure is fully ready.

The snippet below shows the production pattern for safe lifecycle initialization and state synchronization using localStorage:

// Safe lifecycle initialization pattern
document.addEventListener('DOMContentLoaded', function() {
  // Load persistent application state from browser localStorage
  loadApplicationState();
  
  // Attach event listeners only after DOM elements are ready
  setupEventListeners();
});

function setupEventListeners() {
  var coSelect = document.getElementById('reg-co');
  if (coSelect) {
    coSelect.addEventListener('change', onRegCoChange);
  }
}

function onRegCoChange() {
  // Populate dependent category dropdowns before rendering grid
  updateCategoryDropdown();
  loadRegister();
}

function calculateClosingStock(opening, orders, dailyArray) {
  var totalUsage = dailyArray.reduce(function(acc, val) {
    return acc + (Number(val) || 0);
  }, 0);
  return (Number(opening) || 0) + (Number(orders) || 0) - totalUsage;
}

This organized architecture ensures that data flows predictably, dropdown options populate in sequence and inventory formulas execute without runtime crashes.

Serverless Data Synchronization: Scoped Templates and Cloud Sync

Operational tools do not require costly database servers. Hosted SQL databases typically cost ₹2,500 to ₹15,000 monthly in subscriptions and maintenance. Using native browser APIs and structured file exchanges, our app delivers reliable sync at ₹0 infrastructure cost.

The Sync panel delivers four data workflows:

• Scoped CSV Template Generator: Generates formatted CSV templates scoped to specific companies and categories, pre-loaded with valid item codes and vendor names to avoid header errors.
• Direct File Uploads: Supervisors import monthly numbers via CSV or JSON file readers, recalculating balances and updating localStorage in milliseconds.
• Google Drive URL Fetching: Fetches shared Google Drive CSV links into browser memory for distributed teams.
• JSON Backup Archives: Creates instant offline snapshots of master tables and transaction records, protecting against data loss.

For operations tracking utility sub-meters alongside inventory, this workflow pairs with our guide on Towal Tower Electricity & DG Billing to reconcile power meters and diesel stocks.

Operational Schema Repository

To help operations teams and developers adapt this framework, the complete source code, unpopulated data schemas and sample test records are available in our public repository. Inspect the HTML structure, CSS rules and JavaScript logic to adapt them for your workflows.

Transitioning from fragile spreadsheets to interactive client-side web tools gives companies faster entries, automated stock reconciliation and clear accountability. If your facility manages unique utility rate slabs or seasonal attendance shifts, leave a comment below and let us know how you handle daily stock reconciliation.