Download Demo Working Model here.

Skills

  1. Formulas
  2. Formula autofill
  3. XY scatterplot charts
  4. Chart titles, axis titles, borders, format
  5. Copying Excel charts to Word
  6. Creating and using chart templates (2 methods)
  7. Captions and cross-references in Word
  8. VLOOKUP
  9. conditional formatting

Adding Snazz to Charts

This chart is fine, but ordinary:

CBNB-chart-cleanup.gif

This chart is not ordinary:

MNB-chart-all.gif

Workflow

  1. Download base file
  2. Add computed columns for NB, MNB
  3. Add spinners and parameters to select levels of implementation
  4. Add conditional formatting to show currently selected level of implementation
  5. Create multi-column XY scatterplot to show all data. Format with pastel colors, subtle markers.
  6. Nice-ify the formatting.
    1. How to put units on second line and change font size (shift-enter)
    2. Change marker size, shape, fill
    3. Add minor grid lines, change axes limits and subdivisions
    4. Change color of secondary gridlines
    5. Legend : no border, solid fill. Reorder categories via select data.
    6. Reformat data point. Marker. Line. Arrow.
    7. Adding balloons and graphics to charts for better explanatory diagrams
  7. Create "shadow" data that updates in response to selected levels of implementation (use NA() function)
  8. Add these data series.
    1. Format new data series with arrows
  9. Add functionality for "what should I invest in next" (identifying the highest MNB of current next available investments)
  10. Add second set of spinners and labels and such for UI

Details

Compute NB and MNB for each scale of each project
  • Insert formulas for net benefit and MNB for each project.
  • Create a spinner in H3 to control G3. This will be the current level of the police project. Center and enlarge text in G3.
cabmodel02.png
  • Repeat for each of the other projects.
Prepare for conditional formatting
  • To make the conditional formatting easier, we will create a "watcher" for each row in the table - a true/false value that is true when the current level of implementation of this project corresponds to this row. This formula just tests whether the implementation level for this row (the number in column B) is equal to the selected level (in $G$3 - note the absolute reference).
cabmodel03.png
  • Autofill this formula down the table and then repeat this for each of the other projects.
Dynamic conditional formatting
  • Next we will add the moveable conditional formatting. Select the "1" row of the police table. Then Conditional Formatting > Highlight Cell Rules > More Rules > Classic > Use a formula to determine…. And then for the formula we will click on G5 which contains the result of comparing the row's "level" with the current setting of the parameter in G3. Excel will insert $G$5 but we will change this because we want to copy this formatting down the table. Change $G$5 to $G5.
cabmodel04.png
cabmodel05.png
  • To copy this conditional formatting to the other cells in the table, copy the cells that are currently highlighted and then select the rest of the rows in the table and PASTE SPECIAL > FORMATS
cabmodel06.png
  • Repeat this for the other projects.

Create Background Chart
  • Next we create the background chart. Make a discontinuous selection of cells B3:B11,F3:F11 and then create a Scatter Chart > Straight Marked Scatter.
cabmodel07.png
  • Double click the series line in the chart and change line color and marker line to light blue, marker fill to white, marker style to circle size 7.
cabmodel08.png
  • Repeat this for the other projects.

Create "shadow" table that includes data only for the steps up to the current level of implementation
  • In columns I and J next to the police table we'll create copies of the cost and MNB data for levels of implementation AT or below the current level for this project (i.e., the value in G3).
  • We do this by saying "if the scale for this row (column B) is less than or equal to the current level (G3) then copy the cost (or MNB) for this row; otherwise just use Not Applicable (the Excel function NA()).
cabmodel10.png
cabmodel11.png
  • Repeat for other projects

Add chart traces for implementations
  • Add a data series to the chart for each of these "shadow tables" via Select Data > Add Series. Be sure to select all the levels of implementation for a given project, even if most of them are currently NA().
cabmodel12.png
  • Style the lines and markers in the same color, but darker, as for the background lines.
  • Under Format Data Series > Line > Weights and Arrows choose an arrow for the leading head of the chart line.
  • Repeat this for the other projects.

Build tool to scan "next" opportunities and identify the best one (highest MNB of next steps)
  • Off to the side let's create a little table with a row for each project. First column will be project name, second column is current implementation level, third column is the MNB we are looking at for the next step.
  • Use simple formula to insert project name and current level.
  • Then use vlookup to find the MNB that corresponds to the current level (see below).
  • After all these are in the table, use the max() function to identify the highest MNB.
  • Then write a cumbersome compound if statement to find the name of the project that has this highest MNB.
cabmodel09.png
  • Repeat this for the other projects.

Build the rest of the user interface (UI)
  • Add labels for budget, budget remaining, total expenditure, total benefit, etc.
  • Add a spinner to allow for changing the budget (by what increment? probably not 1)
  • Move the spinners from next to the tables in column H to the UI area above the chart. Put labels and cells containing current values nearby in an informative manner.
  • In a prominent place on the UI put a cell that references the next best MNB from earlier step (by a formula reference to AA14 or where ever you had that info.
  • Tweak and tidy on your own initiative
cabmodel13.png

Resources

Microsoft help for Excel 2010 Charts
Microsoft help for Excel 2007 Charts