EXCEL Exponential Regression 📈 Decay Model & Exponential Distribution for Project Cost Management
In this video of #engineeringmanagementacademy #ExponentialRegression is tutored for #regressionanalysis by #drmehrdadarashpour
🔔 Subscribe: https://bit.ly/EngineeringManagementA...
❎ Excel workbook to follow along: https://bit.ly/Exponential_Regression...
🎞 Other Excel Tutorials: https://bit.ly/PM_Excel_Academy
⌛ TIMESTAMPS
0:00 - Introduction to Exponential Regression in Excel
0:08 - Excel’s Dynamic Template, Qualitative Analysis by Plotting Tools, LOGEST Function for Exponential Modeling, Forecasting Future Scenarios
0:22 - Step 1 (Plotting Data to Find Trends of Exponential Growth or Decay)
0:56 - Step 2 (Using LOGEST Function for Exponential Modelling)
1:58 - Step 3 (Forecasting Future Scenarios Based on Exponential Growth or Decay Models)
2:22 - Concluding Remarks
✍ Request Next Tutorial Video: https://forms.gle/Frz9U9imCouofdoD8
You will be surprised to see how well exponential modeling in Excel can forecast future costs in project management! In this video, we’ll create & share a dynamic Template in Excel with 3 easy steps: Finding Exponential Growth or Decay Trends in our data, LOGEST Function for Exponential Modelling, & Forecasting Future Scenarios. You can follow along by getting the Excel workbook via the provided link in the description below. https://bit.ly/Exponential_Regression...
Step 1 is Plotting Data to Find Trends of Exponential Growth or Decay
Our case study includes 11 projects of installing solar panels. The installation costs are increasing exponentially due to factors such as inflation & increased labor costs. The panel prices are decreasing exponentially because of improving manufacturing processes. To investigate trends in our data, we can create a line plot with markers by selecting two columns of cost data. As you can see, installation costs are exponentially growing & panel costs are decaying. Using this plot visualization, we can also estimate intercepts or constant coefficients for our exponential models.
Step 2 is Using LOGEST Function for Exponential Modelling
We can numerically calculate the growth or decay rate & also intercept of exponential curves using the LOGEST Function in Excel or logarithmic estimation. Simply select an array of 2 cells & insert the function. The first argument is known_ys, which are installation costs in this example. The 2nd argument after the comma is known_xs, which is the year. Remember to press “shift+Ctrl+Enter” for array calculations in Excel. 1.08 is the growth rate or exponential coefficient. In this example, year after year, installation costs are increased by 8%.
The intercept or constant coefficient is 3000. This shows starting value of the dependent variable (y) when the independent variable (x) is zero. We can use EXP function to formulate our model as: Installation Cost= 3000*EXP(8%*Year). We follow the same procedure for panel costs or photovoltaic cells. The intercept is 6000 & decay rate is 0.9, showing that year after year, panel costs are decreased by -10%. We can use EXP function to formulate our model as shown here →
Step 3 is Forecasting Future Scenarios Based on Exponential Growth or Decay Models
For future years beyond the data range, you will get the forecasted values. We can predict the installation cost in year 11, as an example, using our model. This equals 3000*EXP(8%*11). This forecast can be replicated for future years including 15 & 20. We can also predict the decaying panel costs in year 11 using our model → Panel Cost = 6000*EXP(-10%*Year). Panel costs in years 15 & 20 can also be estimated by replicating the formula.
