Optimal Pricing and Predicting Demand
Optimal Pricing
Optimal pricing is based on a Price-Response Function intersected with it’s associated Profit Function. The Price-Response Function is typically shown as a Linear Regression line and can be thought of as the product’s Demand over Price.
The Profit Function is calculated from the Price-Response Function. The Profit Function is the area under the Price-Response Function at any given price point.
Using these two together, you can find the Optimal Price by overlaying the Profit Function on top of the Price-Response Function. At the highest point on the Profit Function, at the height of it’s arc, is where the rate of change in profit, goes from positive to negative. This means, no matter how much you increase the price, the profit will decrease rather than increase. You have reached the maximum demand and willingness to pay for your customer.
To find these functions, we use data from SAP. For the example of 03-0001
Example of SAP data used in analysis:
## Customer.Vendor.Name Month SO.Price Sum.of.SO.Qty
## 1 (CASH) GINTER'S PLASTERING April 35.99 1.00
## 2 (CASH) GINTER'S PLASTERING July 35.99 1.00
## 3 (CASH) GINTER'S PLASTERING October 35.99 0.25
## 4 (CASH) JDC MASONRY August 37.99 0.25
## 5 (Cash) NEIL BOOY March 35.99 1.00
## 6 (CASH) SAFETY 1ST CHIMNEY SERVICES LTD. August 39.99 2.00Linear Regression
Linear regression is a statistical method used to create a linear model. The model describes the relationship between a dependent variable y (also called the response) as a function of one or more independent variables Xi (called the predictors). The general equation for a linear model is:
A linear regression line has an equation of the form Y = a + bX, where X is the explanatory variable and Y is the dependent variable. The slope of the line is b, and a is the intercept (the value of y when x = 0).
There are several types of linear regression:
- Simple linear regression: models using only one predictor
- Multiple linear regression: models using multiple predictors
The above Matrix shows the relationship between all of the selected variables for this analysis, Quantity, Price, Month, and Customer.
To start let’s graph all of the data we have so far. To graph this data, we will have Price along the X-axis, Quantity on Y, and Month on Z.
In the above data, we look the shape of the data to see if it looks like a typical set of data. Any sort of outliers or incorrect data will be on the extreme ends of the graph.
Since we want to know what the demand will be for a given month, this graph is only useful for seeing the overall trend for the year. We will need to remove the Z-axis and look at each month seperately.
Finding The Model
## Warning in abline(fit2, col = "red"): only using the first two of 13
## regression coefficients## Warning in abline(fit1, col = "blue"): only using the first two of 68
## regression coefficients
## Sum.of.SO.Qty ~ SO.PriceThis equation is the relationship between the Price of an Item and the Quantity sold at that Price. This can also be called the Price-Response Function or Demand Function. On the graph it is the Green line, and it has a downward trend, meaning that as the Price of an item increases, the Quantity ordered will decrease.
## Sum.of.SO.Qty ~ SO.Price + MonthThe second equation is adds the Month variables to the formula. It can be seen as the red line
formula(Sum.of.SO.Qty ~ SO.Price + Month + Customer.Vendor.Name,data = newdata)## Sum.of.SO.Qty ~ SO.Price + Month + Customer.Vendor.NameThe Price-Response Function does not include how Month and Customer influence the demand of a particular item. So now we include that data in our linear model. This can be seen as the Blue line.
The Blue line is influenced by over 70 different coefficients, representing the Price, each Month, and each Customer. Using this to predict quantity for a specific customer or to find an Optimal Price can be misleading. We will want to reduce the amount of variables to one specific Month, and one specific Customer.
There are two types of variables incorporated in this model, numeric and factors. The Price variable is the only numeric variable, it has the largest influence on the Quantity and is present in every model. Factors are a variable that is set to a value of 1 or 0. For example to variable MonthAugust is set to 1 if the selection criteria has been set to the month of August. All other Month variables are set to zero. Our original equation for this model is:
Quantity = (Intercept) - (Price Coefficient)(Price) + (Month Coefficient)(Month) + (Customer Coefficient)(Customer)
So instead of:
Quantity = (Intercept) - (Price Coefficient)(Price) + (August Coefficient)(August) +(September Coefficient)(September) +(October Coefficient)(October) +(November Coefficient)(November) +(December Coefficient)(December) + … + (Customer1 Coefficient)(Customer1) + (Customer2 Coefficient)(Customer2) + …
You will only have:
Quantity = (Intercept) - (Price Coefficient)(Price) + (August Coefficient)(1) + (Customer Coefficient)(1)
So what are the coefficients and how well does the model fit our data?
To do that we run a Regression Analysis in R.
summary(fit)##
## Call:
## lm(formula = Sum.of.SO.Qty ~ SO.Price + Month + Customer.Vendor.Name,
## data = newdata)
##
## Residuals:
## Min 1Q Median 3Q Max
## -34.222 -3.073 0.000 3.069 43.013
##
## Coefficients:
## Estimate
## (Intercept) 344.19416
## SO.Price -9.51961
## MonthAugust -0.92846
## MonthDecember -16.96386
## MonthFebruary -18.13343
## MonthJanuary -29.18051
## MonthJuly 1.18305
## MonthJune 2.45537
## MonthMarch -7.69209
## MonthMay 2.21317
## MonthNovember -14.30861
## MonthOctober -3.68280
## MonthSeptember 0.05718
## Customer.Vendor.Name(CASH) JDC MASONRY 18.63443
## Customer.Vendor.Name(Cash) NEIL BOOY 7.10884
## Customer.Vendor.Name(CASH) SAFETY 1ST CHIMNEY SERVICES LTD. 39.42366
## Customer.Vendor.NameA & A STUCCO 40.52931
## Customer.Vendor.NameACR ENTERPRISES -0.46954
## Customer.Vendor.NameARMSTRONG STUCCO & MASONRY 5059011 MANITOBA LTD. T/A -0.31990
## Customer.Vendor.NameB & M MASONRY -0.64043
## Customer.Vendor.NameBARDA STUCCO 1.31434
## Customer.Vendor.NameBERG'S PLASTERING 4.73139
## Customer.Vendor.NameBILL SUTHERLAND CONT MAS. -2.77746
## Customer.Vendor.NameBRIAN BRISSON 0.30282
## Customer.Vendor.NameBRICE WILLIAMS 17.07573
## Customer.Vendor.NameBUCKO'S STUCCO 2.14767
## Customer.Vendor.NameCASH SALE CONTRACTOR 27.84726
## Customer.Vendor.NameCASH SALES DIRECT 40.82819
## Customer.Vendor.NameCASTELL PETROSO'S 1.67236
## Customer.Vendor.NameCLEAN FINISH STUCCO & PLASTERING 9.31016
## Customer.Vendor.NameDEBROC CONCRETE & MASONRY 2.59955
## Customer.Vendor.NameDEL'S PLASTERING CO LTD -13.67207
## Customer.Vendor.NameDOUBLE E CONSTRUCTION 1.34520
## Customer.Vendor.NameE & K STUCCO -3.97927
## Customer.Vendor.NameE & K STUCCO LTD. 8.92555
## Customer.Vendor.NameESA STUCCO 6.02548
## Customer.Vendor.NameG & W STUCCO & DRYWALL 0.95050
## Customer.Vendor.NameG. BUSCEMI -0.02328
## Customer.Vendor.NameGEBHARDT MASONRY CONTRACTING LTD. -3.53862
## Customer.Vendor.NameGIANCOLA CONSTRUCTION LTD. 0.53046
## Customer.Vendor.NameH-F STUCCO & CONSTRUCTION -0.58325
## Customer.Vendor.NameHAKO Exteriors Ltd. 6.70358
## Customer.Vendor.NameHANS FROESE STUCCO LTD. 10.23139
## Customer.Vendor.NameHEHA STUCCO -1.43984
## Customer.Vendor.NameJ. PENNER PLASTERING 3.66397
## Customer.Vendor.NameJAY'S STUCCO -2.03862
## Customer.Vendor.NameK & T STUCCO -0.07997
## Customer.Vendor.NameKLASSEN BROS. STUCCO LTD. 3.28922
## Customer.Vendor.NameKOPESCHNY PLASTERING -0.40480
## Customer.Vendor.NameL & A STUCCO LTD. 2010 -5.31593
## Customer.Vendor.NameLAST BRICK STANDING -3.29642
## Customer.Vendor.NameMAD DASHERS STUCCO 7.02996
## Customer.Vendor.NameMAURO'S PLASTERING & STUCCO 9.03173
## Customer.Vendor.NameMETRO PLASTERING & ACRYLICS 46.13377
## Customer.Vendor.NameMURRAY PLASTERING 2.62232
## Customer.Vendor.NameOBRAD STUCCO/PLASTERING -0.19184
## Customer.Vendor.NameOLFERT LATHING & STUCCO 53.15996
## Customer.Vendor.NamePAUL'S LATHING & PLAST. 0.93956
## Customer.Vendor.NameSIGURDSON LATHING & PLAST -19.18191
## Customer.Vendor.NameSIMMONS MASONRY -0.84338
## Customer.Vendor.NameSPADES STUCCO 6.85957
## Customer.Vendor.NameTIMELESS MASONRY & TILE LTD. -2.79642
## Customer.Vendor.NameTRIO STAR PLASTERING -14.71464
## Customer.Vendor.NameTRIPLE 'C' ENTERPRISES LTD. -0.08325
## Customer.Vendor.NameTriple C Stucco 14.13689
## Customer.Vendor.NameW.A. ANDERSON 6.25310
## Customer.Vendor.NameWESTAR PLASTERING & STUCCO -13.51068
## Customer.Vendor.NameWYENBERG, ERIC, STUCCO & WIRE -0.01701
## Std. Error
## (Intercept) 63.10365
## SO.Price 1.74091
## MonthAugust 2.89933
## MonthDecember 8.00533
## MonthFebruary 8.55441
## MonthJanuary 11.06929
## MonthJuly 2.85566
## MonthJune 2.70303
## MonthMarch 4.20776
## MonthMay 2.69742
## MonthNovember 3.72721
## MonthOctober 2.68264
## MonthSeptember 2.79999
## Customer.Vendor.Name(CASH) JDC MASONRY 12.81945
## Customer.Vendor.Name(Cash) NEIL BOOY 12.66889
## Customer.Vendor.Name(CASH) SAFETY 1ST CHIMNEY SERVICES LTD. 14.21173
## Customer.Vendor.NameA & A STUCCO 8.79612
## Customer.Vendor.NameACR ENTERPRISES 9.61912
## Customer.Vendor.NameARMSTRONG STUCCO & MASONRY 5059011 MANITOBA LTD. T/A 8.61864
## Customer.Vendor.NameB & M MASONRY 12.26512
## Customer.Vendor.NameBARDA STUCCO 7.45066
## Customer.Vendor.NameBERG'S PLASTERING 7.24928
## Customer.Vendor.NameBILL SUTHERLAND CONT MAS. 9.61789
## Customer.Vendor.NameBRIAN BRISSON 7.24928
## Customer.Vendor.NameBRICE WILLIAMS 7.05625
## Customer.Vendor.NameBUCKO'S STUCCO 7.45066
## Customer.Vendor.NameCASH SALE CONTRACTOR 6.84261
## Customer.Vendor.NameCASH SALES DIRECT 10.48399
## Customer.Vendor.NameCASTELL PETROSO'S 7.46013
## Customer.Vendor.NameCLEAN FINISH STUCCO & PLASTERING 9.62711
## Customer.Vendor.NameDEBROC CONCRETE & MASONRY 12.14729
## Customer.Vendor.NameDEL'S PLASTERING CO LTD 8.69217
## Customer.Vendor.NameDOUBLE E CONSTRUCTION 12.28614
## Customer.Vendor.NameE & K STUCCO 10.28492
## Customer.Vendor.NameE & K STUCCO LTD. 7.95368
## Customer.Vendor.NameESA STUCCO 7.28275
## Customer.Vendor.NameG & W STUCCO & DRYWALL 7.44795
## Customer.Vendor.NameG. BUSCEMI 7.82471
## Customer.Vendor.NameGEBHARDT MASONRY CONTRACTING LTD. 12.23672
## Customer.Vendor.NameGIANCOLA CONSTRUCTION LTD. 9.61912
## Customer.Vendor.NameH-F STUCCO & CONSTRUCTION 12.14940
## Customer.Vendor.NameHAKO Exteriors Ltd. 12.25589
## Customer.Vendor.NameHANS FROESE STUCCO LTD. 7.24928
## Customer.Vendor.NameHEHA STUCCO 9.62711
## Customer.Vendor.NameJ. PENNER PLASTERING 7.07188
## Customer.Vendor.NameJAY'S STUCCO 12.23672
## Customer.Vendor.NameK & T STUCCO 7.44701
## Customer.Vendor.NameKLASSEN BROS. STUCCO LTD. 7.71332
## Customer.Vendor.NameKOPESCHNY PLASTERING 12.28614
## Customer.Vendor.NameL & A STUCCO LTD. 2010 9.63262
## Customer.Vendor.NameLAST BRICK STANDING 12.25589
## Customer.Vendor.NameMAD DASHERS STUCCO 7.43787
## Customer.Vendor.NameMAURO'S PLASTERING & STUCCO 8.36365
## Customer.Vendor.NameMETRO PLASTERING & ACRYLICS 7.02919
## Customer.Vendor.NameMURRAY PLASTERING 7.12888
## Customer.Vendor.NameOBRAD STUCCO/PLASTERING 7.68616
## Customer.Vendor.NameOLFERT LATHING & STUCCO 7.24928
## Customer.Vendor.NamePAUL'S LATHING & PLAST. 8.06243
## Customer.Vendor.NameSIGURDSON LATHING & PLAST 10.18542
## Customer.Vendor.NameSIMMONS MASONRY 8.57164
## Customer.Vendor.NameSPADES STUCCO 12.26512
## Customer.Vendor.NameTIMELESS MASONRY & TILE LTD. 12.25589
## Customer.Vendor.NameTRIO STAR PLASTERING 8.67874
## Customer.Vendor.NameTRIPLE 'C' ENTERPRISES LTD. 12.14940
## Customer.Vendor.NameTriple C Stucco 7.12551
## Customer.Vendor.NameW.A. ANDERSON 9.04351
## Customer.Vendor.NameWESTAR PLASTERING & STUCCO 7.92650
## Customer.Vendor.NameWYENBERG, ERIC, STUCCO & WIRE 7.64974
## t value
## (Intercept) 5.454
## SO.Price -5.468
## MonthAugust -0.320
## MonthDecember -2.119
## MonthFebruary -2.120
## MonthJanuary -2.636
## MonthJuly 0.414
## MonthJune 0.908
## MonthMarch -1.828
## MonthMay 0.820
## MonthNovember -3.839
## MonthOctober -1.373
## MonthSeptember 0.020
## Customer.Vendor.Name(CASH) JDC MASONRY 1.454
## Customer.Vendor.Name(Cash) NEIL BOOY 0.561
## Customer.Vendor.Name(CASH) SAFETY 1ST CHIMNEY SERVICES LTD. 2.774
## Customer.Vendor.NameA & A STUCCO 4.608
## Customer.Vendor.NameACR ENTERPRISES -0.049
## Customer.Vendor.NameARMSTRONG STUCCO & MASONRY 5059011 MANITOBA LTD. T/A -0.037
## Customer.Vendor.NameB & M MASONRY -0.052
## Customer.Vendor.NameBARDA STUCCO 0.176
## Customer.Vendor.NameBERG'S PLASTERING 0.653
## Customer.Vendor.NameBILL SUTHERLAND CONT MAS. -0.289
## Customer.Vendor.NameBRIAN BRISSON 0.042
## Customer.Vendor.NameBRICE WILLIAMS 2.420
## Customer.Vendor.NameBUCKO'S STUCCO 0.288
## Customer.Vendor.NameCASH SALE CONTRACTOR 4.070
## Customer.Vendor.NameCASH SALES DIRECT 3.894
## Customer.Vendor.NameCASTELL PETROSO'S 0.224
## Customer.Vendor.NameCLEAN FINISH STUCCO & PLASTERING 0.967
## Customer.Vendor.NameDEBROC CONCRETE & MASONRY 0.214
## Customer.Vendor.NameDEL'S PLASTERING CO LTD -1.573
## Customer.Vendor.NameDOUBLE E CONSTRUCTION 0.109
## Customer.Vendor.NameE & K STUCCO -0.387
## Customer.Vendor.NameE & K STUCCO LTD. 1.122
## Customer.Vendor.NameESA STUCCO 0.827
## Customer.Vendor.NameG & W STUCCO & DRYWALL 0.128
## Customer.Vendor.NameG. BUSCEMI -0.003
## Customer.Vendor.NameGEBHARDT MASONRY CONTRACTING LTD. -0.289
## Customer.Vendor.NameGIANCOLA CONSTRUCTION LTD. 0.055
## Customer.Vendor.NameH-F STUCCO & CONSTRUCTION -0.048
## Customer.Vendor.NameHAKO Exteriors Ltd. 0.547
## Customer.Vendor.NameHANS FROESE STUCCO LTD. 1.411
## Customer.Vendor.NameHEHA STUCCO -0.150
## Customer.Vendor.NameJ. PENNER PLASTERING 0.518
## Customer.Vendor.NameJAY'S STUCCO -0.167
## Customer.Vendor.NameK & T STUCCO -0.011
## Customer.Vendor.NameKLASSEN BROS. STUCCO LTD. 0.426
## Customer.Vendor.NameKOPESCHNY PLASTERING -0.033
## Customer.Vendor.NameL & A STUCCO LTD. 2010 -0.552
## Customer.Vendor.NameLAST BRICK STANDING -0.269
## Customer.Vendor.NameMAD DASHERS STUCCO 0.945
## Customer.Vendor.NameMAURO'S PLASTERING & STUCCO 1.080
## Customer.Vendor.NameMETRO PLASTERING & ACRYLICS 6.563
## Customer.Vendor.NameMURRAY PLASTERING 0.368
## Customer.Vendor.NameOBRAD STUCCO/PLASTERING -0.025
## Customer.Vendor.NameOLFERT LATHING & STUCCO 7.333
## Customer.Vendor.NamePAUL'S LATHING & PLAST. 0.117
## Customer.Vendor.NameSIGURDSON LATHING & PLAST -1.883
## Customer.Vendor.NameSIMMONS MASONRY -0.098
## Customer.Vendor.NameSPADES STUCCO 0.559
## Customer.Vendor.NameTIMELESS MASONRY & TILE LTD. -0.228
## Customer.Vendor.NameTRIO STAR PLASTERING -1.695
## Customer.Vendor.NameTRIPLE 'C' ENTERPRISES LTD. -0.007
## Customer.Vendor.NameTriple C Stucco 1.984
## Customer.Vendor.NameW.A. ANDERSON 0.691
## Customer.Vendor.NameWESTAR PLASTERING & STUCCO -1.704
## Customer.Vendor.NameWYENBERG, ERIC, STUCCO & WIRE -0.002
## Pr(>|t|)
## (Intercept) 1.57e-07
## SO.Price 1.47e-07
## MonthAugust 0.749157
## MonthDecember 0.035426
## MonthFebruary 0.035366
## MonthJanuary 0.009101
## MonthJuly 0.679151
## MonthJune 0.364866
## MonthMarch 0.069158
## MonthMay 0.413007
## MonthNovember 0.000170
## MonthOctober 0.171477
## MonthSeptember 0.983729
## Customer.Vendor.Name(CASH) JDC MASONRY 0.147759
## Customer.Vendor.Name(Cash) NEIL BOOY 0.575394
## Customer.Vendor.Name(CASH) SAFETY 1ST CHIMNEY SERVICES LTD. 0.006108
## Customer.Vendor.NameA & A STUCCO 7.59e-06
## Customer.Vendor.NameACR ENTERPRISES 0.961121
## Customer.Vendor.NameARMSTRONG STUCCO & MASONRY 5059011 MANITOBA LTD. T/A 0.970431
## Customer.Vendor.NameB & M MASONRY 0.958413
## Customer.Vendor.NameBARDA STUCCO 0.860169
## Customer.Vendor.NameBERG'S PLASTERING 0.514784
## Customer.Vendor.NameBILL SUTHERLAND CONT MAS. 0.773074
## Customer.Vendor.NameBRIAN BRISSON 0.966726
## Customer.Vendor.NameBRICE WILLIAMS 0.016496
## Customer.Vendor.NameBUCKO'S STUCCO 0.773478
## Customer.Vendor.NameCASH SALE CONTRACTOR 6.98e-05
## Customer.Vendor.NameCASH SALES DIRECT 0.000138
## Customer.Vendor.NameCASTELL PETROSO'S 0.822871
## Customer.Vendor.NameCLEAN FINISH STUCCO & PLASTERING 0.334775
## Customer.Vendor.NameDEBROC CONCRETE & MASONRY 0.830782
## Customer.Vendor.NameDEL'S PLASTERING CO LTD 0.117456
## Customer.Vendor.NameDOUBLE E CONSTRUCTION 0.912934
## Customer.Vendor.NameE & K STUCCO 0.699274
## Customer.Vendor.NameE & K STUCCO LTD. 0.263243
## Customer.Vendor.NameESA STUCCO 0.409103
## Customer.Vendor.NameG & W STUCCO & DRYWALL 0.898590
## Customer.Vendor.NameG. BUSCEMI 0.997630
## Customer.Vendor.NameGEBHARDT MASONRY CONTRACTING LTD. 0.772769
## Customer.Vendor.NameGIANCOLA CONSTRUCTION LTD. 0.956081
## Customer.Vendor.NameH-F STUCCO & CONSTRUCTION 0.961763
## Customer.Vendor.NameHAKO Exteriors Ltd. 0.585064
## Customer.Vendor.NameHANS FROESE STUCCO LTD. 0.159826
## Customer.Vendor.NameHEHA STUCCO 0.881275
## Customer.Vendor.NameJ. PENNER PLASTERING 0.605008
## Customer.Vendor.NameJAY'S STUCCO 0.867869
## Customer.Vendor.NameK & T STUCCO 0.991444
## Customer.Vendor.NameKLASSEN BROS. STUCCO LTD. 0.670291
## Customer.Vendor.NameKOPESCHNY PLASTERING 0.973752
## Customer.Vendor.NameL & A STUCCO LTD. 2010 0.581709
## Customer.Vendor.NameLAST BRICK STANDING 0.788257
## Customer.Vendor.NameMAD DASHERS STUCCO 0.345817
## Customer.Vendor.NameMAURO'S PLASTERING & STUCCO 0.281610
## Customer.Vendor.NameMETRO PLASTERING & ACRYLICS 5.20e-10
## Customer.Vendor.NameMURRAY PLASTERING 0.713412
## Customer.Vendor.NameOBRAD STUCCO/PLASTERING 0.980115
## Customer.Vendor.NameOLFERT LATHING & STUCCO 6.92e-12
## Customer.Vendor.NamePAUL'S LATHING & PLAST. 0.907355
## Customer.Vendor.NameSIGURDSON LATHING & PLAST 0.061241
## Customer.Vendor.NameSIMMONS MASONRY 0.921729
## Customer.Vendor.NameSPADES STUCCO 0.576655
## Customer.Vendor.NameTIMELESS MASONRY & TILE LTD. 0.819768
## Customer.Vendor.NameTRIO STAR PLASTERING 0.091675
## Customer.Vendor.NameTRIPLE 'C' ENTERPRISES LTD. 0.994540
## Customer.Vendor.NameTriple C Stucco 0.048745
## Customer.Vendor.NameW.A. ANDERSON 0.490157
## Customer.Vendor.NameWESTAR PLASTERING & STUCCO 0.089977
## Customer.Vendor.NameWYENBERG, ERIC, STUCCO & WIRE 0.998228
##
## (Intercept) ***
## SO.Price ***
## MonthAugust
## MonthDecember *
## MonthFebruary *
## MonthJanuary **
## MonthJuly
## MonthJune
## MonthMarch .
## MonthMay
## MonthNovember ***
## MonthOctober
## MonthSeptember
## Customer.Vendor.Name(CASH) JDC MASONRY
## Customer.Vendor.Name(Cash) NEIL BOOY
## Customer.Vendor.Name(CASH) SAFETY 1ST CHIMNEY SERVICES LTD. **
## Customer.Vendor.NameA & A STUCCO ***
## Customer.Vendor.NameACR ENTERPRISES
## Customer.Vendor.NameARMSTRONG STUCCO & MASONRY 5059011 MANITOBA LTD. T/A
## Customer.Vendor.NameB & M MASONRY
## Customer.Vendor.NameBARDA STUCCO
## Customer.Vendor.NameBERG'S PLASTERING
## Customer.Vendor.NameBILL SUTHERLAND CONT MAS.
## Customer.Vendor.NameBRIAN BRISSON
## Customer.Vendor.NameBRICE WILLIAMS *
## Customer.Vendor.NameBUCKO'S STUCCO
## Customer.Vendor.NameCASH SALE CONTRACTOR ***
## Customer.Vendor.NameCASH SALES DIRECT ***
## Customer.Vendor.NameCASTELL PETROSO'S
## Customer.Vendor.NameCLEAN FINISH STUCCO & PLASTERING
## Customer.Vendor.NameDEBROC CONCRETE & MASONRY
## Customer.Vendor.NameDEL'S PLASTERING CO LTD
## Customer.Vendor.NameDOUBLE E CONSTRUCTION
## Customer.Vendor.NameE & K STUCCO
## Customer.Vendor.NameE & K STUCCO LTD.
## Customer.Vendor.NameESA STUCCO
## Customer.Vendor.NameG & W STUCCO & DRYWALL
## Customer.Vendor.NameG. BUSCEMI
## Customer.Vendor.NameGEBHARDT MASONRY CONTRACTING LTD.
## Customer.Vendor.NameGIANCOLA CONSTRUCTION LTD.
## Customer.Vendor.NameH-F STUCCO & CONSTRUCTION
## Customer.Vendor.NameHAKO Exteriors Ltd.
## Customer.Vendor.NameHANS FROESE STUCCO LTD.
## Customer.Vendor.NameHEHA STUCCO
## Customer.Vendor.NameJ. PENNER PLASTERING
## Customer.Vendor.NameJAY'S STUCCO
## Customer.Vendor.NameK & T STUCCO
## Customer.Vendor.NameKLASSEN BROS. STUCCO LTD.
## Customer.Vendor.NameKOPESCHNY PLASTERING
## Customer.Vendor.NameL & A STUCCO LTD. 2010
## Customer.Vendor.NameLAST BRICK STANDING
## Customer.Vendor.NameMAD DASHERS STUCCO
## Customer.Vendor.NameMAURO'S PLASTERING & STUCCO
## Customer.Vendor.NameMETRO PLASTERING & ACRYLICS ***
## Customer.Vendor.NameMURRAY PLASTERING
## Customer.Vendor.NameOBRAD STUCCO/PLASTERING
## Customer.Vendor.NameOLFERT LATHING & STUCCO ***
## Customer.Vendor.NamePAUL'S LATHING & PLAST.
## Customer.Vendor.NameSIGURDSON LATHING & PLAST .
## Customer.Vendor.NameSIMMONS MASONRY
## Customer.Vendor.NameSPADES STUCCO
## Customer.Vendor.NameTIMELESS MASONRY & TILE LTD.
## Customer.Vendor.NameTRIO STAR PLASTERING .
## Customer.Vendor.NameTRIPLE 'C' ENTERPRISES LTD.
## Customer.Vendor.NameTriple C Stucco *
## Customer.Vendor.NameW.A. ANDERSON
## Customer.Vendor.NameWESTAR PLASTERING & STUCCO .
## Customer.Vendor.NameWYENBERG, ERIC, STUCCO & WIRE
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 10.43 on 184 degrees of freedom
## Multiple R-squared: 0.7968, Adjusted R-squared: 0.7228
## F-statistic: 10.77 on 67 and 184 DF, p-value: < 2.2e-16The P-Value is the Probability that a given variable has no influence on the Price-Response Function. We will look for P-Value <= 0.05
From the data we can see the following variables have a P-Value less than 0.05 and are therefore significant to the model.
We also see that the R-squared value, the closer to 1, the closer the model fits the actual data. The Adjusted R-squared is the value we will use, it is typically used when there are a large number of variables.
summary(fit1)$r.squared## [1] 0.7967845summary(fit2)$r.squared## [1] 0.2122647summary(fit3)$r.squared## [1] 0.1858518We can clearly see by the r.squared values that fit1 is the closest model of our data.
Finding the Coefficients
coef(fit)## (Intercept)
## 344.19416170
## SO.Price
## -9.51961408
## MonthAugust
## -0.92845563
## MonthDecember
## -16.96386380
## MonthFebruary
## -18.13342931
## MonthJanuary
## -29.18051027
## MonthJuly
## 1.18304522
## MonthJune
## 2.45537106
## MonthMarch
## -7.69209440
## MonthMay
## 2.21317067
## MonthNovember
## -14.30860768
## MonthOctober
## -3.68279836
## MonthSeptember
## 0.05718263
## Customer.Vendor.Name(CASH) JDC MASONRY
## 18.63443274
## Customer.Vendor.Name(Cash) NEIL BOOY
## 7.10884335
## Customer.Vendor.Name(CASH) SAFETY 1ST CHIMNEY SERVICES LTD.
## 39.42366090
## Customer.Vendor.NameA & A STUCCO
## 40.52931262
## Customer.Vendor.NameACR ENTERPRISES
## -0.46953739
## Customer.Vendor.NameARMSTRONG STUCCO & MASONRY 5059011 MANITOBA LTD. T/A
## -0.31990459
## Customer.Vendor.NameB & M MASONRY
## -0.64043368
## Customer.Vendor.NameBARDA STUCCO
## 1.31433723
## Customer.Vendor.NameBERG'S PLASTERING
## 4.73138959
## Customer.Vendor.NameBILL SUTHERLAND CONT MAS.
## -2.77745919
## Customer.Vendor.NameBRIAN BRISSON
## 0.30281816
## Customer.Vendor.NameBRICE WILLIAMS
## 17.07573347
## Customer.Vendor.NameBUCKO'S STUCCO
## 2.14767056
## Customer.Vendor.NameCASH SALE CONTRACTOR
## 27.84725923
## Customer.Vendor.NameCASH SALES DIRECT
## 40.82819169
## Customer.Vendor.NameCASTELL PETROSO'S
## 1.67235975
## Customer.Vendor.NameCLEAN FINISH STUCCO & PLASTERING
## 9.31016362
## Customer.Vendor.NameDEBROC CONCRETE & MASONRY
## 2.59954731
## Customer.Vendor.NameDEL'S PLASTERING CO LTD
## -13.67206946
## Customer.Vendor.NameDOUBLE E CONSTRUCTION
## 1.34520459
## Customer.Vendor.NameE & K STUCCO
## -3.97927445
## Customer.Vendor.NameE & K STUCCO LTD.
## 8.92554865
## Customer.Vendor.NameESA STUCCO
## 6.02547640
## Customer.Vendor.NameG & W STUCCO & DRYWALL
## 0.95049636
## Customer.Vendor.NameG. BUSCEMI
## -0.02327701
## Customer.Vendor.NameGEBHARDT MASONRY CONTRACTING LTD.
## -3.53862210
## Customer.Vendor.NameGIANCOLA CONSTRUCTION LTD.
## 0.53046261
## Customer.Vendor.NameH-F STUCCO & CONSTRUCTION
## -0.58325105
## Customer.Vendor.NameHAKO Exteriors Ltd.
## 6.70357829
## Customer.Vendor.NameHANS FROESE STUCCO LTD.
## 10.23138959
## Customer.Vendor.NameHEHA STUCCO
## -1.43983638
## Customer.Vendor.NameJ. PENNER PLASTERING
## 3.66396628
## Customer.Vendor.NameJAY'S STUCCO
## -2.03862210
## Customer.Vendor.NameK & T STUCCO
## -0.07997004
## Customer.Vendor.NameKLASSEN BROS. STUCCO LTD.
## 3.28921677
## Customer.Vendor.NameKOPESCHNY PLASTERING
## -0.40479541
## Customer.Vendor.NameL & A STUCCO LTD. 2010
## -5.31593063
## Customer.Vendor.NameLAST BRICK STANDING
## -3.29642171
## Customer.Vendor.NameMAD DASHERS STUCCO
## 7.02996460
## Customer.Vendor.NameMAURO'S PLASTERING & STUCCO
## 9.03172556
## Customer.Vendor.NameMETRO PLASTERING & ACRYLICS
## 46.13376968
## Customer.Vendor.NameMURRAY PLASTERING
## 2.62232131
## Customer.Vendor.NameOBRAD STUCCO/PLASTERING
## -0.19183624
## Customer.Vendor.NameOLFERT LATHING & STUCCO
## 53.15996101
## Customer.Vendor.NamePAUL'S LATHING & PLAST.
## 0.93955656
## Customer.Vendor.NameSIGURDSON LATHING & PLAST
## -19.18190699
## Customer.Vendor.NameSIMMONS MASONRY
## -0.84337515
## Customer.Vendor.NameSPADES STUCCO
## 6.85956632
## Customer.Vendor.NameTIMELESS MASONRY & TILE LTD.
## -2.79642171
## Customer.Vendor.NameTRIO STAR PLASTERING
## -14.71464358
## Customer.Vendor.NameTRIPLE 'C' ENTERPRISES LTD.
## -0.08325105
## Customer.Vendor.NameTriple C Stucco
## 14.13688547
## Customer.Vendor.NameW.A. ANDERSON
## 6.25309730
## Customer.Vendor.NameWESTAR PLASTERING & STUCCO
## -13.51068102
## Customer.Vendor.NameWYENBERG, ERIC, STUCCO & WIRE
## -0.01700876## Our Equation
eq## [1] "Quantity = + 344.19 (Intercept) - 9.52 (Price) - 0.93 (April) - 16.96 (August) - 18.13 (December) - 29.18 (February) + 1.18 (January) + 2.46 (July) - 7.69 (June) + 2.21 (March) - 14.31 (May) - 3.68 (November) + 0.06 (October) + 18.63 (September) + 7.11 (JDC MASONRY) + 39.42 (NEIL BOOY) + 40.53 (SAFETY 1ST CHIMNEY SERVICES LTD) - 0.47 (A & A STUCCO) - 0.32 (ACR ENTERPRISES) - 0.64 (ARMSTRONG STUCCO & MASONRY) + 1.31 (B & M MASONRY) + 4.73 (BARDA STUCCO) - 2.78 (BERG'S PLASTERING) + 0.3 (BILL SUTHERLAND) + 17.08 (BRIAN BRISSON) + 2.15 (BRICE WILLIAMS) + 27.85 (BUCKO'S STUCCO) + 40.83 (CASH SALE CONTRACTOR) + 1.67 (CASH SALES DIRECT) + 9.31 (CASTELL PETROSO'S) + 2.6 (CLEAN FINISH STUCCO & PLASTERING) - 13.67 (DEBROC CONCRETE & MASONRY) + 1.35 (DEL'S PLASTERING CO LTD) - 3.98 (DOUBLE E CONSTRUCTION) + 8.93 (E & K STUCCO) + 6.03 (E & K STUCCO LTD.) + 0.95 (ESA STUCCO) - 0.02 (G & W STUCCO & DRYWALL) - 3.54 (G. BUSCEMI) + 0.53 (GEBHARDT MASONRY CONTRACTING LTD) - 0.58 (GIANCOLA CONSTRUCTION LTD) + 6.7 (H-F STUCCO & CONSTRUCTION) + 10.23 (HAKO Exteriors Ltd) - 1.44 (HANS FROESE STUCCO LTD) + 3.66 (HEHA STUCCO) - 2.04 (J. PENNER PLASTERING) - 0.08 (JAY'S STUCCO) + 3.29 (K & T STUCCO) - 0.4 (KLASSEN BROS. STUCCO LTD) - 5.32 (KOPESCHNY PLASTERING) - 3.3 (L & A STUCCO LTD) + 7.03 (LAST BRICK STANDING) + 9.03 (MAD DASHERS STUCCO) + 46.13 (MAURO'S PLASTERING & STUCCO) + 2.62 (METRO PLASTERING & ACRYLICS) - 0.19 (MURRAY PLASTERING) + 53.16 (OBRAD STUCCO/PLASTERING) + 0.94 (OLFERT LATHING & STUCCO) - 19.18 (PAUL'S LATHING & PLAST) - 0.84 (SIGURDSON LATHING & PLAST) + 6.86 (SIMMONS MASONRY) - 2.8 (SPADES STUCCO) - 14.71 (TIMELESS MASONRY & TILE LTD) - 0.08 (TRIO STAR PLASTERING) + 14.14 (TRIPLE 'C' ENTERPRISES LTD) + 6.25 (Triple C Stucco) - 13.51 (W.A. ANDERSON) - 0.02 (WESTAR PLASTERING & STUCCO)NANA (WYENBERG, ERIC, STUCCO & WIRE)"The resulting equation for the Price-Response model is
Y = mX + b
Where Y is your Output variable, in our case it’s Quantity.
m is the slope, or rate of change between different data points. These are the coefficients from the multiple variables.
mX is the slope multiplied by the value of X, which is our variables. We have two different kinds of variables to enter into X. The first is Price, a numeric variable, the second is a Factor variable for each Customer. A factor variable is either a 1 or a 0, a 1 is used if the variable is in the data. For example if a purchase is by A & A Stucco, the (-0.47)(A & A STUCCO) will now be (-0.47)(1) or -0.47. If A & A Stucco is not in the data, the equation will now read (-0.47)(0) or 0.
Y = (-9.52)(Price) + (mX) + b
The remaining variables can be placed into (mX).
To find b, our Y intercept we need to find the value of Y when X is 0. In our case we need to set Price equal to 0 and the other variables set to 1 in order to include them in the resulting equation.
b = 344.19
How do these influence the data?
m - The rate of change for a given variable. In our example the m value for Price is -9.52. This means that when the Price increases by $1.00, the Quantity you sell will decrease by 9.52 units.
b - The Intercept. This value is the value of Y when X is 0. In our example this would mean, what would the Quantity sold be if the Price were $0?
According to common sense if the Price were $0 the demand would be infinite, but in the case of our model, the Intercept is:
b = 344.19
Now let’s see how this line predicts our data.
plot(newdata$SO.Price, newdata$Sum.of.SO.Qty)
abline(a=(344.19 + 0.47-7.69), b=-9.52, col="green")
Using this equation we can predict what the quantity will be at any given price point for the item.
Let’s see what we can predict using Prices of $20, $32, $40 in the month of June for A & A Stucco.
#Quantity = 344.19 + (-9.52)("Price") + (-0.47)("A & A Stucco") + (-7.69)("June")
#Quantity = 344.19 + (-9.52)("Price") + (-0.47)(1) + (-7.69)(1)
Quantity1 = 344.19 + (-9.52)*(20) + (-0.47)*(1) + (-7.69)*(1)
Quantity2 = 344.19 + (-9.52)*(32) + (-0.47)*(1) + (-7.69)*(1)
Quantity3 = 344.19 + (-9.52)*(40) + (-0.47)*(1) + (-7.69)*(1)
Quantity1## [1] 145.63Quantity2## [1] 31.39Quantity3## [1] -44.77With this model, we can predict any month with any customer, with a model that has an R-Squared value of 0.79.
We need to be careful with this model as we do not have a clear idea of the MAX Price level since Price was kept static throughout the year. What is the limit of the Price-Response model? Can you find the price that will give you the most quantity?
To answer these questions we will need to create a new function, the Profit Function.
The Profit Function is the Function of Price and is used to find the optimal price of an item.
Profit Function = (Price - Cost)(Price-Response Model)
Optimal Price = Derivative of the Profit Function
plot(X$SO.Price, Y, type = "l", main = CustomerPick, sub = MonthPick, xlab = "Price", ylab = "Quantity", ylim=c(0,400)) #check
abline(v=currPrice, lty=4, col = "red")
abline(h=(predict(fit1,currPriceQty)), lty=4, col = "red")
text(currPrice-6, 0.1, currPrice, col = "red")
text(1, currQty-10, currQty, col = "red")
text(currPrice/2, currQty/2, currRev, col = "red") 
In the above graph we’ve plotted the current price of Plaster Sand at $33.08
According to our model, the expected quantity is 72 units, with a sales revenue of $2381.76
How does this compare to the actual?
| Customer/Vendor Name | Month | SO Price | SO Qty | Actual Revenue | Predicted Revenue | +/- |
|---|---|---|---|---|---|---|
| A & A STUCCO | April | 33.08 | 59 | $1,951.72 | $2,315.60 | 19% |
| A & A STUCCO | May | 33.08 | 63 | $2,084.04 | $2,381.76 | 14% |
| A & A STUCCO | June | 33.08 | 96.5 | $3,192.22 | $2,381.76 | -25% |
| A & A STUCCO | July | 33.08 | 52 | $1,720.16 | $2,348.68 | 37% |
| A & A STUCCO | August | 33.08 | 75.5 | $2,497.54 | $2,282.52 | -9% |
| A & A STUCCO | September | 33.08 | 96.5 | $3,192.22 | $2,315.60 | -27% |
| A & A STUCCO | October | 33.08 | 47.5 | $1,571.30 | $2,183.28 | 39% |
From our predicted values we can see that the predicted values are more uniform than the actual, the actual has considerable more peaks and valleys, having a larger difference month to month. Both Revenue columns both add up to $16,209.20 since the Predicted Revenue formula is based on the Actual Revenue values.
On the graph you can see that the Predicted Revenue of $2381.76 is the Area under the Demand Function at the given price 33.08.
Is this the optimal price? How far can we move the price to maximize the area/revenue? How can we find out our real Profit?
The Profit Function
par(mar = c(5,5,2,5))
with(newdata, plot(SO.Price, Sum.of.SO.Qty,pch=1,xlab="Price",ylab="Quantity Sold Per Month", xlim=c(0,60),ylim=c(0,200)))
par(new = T)
with(profitData, plot(X, profit, type="l", col="red3",axes=F, xlab=NA, ylab=NA,main = CustomerPick, sub = MonthPick,xlim=c(0,60),ylim=c(0,1200)))
abline(a=coefdata[1,]+40.563970745+1.412811351, b=coefdata[2,], col="green")
axis(side = 4)
mtext(side = 4, line = 3, 'Expected Profit(Profit Function) | Demand Quantity')
legend("topright",legend=c("Profit Function", "Demand","Quantity (All Months + Customers)"),lty=c(1,1,0), pch=c(NA, NA,1), col=c("red3", "green","black"))
Above we’ve added the Profit Function to the graph. Earlier we stated that the profit function is:
Profit Function = (Price - Cost)(Price-Response Model)
What this equation will give us is a curved line that indicates the amount of Profit at a particular price point.
A keen eye will notice that at the price level of $16.55 the Profit Function turns negative.
This is not an error, the $16.55 is the Cost of the item, if you sell that item at Cost, your profit will equal to 0, no matter how high your demand.
At the very height of the Profit Function the maximum value is $914.76, we’ve placed a line intercepting this point, on the above graph it is the black dashed line.
The line indicates that the price of $26.45 is the Optimal Price to maximize your profit.
If we were to continue to use the price of $33.08, the maximum profit would be $476.50