Conducting quantitative analysis with Excel - a brief note

Both Engineering Management and Business Management students might conduct quantitative analysis; I want to identify 2 main functions in Excel that are very useful for these quantitative exercises, as there is a misconception that doing  more sophisticated statistical analysis (e.g for final year projects) requires specialized statistical analysis software such as SPSS. Apparently, for professional practices in commercial enterprises, I still recommend using specialized statistical analysis softwares.

The first main function is Regression analysis; to use this function, you need to add in this function with "Analysis Tool Pak - VBA". Then, you will find this funcion  from "Data", then "Data Analysis"; then choose "Regression". You use this function to conduct multiple regression analysis.

The second function is Solver; you need add in this fucntion with "Solver add in". This function allows you to do linear  programming (solved with the simplex method). Again go to "Data", then "Data Analysis", then choose "Solver". See following exhibits:

Exhibit 1

Exhibit 2

In the case of linear programming, exhibit 3 and 4 below are provided for illustration (the figures are based on an example on simplex method from Lucey (2002):

Exhibit 3

Exhibit 4:

As to multiple regression analysis, the following exhibits are provided:

Exhibit 5

Exhibit 6:

Exhibit 7

Exhibit 8

Exhibit 9

Additional photos for illustration:

Photo 1

Photo 2

Photo 3

Photo 4

Photo 5

The main point is: Excel has a number of statistical and quantitative analysis functions that are in most cases sufficient to support students to conduct quantitative analysis in final year dissertation. The main challenge is to master the underlying quantitative techniques as the key steps involved in using Excel are quite simple and just take a few minutes (not to count the time spent on entering the raw data in Excel).

There are other quantitative/ statistical functions in Excel; I mainly use these two functions as major examples for illustration.

References

1. Lind, D.A., Marchal, W.G. and Mason, R.D. (2001) Statistical Techniques in Business & Economics, McGraw Hill. [Study Chapter 14: Multiple Regression and Correlation Analysis]
2. Lucey, T. (2002) Quantitative Techniques, Thomson. [Study Chapter 18: Linear Programming - Simplex method for maximising]