Spreadsheet Models for Managers

Getting Access to Spreadsheet Models for Managers

If Spreadsheet Models for Managersyou use Excel to model businesses, business processes, or business transactions, this course will change your life. You’ll learn how to create tools for yourself that will amaze even you. Unrestricted use of this material is available in two ways.

As a stand-alone Web site
It resides on your computer, and you can use it anywhere. No need for Internet access.
At this Web site
If you have access to the Internet whenever you want to view this material, you can purchase on-line access. Unlimited usage. I’m constantly making improvements and you’ll get them as soon as they’re available.

To Order On Line

Order "Spreadsheet Models for Managers, on-line edition, one month" by credit card, for USD 69.95 each, using our secure server, and receive download instructions by return email.
Order "Spreadsheet Models for Managers, on-line edition, three months" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email.
Order "Spreadsheet Models for Managers, downloadable hyperbook edition" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email.

To Order by Mail

Make your check payable to Chaco Canyon Consulting, for the amount indicated:
  • For the download: USD 199.00
  • For access online for three months: USD 199.00
  • For access online for one month: USD 69.95
And send it to:
Chaco Canyon Consulting
700 Huron Avenue, Suite 19C
Cambridge, MA 02138

To use the course software you’ll need some other applications, which you very probably already have. By placing your order, you’re confirming that you have the software you need, as described on this site.

Spreadsheet Models for Managers

Problem Set 6Session Links

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.

Blue square 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.

Gray square Problem 6.1 (a) [5]

Jack-O-LanternYou’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.

Retrieve homework data in 2007+ format
Pacific Region 2015 2016
Washington $27,350$35,105
Oregon $17,682$20,632
California $49,741$53,062
Alaska $8,523$10,898
Gray square 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.

Gray square 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.

Gray square Problem 6.1 (d) [5]

CaliforniaCalifornia 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.

Gray square 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.

Gray square 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.

Gray square 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.

Gray square Problem 6.1 (h) [10]

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

Gray square Problem 6.1 (i) [10]

Anger at workNEJOLC’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.

Blue square Problem 6.2 [40]
Gray square Problem 6.2 (a) [10]

The data for the Northern Tier Region are given below:

Retrieve homework data in 2007+ format
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).

Gray square 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).

Gray square Problem 6.2 (c) [10]

Attach a text box to this chart that reads:

Note: California data for
2016 is estimated. Montana data
for 2016 excludes the Missoula
region pending resolution of audit

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

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

About Charts

The purpose of charts is visual communication. Sadly, many charts that contain important information don’t communicate that information very well. Communicating visually in an effective manner is probably an art, but it’s an art we can learn.

One way to learn is by bad examples — actually studying examples of bad technique. Fortunately, bad examples are easy to find. Finding good examples is more difficult, but there are several excellent references in the class notes. Enjoy!