Forum Discussion

GeoffBarrenger's avatar
GeoffBarrenger
Qrew Captain
28 days ago

Calculation which requires Summary from Other Table

Hi all

I'm trying to calculate a total cost per unit shipped of both Overhead Expenses and Operating Expenses so that I can benchmark a KPI for my services.  I am having trouble summarizing totals from another table.

  • Operating Expenses are easy - we track based on jobs and log expenses. This can include gas, contractors, extra rentals, food expenses, etc
  • Overhead Expenses - I am struggling with because my per unit Overhead expense should get lower and lower the more units I ship, but will also go up the more days into the Fiscal Year I am in.  This requires me to solve for how many days into the Fiscal Year have passed (no issue), and also to summarize how many units I have shipped this fiscal from another table (my problem)

How do I put into a formula, a request to summarize the total from a different table using a parameter? ex:  

var number totalUnitsShippedThisYear = <look in table unit shipped and get total # of units shipped who's ship date = this fiscal year>

Below is the step by step how I am thinking this through!  Currently what I do is pull a summary report, and then hard code the total number of units shipped.  This makes it fairly easy to pull the report once a week - but I would like this to be automated.

Thank you thank you!

-----------------------  Below is the thinking!

  1. Divide number of days in a year into my Yearly Overhead Expenses(a number I provide) estimate (rent, salary, fixed costs) to find my Daily Overhead Expenses (how much it costs just to keep the business going)
  2. I can calculate number of days into this fiscal we are currently (days Since April 1)
  3. I can then multiply the number of days passed this year by the Daily Overhead Expense.  I now know how much I have spent to keep the building open this year.
    • example: if it costs me $365,000 to keep the building running, and I am 15 days into the year (April 15th) I have spent $15,000
  4. Then what I need to do is summarize from another table my total shipped units.  The thinking is - if I am shipping more units, my total Overhead Expense PER UNIT will be lower ( the building still costs money to rent if I ship 1 unit per year or 1 million units per year)
  5. If I can get the summary of units shipped this Fiscal Year(from another table), I can now divide the Daily Overhead Expense by the Total Shipped Units this Fiscal Year
  6. I would now have my PER UNIT Overhead Expense
    • scenario 1: If it is April 15th, and I know it has cost me $15,000 to keep the lights on,  but I have only shipped 1 unit, my Per Unit Overhead Expense is $15,000!!!
    • scenario 2: But if we have been busy and shipped 1000 units a day since April 1, out Per Unit Overhead Expense is only $1
  7. For each unit shipped - I will also have an PER UNIT Operating Cost (which I already can calculate) - based off how many units were shipping in that job (another table) and the expenses from that job (ex: we bought gas for the truck and paid for the driver of the truck by mile)
    • scenario 1:  The 1 unit I shipped cost me $100 of gas.  The operating cost per unit is $100
    • scenario 2: every day I made one delivery of 1000 each - and somehow magically the gas was always $100 (to keep math simple for this - but we would use actuals($0.10 per unit)
  8. I want to now add by PER UNIT Operating Cost & PER UNIT Daily Overhead Expense  to find my TOTAL COST PER UNIT
    1. scenario 1:  thinking back to scenario 1, my Total Cost per Unit is actually $15,100
    2. scenario 2: thinking back to scenario 1, my Total Cost per Unit is $1 plus 10 cents, so $1.10 per unit.  
formulas