BI Module Concept – Forecasting
Profit and Loss Forecasts – Budgets
The Profit and Loss Forecasts – Budgets model could be set up as a BI forecasting module within an ERP system to further utilise the features of the model.
After a number of years of forecasting and budgeting on spreadsheets I found that generally I was applying rules to each general ledger account, a set of broader rules to each cost centre in terms of growth and then global rules to the whole file such as CPI increases.
So also being a process improvement person I thought it shouldn’t be necessary for accountants to sit at a computer literally for weeks to manually project out trial balance figures to obtain forecasts and budgets. Obviously it is because there isn’t really any other way for it to be done with confidence to date.
The most important things with forecasting and budgeting is that you know how you’ve arrived at your figures for the purposes of explaining the variances and the other important thing is that the calculations are correct in arriving at them. The program I’ve developed satisfies both of these and saves a lot of time. The assumptions are keyed into the setup areas of the program.
The other thing with this program is that once everything is set up for the first time you use it, thereafter the bulk of the input work can be done by a less expensive data entry person. It only needs the accountant to be involved later in the process to make adjustments to some prior rules if necessary and to put in any manual entries which the program still allows you to do for the purposes of flexibility.
There are also summary reports in the program so scenario analysis can be done when some rules are to be changed and also a forecast can be compared to a previous forecast. Also the program can project forward up to 24 months so one year can be compared to the previous year. It is also not necessary to forecast every period – only when a forecast is needed. The formulas in the program will adjust the projections and comparisons for the full year forecast will still be as compared to the previous forecast.
So then the next step is it shouldn’t be necessary for anyone to have to manually key the trial balance into a program. Even if a trial balance is downloaded from an ERP system into an Excel file it would only be able to be copied and pasted into this program by expense category by cost centre given that all the non-data entry cells which contain the formulas needed to be protected. There is also a need in the program to key in Month figures as well as YTD and Last Year total figures so that all of the data is there for the various rules that can be applied.
So this is where the advantages can be seen if it were a stand alone module in an ERP system as there would be no keying of the trial balance. I don’t believe its ever been done before. The Forecasting module would just read it in from the General Ledger module.
Also an Excel file has limitations. Whilst this program has been created so it doesn’t increase in size by producing extra sheets in the file or extra rows and columns of data, it does have to have a capacity which is 5 cost centres and 161 general ledger accounts per file or copy of the program. These numbers were decided on mainly to limit the number of pages of reports that could be produced from 1 file to a concise level. If it were to be a module in an ERP system cost centres and general ledger accounts would be able to be added indefinitely and fitted into the account structure as desired.
The other 2 programs could also be developed into web based models:
- Weekly Business Analysis – this program is to measure non-financial variables against each other and against revenue by day and by item/asset. This is key in monitoring asset utilisation and an example of its use as per the video for this program is for transport where time, distance, weight, number of deliveries, etc can be measured and compared from asset to asset and against historical data.
- Employees & Resources Charge Allocations – this program is to charge out time so it is essentially a timesheet system which is useful for professional services organisations. It shows total costs against projects and cost centres from various employees as well as other resources that are charged out.
The intellectual property for these 3 programs is for sale to realise this concept – refer to the videos on the 3 product pages.
Original Pricing Levels of the Models
The models are by Business Area. The size of a business and the level of detail at which it wishes to segregate its data as well as the model itself will generally determine how to arrive at these Business Areas – refer also to Data Capacity Levels below on this page.
Models
| Model Name | Business Area Scope |
|---|---|
| Profit & Loss Forecasts – Budgets | Business Unit with up to 5 Sections |
| Weekly Business Analysis | Section of a Business Unit with up to 4 Data Categories |
| Employees & Resources Charge Allocations | Section of a Business Unit with up to 14 Employees |
Prices are AUD excl GST (Goods and Services Tax of 10% needs to be added for sales within Australia)
| No of Business Areas | Profit & Loss Forecasts – Budgets | Weekly Business Analysis | Employees & Resources Charge Allocations |
|---|---|---|---|
| 1 | $870 | $620 | $560 |
| 2 | $1,260 | $950 | $880 |
| 3 | $1,650 | $1,280 | $1,200 |
The same incremental amount applies to any further business areas for each model.
The models are supplied on CD’s with User Guide booklets.
No transport or handling charges will apply within Australia.
There are no ongoing fees or annual charges for the models.
There are no additional costs for extra users in the same Business Area.
There is free support for 6 months from the purchase date of each model firstly by email outside of normal business hours and then if needed by phone. A consultation in person is $85+GST per hour.
Email and phone support after 6 months from the purchase date is $25+GST per query.
Payment needs to be received for a product before it can be distributed and once it is distributed it cannot be refunded.
On each purchase the Business Name and Business Area details will be assigned to the Model. Care should be taken when providing these details to ensure they are spelt correctly as they will appear as titles on the reports.
Multiple Products DiscountsA discount will apply to the cost of the first business area of a product for purchases of different models as follows
| Purchase Type | Discount |
|---|---|
| 2 different models | 10% on the first Business Area for each model |
| 3 different models | 15% on the first Business Area for each model |
Data Capacity Levels of Models
As can be seen from the Business Area Scope at the top of this page the Business Area for the Profit & Loss Forecasts – Budgets model is shown as a Business Unit. This model is intended to cover a wider area of the business than the Weekly Business Analysis and Employees & Resources Charge Allocations models which are intended to be at a lower level of detail and show their Business Area as a Section of a Business Unit.
The following Data Capacity Levels information should be used as a guide:-
Profit & Loss Forecasts – Budgets
A single Profit & Loss Forecasts – Budgets model can contain up to 5 Sections of a Business Unit. These Sections could be either geographical or by product type depending on which best suits the business. The growth rates are shown by Section. Up to 161 chart of accounts line items can be shown in the model. Operating expenses can be split into 9 categories. Smaller accounts should be combined as joint figures on the forecasts.
Weekly Business Analysis
The capacity of the Weekly Business Analysis is that it is divided into 4 Categories, which each have 3 Sub-Categories and with each of those having up to a maximum of 12 Item Identities. An Item Identity is a Business Resource – which could be an employee, subcontractor, fixed asset, etc. Therefore there is a maximum of 144 Item Identities that could be used per model though they need to fit within the Categories and Sub-Categories to make those sub-totals meaningful. Depending on the size of the business, each of the 4 Categories in this model could be made to represent a Section of a Business Unit, but the summaries of the cumulative data are only at the total report level – (that is, not at the Category level), so that will need to be taken into consideration when deciding the break down of the data.
Employees & Resources Charge Allocations
The capacity of the Employees & Resources Charge Allocations model is that it can accommodate up to a maximum of 14 Employees and 6 other Business Resources – subcontractors, fixed assets, etc. It also caters for up to 52 Charge Allocation Areas – being cost centres or projects, which can be split into 3 sub-groups. There is also provision for up to another 8 allocation areas for non-chargeable time such as annual leave. Depending on the size of the business, each of the 3 Sub-groups in this model could be made to represent a Section of a Business Unit, but the number of Employees may ultimately determine how many of these models are required. Also the summaries for this model are only at the total report level – (that is, the sub-groups do not show on separate reports), so that also needs to be considered when deciding the break down of the data.