Intelligent Budget Plugin

[red1]

Screen Shot 2014-04-02 at 4.29.30 PM.png (69.67 KiB) Viewed 36697 times

Following the general specifications in the wiki here, I have finished a plugin engine for an advanced and quite intelligent Budgetary Control and Forecasting Module, that can plugin easily into the iDempiere ERP suite. Here i shall describe the working prototype, how it is designed in its code framework, how we can setup a Budget Rules table, how to test it with Purchases or Journal Ledger entries and how to play around with those rules to appreciate its power.

This plugin remains a proof-of-concept prototype which i intend to finish off some further features such as:
1. Automatic Budget Rules Generator from previous years accounting facts (this shall activate the month-on-month forecasting). At the moment the rules demonstrated here are defined manually.
2. Configured Info-Window that shall allow viewing of which Budget Rule has reached or exceeded its terms from the accounting facts table.

The source-code is committed to my bitbucket repo at http://bitbucket.org/red1/org.idempiere.budget
My later posts here shall go through the workings of the source as a project in the Eclipse IDE. The eventual artifacts will be uploaded to my SourceForge repo. As this initial stage, the following posts will be a bit crude but crisp to give a good snap shot of what it is all about. Enjoy this another contribution from my sponsor, SYSNOVA of Bangladesh. Eventually in the end I shall furnish a complete project and developer manual guide to this plugin.

Screen Shot 2014-04-02 at 4.29.01 PM.png (86.73 KiB) Viewed 36697 times

[red1]

SETUP IS STANDARD
You basically can test it from source within your Eclipse IDE or test it in your adempiere-client and idempiere-server with this exported plugin in my SourceForge.net repo.

DEFINITION OF RULES.
Once you can pack in from the plugin the 2Pack AD definition (or pack in manually from SourceForge), you can view the main menu changes:

Screen Shot 2014-04-02 at 4.58.08 PM.png (10.85 KiB) Viewed 36696 times

Then you can call up the Budget Configurator as shown in the previous post and fill in the values. Then we can call up the Budget Planning window (which is a copy of GL Journal) with extra fields in the Budget Line such as percent and period fields.

Screen Shot 2014-04-02 at 5.00.38 PM.png (59.8 KiB) Viewed 36696 times

The Budget Window will always default to a Budget type posting. However we need not complete or post this as it acts as a dynamic and living budget control system, where changes can be realtime particularly for the forecasting processes.
(to be cont'd)

[red1]

Here is a sample BudgetLine setting which produce the above Budget Exceeded exception message.

BudgetLineTab.png (62.09 KiB) Viewed 36693 times

Note the two new fields: Period and Percent. Here in this example, only a Campaign code is set. Thus during Purchasing, any reference of the purchase order to such a campaign shall fall under this budget control which is 10% of the Revenue amount configured earlier. We shall explain that in a little while. Still at this BudgetLine tab, notice that the Accounting ID is mandatory (as we are reusing GL Journal) thus you have to set it to the first account which is Checking Account as a flag that this is used in Purchasing. Any other accounting element will mean it is meant for the GL Journal entry which we will also deal with shortly.

Now we go to the Budget Config window again.

BudgetConfig.png (109.13 KiB) Viewed 36693 times

The prorata field will toggle other fields irrelevant which is the Month-On-Month reference of previous years and the number of current back months forecast field.
The Budget Forecast Years is set to 15 which will go back the last 15 years to pick out the Revenue (Accounting Elements starting with 4XXX) values and then average out or made trend dependent according to the Budget Trend setting. As this is based on the present GardenWorld client which has very little records since 2002 we will be playing with very small numbers but sufficient to see the module work pretty good as expected.

[red1]

The beauty of this software is the use of OSGi based plugins that completely does not require any changes on the main core software itself. The plugin houses the metadata DB changes in its 2Pack, self-activating upon launching, and all code runs on its own by way of a Document Event validator. In fact all newly crafted business logic happens within this DocEvent class.

Screen Shot 2014-04-02 at 5.37.35 PM.png (233.2 KiB) Viewed 36685 times

Been a plugin, it can be stopped without stopping the main ERP suite. Once stopped all the budget constraining rules simple disappear.
Note from the source layout in my Eclipse IDE above how it is very well packed and defined for easy and low cost maintenance even on its own. I also spent a lot of time in putting as much documenting remarks to ensure even i do not forget what or how the code really works. It is quite complex logic that can be improved further but as it is, i put substantial thought also in its design, refactoring sufficient methods for an easy view via the right panel layout in Outline View. I also attempt optimal reuse of code to minimise future maintenance headache.
At the moment, the only classes i extend from core are MJournalLine using the elegant POWrapper to reduce impact both ways.

[red1]

The Revenue based budget control works in many ways:
1. Firstly the revenue can be set in different ways:
a. By setting how many years back it can calculate from. If you set 10 years then it looks back at your past ten years accounting facts for revenue figures and total them up, and then apply either Average or Progressive trend formula.
b. If you set 0 or a hard-set figure, then it will not look at past year revenue but work either (i) hard set figure that you put in (must be above 100) or if its still zero, it can look at the current month's movement. That you can set at the Month Forecast field.
2. The percent field either or with the Credit Amount field. Only one or the other can be used at each rule. In our example above it is set to 10%. That means it will check if your purchase or Journal posting exceeds 10% of the Revenue estimate. If you intend to use a set value as the budget, then set the percent value to zero.
3. Prorata field when checked will disable the Month On Month and Month Forecast fields but just use the year revenue either Average/Progressive and divide by 12 months to give a flat equal distribution. Your budget figure or percentage will also be treated prorata.
At the moment you are free to set any figure or percentages for your budget planning details. In a later improvement, the auto generation can be used to setup a full set of rules according to set periods at set amounts that are derived from a base of yesteryears performance. The use of percentages is more practical particularly in Month Forecast setting where rolling months during the present year can be dynamic and change according to very current performance.

COMBINATION CRITERIA
This Budget Module can make use of combi mix of criteria for example it can be set to a certain Business Partner for a Project further combined with a certain Campaign code and also for a particular period month. However there are some implicit and exclusivity rules depending on the combi sets in use. I will go through some samples in detail in due time to understand what extra rules maybe ambiguous. However the module will throw an exception message if it hit an ambiguous rule.
This is useful to differentiate different set of budget plans

[red1]

Let's test the direct setting for a new iDempiere user who has no previous year(s) track record to forecast its present year revenue estimate. So here we do it by setting a figure above 100 (which means you can use this module for a hundred years!). Below we set it as 400. And further down is the Budget excess exception upon testing. Note that I also turned on the Prorata switch.

Screen Shot 2014-04-03 at 2.33.56 AM.png (25.09 KiB) Viewed 36633 times

Screen Shot 2014-04-03 at 2.24.19 AM.png (60.72 KiB) Viewed 36633 times

While testing this i got a nicer idea for the exception message, "Exceed Budget by: " and append the difference there. So by changing the code with a new line in the middle below, I can have what i just wished for.

Screen Shot 2014-04-03 at 2.46.34 AM.png (21.46 KiB) Viewed 36633 times

Screen Shot 2014-04-03 at 2.47.28 AM.png (21.82 KiB) Viewed 36633 times

Now it is really helpful in informing the user by how much she has overshot the budget.

[red1]

EXPENDING BUDGET TREND
Today i extended further to cater for more trend rules such as shown below.

Screen Shot 2014-04-03 at 4.56.16 PM.png (24.3 KiB) Viewed 36620 times

Screen Shot 2014-04-03 at 4.57.00 PM.png (15.02 KiB) Viewed 36620 times

Now we have few more trends to give more versatile revenue forecasts that will form the base amount when doing budget checking with percentages. Below is the code done to handle this. They are not tested as yet.

Screen Shot 2014-04-03 at 6.32.12 PM.png (289.66 KiB) Viewed 36620 times

To explain further on the trends:
1. Average is the simple yearly average of all the amounts within the Years Forecast range given. It can range up to 99 years! Any figure from 100 onwards will be a exact revenue estimate for the present year to base on. Otherwise the average becomes the revenue estimate.
2. Average+Last is the average added on top of last year's revenue. This will give a projected or higher estimate for this year compared to last year.
3. Progressive is the rate of change from first year till last year within range, and that rate applied to last year's revenue. Now this needs testing as i m not sure i got the formula right (you can see the code snap shot above).
4. Accumulative is adding up all the revenues within the years range stated in the Years Forecast field. Thus this should be used sparingly or for very short range.
5. Year-to-date is the present year (this year) revenue to date. This mean it is most appropriate when used with percentages and it is like a 'startup' company growing along according to what it earned rather than what it did not earn.
Of course these are just control options and I may think of a way where excess expense will not stop the purchasing or GL posting but just flash a pop up warning. (Hopefully because i have yet to know how to do that!).

[red1]

INFO-WINDOW
Using Info-Windows will probably be the fun part which i be focusing a lot of my energy on next.

BUDGET CREATION
First very useful Info-Window will be to pull out the Accounting Facts in previous years range (set by BudgetConfig) displaying each row with Accounting ID, Description, Budget Amount, Budget Percent, Base Amount, To date expense, Budget Balance
They can be in order by Accounts Category, Accounting ID or unprocessed lines first.
The process will be to create budget journallines which will populate the Budget Amount or Budget Percent based on the panel criteria.
The criteria panel for selection: Years Range, Budget Percent or Amount, Formula Trend
The SQL select can be two strategies: Only those that has no present year budget lines, or all fact-accts irrespective with or without budget lines.
The selection checkbox will be very powerful as you can select which lines you want to be processed, and the process can use the panel criteria that you select (I am not sure now how the panel criteria can be used, whether you can use it for both selection and processing, but been the developer i am quite sure they can be done and this can be very versatile. I am also not certain about all the display columns until i actually do it. Then i can judge its practicality or get fresh ideas and edit accordingly).
The resulting resultset remains displayed but the budget percent/amount becomes populated.

Another Info-Window can be more analytical where certain criteria used to pick out those that are nearing excess budget condition or over budgeted.

[red1]

I have tested deployment of the Budget plugin to binary space and the testing with adempiere-client has been good as the screen shots below showed. (The BudgetTrend is also set as 'Accumulative' rule.)

ClientPercent.jpg (56.29 KiB) Viewed 36587 times

ClientBudgetAmt.jpg (54.7 KiB) Viewed 36587 times

The first screenshot shows the use of percentage rule which follows no extra criteria other than this is a purchase. The second screenshot shows the use of a fixed budget amount rule which follows an extra criteria of Project code set to Standard. So this demonstrates that the plugin works well at this stage to differentiate which criteria the purchase falls under. Below here I tried with the Prorata switch on, and again for the fixed amount budget case.

ProrataSwitch.png (35.93 KiB) Viewed 36587 times

ProrataSwitchBudget.png (35.13 KiB) Viewed 36584 times

So this working plugin is uploaded to https://sourceforge.net/projects/red1/files/p2/Budget/ . The org.idempiere.budget_1.0.0.201404060904.jar is the one you install directly into your OSGi console. It will self extract its 2Pack.zip for data model changes. In case that does not work or you want to view the data model only, you can also Pack In manually with Budget.zip.

[red1]

Now I have put in an option for allowing Budget excess to go through which will issue notices. This is controlled easily from the Budget Configurator where the Valid field is unchecked. The code is shown here together with its effect on both Purchase Order and GL Journal. You can drill back to those docs from the Notices.

handleError.png (65.04 KiB) Viewed 36528 times

PurchaseNotice.png (86.35 KiB) Viewed 36528 times

GLNotice.png (82.38 KiB) Viewed 36528 times

[red1]

You can now Generate Budget from an old one by analysing past performance as far back as 99 years right up to recent months! It can also give Year On Year, month to month analysis, grouping them into individual new period budgets.

GenerateBudget.png (38 KiB) Viewed 36288 times

I have made a PDF guide in progress that contains mostly specifications of the module. As for the code, it is done quite to specs awaiting for last tuning testing before i export as a plugin.

I am then ready to port this all over as compatible to ADempiere 360 series based on Carlos Ruiz latest 361. I been keeping in mind the code as backward in that regard while writing it.

[red1]

I made some vast improvements:
1. Including related Invoices and Payments
2. Allow Sales Targeting! - an idea while discussing with Thomas Bayen in the chatroom a while ago where he described his use case of keeping track of which customer are doing beyond their targets.
3. Tidy up the Config window with Debug Mode that will log all events irrespective whether they exceed or not which is good for watching as figures draw nearer to their targets or limits.
Screens below explain this further.

IsSOTrx.png (17.53 KiB) Viewed 36345 times

The use of isPurchasing flag when checking if documents are sales or not, then use the string set to prefix to the SQL clause resolve the new feature in the most minimalist and elegant manner.

SalesTarget.png (54.33 KiB) Viewed 36330 times

I have to put in a checkbox for isSOTrx renaming it to Sales Target to differentiate between budget rules that are meant for Sales or Purchasing side. With a final check in the code, they are ready for same elaborate matching of all such criteria as in purchasing, right up to Invoice and Payment that is of Sales nature.

BudgetConfig.jpg (54.27 KiB) Viewed 36345 times

The Stop Excess? box is to allow exceptions passing through and a log of it kept in the Notice table for later checking.

Sales Target performance measure will automatically write to Notice when reached and will not affect Sales operations i.e. excess of targets will not throw exceptions. Final code in the BudgetDocEvent below does that:

HandleException.png (191.8 KiB) Viewed 36330 times

[red1]

The Sales Target testing went OK for a POS Order, triggering testing on its generated Invoice and Payment records which is considered associated with the Sales Order and thus ignored. A Notice is then issued as shown here.

Notice.png (61.92 KiB) Viewed 36297 times

[red1]

Porting over to ADempiere 361 (2012-11-5) of http://bitbucket.org/CarlosRuiz_globalqss/adempiere361, i note here some things that need to be changed and what does it means.

1. Have to move model classes to org.compiere.model which means separation cannot happen, and classpath hell can, the very issue that OSGi tries to solve.

2. The Event ModelValidator get calls more often because the event is checked each time before its qualified. What it means? Simply more work to debug.

Screen Shot 2014-05-09 at 6.07.33 AM.png (30.93 KiB) Viewed 36249 times

3. Model casting is back to old school where you cannot cast extended models while in Query. You just overload directly the models and store them all in same org.compiere.model package of your local custom project .

4. More apparent, everytime you want to refresh the module with changes, you need to stop and start again the whole application. In iDempiere you need not and just refresh the bundle ID.

Below are POCs that the back porting works.

POCGL.png (59.75 KiB) Viewed 36242 times

POCpurchase.png (48.2 KiB) Viewed 36242 times

[red1]

Budget module for ADempiere 361 FINAL is now available at http://sourceforge.net/projects/red1/files/p2/Budget/
1. Budget360.jar is for putting under <ADempiere_Home>/Packages/Budget/lib/ and then RUN_setup.bat or sh.
2. Apply either the migration script or 2Pack which either one will make the AD changes.

The source is committed to https://bitbucket.org/red1/budget360 and i will be doing a full test plan next and also:
1. Reporting format for budget vs accounting performance analysis.
2. Complete the Budget operations Manual.

Meanwhile you can read the manual from page 11 to 17 to learn how to setup for ADempiere. Please report here any issues and i will look into it.

BudgetCover.png (89.43 KiB) Viewed 36172 times

[red1]

I am making things as dynamic as possible. The Budget Configurator can set the Revenue and Purchasing IDs instead of the hard-coded '4-Sales' and '11000-Checking' accounting elements. This will cater for different Chart of Accounts and different consideration for Revenue tracking.

BudgetConfigDynamic.png (35.81 KiB) Viewed 36111 times

I also made the BudgetLine percentage reference to be open to any other accounting facts. If left blank it shall default to the Revenue ID in the configurator. In this way the user can set a particular budget rule to refer to another set of facts figure such as Tax amount, Asset value, or another spending figure. This can be a compromise between a more complex rules engine and just a single hardcoded 4-Sales category.

BudgetJournalLinePercentageBase.png (37.19 KiB) Viewed 36111 times

[red1]

A more powerful feature is been done this past few days which is to allow budgeting at an Order Line level to check on Product and Qty. Finally coded and tested to be working you can pick up the latest version 0.5 of the Budget.pdf to read pages 41-46 and 68-73 with source-code expose.
As usual the source-code is pushed to bitbucket and a deployment jar Budget360.jar for Adempiere361FINAL is uploaded to same spot as the PDF at http://sourceforge.net/projects/red1/files/p2/Budget/

Screen Shot 2014-05-21 at 11.30.27 AM.png (58.87 KiB) Viewed 36020 times

My task rest of week:
1. Write up and publish Fitnesse testing
Next week:
1. PrintFormat to report each BudgetRule, Document or Facts historical analysis.
2. Port back to iDempiere
3. Port back Fitnesse tests.

[red1]

I am doing some more clean up here and there and that includes the OutputMessage box, putting a chapter on it in the PDF. Also dabbled around with my love for promotional graphics. The illustration of the message box can be a good snap shot reference. Hope to be fresh for the FitNesse testing routine code-work around this weekend.

newProdQtyMsgBox.png (49.77 KiB) Viewed 35988 times

Screen Shot 2014-05-22 at 11.04.36 AM.png (57.63 KiB) Viewed 35988 times

cover.png (495.78 KiB) Viewed 35988 times

OutBoxReference.png (365.29 KiB) Viewed 35896 times

MainScreens.png (174.79 KiB) Viewed 35896 times

[red1]

In the thick of setting up the Fitnesse fixture and story, i made some discoveries and improvements. Firstly the Populate Facts has a better method to CreatePeriods which is to look up first via the newer findByCalendar method instead of directly MPeriod.getC_Period_ID(getCtx(), date, Env.getAD_Org_ID(getCtx()));

Screen Shot 2014-05-31 at 6.29.52 AM.png (31.81 KiB) Viewed 35896 times

Then the parsing Fitnesse value to return record ID by Carlos Ruiz cannot handle String blanks well. And i solve it with a catch and subsequent values must be zero if it follows after a number in the row before it.

Screen Shot 2014-05-31 at 6.29.28 AM.png (23.9 KiB) Viewed 35896 times

The fixture now handles runtime creation of Budget Plan rules which will not commit but roll back together with any document transactions later. However it be good to use this on development server.

Screen Shot 2014-05-31 at 6.29.08 AM.png (52.97 KiB) Viewed 35896 times

The Fitnesse fixture code will automatically populate accounting facts for 3 years of what element you place in the column. It will not be committed to DB at the end of test. However i made the Calendar year and periods commit to DB with all periods within opened. This is for convenience when using a new DB which needs past years to be present anyway. So this is a good shortcut to use first while testing at the same time before further Budget operational development.

[red1]

I done a major refactoring, separating from GL Journal/Line use for Budget Planning, which now has its own B_BudgetPlan and B_BudgetPlanLine tables.

BudgetPlanLine.png (90.4 KiB) Viewed 35824 times

This allow cleaner code where my own Callout to do LookupBase can be done on the fly while planning a budget rule. Later i will post on this LookupBase that does historical checking of sales/purchasing/account facts where relevant to the rule at hand.
Find in the SourceForge repo to get the latest files including FitNesse testing. I will post next about how to do the test and its results. All source is at my bitbucket.

SFrepo.png (67.72 KiB) Viewed 35824 times

[red1]

You can learn up FitNesse here http://www.adempiere.com/FitnesseSlim . Below is based on the FitnesseScript.txt in the repository above.

FitnesseSetup.png (235.04 KiB) Viewed 35824 times

Below create Year Calendar Periods, Populate sample base accounting facts for Revenue or Percentage Base reference.

setupPlan.png (138.49 KiB) Viewed 35823 times

The Budget Plan creates the budget rules for Sales, Purchases, and GL journal entry. The activity of transactions against those rules. However knowing the budget module, it will not fetch the RevenueEstimate which is stable for year to year basis and made static for daily operations. However the budget system can work for right up to date current accounts. That is done by setting the Configurator to Previous Months. But it needs a refresh right after the Sales activity to get it into account before the Purchasing can pick up the realtime revenue figures.

BudgetPlanActivity.png (53.68 KiB) Viewed 35823 times

This is to demonstrate how versatile the budgeting can be. Thus in the Activity above I included another series that allows changes to the revenue parameters such as range of Previous Years or Previous Months, and do a RevenueEstimate again (in actual budget menu you run a blank Populate process).
Below, the resulting notices (DebugMode) produced are issued. Note that I have the GetAccounts feature on. It can be commented off to save time and space, together with the Accounting Facts query below it.

BudgetNotes.png (144.85 KiB) Viewed 35823 times

Another last image below gives the ending accounting facts proving that the sales and Gl entries worked in full. Note however that the Product Hoe Qty picked up another 40 before it. So this is very helpful because it saves me time to guess where potential bugs may arise. Running Fitnesse will execute a long series of steps that usually takes hours to go through systematically. Note also that the the plans and activity are not committed to database but 'rolled back' so that consistent testing of steps and results can be repeated. Only the Calendar Year Periods and opening of them are committed as they are useful for later use.

EndAccts.png (90.91 KiB) Viewed 35823 times

[red1]

Now i am ready to debug. I put the Budget Plan, Activity and the Notice results side by side so as to easily figure out what i have to check on besides the one i mentioned earlier.

BudgetPlan.png (43.56 KiB) Viewed 35823 times

BudgetPlanActivity.png (53.68 KiB) Viewed 35823 times

results.png (105.23 KiB) Viewed 35823 times

I noticed the following works:
1. Debug mode label indicating that all events are logged
2. Sales and Purchasing sides detection label
3. Balance or Short detecting positive or negative difference with budgeted targets
4. Percentage or Fixed Amount control
5. Prorata (divide by 12) when a Period Month is set in the budget plan rule
6. PercentageBase reference (16100 - Land) as alternative to Revenue
7. GL Journal checking
8. Budget Trend label

[red1]

That bug solved, and now testing BudgetTrends.

Populate10plus1.png (16.63 KiB) Viewed 35819 times

The revenue accounts is built starting at 10 incrementing each month with 1 from three years back.

Trends.png (74.25 KiB) Viewed 35819 times

Trends starting from A(C)cumulative including the initial 3 years, (A)verage, Average+(L)ast and (P)rogressive, all giving different output for easy and convenient examination on a single page. The range is switched from 3 years to 6 months to examine the real-time dynamic feature up close.

TrendsOutput.png (65.23 KiB) Viewed 35791 times

I dumped the Fact_Accts to have a quick visual chart of the revenue accounts and snapped the last 12 months below.

last12Mths.png (56.97 KiB) Viewed 35819 times

[red1]

Here is another full test, firing away. It has enough data to make reference in planning for correct rules. Note that some rules are inclusive in assessing base data as the only explicit rules are Project/Campaign/Activity.

FullTest1.png (26.61 KiB) Viewed 35818 times

FullTest2.png (83.54 KiB) Viewed 35818 times

FullTest3.png (45.06 KiB) Viewed 35818 times

FullTest4.png (171.37 KiB) Viewed 35791 times

[red1]

After solving a last bug on Progressive Trend, and streamlining the handling of params of PreviousYears/Months and PeriodIDs in Select<methods> from source records I conducted the same set of a previous test at 3 years setting to see how the BudgetTrend revenue series of C,A,L,P shows up. You can compare this with the 'TrendsOutput.png' image 2 posts above.

3YearsPrevious.png (71.92 KiB) Viewed 35791 times