Our team has created Forecast__c which is a custom object related to the Parent Account of any hierarchy. Each Parent Account has a Forecast for every month of the account. So, for example, there is a "Account A, Month 10/22" forecast.
Additionally, each Account, and all its children, have various Opportunities. Opportunities similarly have a date, and a Spend. So, for example, Account A has Opportunities x and y: Opportunity x is for 10/22 and lists Spend = $100, and Opportunity y is also for 10/22 and lists Spend = $50.
I would like to create a field on the related month's forecast that sums up the opportunity spend values for that month. i.e., Total Spend for Account A of 10/22 Forecast = $150.
The goal is to avoid Apex. Is there a way to create a filter that matches the (Month and Account) of the Opportunity to the (Month and Account) of the Forecast? Perhaps through use of a Workflow?
Tried so far: Workflow triggered by the creation/update of the Opportunity. From there, I know I can search for the Forecast, and also the other Opportunities of the same Month/Account. Is there a way to use a formula of the opportunities to place in the forecast?
Or else, if you know a better way, I am open to suggestions. However, again, the goal is no Apex.
Additional Info: Attempts to use Master-Detail Relationship fail because records have already been created. Attempts to use formula fields fail because filters don't seem to be advanced enough. Attempts to use workflows have caused issues in calculating the summations (maybe there is a way that I don't know of?). We have previously used Rollup Helper App to allow for the summation of child accounts to parent, but this does not let you filter based on details of another object. I am very new to Salesforce, and do not have experience with Workflows.