|
Compare Sales Forecast with Actual
I want to generate two superimposed line charts to represent, during a
particular month: a) forecast sales (accumulated) b) actual sales as they are taken (accumulated) I want to be able to directly compare the progress of actual sales with the forecast, and a prediction of month end sales. The forecast sales values and expected dates are entered at the start of the month. The actual sales are entered daily by checking a separate resource and entering sales values and corresponding dates. Multiple sales values may be forecast and taken on the same date. By the end of the month, in an ideal scenario, all the forecast sales values will have been entered in the actual column at the forecast date and the actual total will match the forecast total. In practice, however, the actual dates may vary from the forecast dates, and perhaps not all the sales will have been taken. The shortfall should be obvious from the chart. This sounds to me like two separate charts, because although the range of dates (month start to month end) is the same for forecast and actual, I can't use a single column date range for both. Any advice please? I'm sure this must have already been done, but I've looked at online templates and not found anything suitable. I'm using Excel 2003. -- Richard Search the web and raise money for charity at www.everyclick.com |
Compare Sales Forecast with Actual
Rather than two separate charts, use one chart, of type XY, with two
separate sets of XY data. Make the chart with one set of XY data, then copy the other set, select the chart, and use Paste Special to add the data as a new series, with category data in the first column (or row). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... I want to generate two superimposed line charts to represent, during a particular month: a) forecast sales (accumulated) b) actual sales as they are taken (accumulated) I want to be able to directly compare the progress of actual sales with the forecast, and a prediction of month end sales. The forecast sales values and expected dates are entered at the start of the month. The actual sales are entered daily by checking a separate resource and entering sales values and corresponding dates. Multiple sales values may be forecast and taken on the same date. By the end of the month, in an ideal scenario, all the forecast sales values will have been entered in the actual column at the forecast date and the actual total will match the forecast total. In practice, however, the actual dates may vary from the forecast dates, and perhaps not all the sales will have been taken. The shortfall should be obvious from the chart. This sounds to me like two separate charts, because although the range of dates (month start to month end) is the same for forecast and actual, I can't use a single column date range for both. Any advice please? I'm sure this must have already been done, but I've looked at online templates and not found anything suitable. I'm using Excel 2003. -- Richard Search the web and raise money for charity at www.everyclick.com |
Compare Sales Forecast with Actual
Jon
I tried your suggestion, but the new series seems to use the X range of the pre-existing series. -- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: Rather than two separate charts, use one chart, of type XY, with two separate sets of XY data. Make the chart with one set of XY data, then copy the other set, select the chart, and use Paste Special to add the data as a new series, with category data in the first column (or row). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... I want to generate two superimposed line charts to represent, during a particular month: a) forecast sales (accumulated) b) actual sales as they are taken (accumulated) I want to be able to directly compare the progress of actual sales with the forecast, and a prediction of month end sales. The forecast sales values and expected dates are entered at the start of the month. The actual sales are entered daily by checking a separate resource and entering sales values and corresponding dates. Multiple sales values may be forecast and taken on the same date. By the end of the month, in an ideal scenario, all the forecast sales values will have been entered in the actual column at the forecast date and the actual total will match the forecast total. In practice, however, the actual dates may vary from the forecast dates, and perhaps not all the sales will have been taken. The shortfall should be obvious from the chart. This sounds to me like two separate charts, because although the range of dates (month start to month end) is the same for forecast and actual, I can't use a single column date range for both. Any advice please? I'm sure this must have already been done, but I've looked at online templates and not found anything suitable. I'm using Excel 2003. -- Richard Search the web and raise money for charity at www.everyclick.com |
Compare Sales Forecast with Actual
What Jon said was:
"... add the data as a new series, with category data in the first column (or row)." The last phrase is significant. -- David Biddulph "RichUE" wrote in message ... Jon I tried your suggestion, but the new series seems to use the X range of the pre-existing series. -- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: Rather than two separate charts, use one chart, of type XY, with two separate sets of XY data. Make the chart with one set of XY data, then copy the other set, select the chart, and use Paste Special to add the data as a new series, with category data in the first column (or row). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... I want to generate two superimposed line charts to represent, during a particular month: a) forecast sales (accumulated) b) actual sales as they are taken (accumulated) I want to be able to directly compare the progress of actual sales with the forecast, and a prediction of month end sales. The forecast sales values and expected dates are entered at the start of the month. The actual sales are entered daily by checking a separate resource and entering sales values and corresponding dates. Multiple sales values may be forecast and taken on the same date. By the end of the month, in an ideal scenario, all the forecast sales values will have been entered in the actual column at the forecast date and the actual total will match the forecast total. In practice, however, the actual dates may vary from the forecast dates, and perhaps not all the sales will have been taken. The shortfall should be obvious from the chart. This sounds to me like two separate charts, because although the range of dates (month start to month end) is the same for forecast and actual, I can't use a single column date range for both. Any advice please? I'm sure this must have already been done, but I've looked at online templates and not found anything suitable. I'm using Excel 2003. -- Richard Search the web and raise money for charity at www.everyclick.com |
Compare Sales Forecast with Actual
If you make an XY chart, not a line chart, this will not happen.
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... Jon I tried your suggestion, but the new series seems to use the X range of the pre-existing series. -- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: Rather than two separate charts, use one chart, of type XY, with two separate sets of XY data. Make the chart with one set of XY data, then copy the other set, select the chart, and use Paste Special to add the data as a new series, with category data in the first column (or row). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... I want to generate two superimposed line charts to represent, during a particular month: a) forecast sales (accumulated) b) actual sales as they are taken (accumulated) I want to be able to directly compare the progress of actual sales with the forecast, and a prediction of month end sales. The forecast sales values and expected dates are entered at the start of the month. The actual sales are entered daily by checking a separate resource and entering sales values and corresponding dates. Multiple sales values may be forecast and taken on the same date. By the end of the month, in an ideal scenario, all the forecast sales values will have been entered in the actual column at the forecast date and the actual total will match the forecast total. In practice, however, the actual dates may vary from the forecast dates, and perhaps not all the sales will have been taken. The shortfall should be obvious from the chart. This sounds to me like two separate charts, because although the range of dates (month start to month end) is the same for forecast and actual, I can't use a single column date range for both. Any advice please? I'm sure this must have already been done, but I've looked at online templates and not found anything suitable. I'm using Excel 2003. -- Richard Search the web and raise money for charity at www.everyclick.com |
Compare Sales Forecast with Actual
True, but if the initial chart was a line chart, the added series will still
assume the original series' X values. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... What Jon said was: "... add the data as a new series, with category data in the first column (or row)." The last phrase is significant. -- David Biddulph "RichUE" wrote in message ... Jon I tried your suggestion, but the new series seems to use the X range of the pre-existing series. -- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: Rather than two separate charts, use one chart, of type XY, with two separate sets of XY data. Make the chart with one set of XY data, then copy the other set, select the chart, and use Paste Special to add the data as a new series, with category data in the first column (or row). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... I want to generate two superimposed line charts to represent, during a particular month: a) forecast sales (accumulated) b) actual sales as they are taken (accumulated) I want to be able to directly compare the progress of actual sales with the forecast, and a prediction of month end sales. The forecast sales values and expected dates are entered at the start of the month. The actual sales are entered daily by checking a separate resource and entering sales values and corresponding dates. Multiple sales values may be forecast and taken on the same date. By the end of the month, in an ideal scenario, all the forecast sales values will have been entered in the actual column at the forecast date and the actual total will match the forecast total. In practice, however, the actual dates may vary from the forecast dates, and perhaps not all the sales will have been taken. The shortfall should be obvious from the chart. This sounds to me like two separate charts, because although the range of dates (month start to month end) is the same for forecast and actual, I can't use a single column date range for both. Any advice please? I'm sure this must have already been done, but I've looked at online templates and not found anything suitable. I'm using Excel 2003. -- Richard Search the web and raise money for charity at www.everyclick.com |
Compare Sales Forecast with Actual
I changed the Chart Type to XY so now I can compare two XY plots - thanks.
I'm just puzzled that the points on each XY plot are not joined up. I used the 'joined up' chart version. Specifically, two of the forecast series are joined (3 not), and three of the actual series (3 not). -- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: If you make an XY chart, not a line chart, this will not happen. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... Jon I tried your suggestion, but the new series seems to use the X range of the pre-existing series. -- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: Rather than two separate charts, use one chart, of type XY, with two separate sets of XY data. Make the chart with one set of XY data, then copy the other set, select the chart, and use Paste Special to add the data as a new series, with category data in the first column (or row). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... I want to generate two superimposed line charts to represent, during a particular month: a) forecast sales (accumulated) b) actual sales as they are taken (accumulated) I want to be able to directly compare the progress of actual sales with the forecast, and a prediction of month end sales. The forecast sales values and expected dates are entered at the start of the month. The actual sales are entered daily by checking a separate resource and entering sales values and corresponding dates. Multiple sales values may be forecast and taken on the same date. By the end of the month, in an ideal scenario, all the forecast sales values will have been entered in the actual column at the forecast date and the actual total will match the forecast total. In practice, however, the actual dates may vary from the forecast dates, and perhaps not all the sales will have been taken. The shortfall should be obvious from the chart. This sounds to me like two separate charts, because although the range of dates (month start to month end) is the same for forecast and actual, I can't use a single column date range for both. Any advice please? I'm sure this must have already been done, but I've looked at online templates and not found anything suitable. I'm using Excel 2003. -- Richard Search the web and raise money for charity at www.everyclick.com |
Compare Sales Forecast with Actual
You mean the markers are not connected by lines? If the cells between the
data points are actual blank cells (not "" returned by a formula), then go to Tools menu Options Chart tab, and choose the Interpolate option for dealing with blank cells. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. 774-275-0064 208-485-0691 fax http://PeltierTech.com/ _______ "RichUE" wrote in message ... I changed the Chart Type to XY so now I can compare two XY plots - thanks. I'm just puzzled that the points on each XY plot are not joined up. I used the 'joined up' chart version. Specifically, two of the forecast series are joined (3 not), and three of the actual series (3 not). -- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: If you make an XY chart, not a line chart, this will not happen. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... Jon I tried your suggestion, but the new series seems to use the X range of the pre-existing series. -- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: Rather than two separate charts, use one chart, of type XY, with two separate sets of XY data. Make the chart with one set of XY data, then copy the other set, select the chart, and use Paste Special to add the data as a new series, with category data in the first column (or row). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... I want to generate two superimposed line charts to represent, during a particular month: a) forecast sales (accumulated) b) actual sales as they are taken (accumulated) I want to be able to directly compare the progress of actual sales with the forecast, and a prediction of month end sales. The forecast sales values and expected dates are entered at the start of the month. The actual sales are entered daily by checking a separate resource and entering sales values and corresponding dates. Multiple sales values may be forecast and taken on the same date. By the end of the month, in an ideal scenario, all the forecast sales values will have been entered in the actual column at the forecast date and the actual total will match the forecast total. In practice, however, the actual dates may vary from the forecast dates, and perhaps not all the sales will have been taken. The shortfall should be obvious from the chart. This sounds to me like two separate charts, because although the range of dates (month start to month end) is the same for forecast and actual, I can't use a single column date range for both. Any advice please? I'm sure this must have already been done, but I've looked at online templates and not found anything suitable. I'm using Excel 2003. -- Richard Search the web and raise money for charity at www.everyclick.com |
Compare Sales Forecast with Actual
Thanks, Jon. All the markers are connected now.
-- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: You mean the markers are not connected by lines? If the cells between the data points are actual blank cells (not "" returned by a formula), then go to Tools menu Options Chart tab, and choose the Interpolate option for dealing with blank cells. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. 774-275-0064 208-485-0691 fax http://PeltierTech.com/ _______ "RichUE" wrote in message ... I changed the Chart Type to XY so now I can compare two XY plots - thanks. I'm just puzzled that the points on each XY plot are not joined up. I used the 'joined up' chart version. Specifically, two of the forecast series are joined (3 not), and three of the actual series (3 not). -- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: If you make an XY chart, not a line chart, this will not happen. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... Jon I tried your suggestion, but the new series seems to use the X range of the pre-existing series. -- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: Rather than two separate charts, use one chart, of type XY, with two separate sets of XY data. Make the chart with one set of XY data, then copy the other set, select the chart, and use Paste Special to add the data as a new series, with category data in the first column (or row). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... I want to generate two superimposed line charts to represent, during a particular month: a) forecast sales (accumulated) b) actual sales as they are taken (accumulated) I want to be able to directly compare the progress of actual sales with the forecast, and a prediction of month end sales. The forecast sales values and expected dates are entered at the start of the month. The actual sales are entered daily by checking a separate resource and entering sales values and corresponding dates. Multiple sales values may be forecast and taken on the same date. By the end of the month, in an ideal scenario, all the forecast sales values will have been entered in the actual column at the forecast date and the actual total will match the forecast total. In practice, however, the actual dates may vary from the forecast dates, and perhaps not all the sales will have been taken. The shortfall should be obvious from the chart. This sounds to me like two separate charts, because although the range of dates (month start to month end) is the same for forecast and actual, I can't use a single column date range for both. Any advice please? I'm sure this must have already been done, but I've looked at online templates and not found anything suitable. I'm using Excel 2003. -- Richard Search the web and raise money for charity at www.everyclick.com |
Compare Sales Forecast with Actual
Now that I have a working chart (thanks to the forum members), am I 'stuck'
with an XY scatter chart to superimpose two different X-Y relationships? Is it possible to change one of the scatter charts to a column style? When I've tried this for the Actual chart, the columns use the X parameters from the Forecast chart instead. Thius gives wrong information because the Actual sales values are taken on different dates from Forecast sales. -- Richard Search the web and raise money for charity at www.everyclick.com "RichUE" wrote: Thanks, Jon. All the markers are connected now. -- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: You mean the markers are not connected by lines? If the cells between the data points are actual blank cells (not "" returned by a formula), then go to Tools menu Options Chart tab, and choose the Interpolate option for dealing with blank cells. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. 774-275-0064 208-485-0691 fax http://PeltierTech.com/ _______ "RichUE" wrote in message ... I changed the Chart Type to XY so now I can compare two XY plots - thanks. I'm just puzzled that the points on each XY plot are not joined up. I used the 'joined up' chart version. Specifically, two of the forecast series are joined (3 not), and three of the actual series (3 not). -- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: If you make an XY chart, not a line chart, this will not happen. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... Jon I tried your suggestion, but the new series seems to use the X range of the pre-existing series. -- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: Rather than two separate charts, use one chart, of type XY, with two separate sets of XY data. Make the chart with one set of XY data, then copy the other set, select the chart, and use Paste Special to add the data as a new series, with category data in the first column (or row). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... I want to generate two superimposed line charts to represent, during a particular month: a) forecast sales (accumulated) b) actual sales as they are taken (accumulated) I want to be able to directly compare the progress of actual sales with the forecast, and a prediction of month end sales. The forecast sales values and expected dates are entered at the start of the month. The actual sales are entered daily by checking a separate resource and entering sales values and corresponding dates. Multiple sales values may be forecast and taken on the same date. By the end of the month, in an ideal scenario, all the forecast sales values will have been entered in the actual column at the forecast date and the actual total will match the forecast total. In practice, however, the actual dates may vary from the forecast dates, and perhaps not all the sales will have been taken. The shortfall should be obvious from the chart. This sounds to me like two separate charts, because although the range of dates (month start to month end) is the same for forecast and actual, I can't use a single column date range for both. Any advice please? I'm sure this must have already been done, but I've looked at online templates and not found anything suitable. I'm using Excel 2003. -- Richard Search the web and raise money for charity at www.everyclick.com |
Compare Sales Forecast with Actual
If you're showing a relationship between variables, the column chart type is
probably not a great choice. If your problem is the starting point of chart series, well, XY series don't care, but line or column series need you to space out the later-starting series with blanks. It's illustrated in this post: http://peltiertech.com/WordPress/200...area-charts-1/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... Now that I have a working chart (thanks to the forum members), am I 'stuck' with an XY scatter chart to superimpose two different X-Y relationships? Is it possible to change one of the scatter charts to a column style? When I've tried this for the Actual chart, the columns use the X parameters from the Forecast chart instead. Thius gives wrong information because the Actual sales values are taken on different dates from Forecast sales. -- Richard Search the web and raise money for charity at www.everyclick.com "RichUE" wrote: Thanks, Jon. All the markers are connected now. -- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: You mean the markers are not connected by lines? If the cells between the data points are actual blank cells (not "" returned by a formula), then go to Tools menu Options Chart tab, and choose the Interpolate option for dealing with blank cells. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. 774-275-0064 208-485-0691 fax http://PeltierTech.com/ _______ "RichUE" wrote in message ... I changed the Chart Type to XY so now I can compare two XY plots - thanks. I'm just puzzled that the points on each XY plot are not joined up. I used the 'joined up' chart version. Specifically, two of the forecast series are joined (3 not), and three of the actual series (3 not). -- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: If you make an XY chart, not a line chart, this will not happen. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... Jon I tried your suggestion, but the new series seems to use the X range of the pre-existing series. -- Richard Search the web and raise money for charity at www.everyclick.com "Jon Peltier" wrote: Rather than two separate charts, use one chart, of type XY, with two separate sets of XY data. Make the chart with one set of XY data, then copy the other set, select the chart, and use Paste Special to add the data as a new series, with category data in the first column (or row). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RichUE" wrote in message ... I want to generate two superimposed line charts to represent, during a particular month: a) forecast sales (accumulated) b) actual sales as they are taken (accumulated) I want to be able to directly compare the progress of actual sales with the forecast, and a prediction of month end sales. The forecast sales values and expected dates are entered at the start of the month. The actual sales are entered daily by checking a separate resource and entering sales values and corresponding dates. Multiple sales values may be forecast and taken on the same date. By the end of the month, in an ideal scenario, all the forecast sales values will have been entered in the actual column at the forecast date and the actual total will match the forecast total. In practice, however, the actual dates may vary from the forecast dates, and perhaps not all the sales will have been taken. The shortfall should be obvious from the chart. This sounds to me like two separate charts, because although the range of dates (month start to month end) is the same for forecast and actual, I can't use a single column date range for both. Any advice please? I'm sure this must have already been done, but I've looked at online templates and not found anything suitable. I'm using Excel 2003. -- Richard Search the web and raise money for charity at www.everyclick.com |
| All times are GMT +1. The time now is 01:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com