Equipment quotation
Migration blueprint
What the workbook calculates, the rules a replacement must preserve, and the decisions the business still needs to make.
Synthetic demonstration. This report describes the supplied illustrative workbook. It is not a customer case study. Observed formulas describe current workbook behavior, which may differ from approved business policy.
Download the source workbook. All references below refer to its Quote sheet. The current workbook has three order lines and editable discount/freight assumptions. It excludes tax.
1. Operational flow
Order lines + discount policy → line gross → line discount → line net → net goods total + freight → quoted total
The quantity threshold applies to each line. It does not add all quantities together to determine discount eligibility. Freight is determined after discounts. These two details should remain visible in the replacement specification.
2. Source-linked rule register
| ID / source | Observed behavior | Formula evidence |
|---|---|---|
Q1Quote!D13:D15 | Each gross line amount is quantity multiplied by unit price. | D13 = B13*C13The same relative pattern appears on rows 14 and 15. |
Q2Quote!E13:E15Quote!B7:B8 | At a line quantity of at least 10, apply a 10% discount. Below 10, apply zero discount. | E13 = IF(B13>=$B$7,$B$8,0)B7 = 10; B8 = 0.10 |
Q3Quote!F13:G15 | Discount amount equals gross multiplied by the discount rate. Net equals gross minus that amount. | F13 = D13*E13G13 = D13-F13 |
Q4Quote!B18:B19Quote!B9:B10 | Sum the net line amounts. Net goods at least USD 1,000 receives zero freight. Below the threshold, freight is USD 45. | B18 = SUM(G13:G15)B19 = IF(B18>=$B$9,0,$B$10)B9 = 1000; B10 = 45 |
Q5Quote!B5 | The quoted total includes net line amounts plus freight. No tax calculation is present. | B5 = SUM(G13:G15)+B19 |
Q6Quote!B20 | Total units sum all three line quantities. This result does not drive discount eligibility. | B20 = SUM(B13:B15) |
3. Reconcile the current example
| Line | Gross | Discount | Net |
|---|---|---|---|
| WIDGET-A: 10 × USD 80 | USD 800 | 10% / USD 80 | USD 720 |
| WIDGET-B: 5 × USD 60 | USD 300 | 0% / USD 0 | USD 300 |
| SERVICE-C: 1 × USD 200 | USD 200 | 0% / USD 0 | USD 200 |
| Net goods + freight | Freight: USD 0 | USD 1,220 |
4. Proposed replacement data model
These entities are design proposals, not objects found inside the workbook.
- Quote: identifier, status, currency, line collection, applied policy version, net goods total, freight and total. Customer identity and quote expiry require clarification.
- Quote line: SKU, quantity, unit price, gross, discount rate, discount amount and net amount. Keep derived amounts separate from editable inputs.
- Pricing policy: discount quantity, discount rate, free-freight threshold and freight charge. Confirm who can change policy and whether old quotes retain their original policy.
5. Decisions needed before a fixed-price migration
| Question | Why it changes the implementation | Evidence / gap |
|---|---|---|
| Is the discount intentionally per line, including service SKUs? | A product/category policy or quote-wide discount would need different logic and data. | Every row uses the same formula at E13:E15, including SERVICE-C. |
| Must line amounts be rounded to cents before totals, or only for display? | Rounding order can change totals and reconciliation. | Currency display uses two decimals, but no ROUND appears in the formulas. |
| Are zero/negative quantities and prices valid? | Returns, credits and invalid-input handling need explicit rules. | The basic input cells have no numeric validation rule in this demonstration. |
| Does freight depend on destination, item class or delivery method? | Additional pricing tables and integrations may be needed. | B19 uses only net goods total and two assumptions. |
| Who may edit prices and approve discounts? | Role permissions, approval states and audit history affect scope. | No roles or approval workflow are represented. |
| Should historical quotes preserve the policy in force when created? | Versioned pricing and immutability may be necessary. | Editing B7:B10 immediately changes the current quote. |
| What tax, expiry and customer-specific pricing apply? | These are separate scope decisions, not features established by the workbook. | Tax, quote expiry and customer pricing data are absent. |
6. Draft acceptance examples
The first four preserve observed behavior. The remaining cases require a business decision before an expected result can be approved.
- Quantity boundary: a line with quantity 9 and unit price USD 80 has gross/net USD 720 and zero discount. At quantity 10, gross is USD 800, discount USD 80 and net USD 720.
- Freight boundary: net goods exactly USD 1,000 gives freight USD 0. Net goods USD 999.99 gives freight USD 45.
- Per-line eligibility: two lines of five units do not receive a discount merely because total units equal ten.
- Current reconciliation: the supplied three lines give net goods USD 1,220, freight USD 0 and total USD 1,220.
- Rounding decision: confirm approved outcomes for fractional unit prices before codifying cents precision.
- Validation decision: decide whether negative quantities/prices are rejected or treated as returns.
- Policy version decision: decide whether a saved quote changes when the active pricing policy is edited.
7. Coverage and verification
Reviewed all 16 formula cells in one visible worksheet, the editable assumptions and three order lines. The exported sample contains no macro project or external workbook links. Formula results and representative quantity/freight boundaries were recalculated using Artifact Tool. Every sheet was rendered and visually inspected. Native Microsoft Excel recalculation was not available for verification.
What this does not establish: employee workarounds, correct business policy, production system behavior, supplier rules or the completeness of an application specification. This report gives a developer a concrete starting point and a business owner a precise decision list.