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!

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)
I can calculate number of days into this fiscal we are currently (days Since April 1)
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

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)
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
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

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)

I want to now add by PER UNIT Operating Cost & PER UNIT Daily Overhead Expense to find my TOTAL COST PER UNIT

scenario 1: thinking back to scenario 1, my Total Cost per Unit is actually $15,100
scenario 2: thinking back to scenario 1, my Total Cost per Unit is $1 plus 10 cents, so $1.10 per unit.

Forum Discussion

Calculation which requires Summary from Other Table