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
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.
- Open Microsoft Excel;
- Go to the Data tab in the top menu;
- 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):
- Click the File tab in the top-left corner of Excel;
- Select Options at the bottom of the left-hand navigation pane;
- In the Excel Options window, click Add-Ins on the left;
- In the Manage drop-down menu at the bottom of the window, select Excel Add-ins and click the Go button;
- 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:
- Visit the official Microsoft website to download the Power Query Add-In.
- Once downloaded, open Excel.
- Click on "File" and then "Options."
- In the "Excel Options" window, click on "Add-Ins."
- In the "Manage" dropdown at the bottom, select "COM Add-ins" and click "Go."
- Check the box next to "Microsoft Office Power Query" and click "OK."
- 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.
- Age: customer’s age
- Gender: customer’s gender
- User Region: customer’s location for The Trendy Shopper’s distribution
- Product Category: a diverse selection at The Trendy Shopper
- Product Price: the monetary cost assigned to a specific product
- Discount: the reduction in the original product price (given in absolute monetary value and not presented as a percentage)
- Total Amount Spent: the shopper's total expenditure derived from multiplying the product price by quantity
- 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