Originally posted 11/17/09
This information comes our way from Chris Barber at Fine Art Shipping. Read it to find out more about dealing in a very systematic way with an age-old issue. This article offers step by step instruction in how to use basic information technology to create a tool to increase your efficiency and effectiveness in managing projects.
Depending on how comfortable you are with creating and managing a partially automated system, a custom estimate and cut-list program can be a ridiculous time saver for your crating department. My “crating engine” uses mostly simple math functions in a simple database application. With it, I can estimate the cost and dimensions of a crate and have a formatted cut list ready to print for the craters in as little as fifteen seconds. Unusual crating scenarios often require only a few extra minutes of data entry before the results can be sent to a client services representative or printed for execution. Once all specs are chosen from menus, every square inch of building material is automatically priced and calculated for weight, both for estimates and for the actual price of the built crate.
But whether you have your own crating program, or whether you do all of your math with pencil and paper, the big unknown for crating estimates is labor. Any given builder will have good days and bad days. Averaging their past performance won’t always give a perfect estimate, but it will take their history into account and mitigate guesswork based on misleading examples. Naturally, the more examples of past performance you record, the more likely you are to approach a good reliable mean. The other sticking point in estimating labor is the duration/volume ratio. For obvious reasons, this ratio is not a straight line, but a curve. The smaller the cubic footage of any style of crate, the more minutes it will take to build per cubic foot, at a disproportionate rate of increase. Likewise, the same curve levels off to nearly flat in the upper size range. I’ve plotted these curves for my lead crater so that I can make a reliable prediction of his performance on any style of crate, regardless of the size job. Even if you do everything else in your head, an accurate time curve is an elegant alternative to guesswork. Of course, this isn’t limited to crating. It can be applied to any production task with a similarly predictable set of actions. Here’s how to make your own:
Step 1. The first thing you will need is the raw data. Start recording exactly how long it takes you or your staff to build crates. Start a separate log for each crater, and each style of crate that crater produces. Every log should include a series for minutes and a series for cubic feet. Then make a third series, dividing minutes by cubic feet. I put these series in columns; so if cell A3 = minutes, and cell B3 = cubic feet, cell C3 = A3/B3. You will only use the second and third columns in the next step - cubic feet & minutes/cubic foot. Here’s an example log for “B-crates” with two hypothetical craters, one a faster builder than the other:
Soon you should have enough data in those series to get reasonable estimates. The data collection is an ongoing process, however, and your logs should be updated regularly. Older numbers could be dropped eventually to account for your crater’s growing experience and speed, but the aim is to collect as much information on each builder as possible. This is not to spy on your crew. It is to accurately predict the time it will likely take this person or that to build the next crate.
There are two ways you can process your database into functional labor estimate curves. First I’ll show the quick way, and then I’ll explain what these numbers mean by showing the chart method.
Step 2a. Find the “power trendline” of each crating log you have made, and multiply it by the estimated cubic feet. I’ll explain what the power trendline is in some depth below, but for now you can just treat it like a magic spell. If you aren’t a math geek and don’t care how, why or whether this really works, you can stop reading at the end of this step.
The fastest and most efficient way to process a given crater’s average curve on a given style of crate can be done in five math functions, and will fit on a spreadsheet the size of a postage stamp.
| Cells | Functions | Descriptions |
| A1 | =[length]*[width]*[height]*1/1728 | estimated cubic feet |
| A2 | =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2)) | coefficient A |
| A3 | =INDEX(LINEST(LN(y),LN(x),,),1) | coefficient b |
| A4 | =A*(x^(b)) | trendline equation |
| A5 | =[cell A1]*[cell A4]*1/60 | labor estimate |
A1) The first cell should simply display the cubic footage of the crate being estimated. The least fussy way is to link this function to three blank cells somewhere else where you enter the crate’s L, W, & H. Those same three blank cells can be linked to every curve you make (since you need a separate curve for each crater on each style of crate).
A2) The second cell should return the value of A to be used in the equation in cell 4. This cell should contain the exact function shown, but in place of x, link to the whole cubic feet series in your crater’s log (B3:B14, to use the slower crater shown above as an example). Likewise, y must be linked to the whole series of data in the minutes/cubic foot column of your crater’s log (In this example; C3:C14).
A3) The third cell should return the value of b for the equation in cell 4. Treat series variables x & y the same way here as you did in cell 2.
A4) The forth cell should contain the function shown, but replacing x, A, b with the results of cells 1-3 respectively. Caution: in this equation, x refers only to the cubic footage of the crate being estimated, because it is graphed on the x-axis. It is not the same 'x' variable as in cells 2 & 3.
A5) The fifth cell is the product of the values returned in cell A1 and cell A4, then divided by 60 to convert minutes into hours.
You can use these five steps to bypass the charting step described below and get your trendline equations straight from your database. But the chart actually shows what these numbers mean, and I prefer to see graphic representations of the curves anyway.
Step 2b. If the step described above seems too cryptic, the numbers involved can be more readily understood by graphing them. The program I use allows me to insert a visual chart into my spreadsheet, define the x & y parameters and link them to the two relevant series of data. This is pretty basic, and I’m sure that it’s a universal feature in spreadsheet applications. The type of graph you want is an x-y scatter chart. Your chart’s values are simply: x = cubic feet, and y = minutes/cubic foot. Once your graph is linked to those two series, you will see points plotted in the field – each point representing the crater’s total time spent on a specific crate.
B-crate
The more information you have (and the more consistent your crater is), the more it should suggest the hint of a curve starting in the top left corner and ending in the bottom right. Now you can give the graph a trendline. The trendline extrapolates an average curve from your unwieldy cloud of points, in a visible line. You may need to choose from several types of trendline. I prefer what my application calls the “power” type, which appears to produce the most realistic curve, leveling off dramatically as it approaches zero on each axis. The “exponential” and “logarithmic” types both trace the trendline right off the chart at each end, and there’s no way a large crate will ever take negative minutes to build. Nor will a small crate ever have negative dimensions. The “linear” type overrides the curve that I believe is there. The “moving average” type defeats our purpose entirely. The “polynomial” type creates a dip in the middle ground that doesn’t make sense to me. Even if I wanted to address the handling logistics of larger crates, this potential issue is completely unrelated to the polynomial formula.
As you can see above, there is less data from the faster builder, and the blue curve is barely visible. This makes the blue trendline less reliable in the extreme size ranges; particularly the smaller sizes. This problem can be addressed quickly by giving that crater a very small crate to build and a very large one. Getting just a few points plotted past the margins of that crating history will give the blue trendline a wider range of accurate predictions.
Step 2c. Once you have your trendline plotted, tell your graph to show the trendline’s equation (which is hidden by default). Each trendline is described by a math equation reflecting the moving average of your plotted data. The power trendline equation should look like this:
y = Axb
The values of x and y are still cubic feet & minutes per cubic foot respectively, as the chart suggests. The coefficients “A” and “b” come directly from the trendline, which in turn is a biased average of the data your chart illustrates. *
Step 2d. Now here’s the nice part: Your trendline equation can be recuperated back into the spreadsheet for the purpose of estimating labor. Once you estimate the cubic footage of your prospective crate, you can simply multiply it by the trendline to get the most accurate possible labor estimate for any given crater. The spreadsheet function for this looks a little tricky, but here it is using the same variables, A & b, as my example of the trendline equation above:
=A*(x^(b))
So if your trendline shows the equation: y = 35.956x-0.789 …the spreadsheet cell representing it should say: =35.956*(x^(-0.789)).
If your trendline shows the equation: y = 5.5678x-0.2912 …the spreadsheet cell representing it should say: =5.5678*(x^(-0.2912)).
Note that to make either of these examples functional, x must refer to the cell that displays the crate’s estimated cubic feet. The current value of x must be folded into the trendline equation before it can return a relative unit of duration/volume adjusted by the crate’s size. While the trendline equation merely displays the coefficients A & b, the spreadsheet cell as typed above will return the actual value of y -- as long as x points to the cell displaying the current value of x and the function begins with the equal sign. Once you have a spreadsheet cell representing the trendline linked to the variable cubic footage cell, all you need do is multiply the two cells. Keep in mind that this will result in minutes; so if you prefer estimated hours, just divide the result by 60.
So to mentally separate this step from the raw database illustrated above, let’s skip over (arbitrarily) to column H on our example spreadsheet.
The blue and orange numbers in this screenshot represent the faster and slower craters, like in the curve chart. The top number in each set is the cubic footage of the crate currently being estimated. This cell changes with every estimate, as it is the product of the crate’s length, width & height, divided by 1728 to convert from inches to feet. Let’s say for the sake of argument that the cell displaying orange cubic footage is in position H4 on the spreadsheet. The next cell down, H5, is the trendline equation for that crater, with the current cubic footage plugged into it. So in place of “x” in =A*(x^(b)), the function says H4. And in place of “A” and “b”, the function shows the actual trendline coefficients. In this case what I actually typed into cell H5 is: =70.254*(H4^(-0.656)). Refer to the orange trendline on the chart to see how I got A and b. This is a functional version of the trendline equation, responding automatically to the cubic footage displayed above it. If the cubic footage dropped, the result displayed in cell H5 would rise appropriately for the crater in question. The next cell down, H6, is the product of the first two cells, divided by 60 to convert from minutes to hours. This is the estimated hours it will likely take that crater to build that style of crate at that particular size.
Step 3. Update and fine-tune your logs. Some spikes may occur that throw the whole curve out of whack. They are usually in the negative direction – like when a crater made a big mistake and spent a lot of extra time correcting it. I toss the worst spikes. I would rather take the hit when random problems happen than let them affect every estimate. Such large spikes are very rare, and I’ve only eliminated about four crates from my whole database for that reason.
Packing estimates: Of course, packing a crate involves many more variables than building it, so you should keep building time and packing time separate in your database, charts and equations. I don’t even use packing curves myself. I use a flat time for each type of flatwork, and estimate dimensional items in my bean.
There are many different ways you can approach the problem of labor in estimates, depending on how tight you want your estimates to be. Plotting curves is admittedly a bit anal, but quite easy to set up. And it only improves over time as you add more information.
Chris Barber
Fine Art Shipping
Menu
Search topics by category