Work sample 12 / 17Pricebook and margin
Pricebook re-costing for an electrical contractor, after three supplier price rises
How I'd approach a Melbourne electrical contractor whose simPRO pricebook was last updated in January, while three supplier price rises since then have taken margin off fixed-price jobs that nobody noticed.
- Area
- Pricebook and margin
- Tools
- simPRO · Excel · Xero
- Typical scope
- US$1,200–2,000
- Timeline
- About 4 weeks, 4 milestones
Work sample: realistic data, not a past client job. Amounts are in AUD and GST is 10%.
01The problem
An electrical contractor sells switchboards, circuit protection, cable and lighting. Its three main suppliers raised prices in February, May and August 2026. The simPRO pricebook still holds the January cost and sell prices, so every quote and every fixed-price job is built on costs that are out of date.
In this sample, the pricebook has 1,400 lines and was last updated in January. Against current supplier prices, none of the 1,383 active lines had a cost within 1% of the supplier's file, and 1,382 lines sat more than one percentage point below their group's target margin. The last six months of billing (April to September 2026) is 11,000 lines, $1,835,368 ex GST, billed at a margin of 30.9%.
Owners usually notice it as
- Jobs that looked fine at quote stage finish with a thinner margin than expected
- Nobody can say what a part costs today without ringing the supplier
- Supplier price files arrive by email and are never loaded
- The same part is sold at the same price whether it cost $4 or $5 to buy
02The outcome
This is what the setup does and delivers:
- Every pricebook line compared with the current supplier price, with a clear status: current, superseded or discontinued
- A short set of cost and margin rules by group, so a new sell price is calculated the same way every time
- A re-costed import file that updates existing simPRO items by part number only, with a backup of the catalogue exported first
- An approval list for the large changes, with the decision and the reason for each recorded
- A report showing what the last six months of billing would have earned at the new prices
- A repeatable quarterly routine, so the next price rise takes an hour rather than a project
03Scope
| Deliverable | What's included | Notes |
|---|---|---|
| Pricebook and supplier file audit | Pricebook exported from simPRO; the three supplier price files matched line by line; superseded, discontinued and unmatched parts listed | US$300 health check, and the findings are yours to keep. If the findings change the plan, I re-quote before M2 |
| Cost and margin rules | Cost basis, target margin by group, competitive-item rule, rounding, approval thresholds | Agreed with the owner in M1 |
| Re-costed import file | Import file with current cost and new sell price for every active line | Built in Excel. Imported with Update Only, matched on part number |
| Margin impact on the last 6 months | Billed lines re-priced at the new sell prices, by group and by month | An estimate of what could have been earned. Fixed-price jobs already accepted do not change |
| Approval list for big changes | Lines with a large change listed with proposed price, decision and reason | The owner decides. Nothing large is imported without a recorded decision |
| Quarterly update SOP | One-page routine for the next price file, with the check steps | Includes the Xero bill price cross-check |
| Not included | Supplier negotiations, new supplier set-up, or rebuilding the catalogue structure | Quoted separately if wanted |
Not included: payroll, STP or BAS lodgement.
| Item | Volume |
|---|---|
| Pricebook lines | 1,400 in 9 groups |
| Supplier price files | 3 (676 + 385 + 382 rows) |
| Lines superseded by the supplier | 31 |
| Lines discontinued by the supplier | 17 |
| Billed lines, April to September 2026 | 11,000 on 1,000 jobs ($1,835,368 ex GST) |
| Xero bill lines cross-checked | 60 on 13 bills |
04Walkthrough
4.1 The three price rises
Each supplier's rises hit some groups harder than others. Cable moved the most. The table shows the rise in each round, the combined rise, the target margin for the group, and the average margin the old sell price now earns at today's cost:
4.2 The three supplier files
Each file is matched on part number to the pricebook:
For renumbered parts the supplier's new number is recorded in a notes column, and the import uses the part number that simPRO already holds so that Update Only matches the existing item.
4.3 Cost and margin rules
4.4 Example lines
4.5 The approval list
279 lines changed by more than the rules allow, and went to the owner. 1,104 lines were within the rules and 17 are archived.
Largest proposed changes (per unit):
A capped line is brought up to the full price at the next quarterly update. A held line is listed with a review date, so it does not stay low by accident.
4.6 The import
- Export the existing catalogue from simPRO (Export > Catalogue) and keep the file as the backup
- Run a trial of 20 lines through Utilities > Import, with Update Only selected, and check the result in simPRO against the file
- Import the rest. Update Only matches the part number and updates existing items. Rows that do not match are ignored, so nothing new is created
- Compare the new catalogue export with the import file and record the numbers
The import file has the part number, description, cost price and sell price for each active line. If the business prefers simPRO to calculate sell from a markup, the file carries the markup instead, and the trial import confirms which columns the import accepts on that setup. In this sample the file has 1,383 rows, one for every approved active line. The 17 archived lines are not in it.
4.7 Margin impact on the last six months
The 11,000 billed lines from April to September were billed at the old sell prices. Each line's cost is the supplier price at the date of the job, so the cost rises step up in May and August. The "new" columns show the same lines at the re-costed sell prices:
By month:
This is an estimate of what the re-costed prices would have earned. Jobs quoted at a fixed price before the rises cannot be re-priced, so the real recovery is spread over new quotes from here.
One job, before and after: Switchboard upgrade, Moonee Ponds (job EJ4049).
4.8 Cross-check against Xero bills
Prices in the pricebook are checked against what the suppliers actually charged. A Xero export of September's bill lines is matched by part number to the new pricebook cost, and any line more than 1% away is raised with the supplier:
57 of 60 lines were within 1% of the new cost. 3 were above it, and went back to the supplier. The detail is in xero_bill_crosscheck.csv.
4.9 The quarterly routine
- Ask each supplier for a current price file, or download it on the first Monday of the quarter
- Export the simPRO catalogue as a backup
- Paste the files into the workbook, which matches them on part number and applies the rules
- Review the approval list and record each decision
- Run a trial import of 20 lines, then the full import with Update Only
- Cross-check a sample of recent Xero bill lines against the new cost
- Save the workbook, the import file and the approval decisions together
05Checks
| Check | Before | After | Status |
|---|---|---|---|
| Every line has a current cost | 0 of 1,383 within 1% of the supplier file | 1,383 of 1,383 active lines (17 archived with a reason) | PASS |
| Sell price meets target margin or has a reason | 1,382 of 1,383 more than 1 point below target margin | 1,066 at or above target, 317 below target with a recorded reason (255 competitive, 38 capped, 24 held), 0 without | PASS |
| Import count = approved count | Not tracked | 1,383 rows in the import file, 1,383 lines approved | PASS |
| Xero bill prices agree with the new cost | Not compared | 57 of 60 within 1%, 3 raised with the supplier | PASS |
06Milestones
| Milestone | What happens | Rough duration | What is signed off | Share |
|---|---|---|---|---|
| M1 · Rules agreed | Health check (US$300), pricebook and files audited, margin rules, competitive items and approval thresholds agreed | About 1 week | Audit findings, rules and fixed quote | 25% |
| M2 · Re-costed file | Supplier files matched, sell prices calculated, margin impact on the last six months, approval list built | About 1.5 weeks | The re-costed workbook and the approval list | 40% |
| M3 · Approval and import | Owner decisions recorded, catalogue backed up, trial import, full import and count check | About 1 week | Import count equals approved count | 25% |
| M4 · SOP | Quarterly routine written, one test run on a small price file, walkthrough recording | About 0.5 week | Office can run the next update | 10% |
The fixed quote follows the US$300 health check, so the price is based on what is actually in your files. If the health check changes the plan, I re-quote before M2.
Typical scope for this kind of project: US$1,200–2,000.
About 4 weeks, 4 milestones. Your software subscriptions are billed to you by the vendors.
07Handover
- The re-costing workbook (Excel) with the rules and the group margins
- The import file and the approved decision list
- A backup of the catalogue before the import
- The margin impact report
- A one-page quarterly SOP
- A recorded walkthrough
- Prepared by Manaz Afzal
08Files
A screen from the sample workbook, then every file in the work sample. Select the image to open it full size.
pricebook-recosting.xlsx › Summary
| File | What it is | Size |
|---|---|---|
| pricebook-recosting.xlsx | Workbook with 14 tabs (Read me, Summary, Pricebook, Approval list, Import file, Billed lines Apr-Sep, Xero cross-check, Xero bills, Margin by group, Margin by month, Decisions, Supplier file A, Supplier file B, Supplier file C). The Summary tab lines up every figure on this page with a live formula | 1.5 MB |
| pricebook-recosting.pdf | This work sample as an A4 PDF (8 pages) | 354.8 KB |
| pricebook_before_after.csv | 1,400 pricebook lines with old and new cost, old and new sell, margin and reason | 253.2 KB |
| supplier_price_file_A.csv | The three supplier price files (A) | 55.9 KB |
| supplier_price_file_B.csv | The three supplier price files (B) | 35.7 KB |
| supplier_price_file_C.csv | The three supplier price files (C) | 33.5 KB |
| simpro_catalogue_import.csv | 1,383 rows for the Update Only import | 87.6 KB |
| approval_list.csv | 279 lines that went to the owner, with the decision | 36.1 KB |
| billed_lines_apr_sep_2026.csv | 11,000 billed lines with cost at date and the re-costed sell | 1.3 MB |
| xero_bill_crosscheck.csv | 60 bill lines checked against the new cost | 5.5 KB |
Tell me what’s bugging you and I’ll tell you where I’d look first.
Send a short note on Upwork about what is slowing the business down. I’ll reply with where I’d start, and whether the US$300 Systems Health Check is the right first step.