Managing marketing budgets across 22 automotive brands and 19 regional dealerships is not primarily a spreadsheet formatting challenge. It is a data modeling, multi-entity allocation, and financial reconciliation problem.
A dependable management platform must connect annual budget planning, monthly allocations, multi-brand marketing campaigns, vendor invoices, and group-level executive reporting without forcing staff to maintain multiple disconnected workbooks.
AI-assisted development can accelerate the delivery of this tool significantly. However, preserving ledger accuracy, preventing unallocated budget leakage, and ensuring transparent audit trails require strict engineering controls from day one.
1. Start with the financial model, not the UI
When an automotive group manages dozens of franchise agreements, three distinct monetary values must remain strictly separated in the database schema:
- Planned Budget: The baseline approved budget allocated to a specific brand, dealership location, expense category, and fiscal period.
- Allocated Spend: The projected cost committed to specific marketing campaigns and distributed proportionally across participating brands and branches.
- Actual Invoiced Spend: The finalized expenditure recorded from vendor invoices, verified against matching purchase orders and activity records.
Conflating these amounts into a single mutable field makes identifying campaign overruns and unbilled commitments virtually impossible.
| Data Entity | Architectural Responsibility |
|---|---|
| tbl_BudgetPlan | Approved monthly and quarterly financial ceilings partitioned by brand, facility, and channel. |
| tbl_MarketingActivity | Campaign definition, contracted vendor, execution timeline, channel category, and total committed cost. |
| tbl_ActivityAllocation | Proportional percentage and derived currency allocation for each participating brand and dealership. |
| tbl_MonthlyDistribution | Time-phased amortization of multi-month campaigns across discrete reporting cycles. |
| tbl_InvoiceRecord | Vendor invoice number, billing date, settled payment amount, and foreign key link to the campaign. |
| tbl_AuditHistory | Immutable log recording actor ID, prior value, revised value, and timestamp for all financial adjustments. |
How the multi-brand allocation logic works
Consider a regional digital acquisition campaign costing €12,000 spanning three distinct automotive brands across multiple dealership locations. The data model isolates the master campaign record from individual brand share entries:
| Entity Allocation | Share Percentage | Calculated Amount |
|---|---|---|
| Brand A • Metro Hub Location | 40.0% | €4,800 |
| Brand B • Suburban Center | 35.0% | €4,200 |
| Brand C • Regional Dealership | 25.0% | €3,000 |
| Reconciled Total | 100.0% | €12,000 |
The validation layer enforces strict integrity: entries where the sum of percentages does not equal 100% are rejected immediately. Cent-level rounding discrepancies are automatically absorbed into the largest allocation line to eliminate unallocated residual variances.
2. Platform selection: Microsoft 365 vs Google Workspace
For multi-location dealer groups, Microsoft 365 represents the most reliable foundation when utilizing existing SharePoint and Power Platform subscriptions:
- Microsoft 365 (Recommended): SharePoint Lists as the relational data store, Power Apps for validated campaign entry forms, and Power Automate for invoice routing. Reporting is delivered via dynamic Excel pivot models or direct Power BI integration.
- Google Workspace (Alternative): Google Sheets structured as a normalized relational database with protected ranges, backed by Google Apps Script web forms and Looker Studio dashboards. Effective for lean operations without M365 infrastructure.
3. Core system architecture and data flow
The application pipeline operates through clean structural boundaries:
Input Interface (Power Apps) → Data Integrity Validation → SharePoint Storage Hub → Power Automate Ledger Sync → Reporting Views (Excel / Power BI)
4. Controlled AI-assisted implementation steps
Rather than generating the complete solution from an unstructured prompt, structure execution across disciplined milestones:
- Stage 1: Schema Normalization: Establish entity models, field data types, and primary-foreign key relationships.
- Stage 2: Vertical Slice Construction: Build one end-to-end operational slice (create budget → enter multi-brand activity → log vendor invoice → verify ledger report).
- Stage 3: Edge Case Testing: Subject the calculation logic to cross-year campaigns, split invoices, and partial cancellations.
- Stage 4: Pilot Deployment: Pilot with 3 dealership locations over one complete monthly close cycle before group-wide launch.
Testing financial reconciliation edge cases
Validation suites must verify:
- Total allocation percentages strictly equal 100.00%.
- Invoiced actuals that deviate from planned commitments flag appropriate variance warnings.
- Multiple invoice records can be matched against a single marketing campaign without inflating committed totals.
- Concurrent user edits on the same dealership budget maintain record locking and audit preservation.
5. Implementation roadmap and delivery stages
Delivery planning should be organized into discrete architectural milestones:
| Delivery Phase | Technical Deliverables | Validation Checkpoint |
|---|---|---|
| Requirements & Rules | Catalog of 22 brand allocation agreements, expense taxonomy, and authorization matrix. | Signoff by group marketing director and financial controller. |
| Data Modeling & Hub | Normalized SharePoint lists, indexed columns, and security inheritance rules. | Delegation limits and query indexing validated against 5,000 sample records. |
| Forms & Allocation UI | Power Apps responsive form with automated 100% share validation and rounding reconciler. | Multi-brand campaign allocation test cases executed successfully. |
| Invoice Reconciliation | Vendor invoice matching workflows, variance flags, and approval routing. | Reconciliation against historical accounting export verified to zero variance. |
| Executive Reporting | Dynamic Excel pivot models, scheduled summary distributions, and optional Power BI dashboards. | Live pilot review with 3 dealership general managers. |
Frequently asked questions
How does the data model prevent double-counting multi-brand campaigns?
The system stores the campaign record once in tbl_MarketingActivity and generates separate child allocation rows in tbl_ActivityAllocation. Group-wide reporting queries either aggregate the master campaign records or sum the allocated line items, never both, completely avoiding duplicate spend calculations.
Can SharePoint Lists handle high transaction volumes without hitting Power Apps delegation limits?
Yes, by indexing primary lookup columns (such as Fiscal Year, Dealership ID, and Brand Code) and using delegable Power Fx filter expressions. For enterprises with tens of thousands of annual transactions, Microsoft Dataverse or Azure SQL can serve as the data layer while retaining the exact same user-facing Power Apps interface.
