I am looking into CODA as a solution for developing and publishing our annual budget. Our annual budget can be divided into two main sections (revenues and expenditures) and further subdivided into other detailed pages by Department. The data we use to develop the budget cannot live in CODA, as the budget is developed in our ERP system. The budget pages in the book are designed like so:
My inclination is to import the data for each budget year separately and then link all of the data together in one table to form the table that I can create views from so that the data can be easily updated for new budget developments. I would be curious to hear thoughts on data organization and the best way to proceed forward.
I have been an SAP R/3 consultant for 25 years. SAP has settled on the table structure below in their most modern (S4/HANA) implementation. It is now basically a giant pivot table.
Plan version - an indicator that identifies different version of the budget. There is usually an official version, and a few other versions, depending on organisational needs.
a. Plan/actual indicator (this can be a specific plan version.)
Fiscal Year
Fiscal month
GL Account
Cost object (cost centre, funds centre, etc)
… Additional dimensions that needs to be reported on. (Product, customer group, etc) Optional.
Amount in Currency 1
… Amount in Currency 2 (Optional)
Notes.
How many tables you have, will depend on your data volume. It seems like the rule of thumb for a Coda table is about 10,000 lines. If you have large volumes, I would have a table per fiscal year.
a. If you have a single table everything else is just summarised views of that table.
b. If you have annual tables, you will need to have a master table with your reporting dimensions, which you will use to filter in the information into annual/ monthly columns in your master table for reporting. (Reporting dimensions: GL Account, Cost Object, + optional dimensions. The master data will need to have a check to ensure that all dimension in the annual tables are reflected in the naster table.)
Let me know if you have any questions.
Rambling Pete
We cannot use the ERP system’s report for presentation purposes. I work with a government organization and the budgets have to be presented in simpler terms than the ERP system allows. Furthermore, there are written sections (other than the budget tables) that get incorporated into the document.
I would prefer to have the annual tables that feed to a master table. Prior year actuals should not change, so those tables would not need to be updated. I would only add a table to notate a change at each step of our budget process. The actual development of the budget will remain in the ERP. As stated above, my intent is to use the CODA to streamline our presentation.
Assuming I go with separate (annual tables) that feed a master table, I assume the following column layout for the smaller tables would be acceptable as column 1: Org, Column 2: Obj, Column 3: Project, and column 4: Currency amount. Where I am falling short in CODA is the functions that could be used to incorporate the data from the smaller tables into the master.
Thanks for all your help. Hope I am explaining correctly.
Thanks Hendrik. This seems like the single table format that Piet and I discussed above. I am interested in a multiple fiscal year table format. Thanks
I was able to create a template by creating a table for each fiscal year that feeds into a master table. To pull the values into the master table, I used the filter function below.
[FY22 APPROVED].Filter(ORG=thisRow.ORG AND OBJ = thisRow.OBJ AND PROJECT=thisRow.PROJECT ).[2022 APPROVED]
