Excel Sales Forecasting help

div0 · 13 Oct 2010 at 09:31

I'm trying to help someone out with displaying their sales/forecasting information nicely on a month-by-month basis.

Currently their worksheet is laid out as follows:

Customer, Product, Jan Forecast, Feb Forecast, ..., Dec Forecast, Jan Sales, Feb Sales, ...., Dec Sales

They want an easy way to review the Sales/Forecasting for each customer/product on a month-by-month basis. Basically to compare how the actual sales compared to the forecast.

Possible solutions I've come up with mostly involve a macro of some kind, either:

1 - Manually rearrange the columns so that Jan Forecast/Sales are adjacent, then Feb Forecast/Sales, etc. Easy to do and probably sufficient for what they need.

2 - Add a drop-down list of months, and for any month selected, use a macro to hide the columns that don't apply.

3 - Write a macro to reformat the data so that columns are, Customer, Product, Month, Year, Forecast, Sales. Data manipulation using Pivot Tables etc is then much easier.

Are there any other options I've missed, any better solutions?

dazzerd · 13 Oct 2010 at 12:30

I maybe be being thick here but isn't it better to look at data sets in a visual form?

Why not just create a chart on another tab with a drop down box to choose which product or which consumer they want to look at? You could set up it up so they can compare a number of products or or customers if need be.

If not then just manually move the data.

mangobreeder · 13 Oct 2010 at 12:34

months across the top,

2 rows per client/product one for sales other for forecast.

the third row showing the difference

div0 · 13 Oct 2010 at 13:30

mangobreeder said:
months across the top,

2 rows per client/product one for sales other for forecast.

the third row showing the difference

I thought about doing this, but the problem is that the format they're using now is one they have decided on as a sort of 'standard format'.

So short of manipulating the data manually into the format you described, the only other option would be to write a macro, unless I'm missing something.

Ideally I'd like to be able to chart (or visually display) the 'report' that they're after, but based on the layout they're currently using.

So far I can't think of a good, easy way to do this.