Microsoft Office Tutorials and References
In Depth Information
Showing a Trend of Monthly Sales and Year-to-Date Sales
To add a trendline, follow these steps:
1. Right-click the series line for the Actual column. Select Add Trendline.
2. The Format Trendline dialog offers to add exponential, linear, logarithmic, polynomial,
power, or moving average trendlines. Select a linear trendline.
3. In the Trendline Name section, either leave the name as Linear (Actual) or enter a
custom name such as Forecast.
4. When forecasting forward or backward for a certain number of periods, leave both
of those settings at 0 because this chart already has data points for the entire month.
There are also settings where Excel shows the regression equation on the chart. Add
this if you desire.
5. Right-click the trendline to select it. On the Format tab, select Shape Outline, Dashes
and then select the fourth dash option. Also, select Shape Outline, Weight, 3/4 point.
The trendline is shown at the bottom of Figure 3.31. In this particular case, the trendline
extrapolates that if I continue writing at the normal pace, I will miss the deadline by 15
pages or so.
Excel’s trendline is not an intelligent forecasting system. It merely fits past points to a straight line and
extrapolates that data. It works great as a motivational tool. For example, the current example shows
that it would take a few days of above-average production before the trendline would project that the
goal would be met.
Showing a Trend of Monthly Sales and Year-to-Date Sales
In accounting, sales are generally tracked every month. However, in the big picture you are
interested in how 12 months add up to produce annual sales.
The top chart in Figure 3.32 is a poor attempt to show both monthly sales and accumulated
year-to-date (YTD) sales. The darker bars are the monthly results, while the lighter bars
are the accumulated YTD numbers through the current month. To show the large YTD
number for November, the scale of the axis needs to extend to $400,000. However, this
makes the individual monthly bars far too small for the reader to be able to discern any
The solution is to plot the YTD numbers against a secondary vertical axis. My preference is
that after you change the axis for one series, you should also change the chart type for that
series. Follow these steps to create the bottom chart in Figure 3.32:
1. Left-click one of the YTD bars to select the YTD series. Right-click the selected series
and select Format Data Series. Excel displays the Format Data Series dialog.
Search JabSto ::

Custom Search