Skip to content

README.md

Latest commit

 

History

History
89 lines (60 loc) · 4.36 KB

README.md

File metadata and controls

89 lines (60 loc) · 4.36 KB

📊 Infinity Tech Corporate Expense Tracker

Excel Finance Status

A strategic financial dashboard designed to analyze corporate spending patterns, assess vendor concentration risk, and monitor Capital vs. Operational expenditure.

📸 Dashboard Preview

Infinity Tech Expense Tracker Dashboard (A comprehensive view of Q1-Q4 expense trends, departmental allocation, and vendor reliance.)

📌 Project Overview

Infinity Tech required a solution to move away from static spreadsheets to a dynamic view of their financial health. This Excel-based tool ingests raw transactional data and transforms it into actionable intelligence.

Key Capabilities:

  • Spend Classification: Automated breakdown of Capital (CapEx) vs. Operational (OpEx) expenses.
  • Vendor Risk Analysis: Identification of top vendors to assess dependency.
  • Departmental Auditing: Granular view of spending by department (IT, HR, Marketing, etc.).
  • Trend Detection: Quarterly and monthly analysis to spot inflation or spending spikes.

🔎 Key Findings & Data Story

Based on the analysis of R1.3 Million in transaction data, the following insights were uncovered:

1. ⚖️ CapEx vs. OpEx Balance

  • The Split: Expenditure is split almost evenly: Capital (49.7%) vs. Operational (47.3%).
  • Insight: The slight lean towards Capital spending suggests the company is in an investment/growth phase, heavily allocating funds to long-term assets rather than just maintenance.

2. ⚠️ Vendor Concentration Risk

  • Top Heavy: Spending is highly concentrated. Three vendors (Vendor D, E, and B) account for over 60% of total procurement costs.
  • Risk: This creates a high dependency. Any pricing changes or supply chain issues with these three vendors could significantly impact operations.

3. 🏢 Departmental Efficiency

  • High Spenders: Training & Development is the #1 cost center (~R177K), followed by IT Infrastructure.
  • Lean Operations: HR and Marketing are operating with the lowest budgets (<R100K).
  • Observation: While high training spend is good for culture, at >13% of the total budget, the ROI of these programs should be audited.

4. 📉 "Essential" vs. "Non-Essential"

  • The Gap: Non-Essential expenses (53%) currently outweigh Essential expenses (47%).
  • Opportunity: This is the primary area for cost-cutting. Tightening discretionary spending could free up significant capital for strategic projects.

🛠️ Technical Implementation

This dashboard demonstrates advanced Excel proficiency:

⚙️ KPI Logic & Formulas

Metric Technical Approach
Dynamic Summation SUMIFS used to toggle between Capital/Operational views.
Top Vendor Logic INDEX + MATCH + MAX to dynamically pull the highest spender.
PO Utilization COUNTIFS / COUNT to calculate the percentage of expenses backed by Purchase Orders.
Data Modelling UNIQUE functions used to validate departmental lists.

🎨 formatting & UX

  • Custom Number Formatting: Large figures formatted dynamically (e.g., ###.0,,\M for Millions, ###.0,\K for Thousands) for readability.
  • Interactivity: Slicers connected to multiple Pivot Charts for cross-filtering by Department, Vendor, and Time Period.

📂 Repository Structure

  • Corporate_Budget_Tracker.xlsx - The main interactive dashboard file (Includes Raw Data, Pivot Processing tab, and Dashboard).
  • Dashboard-Screenshot.png - Preview image.

🚀 How to Use

  1. Download: Clone the repo or download the .xlsx file.
  2. Interact: Open in Excel. Use the Slicers on the left to filter by Department or Nature of Expense.
  3. Analyze: Hover over the trend lines to see specific monthly variances.

🧑‍💼 Author

Bheki Mogola Aspiring Data Analyst turning numbers into business strategy.

📍 Connect with me: LinkedIn Profile