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

Welcome to Part Two of our exploration into Poisson distribution with Excel, where we’ll apply statistical methods to understand pothole occurrences on roads. Take a deep breath and prepare for a smooth ride through this detailed Excel tutorial!

Starting Point

If you don’t have access to our workbook from the previous presentation, don’t worry. You can either start from a blank worksheet or review the earlier presentation to get up to speed. For those with the workbook, you’ll find three tabs at the bottom:

1. Example: Pre-formatted cells for practice problems.
2. Blank: A blank worksheet where we’ll continue from.
3. Answer Key: For checking your work.

Recap of Previous Work

In our prior session, we generated data using a random number generator to simulate potholes on a road over 100-mile intervals. This data followed a Poisson distribution with a mean of 20. Here’s a quick recap:

• Data Generation: We generated random data using the Poisson distribution function with a mean of 20.
• Data Analysis: We grouped the data to find out how often we observed different numbers of potholes in 500 tests.
• Visualization: We created graphs to visualize this data and compared it to a theoretical Poisson distribution.

Creating a Poisson Distribution Curve

Now, let’s delve into creating a more accurate Poisson distribution curve.

1. Setup Your Worksheet:
   • In a new column, list the number of potholes (x values) ranging from 0 to 100.
   • In another column, use the POISSON.DIST function to calculate the probability for each x value. For example, in cell B2, enter:
     excel

     Copy code

     =POISSON.DIST(A2, $D$1, FALSE)


     where A2 is the x value (number of potholes), $D$1 is the mean (20), and FALSE specifies that we want the probability mass function.

2. Format the Results:
   • Highlight the column with the Poisson probabilities and format them as percentages with appropriate decimal places.
3. Create the Graph:
   • Select your data and insert a chart from the “Insert” tab. Choose a suitable chart type (e.g., scatter plot with lines) to visualize the Poisson distribution curve.

Comparing Data

1. Add Actual Data to the Graph:
   • Compare your theoretical Poisson distribution curve with the actual data you collected. Add a new data series to your chart representing the actual pothole counts.
2. Refine Your Chart:
   • Ensure the chart labels and legends are clear. This helps in making comparisons easier.

Advanced Analysis

1. Calculate Cumulative Probabilities:
   • If you want to find the probability of having up to a certain number of potholes, use the cumulative option in the POISSON.DIST function. For example:
     excel

     Copy code

     =POISSON.DIST(5, $D$1, TRUE)


     This gives the probability of having 5 or fewer potholes.

2. Handle Ranges of Values:
   • To calculate probabilities within a specific range (e.g., 7 to 14 potholes), use:
     excel

     Copy code

     =POISSON.DIST(14, $D$1, TRUE) - POISSON.DIST(6, $D$1, TRUE)


Formatting and Final Touches

• Visual Appeal: Format your chart with colors and borders to make it visually appealing. Choose a color scheme that you find pleasant and easy to interpret.
• Final Checks: Review all calculations and ensure they match your expectations. Use Excel’s spell check feature to correct any typos.

Conclusion

In this guide, we explored the Poisson distribution in Excel to analyze pothole occurrences. By comparing theoretical and actual data, and visualizing it through charts, we gained insights into pothole distribution patterns.

If you have any questions or need further assistance with your analysis, feel free to ask. Happy analyzing!