DEV Community

Cover image for Ms Excel and Predictive Data analysis
Loi2008
Loi2008

Posted on

Ms Excel and Predictive Data analysis

Overview

Microsoft Excel is a keystone of business analytics and is valued for its familiarity, flexibility, and widespread use across industries. As businesses demand ever-more sophisticated insights, Excel’s limitations become increasingly pronounced. Below, is an exploration of its strengths and limitations in predictive analysis, and its critical role in enabling data-driven decisions. Despite the limitations, businesses can still leverage its capabilities to gain insights and make informed decisions. This is possible by following best practices, understanding its limitations, and maximizing the benefits of using Excel for predictive analytics.

Strengths and Limitations of Excel in Predictive Analysis

Ms Excel Strengths in Predictive Analysis

- Accessibility and Ubiquity

Excel is an accessible tool for predictive analysis since it is readily available and well-known to many people. Because of its established reputation in the business sector, many professionals choose it by default. Its widespread use and user-friendly interface allow for rapid analysis with little training.

- Built-In Analytical Tools

For trend-based predictions, Excel offers a number of forecasting tools, including single and double exponential smoothening and linear forecasting. Furthermore, its ability to create bespoke models makes it possible to perform competent financial modeling, including scenario simulations, projections, and discount cash flow assessments. - Built-in Functions: Excel's built-in functions, like forecasting and regression analysis, offer a strong basis for predictive analytics.

- Visualization and Layout Capabilities

Excel gives customers the ability to visualize data patterns and derive actionable insights with its built-in charts, pivot tables, conditional formatting, and formatting flexibility.

- Data Integration & Flexibility

Excel's versatility makes it useful for combining data and enabling rapid, exploratory research. It can import data from a various sources: - CSV files, databases, and APIs.

- Data Manipulation

Users can prepare data for predictive models thanks to Excel's flexibility in data manipulation and presentation.

- Add-ins

Excel's predictive skills are improved with add-ins like Analysis ToolPak, PI DataLink and Solver.

Limitations of Ms Excel in Predictive Analysis

- Scalability & Performance Constraints

Excel may become unresponsive for large-scale predictive modeling as dataset sizes increase. It may become sluggish, crash, or unable to handle the volume.

- Limited Forecasting Sophistication

Excel's Forecast function assumes linear or exponential trends. Inventory Planner frequently fails in situations involving non-linear dynamics, complex seasonal patterns, or environments with outliers and volatility.

- Error-Prone Manual Handling

Errors that can jeopardize analytical integrity are frequently caused by manual entry, formula formulation, and inadequate data management.

- Version Control and Siloed Collaboration Issues

Spreadsheets frequently spread among departments, resulting in several, erratic versions. This makes it more difficult to maintain a single source of truth and hinders collaboration.

Role of Excel in Data-Driven Business Decisions: Analyzing Sample Jumia Dataset

Overview

The Jumia dataset contains sample Jumia data on products, pricing of each product, discounts offered on each product, products' rating and reviews. The data was cleaned and analyzed using Ms Excel.A dashboard revealing various perspectives on the data provided enables effective decision making on the products performance (Fig1)

Jumia Products Analysis Dashboard
Fig1: Jumia Products Analysis Dashboard

Findings

A total of 112 products sold on Jumia were analysed.All the products were discounted. Fig2 illustrates the top 10 discounted products

Top 10 discounted products
Fig2: Top 10 Discounted Products
The products had good reviews, with the top 10 products having reviews ranging between 20 and 70 as illustrated in Fig3.

Top 10 products with the highest reviews
Fig3: Top 10 Reviewed Products
The finding also revealed a positive relationship between the product ratings and the number of reviews (Fig4).

Relationship between the products review and the rating
Fig.4: Relationship between Product Review and the Rating
More than 50% of the products had high discount and 43% of the products had excellent rating (Fig5 & Fig6).

Product Discount category
Fig5: Product Discount Category
Product Rating Category
Fig6: Product Rating Category

Conclusion

Overall, the analysis provides a comprehensive view of Jumia’s sales ecosystem and guides strategic decisions to optimize revenue, customer engagement, and operational efficiency.

Reference

  1. Jumia Sample DataSet
  2. What are the pros and cons of using Excel for data analysis?

Top comments (0)