Sales and operations planning dashboard in Excel

A sales and operations planning dashboard in Excel reconciles demand, inventory and feasible production by product family. Start with the inventory balance, calculate production required, and compare it with the capacity each family can actually use. The useful output is a shortage and a decision owner, alongside the totals.

Set up a planning table with clear inputs

Use a monthly plan for a US equipment manufacturer. Each family below uses a dedicated production resource, and the finished units within each family are treated as equivalent for this illustration. The exercise assumes materials are available and production can occur before demand is due; check those assumptions separately before approving the plan.

Keep the approved demand version, inventory snapshot date and receipt cutoff visible. Confirmed receipts are finished goods available during the month; they exclude the production being calculated, which prevents double counting.

Available hours should already allow for planned downtime and agreed staffing limits. Do not enter theoretical machine time while applying a separate productivity adjustment to only some families. The inputs need one common interpretation.

Use sales planning to define the commercial assumptions behind demand. This dashboard accepts that demand version and tests its supply implications.

Work the demand and supply balance

These are the November 2026 inputs. Family units are separate planning quantities; a unit of A cannot satisfy demand for B.

With this input table in A1:G4, enter these formulas in H2:M2, in order, and fill down:

=MAX(0,B2+E2-C2-D2)
=ROUNDDOWN(G2/F2,0)
=MIN(H2,I2)
=C2+D2+J2-B2
=MAX(0,E2-K2)
=J2*F2

These formulas require positive numeric hours per unit and nonnegative numeric inputs. Flag blanks for review before calculating. Whole units are rounded down for capacity; displayed utilization percentages below are rounded to one decimal.

The zero in H2 prevents a surplus inventory position from becoming negative production. Microsoft documents MAX as returning the largest supplied value, which makes that floor explicit. MIN returns the smallest value, so J2 limits planned production to the lower of need and capacity.

Utilization is planned hours divided by available hours:

=IF(G2=0,NA(),M2/G2)

For B, required production is 800 + 80 - 100 - 0 = 780 units. Capacity is 600 / 1.0 = 600 units. Its projected ending position is 100 + 0 + 600 - 800 = -100 units, indicating unmet demand. The 180-unit shortfall includes that demand shortage and the 80-unit ending inventory target.

Prove the balance before approving a scenario

For every family, the following identity must hold:

Opening inventory + Confirmed receipts + Feasible production - Demand = Projected ending inventory

Use a check column that must return zero:

=C2+D2+J2-B2-K2

Also check the source inputs independently: opening units against the stock availability report, receipts against dated supplier confirmations, and available hours against the operations-approved resource calendar. A mathematically balanced plan can still rely on an unconfirmed delivery.

Retain the unconstrained requirement in H even when capacity limits production. Replacing required production with feasible production would erase the reason the plan needs attention. Keep the demand owner, supply owner and approval date next to each changed assumption.

For a single-input variation, increase B's available hours from 600 to 780 while holding everything else constant. Capacity and feasible production both become 780 units; projected ending becomes 100 + 780 - 800 = 80 units, exactly the target. Its shortfall becomes zero. This scenario only works if operations confirms the additional usable hours and the required materials and timing.

Build the review page around the decisions

Display a family comparison of required versus feasible production, a table of projected ending inventory against target, and a short list of unresolved constraints. Family B is the immediate review item. Ask whether the business can obtain approved capacity, change supply timing or revise demand commitments.

Avoid a company utilization card as the main answer. Spare time on A or C's dedicated resource cannot clear B's shortage. Show the constrained resource alongside the family before proposing a transfer.

For presentation choices, the Excel dashboard examples show common ways to organize a management view. A sales territory planning dashboard addresses a different decision: coverage and commercial capacity across territories. Keep that separate from this production balance.

Where it goes wrong

• Counting the same supply twice. A production receipt included in confirmed receipts must not also enter feasible production.
• Assuming capacity transfers. Hours on a resource that cannot make the constrained product do not solve its gap.
• Hiding shortages with a zero floor. Keep negative projected ending positions visible as planning exceptions. Floor production need, not the ending balance.
• Using month totals as a schedule. A late receipt cannot cover early demand merely because both occur in November. Review time buckets when timing is tight.
• Treating targets as demand. Keep customer demand and inventory policy in separate columns so managers can distinguish the service gap from the stock buffer.

Covirage is not affiliated with Microsoft.

Reconcile your planning file in Covirage

Bring the workbook containing demand, opening inventory, confirmed receipts and operations-approved capacity assumptions. Covirage maps the columns; its tools compute and check the comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.

See capacity planning with Covirage for the supporting business context.