Select the EXCEL spreadsheet with the data set you need. Select Data/Data Analysis/ Regression and you will be presented with a menu box to set the parameters for the regression.

Remember x = the independent variable and y = the dependent variable (In cost estimation, y = total cost and x = the cost driver).

Select the Input Y Range by clicking on the spreadsheet icon in the Y window, then dragging the mouse down the “X” column and clicking on the icon again. . Be careful not to include the title of the column but only the numbers. Now select the Input X Range in the same manner entering the data for X (if there is more than one X, you can select multiple columns). Without selecting any other options on the regression menu, click on “OK.” EXCEL runs the regression and produces an output table on a new spreadsheet in the workbook. Under the regression statistics we will use the “ adjusted R Square” statistic (goodness of fit) to evaluate the relationship. We will also use the coefficient information to estimate the function expressing the relationship. The intercept term (a) is the constant in our linear function (y = a + bx). If there is only one X, this is called a simple regression. If there is more than one X, it is a multiple regression.

Write in the answers to the questions in a separate workbook in your main file. Show all work. For initial estimating equations, carry decimals at least to four places (0.000). When submitting your assignment, please submit the regression output and the answers to the questions in one worksheet using 2 tabs (or workbooks).

Data Set – (food.xls); Note that variable definitions are included on the spreadsheet. We will estimate the relation between weekly expenditures for food and weekly income.

Using regression, estimate: Y = a + bX, which in this case is

Weekly food expenditures = a + b (weekly income in $100)

1. What is the adjusted R Square ?

2. Write the estimating equation for this relationship (remember, X is in hundreds of $s).

3. A family has an annual gross income of $100,000. Assuming there are 52 weeks in the year, what is their weekly food expenditure?

4. What percentage of their gross weekly income goes to buy food?