Icon

Financial Reporting Variance Analysis

Financial Reporting Variance Analysis
Actuals - Raw data from Accounting System Budget Tables - by Store, SKU and month Join Actuals and BudgetsPerform analysis in KNIME Output to varioustools at any stage Finance Variance Analysis ReportingThis workflow demonstrates a typical variance analysis report created by Finance teams. The process identifies corresponding Excel actions a user may take to perform the same task (in red where it is a manual process inExcel, in green where it can be somewhat automated using Excel formulas). Sample data represents raw Finance system accounting information, and budgets arranged in table formats. Following the cleanup andpreparation of the data, data is output into multiple formats for consumption by the audience (Excel, Tableau, PowerBI shown here, andcan also be written out to various databases too).This process can scale to many months of actuals, and millions of rows of input data will no additional manual work required.Changing to a database input is the simple replacement of the Excel Reader nodes. Budgets - Information provided in Table Format Output for BusinessIntelligence Tools(retain granularity) Finance Extract Actuals1 month, or 6! Replace input with database as neededbreak apart codingExcel: (manual) text-to-columnsrelabel splitsExcel: (manual) rename columnjoin multiple sku detailsExcel: multiple vlookups and (manual) column namingsku mappingtable(vlookup reference table)revenue and costby SKU and storeto match budget granularityExcel: pivot with(manual) refreshFinance BudgetSpreadsheettable structuresplit (and drop) certain rowsExcel: (manual)row filterrearrange to make suitable for analysis No Excel Equivalent!convert data typeto numberExcel: (manual) text to columnslabel appropriately postunpivotExcel - (manual) renamestore revenue %performanceExcel: formulaFull outer join ofactuals and budgetsNo Excel Equivalent ofFull Outer JoinExtract month from dateExcel: multiple formulas needed=TEXT(MONTH(B2),"mmmm")revenue performance by storeExcel: pivot with (manual) refreshSort ColumnsExcel: (manual) sortstore revenue performanceExcel: formularevenue performance by store and by SKUExcel: pivot with (manual) refreshExcel: No equivalent functionFil missing budgetswith 0Excel: (manual) data entryWrite out resultsVariance_Analysis_Report_OutputTableau Online or Tableau Server on-premExcel: No equivalent functionrevenue and costby SKU and storeto retain granularityExcel: pivot with(manual) refreshWrite actualsto BI toolKNIME_output_actualsWrite budgetto BI toolKNIME_output_budgetExcel Reader (XLS) Cell Splitter Column Rename Joiner (Labs) Excel Reader (XLS) Pivoting Excel Reader (XLS) Row Splitter Unpivoting String To Number Column Rename Math Formula Joiner (Labs) Extract Date&TimeFields GroupBy Sorter Math Formula GroupBy Send to Power BI Missing Value Excel SheetAppender (XLS) Send to TableauServer Pivoting Excel SheetAppender (XLS) Excel SheetAppender (XLS) Actuals - Raw data from Accounting System Budget Tables - by Store, SKU and month Join Actuals and BudgetsPerform analysis in KNIME Output to varioustools at any stage Finance Variance Analysis ReportingThis workflow demonstrates a typical variance analysis report created by Finance teams. The process identifies corresponding Excel actions a user may take to perform the same task (in red where it is a manual process inExcel, in green where it can be somewhat automated using Excel formulas). Sample data represents raw Finance system accounting information, and budgets arranged in table formats. Following the cleanup andpreparation of the data, data is output into multiple formats for consumption by the audience (Excel, Tableau, PowerBI shown here, andcan also be written out to various databases too).This process can scale to many months of actuals, and millions of rows of input data will no additional manual work required.Changing to a database input is the simple replacement of the Excel Reader nodes. Budgets - Information provided in Table Format Output for BusinessIntelligence Tools(retain granularity) Finance Extract Actuals1 month, or 6! Replace input with database as neededbreak apart codingExcel: (manual) text-to-columnsrelabel splitsExcel: (manual) rename columnjoin multiple sku detailsExcel: multiple vlookups and (manual) column namingsku mappingtable(vlookup reference table)revenue and costby SKU and storeto match budget granularityExcel: pivot with(manual) refreshFinance BudgetSpreadsheettable structuresplit (and drop) certain rowsExcel: (manual)row filterrearrange to make suitable for analysis No Excel Equivalent!convert data typeto numberExcel: (manual) text to columnslabel appropriately postunpivotExcel - (manual) renamestore revenue %performanceExcel: formulaFull outer join ofactuals and budgetsNo Excel Equivalent ofFull Outer JoinExtract month from dateExcel: multiple formulas needed=TEXT(MONTH(B2),"mmmm")revenue performance by storeExcel: pivot with (manual) refreshSort ColumnsExcel: (manual) sortstore revenue performanceExcel: formularevenue performance by store and by SKUExcel: pivot with (manual) refreshExcel: No equivalent functionFil missing budgetswith 0Excel: (manual) data entryWrite out resultsVariance_Analysis_Report_OutputTableau Online or Tableau Server on-premExcel: No equivalent functionrevenue and costby SKU and storeto retain granularityExcel: pivot with(manual) refreshWrite actualsto BI toolKNIME_output_actualsWrite budgetto BI toolKNIME_output_budgetExcel Reader (XLS) Cell Splitter Column Rename Joiner (Labs) Excel Reader (XLS) Pivoting Excel Reader (XLS) Row Splitter Unpivoting String To Number Column Rename Math Formula Joiner (Labs) Extract Date&TimeFields GroupBy Sorter Math Formula GroupBy Send to Power BI Missing Value Excel SheetAppender (XLS) Send to TableauServer Pivoting Excel SheetAppender (XLS) Excel SheetAppender (XLS)

Nodes

Extensions

Links