π 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