This assignment requires you to use Excel. In question 1, you will use the charting features. In
questions 2 and 3, use the regression tool from the analysis toolpack.
There is no template for this assignment. Make sure you explain your answers and provide the
regression output tables for questions 1 and 2.
Question 1
Shown below are rental and leasing revenue
figures for office machinery and equipment in the United
States over a seven-year period according to the U.S. Census Bureau. Use these data to run a linear
regression and then
forecast the rental and leasing revenue for the year 2012.
Year Rental and Leasing ($ millions)
2004 5,860
2005 6,632
2006 7,125
2007 6,000
2008 4,380
2009 3,326
2010 2,642
Regression equation is in this format:
predicted y = a + bx
...where a = intercept and b = slope.
Determine the equation, then substitute 2012 for x to find predicted y (forecasting the rental and leasing revenue).
To answer question 1, you need to run a linear regression using the rental and leasing revenue data for office machinery and equipment over the seven-year period. Here's how you can do it in Excel:
1. Open Excel and enter the provided data into two columns. Put the years in column A and the revenue figures in column B.
2. Select the range of data (both columns) by clicking and dragging over the cells.
3. Go to the "Insert" tab in the Excel ribbon and click on the "Scatter" chart icon. Choose the first scatter chart option, which is simply dots without lines connecting them.
4. Right-click on any of the data points on the chart and select "Add Trendline" from the menu.
5. In the "Add Trendline" dialog box, select the "Linear" option. Make sure the "Display Equation on chart" and "Display R-squared value on chart" checkboxes are ticked.
6. Click "Close" to add the linear regression trendline to the chart.
7. Now you can see the trendline on the chart along with the equation and R-squared value.
To forecast the rental and leasing revenue for the year 2012, you can use the equation of the trendline. The equation represents the relationship between the year (x-axis) and the revenue (y-axis).
Now, to forecast the revenue for 2012:
1. First, find the corresponding value of x for the year 2012. In this case, 2012 is 8 years after the starting year (2004).
2. Plug the x-value into the equation of the trendline and solve for the y-value (revenue).
For example, if the equation of the trendline is y = 1000x + 1000, you would substitute x = 8 to find the forecasted revenue for 2012.
Please note that you may have a different equation for the trendline based on your data. Use the equation given on your chart to calculate the forecasted revenue.
Be sure to include the regression output tables and explain your findings in your answer.
To run a linear regression and forecast the rental and leasing revenue for the year 2012 using Excel, follow these steps:
Step 1: Enter the data into an Excel spreadsheet in two columns, one for the years and another for the rental and leasing revenue. Your data should look like this:
Year Rental and Leasing ($ millions)
2004 5,860
2005 6,632
2006 7,125
2007 6,000
2008 4,380
2009 3,326
2010 2,642
Step 2: Select the two columns of data.
Step 3: Go to the "Insert" tab on the Excel ribbon and click on "Scatter" in the "Charts" section. Choose the first scatter plot option which is just dots without connecting lines.
Step 4: Right-click on one of the data points in the scatter plot and select "Add Trendline" from the menu that appears.
Step 5: In the "Add Trendline" dialog box that appears, choose the "Linear" option from the "Trend/Regression Type" section. Make sure the "Display Equation on chart" and "Display R-squared value on chart" options are checked. Click "Close" to insert the trendline.
Step 6: Right-click on the trendline and select "Format Trendline" from the menu.
Step 7: In the "Format Trendline" dialog box, go to the "Options" tab and check the "Display equation on chart" and "Display R-squared value on chart" options. Click "Close" to apply the changes.
Step 8: The equation of the trendline represents the linear regression equation. It will be in the form "y = mx + b", where "m" is the slope (gradient) and "b" is the y-intercept. Note down the values of "m" and "b" from the equation.
Step 9: Use the linear regression equation to forecast the rental and leasing revenue for the year 2012. Substitute "2012" for "x" in the equation and calculate the forecasted value.
Step 10: The final step is to interpret the forecasted value and provide the regression output table. The regression output table should include the values for the coefficient (slope), intercept, and R-squared.
That's it! You have now run a linear regression and forecasted the rental and leasing revenue for the year 2012 using Excel.