I had a similar request from Treasury as well. Their existing process involved recieving individual bank transactions and a treasury analyst manually inputs it onto a spreadsheet. Each day would be a spreadsheet of daily invoices and they link all of that up to do their analysis each week for an 18-week forecast (later changed to a 13 month forecast - more on that later)...well you get the idea. So here's my CAsh app in a nutshell.
I could have just setup using standalone essbase but I figured I could use Planning to leverage the security and web features. At the time, we were on Planning v4.01, which we all know is pretty painful for a developer, however usable once you get it rollin. (it includes Essbase 7.1.3).
Treasury wanted an 18 week forecast each week based on the prior week bank cash data. Since this would be a weekly time scenario, I initially setup the application with a 53 week Time Period (W1,W2,W3,...,W53)
I ended up with 6 dimensions:
1Accounts (*Accounts based on summary report)
1Time Periods
2Total
3W1 - 12/29/08-01/04/09 (*Alias week range generated via script)
3W2 ... W53
1Years
1Scenarios
2Current
2Potential (*Potential scenario "what-if" driven)
1Versions
2Week1 -
2Week2 - ... Week53 (*Each weekly forecast saved as a version)
1Entities
2Restricted Plants
3...
2Unrestricted Plants
3...
Using a cash report provided by Treasury, it provided the basis for the Accounts consisting of the Net Operating Cash Flow accounts provided by bank data (bank data fed into SQL db then generates a txt file and uploaded into EB each month via batch and maxl scripts), and driven by Beginning CAsh Flow Balance all part of a business logic calc script giving the Ending Liquidity for the first week. This is used as a basis to forecast the remaining 17 weeks. The business calc scripts were based on the formulas in the summary report provided by Treasury.
On the Planning side, I created a few webforms, one to input user variables to feed the business calculation. A second webform containing the bank data load accounts with the 18 week forecast in columns (this one was setup for the end users). A third webform based on the second but with additional override accounts for the Treasury admin to make adjustments(admins webform). The Planning security does a good job of controlling which webform and accounts the end user can see. After it was all said and done, Treasury wanted to change the 18 weeks to a 13 month forecast instead… So after spending 2 months building this app (by myself mind you…) I had to basically rebuild since the Time dimension is set on the initial Planning build, so the change requires a rebuild of the whole app again. So I had to change up the Time Period to 13months and Versions to FY/Month (i.e. Jan09, Feb09, etc.). Don't you just love it when requirements change!
So once I got the application up and running, here's a quick summary of the weekly process:
-bank data received and stored in a SQL db. Weekly data queried and extracted to a text datafile
-scheduled server script picks up data file and loads into Treasury cube
-end user accesses Treasury web application and inputs variables, reviews and verifies bank receipts
-Treasury admin accesses web app and reviews variables, verifies bank receipts, and makes necessary adjustment
-once admin saves webform, it kicks off business calc to forecast 18 weeks
-end users can run HR Treasury report afterwards to analyze and distribute
Well that's my version of our small cash app in a nutshell. Hopefully this helps? Or I just wasted all your time!
|