Excel Case: Poke Mart Shop Cart

In this Excel case study, we’ll simulate managing a shopping cart for a fictional store called Poke Mart, a popular shop selling Pokémon merchandise. This example will demonstrate practical Excel techniques such as formulas, conditional formatting, data validation, and charts to track and analyze shopping cart details.

Let’s dive into this hands-on Excel exercise brought to you by The Coding College!

Problem Statement

Imagine a customer shopping for Pokémon-themed items. They add multiple products to their cart, and you, as the manager, want to:

1. Maintain an itemized record of purchases.
2. Calculate totals, discounts, and taxes.
3. Display a summary of the shopping cart visually.

Steps to Build the Poke Mart Shop Cart

Step 1: Create the Dataset

Prepare a table with these columns:

• Item Name: Name of the product (e.g., Pikachu Plushie, Poké Ball Keychain).
• Price: The price of each product.
• Quantity: Number of units purchased.
• Subtotal: Price × Quantity.
• Discount: Any applicable discounts.
• Total: Final price after the discount.

Example Dataset

Step 2: Use Formulas

• Calculate Subtotal:
  In the Subtotal column, use:

=Price * Quantity  

• Apply Discount:
  In the Total column, calculate:

=Subtotal - (Subtotal * Discount / 100)  

• Calculate Grand Total:
  Use the SUM function at the bottom of the Total column:

=SUM(Total Range)  

Step 3: Add Data Validation

To avoid errors in quantity and discount entries, set data validation rules:

• Quantity: Must be a whole number greater than 0.
• Discount: Between 0 and 100.

Step 4: Conditional Formatting

Highlight key insights dynamically:

• Use conditional formatting to flag items with discounts greater than 20%.
• Apply a color scale to the Total column to visualize high-value items.

Step 5: Visualize the Shopping Cart

Create a bar chart to show:

• Items on the X-axis.
• Total price on the Y-axis.

Use a Pie Chart to display the percentage contribution of each item to the grand total.

Example Scenario

A customer purchases the following:

• Pikachu Plushie: 2 units, 10% discount.
• Poké Ball Keychain: 3 units, no discount.
• Charmander T-Shirt: 1 unit, 5% discount.

The system calculates totals and provides insights, such as:

• The grand total cost.
• Which item contributed the most to the sales.

Advanced Features

• Automate Tax Calculations: Add a tax column to calculate VAT or GST.

=Total * Tax Rate  

• Dynamic Dashboard: Create slicers to filter items by category or price range.

Why Use Excel for Poke Mart?

Excel is ideal for small businesses like Poke Mart to track sales and generate insights without expensive tools. It’s simple, cost-effective, and powerful.

Learn Excel with The Coding College

At The Coding College, we turn theory into practice with case studies like Poke Mart Shop Cart to help you master Excel for real-world applications. Follow us for more tutorials, case studies, and tips to elevate your skills!