I am working on a financial analysis project for my company. The current "workstation" that I'm working on shows cost incurred compared to open commitments (primarily open purchase orders not yet fulfilled) and budget to ultimately show the Estimate At Completion (EAC) for the end of the year. The problem that I'm running into is that some of the POs will not be fulfilled until the next year thus moving the cost incurred into the next year. I need to be able to allow a manager to adjust the open commitments to account for this variable.
The solution that I have so far is essentially a simple table arranged by General Ledger (GL) account names calculating the total cost, open commitments, budgeted amount, and EAC. I also have started creating variables for each account to hold the +/- adjustment amount. I added an expression to show this variable for each account in the table (nasty if statement...need case statement but may consider calling it as a function or doing something like naming the variable the same as the account name and doing some variable expansion tricks). Finally, I would like to let the end users modify the variable directly in the table instead of having to go to an input box. The problem with an input box is that I can't find a way to show the open commitments forcing the user to focus on the input box, then go back to the table to determine the adjustment, then back to the input box to enter the change, then back to the table to see if it had the desired effect.
It seems that you may be able to have an array stored in a variable. I wonder if I could store something like (CostToDate, OpenCommitments, Management Adjustment) in the variable so that I could possibly show all of the data?
Any thoughts, suggestions, criticisms?