Manufacturing Cost calculator; is what I am trying to do feasible?

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • Answered

I am trying to solve the following business problem and I think QB may have the needed functionality but I am not a software / database programmer so am reaching out.

The problem:

I run a small engineering / metal fabrication company and I have a need to produce quotations fast and accurately. I currently use an Excel spreadsheet and although this is OK for simple jobs it is very labour intensive when calculating prices for multiple items or complex jobs with lots of parts and process steps.

I want to build an App’ that has the following capabilities:

1 – A Table of Materials with the cost for each material based on £/meter (easily done in one table).

2 – A Table of Process “Standard Times”. This is a list of process Types (e.g. MIG weld), process details (100mm long fillet weld), with the associated time it takes to perform this operation (e.g. 1min 30 seconds).

3 – From the two tables I want to be able to create a “Job Costing” query. This is where I ran out of knowledge when trialling QB.

3.1 - The query needs to be a new form that I create for a new job / quote. I want to be able to look up and select from the materials table to select a type of material and then say how much of it will be used – e.g – look up Steel, 50x50x3 box section – select this material and then specify a length – e.g. 1500mm and then select a quantity of these parts – e.g, 10 off The resulting record in the query table would be : 50x50x3 box section; 1500mm; 10 off. The query should pull in from the Materials Table the cost per meter (a field) and then multiply it by the number of pieces; in the example above it would give 10x1,500mm = 15,000mm. And because the table also has cost/meter then it would also calculate the total cost for these 10 pieces of box section.

The query / app should be able to add multiple selections of material so that a list of parts with their corresponding material costs is generated.

3.2 – In the same way as the Materials query works a second query that pulls in Process costs is needed. So the user would select from a pull down or similar each of the manufacturing steps needed to make the product. E.g. Operation 1 might be “Sawing, Bar stock less than 50mm” – this would have an associated “time” in minutes of (e.g.) 0.75 minutes. The number of these operations would be a user selectable field in the query – e.g. 10 operations to cut 10 pieces of material. And this would then total up for this step the time taken. The time has a standard labour rate associated with it and so the labour cost for this process step is computed.

As above, multiple selections would be added to the Job costing query until all steps needed to make the part are presented.

4 - With the two sets of information in place – all drawn out of a database / set of tables, the overall job cost can be calculated. To this an overhead value and a profit margin can be added and the resulting number is the price to quote the customer for the job.

 

I have managed to set up tables that have the basic functionality for Materials and Process costs. But then I ran out of smarts. I think what I am lacking is the overall design / schema for the app’ – I can’t figure out how to get the “Job Costing” query to pull out the data from the tables and then do the multiplication and store it in a line item of the query.

I have an Excel sheet that does the whole lot but it is labour intensive to cut and paste each line item from the Materials and Process Tables (tab in excel) into the Job Costing sheet. I tried learning Pivot Tables but could not achieve what I wanted.

Is this achievable with the functionality QB has to offer or is it beyond its capabilities?

If it is - what might a database/ table design at the high level look like to deliver this functionality?

From what I’ve seen by trialling the tool I can figure out the programming but I think without a high level design I’m just going to go round in circles.

Any help / suggestions very welcomed.
Photo of Andrew Aguilar

Andrew Aguilar

  • 110 Points 100 badge 2x thumb

Posted 10 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,842 Points 50k badge 2x thumb
I will respond more fully when I get a chance, but this kind of job costing is squarely within the sweet spot of Quick Base capabilities.  You just need some help getting your foundation and "architecture" of the tables and their relationships in place.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,842 Points 50k badge 2x thumb
So here would be the setup.  The goal is to make an estimate.  So let's call it that and not a Query.

Here are the tables and their relationships.

One Estimate has Many Material Estimate Lines.
One Material Master has Many Material Master Lines.

So you will need to make a new table for Estimates and also a new table for what is called the join table which are the Material Estimate line items on the Estimate.

Lookup the unit cost of the Material down to Material Estimate Lines and call it [Unit Material Cost (lookup)] (well the field name is actually Unit Material Cost (lookup) without the square brackets

Then make a new field called [Unit Material Cost]

https://help.quickbase.com/user-assistance/setting_up_snapshot_fields.html

Edit the field properties to make it a snapshot of [Unit Material Cost (lookup)]

The reason is that a year from now when your costs change, you do not want to change the original costing for last years' jobs.  So you freeze the lookup with a snapshot field.


Then we do the same thing with the Labour component, assuming that you want to maintain your Labour costs in a separate Labour Master table.

So then to do your Estimates, you first create an estimate with basic header information like who the customer is that you are Quoting and the Date.

Then you will add Material Estimate line itms and have fields to calculate the extended cost of the line item with price times qty.

Then on the relationship, make a summary field to summary total the extended Materials Line Items extended price.

Then do the same with the Labour costs.

Post back if you get stuck anywhere.
Photo of Andrew Aguilar

Andrew Aguilar

  • 110 Points 100 badge 2x thumb

Hi and thanks for the response.

I had a go at what you suggested but am struggling. Perhaps I need to spend more time watching the tutorial video’s to grasp the basics.

In my current App I have the following Tables / fields:

Materials:

•       Material – selectable list

•       Shape – selectable list

•       Size – selectable list

•       Bar length - numeric

•       Cost / bar - Currency

•       Cost / meter – Currency (formula)

Process Costs

•       Process Group – selectable list

•       Process Detail – selectable list

•       Labour rate (£) – Currency

•       Process Time (mins) - numeric

•       Standard cost- Currency (formula)

 

As you suggested, I then created two new tables, one called Estimate and one called Material Estimate

Initially I created no fields to either table beyond the default ones created by the system. But I then created a field in Estimate Table called “Unit Material Cost (look up)” as suggested.

 

I then added a one to many Relationship in the Estimate -Table to The Materials table.

 

The fields in the Master Table - Estimate are: Record ID (key field), Materials (report link), Add Material (URL formula). The fields in the Details Table – Materials are Cost per meter (numeric-reference) and I added the lookup field called “Cost per meter – Unit Material Cost (lookup) – numeric (reference proxy).

 

I saved and came out and then went into the Estimate Table to see what I had.

 

The Estimate Table now does something. On the Home Page for Estimate I see a table that has the following data columns: Unit Material Cost (lookup) , Materials (with a hyperlink titled Materials), and a Add Material (a button).

When I click the Add Material button it opens a new data entry form to the Materials table allowing me to create a new material record.

At the top right of the home page there is a green button called “New Estimate”. When I click I get a form with a box that says Unit Material Cost (lookup) and I can enter values to this.

Below this I see a button for Add Material. This again is a new data entry form for creating a new material record.

 

So it is doing something but I don’t really get what it is doing or how I arrived at it.

I think what I am missing is a clear picture of the overall schema of the database in terms of what tables do I need? What fields in each table? What relationships between tables? What lookup fields to place in the “Estimate” table.

 

Even with the experimenting I have done I can see the tool is capable of what I am trying to do. I just can’t get at it.

 

Can you suggest which of the training videos should I go over to grasp the basic concepts more firmly?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,842 Points 50k badge 2x thumb
I suggest that you take a step back from the “weeds” and make a diagram on paper of your tables and relationships and post that. There is no sense floundering and guessing until you get the architecture correct.


Please read my post again,especially the parts where I said that you need this setup


“One Estimate has Many Material Estimate Lines.

One Material Master has Many Material Master Lines.


So you will need to make a new table for Estimates and also a new table for what is called the join table which are the Material Estimate line items on the Estimate.”


I don’t believe that you made the table called Material Estimate Line Items.


These are like what you would see on any invoice


One invoice has Many invoice line items

One Item Master has Many Invoice line Items.


Post back with your progress or contact me directly to get you over this hump. I’m at QuickBaseCoach.com.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,842 Points 50k badge 2x thumb
The drop down lists for the materials when add8ng a materials estimate lin3 item will be from that materials table itself. The whole list of materials will be your drop down list.

There should not be a relationship between estimates and materials. Delete that relationship if it exists.

When you go to add a line item to the estimate you are not creating a new Master record fir a new material. You are choosing an existing material when you add the material estimate line item.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,842 Points 50k badge 2x thumb
I’m not sure of all the videos but if there are videos on relationships and especially on many to many relationships, those are the ones to watch.

Hang in there, and any time now there will be a giant light bulb go off in you head and you will “get” relationships.
Photo of Andrew Aguilar

Andrew Aguilar

  • 110 Points 100 badge 2x thumb


Here is a high level design of what I think I am trying to achieve.

i spent some time experimenting with different look ups and relationships and can get tables to pull data from one place to another but not in a planned / structured way.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,842 Points 50k badge 2x thumb
You are missing the table called Estimates. It will be the parent of Job Cost lookups, I would prefer to call that table Materials Estimate Lines.
Photo of Andrew Aguilar

Andrew Aguilar

  • 110 Points 100 badge 2x thumb
Like this ?

Photo of Andrew Aguilar

Andrew Aguilar

  • 110 Points 100 badge 2x thumb
This is what I have so far

Screenshot of App dashboard. All 4 tables present. Below the dashboard is the Materials table with some data entries.



Similarly - the Process Costs tables with some data entries.
In these two tables is everything I need in the App - its just a case of looking them up and doing som math on certain fields.




I have set up to tables - Estimates and Material Estimate Lines - and have added one field called "temporary field" in anticipation of something being needed in there.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,842 Points 50k badge 2x thumb
If you contact me via my contact info in my website, I will take 15 minutes to sort you out at no cost.

You will need these 3 tables in total if you choose to combine your Materials and Process Master tables into one table.  If it was me, that is how I would do it.

1. Estimates (this will hold a each separate job you are estimating).  It is a Parent Record to Estimate Lines.

2. Materials & Processes (this is a master list of each unique material and their std Costs.  This same table can also hold the costs for the processes. There would be a field here for the Type of Cost "Materials" or "Process". It is a Parent record to Estimate Lines.

3. Estimate Line Items.  These are the line items on the estimate and it is a child of both the Estimate and the Materials & Processes Master costs table.

Here are the Relationships.  There are only two relationships required.

One Estimate has many Estimate Lines.
One Materials & Processes Master has Many Estimate Lines.

There will be summary fields on the Estimate to sum up the total cost of the Estimate Lines for "Materials".  The Materials totals summary field will have a filter 
where Type = Material.


and another summary field to total up the total cost of the Process costs. 
The Process costs summary field will have a filter 
where Type = Process Cost.

I suggest that you start fresh and have just three tables and just two relationships.  Or else get rid of everything that does not match this setup.