Throughout university, I came across various concepts, but didn’t fully understand how I could implement them into real-world situations. Over those years in my Economics and Commerce degrees, it always felt like I was chasing the understanding as it had been a few years since leaving high school and I hadn’t thought about particular concepts, or how to even approach using them. I always remember feeling comfortable with easy derivatives but never knew how to use them.

Throughout the Economics degree, the main focus seemed to be finding the demand curve (schedule), taking the derivative, and then discussing the margin (inflection) point. Things would obviously get more complicated as you went on, but the crux was this margin point. Given how much this concept and approach was drilled into students throughout each of the courses (even within “non-economics” subjects like Business Management), I attempted to see the margin everywhere. Early on in the piece, I took to heart the fact that many “economists” couldn’t properly explain basic concepts like opportunity cost.

I decided to approach this post about taking a concept that I’ve struggled with for years and implementing it in a manner that makes sense (to me). I think I’ve also wanted to create a process (algorithm even) that I can utilise at a moment’s notice. This has been a typical cost-saving venture for me over the past year as I have been able to refer back to many of these posts and use (or adapt) the solutions that I have created for myself.

https://docs.google.com/spreadsheets/d/13hEFc6Wkf7XP56KC_XYGpVRZcjRCKEw0Iv2gleYih78/edit#gid=0

Create Sample Data

Let’s dive into using Eigenvalues and Eigenvectors in Sales data, but let’s understand why we’re doing this. Have a look at this data below and think about what it’s telling us:

What we can see is the sales revenue for a company’s four products (A, B, C, and D). I like to visualise things, so let’s look at what this looks like on a graph:

Linear Estimation

I want to dive a little deeper into this analysis, so I’m going to create another sheet (ProdCurveAnalysis) and move there. If we gather their respective functions we get:

=LINEST(<Product Sales>,ARRAYFORMULA(SEQUENCE(4)),1,1)

• Product A: 4x + 105
• Product B: -2x + 160
• Product C: 5.2x + 118.5
• Product D: 4x + 122.5

As we would expect, the R² for each of these functions are quite low (the highest being 68.63% and the lowest being 4%). This means that the linear estimations are not the strongest representation of the product sales.

Second-Degree Polynomial Estimation

Let’s get more accurate and introduce a second-degree polynomial into the LINEST function for each of these products:

=ARRAYFORMULA(LINEST(<Product Sales>,SEQUENCE(4)^{1,2},1,1))

• Product A: -10x² + 54x + 55
• Product B: -10x² + 48x + 110
• Product C: -3.5x² + 22.7x + 101
• Product D: -7.5x² + 41.5x + 85

These R² variables are looking a bit stronger, meaning that we’re getting a closer representation of the product sales relationship (the highest being 96% and the lowest being 84%).

Third-Degree Polynomial Estimation

Let’s go one more level and check out the third-degree polynomial:

=ARRAYFORMULA(LINEST(<Product Sales>,SEQUENCE(4)^{1,2,3},1,1))

• Product A: -3.33x³ + 15x² – 1.67 + 90
• Product B: -6.67x³ + 40x² – 63.33 + 180
• Product C: -2.67x³ + 16.5x² – 21.833 + 129
• Product D: -3.33x³ + 17.5x² – 14.167 + 120

These R² variables are looking the strongest of the three, especially as we now have a 100% R² value for each of the products:

Covariance Matrix

Now that we can understand how these curves look for each product, we can actually perform the analysis through the lens of Eigenvalues and Eigenvectors. For this analysis, the Eigenvalues are going to tell us how much each product contributes to our profit. The Eigenvectors are going to tell us which products are most influential for the overall patterns; which ones have the strong correlation to consumer purchasing behaviour. I’ll repeat this at the end so that we can refresh ourselves.

The first thing we need to do though is create a covariance matrix based on the Products sales:

Let’s take care of Product A first:

=COVARIANCE.P(B$2:B$5,$B$2:$B$5)

=COVARIANCE.P(B$2:B$5,$C$2:$C$5)

=COVARIANCE.P(B$2:B$5,$D$2:$D$5)

=COVARIANCE.P(B$2:B$5,$E$2:$E$5)

Now that we have the entire first column sorted, we can select all of those cells and drop the copying function across to the right (column J):

Create the Eigenvalue Decomposition Function

The Eigenvalues will require a bit more work, specifically by using the Google Apps Script portion of the Sheets:

Click Extensions > Apps Script:

This will create a new script for us to add code into:

This should look familiar from a previous post where we converted Excel VBA scripts to Google scripts. Now, we can title the project ‘Eigenvalues_vectors Sales Data’ by clicking on the ‘Untitled project’ text at the top:

Rename the script file from Code.gs to eigenValuesVectors.gs; where the eigenvalues and vectors will be calculated:

Now that we have the project set up, we can enter the following code which will calculate the Eigenvalues and Eigenvectors:

function calculateEigenvalues() {

  // Get the covariance matrix data

  var covarianceRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Sheet1’).getRange(‘G2:J5’);

  var covarianceData = covarianceRange.getValues();

  // Log the covariance matrix data

  Logger.log(“Covariance Matrix Data:”);

  Logger.log(covarianceData);

  // Convert the covariance matrix data to a 2D array

  var covarianceMatrix = [];

  for (var i = 0; i < covarianceData.length; i++) {

    covarianceMatrix.push(covarianceData[i]);

  }

  // Calculate eigenvalues

  var eigenvalues = eigenvalueDecomposition(covarianceMatrix);

  // Output eigenvalues to the spreadsheet

  var outputRange = covarianceRange.offset(0, covarianceData[0].length + 2, eigenvalues.length, 1);

  for (var i = 0; i < eigenvalues.length; i++) {

    outputRange.offset(i, 0).setValue(eigenvalues[i]);

  }

}

function eigenvalueDecomposition(matrix) {

  // Convert matrix to 2D array

  var array = [];

  for (var i = 0; i < matrix.length; i++) {

    array.push(matrix[i]);

  }

  // Use Numeric.js for eigenvalue decomposition

  var eigResult = numeric.eig(array);

  var eigenvalues = eigResult.lambda.x;

  // Log the eigenvalues

  Logger.log(“Eigenvalues:”);

  Logger.log(eigenvalues);

  return eigenvalues;

}

// QR decomposition algorithm

function qrDecomposition(matrix) {

  var n = matrix.length;

  var Q = [];

  var R = matrix.slice();

  for (var i = 0; i < n – 1; i++) {

    var col = getColumn(R, i);

    var v = col.slice();

    for (var j = 0; j < i; j++) {

      var projection = vectorProjection(col, Q[j]);

      v = vectorSubtract(v, vectorScalarMultiply(Q[j], projection));

    }

    var u = vectorNormalize(v);

    Q.push(u);

    for (var j = i + 1; j < n; j++) {

      var colJ = getColumn(R, j);

      var projection = vectorProjection(colJ, u);

      R = vectorSubtract(R, vectorScalarMultiply(u, projection), j);

    }

  }

  return { Q: transpose(Q), R: R };

}

// Helper functions

function matrixMultiply(A, B) {

  var result = [];

  var m = A.length;

  var n = B[0].length;

  var p = B.length;

  for (var i = 0; i < m; i++) {

    result.push([]);

    for (var j = 0; j < n; j++) {

      var sum = 0;

      for (var k = 0; k < p; k++) {

        sum += A[i][k] * B[k][j];

      }

      result[i].push(sum);

    }

  }

  return result;

}

function getColumn(matrix, colIndex) {

  var column = [];

  for (var i = 0; i < matrix.length; i++) {

    column.push(matrix[i][colIndex]);

  }

  return column;

}

function transpose(matrix) {

  var result = [];

  var rows = matrix.length;

  var cols = matrix[0].length;

  for (var j = 0; j < cols; j++) {

    result.push([]);

    for (var i = 0; i < rows; i++) {

      result[j].push(matrix[i][j]);

    }

  }

  return result;

}

function vectorSubtract(v1, v2) {

  return v1.map(function (value, index) {

    return value – v2[index];

  });

}

function vectorScalarMultiply(vector, scalar) {

  return vector.map(function (value) {

    return value * scalar;

  });

}

function vectorMagnitude(vector) {

  var sumOfSquares = vector.reduce(function (acc, value) {

    return acc + value * value;

  }, 0);

  return Math.sqrt(sumOfSquares);

}

function vectorNormalize(vector) {

  var magnitude = vectorMagnitude(vector);

  return vectorScalarMultiply(vector, 1 / magnitude);

}

function vectorProjection(v1, v2) {

  return vectorDotProduct(v1, v2) / vectorDotProduct(v2, v2);

}

function vectorDotProduct(v1, v2) {

  var result = 0;

  for (var i = 0; i < v1.length; i++) {

    result += v1[i] * v2[i];

  }

  return result;

}

function computeEigenvectors(matrix) {

  var n = matrix.length;

  var maxIterations = 1000;

  var tolerance = 1e-12;

  // Initialize a random vector

  var v = [];

  for (var i = 0; i < n; i++) {

    v.push(Math.random());

  }

  // Power iteration method to find eigenvectors

  for (var iter = 0; iter < maxIterations; iter++) {

    var nextV = matrixVectorMultiply(matrix, v);

    v = normalize(nextV);

    if (converged(v, nextV, tolerance)) {

      break;

    }

  }

  return [v];

}

function matrixVectorMultiply(matrix, vector) {

  var result = [];

  for (var i = 0; i < matrix.length; i++) {

    var row = matrix[i];

    var sum = 0;

    for (var j = 0; j < row.length; j++) {

      sum += row[j] * vector[j];

    }

    result.push(sum);

  }

  return result;

}

function normalize(vector) {

  var magnitude = Math.sqrt(vector.reduce(function(sum, x) { return sum + x*x; }, 0));

  return vector.map(function(x) { return x / magnitude; });

}

function converged(v1, v2, tolerance) {

  var squaredDistance = v1.reduce(function(sum, x, i) { return sum + Math.pow(x – v2[i], 2); }, 0);

  return Math.sqrt(squaredDistance) < tolerance;

}

You need to make sure that you select two important pieces of information in the 3rd line of the code:

• Sheet name, and
• The Range.

In my Google Sheets file, I am using the sheet ‘Sheet1’ and the covariance matrix is located in G2:J5:

In order to get this code to work, I needed to implement the Numeric.js library, but I was unable to work out how to connect the necessary libraries for this computation. I was able to find this resource and it didn’t appear to have the necessary id for this library. I ended up copying the source code from this github user.

Now that we have the source code (from the github user above), we are going to implement it into this project. First, we’re going to create another script file by pressing the ‘+’ button then select Script from the drop-down menu. I copied and pasted the source code (from the link above) into this page:

Now that we have both the eigenValuesVectors.gs and numeric.gs scripts sorted, we need to set permissions for the eigenValuesVectors.gs script to interact with our sheets, so select that script and hit the Debug function to check that this script is correct and it will allow us to engage it with the sheet:

Next, click Review permissions:

Sign in to your Google Account:

Click Advanced:

Then click, Go to Eigenvalues_vectors Sales Data:

Finally, click Allow:

Calculating the Eigenvalues

Now that we have set up all of this code, we need to use it. The function that we are going to be using within the Google Sheet document is ‘eigenvalueDecomposition()’:

We already have a table prepared for our Eigenvalues in L2:L5. I can enter =eigenvalueDecomposition(G2:J5) into L2 and the array will fill out those 4 rows:

Eigenvectors

In the code that we have already entered, we have coded the Eigenvector function, computeEigenvectors(matrix); line 147. So we can enter the function =computeEigenvectors() and enter the covariance matrix (G2:J5):

Now that we have calculated these Eigenvalues and Eigenvectors, how can we actually use them? The Eigenvalues represent the variance of the data along the corresponding eigenvector direction. The Eigenvalues are going to tell us how much each product contributes to our profit. The Eigenvectors are going to tell us which products are most influential for the overall patterns; which ones have the strong correlation to consumer purchasing behaviour. Higher eigenvalues indicate more variance in the data along that particular direction. In this case, the largest eigenvalue, λ₁ = 324.32, suggests that there is a significant amount of variance in the data along the corresponding eigenvector, v₁. Eigenvectors, on the other hand, represent the direction in which the data varies the most. The eigenvector corresponding to the largest eigenvalue, v₁, indicates the primary direction of variance in the dataset. Now that we have calculated these Eigenvalues and Eigenvectors, how can we actually use them? The Eigenvalues represent the variance of the data along the corresponding eigenvector direction. The Eigenvalues are going to tell us how much each product contributes to our profit. The Eigenvectors are going to tell us which products are most influential for the overall patterns; which ones have the strong correlation to consumer purchasing behaviour. Higher eigenvalues indicate more variance in the data along that particular direction. In this case, the largest eigenvalue, λ₁ = 324.32, suggests that there is a significant amount of variance in the data along the corresponding eigenvector, v₁. Eigenvectors, on the other hand, represent the direction in which the data varies the most. The eigenvector corresponding to the largest eigenvalue, v₁, indicates the primary direction of variance in the dataset.

Suggestions

Based on the understanding of this data, here are specific actions that we can take for the products. These suggestions are based on the observed patterns in the data and aim to leverage them to optimise business performance and drive sustainable growth:

Product A

1. Conduct a detailed analysis of market demand, consumer preferences, and competitive landscape to identify factors driving fluctuations in sales. We observe that Product A has the highest eigenvalue (λ₁ = 324.32), indicating it contributes the most to the overall variance. This suggests that understanding market demand for Product A is crucial for overall business performance.

2. Invest in marketing campaigns or product enhancements to capitalise on growth opportunities during quarters when sales are higher. Peaks in Product A’s sales (e.g., Q3: 130 units) suggest there is significant demand during certain quarters. Investing in marketing campaigns or product enhancements during these periods can help capitalise on the growth opportunities and maximise sales revenue.

3. Implement inventory management strategies to ensure adequate stock levels during peak demand periods and minimise excess inventory during slower quarters. Variability in Product A’s sales (e.g., Q1: 100 units, Q2: 120 units) indicates the need for effective inventory management. Maintaining optimal stock levels during peak demand periods and minimising excess inventory during slower quarters can help reduce holding costs and improve overall profitability.

Product B

1. Explore opportunities for product diversification or line extensions to cater to evolving customer needs and preferences. While Product B exhibits consistent sales growth (e.g., Q1: 150 units, Q2: 160 units), exploring opportunities for diversification or line extensions can help sustain this growth trajectory and cater to evolving customer preferences.

2. Optimise production processes to improve efficiency and reduce costs, thereby enhancing profitability. Consistent demand for Product B (e.g., Q3: 170 units) indicates the importance of optimising production processes to meet customer requirements efficiently. Improving production efficiency can help reduce costs and enhance profitability.

3. Monitor competitor activities and consumer trends to identify potential threats or opportunities in the market. Understanding competitive dynamics and consumer trends is essential for maintaining Product B’s market position. Monitoring competitor activities and consumer preferences can provide valuable insights for strategic decision-making and product development.

Product C

1. Analyse customer feedback and sales data to identify areas for product improvement or feature enhancements. Fluctuations in Product C’s sales (e.g., Q1: 121 units, Q3: 140 units) suggest the need to analyse customer feedback and sales data to identify areas for product improvement or feature enhancements. Addressing customer needs and preferences can help stimulate sales and enhance customer satisfaction.

2. Develop targeted marketing campaigns or promotional strategies to stimulate sales during periods of slower demand. Implementing targeted marketing campaigns or promotional strategies during periods of slower demand for Product C (e.g., Q1: 121 units) can help stimulate sales and increase market penetration.

3. Explore partnerships or collaborations with complementary products or brands to expand market reach and drive sales growth.

Product D

1. Evaluate pricing strategies to ensure competitiveness while maintaining profitability. Analysing sales data for Product D (e.g., Q2: 135 units, Q3: 145 units) can provide insights into pricing dynamics and market competitiveness. Evaluating pricing strategies can help ensure Product D remains competitive while maintaining profitability.

2. Implement customer retention initiatives such as loyalty programs or personalised marketing efforts to increase repeat purchases. Developing customer retention initiatives such as loyalty programs or personalised marketing efforts can help increase repeat purchases and foster long-term customer relationships for Product D.

3. Monitor supply chain operations to mitigate risks of disruptions and ensure timely delivery to meet customer demand.

The eigenvectors represent the directions of maximum variability in the data. Let’s interpret the values of the eigenvectors for the dataset:

v₁

The components of this eigenvector represent the relative importance of each variable (Product A, Product B, Product C, Product D) in determining the direction of maximum variability in the data. The first component (0.6148604234) suggests that Product A has a relatively higher weight or importance in this direction compared to the other products. Similarly, the other components indicate the importance of each product in determining the direction of maximum variability.

v₂,v₃,v₄

These eigenvectors represent orthogonal directions of decreasing variability in the data. The values of the components in each eigenvector indicate the relative importance of each variable in determining the variability along their respective directions. The smaller eigenvalues corresponding to these eigenvectors indicate less variability along these directions compared to the direction represented by v₁.

The values of the components in each eigenvector provide insight into the relative importance of each variable (Product A, Product B, Product C, Product D) in determining the variability in the data along different directions. By analysing the eigenvectors, we can identify which variables contribute the most to the direction of maximum variability in the dataset. The eigenvectors can be used to understand the underlying structure of the data and to identify patterns or trends that may be present in the dataset. Together with the eigenvalues, the eigenvectors provide a comprehensive understanding of the variability and structure of your dataset, which can inform decision-making and strategic planning.

Additions

What’s great about this sheet and code is that you can adjust it for different period lengths for those products. Let’s assume that we want to track these products over 3 years, we can adjust that in the sheet (A1:E13) and the covariance matrix:

And the Eigenvalues and Eigenvectors will update automatically:

Conclusion

In summary, leveraging eigenvalues and eigenvectors provides valuable insights for economic managerial perspectives. By identifying key product trends, managing portfolios, assessing risk, allocating resources effectively, and enhancing decision-making processes, managers can optimise their strategies and enhance overall performance in a dynamic market environment.