Create Production Report to Calulate Shorts and Total Revenue

I am trying to create a report for production scheduling. I have been able to use some vlookup functions, but I am stock trying to calculate if an item is short how much revenue it affects. Some of the items on the tab "prod schedule" are used in more than one place as shown by the "CML Data" tab. I need to find the items that are short in the "No product Report" tab and summarize on the "prod schedule" tab how much total revenue is being affected. Any help will be greatly appreciated!! I have been trying for two days now and cant figure out anything!!!