📊 Optimizing Profits Using Excel What-If Analysis 📌 Project Overview
This project demonstrates how Excel What-If Analysis tools can be used to evaluate pricing and quantity decisions and analyze their impact on profitability. The focus is on decision modeling, scenario comparison, and sensitivity analysis using Excel.
📁 Dataset & Model Structure
The dataset contains product-level information including:
Price
Quantity
Transport Cost
Item Cost
Total Revenue (calculated)
Total Cost (calculated)
Profit (calculated)
All calculations are formula-driven to support What-If Analysis.
🔧 Excel Features & Techniques Used
Scenario Manager
Goal Seek
Two-variable Data Tables
Excel formulas for revenue, cost, and profit
Conditional Formatting (Color Scales) for insight clarity
🔁 Scenario Manager Analysis
Scenario Manager was used to compare different pricing and volume strategies for a selected product.
Scenarios created:
Base Case
High Price Strategy
High Volume Strategy
📷 Screenshot: Scenario Manager Setup
📷 Screenshot: Scenario Summary Output
🎯 Goal Seek Analysis
Goal Seek was applied to determine the required quantity needed to achieve a target profit.
Set Cell: Profit
To Value: Target Profit
By Changing Cell: Quantity
📷 Screenshot: Goal Seek Before Execution

📷 Screenshot: Goal Seek Result

📐 Data Table (Sensitivity Analysis)
Two-variable Data Tables were created to analyze how changes in price and quantity affect profit.
Rows: Quantity values
Columns: Price values
Output: Profit
Conditional formatting was applied to clearly highlight:
Loss regions
Break-even zones
High-profit combinations
📷 Screenshot: Data Table with Conditional Formatting
📈 Key Insights
Increasing sales volume had a stronger impact on profitability than price changes
Data Tables made it easy to identify break-even points
Conditional formatting improved interpretability of sensitivity analysis
Project By Ali Hamza Shaikh