Icon

Quarterly_​Reporting_​Analysis

Loop Over Grouping of Industry Group and Region

Quarterly Reporting Analysis

Given a folder of excel files, we have two options.
1. Let the Excel Reader handle it.
2. Loop over the files individually (if there are differences between the table specs)

Gather Sheets From All Quarters

Clean Up and Remove Missing Values

Prepare Additional Columns

Compute QoQ Growth

Growth Per Quarter (Over All Groups and Regions)

Create 'Index' for mapping Quarter (As integer instead of date, so that the linear regression will work as intended).

Linear Forecasting of Next Quarter Revenue

In this case, we train the model to predict a single value based on the input time value.

The input will be a simple number describing the time numerically.

We wish to teach it a simple mapping. So, we would like it to be able to map the input value of the so-called 'time-index' to the predicted revenue for any time period.

As a thought experiment, imagine that our revenue increased by the same amount every quarter. For each quarter (time-period/time-step), we could just add some multiple of the timestep value.

Just like the most basic case of y=mx+b where the gradient (m) is how much we increase every period, and x is the time-step number starting at zero.

It's a bit like this with linear regression, so we could input any time-index value like 10, 20 or even 30 which would ideally map forward that many time periods, however it would be inaccurate for such scales.

List Files/Folders
Table Row to Variable Loop Start
Excel Reader
Loop End
Clean up columnsfor readability
Column Filter
Fill certain numerical columnswith zeros where appropriate(E.g. revenue)
Missing Value
Column Filter
Group byIndustry Groupand Region
Group Loop Start
Column Renamer
Loop End (Column Append)
Calculate QoQgrowth %
Math Formula
View Predictions for each(Time step '5' is the nextquarter)
Line Plot
Loop End
Group byIndustry Groupand Region
Group Loop Start
Ensure the quartersare ordered
Sorter
Counter Generation
"Push" the revenuedown by 1 quarter, so wecan compare each revenue'srow with the previous quarter
Lag Column
Average growth over allregions and industry groups
Pivot
GroupBy
Remove 'overflow'column from Lag Columnnode
Row Filter
GroupBy
Get integerindex foreach date
Value Lookup
Column Renamer
Counter Generation
Remove rows withnull quarter or yearsince it can't be used
Row Filter
Get Avg RevenuePer Quarter
GroupBy
Recalculate somecolumns which arenull. Also, ensureno divisors are zero.
Expression
Next Quarter Dateand Index (integer)NOTE: The date andindex are MANUALLYset, not programatically.
Table Creator (deprecated)
CalculateEBIT_Margin
Math Formula
Linear Regression Learner
Regression Predictor
Join the Quarter startdate with the year
String Manipulation
Column Renamer
Convert to dateso we can sortthe data by date
String to Date&Time (deprecated)
String to Date&Time (deprecated)
Split the column'Quarter' on a "-"and take only thelast element
Expression
Join average revenueper quarter (real data)with the prediction
Concatenate

Nodes

Extensions

Links