I have created a doc to do simple financial projections. It takes the specified timeframe and create the projection worksheeet, and populate the projected items with drivers. Also it could perform some calculations on the projected items to form another projection line item. A simple example is:
- Sales = ASP * Order Volume
- Cogs = Cost * Order Volume
In this case, Sales and Cogs are the projected items, and ASP, order volume and Cost are the drvers.
- Profit = Sales - Cogs, which is a calculation based on the line items
With the housekeeping items out of the way, I’ll jump into the issue I ran into:
So in page DB_Entries, I have a table called DB_entries, and I am having some issues with the button “Calculate Result Items”, which is the calculation for type 3 above. For every Profit item, it needs to know its corresponding Sales and Cogs items in the entries table. and for each item, it must know whether its a revenue item or cost item, for the former you need to add it to the sum and deduction for the latter.
So the problem I ran into is, I have a Nested for loop setup to iterate through each profit entry and each corresponding item drivers, see if it is a plus or minus, and update the _result_holder field. However it seems the results just got overwritten.
For example, for profit year 19, the revenue is 19.52, and cost being 8.71, the profit should be 10.81. Instead, now the profit is -8.71. Something is off in the formula. I have also tried wrapping
modifyrows() and still the same.
What’s odd is, the calculation logic of the normal calculation button, which uses almost the same mechanism, a double for loop, but draws data slightly different, works just fine. So I am not sure what went wrong here.
Any help is appreciated. Thanks!