6.1 [60] 

For each of the parts of this problem, make one chart. Create all charts on a single worksheet, rather than as pages in the workbook. Do not use the same chart for more than one part. If you like, you can copy the chart you used for part (a) repeatedly, modifying it appropriately for each part of the problem.

(a) [5] 

You're preparing a presentation for the VP/Sales of The New England Jack-O-Lantern Cooperative (NEJOLC). As part of the presentation, you must make a column chart of the data below. Do this by entering the data into a range of a worksheet. Then use the chart wizard to create a column chart on a chart sheet.

It's good design practice to choose a font for the chart that matches the font you're using in the document in which you expect to use the chart. This avoids the "ransom note" effect that comes from using too many different fonts. So practice this — choose a font for the chart that differs from the default, such as Times New Roman or Verdana.

Pacific Region 2008 2009 Washington $27,350 $35,205 Oregon $17,882 $20,950 California $49,781 $53,842 Alaska $8,561 $10,898

(b) [5] 

Attach a text box to the graph. The comment in the text box should read "California Sales Estimated 2009." This is a useful technique that comes in handy when you're presenting data in chart form and want to call attention to some specific feature.

(c) [5] 

Add an arrow pointing from the text box you attached in part (b) to the 2009 California column. Use arrows like this when the notes you attach to charts need a little extra assist to make clear what features they're talking about.

(d) [5] 

California sales data is late every year. Next year, you'll have to edit the attached text to read "California Sales Estimated 2010." To automate this maintenance step, you would like the attached text to be computed, so that every year it's automatically updated and will not require manual editing. Figure out how to do this using only one text box. Hint: perform the necessary computations in a cell or cells on the worksheet, rather than in the chart itself.

To test your solution, change the year headings for the data table to 2009 and 2010, and observe that your attached chart comment now reads "California Sales Estimated 2010." After you've verified this, change them back.

This technique saves some time when you have to update the chart, but its real value is that it enhances reliability. How often have you forgotten to update the chart note in situations like this? This is an especially handy method when the chart must be reissued periodically, but more frequently than annually — say, weekly or monthly.

(e) [10] 

You receive a request to change the appearance of the chart in (d) without changing the data table that drives it. Make a new chart just like (d) except that the 2009 columns appear to the left of their respective 2008 columns. Remember that an easy way to do this is to copy the chart and modify the copy.

It's good to know how to control this sort of thing. The visual impact of the data is important, and controlling the order of the time series is a way to control the data's visual impact.

(f) [5] 

Adjust the spacing between the columns for each state in the chart you made for (e) so that it's 10% of the width of a category. For example, in the category "Oregon," you have two columns — one for each year. Make a space between those columns that is 10% of Oregon's total width. Again, more control of visual impact.

(g) [5] 

On the worksheet that holds the data, define the names LastYear and ThisYear to refer to the column headings for the 2008 and 2009 data, respectively. Define names LastYearData and ThisYearData to refer to the data in those two columns, respectively. Define the name States to refer to the column of state names. Make a copy of the chart in (f), and modify the copy so that it uses these names rather than the absolute references that the Chart Wizard installs automatically.

Using names is an important technique for improving readability, improving reliability and reducing costs. Too bad the Chart Wizard isn't smart enough to do this on its own. It should. Also too bad that the commands Insert>Names>Apply… (Excel 2003 and 2004), and Formulas>Use in Formula (Excel 2007) don't work for charts. This gap is plugged by the command Apply Names to Chart Objects in the special tools for this course.

(h) [10] 

Make a pie chart for the Pacific Region data for 2008.

(i) [10] 

NEJOLC's VP of Sales isn't happy with the pie chart. For aesthetic reasons, the VP wants the order of the pie slices, clockwise from midnight, to be Washington, Oregon, Alaska, California. It's also necessary to preserve the order of the tabular data. So the problem is this: the order of the pie slices has to be different from the order of the table, but you still have to adhere to the Ripple Principle.

In other words, the order of the tabular data as indicated above is different from the order required for the pie slices. But the VP also insists that you not make a simple copy of the table, with the data reordered — you're to construct a worksheet mechanism that meets both ordering requirements, while preserving the "live updating" capability (the Ripple Principle). That is, changing the data in the table you constructed in (a) should cause changes in both the pie chart of part (h) and the pie chart you're about to make now.

This situation is common when you don't control the data source — you receive the data from someone else or from another department and you don't control its layout, but you need that layout to be different from the form it's in. The technique you develop in solving this part of the problem is a standard method that applies even in situations where charts aren't involved.

(a) [10] 

The data for the Northern Tier Region are given below:

Northern Tier Region 2008 2009 Idaho $17,350 $15,205 Montana $27,693 $30,959 North Dakota $39,782 $43,845 Minnesota $18,551 $19,893

Create a new worksheet with a range that contains this data. Assign names to the analogous portions of that range in the same way that you did in Problem 6.1 (g).

(b) [20] 

Make a plot of the data for all eight states, exactly like you did for the four states in Problem 6.1 (a), except: the order of the states in this chart should be Washington, Oregon, California, Alaska, Idaho, Montana, North Dakota, Minnesota. Your plot should be constructed according to the Ripple Principle. If I change any of the numbers in the data tables in your answers to part 6.1 (a) or 6.2 (a), the effects of that change should ripple forward into your plot. That is, your plot should depend ultimately (if not directly) on the data tables in your answers to 6.1 (a) and 6.2 (a).

(c) [10] 

Attach a text box to this chart that reads:

Your text box should be left justified, with line breaks exactly as indicated above.