Profit Optimizer
Predictive Analytics

Profit Optimizer in Excel using Multiple Regression

In this article, we have explained how to predict a value using multiple regression Analysis in Microsoft Excel.

What is Multiple Regression?

Multiple regression is an extension of simple linear regression. It is used when we want to predict the value of a variable based on the value of two or more other variables. The variable we want to predict is called the dependent variable (or sometimes, the outcome, target or criterion variable)

The multiple regression equation is y = b1x1 + b2x2 + … + bnxn + c. Here, bi’s (i=1,2…n) are the regression coefficients, which represent the value at which the criterion variable changes when the predictor variable changes.

You can use the Data Analysis Add-in to run a regression in Excel.  if the Data Analysis command is not available in your version of Excel, you need to load the Analysis ToolPak add-in program. These instructions apply to Excel 2010, Excel 2013 and Excel 2016.

  • Click the File tab, click Options, and then click the Add-Ins category.
  • In the Manage box, select Excel Add-ins and then click Go.
  • In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK.
  • Data Analysis button will be available in Data tab.

We have the below given Sales data. We have to create a model on to predict the Unit sold on the base of Sale Price and Discount%.

Sales Data
Sales Data

Below is the steps to run the regression in Excel.

  • Click on Data Analysis button in Data Tab
  • Data Analysis window will be opened.
  • Select the Regression in this window and click on OK
Data Analysis Window
Data Analysis Window
  • Provide the Input Y Range for until sold (Select the data including headers)
  • Provide the Input X Range for Sale Price and Discount% (Select the data including headers)
  • Tick the Labels check box.
  • Click on OK.
Regression Window
Regression Window

Regression Summary Output sheet will be created. We will use Intercept and Coefficients values to calculate the predicted Until Sale on the base Discount% and Sale Price. These values are highlighted in yellow in below given image.

Regression Summary Output
Regression Summary Output

Now we can create a table as given in below image and we can put below given formula to predict the Unit Sale –

Predicted Unit Sale = Sale Price * Sale Price Coefficient + Discount% * Discount% Coefficient + Intercept

Once we will get Predicted Unit Sale then we can get the other values also –

Gross Sale = Predicted Unit Sale * Given Sale Price

Net Sale = Gross Sale - Gross Sale * Given Discount%

COGS (Cost of Goods) = Predicted Unit Sale * 5 (Note here you can take your own COGS Calculation, We have put a default cost per unit 5)

Profit = Net Sale – COGS

Profit% = Profit / Net Sale
Profit Optimizer
Profit Optimizer

 

Click here to download this practice file.

Watch the step by step video tutorial:

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com