📅 Published by Subham Ghadge  |  Category: Corporate Finance, Business Intelligence, Excel Systems  |  Free Download Excel Macro Multi-Company Vendor Analytics

Multi-Company Expense Tracker & Dashboard in Excel

Managing expenses across multiple companies or business entities can get complicated fast. If a parent company runs different subsidiaries—like a digital marketing agency, a real estate firm, and an e-commerce brand—financial data often gets scattered across separate local spreadsheets.

At the end of the financial year, consolidating vendor payments and compiling quarterly expense reports across 15 or 20 different spreadsheets takes days of manual work. Without a single dashboard to view group-wide spending, business owners miss out on vendor volume discounts and lose track of overall operational costs.

To solve this data fragmentation, I created the SkyLine Venture Expenses Tracker in Excel. It uses a macro-enabled dashboard to bring raw data from multiple companies into one interactive report, calculates Indian Financial Year (April-March) totals automatically, and uses dynamic named ranges to adapt as your business grows.

Interactive Macro-Driven Dashboard

Instead of filtering data manually across separate sheets, the main dashboard uses a macro script linked to a company dropdown menu.

When you select a company name (such as "BluePeak Ventures" or "Nexora Digital"), the dropdown selection triggers a VBA script linked to the sheet's Change event. This script refreshes backend Pivot Table caches automatically using ActiveWorkbook.RefreshAll.

The pie charts, quarterly expense breakdowns, and monthly trend graphs update instantly to display numbers for the selected company—letting you inspect any subsidiary from a single control screen.

The Executive Dashboard. Selecting a company from the dropdown refreshes charts and metrics automatically.

Automated Indian Financial Year (April - March) Alignment

Most global software templates default to a January-to-December calendar year. However, corporate tax reporting and auditing in India follow an April-to-March Financial Year (FY) cycle.

To keep reports aligned with Indian financial quarters automatically, the system uses a date formula when raw transactions are logged:

=IF(MONTH(Transaction_Date)<4, YEAR(Transaction_Date)-1 & "-" & YEAR(Transaction_Date), YEAR(Transaction_Date) & "-" & YEAR(Transaction_Date)+1)

This formula assigns transactions logged in January, February, or March to the correct prior-year fiscal bracket (e.g., placing Feb 15, 2026 under FY 2025-2026).

This keeps all generated quarterly summaries ready for tax filings and Chartered Accountant reviews without manual sorting. This date setup uses principles similar to our Purchase Requisition System.

The Fiscal Quarter View. Groups transactions into April-to-March financial quarters automatically.

Dynamic Named Ranges using OFFSET & COUNTA

Business groups grow over time as new vendors or subsidiaries are added. If dropdown menus use fixed cell ranges (like A2:A50), adding a 51st vendor would break the dropdown list unless formulas are updated manually.

To ensure the file expands automatically, the sheet uses dynamic named ranges built with OFFSET and COUNTA:

=OFFSET(DataSheet!$A$2, 0, 0, COUNTA(DataSheet!$A:$A)-1, 1)

When you add a new vendor or subsidiary to the data master sheet, COUNTA counts the new row and OFFSET expands the list range automatically. The new name appears in dashboard dropdowns right away without editing formulas.

Cross-Subsidiary Vendor Tracking & Volume Discounts

Companies often lose negotiating power when different subsidiaries buy from the same supplier independently. If Company A uses "MetroBuild Solutions" for repairs and Company B uses them too, buying separately means missing out on group volume discounts.

This workbook summarizes total vendor payments across all companies in your group. By selecting a vendor name on the vendor analysis sheet, you can see total spending with that supplier across all subsidiaries.

If total group spending with a supplier is high, your procurement team has the data needed to negotiate better corporate pricing.

The Vendor Aggregation Report. Shows group-wide spending with key suppliers across all subsidiaries.

Fast Performance via Pivot Caching

Processing thousands of expense rows with text descriptions can slow down Excel if too many SUMIFS formulas run at once.

This system maintains fast performance by using backend Pivot Table caches. When you enter raw expense data, Excel loads it into compressed memory caches. Dashboard tables query this cache instead of scanning raw rows directly.

Even as your expense ledger grows to 10,000+ rows, the dashboard dropdowns refresh charts smoothly without lag.

Setup Instructions & Requirements

Because this dashboard uses VBA macros to refresh charts, click **"Enable Macros"** when opening the workbook in desktop Microsoft Excel (Windows or Mac).

Note: Desktop Excel is required for macro automation. Cloud spreadsheet apps (like Excel Online) do not execute desktop VBA macros natively.

Frequently Asked Questions

Can this replace primary accounting software like Tally or QuickBooks?
No. This tool is an executive reporting dashboard designed to visualize category spending, monitor quarterly trends, and consolidate multi-company vendor costs. Primary accounting software is still used for daily double-entry bookkeeping and official GST filings.

Connect For Custom Business Dashboards

If your group needs custom financial dashboards, multi-branch expense reporting, or Power BI models for executive tracking, feel free to reach out.