• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

graph to visualize annual budget goal vs actual progressively

K

Kingerick

New Member
hello,

i'm looking for help...i've been trying to figure out a way to visually represent an annual expense budget by month (and how much is remaining). i have a fixed annual budget but it's broken down by month based on the trended growth/reduction each month. example $10k budget for year, jan is $1,200, feb is $800, mar is $1,000 and so forth. I would like to show on a single graph the overall progress through the year and detail how each month compares to the monthly goal with a allowable overage up to 5%. example, jan budget is $1,200 but we went over by $100, it should just show over but if we went over by $500, then it should show as red (or some way of drawing attention). and if possible, the other direction should draw attention too, if we beat the goal by $500. one more thing, this needs to be done for 2 areas. area 1 has a budget and area 2 has a budget.


i tried searching for ideas but i couldn't find anything that seemed to fit my needs. i looked at the article "Best Charts to Compare Actual Values with Targets – What is your take?" but none of those seemed to be the right thing. thermometer seemed the closest but didn't seem to really tell the story. I also saw the burn down chart but i couldn't figure out how to use it for my needs.
 
Do the two areas need to be compared to each other? If, I'd either do a) seperate graphs, b) a dynamic graph, or c) a panel graph. (http://peltiertech.com/WordPress/easy-two-panel-line-chart-in-excel/)


Since you're dealing with time, we'll want to do either a line or column chart. As the month's aren't directly related, I recommend a clustered column chart. This lets you compare, within a month, the budget with actual the most clearly. Now, the formatting is the tricky part. What I would recommend is actually having 3 series. You have the normal budget series, but the latter 2 series are a set of formulas, called Normal and Exceed. Formulas in these would be something like:

=IF(Actual-Budget<=Budget*5%,Actual,NA())

and

=IF(Actual-Budget>Budget*5%,Actual,NA())

respectively.


So, any values that are within the 5% target are on one series, and can be colored one color, and any values that exceed the target are on a different series and can be formatted red or something.


Now, to get things lined up correctly, we'll actually need to have the Exceed Series on the secondary axis, and position it behind the Normal series. To do this, you may need to add a blank series (since there's 2 series on primary, probably need 2 series on the secondary).

Article about some conditional formatting ideas:

http://peltiertech.com/WordPress/conditional-formatting-of-excel-charts/
 
Hi ,


Another option is shown in the following link :


http://peltiertech.com/Excel/ChartsHowTo/DeviationColumnCharts.html


Narayan
 
You must log in or register to reply here.
Back
Top