I'm building a FM database that performs multiple year forecasts of expenses and I'm considering what approach to use for viewing and furture calculations for other related data later on. An overview of where I'm at right now:
An Expense layout has fields that contain these values (amongst many others) such as:
Start Date of the expense
Inflation Factor to apply to the expense
End Date of the expense
Based on field values in the existing expense layout, combined with additional calculations (not currently contained anywhere), I will be able to determine the total expense for any given year while the expense item is alive.
The additional calculations will for example, look at start dates and end dates of the expense item and inflation for the expense item to determine the final value at the end of a given year.
In the end, I wish to view columns of years with the expense items in rows below. Each expense item would show it's total at the end of each year.
The columns of years only have to appear to whatever the greatest expense End Date is.
After all that, my question is - what's an effective way to deal with this, a typical layout (likely a table view) or a report? Would the calculations for each subsequent year's value be performed by 80 fields for each year in the Expense Table (there has to be a better way), or in Year fields dynamically created in the layout / report? The issue I'm dealing with is I created this in Excel and the translation to FM gets muddy when I keep relating back to Excel.