Functional Question about Estimating and Quoting Software

[Fran Lombard]

Hoping to get some insight into the level of granularity i should implement in the Estimating/Quoting module I am building.

As I posted before, I currently run a small Home Improvement company.
We specialize in really just a few types of projects. Deck Construction during most of the year, then interior remodeling in the off season. Mostly Kitchen and Bathroom remodels.

I am currently adding in functuonality to my system to aide in the process of generating Propisals or Quotes for the projects i'm trying to win. My goal is to have the system generate Detailed Estimates of both the costs and the billing amounts for the projects. By Detailed, i mean with enough information to produce the proposal, but the also create the Material Purchase lists. The plan is to have detailed templates by Project Type then a series of questions that capture the critical information need to generate the output This Particular Project.

Hopefully, thats enough Functional information for any potential responder to get a sense of the objectives.

Generally Speaking by Core Entity is a Project.

My Projects are broken down into Phases.
Each Phase has multiple Labor Steps and Each Labor Step has a list of Materials.

Now to the question

I am currently focused on the material side.
I have multiple hierarchies associated materials.

There are Componets and Component Groups.

Components are the level I include on a Proposal and include items like Floor Joist", "Stairway Landing Pad".

The Group is really just a mechanism to filter the list when looking up a component. Examples are like "Framing Components", "Foundation Components "


Then there are Parts, and Part Categories

Parts are the lowest level in the procurement process and represent the line item level you would see on a Purchase Order.

Part Categories are like a grouping by department.

For instance
Part Category: PT-Lumber
Part Code: 2x4x10,
Part Code: 2x4x12
Part Code: 2x10x16...

Part Category: Concrete Products
Part Code: 80 lb. Ready Mix
Part Code: 94lb Portland Cement

Now to the specfic item that spurred the question.

Labor Step: Build and Install Pad Form.

Pad Form is currently defined as the Component that the Labor Step is acting on.

The Component Pad Form needs Parts from multiple Categories.

Needs parts from 3 categories
Lumber, Hardware. And Concrete

While i can have a junction table
ComponentCategory

Im questioning if my component Pad Form
Is too big and should it be broken down into smaller Sub-Components.

Think Assembly/Sub-Assembly.

Im hoping soneone has some experience with this kind of process and can provide some insights into the rules i should follow.

Thanks in advance
Fean

 

[Deleted member 147267]

That was a bit hard to follow; probably requires a second read when momma doesn't have the tv blaring! That can be tomorrow when she's zzz-ing.
WRT experience, mine is primarily in the maintenance world, but also ISO and contracting and I think my 'handy man' persona might be of some help (I built my own home one time, so I certainly understand decks and the like).

The CMMS system my employer developed seems to parallel what you're doing in that the primary thing was the work order. In your case, probably a quote, but I don't see the problem with starting it out as a work order that's in a planning status. You might call that a quote status. Then there were tables related to the work order for
- work order notes/comments
- tasks (up to 99)
- task parts
- task comments
- task level SOP's
- task level resources (tools/equipment)
Tasks were department specific; i.e. if the task dept and wo dept were the same, the task performing dept was the same as the wo dept. Otherwise, it was obvious that the task was dependent upon some other dept, who was responsible for their own wo planning. That level may seem to be not pertinent to you, but if you're sub-contacting the likes of post hole digging, think again.

As for the "rules" you should follow, I can only say with certainty that if you fully grasp normalization principles then you have a good chance at success. However, that means that you must accurately define what the entities are, and that is wholly dependent upon the nature of your business model. In the above, I've identified six entities. Whether or not they all would apply here is solely dependent upon the business model. IMHO, to do this right is no trivial task.

 

[The_Doc_Man]

In general, what you are describing is not exceptionally different from a multi-layer component inventory for sales or manufacturing. The real trick is that such inventories grow as you go. I.e. as you develop more experience and can improve your documentation and your history of costs, you can get better at it and get better numbers as well as more detail. We had a person in this forum recently doing a manufacturing assembly inventory. I'll use that as the basis of my (admittedly not expert) viewpoint.

Your business has these "things" that you do. You know that when you do them, you will need certain materials. So you start listing materials. You start breaking the "things" down into component actions. You talked about groups and sub-groups and hierarchies. The temptation would be great to try to have separate tables for such things. However, you will find the consistency (or inconsistency) of those groups will drive you nuts if you are not very careful. And maybe even if you ARE.

Instead, you could have a table in which you store EVERYTHING. Heck, in theory you could even store a "labor requirement" right next to the wood-frame parts for your concrete molds, which would be next to the concrete bags. How would this work? You need to read up on the topic called "Junction Tables" which describes a way to make a component list using a many-to-many relationship. When you have this implemented, you have two tables.

In the inventory table, you can have the basic element "Concrete form" (of various sizes, perhaps).
Add to that the concrete bag sizes that you use and a per-unit cost and maybe a discount code telling you costing data for larger orders.
Add to that the wood you use to make the form and a per-unit cost and maybe a discount code telling you costing data for larger orders.

In the component table, you have a series of entries with three fields (or more). The fields would be a "parent" and a "child" foreign key into the inventory table (plus perhaps a quantity if you take the approach of having different size concrete forms as separate items.) The parent items and the child items will BOTH be in the inventory table.

Then you have component entries as needed to show what it takes to build something.
You have an entry for which the parent is the "concrete form" and the child is "concrete bag" and "quantity 2".
You have another entry for which the parent is the "concrete form" and the child is "wood, 2x4x10" and "quantity 4" (perhaps)>

The way this works, you have more complex "things" as the target of that "parent" pointer and component "things" as the target of that "child" pointer. I.e. the parent points "UP" in the hierarchy while the child points "DOWN." Then you have as many junction entries as you need to tell you how many components you need for the thing you want to build. Where this gets really interesting is, suppose that you have an assembly that is itself complex and at the same time is part of a larger "thing" (more complex assembly). You can have these "component" entries for which the "parent" points to a thing that is a child of a higher level thing. So your "hierarchies" are handled this way.

The reason this is a many-to-many table is because suppose one day you decide you want to know every "thing" you do that uses "portland cement" bags. You have that by finding every child pointer in the component table that points to portland cement. Therefore you can get a quantified measure of how commonly you use a particular item.

This is NOT a trivial process, because you might have to write some complex VBA code to "drill down" to the bottom line. However, if you have unit cost in the main inventory table and unit counts in the assembly table, you have a means of giving a really detailed breakdown. Now if you REALLY want to get kinky, there is no reason you couldn't put "Labor" and an average pay for one hour of work in the inventory table, and as a component of the "assembly" include some number of hours of labor. Which means your estimate can even include labor costs.

You'll have to draw this out on paper to actually see it. But this would contain everything you need for your estimating and all you need to do is break down your hierarchies and sub-hierarchies using the "components" table to show the relationships. It is POSSIBLE that to do this at the lowest level, you would have to write some complex VBA to do "recursive" (self-calling) code if you have too many levels of the hierarchy. However, it IS doable with a little effort and a little study.

Take your time to read this. It is complex. But with a bit of paper and pencil, or perhaps a dry erase board and markers, you can draw this out to see what I'm talking about.

 

[Fran Lombard]

@The_Doc_Man Thank you for taking the time to put together such a detailed response. I will definitely need to read and re-read many times to fully grasps it all.

On the surface, i think im heading in the same direction. What i didn't mention was i do also have a set of tables that define labor crews and crew members for deriving labor costs for each Labor Step in an estimate.

This data set includes cost factors (Productivity Factors) Like Each/Hr.Or SqFt/Hr.
And each labor step has a has a value thats tied to these factors.

So a labor step in the template would be
Install 1 floor Joist - by which crew, with expected productivity of .15/hr. This means it takes this crew
.15 of an hour to perform this task.

When this step is added to an estimate it would gain a qty of say 15 to represent how many floor joists are needed on this project. With this information, coming up with the total labor cost for the step is relatively easy.

My struggle centers around how granular should each lowest member be.

When you think of a Quote/Estimate level line item like i eluded to in the initial post
- Install Concrete Landing Pad for Stairway

This one line item can theoretically be broken down into many steps and include materials as granular as the number of screws needed to assemble the form. This is a bit of a daunting task, however luckily i have 10 years of actual data to use for data mining to help define thes data elements. Unfortunately, the labor activity and material component tied to this data did not have any true meaning. It was just decriptive flags on the labor step and material requirement records. There was no business logic tied to these flags. Now Im trying to leverage theses flags and give them meaning. This process is highlighting how either incomplete or inaccurate these sets of flags are for their newly intended purposes.

At this stage, ive decided my current data for component is truly a mix of information that may or may not be residing at the right level.

Some components need to move up to component parent(which will be a new level)

Some components are ok where they are but need to be associated with one or multiple parents.

Some components need more information defined by multiple subcomponents.

Some Components need to move down and be a subcomponent.

My current challenge is in defining the rules that govern the definition an usage of each data element.

Where do i create the link to the material parts needed for an item?

Can components exist without any sub components or should All components need to have at least one sub.

The lowest level in any one (branch) would be a subcomponent and i know i need a link to material part code here. But is it ok to have a component with a link to part code without a subcomponent.

While i can write the processing code to handle this scenario, should I? I always try to ask the question
Just becuase I can, should I? Im trying to avoid potential problems in and over complications of the processing code.

While I currently have a data model that I think is gonna work, that data model was created before all the rules were completely flushed out. I know a bit backwards, but i find sometimes you gotta play with the data in a defined data model to validate your assumptions. I feel im getting close.

 

[The_Doc_Man]

You have a lot of questions. Some of them, I will answer. Some of them, I cannot possibly answer - because they will depend on your business model and that, I do not know.

Where do i create the link to the material parts needed for an item?

In the "components" or "assembly" table, you make as many entries as needed where the "parent" link is the item and the "child" link is an item that is lower in the hierarchy. This is a "many to many" table, so you can have many components, all of which point to the same "parent" entry. Then a query to find all component-table items with the same parent tells you what you need to make that parent item. You can save space here by allowing a quantity other than 1 in the individual entry that leads from item to component. I.e. for parent #21, you need TWO bags of Portland cement.

Can components exist without any sub components or should All components need to have at least one sub.

Would that mean that you have items in your physical (i.e. non-database) inventory that you have no idea why they are there? Because that is basically what you just asked me in database terms.

i find sometimes you gotta play with the data in a defined data model to validate your assumptions.

Allow me to (gently) correct you. Not "sometimes." You do this for ANY model, EVERY time. Because either you (a) confirm the model or (b) fix the model. And BOTH of those are good things.

Some components are ok where they are but need to be associated with one or multiple parents.

That's why I suggested a many-to-many set up for the assembly or components table. It handles EXACTLY that case. And it supports a query of "how many objects are children of multiple parents?"

Some components need more information defined by multiple subcomponents.

Yep, that happens too. And there is nothing in the model I described that says the component's "parent" entry has to be a top-level item. There is no reason it couldn't just be an item that is also part of a larger assembly and thus is a child of something else. The ONLY problem here will be to visit all of the components including the ones that are not elementary-level components.

This component/assembly table I proposed merely says "You need N items of type X to make item Y" where both X and Y are in the master inventory listing and N is in the entry specific to the combination of X and Y. It doesn't matter WHERE in the table X and Y are found and it doesn't matter whether X is itself a child of another component entry or Y is itself a parent of another component entry.

There is also nothing wrong with the idea that in the master inventory list, you keep a flag that says "This is an elementary item." In fact, if you wrote recursive code to "elaborate" the elements encompassed by one of your top-level complex items, the flag would help your recursive code decide that it had reached the end of a branch. That flag would support queries to help you manage the physical inventory of the lowest-level components in case you wanted to do a "real" inventory, though that would be done differently than I described here.

 

[Fran Lombard]

You have a lot of questions. Some of them, I will answer. Some of them, I cannot possibly answer - because they will depend on your business model and that, I do not know.

Thanks again @The_Doc_Man
You have done more than I have a right to ask.
You have invested your time to help me solve my challenges with thoughtful suggestions.

It now time for me print out your responses, go to my "Thinking Rock" tap my head "think think, think" and make some decisions.
- Winnie the Poo reference for those not versed in the classics.

 

[The_Doc_Man]

Just don't tap TOO hard.

 

[Pat Hartman]

@Fran,
I don't want to discourage you but your time is worth money and there are commercial products available for this type of business. Have you looked into any of the available products? Just reviewing them will give you some good ideas regarding structure

 

[Dreamweaver]

I have something you might find interesting but it's a accde I wrote it to help prevent contractors from making the same mistakes i did back in the 90's

It's only available on 32 bit versions of access at the moment but your welcome to have a look and see if it gives you any dears.

Works Manager

Good luck with your project

keep safe mick

 

[Fran Lombard]

@Fran,
I don't want to discourage you but your time is worth money and there are commercial products available for this type of business. Have you looked into any of the available products? Just reviewing them will give you some good ideas regarding structure

Thank you of your thoughts.

I have - and generally I find the products that I could consider from a cost perspective fall short of the functionality I desire.
It seems they want to focus on one aspect or another - like producing pretty quotes with pictures but not having the smarts to generate different projects sizes based on parameters. The closet I've seen to what I'm looking to create is the software used by Insurance Adjusters to generate a Claim Estimate or Manufacturing software. Both of these options are way outside my budget. Plus there is the fact that I like an intellectual challenge and want to keep my software design and development skills from a previous life alive.

I'm finding the real challenge here is not the Design of the Database or the Coding of the Screens or the coding of the estimating process.
The real challenge is in the cleansing of existing data and the establishment of my source data to drive the process. This is basically an Implementation Task that would be needed no matter what software I was using.

Attached is a file I found out on the internet that gave me a direction to head down. Looks like it was this guys Computer Science Master Thesis

 

[Fran Lombard]

@The_Doc_Man I had a chance to spend some time at the Thinking Rock and read through your suggestions a couple of times and well the light bulb turned on. I always have seperate tables to define the parent and child then a third table to establish the many to many relationships. I see now how this creates a nightmare in future maintenance when items need to change levels or another level is needed. If i understand the approach you are suggesting, effecting this change within your structure is essentially chaging the parent pointer or adding a new item in the bottom table that becomes a parent to an existing parent with entries in the junction table.

Elegant solution. I guess the trick now is coming up with an easy to use user interface.

Ive been playing around with @MajP treeview and while my first impression is a tree would be nice, but this structure is not really a single root tree and leaf nodes can end up pointing to more than one branch.
I guess each top level Parent is a root, but whoose to say some individual leaf nodes dont end up to different sub asseblies of the same top paremt.

My guess is a treeview is not appropriate here unless i havent fully grasped the data structures.

Any suggestion regarding an effective UI.?

Fran

 

[The_Doc_Man]

I guess the trick now is coming up with an easy to use user interface.

Yes and no. The first task is to decide what you need to do in this putative user interface. You need to decide who will use it and then worry about how. I think from your comments that you can now see that all the data could be there to give you an incredibly detailed cost estimate by selecting only some high-level items. But you have to also ask yourself what kind of maintenance you will perform, how do you get data into the system for use in future estimates, what kind of estimates you will need, and how you want to present your estimates (i.e. in what format and to what level of detail).

That sounds like a lot, but it is crucial. Despite that "elegant solution" there is the little matter that you need to be able to meaningfully pull in data or push out data that is useful. Otherwise it's just a fancy hat rack.

I'm going to offer a thought or two as to interfaces. My ideas are coming out of thin air, and I am FLAT OUT GUESSING that this could work for you. No written warranties here.

To define items in the table, you will either want to enter elementary items (a.k.a. "leaf node" items, things that have no children) or you will want to enter complex items (things that have children.) This might be better done with two forms if you are not comfortable with multi-function forms.

With one form you would enter single items in the "inventory" table even if they are intended to become complex. They remain simple at first because you don't have a defined sub-structure yet.

Another form populates the "assembly/component" list, where you have the ability to select either an existing or a newly created "parent" item using a combo box (probably). Then perhaps you can have a sub-form in continuous form mode to allow you to enter the component (child) item's ID (also selected from a combo box) and the quantity when associated with that particular parent. I think the hardest part about this procedurally is that it would probably be necessary to do this from elementary to complex. I.e. it might be harder to enter complex first and then drill down while adding components that are also not leaf-node items. Not saying impossible, just more difficult if you have to add a previously undefined non-leaf-node item as a child of the current parent.

The trick with estimates is you have a form for this, too. You need a separate estimates table sort of like an invoice table. This has customer name or ID, date, who is entering the estimate, whatever else is appropriate about the estimate as a whole - but NO detail level stuff.

You have another combo box to select items that will be part of the estimate. Repeatedly use that to make a list of items that will be in your estimate. Now if every item is in your inventory (i.e. no "surprise"/"custom" assemblies), you just make another junction table. The parent for every estimate component will be the estimate number. The child will be an assembly item or an elementary item.

Have a command button that adds the currently selected combo item. Code behind the button tests whether it was an elementary or complex item. If elementary, you are done. If complex, have the code pull the list of child components from the assembly table into the estimate table to get the items that were implied by selecting that parent item. Before that code exits, you test whether you have reached all leaf node items. If not, then you have to again reach into the assembly table. How you actually choose to do this is a matter for separate discussion, perhaps.

Haven't touched on reporting this, but if you have a way of selecting everything with that estimate component form, it is a piece of cake because you can build queries against that combination and use different sorts to build different kinds of reports.

 

[Fran Lombard]

Thanks again @The_Doc_Man you've given me plenty to ponder. I think I need to put this down for a day or two in order get out some overdue proposals using my old process. During that time these concepts and ideas will be allowed to brew.

One more question. Since most of these items and components aready exist in my system with various groupings, im thinking i should add a few extra columns to the your inventory table and the junction table to hold references to these other grouping definitions. These values would really only be used as filters to parry down the dropdown combo when looking up a particular member.

Does this make sense to you?

Fran

 

[The_Doc_Man]

There is not even one little thing wrong with the idea of having some "secondary keys" in the base inventory table to allow for filtration to reduce the number of choices to be shown in a combo box. Probably advisable, in fact. Like, your lumber items might have keyword "LUMBER" as a secondary key. The bags of different kinds and sizes of concrete and cement would have their own key, certainly.

I am not entirely sure about having such keys in the junction table. The reason is what I sometimes call the "purity principle." The purpose of the base inventory table is to give you things to select. From the inventory code number, which was suggested as a candidate for prime key, you could search the junction table for things related to what you selected from the inventory table. The purpose of that junction table is to show relationships between complex items and their elementary components. There is a certain "purity of purpose" to having the secondary selection keys in the main inventory table because there, one entry is one type of thing (whether elementary or not). It is something that could appear in an estimate. You can select based on types of thing. However, in the junction table, a single junction entry isn't something you would select via a secondary key. You would select that entry because you were looking for components of an already selected prime key.

Things that could easily go into the junction table? Parent ID, Child ID, quantity of the child item required for making the parent. What else WOULD go there? The purpose of the inventory table is to hold things you want to be able to select. The purpose of the junction table is to enumerate the fine structure of a parent item. What would you possibly select from the junction table OTHER than the "parent ID" to see structure, or reverse the roles to make a list of all parent items that use a particular child component.

You need to look up some articles on this forum regarding "Cascading Combo Boxes" to see how that pre-filtration for the final combo box would be implemented.

 

[Fran Lombard]

@The_Doc_Man Thanks again, at this point i "just" have to build it.
I already have a number of places with cascading combo's so im good there. I think the primary decision i have left is the location of the processing code when generating a new quote. Since my db is in mssql and i'm fairly adept at writing stored procedures, my inclination is to do it there.

The overall design of the system has been to keep as much business logic close to the data and decoupled from the user interface. The philosophy ive applied is the user interface is the necessay evil for entering data but all heavy lifting is done inside the db. I believe this approach increases the value of the permanent aspect of the system, eases any future migration to a new UI and keeps Vb code down to a minimum.

Do you see any reason I should reconsider this approach?

 

[The_Doc_Man]

Do you see any reason I should reconsider this approach?

Maybe, although it could also be just a matter of semantics. If the user interface is replacing something you already had, and if what you already had was part of your previous business flow, "decoupling" from that old user I/F is radically changing something, which implies retraining of your users. But it could be that I read more into your statement than you intended.

 

[Fran Lombard]

Maybe, although it could also be just a matter of semantics. If the user interface is replacing something you already had, and if what you already had was part of your previous business flow, "decoupling" from that old user I/F is radically changing something, which implies retraining of your users. But it could be that I read more into your statement than you intended.

You are reading too much into it. Right now Im the only user. The system is using Ms Access 2007 as the front end and is an Adp. Project. Support for this setup ended with either access 2010 or with 2013. So i really don't have a simple upgrade path.
Having said that, the next iteration of this program will likely have the front end written in C# or some other Mobile enabled platform.

 

[The_Doc_Man]

Makes sense now that you have explained it as "single user."

 

[Fran Lombard]

@The_Doc_Man
Just wanted to give you an update on the direction I've decided to go with and ask for your feed back with regard to the approach.

I think the data structure is similar to the one you outlined but a bit more complex and hopefully provides more capabilities.

The will be 5 key tables used to describe an Item.

Item - Master Record of things we make
- 12 Inch Concrete Pier
- 10 Inch Concrete Pier
- 2 x10 Ledger Board

ItemTasks - many to many junction between Item and task. Will also hold time/quantity values (How much effort (Time) needed on this task for this item)

Tasks - Individual Labor Steps with association to labor costs
- Excavate Hole
- Set Pier Form
- Mix and Pour Concrete
- Nail Ledger to House
- install Ledger Bolts
- Install Ledger Flashing

ItemParts - Many to Many junction between Item and Parts. Will also hold quantity values ( How many of this particular part needed for this item)

Parts - Master table of Parts with unit cost
- 12 inch Pier Form
- 10 inch Pier Form
- 80lb Bag of Concrete
- 2x10 Pressure Treated Lunber
- 2 x 8 Pressure Treated Lumber

Once these associations are all defined, creating my estimates will essentially be answering the question
What Items and how many of them are need for this Project?

I also have Project Types and Phases that define essentially Template Projects. So, the estimating process will be to pick a Project Type as the starting point, have a stored procedure to copy the definitions from the Template Tables into the Estimate Tables then I tweak the estimate as needed.

I kinda feel like I nailed it for how i view my business and what I want out of the system, was just hoping to get your insights with regard to - Did I miss anything and/or Do you forsee any limitations or difficulties with this approach.

Thanks in Advance
Fran

 

[The_Doc_Man]

Did I miss anything and/or Do you forsee any limitations or difficulties with this approach.

1. There is no way for me to know since I don't work at your business. I don't know and can't know the ins and outs of it. However, trust me when I say that if you DID miss anything, you will all too quickly come to recognize it. The indication would be new bite marks on the derriere.

2. Limitations / difficulties will be present in ANYTHING you build because you build an "ideal world" model that everything will be clearly and cleanly identifiable. Then a duck-billed platypus walks in the room.

Just remember that your business is a living, breathing entity. It grows and changes and evolves and adapts to new challenges. Expect your model to have to evolve to keep pace. The one down side to ANY application approach, home-grown or commercial, is divergence from past reality. Or the Forrest Gump comment, cleaned up a little bit: Stuff happens.

I kinda feel like I nailed it

This is the most important part. If you, who live with this business every working day, think you have captured its essence, who am I to second-guess? If this tool does what you want it to do, chalk up a success on whatever you are using to keep score. And that makes me feel good that I helped you with suggestions to consider and advice to organize your ideas. It doesn't matter that you added your own take on everything. You should have done exactly that. After all, in an earlier post I DID offer a disclaimer of specific expertise.

Keep on going, Fran, and good luck. Don't hesitate to start a new thread if you find issues.