Question

We have data (given Below) that shows, the annual sales for four different companies (called company A, B, C and D) for duration of 2000-2010 years.

Make two scatter plot for each company's annual sales. In first scatter plot show Linear regression, display its equations and the R-square value. And in the second scatter plot show Exponential Regression, display its equation and the R-square value. For each company answer which regression (linear or exponential) is a better fit for the given company's sales data, and why? Make sure you specify the company's name (i.e A, or B, etc. ) on each plot

Year 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010

x, number of years passed year 2000 0 1 2 3 4 5 6 7 8 9 10

y, total annual sales for company A ($) 9500 12000 17000 17500 25000 31000 35000 48000 62000 71000 94000

y, total annual sales for company B ($) 10000 12500 15625 19531 24414 30518 38147 47684 59605 74506 93132

y, total annual sales for company C ($) 9500 13500 14000 18000 20500 21000 24000 28500 31000 31500 35500

y, total annual sales for company D ($) 10000 12500 15000 17500 20000 22500 25000 27500 30000 32500 35000

Answer 1

FILE НOМE INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW TEAM Shapes SmartArt Store Pictures Online Recommended Line Column Win/ Slicer Timeline PivotTable Recommended Table Нуperlink My Apps Pictures +Screenshot PivotTables Charts Loss llustrations Add-ins Cha Scatter Tables Sparklines Filters Links 1 YEAR TOTAL ANNUAL SALES FOR COMPANY 'A 2 9500 3 2000 2001 4 12000 2002 2003 17000 5 6 17500 Bubble 7 2004 25000 2005 31000 8 TOTAL AN PANY 'A' 9 2006 35000 100000 48000 10 2007 62000 90000 2008 More Scatter Chart... 11 80000 12 2009 71000 2010 94000 70000 13 60000 14 15 50000 16 40000 17 30000 18 20000 19 10000 20 0 1998 2000 2002 2004 2006 2008 2010 2012 21 22 23 24 25 26 27 Sheet1

FILE PAGE LAYOUT НОМЕ INSERT FORMULAS DATA REVIEW VIEW TEAM DESIGN FORMAT =SERIES (Sheet1!$B$1,Sheet1! $A$2:$A$13,Sheet1!$B$2:$B$13,1) Chart 10 A C D F G H K TOTAL ANNUAL SALES FOR COMPANY 'A 1 YEAR TOTAL ANNUAL SALES FOR COMPANY 'A' LINEAR 2 REGRESSION PLOT 3 2000 9500 12000 2001 4 100000 17000 17500 25000 5 2002 80000 6 2003 Series "TOTAL - 2004 7 Fill Outline 60000 8 2005 31000 35000 48000 62000 71000 94000 2006 2007 2008 9 40000 Delete 10 Reset to Match Style 11 20000 Change Series Chart Type... 12 2009 2010 Select Data... 13 2000 1998 2002 2004 2006 14 3-D Rotation.. -20000 15 Add Data Labels 16 Add Trendline.... 17 Format Data Series... 18 19 20 21 22 23 24 25 26 27 Sheet1 READY

Chart 10 C E F G K Format Trendline TOTALANNUAL SALES FOR COMPANY 'A YFAR + TOTAL ANNUAL SALES FOR COMPANY 'A' LINEAR TRENDLINE OPTIONS REGRESSION PLOT 9500 3 2000 2001 12000 4 100000 2002 17000 2003 17500 80000 TRENDLINE OPTIONS 2004 25000 7 60000 OEgponential 2005 31000 35000 2006 40000 OLinear 10 48000 2007 11 62000 20000 2008 OLogarithmic 12 2009 71000 0 2010 94000 O Polynomial 1998 Order 2 200 2002 2004 2006 2008 2010 2012 -20000 15 Power 16 Moving Average Period 2 17 18 Trendline Name 19 Linear (TOTAL ANNUAL SALES OR COMPANY 20 21 OAutomatic 22 23 OCustom 24 Forecast 25 Forward 0.0 period 26 period 27 Backward 0.0 Sheet1 - 100% READY

Chart 10 A B G Format Trendline TOTALANNUAL SALES FOR COMPANY 'A 1 YEAR TOTAL ANNUAL SALES FOR COMPANY 'A' LINEAR TRENDLINE OPTIONS REGRESSION PLOT 3 2000 9500 12000 4 2001 100000 Y y-7859.1x-2E+0o7 R =0.9088 80000 7 2004 25000 Logarithmic 60000 31000 8 2005 Polynomial Order 2 2006 35000 40000 10 2007 48000 Power 11 2008 62000 20000 2009 71000 12 Moving Average Period 2 94000 13 2010 2002 2004 2006 2008 2010 2012 14 Trendline Name -20000 15 Linear (TOTAL 16. ANNUAL SALES OAutomatic 17 FOR COMPANY 18 A) O Custom 19 20 Forecast 21 period 0.0 Forward 0.0 Backward period Set Intercept 24 0.0 25 Display Equation on chart 26 Display R-squared value on chart 27 Sheet1 +

scatterplot Excel FORMULAS FILI HOME INSERT PAGE LAYOUT DATA REVIEW VIEW TEAM fx P16 K M A B C D E F H N C 1 YEAR TOTALANNUAL SALES FOR COMPANY 'A' TOTAL ANNUAL SALES FOR COMPANY 'A' LINEAR 2 REGRESSION PLOT 9500 2000 2001 12000 100000 5 2002 17000 y 7859.1x-2E+07 80000 6 2003 17500 R2-0.9088 7 2004 25000 60000 2005 31000 40000 9 2006 35000 10 2007 48000 20000 11 2008 62000 0 12 71000 2009 1998 2000 2002 2004 2006 2008 2010 2012 13 2010 94000 -20000 14 15 TOTAL ANNUAL SALES FOR COMPANY 'A' 16 EXPONENTIAL REGRESSION PLOT 17 18 100000 90000 19 20 70000 y 1E-192e.225 21 60000 R-0.9942 22 50000 23 40000 30000 24 20000 25 10000 26 27 2000 2008 1998 2002 2004 2006 2010 2012 28 Sheet READY

x N49 A C D F F G M P 28 29 30 31 TOTALANNUAL SALES FOR COMPANY B 32 YEAR TOTAL ANNUAL SALES FOR COMPANY 'B TOTAL ANNUAL SALES FOR COMPANY 'B 33 100000 100000 34 2000 10000 y-1E-189-ax 90000 35 2001 12500 Y-7852.6x-2E+07 -0.9147 R-0 9998 80000 80000 36 2002 16250 70000 37 2003 19531 60000 60000 50000 30518 40000 40000 40 2006 38147 30000 20000 41 2007 47684 20000 59605 42 2008 10000 43 2009 74506 1998 2000 2002 2004 2006 2008 2010 2012 93132 44 2010 2012 998 2000 2002 2004 2006 2008 2010 -20000 45 46 47 48 49 50 51 52 53 Chart Area 55 Sheet1 +

Similarly as given the steps in the above screenshots to find the linear regression and exponential regression apply the same to the other B,C and D also then we will get the desired plots.
fx L84 L A C D F G M N C 64 65 TOTAL ANNUAL SALES FOR COMPANY 'C 66 YEAR TOTAL ANNUAL SALES FOR COMPANY C TOTAL ANNUAL SALES FOR COMPANY 'C' IINEAR 67 REGRESSION PLOT EXPONENTIAL REGRESSION PLOT 9500 68 2000 45000 40000 69 2001 13500 40000 35000 +06 70 2002 14000 Y=BE-103e ga09871 71 2003 18000 30000 s0000 72 2004 20500 25000 25000 2005 2100 20000 20000 .. 15000 75 2007 28500 15000 10000 76 2008 31000 10000 5000 77 2009 31500 S000 78 2010 35500 0 2002 1998 2000 2004 2006 2008 2010 2012 1998 2000 2002 2004 2006 2008 2010 2012 79 80 81 82 83 84 85 86 87 88 91 Sheet1 + 009% REARY

X fx 1117 N G M O P O 91 92 93 94 95 96 97 98 99 YEAR TOTAL ANNUAL SALES FOR COMPANY 'D TOTAL ANNUAL SALES FOR COMPANY 'D TOTAL ANNUAL SALES FOR COMPANY D' IIINEAR 100 EXPONENTIAL PLOT REGRESSION PLOT 101 2000 10000 45000 102 2001 12500 4000 103 2002 15000 40000 y-1E-101e1 R-0.9704 500 y-250u 104 2003 17500 SE+06 30000 000 000 25000 25000 20000 20000 107 2006 25000 15000 15000 108 2007 27500 O00L 109 2008 30000 10000 110 2009 32500 5000 1998 2000 2002 2004 2006 2008. 2010 2012 111 2010 35000 0 1998 2000 2002 2004 2006 2008 2010 2012 112 113 114 115 116 11기 118 Sheet1 100% READY 885일명8명을5gg은 듣 얻 쁜은 은 은E

As the graph that suits the A is Exponential regression plot

B is Linear regression plot

C is Linear regression plot

D is Exponential regression plot