MS Excel: Predicting Sales with Linear and Non-Linear Demand Curve Models (Tutorial)
DescriptionIn this video, the presenter is discussing how to predict sales or demand through linear and non-linear prediction models based on the demand curve. The demand curve is a graphical representation of the relationship between the price of a good or service and the quantity of it that consumers are willing and able to purchase.
The presenter starts by using a decade's worth of average prices and sales data for a volatile product or service to create both a linear and a non-linear model to predict its demand. The data is presented in the form of a scatter chart, with the prices and sales as the x and y axis, respectively.
To create the linear prediction model, the presenter inserts a trendline in the scatter chart and displays the equation of the trendline. The equation is then formatted as numbers, and a copy of the scatter chart is created to create the non-linear model. To create the non-linear model, the presenter changes the trendline type from linear to power.
The presenter then moves on to creating a demand curve chart. The chart is created on a new sheet and includes the value of demand when the price is zero and the slope of the demand curve from the linear curve equation. Three scenarios are used in the model and are compared on the demand curve chart. To create the chart, the presenter uses a formula to compute demand for each scenario. The formula is the intercept minus the slope multiplied by the price.
For the non-linear model, the slope represents the elasticity of demand. The demand formula for the non-linear model is the intercept multiplied by the price raised to the power of the negative slope. A scatter chart is created from this data to create the non-linear demand curve chart.
#DemandCurve #SalesPrediction #LinearModel #NonLinearModel #DataAnalysis #ScatterChart #Trendline #ElasticityOfDemand #PriceAndDemand #Intercept #Slope #ExcelTutorial