Skip to content

analyticshamza/Optimizing-Profits-Using-Excel-What-If-Analysis

Folders and files

NameName
Last commit message
Last commit date

Latest commit

Β 

History

3 Commits
Β 
Β 
Β 
Β 
Β 
Β 

Repository files navigation

πŸ“Š 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.

image

πŸ”§ 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

image

πŸ“· Screenshot: Scenario Summary Output

image

🎯 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 22 12 2025_00 14 14_REC

πŸ“· Screenshot: Goal Seek Result 22 12 2025_00 14 44_REC

πŸ“ 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

image

πŸ“ˆ 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

About

No description or website provided.

Topics

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published