Capital Budgeting and Financial Planning

1. Capital Budgeting and Financial Planning Course Instructor: M.Jibran Sheikh

2. Regression Analysis Using Excel

3. Regression and Correlation • Techniques that are used to establish whether there is a mathematical relationship between two or more variables, so that the behavior of one variable can be used to predict the behavior of others. Applicable to “Variables” data only. • “Regression” provides a functional relationship (Y=f(x)) between the variables; the function represents the “average” relationship. • “Correlation” tells us the direction and the strength of the relationship. • The analysis starts with a Scatter Plot of Y vs X. The analysis starts with a Scatter Plot of Y vs X

4. Simple Linear Regression rise run m = slope = Y b = Y intercept = the Y value at point that the line intersects Y axis. rise run b 0 X A simple linear relationship can be described mathematically by Y = mX + b

5. 1 (6 - 3) rise run = = slope = 2 (10 - 4) rise run intercept = 1 Simple Linear Regression Y 5 0 X 0 10 5 Y = 0.5X + 1

6. Regression Analysis Using Excel 2007 The Analysis Tookpak Excel 2007 has a built-in regression analysis tool that’s packaged as part of its “Analysis Toolpak”. The Analysis Toolpak is a standard component of Excel. Microsoft makes it available as an Excel add-in. If you loaded your copy of Excel on your computer, you may recall that you had a choice during the installation process whether or not to include add-ins such as the Analysis Toolpak.

7. If the Add-In is ALREADY Installed If the Analysis Tookpak add-in is already installed, you’ll see an “Analysis” group in the “Data” tab of Excel’s ribbon. Click the “Data Analysis” button in that group to open the “Data Analysis” dialog. Then scroll down to find the “Regression” option.

8. If the Add-In is NOT ALREADY Installed If you’re using your own computer and you do not yet have the add-in installed, follow these steps to install it: 1. Close Excel if you have it open. 2. Make your installation medium (probably a CD) available to your computer. 3. Restart the Excel installation routine. (Note that you need not reinstall Excel entirely.) 4. Find the option to modify the installation and choose the Analysis Tookpak as an add-in that should be activated. 5. Exit the installation process. 6. Open Excel and confirm that the “Data Analysis” dialog is available

9. Open the Regression Analysis Tool In the “Data” tab on Excel’s ribbon find the “Analysis” group and click the “Data Analysis” button

11. Choosing dependent and Independent variables The dependent, Y variable. Independent, X variable

12. Excel 2007’s “Regression” dialog displays. This is the sole interface to Excel’s regression tool. All your interaction with Excel’s regression tool happens in this dialog.

13. Completing the “Regression” Dialog Box • Specify “Company returns” as the “Input Y Range”. Include the column header cell (the cell that holds the text “Company returns”) in your range specification1. • Specify the “Input X Range” i.e. Market Returns. • Include only one row of column headers with your range specification (that is, include only the labels directly above the data). • - In the “Regression” dialog box make sure the Labels box is checked. With this option • checked Excel will recognize that the first cell in each column of data is a data label.

14. Simple Linear Regression What is it? Determines if Y depends on X and provides a math equation for the relationship (continuous data) y • Examples: • Process conditions and product properties • Sales and advertising budget x Does Y depend on X? Which line is correct?

15. The data on Company returns and market returns for 36 months will be analyzed in EXCEL.

16. Scatter plot Scatter plot suggests that there is a ‘linear’ relationship between Company Returns and Market Returns