The weight estimate
Possibly the least-loved, most important aspect of the discipline of design.
There’s not much point having the perfect vessel that sinks on launch. It has happened. It’s awkward.
The sum of the parts
The weight estimate is simply the ‘sum of the parts’ - it’s the list of all the things that go in to a boat, along with what they weigh and their centres of gravity.
How hard can it be?
At face value, it’s simple. However, in the revolutions around the design spiral, working against deadlines, with changing priorities and frequently with a changing design (clients are always right, right?) it can be complex and vexed, but it still must be correct.
Many final decisions are not made until late in the schedule (AV/IT is notorious as it’s non-critical from a regulatory standpoint and it is constantly evolving) and it’s frequently hard to capture the weight implications of these changes and of their associated rework - particularly if your team already has too many spinning plates.
If the weight estimate is wrong, potentially everything is wrong.
If your boat does not pass the stability requirements, then it’s not a boat, it’s a sculpture. A very expensive, land-based sculpture.
So what to do?
The following are a few, minimal suggestions - it’s possible (and worthwhile!) going way beyond what’s written here. Recording the provenance of the data for instance is important for auditing. But for now we’ll stick to the basics.
Maintainability
I had a lecturer at university who was full of sage advice, such as “If you design it so it’s hard to maintain, they won’t maintain it”. [There’s a whole post in unpacking the design implications and responsibilities implied by that statement, but not today]. The same is true of spreadsheets generally, especially of the weight estimate.
Make it maintainable.
You’ll be visiting it frequently - you need it to be reliable, transparent and simple. If it looks intimidating (this will happen if it’s not totally clear how the calculations flow!) then it may get neglected by people not wanting to break it. This is a bad outcome, because the estimate must be checked and checked… and checked again.
Intimidating spreadsheets are a design fail.
Occam’s razor: maximum flexibility, simplicity and transparency
The spreadsheet should be as simple as needed, but no simpler. Any ‘hidden magic’ that is not 1000% necessary should be avoided - so many details seem like a great idea at the time, but recalling that magic six weeks later in the heat of a design update can trip you up. Trust me, I’ve seen this end badly.
How calculations flow from the start to the end should be obvious. Intermediate steps should be avoided unless absolutely absolutely necessary. This includes manually calculating moments for weights. Don’t. a) they’re unnecessary b) they clutter up the spreadsheet and c) every unnecessary calculation risks breaking the spreadsheet. See ‘SUMPRODUCT’ below.
One calculation sheet
As fas as is possible, use only a single sheet for weights. The current version of Excel supports over a million rows. To keep data legible, use filters and pivot tables. This keeps all the critical calculations in one place.
Formatting matters. Fonts, colours, number formatting all influence legibility. One approach is to use colours to guide ideas.
The spreadsheet below shows a few of these ideas.
Use group/subgroup/item to filter the data to what you’re currently working on
Use the ‘product’ function to multiply item by quantity - this will give the correct results when some columns are blank (such as in the qty3 column)
Weights are shown in blue, centres are shown in orange. Calculated cells are filled in.
The weight is the total of the ‘weight’ column. Maximum simplicity. A quick check of the weight can be made by clicking the ‘H’ at the top of the column:
Total weights and centres are calculated using SUM and SUMPRODUCT functions. This means that rows and columns can be added or re-ordered without breaking anything. This also limits the visual clutter of the spreadsheet.
Explain assumptions
Even if it’s obvious to you, it’s not necessarily so to others. Make it as easy to use as possible. if Apple was designing your spreadsheet, what would they do?
Explain ALL assumptions. Future you will thank you for it. Particularly explain coordinate systems. How does your CAD system’s XYZ space translate to the vessel? Is ‘longitudinal’ from the transom, bow at DWL or midships? Is a transverse dimension positive to port or starboard?
One ‘revisions’ sheet
Keep a sheet that describes the history of the data. Consider recording the provenance of each weight item on the weight spreadsheet alongside the revision date. Traceability of the data is crucial. Make it easy. It doesn’t need to be complicated, but it does need to be present and up to date.
Filters are your friend
Use filters to minimise what you’re looking at. In Excel, select multiple columns, go to the ‘data’ tab and hit ‘filter’. Excel will provide you with buttons for filtering the data shown in the column, intelligently providing options for filtering by value, by text contents or by number ranges. Super handy.
Pivot tables are your best friend
Pivot tables are like running database queries on your spreadsheet. They’re fantastic ways of generating quick summaries of what’s going on in your data.
Imagine you’ve got a 5000-ish row spreadsheet of weight data, and you’d like that summarised by system (structure, HVAC, fitout etc) and by vessel compartment. Pivot tables can instantly give you a grid that sums weights with (say) system on the vertical axis and compartment on the horizontal.
These summaries are also handy things to copy/paste into the ‘revisions’ sheet to provide snap-shots of where the design was up to at a point in time.
SUMPRODUCT for the win
Calculating the combined centre of gravity is usually accomplished by adding up the product of every item along it’s XYZ axes, summing these products and dividing by the sum of their weights. Standard first-moment of mass calculation.
Excel saves you most of the work with it’s SUMPRODUCT function. This function returns the sum of all the products of two lists of numbers. In the image above, the weight is the sum of all the entries in column ‘H’. The LCG is the sum, of the product, of each row in ‘H’ (the weight) and each row in ‘I’ (the LCG). Internally, excel runs through all the rows and adds up the H*I values, and gives you the result.
Dividing the result of SUMPRODUCT by the total weight (cell ‘o5’ above) thus gives the first moment of mass longitudinally - the LCG. Occam would be happy.
Using this technique deliberately avoids using specific cell ranges in the calculation. The idea is “if a weight is in the weight column (‘H’), then it’s in the total”. You don’t need to remember to extend the calculation range from row 25600 to 25601 every time you add an item.
You will thank yourself
Having a well defined structure for your weight estimate spreadsheet is handy for the weight estimate per se, but it will serve you well when you want to write code to analyse and visualise the data. Stay tuned for our next post on exactly that.
I hope this has been helpful. To comment on any of this or to find out more, hit the ‘contact’ page and get in touch!
All the best,
Nick.