Download Basic Instructions for Using Excel to Generate Scatter Graphs/Regression | ACCT 202 and more Assignments Accounting in PDF only on Docsity! BASIC INSTRUCTIONS FOR USING EXCEL TO GENERATE SCATTERGRAPHS AND REGRESSION DATA The following instructions are tied to Exercise 5-2 in the text, but may be used for any problem. Set up your data in columnar format with the first row used as labels for the column. For example, Column A could be labeled “Month,” Column B could be labeled “Units Shipped,” and Column C could be labeled “Shipping Expense.” Then type in the actual data using rows 2 through 8. To make a scatterplot, Click on “insert” in the menu bar Click on “chart” Click on the icon representing the XY Scattergraph (XY scatter) Click next In the “data range” box, type in B2.C8 L [make sure that “series in columns” is selected] Click next (add a title if desired; value X could be labeled “units” and value Y could be labeled “dollars” if desired) Click next Choose “new sheet” or “object in sheet 1" (depending on whether you want the char t on a new page or on the page with the data) Click on finish To do a simple regression, Click “Tools” on the menu bar Click “Data Analysis” L if data analysis is not present, you will have to do an add in; click on “add in” under the tools menu and check “analysis tool pak” and click okay Under “data analysis,” click on “regression” and hit okay. In the box that appears, enter the following in the “input frame” Input Y range (the dependent variable)–type in C1.C8 Input X range (the independent variable)–type in B1.B8 Make sure that the “labels check box” is checked since we have used row 1 for labels. In the “output options frame,” click either Enter output range and type in A10 (or a cell of your choice) if you want the output on the same page or New Worksheet Ply (type in a title name if desired) if you want the output on a new page In the “residuals frame,” check “line fit plots” and then click okay. (You may also want to click residual plots to see another picture of the “goodness of fit”) The regression and chart will now appear Click on the data plots on the chart to highlight them and then right click with the mouse On the menu that appears, click “add trend line” Click on the “linear” box and then click on okay You may resize the chart by dragging on its drag icons. You may extend the trend line back to the Y axis if you desire by clicking on the trend line to highlight it. Then right click with the mouse and click on “format trend line.” Click on “options” and in the “forecast backwards” box, type in 2 (to move back two units to get to 0). You should now have the complete regression line. Practice and experiment on your own with other exercises and problems.
