Guest Author - Chris Curtis
In business, it's important to be able to track progress toward achieving your goals. We use charts to visually display the actual results of our progress. You can make a better analysis when you compare the actual results to the goals that have been set whether it be in production or in financial results. Managers are often called upon to report on how well financial indicators are measuring up to the budget for the year or to measure actual production against projected production. You can make a greater impact if your presentation displayed a chart that shows actual results against the goals that were set.
When you use Excel to Chart data, you typically start by selecting the data points to be charted. To establish a goal line you must also have a goal data point for each data point in your results data. In a column adjacent to your actual data points define a goal data point for each indicator in the dataset.
For example suppose you are reporting on monthly revenues
A B C
Month Actual Budgeted
January $1000 $1200
February $1500 $1200
March $1200 $1200
April $1800 $1200
May $1300 $1500
June $1600 $1500
Once you have established the goal data on your worksheet you would proceed as follows:
• Select the data including the row labels, column labels, results data points, and goal data points. In our example you would select the entire block of data as shown above.
• Press the F11 key to create your bar chart. The F11 Key is a shortcut method to create a simple bar chart without having to go through the steps of the chart wizard
• Right click one of the chart bars representing the Goal series and select Chart Type from the shortcut menu.
• From the Chart Type dialog box, choose a line type chart, accept the default style, and click on OK.
Your chart will now display the results in bar chart form with a goal line. If you want to emphasize the goal line, you can format it by right clicking on the goal line and choose “Format Data Series”. A red, 6pt. line style makes the goal line stand out.