Regression Analysis in Excel Project

Exploring the Influence of Various Determinants on Customer Spending free intermediate

With Ivan Kitov

Type: Course project

Duration: 4 Hours

Case Description

In this Regression Analysis in Excel project, you’ll be working with data from a company in the e-commerce sector, The Trendy Shopper—a fast-growing e-commerce business that offers a diverse selection of contemporary products across various categories, including fashion, electronics, and home decor. The company has a broad customer base and a wide range of products. However, the enterprise is unsure about the impact of product prices and discounts on customers’ spending, which hampers its ability to create an effective pricing strategy to maximize sales and profits. It currently uses a one-size-fits-all marketing approach. But given the diversity in the customer base and the wide range of products, there may be more effective approaches. That's why The Trendy Shopper aims to leverage its transactional data to understand how factors (product prices, quantity purchases, discounts) affect overall expenditure by utilizing predictive modeling (simple and multiple linear regression). Therefore, your task will be to help the company optimize its pricing and discount strategies, potentially increasing sales and customer satisfaction.

Project requirements

To complete this Regression Analysis in Excel project, please install Microsoft Excel 2007 or later. You also need the Data Analysis ToolPak to conduct the analysis. Follow the steps below to check if the ToolPak has been installed; if not, install it manually.

  1. Open Microsoft Excel;
  2. Go to the Data tab in the top menu;
  3. Search for the Data Analysis button within the Analysis group. If it exists, the ToolPak has been installed, and you can proceed with the analysis.

Install the Data Analysis ToolPak (for Windows):

  1. Click the File tab in the top-left corner of Excel;
  2. Select Options at the bottom of the left-hand navigation pane;
  3. In the Excel Options window, click Add-Ins on the left;
  4. In the Manage drop-down menu at the bottom of the window, select Excel Add-ins and click the Go button;
  5. In the Add-Ins window, check the box next to Analysis ToolPak and click OK to install it.

During the project you will be asked to clean the dataset, remove missing values and transform categorical data into a binary format. One effective way to do this is to use Power Query which is integrated into Excel 2016 and later versions, so no separate installation is needed for these versions.

For users with Excel 2010 or 2013:

  1. Visit the official Microsoft website to download the Power Query Add-In.
  2. Once downloaded, open Excel.
  3. Click on "File" and then "Options."
  4. In the "Excel Options" window, click on "Add-Ins."
  5. In the "Manage" dropdown at the bottom, select "COM Add-ins" and click "Go."
  6. Check the box next to "Microsoft Office Power Query" and click "OK."
  7. You should see a "Power Query" tab added to Excel's ribbon.

After installation, you can access Power Query features via the "Power Query" tab in Excel.

Project files

Excel’s Regression Analysis Dataset.xlsx file consists of four sheets: Task 1, Task 2, Task 3, Task 4, and Task 5. Each sheet contains specific information regarding the project's tasks and corresponding data. Use the data in these sheets to answer the questions that follow.

In this regression analysis in Excel, you'll delve into a dataset from The Trendy Shopper. This dataset is derived from the company's transactional records. It contains the following fields.

  1. Age: customer’s age
  2. Gender: customer’s gender
  3. User Region: customer’s location for The Trendy Shopper’s distribution
  4. Product Category: a diverse selection at The Trendy Shopper
  5. Product Price: the monetary cost assigned to a specific product
  6. Discount: the reduction in the original product price (given in absolute monetary value and not presented as a percentage)
  7. Total Amount Spent: the shopper's total expenditure derived from multiplying the product price by quantity

 

Start project
Project content
  • 1 Project file
  • Guided and unguided instructions
  • Part 1: Correlation Analysis
  • Part 2: Simple Linear Regression
  • Part 3: Multiple Linear Regression
  • Part 4: Advanced Multiple Linear Regression
  • Part 5: Predictive Modeling and Next Steps
  • Quiz
Topics covered
Excel Data Analysis Machine learning