Solver for optimization in Excel - Dashboard Guide

Dashboard Mode - Solver for optimization

Goal

Find the best number of products to produce to maximize profit without exceeding resource limits.

Sample Data

Product	Profit per Unit ($)	Resource Used per Unit (hours)
Product A	40	2
Product B	30	1

Resource Limit: 100 hours available

Dashboard Components

• KPI Card: Total Profit
  Formula: =SUMPRODUCT(B2:B3, D2:D3)
  Shows total profit based on units produced.
• Input Table: Units to Produce
  Cells D2 and D3 where user or Solver inputs number of units for Product A and B.
• Resource Usage: Total hours used
  Formula: =SUMPRODUCT(C2:C3, D2:D3)
  Shows total resource hours used by production plan.
• Constraint: Resource Usage ≤ 100 hours

Dashboard Layout

+----------------------+--------------------+
|      KPI: Profit      |  Input: Units Prod  |
|  (Total Profit $)     | Product A | Product B |
+----------------------+--------------------+
|   Resource Usage      |                    |
|  (Hours used / 100)   |                    |
+----------------------+--------------------+

Interactivity

User changes units to produce in cells D2 and D3. Solver adjusts these values to maximize total profit while keeping resource usage ≤ 100 hours.

Solver setup:

• Set Objective: Maximize cell with total profit formula.
• By Changing Variable Cells: Units to produce (D2:D3).
• Subject to Constraint: Resource usage ≤ 100 (cell with resource usage formula).

Self Check

If you add a filter to limit Product B units to 20 max, which components update?

• The units input for Product B (D3) is limited.
• Total profit recalculates based on new units.
• Resource usage updates accordingly.

Key Result

Dashboard to optimize product units for maximum profit within resource limits using Solver.