Can this event supply order stay under $60?
See how each decision connects to evidence. For your own project, use truthful experience or clearly labeled practice data.
Order 66 units across five item types for $52.00; $8.00 remains.
The fictional order fits the $60 limit: buy 12 notebooks, 20 pencils, 20 folders, four marker packs, and 10 glue sticks for $52. Buy no erasers because 35 are on hand and only 30 are needed. Keep the remaining $8 unspent until quantities and final prices are confirmed.
| Item ID | Item | On hand | Needed | Unit cost | Order | Cost | status |
|---|---|---|---|---|---|---|---|
| S01 | Notebooks | 18 | 30 | USD 1.25 | 12 | USD 15.00 | Order |
| S02 | Pencils | 40 | 60 | USD 0.20 | 20 | USD 4.00 | Order |
| S03 | Folders | 10 | 30 | USD 0.60 | 20 | USD 12.00 | Order |
| S04 | Erasers | 35 | 30 | USD 0.30 | 0 | USD 0.00 | Enough in stock |
| S05 | Marker packs | 8 | 12 | USD 2.50 | 4 | USD 10.00 | Order |
| S06 | Glue sticks | 5 | 15 | USD 1.10 | 10 | USD 11.00 | Order |
Buy the gap, not the whole target
Notebooks need 30 units, but 18 are already on hand. MAX(30 − 18, 0) gives an order of 12, costing $15.
An oversupply is not a negative purchase
There are 35 erasers for a target of 30. MAX(30 − 35, 0) gives zero; subtracting the five extras from the bill would understate cost.
The summary leads to a decision
A $52 order leaves $8 under the $60 limit. The decision names the actual quantities and the assumptions to confirm, so a new user knows what to do next.
Three views for the decision
Order list: Filter the status column to Order to show which items to buy and their orderUnits quantities. Enough in stock means no purchase is needed for that item.
Budget summary: Shows the $60 limit, $52 total, and $8 remaining together.
Stock coverage: Compare on-hand + order units against required units; every row meets its target and erasers retain five spare units.
Formulas and checks
Order units = max(needed − on hand, 0). Order cost = order units × unit cost. Remaining budget = $60 − total order cost. This example assumes all-in unit prices and no pack-size constraints.
Expected results for this fictional example. These checks have not run against your workbook.
Set up the Excel table and budget cells
Name the six-row Excel table Supply. Its exact column headers, in order, are:
itemId, item, onHandUnits, neededUnits, unitCost, orderUnits, orderCost, statusKeep these literal headers if using the structured formulas. Friendly display labels in the website do not automatically become Excel column names. A1 versions use the six example rows only. When adding records, expand every fixed A1 range or use the table formulas.
- Dashboard!B2: set the input to 60 and name this cell
orderBudget. - Dashboard!B3:
=SUM(Supply[orderCost])· expected value: 52 - Dashboard!B4:
=orderBudget-SUM(Supply[orderCost])· expected value: 8
Three calculated columns · one formula in every row
Enter these in the first data row of the named table. Excel normally fills each calculated column automatically; confirm that all six rows contain its formula. The example has five input columns followed by these three calculated columns.
orderUnits=MAX([@neededUnits]-[@onHandUnits],0)orderCost=ROUND([@orderUnits]*[@unitCost],2)status=IF([@orderUnits]=0,"Enough in stock","Order")
status displays Order when a purchase is needed, or Enough in stock when orderUnits is zero. Filter this same column to Order.
Where checks go: Whole-table checks are calculated once in separate summary cells outside Supply. Per-row checks go in additional check columns inside Supply and must cover every data row. They are separate from the three calculated project columns above.
Row count
Whole-table check · calculate once
Excel formula:
=ROWS(Supply[itemId])Cell-reference alternative
=COUNTA(Supplies!A2:A7)Calculate this once in a summary cell outside the source table. Keep the fixed ranges matched to the six example rows.
Expected result: 6
Compare the result with six independently counted stock records. This assumes one nonblank ID per source row; also check blank and duplicate IDs. The A1 range covers this six-record example only.
Unique IDs
Per-row check · every record
Formula in each table row:
=AND([@itemId]<>"",COUNTIF(Supply[itemId],[@itemId])=1)Cell-reference alternative
=AND(A2<>"",COUNTIF($A$2:$A$7,A2)=1)Use the sheet layout above and copy this check down all six example rows.
Expected result: TRUE in every row
Add a check column inside Supply and fill the formula through all six rows. In the A1 version, enter the first formula in Supplies!I2 and copy through I7. Every result must be TRUE. A duplicate flag is a reason to compare with the original stock record, not invent a new ID.
Order-cost control total
Whole-table check · calculate once
Excel formula:
=SUM(Supply[orderCost])Cell-reference alternative
=SUM(Supplies!G2:G7)Calculate this once in a summary cell outside the source table. Keep the fixed ranges matched to the six example rows.
Independent calculation:
12×1.25 + 20×0.20 + 20×0.60 + 0×0.30 + 4×2.50 + 10×1.10 = 15 + 4 + 12 + 0 + 10 + 11 = 52Expected result: 52
Calculate each required shortfall and cost from the original quantities and unit prices, then compare the independently calculated $52 with the formula result. On a refresh, recompute the independent total.
No negative order quantity
Whole-table check · calculate once
Excel formula:
=COUNTIF(Supply[orderUnits],"<0")Cell-reference alternative
=COUNTIF(Supplies!F2:F7,"<0")Calculate this once in a summary cell outside the source table. Keep the fixed ranges matched to the six example rows.
Expected result: 0
Count order quantities below zero. Zero negative values is the expected result. This check alone does not detect blank or nonnumeric entries.
Coverage after order
Per-row check · every record
Formula in each table row:
=[@onHandUnits]+[@orderUnits]>=[@neededUnits]Cell-reference alternative
=C2+F2>=D2Use the sheet layout above and copy this check down all six example rows.
Independent calculation:
Stock after the order is 30, 60, 30, 35, 12, and 15 units against targets of 30, 60, 30, 30, 12, and 15.Expected result: TRUE in every row
Add a coverage check column inside Supply. In the A1 version, enter the first formula in Supplies!J2 and copy through J7. Inspect every row; each result must be TRUE.
Budget balance
Whole-table check · calculate once
Excel formula:
=ROUND(SUM(Supply[orderCost])+Dashboard!B4,2)Cell-reference alternative
=ROUND(SUM(Supplies!G2:G7)+Dashboard!B4,2)Calculate this once in a summary cell outside the source table. Keep the fixed ranges matched to the six example rows.
Independent calculation:
52 order cost + 8 remaining = 60 budgetExpected result: 60
With the explicitly mapped dashboard cells in the setup above, add displayed order cost and displayed remaining budget, then compare with the named orderBudget input ($60). Because remaining budget is derived from cost, this is a reconciliation of dashboard cells; the independent $52 cost check establishes cost accuracy.
Formula reference: Microsoft: COUNTIF · Microsoft: Structured references in Excel tables ·
Read-me: refresh and hand off
- Keep the original source sheet unchanged and date the refreshed copy.
- Update the inventory, needed quantities, and confirmed unit costs in the Supply table. Add new items inside that table, with a new unique ID; do not type new records below an unrelated fixed range.
- Keep orderUnits, orderCost, and status as calculated columns. Check that each formula fills every new table row; do not type over a formula with its current result.
- Check row counts against the refreshed source, duplicate IDs, nonnegative quantities and prices, stock coverage, the manual order-cost total, and the budget balance.
- Read the updated order list and budget summary, then rewrite the decision if the order exceeds the limit or assumptions changed.
Start on the read-me: update the named budget input and the five input columns; the three formula columns and dashboard recalculate. Dates, units, fictional source labels, limits, and control checks stay visible. No named learner or coworker is claimed to have tested this example.