How do I calculate cost per development hour from an average?

  • 0
  • 1
  • Question
  • Updated 9 months ago
  • Answered
I want to automate the process of calculating the cost of development, based on an entry field (development hours) - so when a developer enters their project hours, it automatically calculates the cost based on our team average.

Any thoughts/assistance would be greatly appreciated.
Photo of Ron

Ron

  • 300 Points 250 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
Can you tell us more about your tables and relationships and also give a verbal description with example numbers of the calculation?
Photo of Ron

Ron

  • 300 Points 250 badge 2x thumb
I have my main table (Project), which is the app that contains the general project information (Name, type, creation date, owner, etc).  Within this is a relationship to another table, that allows the developers to 'code' their time to the project (Dev_Hours).  The sum of the entries are then displayed in the Project table as Total Design Hours.

So next, I would like to take the Dev_Hours sum and, in a new field, output a cost.

For example let's say it costs an average of $65 dollars per hour to develop and there are 12 Dev_Hours input, I would like to create an additional field (next to Total Design Hours) in the Project that displays the cost.

Hope that helps - and this may be a very simple formula to do, but I am struggling with this one...

Thanks in advance for your help and please let me know if you need any additional information.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,842 Points 50k badge 2x thumb
The formula to calculate cost would be

[Total Design Hours] * 65
Photo of Ron

Ron

  • 300 Points 250 badge 2x thumb
That's just it - it doesn't show a total.  I set the field up as a Numeric (formula) and nothing happens.  If I switch it over to Duration, it will calculate it out as Days just fine. Is there a specific type of field I need to set it up as, other than Formula - Numeric?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,842 Points 50k badge 2x thumb
OK, so if this is a duration field type for 

[Total Design Hours] 

Then you will need to convert that to be a numeric field type before multiplying

ToHours([Total Design Hours]) * 65

That is because when you have a duration field, even though it happens to be named "hours", it is a a duration which could also be expressed in seconds, minutes, hours, days, weeks ... so you need to get it converted ot the number of hours if you are going to multiply by an hourly rate.
Photo of Ron

Ron

  • 300 Points 250 badge 2x thumb
Apologies for the late follow-up - travel/schedules bogged me down for a bit. So here's what I wound up with:

If([Designer]= "Name",
[Hours Worked] * 40.9,

I adjust the hours-worked formula based on cost relative to designer.  Where I was getting hung up is the closing parenthesis (I needed 14 and only had 1) - once I figured that out, the formula worked.  So now, when I select a designer from my pull-down, it automatically calculates based on the hours worked and auto-populates the Cost field.
Photo of Ron

Ron

  • 300 Points 250 badge 2x thumb
So for anyone that would like to use this (or try it out) here's the steps.  To start, create the following three fields:

Designer (or any other name that you need) Type: Text Multiple Choice
Hours Worked (or any other name that you need) Type: Numeric Field
Cost (or any other name you need) Type: Numeric/Currency)

Input the names in your Designer list; in the Cost field properties, locate the formula field and enter the following information:

If([Designer]= "Name",
[Hours Worked] * 40.9,
If([Designer]= "Name",
[Hours Worked] * 38.6,
If([Designer]= "Name",
[Hours Worked] * 34)))

Enter as many formulas as you need.  To close out your formula, use the same number of parenthesis as your If statements.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,842 Points 50k badge 2x thumb
Ron,
Good to see that you made it work.

Here are two suggestions for the future

You only need 1 IF in QuickBase, so you need to undo your excel background.

If(
[Designer]= "Name", [Hours Worked] * 40.9,
[Designer]= "Name", [Hours Worked] * 38.6,
[Designer]= "Name", [Hours Worked] * 34)

But the right way to do this is not to hard code it.

There should be a table of designers and their rates.  Then setup a relationship where 1 designer has many rates and pull that down as a lookup field for the Rate.

Then I would set up a snapshot field on that rate lookup https://help.quickbase.com/user-assistance/setting_up_snapshot_fields.html so that you do not change the historical records, when the rates are changed over time.
Photo of Ron

Ron

  • 300 Points 250 badge 2x thumb
Thanks for the additional info (for guys like me, this is the stuff that helps tremendously!).  The single If statement does make it so much easier!

The additional ideas on the relationship table is brilliant, as is the snapshot.  Going to give those a try so you may be seeing me back here asking questions when it doesn't work :)  
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,842 Points 50k badge 2x thumb
Correction to my post above.

There should be a table of Designer with their Rates.

Then set up a relationship where 1 Designer has Many Orders (or whatever you call your table that now has the formula)