Stock Budget (Item usage budgets) and Basic forecasting

[Tropicalrambler]

Challenge:

ERPNext currently allows you to create budgets against Cost Centers and Projects, with the source being the account. For financial reporting this is fabulous. The only limitation there is the Monthly Distribution for the budget. There is an open issue (#7095) for that and our team will be working to resolve that soon. The problem still remains that you cannot track Stock Entry of type Material Issue against an "expected usage amount". You can certainly revise it, but it would be fantastic for field managers to keep track of how much they have used of a specified Item from their plan. This inherently differs from the financial budget, as Purchase Receipt or Purchase Invoice date is not the same as Stock Entry > Material Issue date.

Proposed Solution

1. Add an option to the Budget DocType to do so against Items. In this case, the user specifies each individual Item desired on the list, and the Budgeted qty of usage with its UOM. Conversion behavior would be as specified in Item DocType.

2. The same Monthly Distribution or pending Weekly Distribution would apply, and the user just selects the appropriate distribution to clarify how the item will be used.

2.1 Changes to the type of Distribution would be necessary:

• [ ] Add a Distribution type, where user can select "Percentage" or "Unitary". A unitary distribution would require an integer or float number, that when manually entered, would alter the sum total of the periods indicated in the Unitary distribution. When percentage is selected, the behavior is as before: A monthly distribution assigns 8.33% per month by default, and user can change it manually.

• [ ] When in Unitary distribution, user can specify, for each row, the following fields: Lower limit -2 Std deviations from Budgeted -1 Std deviations from Budgeted Budgeted +1 Std deviations from Budgeted +2 Std deviations from Budgeted Upper Limit

Each field refers, of course, to the UOM and qty, which shall remain the same for the entire unitary stock budget.

• [ ] Given that it is a data-intensive DocType, a quick table entry functionality, or having the ability to copy from an excel table would be beneficial to speed up entry. (The source would be an excel file where historical data was analyzed, a forecast model was created and for each week/ month there is a specific distribution of the forecast model for qty to be used.

3. The report

• [ ] A summary view in Stock is added where all items with budgets are listed,

• [ ] with one column showing UOM used, and the other columns show the months/weeks. The rows for each item show either budgeted or actual qty of each amount. A single variance column is shown for the entire period, and an option to show/hide individual period variance columns allows the user to see the difference.

• [ ] Each actual use (taken from Material Issues for that Item for that period = one cell in this table) qty number should be color coded as such: If material issues are within limits of budgeted amount, the number is "green". Any deviation above or below the budgeted amount should therefore gradually color code into the "red", passing through "yellow", "orange" and then "red" when the actual amount of all material issues for the specified period exceeds the budgeted parameters.

• [ ] Selecting an item's checkbox on the left of it in the table, will display a Frappe chart /graph showing the actual amount against the budgeted amounts, and their lower limit, -2, -1, +1, +2, and upper limit specified in the Unitary budget type respectively.

• [ ] Add a feature to enable comparison of two years divided as chosen: monthly or weekly.

• [ ] Add a feature when creating the budget to automatically generate the budget (and control graphs) based on past year material issues. So, for budgeting 2019, user can select the years to include in the calculation, such as 2014, 2016, 2017 (intentionally leaving 2015 out for example). This would save data entry for each item and enable a quicker budget generation process for material usage.

[Tropicalrambler]

This issue has mentioned some features of what is being proposed here: #4482

[w-ubx]

hi @Tropicalrambler, is item 2.1 already implemented? if yes, what branch please? Thanks!

[meaziz]

@Tropicalrambler what are the updates here? where to look at?

I think the proposal is pretty solid but the. monthly distribution should be a optional as some projects may not need that throughly planning upfront.