# Markov Analysis in Spreadsheets Tutorial

Learn Markov Analysis, their terminologies, examples, and perform it in Spreadsheets!
Dec 2019  · 9 min read

The real-life business systems are very dynamic in nature. It is not easy for market researchers to design such a probabilistic model that can capture everything. It means the researcher needs more sophisticate models to understand customer behavior as a business process evolves. A probability model for the business process which grows over the period of time is called the stochastic process. The stochastic process describes consumer behavior over a period of time. Markov chain is one of the techniques to perform a stochastic process that is based on the present state to predict the future state of the customer.

Markov analysis technique is named after Russian mathematician Andrei Andreyevich Markov, who introduced the study of stochastic processes, which are processes that involve the operation of chance (Source). This analysis helps to generate a new sequence of random but related events, which will look similar to the original. It is useful in analyzing dependent random events i.e., events that only depend on what happened last. Markov Analysis is a probabilistic technique that helps in the process of decision-making by providing a probabilistic description of various outcomes.

## What is Markov Analysis?

Markov model is a stochastic based model that used to model randomly changing systems. It assumes that future events will depend only on the present event, not on the past event. It results in probabilities of the future event for decision making. Assumption of Markov Model:

• The probability of moving from a state to all others sum to one.
• The probabilities apply to all system participants.
• The probabilities are constant over time, and
• The states are independent over time.

You have a set of states S= {S_1, S_2, S_3…….S_r }. The process starts at one of these processes and moves successively from one state to another. If the system is currently at Si, then it moves to state Sj at the next step with a probability by Pij, and this probability does not depend on which state the system was before the current state. ## Terminology

• Stochastic Processes: It deals with the collection of a random variable indexed by some set so that you can study the dynamics of the system.

• Independent Events: One of the best ways to understand this with the example of flipping a coin since every time you flip a coin, it has no memory of what happened last. The sequence of head and tail are not interrelated; hence, they are independent events.

• Dependents Events: Two events said to be dependent if the outcome first event affects the outcome of another event.

• Random Variables: A variable whose value depends on the outcome of a random experiment/phenomenon.

• Steady-State Probabilities: As you continue the Markov process, you find that the probability of the system being in a particular state after a large number of periods is independent of the beginning state of the system. The probabilities that you find after several transitions are known as steady-state probabilities.

Source: An Introduction to Management Science Quantitative Approaches to Decision Making By David R. Anderson, Dennis J. Sweeney, Thomas A. Williams, Jeffrey D. Camm, R. Kipp Martin

## Example of Markov Analysis

Let's analyze the market share and customer loyalty for Murphy's Foodliner and Ashley's Supermarket grocery store. Our primary focus is to check the sequence of shopping trips of a customer. You can assume that customers can make one shopping trip per week to either Murphy's Foodline or Ashley's Supermarket, but not both.

Using the terminologies of Markov processes, you refer to the weekly periods or shopping trips as the trials of the process. In each trial, the customer can shop at either Murphy’s Foodliner or Ashley’s Supermarket. The particular store chosen in a given week is known as the state of the system in that week because the customer has two options or states for shopping in each trial. With a finite number of states, you can identify the states as follows:

• State 1: The customer shops at Murphy’s Foodliner.

• State 2: The customer shops at Ashley’s Supermarket. The given transition probabilities are: Similarly, Hence, probability murphy’s after two weeks can be calculated by multiplying the current state probabilities matrix with the transition probabilities matrix to get the probabilities for the next state.

Generalize formula: Where P1, P2, …, Pr represents systems in the process state’s probabilities, and n shows the state.

Source: An Introduction to Management Science Quantitative Approaches to Decision Making By David R. Anderson, Dennis J. Sweeney, Thomas A. Williams, Jeffrey D. Camm, R. Kipp Martin

## Performing Markov Analysis in Spreadsheets

Let’s solve the same problem using Microsoft excel –

Step 1: Let’s say at the beginning some customers did shopping from Murphy’s and some from Ashley’s. This can be represented by the identity matrix because the customers who were at Murphy’s can be at Ashley’s at the same time and vice-versa. Step 2: Let’s also create a table for the transition probabilities matrix. Step 3: Now, you want the probabilities at both the store at first period: First, let’s design a table where you want values to be calculated: Step 4: Now, let’s calculate state probabilities for future periods beginning initially with a murphy’s customer. Probabilities can be calculated using excel function =MMULT(array1, array2). To use this first select both the cells in Murphy’s customer table following week 1. After applying this formula, close the formula bracket and press Control+Shift+Enter all together. Then you will see values of probability. Step 5: As you have calculated probabilities at state 1 and week 1 now similarly, let’s calculate for state 2. The only thing that will change that is current state probabilities. Week one’s probabilities will be considered to calculate future state probabilities. Now you can simply copy the formula from week cells at murphy’s and Ashley's and paste in cells till the period you want. If you had started with 1000 Murphy customers—that is, 1000 customers who last shopped at Murphy’s—our analysis indicates that during the fifth weekly shopping period, 723 would-be customers of Murphy’s, and 277 would-be customers of Ashley’s. Moreover, during the 10th weekly shopping period, 676 would-be customers of Murphy’s, and 324 would-be customers of Ashley’s.

Source: An Introduction to Management Science Quantitative Approaches to Decision Making By David R. Anderson, Dennis J. Sweeney, Thomas A. Williams, Jeffrey D. Camm, R. Kipp Martin

Step 6: Similarly, now let’s calculate state probabilities for future periods beginning initially with a murphy’s customer.

For week 1: For week 2: Just drag the formula from week 2 to till the period you want. In the fifth shopping period, the probability that the customer will be shopping at Murphy’s is 0.555, and the probability that the customer will be shopping at Ashley’s is 0.445. In the tenth period, the probability that a customer will be shopping at Murphy’s is 0.648, and the probability that a customer will be shopping at Ashley’s is 0.352.

Source: An Introduction to Management Science Quantitative Approaches to Decision Making By David R. Anderson, Dennis J. Sweeney, Thomas A. Williams, Jeffrey D. Camm, R. Kipp Martin

You can also look graphically how the share is going down at murphy’s and increasing at Ashley’s of customer who last shopped at Murphy’s.  ## Pros and Cons

Pros:

• It has advantages of speed and accuracy because of its analytical nature.
• It is also faster and more accurate compared to Monte-Carlo Simulation.
• Markov model is relatively easy to derive from successional data.
• The transition matrix summarizes all the essential parameters of dynamic change.
• It gives a deep insight into changes in the system over time.

Cons:

• Markov property assumptions may be invalid for the system being modeled; that's why it requires careful design of the model.
• The customer can enter and leave the market at any time, and therefore the market is never stable.
• Markov analysis can't predict future outcomes in a situation where information earlier outcome was missing.

## Conclusion

Congratulations, you have made it to the end of this tutorial!

In this tutorial, you have covered a lot of details about Markov Analysis. You have learned what Markov Analysis is, terminologies used in Markov Analysis, examples of Markov Analysis, and solving Markov Analysis examples in Spreadsheets. Also, discussed its pros and cons.

Hopefully, you can now utilize the Markov Analysis concepts in marketing analytics. Thanks for reading this tutorial!

BeginnerSkill Level
3 hr
7K
Learn to use spreadsheets to clean, analyze, and draw insights from data. Discover how to sort, filter, and use VLOOKUP to combine data.
See Details
Start Course

BeginnerSkill Level
4 hr
45.1K
Expand your spreadsheets vocabulary by diving deeper into data types, including numeric data, logical data, and missing data.

BeginnerSkill Level
4 hr
59.4K
Explore the world of Pivot Tables within Google Sheets, and learn how to quickly organize thousands of data points with just a few clicks of the mouse.
See More
Related  ### Do Spreadsheets Need a Rethink? With Hjalmar Gislason, CEO of GRID

Richie and Hjalmar Gislason explore the integral role of spreadsheets in today's data-driven world, the limitations of traditional Business Intelligence tools, and the transformative potential of generative AI in the realm of spreadsheets.

Richie Cotton

54 min

### Excel Formulas Cheat Sheet

Learn the basics of Excel with our quick and easy cheat sheet. Have the basics of formulas, operators, math functions and more at your fingertips.

### Data Manipulation in Excel Cheat Sheet

Discover how to manipulate data in Excel using this convenient cheat sheet!

### The 15 Basic Excel Formulas Everyone Needs to Know

Learn how to add arithmetic, string, time series, and complex formulas in Microsoft Excel.

### How to Create a Dashboard in Excel in 3 Easy Steps

Learn everything you need to know about how to create a dashboard in Excel, with tips and examples.

### How to Analyze Data in Google Sheets With Python: A Step-By-Step Guide

Boost your data analysis skills with our step-by-step guide on how to analyze, manipulate and write back data in Google Sheets using Python.
See MoreSee More