Bar Charts to Show Actual v/s Forecast in Single Bar

[karthikgoel]

Hi I have source data in the below file

Month Actual/ Forecasted Revenue Contract value
Apr-14 Actual 0.02 0.01
Apr-14 Forecasted 0.02 0.022
May-14 Actual 0 0
May-14 Forecasted 0.02 0.04
Jun-14 Actual 0 0
Jun-14 Forecasted 0.03 0.039

I would like to show the bar chart with the Bar representation such that the KPI "Revenue" should have one single bar for each month and portion of that should show how much is actual in different color and how much is forecasted for the same month in different color code on the same bar. Need to do the same with "Contract value" however when I try to generate Bar chart it shows me separate Actual and Forecast bars for each month for the same metric. It would pose interpretation challenges for anybody.

Hope you have understood my problem, Looking forward to your responses

 

[NARAYANK991]

Hi Karthik ,

If you want the following :

1. One stacked bar showing the Actual Contract value and the Expected Contract value for each month

2. Beside the above , another stacked bar showing the Actual Revenue and the Expected Revenue for each month

then what you are talking about is a combination stacked and clustered bar chart.

There are several good posts on the Internet explaining how to achieve this :

http://peltiertech.com/WordPress/clustered-stacked-column-bar-charts/

http://alesandrab.wordpress.com/2013/01/28/create-combination-stacked-clustered-charts-in-excel/

Narayan

 

[Debraj]

I would change one type to Negative, and another to Positive..
and then accordingly I will create a normal Bar Chart.. with Overlapped..



It will give you both direction comparison and with Actual Forecast also.

 

[NARAYANK991]

Hi Deb ,

Your chart will not show the Forecast , if the Actual is bigger.

Put in some tranparency.

Narayan

 

[Debraj]

Hi Narayan!

I thought.. If Actual is higher than, no need to display forecast, as it was crossed..

But, to use the same.. we have to use another image.. something like this..



@karthikgoel

Checkt he attached for more detail..