Accounting Instruction, Help, & How To (Financial & Managerial)

Before we dive in, take a deep breath and hold it for 10 seconds. As you exhale smoothly and soothingly, get ready to explore the world of statistics and Excel. Whether or not you have the workbook, we’ll build this from scratch, starting from a blank worksheet. If you do have the workbook, you’ll find three tabs: “Example,” “Practice,” and “Blank.”

 

• Example Tab: A completed version for reference.
• Practice Tab: Preformatted cells for solving practice problems.
• Blank Tab: A fresh worksheet to format as we solve problems.

Overview of Poisson Distribution

We’ve previously discussed various types of curves and distributions, including the uniform distribution. This time, we’ll delve into the Poisson distribution, which is a bit more complex but immensely useful in specific scenarios.

Conditions for Using Poisson Distribution

First, let’s list the conditions under which a Poisson distribution is applicable. You can type these conditions manually or copy and paste them:

1. An event can occur any number of times during a time period.
2. Events occur independently.
3. The rate of occurrence is constant over time.
4. The probability of an event is proportional to the length of the time period.

If these conditions are met, the Poisson distribution can provide predictive power for certain data sets.

Constructing the Poisson Formula in Excel

Let’s create the formula step-by-step. We’ll use Excel’s tools to build and represent the formula.

Poisson Distribution Formula

P(X)=λX⋅e−λX!P(X) = \frac{\lambda^X \cdot e^{-\lambda}}{X!}P(X)=X!λX⋅e−λ​

Where:

• $\lambda$ (lambda) is the mean number of occurrences.
• $e$ is Euler’s number (approximately 2.71828).
• $X!$ (factorial) is the product of all positive integers up to $X$.

Steps in Excel:

1. Insert the Formula:
   • Go to Insert > Equation and type: P(X)=λX⋅e−λX!P(X) = \frac{\lambda^X \cdot e^{-\lambda}}{X!}P(X)=X!λX⋅e−λ​.
2. Formatting:
   • Increase the font size (e.g., to 24) for better visibility.
   • Change the background color of the formula cell to hide gridlines (e.g., orange).
3. Using Excel Functions:
   • Euler’s Number (e):
     • Type =EXP(1) in a cell to get the value of $e$.
   • Factorial:
     • Use =FACT(number) to compute factorials.

Understanding the Components

Mean ($\lambda$ or $\mu$)

• Often represented by $\lambda$ in Poisson distributions, but can also be denoted by $\mu$.

Variance (σ2\sigma^2σ2)

• For Poisson distributions, the mean and variance are equal: λ=σ2\lambda = \sigma^2λ=σ2

Example in Excel

1. Calculate $e$:
   • In a cell, type =EXP(1) to display Euler’s number.
2. Calculate Factorial:
   • For example, for 5!, type =FACT(5) to get 120.
3. Insert Greek Symbols:
   • Go to Insert > Symbol, choose the Greek and Coptic subset, and find $\lambda$, $\mu$, and $\sigma$.

Practice Problems

1. Practice with Poisson Function:
   • Use =POISSON.DIST(x, mean, cumulative) to calculate Poisson probabilities directly in Excel.
   • x: number of events,
   • mean: average number of events (λ),
   • cumulative: TRUE for cumulative distribution, FALSE for probability mass function.
2. Create a Poisson Table and Graph:
   • Generate data based on Poisson distribution and visualize it with graphs in Excel.

Formatting and Final Touches

• Highlight key areas and apply bold or color formatting for clarity.
• Perform a spell check to ensure accuracy.

By the end of this exercise, you should be comfortable working with the Poisson distribution in Excel, utilizing its functions, and understanding the theoretical background. Stay tuned for future presentations where we’ll build tables and graphs based on this powerful distribution!