Portfolio Project: Financial Analysis-Variance Model- Simple (Budget vs Actuals)

Arnav Chaturvedi

Project Role: Financial Analyst – Retail – Pet Services Industry.

Variance Financial Model:

A variance model in financial analysis refers to a method of comparing budgeted amounts to actual financial performance, essentially calculating the difference between what was planned and what occurred. This allows businesses to identify areas where spending deviates from the budget and understand the reasons behind those discrepancies; it's a key tool for monitoring cost control and making informed financial decisions.

Portfolio Project Overview:

Pets & More, LLC is a small business owned by an entrepreneur who would like to keep track of his financials for future planning and get insights into any variations in income or spend. This business provides sales and services for pet owners in a suburban residential area. As a financial analyst, I created this financial variance model for ‘Pets & More, LLC’ to analyze company's revenue, expenses and variances. This model provides a dynamic and interactive way to check data for any given month, to get insights into that specific month’s financials. This model was also built to be very extendable, i.e. business owners can keep adding future monthly budgets and actuals data in this, and the model will work without many changes in it.

Business Problem Scenarios, Objectives and KPIs:

The owner and financial manager at ‘Pets & More, LLC’ would like to know and analyze the company’s financials for future planning, and to get insights in any variations in income or spending.

Target Stakeholders:

This financial analysis dashboard will be used for the following job roles to increase their productivity and achieve business goals:

• Finance Manager
• Financial Analysts
• Business Analysts

Skills and Applications used:

• SME (Subject Matter Expertise)
  • Business requirement gathering skill and techniques.
  • Finance knowledge and expertise.
  • Budget Planning and Forecasting
  • Income and Expenses Planning
  • Income Statements
  • Balance Sheets
  • Related all key line items
• Microsoft Excel
  • Knowledge of formulae and functions to be used in any typical financial data analytical solutions.
  • Dynamic Drop-Down in cell: Interactive feature which allows users to select month for which they would like to see variance analysis (Actuals vs Budget).
  • INDEX() function/formula
  • MATCH() function/formula
  • SUMIFS() function/formula
  • TODAY() function/formula for interactive dashboard automation.
  • TEXT() function/formula for interactive dashboard automation.
  • Conditional Formatting: “Highlight Cell Rules” with custom formatting for easier understanding for business users to see data insights in this variance analysis data. * Dynamic and extendibility features: This model allows business users to add new data (data row in actual workbook) which will be inputted in the calculation automatically. This makes this model very dynamic, interactive, extendable and useful.
  • Automation in user selection: When business users open this interactive variance financial model, this dashboard will automatically select the current month in the drop-down box for the ease of business users.
  • Charts for Income Data: “Clustered Column Chart” with custom formatting – series, chart area, etc.
  • Charts for Actual Expenses Data: “Pie Chart” with custom formatting – series, chart area, etc.

Solution Approach and Process to build this project:

I created a simple but dynamic/interactive variance model which helps small business owners to track their finances and get variance data insights for future planning.

Solution Steps:

• Business requirement gathering – How business owner want to see data (for key data insights), User experience and UI/UX standards as business user’s expectations.
• Gather data – Collected all historical income and expense data for analysis, and future planning information for budget forecasting.
• Pre-processing datasets – Clean and validate datasets (actuals, budget, all line items data).
• Build a Variance Model (Actual v/s Budget) – Dynamic dashboard with data visualizations to get data insights.
• Flexible, automated and extendable models which can be used with very small changes needed in future with more data.

Data Preparation Process (Data gathering, Data Cleaning, Data Transformation):

• Data Gathering – The Dataset (Microsoft Excel/ CSV File).
  • Data Sheet: 2 tabs in MS-Excel workbook (Budget, Actuals) by category.
  • Drop-down list of all income/expense categories which can be modified per future needs.
• Data Pre-Processing and Transformation.
  • Cell formatting, proper cell indentation, as per financial reports standards.
  • Calculations for totals and sub-totals for report line items.
  • Creating a new custom formatting for columns

Data Analysis & Visualization – Dashboard, Reports, Charts, Key findings, Data Insights:

• Standard UI/UX experience for business users.
• Automation – This dashboard automatically shows the current month’s variance modeling based on the ‘Current Month’ drop-down. This is achieved using the Excel functions, TODAY() and TEXT(), for this automation.
• Used proper visual formatting as per financial reports standards – Totals, Sub-Totals, Bold, Underlines etc.
• For Income Data Visualization: “Clustered Column Chart” with custom formatting – series, chart area, etc.
• For Actual Expenses Data Visualization: “Pie Chart” with custom formatting – series, chart area, etc.
• Refer to screenshots below for reports on this solution.

Portfolio Project Documentation (Final Conclusion):

Documentation:

• ReadMe-Financial-Analysis-Variance Model- Simple (Budget vs Actuals)-Excel.docx

Output File:

• Financial-Analysis-Variance Model- Simple (Budget vs Actuals).xlsx

Dynamic and Interactive Dashboard – Financial Variance Model (Budget vs Actuals):

Dataset: Budget.

Dataset: Actuals by category.