Business Tools Blog

Net Installs are tracked and forecasted on a weekly basis.  We use historical net installs as a rear view mirror and a forecast, to tell us where we are going.

We use a stacked bar to depict net installs.  The top of the bar = Net Installs.  Notice that when Net Installs >$0, the blue bar is split into 2.  The bottom blue bar = the disconnect bar, the other blue bar = the amount that Net Installs is >$0.

Here’s how you build the chart:

1.  Start by setting up the data sheet:

• In column B, create boxes and color them.  These will be the legend for your chart
• You can use the EOMONTH command to create the dates in row 17

2.  Create the graph data (in gray below).  The graph requires 4 bars.

• Blank bar -  Formula =IF(D20<0,D20,”") If the Net Installs<0, then this bar = Net Installs. If Net Installs>0, then this bar has no value
• Gross Install1 bar - Formula=IF(D23<0,”",D20) If Blank<0, then this bar has no value, if Blank>0, then this bar=Net Installs.
• Gross Install2 bar - Formula=IF(D23<0,D18,D19) If Blank<0, then this bar = -Gross Installs, if BLANK>0, then this bar=Disconnects
• Disconnects bar - Formula=+D19.  This bar=Disconnects

3. Create the chart

• INSERT >> CHART >> Column >> Stacked Column

• Select the gray area as the range for the chart

• Create the Title

• On the Legend tab, uncheck the Show Legend box
• Click Finish

Your Chart looks like this:

4. Format Chart Area

• Remove the chart outline
• Double click on the line on the outside of the chart
• Select Border=None and Area=None
• Click OK

5.  Format Plot area

• Remove the gray shading in the plot area
• Double click on outline of the plot area
• Select Border=None and Area=None
• Click OK

6.  Format the Y axis, so that it displays currency

• Double click on the Y axis
• On the Number tab, click Currency
• Decimal places=0
• Symbol=$
• Negative numbers=($1,234)

7.  Format the X Axis

• Double click on the X axis
• On the patterns tab, Tick mark labels=Low

8.  Color the bars

• Double click on the Disconnect bar
• Change the color to red
• Click OK

• Double Click on the Gross Install1 and Gross Install2 Bars and make them blue (you will have to do this separately
• Click OK

• Double click on the Blank bar
• On the Patterns tab, select Border=None and Area=None

Your chart now looks like this:

Go to Businesstoolsblog Shared Files Google Group for a free template.