Is there a method for lateral summation (without Apex)?

Question

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.

Answer 1

This is easy enough in Salesforce, but will need two different flows. The first flow would be a Before Save flow that checks for the presence of the Account/Month combination; if not found, creates a new Forecast. After that, assign the Forecast to the Opportunity via a lookup field on the Opportunity. The second flow would be a Record-Triggered flow. The goal here would be to sum all of the opportunities for that month and account, and then set the value for the Forecast to the newly summed value.

---

That said, have you seen Forecasting? Salesforce has a few different models available, and can do some pretty cool stuff. Combine that with the ability to run reports that include forecasting data, and you might find that all the information you need is already available out of the box. It would even be possible to add this report to an Account layout, allowing you to visualize the data when viewing records directly. Without knowing what your business logic endgame is, this may not work for you after all, but I'd consider it at least worth looking at.