Business statistics

104 Statistics & Data Analysis Concepts for Business

Appendix 2A Charts and Graphs using EXCEL: Computer Instructions

The data for constructing charts and graphs in this Appendix can be found in the file: App 2A‐Charts and Graphs and in Data File for Assignment 1

Note: Data File App 2A ‐Charts and Graphs.xlsx is the same as Data File for Assignment 1

Constructing a Histogram Using EXCEL Column A of the EXCEL data file App 2A ‐Charts and Graphs.xlsx contains the lifetime of 200 television components Construct a frequency histogram of this data.

Before constructing the histogram, we need to determine the number of class‐intervals or bins for the data. See the section Frequency Distribution in Chapter 2. Suppose we decided to divide the data into class intervals shown in Table 2A.1. Table 2A.1.

Intervals or Bins

200‐230 230‐260 260‐290 290‐320 320‐350 350‐380 380‐410 410‐440

The above class intervals contain the minimum and the maximum values in the data (203 and 430). Excel requires that the class‐intervals are clearly defined. Once you have this information, you are ready to enter data and construct a histogram. Follow the instructions in Table 2A.2.

Table 2A.2: Instructions for Constructing a Histogram 1. Open the file App 2A‐Charts and Graphs.xls (In this file, column A contains 200 data values

labeled Lifetime. Note that all 200 values are entered in one column because EXCEL reads one variable per column.

2. Column B is labeled Bins and contains the upper limit of each class‐interval in Table 2A.1. In this example, the class intervals we decided were 200‐230, 230‐260,…,,410‐440 and the upper class interval values for these classes were 230, 260, 290, and so on. You may create different class interval values. In creating the class‐intervals, the first and the last intervals must contain the minimum and the maximum values in the data.

3. From the menu bar, click on Data tab. Then click Data Analysis from the far right. 4. Select Histogram from the Data analysis box then click OK. 5. Select cells A1: A201 of column A labeled Lifetime (Hours) for the Input Range. Make sure to

include the label – Lifetime (Hours). The Input Range specifies the actual data. 6. Select column B labeled Bins for the Bin Range. Make sure to include the label. (This column

specifies the upper limit of each class‐interval). 7. Check the Labels box. 8. Click on the circle to the left of Output Range. Next, click on the box next to output range box, and

then click on any blank column on the work sheet. Your results will be displayed starting with the

Chapter 2 Descriptive Statistics: Graphical Methods 105

column you selected. Alternatively, check the circle to the left of New Worksheet Ply to display the chart on a separate sheet.

9. Check the Chart Output box. Click OK. The frequency distribution table (with Bins and Frequency), along with the histogram will be

displayed on the worksheet. If you selected New Worksheet Ply followed by the Chart Output box your chart will be displayed on a separate worksheet. Continued…

10. Click anywhere on the displayed histogram, then from the main menu click on the Design tab

and select one of the several displays you like. 11. Right‐click the mouse on any bar of the histogram and select Format Data Series option. On the

right pane, move the pointer to the left of Gap Width to extreme left to 0% or type 0 in the Gap Width box..

12. Right click any bar of the histogram and select Fill and Outline (one at a time) to change the color and outline of the bars. Note: By clicking on the chart, three additional tabs are displayed on the top right of the chart along with the Series Options (far right‐hand side). These are common to all the charts and can be used to edit the charts and graphs (see Figure A).

13. Click on the histogram then from the top right of the displayed chart, click the box with + sign 14. Check the Axes followed by Axis Titles and type appropriate axis titles for your graph. Check the

Chart Title box and type a title for your chart. Check the Data Labels. This will put the values of each category on the top of the bar.

15. Right click on any bar of the displayed chart. Select Fill to change the color and type of fill of the bars.

16. Right click on any bar of the displayed chart. Select Outline to change the color of the outline of bars by selecting desired color.

17. Convert the bins to actual class intervals (200‐230, 230‐260…and so on) by typing the values in the Bins column of the table. For example, replace the first bin value from 230 to 200‐230, the second value to 230‐260, and so on. The classes will now be replaced by new bin values.

The resulting histogram after completing step 12 in the table above is shown in Figure 2A.1. Figure 2A.2 shows the histogram obtained using step 17.

Use these tabs to edit the charts

Figure A: Editing Tools for Charts and Graphs

106 Statistics & Data Analysis Concepts for Business

Bins Frequency

230 4

260 13

290 28

320 46

350 49

380 40

410 15

440 5

More 0 Figure 2A.1: Histogram of TV Component data

Bins Frequency

200‐230 4

230‐260 13

260‐290 28

290‐320 46

320‐350 49

350‐380 40

380‐410 15

410‐440 5

More 0

Figure 2A.2: Histogram with modified bin values

Plot of Cumulative Frequency (Ogive) over the Histogram An ogive is a plot of cumulative frequency or relative cumulative frequency. It is a very useful plot in estimating the data values between the class intervals when the data are grouped into a frequency distribution. It also can be used in estimating different percentiles from the data. The plot of cumulative frequency for the lifetime data in column A of the data file is shown in

Figure 2A.2 (a). To do this plot, follow the steps in Table 2A.2 but in Step 9, check the circle to the left of New Worksheet Ply. Then check the box Cumulative Percentage. Next, follow the rest of steps 10 through 13 in Table 2A.2. The cumulative percentage plot, along with the frequency histogram, is shown below.

Chapter 2 Descriptive Statistics: Graphical Methods 107

Bins Frequency Cumulative %

200‐ 230 4 2.00%

230‐260 13 8.50%

260‐290 28 22.50%

290‐320 46 45.50%

320‐350 49 70.00%

350‐380 40 90.00%

380‐410 15 97.50%

410‐440 5 100.00%

More 0 100.00%

Figure 2A.2 (a): Histogram and the Cumulative Percentage Plot

Constructing a Frequency Distribution of Qualitative (categorical) Data using EXCEL The data in Table 2A.3 (column B) shows the sample of 60 name brand HD TVs sold during the past two months in a particular store. Part of the data is shown below. Use EXCEL to form

a frequency distribution of this qualitative data. To do this, use the COUNTIF function.

Table 2A.3: TV Sales A B C … E F  TV Brand Frequency 1 Sony Sony =COUNTIF($B$1:$B$60,C1) 2 Mitsubishi Mitsubishi 7 3 Sony RCA 5 4 Sony Panasonic 18 5 Panasonic Sharp 9 6 Mitsubishi 7 Panasonic … continued (see the data file)

The complete data of Table 2A.3 is shown in columns C of the EXCEL data file. To construct a

frequency distribution of the data or to determine how many of each brand were sold, follow the steps in Table 2A.4.

Table 2A.4: Instructions for Constructing a Frequency Distribution of Categorical Data 1. Open the file App 2A‐Charts and Graphs.xlsx

(Column C contains the data on TV brand sold). 2. Label columns E and F as TV Brand and Frequency respectively. 3. In column E, type the name of the 5 television brands (Sony, Mitsubishi, RCA, Panasonic, and

Sharp) for which the data are collected. 4. In column F, type the following function

=COUNTIF($C$1:$C$60,E2) (see Table 2A.3, columns E and F ) 5. Hit the Enter key.

This formula will count all the Sony brands sold from column C and write the result in column F2 (column F, row 2). Copy this formula in the cells F2 through F5. The frequency or the count for the

0.00%

20.00%

40.00%

60.00%

80.00%

100.00%

120.00%

0

10

20

30

40

50

60

F re q u e n cy

Bins

Histogram

Frequency

Cumulative %

108 Statistics & Data Analysis Concepts for Business

data will be as shown in Table 2A.5. You can use this table to construct other types of graphs, such as a bar chart or a pie chart.

The frequency distribution table for the data in column C is shown in Table 2A.5. Table 2A.5

TV Brand Frequency

Sony 21 Mitsubishi 7

RCA 5 Panasonic 18 Sharp 9

Other Graphical Techniques Bar Chart (1) A bar chart is very similar to a histogram. In a histogram, the horizontal or x‐axis always contains the class intervals or quantitative data. In a bar chart the horizontal axis can be a qualitative or quantitative variable (not the class intervals). The other difference between a histogram and a bar graph is that in a bar graph, the bars are usually separated to show that each class is separate. A bar graph is used to show the data that have been summarized in a frequency,

relative frequency. A bar graph is a two‐ dimensional plot where one of the axes shows the labels or categories and the other axis shows the frequency (count), whether that frequency be a relative or percent frequency.

We will construct a bar chart from the data in Table 2A.5 above. The data are reproduced in Table 2A.6.

Table 2A.6: Sale of Big Screen Televisions TV Brand Frequency Sony 21 Mitsubishi 7 RCA 5 Panasonic 18 Sharp 9

To construct a bar chart using EXCEL, follow the instructions in Table 2A.7. Table 2A.7: Instructions for Constructing a Bar Chart

1. Open the file App 2A‐Charts and Graphs.xlsx (Note that column E contains the TV brand and column F contains the frequency or the number sold).

2. Select cells E1:F6 3. Click the Insert tab from the main menu. 4. In the Charts group, select the first option – Column Chart. Under 2‐D Column

select the first chart (Clustered Column) from the list of the displayed charts. 5. From the displayed Designs under the DESIGN menu, select the desired design. 6. From the top right of the displayed chart, click the box with + sign 7. Check the Axes followed by Axis Titles and type appropriate axis titles for your

graph. Check the Chart Title box and type a title for your chart. Check the Data Labels. This will put the values of each category on the top of the bar.

8. Right click on any bar of the displayed chart. Select Fill to change the color and type of fill of the bars.

9. Right click on any bar of the displayed chart. Select Outline to change the color

112 Statistics & Data Analysis Concepts for Business

of the outline of bars by selecting desired color.

The resulting bar chart is shown in Figure 2A.3 below.

Bar Chart 2 The data in columns H and I of the data file contain the unemployment rate from 1980 to 2014. We will construct a bar chart of this data.

Follow the instructions in Table 2A.8 to construct this horizontal bar chart.

Table 2A.8: Instructions for Constructing a Horizontal Bar Chart

1. Open the file App 2A‐Charts and Graphs.xlsx (Note that column H contains the year and column I contains the percent unemployment rate).

2. Select cells I1:I19 3. Click the Insert tab from the main menu. 4. In the Charts group, click the second option (horizontal bar chart) – 2D‐Bar. Under

2‐D Bar select the first chart (Clustered Bar) from the list of the displayed charts. 5. From the displayed Designs under the DESIGN menu, select the desired chart style

(we selected the fourth option.) 6. From the Data options (next to the chart styles), click Select Data 7. In the Select Data Source click the Edit tab under Horizontal (Category) Axis

Labels and from column H select the cells H2:H19 (the cells containing 1980 to 2014). Click OK in the dialog boxes. The vertical axis data values will be replaced by years.

8. From the top right of the displayed chart, click the box with + sign. If the box is not displayed, click on the chart and the tab will appear.

9. Check the Axes, Axis Title, Chart Title, and Data Labels. Type the axes titles and the chart title shown in Figure 2A.4. Selecting Data Labels will put the values of each category on the top of the bar.

10. Right click on any bar of the displayed chart. Select Fill to change the color and the type of fill of the bars

11. Right click on any bar of the displayed chart. Select Outline to change the outline of the bars if desired.

The resulting bar chart is shown in Figure 2A.4.

Figure 2A.3: Bar Chart of TV Sales

Figure 2A.4: A Horizontal Bar Chart of Unemployment Rate

21

7 5

18

9

0

5

10

15

20

25

Sony Mitsubishi RCA Panasonic Sharp

N U M B E R S O L D

TV BRAND

Bar Chart of TV Sales

7.2 10.8

7.3 6.6

5.3 6.3

7.4 5.5 5.4

4.4 3.9

6 5.4

4.4 6.1

9.5 7.9

5.9

0 2 4 6 8 10 12

1980

1984

1988

1992

1996

2000

2004

2008

2012

Unemployment Rate (%)

Y e a r

U.S. Unemployment Rate (1980‐2014)

112 Statistics & Data Analysis Concepts for Business

Bar Chart 3: Comparing Two Sets of Data Columns K, L, and M of the data file Charts and Graphs.xls show the U.S. export to different countries for the year 2006 and 2007. To compare the export values for the two years, we

can display the data using a bar chart. The instructions for constructing such a chart are shown in Table 2A.9.

Table 2A.9: Instructions for Constructing a Horizontal Bar Chart of Two Sets of Data

1. Open the file App 2A‐Charts and Graphs.xlsx (Note that column K contains the country and columns L and M contain the export

values for the year 2006 and 2007 in billions of dollars). 2. Select cells K1:M11 then click the Insert tab from the main menu. 3. In the Charts group, click the second option (horizontal bar chart) – 2D‐Bar. Under

2‐D Bar select the first chart (Clustered Bar) from the list of the displayed charts. 4. From the displayed Designs under the DESIGN menu, select the desired chart style

(we selected the fourth option.)

5. From the top right of the displayed chart, click the box with + sign 6. Check the Axes, Axis Title, Chart Title, and Data Labels. Type the axes titles and the

chart title as shown in Figure 2A.5. Selecting Data Labels will put the values of each category on the top of the bar.

7. Right click on any bar of the displayed chart. Select Fill to change the color and the fill pattern of the bars

The bar chart is shown in Figure 2A.5.

Figure 2A.5: A Horizontal Bar Chart Comparing Two Sets of Data

Bar Chart 4: Comparing Two Sets of Data using a Vertical Bar Chart A vertical bar chart can also be used to display the data in Figure 2A.5. The instructions to do such a chart are very similar to that of Table 2A.9.

Follow the instructions in Table 2A.9 except in the Charts group; click the first option (vertical bar chart). Note that we have changed the fill pattern for one of the bars to distinguish the comparison. This can be done by right‐clicking

189

111

54

36

35

31

26

24

22

21

212

140

60

32

30

43

38

20

28

26

0 50 100 150 200 250

Canada

Mexico

Japan

UK

China

Germany

South Korea

Netherlands

Taiwan

France

Export($Billion)

Co un tr y

U.S. Export to Different Countries

Export ($ Billion, 2007) Export ($ Billions, 2006)

Chapter 2 Descriptive Statistics: Graphical Methods 123

any bar and selecting the Fill option or by choosing Pattern Fill from the Fill menu on the right pane. The chart is shown in Figure 2A.6.

Figure 2A.6: A Vertical Bar Chart Comparing Two Sets of Data

Line Chart or Connected Line Plot A line chart connects data points using a straight line. This type of chart is useful in observing increasing/deceasing trend or pattern in the

data. We will construct a line chart of Nasdaq Stock Values from 1984 to 2014. The values are for the month of December except for 2014 value which is the October value. The yearly values are shown in columns O and P of data file Charts and Graphs.xls. To do a line chart of the data, follow the instructions in Table 2A.10.

Table 2A.10: Instructions for Constructing a Line Chart

1. Open the file App 2A‐Charts and Graphs.xlsx (Note that column O contains the year and column P contains the percent Nasdaq Stock Index values).

2. Select cells P1:P32 3. Click the Insert tab from the main menu. 4. In the Charts group, select the Line Chart. Under 2‐D Line menu select the 4th chart

(Line with Markers) from the list of the displayed charts. 5. From the displayed Designs under the DESIGN menu, select the desired chart style

for a different design (we selected the 6th option.) 6. From the Data options (next to the chart styles), click Select Data 7. In the Select Data Source click the Edit tab under Horizontal (Category) Axis

Labels and from column O select the cells O2:O32 (the cells containing 1984.12 to 2014.12). Click OK in the dialog boxes. The x‐axis data values will be replaced to years.

8. From the top right of the displayed chart, click the box with + sign. If the box is not displayed, click on the chart and the tab will appear.

9. Check the Axes, Axis Title, and Chart Title. Type the axes titles and the chart title shown in Figure 2A.7.

189

111

54 36 35 31 26 24 22 21

212

140

60 32 30 43 38 20 28 26

0

50

100

150

200

250 ($ B IL LI O N )

COUNTRY

U.S. Export to Different Countries

Export ($ Billions, 2006) Export ($ Billion, 2007)

112 Statistics & Data Analysis Concepts for Business

The chart is shown in Figure 2A.7.

Figure 2A.7: A Line Chart of Stock Index Values

Pie Chart A pie chart is used to show the relative magnitudes of parts to a whole. In this chart, relative frequencies or percent frequencies for each group of data are plotted. A circle is constructed and divided into distinct sections. Each section represents one group of data. The area of each section is determined by multiplying the relative frequency of each section by the angle of a circle. Since there are 360 degree in a circle, each section is multiplied by 360 degree to obtain the correct number of degrees that each section represents.

A pie chart of Federal Budget for 2004 has been constructed using data in Table 2A.11. The data shows the dollar value for each of the categories. The steps in Table 2A.12 show how to construct a pie chart using this data.

____________________________________________________________________________________________________ Table 2A.11: Federal Expenditure for 2004

Federal Expenditure(2004) Billions of $ National Defense 455.9 Social Security 495.5 Medicare 269.3 Medicaid 176.2 Interest on Debt 160.2 Education/Employment 87.9 Transportation 64.6 Retirement and disability 94.8 Health 63.9 Other 481.5

Table 2A.12: Instructions for Constructing a Pie Chart

1. Open the file App 2A‐Charts and Graphs.xlsx (Note that column R contains the categories and column S contains the corresponding values in billions of dollars). 2. Select cells R1:S11 3. Click the Insert tab from the main menu.

0 500

1000 1500 2000 2500 3000 3500 4000 4500 5000

St oc k V al ue s

Year

Nasdaq Stock Index Values

Chapter 2 Descriptive Statistics: Graphical Methods 113

4. In the Charts group, select the Pie Chart. Under 2‐D Pie options, click on the first pie chart from the list of the displayed charts. A default pie chart will be displayed. Continued…

5. From the top right of the displayed chart, click the box with + sign. If the box is not displayed, click on the chart and the tab will appear.

6. Check the Chart Title box and type‐in a title for your chart. Similarly, check the Data Labels. This will print the value of each category on the chart.

7. From the displayed Designs under the DESIGN menu, select the desired chart style (we selected the design with category names on the side of each slice.) Click on the category name and change the font size, font color, etc. if desired.

The resulting chart is shown in Figure 2A.8. You can create several other variations by selecting different designs from the Design menu. Figure 2A.9 shows another chart with category names, values, and percent for each category. You may click on the slice labels and change the font type and color.

Figures 2A.8 and 2A.9 show two different pie charts.

Figure 2A.8: Pie Chart Showing the Actual Values in Each Category

Figure 2A.9: Pie Chart Showing the Percent in Each Category

Frequency Polygon A frequency polygon is the plot of mid‐point and the frequency of each class of a frequency

distribution. You can construct a frequency polygon by plotting the mid‐points and the frequencies and connecting the lines between

National  Defence, 455.9

Social Securit,  495.5

Medicare, 269.3Medicaid, 176.2

Interest on  Debt, 160.2

Education/Empl oyment, 87.9

Transportation,  64.6

Retirement and  disability, 94.8

Health, 63.9

Other, 481.5

Federal Expenditure 2014 ($Billion)

National  Defence, 455.9, 

19%

Social Securit,  495.5, 21%

Medicare, 269.3,  11%Medicaid, 176.2, 

8% Interest on 

Debt, 160.2, 7%

Education/Empl oyment, 87.9, 

4%

Transportation,  64.6, 3%

Retirement and  disability, 94.8, 

4%

Health, 63.9, 3% Other, 481.5, 

20%

Federal Expenditure 2014 ($Billion & Percent)

114 Statistics & Data Analysis Concepts for Business

the points. This provides a connected graph. You can also construct a frequency polygon on the histogram itself. The frequency polygon is useful in determining the shape of the data (symmetrical or skewed). It is more useful in comparing two sets of data in terms of their shape.

Table 2A.13 shows the annual income of engineers in two cities. We constructed the frequency distribution for the data in both cities

with common class intervals. The class‐intervals along with the midpoints and frequencies are shown in Table 2A.14.

Table 2A.13: Income of Engineers in Two Cities City1 Data(X1000$)

40 47 56 57 59 59 63 64 65 70 70 72 73 73 74 74 76 76 77 81 83 84 85 93 94 94 95 99 109 114 124 125 130 140 141 154 165

City2 Data(X1000 $) 77 117 70 107 93 70 48 40 37 48 47 78 82 75 95 63 89 74 72 80 58 63 81 74 84 75 65 81 70 94 84 124

Table 2A.14: Incomes of Engineers in Two Cities Class Intervals ($ 1000 )

Mid‐point Freq1 (city 1) Freq2 (city 2)

20 ‐ 40 30 0 1 40 ‐ 60 50 6 5 60 ‐ 80 70 13 13 80 ‐ 100 90 9 10 100 ‐ 120 110 2 2 120 ‐ 140 130 3 1 140 ‐ 160 150 3 0 160 ‐ 180 170 1 0

To construct frequency polygons, follow the steps in Table 2A.15.

Table 2A.15: Instructions for Constructing Frequency Polygons 1. Open the file App 2A‐Charts and Graphs.xlsx

(Note that column U contains the mid‐points and columns V and W contain the frequencies).

2. Select cells V1:W9 3. Click the Insert tab from the main menu. 4. In the Charts group, select the Line Chart. Under 2‐D Line menu select the 4th chart

(Line with Markers) from the list of the displayed charts. 5. From the displayed Designs under the DESIGN menu, select the desired chart style

for a different design 6. From the Data options (next to the chart styles), click Select Data 7. In the Select Data Source click the Edit tab under Horizontal (Category) Axis

Labels and from column U select the cells U2:U9 (the cells containing the values 30 to 170). Click OK in the dialog boxes. The x‐axis data values will be replaced by mid‐ point values.

8. From the top right of the displayed chart, click the box with + sign. If the box is not displayed, click on the chart and the tab will appear.

Chapter 2 Descriptive Statistics: Graphical Methods 115

9. Check the Axes, Axis Title, and Chart Title. Type the axes titles and the chart title shown in Figure 2A.10.

The resulting polygons are shown in Figure 2A.10.

Figure2A.10: Frequency Polygons of Income Data

Scatter Plots Scatter plots are helpful in investigating the relationship between two variables. One of these variables is considered a dependent variable and the other an independent variable. The data value is thought of having an x value and a y value. Thus, we have (xi,yi), i=1,2,3,.....,n pairs. If we are interested in the relationship between the two variables, one of the easiest ways to

investigate this relationship is to plot the (x, y) pairs. This type of plot is known as a scatter plot. Several options are available for scatter plots. We have demonstrated one below. In the example given here, we have plotted the summer temperature and the amount of electricity used by customers (in millions of kilowatts). The data are shown in columns Y and Z of the data file Charts and Graphs.xlsx. Follow the steps in Table 2A.16 to do a scatter plot of the data.

Table 2A.16: Instructions for Constructing a Scatter Plot

1. Open the file App 2A‐Charts and Graphs.xlsx (Note that column Y contains the Summer Temperature and column Z contains the electricity usage in millions of kilowatts)

2. Select cells Y1:Z26 3. Click the Insert tab from the main menu. 4. In the Charts group, click on Scatter plot icon and select the first option. 5. A scatter plot will be displayed. Note that the x and y‐axis values are selected

by the program. We will change the axes values to make the chart more clear and readable. To change the x‐axis values, put the mouse pointer on any of the x‐axis value, right‐click the mouse button and select Format Axis. The Axis Options dialog box will be displayed on the right side. In the Minimum box type 70 (the minimum x‐value is close to 70) and type 110 for the Maximum value.

6. Repeat step 5 and type 20 and 32 for the minimum and maximum y‐values. 7. From the top right of the displayed chart, click the box with + sign. If the box is

not displayed, click on the chart and the tab will appear. 8. Check the Axes, Axis Title, and Chart Title. Type the axes titles and the chart title

0 2 4 6 8 10 12 14

30 50 70 90 110 130 150 170

Fr eq ue nc y

Mid‐point

Frequency Polygon

Freq.1 Freq.2

116 Statistics & Data Analysis Concepts for Business

shown in Figure 2A.11.

Your chart will look similar to the one shown in Figure 2A.11.

Figure 2A.11: Scatter Plot of Summer Temperature and Electricity Usage

Scatter Plot with Regression Line You can also plot a regression line over the scatter plot. To plot a regression line on the scatter plot, follow steps 1 through 8 in Table 2A.16. When the scatter plot is completed right‐ click on any of the plotted point on the graph. and

select Add Trend line. A trend line will be displayed on the plot and a Format Trend line dialog box will appear on the right pane. Scroll down and from the available options, check the boxes Display Equation on chart and Display R‐squared value on chart. You may move the equation to the other side of the graph.

Your chart will be similar to Figure 2A.12.

Figure 2A.12: Scatter Plot with Regression Line

Time Series Plot In a time series plot, data are plotted over time. The graph plots the (xi,yi) pairs of points and connects these points using straight lines where

the x values are time. The plot is helpful in visualizing a trend or pattern in a data set. The example below shows a time series plot of

20

22

24

26

28

30

32

70 75 80 85 90 95 100 105 110

K ill ow

at ts ( m ill io ns )

Summer Temperature

Electricity Used

y = 0.3134x ‐ 2.6304 R² = 0.8983

20

22

24

26

28

30

32

70 75 80 85 90 95 100 105 110

K il lo w at ts ( m ill io ns )

Summer Temperature

Electricity Used

118 Statistics & Data Analysis Concepts for Business

demand data over time. The data shows the demand for 65 weeks for a particular brand of a cell phone. The data are shown in columns AB and AC of the data file Charts and Graphs.xlsx.

Follow the steps in Table 2A.10: Instructions for Constructing a Line Chart to do a time series plot of the data. Your plot will look similar to Figure 2A.13.

Figure 2A.13: A Time Series Plot of Cell Phone Demand

A Multiple Time Series Plot Use this option to plot two or more series of data (multiple values). For example, suppose you want to plot the actual sales and the forecast on the same plot. The data are shown in columns AE, AF, and AG of the data file App 2A‐ Charts and Graphs.xlsx.

To do a multiple time‐series plot, follow the steps in Table 2A.10 – Instructions for Constructing a Line Chart. Make sure you select appropriate data columns (AF and AG). The rest of the steps are similar. You may want to edit the graph for clarity. Your plot will be similar to Figure 2A.14.

Figure 2A.14: A Multiple Time Series Plot

100

150

200

250

300

350

400

450

1 4 7 10131619222528313437404346495255586164

D em

an d

Week

Weekly Demand of a Cell Phone

35

45

55

65

75

85

95

1 4 7 10 13 16 19 22 25 28 31 34 37 40 43 46 49 52 55 58 61 64

Sa le s

Week

Sales and Forecast

Sales Forecast