Spreadsheet Models for Managers

Problem Set 6Session Links
Graphics

Be sure to check the list of worksheet functions that are needed for the homework assignments, to see which new functions (if any) might help with this assignment.

For a quick way to copy homework problem data into your homework solution, see “Avoid retyping homework problem data.”

Since macros aren’t permitted in this homework assignment, be certain that the workbook you submit for grading has no macros. Read about how to check your workbooks for macros.

Remember that some problems are slight extensions of what we show you in class, in the demonstrations and in the session notes, and some problems are somewhat ambiguous. This is intended to parallel what you’ll frequently encounter at work. If you feel a bit confused, there are some things you can do to help clarify things.

Numbers in square brackets to the right of the problem numbers indicate point values.

To complete this homework set, it will be helpful to look at the sections of Excel’s online help, or an equivalent reference, on charting.

Special note for Excel 2011 users

As explained in the readings (see Software You Need for This Course), this assignment cannot be solved using Excel 2011 (Mac), which does not yet implement all of Excel’s charting capabilities. Please solve these problems using another version of Excel.

 Problem 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. Each part is to be completed using its own chart. If you like, you can copy a chart you made for one part of the problem and use the copy for another, modifying it appropriately for that part of the problem.

 Problem 6.1 (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 the tools of the Insert tab of the Ribbon (Excel 2007, 2010, and 2013) to create a column chart on the worksheet.

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: either Times New Roman or Verdana.

Pacific Region 2015 2016 Washington $27,350 $35,105 Oregon $17,682 $20,632 California $49,741 $53,062 Alaska $8,523 $10,898

 Problem 6.1 (b) [5]

Make a copy of the chart you made in (a) and attach a text box to the graph. The comment in the text box should read “California Sales Estimated 2016.” This is a useful technique that comes in handy when you’re presenting data in chart form and you want to call attention to some specific feature.

 Problem 6.1 (c) [5]

Make a copy of the chart you made in (b) and add an arrow pointing from the text box you attached in part (b) to the 2016 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.

 Problem 6.1 (d) [5]

California sales data is late every year. Next year, you’ll have to edit the attached text to read “California Sales Estimated 2017.” To automate this maintenance step, you would like the attached text to be computed, so that every year it’s automatically updated and won’t 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.

You’ll save some time and trouble if you make a copy of the chart you made in part (c) and then modify it appropriately.

To test your solution, change the year headings for the data table to 2016 and 2017, recalculate if necessary, and observe that your attached chart comment now reads “California Sales Estimated 2017.” After you’ve verified this, restore the headings to 2015 and 2016, respectively.

This technique of computing text comments saves some time when you must 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.

 Problem 6.1 (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 2016 columns appear to the left of their respective 2015 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.

 Problem 6.1 (f) [5]

Make a copy of the chart you made in (e) and adjust the spacing between the columns for each state so that it’s about 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’s 10% of Oregon’s total width. Again, this gives you more control of visual impact.

 Problem 6.1 (g) [5]

On the worksheet that holds the data, define the names LastYear and ThisYear to refer to the column headings for the 2015 and 2016 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 Excel’s chart-making tools install automatically.

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

 Problem 6.1 (h) [10]

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

 Problem 6.1 (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’ll develop in solving this part of the problem is a standard method that applies even in situations where charts aren’t involved.

 Problem 6.2 [40]

 Problem 6.2 (a) [10]

The data for the Northern Tier Region are given below:

Northern Tier Region 2015 2016 Idaho $17,350 $15,105 Montana $27,893 $30,649 North Dakota $37,082 $43,745 Minnesota $16,541 $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).

 Problem 6.2 (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 we 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).

 Problem 6.2 (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.

Instructor Contact Information

Voice: (617) 491-6289
Toll-free: (866) 378-5470 in the continental US
Email: rbrenner@ChacoCanyon.com

Rick Brenner
Chaco Canyon Consulting
700 Huron Avenue, Suite 19C
Cambridge, MA 02138

Download vCard

Author Contact Information

Toll-free: (866) 378-5470 in the continental US

Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT