Optimizing Logistics with Real-Time Margin Analysis for an Australian Linen and Garments Company

1. Introduction

Commercial laundry and linen rental is a recurring revenue business. Hotels, hospitals, aged care homes and restaurants receive linen and workwear on a regular delivery schedule, and they are invoiced week after week for the same service. The revenue on any single delivery is small. The margin depends almost entirely on operational detail such as how much the linen weighs, how many trolleys that fills, which depot serves the site, how the items are packed, and how many days a week the delivery vehicle arrives.

An Australia based laundry company was running Microsoft Dynamics 365 Sales for its pipeline, but the actual costing of each quote happened outside the system in a large Excel workbook. The workbook worked, and it had years of accumulated knowledge inside it, yet it sat entirely outside the process that the business managed and reported on.

This article walks through why that arrangement became a problem, how the costing logic was rebuilt on the standard Dynamics 365 Quote entity, and what changed for the sales team as a result. The focus is on the shape of the solution rather than the arithmetic behind it.

2. The Business Problem

Every quote began as a copy of the workbook. The sales team pasted in the product catalogue, removed the rows that were not required, looked up the rate for the depot serving that customer, checked how the customer received their linen because the packing rate depended on it, entered the delivery frequency, and then worked down through the cost build up until a margin percentage appeared at the top of the sheet.

When the numbers looked acceptable, roughly twenty summary values were typed back onto the Quote record in Dynamics so that the pipeline reported something meaningful. The quote itself was then produced from the spreadsheet.

Figure 1: The previous quote costing process, carried out by hand in Excel for every opportunity.

Several problems followed from this. Rates lived inside spreadsheet cells, so updating a depot rate meant updating every copy of the file that anyone happened to be working from. Quality control lived in a column of written reminders alongside the calculation, which meant each check worked exactly as well as the attention of the person reading it. Testing an alternative, such as reducing the number of delivery days, meant working through the sheet again from the beginning.

The consequence that mattered most to management was simpler. Because the summary figures were typed in by hand, the margin shown on the Quote in Dynamics did not always agree with the margin the business had actually approved. Once a reported number is not trusted, people stop looking at it and go back to the spreadsheet, and the CRM becomes a filing cabinet.

3. The Solution

The Quote and Quote Product entities were kept exactly as Dynamics provides them. No separate pricing application was built, and no parallel record structure was introduced. Products are still added to the quote in the normal way, and the quote is still created from the opportunity in the normal way.

What changed is what the record knows and what it does without being asked. Custom fields on the Quote hold the service details that Dynamics does not ship with, such as the depot serving the site, how the linen is packed, and how many delivery days a week the customer receives. Custom fields on the Quote Product hold the unit cost and unit weight of each item. Two small reference tables hold the rates. Server side and client side logic then produce every derived figure.

Figure 2: The current process. The sales team describes the customer and the service, and Dynamics 365 produces the cost and margin figures.

The sections below describe each part of the solution and the reason it was built that way.

3.1 Moving Rates into Dynamics Reference Tables

In the workbook, the rate for a depot was a number typed into a cell, and the packing rate was decided by a formula that tested a piece of text describing how the customer received their linen. Both approaches worked while one person maintained the file. Neither survived being copied.

Two reference tables were created in Dynamics. One holds each depot with its associated rate. The other holds each packing type with its associated rate. The Quote carries a lookup to each of them.

When the sales team selects the depot on the quote, the rate is retrieved from that depot record and applied. When they select how the linen is packed, the same happens for the packing rate. Nobody types a rate, so nobody can mistype one. When operations revises a rate, it is revised once, on one record, and every quote raised from that point forward reflects it.

3.2 Cost and Weight on Every Quote Line

Cost and margin for this business cannot be worked out from price and quantity alone. Each item has a unit cost and a unit weight, and the weight is what drives most of the delivery and handling cost. The spreadsheet resolved both of these by matching the item description against a separate cost list, which meant the match depended on a text value that a user could edit, and some rows had been quietly overridden with values typed in by hand.

In Dynamics, unit cost and unit weight are fields on the Quote Product, populated from the product record when the line is created. The relationship between a line and a product is a real relationship rather than a text match, so the correct cost and weight arrive with the line and stay with it.

This also made a data quality issue visible rather than silent. A quote raised directly, instead of being generated from an opportunity, could arrive with lines that carry no unit cost. Zero cost means zero cost of goods, which produces an excellent and entirely fictional margin. The solution surfaces this condition on the record instead of quietly reporting a healthy number.

3.3 Where the Calculation Runs

The calculation runs in two places, for two different reasons.

Plugins handle the product lines. Adding, changing or removing a quote line alters the total weight and the total production cost, and those two values drive everything downstream. That recalculation runs server side on update of Quote Product, so the stored totals on the parent Quote are correct no matter how the line arrived. Whether someone added it through the form, imported it, or generated the quote from an opportunity, the outcome is the same. Anything that has to be true regardless of what touched the record belongs on the server.

JavaScript handles the form fields. When the sales team changes the depot, the packing type, the number of delivery days or a price adjustment, JavaScript recalculates on change so the result appears immediately. This is what keeps people on the record. Waiting for a save and a reload to find out whether a scenario works is precisely the friction that sent everyone back to Excel in the first place.

Figure 3: JavaScript gives the sales team an immediate response on the form. Plugins guarantee the stored figures are correct however the record was changed.

The principle held throughout was that client side script never owns a value the business reports on. It shows the consequence of a change that has not been saved yet. The plugin writes the number that is reported.

3.4 Turning Manual Reminders into System Behaviour

The most interesting part of the workbook was not its formulas. It was a column of about fifteen written reminders sitting alongside the calculation, telling whoever had the file open what to verify before trusting the result. Filter one set of rows first, then filter another set. Double check the depot rate. Double check how the customer is packed. Make sure only one delivery costing method has been applied. Make sure the service charge row is visible and has been checked. Mark the row once it has been verified against the current contract. Record who checked it.

That column was the entire control environment for pricing. Every check in it worked exactly as well as the attention of the person reading it late on a Friday afternoon.

Rebuilding the arithmetic was the straightforward half of this project. The valuable half was working out what each reminder was protecting against, and deciding where that protection belonged.

Two of those reminders turned out to be guarding against genuine ambiguity in the workbook rather than user error. One alternative costing method had been built and left unconnected, so a quote missing a particular rate could cost that element at nothing and report a margin that looked excellent. Another total combined two options that were meant to be mutually exclusive, with a written note two columns away instructing a human to use only one of them. Both were resolved as explicit, visible decisions on the Quote rather than inherited quietly into code.

3.5 Margin Visibility While the Quote Is Being Built

The point of automating the calculation was never the calculation. It was to put the answer in front of the person making the decision, while the decision is still open.

The Quote now shows the margin against target, the cost build up that produced it, and the contribution of each individual line. Because the figures move as inputs change, the sales team can see the consequence of a commercial choice immediately. Reducing delivery frequency lowers the number of trips, and it also concentrates more weight into each delivery, which can push the trolley requirement up. That trade off was always present in the business. It was previously buried several steps into a spreadsheet, where nobody looked at it during a negotiation.

Line level contribution had a similar effect. When a quote came in below target, the previous process gave a single unsatisfactory number at the top of a sheet. Now the specific items dragging the deal down are identifiable, which turns a vague problem into a short conversation about a handful of products.

4. Implementation

The solution was delivered on the standard Dynamics 365 Sales Quote entity. Custom fields on the Quote hold the service inputs and the derived cost and margin values. Custom fields on the Quote Product hold unit cost and unit weight. Two custom tables hold the depot rates and the packing rates, each referenced from the Quote by a lookup.

Plugins registered on update of Quote Product recalculate the totals that depend on the product lines. JavaScript registered on the change events of the relevant Quote fields recalculates the derived values on the form so the sales team receives immediate feedback. The derived values written by the plugin are the values used for reporting.

The most important part of the delivery approach was sequencing. Before building anything, the existing calculation was reconciled against the workbook, and the first version deliberately reproduced the numbers the business already had, including the behaviour we believed to be wrong. Each known difference was then resolved one at a time, in the open, with the business present.

This mattered because a pricing tool that arrives with better numbers than the ones the business currently uses is simply a fourth opinion in a room that already holds three. Starting from agreement, and then moving deliberately, is what turned the output into something the pricing team was willing to rely on.

5. Business Impact

Quote costing now happens on the Quote. The sales team describes the customer and the service using a small number of fields, and the system produces the cost and margin figures. The workbook remains a reference for why the logic is what it is, but it is no longer where work is done, and it is no longer copied for every opportunity.

Because the figures are derived rather than retyped, the margin reported in Dynamics is the margin the business actually discussed. That single change is what restored confidence in the number, and confidence in the number is what brought people back to the record.

There was also a benefit that was never part of the original brief. Because the spreadsheet relied on lookups across the customer base, every copy of that quote file carried a large volume of unrelated customer information with it. A Quote record needs the data of one customer. Moving costing into Dynamics removed an entire category of unnecessary data circulation.

6. Frequently Asked Questions (FAQs)

7. Conclusion

The instinct when pricing lives in a long standing spreadsheet is to admire the formulas and rebuild them exactly. In this project the formulas were the least of it. The workbook was not really a specification. It was a record of every decision nobody had found time to finish, including an alternative calculation that had been built and never connected, a total that combined two options meant to be mutually exclusive, and a column of reminders standing in for a control environment.

Extending the standard Dynamics 365 Quote with the right fields, two reference tables, plugins for correctness and a small amount of client side script for responsiveness meant that quote arithmetic stopped being anyone’s job. What remains for the sales team is the part that was always theirs, which is choosing the depot, the delivery frequency and the price, with the consequence of each choice visible while they make it.

For any organisation running a recurring revenue service model, the lesson generalises. If the pricing knowledge sits in a spreadsheet and the pipeline sits in CRM, the work worth doing is in the gap between them. Reconcile first, then automate.