Blog · AI and self-service analytics
Data-driven decision making starts with a question, comparable options and reconciled files. Follow a shipping-cost example from sales and cost exports through spreadsheet formulas, sensitivity checks and an accountable decision.
Data driven decision making in business means comparing realistic options with figures that reconcile to their source files, then choosing an action with a named owner. Start with the decision and its time horizon. A report becomes useful when it shows what changes between options, what stays uncertain and what evidence would change the choice.
Write a short decision statement: “Should our US distribution business switch to consolidated shipping for the next quarter?” Define the alternative, approval date, service requirements and costs within scope.
Separate the person calculating the comparison from the person authorizing the change. Operations owns the delivery assumptions, finance validates the cost treatment, and the budget owner decides. The same distinction matters when getting an analytics purchase approved.
Use contribution after product cost and freight for this example. Rent, corporate salaries and other costs unchanged by the choice are outside the comparison. That measure is not company net income.
Take the last closed month's posted sales quantities, net sales and product costs. Add the current freight invoice total and the proposed freight quote. Record the one-time setup fee separately.
Before joining anything, confirm that customer returns, canceled orders and credits are treated consistently. A shipment-level file can contain several rows for an invoice. Adding the full invoice revenue to every shipment would duplicate sales.
Keep the historical actuals and the option assumptions in separate columns or sheets. The proposed freight quote is an input to a decision, not a booked expense. If the choice concerns customer prioritization instead, use the more specific method for tiering accounts from your own data.
The fictional distributor sold the following products in the closed month. Assume the next month has the same volume and prices for the base case.
| Product | Units | Net unit price (USD) | Unit product cost (USD) | Net revenue (USD) | Product cost (USD) |
|---|---|---|---|---|---|
| A | 400 | 100 | 60 | 40,000 | 24,000 |
| B | 600 | 80 | 48 | 48,000 | 28,800 |
| Total | 1,000 | 88,000 | 52,800 |
For an Excel sheet named Sales with the columns shown, enter this revenue formula in E2:
=B2*C2
In F2, calculate product cost:
=B2*D2
Fill both formulas through row 3. In E4, total revenue:
=SUM(E2:E3)
In F4, total product cost:
=SUM(F2:F3)
If the underlying export includes multiple dates and statuses, sum only the closed period and posted rows. For a table named Export, this formula totals September 2026 revenue:
=SUMIFS(Export[Net revenue],Export[Posting date],">="&DATE(2026,9,1),Export[Posting date],"<"&DATE(2026,10,1),Export[Status],"Posted")
Microsoft's SUMIFS reference documents summing values that meet multiple conditions. The filtered sales and product-cost totals must match the accounting export for the same scope before proceeding.
The current shipping arrangement costs $6,000 per month. Consolidated shipping is quoted at $4,200 per month, with a $2,500 setup fee charged in the first month. The quotes are assumptions supplied for this example; they are not market benchmarks.
| Option | Revenue (USD) | Product cost (USD) | Freight (USD) | Setup (USD) | Contribution (USD) |
|---|---|---|---|---|---|
| Keep current shipping, month one | 88,000 | 52,800 | 6,000 | 0 | 29,200 |
| Consolidate shipping, month one | 88,000 | 52,800 | 4,200 | 2,500 | 28,500 |
| Consolidate shipping, later month | 88,000 | 52,800 | 4,200 | 0 | 31,000 |
With revenue in B, product cost in C, freight in D and setup in E, calculate contribution in F2:
=B2-C2-D2-E2
The first month is $700 worse after setup. The later monthly improvement is $1,800. Comparing only freight would hide the initial reduction in contribution.
Over a quarter with unchanged monthly demand, keeping the current arrangement produces 3 × 29,200 = $87,600. Consolidating produces 28,500 + 31,000 + 31,000 = $90,500. Its quarterly advantage is $2,900.
The setup fee divided by recurring monthly savings is 2,500 / 1,800 = 1.39 months, rounded to two decimal places. This is a simple recovery calculation, not a cash forecast: invoice timing and payment terms may differ.
If setup is in H2 and recurring savings in H3, show a missing result when savings are zero:
=IF(H3<=0,"No recovery",H2/H3)
Do not turn every calculation error into a zero. Microsoft's IFERROR documentation lists several errors it catches, including reference errors and division by zero. An explicit denominator check leaves other problems visible for correction.
Operations is concerned that slower delivery could reduce orders. Test a 5% volume decline under consolidated shipping, holding unit prices, unit product costs and quoted freight unchanged.
| Product | Revised units | Revenue calculation (USD) | Product cost calculation (USD) |
|---|---|---|---|
| A | 380 | 380 × 100 = 38,000 | 380 × 60 = 22,800 |
| B | 570 | 570 × 80 = 45,600 | 570 × 48 = 27,360 |
| Total | 950 | 83,600 | 50,160 |
Quarterly contribution becomes 3 × (83,600 - 50,160 - 4,200) - 2,500 = $85,220. That is $2,380 below the unchanged current option's $87,600. The base case supports a switch; this sensitivity shows that a modest volume effect could reverse the comparison.
The calculation does not establish that consolidation causes lost orders. It tells the owner which uncertainty deserves evidence before approval. Review historical delivery complaints or agree on a limited operational test with explicit service conditions.
Keep a short decision record containing the chosen option, comparison period, approved assumptions, evidence gaps, approver and review date. Save the exact exports used. Review actual freight, volume and contribution against those assumptions after the agreed period.
Avoid compressing that record into a single score. Where the decision concerns customer relationships, an account health score needs its own defined inputs and checks.
Covirage is not affiliated with Microsoft.
Bring the sales, product-cost and operating-cost files that define your comparison, with assumptions clearly identified. Covirage maps the columns, and its tools compute and check the figures; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See self-service analytics for comparing business results from the files your team already uses.
A distributor comparing shipping options can combine sales quantities, product costs, freight quotes and setup charges. It calculates contribution under each option and tests a lower-volume case before deciding. The decision record then states who approves the switch and when results will be reviewed.
Use the files that measure the decision's financial and operating consequences. Common inputs include posted sales, product costs, labor or freight expenses, customer service results and approved budgets. Give every input a period, source owner and definition before combining it with other data.
A calculation can show which option performs better under stated assumptions. A responsible owner still decides whether those assumptions are acceptable and whether service, capacity or other constraints permit the action. A favorable total does not remove those responsibilities.
Keep the original comparison and review actual results for the agreed period. Separate differences caused by volume, price, cost and timing. Compare outcomes with the assumptions used at approval so the team can revise its next decision using evidence.