I am organising a non-profit event with crafting workshops in a month. People can order 3 different cardsets or sign up for 1 of 2 workshops. I am trying to calculate how much each thing cost seperately (able to do that), how much profit we will make per product for the charity (can do that) and how much I need to order in crafting supplies to have enough (getting stuck there).
Right now I have 3 tables: Product/Line Items/Project
Product has Item name, retail price, amount of pieces, price per piece (formula of retail price/amount of pieces) and link to Line Items
Line Items has name (formula of product and amount), Product (link to product), Amount, Calculation Column (lookup of price per piece), Price per amount (price of product in that amount), Projects (link to Projects)
Projects has name, used (link to Line Items), total price (roll up of all price per amount), amount ordered.
A product can be used in multiple projects. 1 project might need 3,5 sheets and is ordered 4 times, while another project need 2 sheets and is ordered 5 times. I want to know the total amount of sheets necessary then.
Who can help me get unstuck? I keep going in circles how to know how many products I need.