Using linear and seasonal regression for Revenue Forecasting

Revenue forecasting example (using linear and seasonal regression)

To aid in setting goals for next year, you would like to establish a forecast of your company's revenue based on existing trends.

You will use the revenue values already available in your MicroStrategy project. Predictions of future revenue will be determined at quarterly intervals. The quarters for the future dates are already defined in your MicroStrategy project, but revenue figures for each are not yet available.

Your model will require a continuous input of quarters to recognize a regression pattern. A Quarter attribute is commonly formatted to represent a year followed by a quarter, and therefore cannot be used as a continuous index into the quarters. To address this requirement, create a Quarter Index metric using the Quarter attribute. Since a year followed by a quarter is still a sequential list of values, you can often create a Quarter Index metric with the following simple expression:

Rank(Max(Quarter@ID) {Quarter})

This uses the ID values for a Quarter attribute, and creates a sequential list of values by ranking them. For example, assume you are using values dating back to the beginning of 2012. Quarter 201202 (Q2 in year 2012) has an index of 2 since it is the second quarter overall. Quarter 201403 (Q3 in year 2014) has an index of 11.

If the ID values for your attribute representing quarter data are not sequential, you must determine a way to convert the ID values into a sequential list of values.

The example Tutorial project includes a Quarter Index metric, along with other reports and metrics created for this forecasting example. Some of the definitions of metrics and reports within the Tutorial project are different than the simplified descriptions in this example. You can use the definitions provided in the steps here, or the definitions in the Tutorial project, depending on what works best for your reporting environment.

To begin your data mining analysis, use the Training Metric Wizard to design a training metric, following the steps described below.

To create a training metric for linear regression analysis

1

In MicroStrategy Developer, select Training Metric Wizard from the Tools menu. The Training Metric Wizard opens on the Introduction page.

2

Click Next. The Select Type of Analysis page opens.

3

Select Linear regression as the type of analysis.

4

Click Next. The Select Metrics page opens.

5

Add Revenue as the Dependent Metric.

6

Add the Quarter Index metric to the list of Independent Metrics.

7

Clear the Show advanced options check box to use the default settings for variable reduction and other variable settings.

8

Click Next. The Select Output page opens.

9

Select the Automatically create on report execution check box.

10

Within the Predictive metrics to generate area, select Predicted Value.

11

Click Finish to save and create the metric.

For more information on the Training Metric Wizard, see Creating a predictive model using MicroStrategy or refer to the MicroStrategy online help.

Next, create a report that includes the new training metric and the Quarter attribute. Include the Revenue metric to compare the values calculated by the training metric with the original values. Review the Report Data Options and VLDB properties for your report to ensure that outer join results are displayed for the metrics of your report. Execute the report.

The training metric generates a straight line that best fits the Revenue data. The report, converted into a dashboard to display this trend, is shown below.

A predictive metric is created in the folder you specified in the Training Metric Wizard. The default location is the My Objects folder.

The predictive metric accurately depicts a linear line through the Revenue data, but for this example, assume that you are not satisfied with the predictions. Your data is seasonal and you need to forecast the fluctuations that will occur throughout the year.

Seasonality is recognized by adding another independent metric to the training metric. This additional metric specifies the quarter within the year associated with each Quarter Index value. For example, the Quarter Index values of 1, 5, and 9 are all from the first quarter. The Quarter of Year metric uses the same basic formula as Quarter Index. The BreakBy parameter is defined as year so that the ranking is restarted for each year, allowing each quarter to be numbered 1 through 4 for a given year. The formula is shown below:

Rank<BreakBy={Year}>(Max(Quarter@ID) {Quarter} )

To include seasonality in your data mining model, complete the following steps.

To add seasonality to the data mining model

1

In MicroStrategy Developer, double-click the training metric you created in To create a training metric for linear regression analysis to open the Training Metric Wizard to the Introduction page.

2

Click Next. The Select Type of Analysis page opens.

3

Do not change any of the values on this page. Click Next. The Select Metrics page opens.

4

Add the Quarter of Year metric to the list of Independent Metrics, which already includes the Quarter Index metric.

5

Click Next. The Select Output page opens.

6

Rename the predictive metric so that the existing linear predictive metric is not overwritten.

7

Save the training metric with a new name to distinguish it as a seasonal prediction.

For more information on the Training Metric Wizard, see Creating a predictive model using MicroStrategy or refer to the MicroStrategy online help.

You can now re-execute the report you created earlier that included the training metric, Quarter, and Revenue. The results of the training metric now recognize the fluctuations in revenue throughout each year and predict values accordingly. Notice that the data accounts for seasonality and is no longer a straight line, as shown in the report below.

A predictive metric is created in the folder you specified in the Training Metric Wizard. The default location is the My Objects folder.