Skip to content

ggplot2: Budget vs Actual Performance

April 23, 2009

A reader of a Pointy Haired Dilbert blog enquired about best ways to visualise budget vs. actual performance. In response PHD challenged his blog readers to contribute their visualisations made using Excel or Google Docs spreadsheets, and later picked the winner.

After reviewing the submissions I decided to redraw the two I liked most in ggplot2.



Data Preparation, and First Look at the Data

As always, the process begins with importing the data and adjusting the layout for plotting. First the csv datafile is imported, and converted into a long format. During the import the date information is deliberately omitted and will be added manually by first generating the quarterly sequence using zoo, and then repeating it 12 times. The variables renamed during the data import to avoid duplication (Actual, Actual.1, etc) are reverted back to their original format.

> library(ggplot2)
> library(zoo)
> df <- read.csv("pub.txt", skip = 1)
> df$X <- NULL
> dfm <- melt(df)
> qtr <- as.yearqtr(seq(2008, by = 0.25, length.out = 5),
     )
> dfm$qtr <- rep(qtr, each = 12)
> dfm$variable <- substr(dfm$variable, 1, 6)

This data format allows to have the first look at the results

> ggplot(dfm, aes(factor(qtr), value, fill = variable,
     colour = variable)) + facet_wrap(~Center,
     ncol = 3) + geom_bar(position = "dodge",
     stat = "identity")
https://learnr.files.wordpress.com/2009/04/budget_actual_default.png

However, as the main goal is to explore actual vs budgeted performance and this involves combining different chart types, it would be best if the actual and budget were separate variables. Also, the variances are calculated.

> dfm2 <- cast(dfm, Center + qtr ~ variable)
> dfm2$BuAc <- with(dfm2, Budget - Actual)
> dfm2$BuAcPc <- with(dfm2, (Budget/Actual -
     1) * 100)

The data now looks like this:

> head(dfm2)
    Center     qtr Actual Budget BuAc    BuAcPc
1 Center 1 2008.00   1200   1347  147 12.250000
2 Center 1 2008.25   1980   2132  152  7.676768
3 Center 1 2008.50   1976   2040   64  3.238866
4 Center 1 2008.75   1618   1770  152  9.394314
5 Center 1 2009.00   2938   3090  152  5.173587
6 Center 2 2008.00   2300   2738  438 19.043478

Contest Entry # 6

https://learnr.files.wordpress.com/2009/04/budget-vs-actual-mb1.png

I liked entry #6 for its simplicity and concise presentation of information. In the ggplot2 plot I will add the both the absolute and percentage variance.

> p1 <- ggplot(dfm2, aes(Center))
> p1 + geom_bar(aes(y = Actual), fill = "grey85") +
     geom_errorbar(aes(ymin = Budget, ymax = Budget),
         size = 1) + geom_text(aes(y = 1, label = paste(BuAc,
     " (", percent(BuAcPc/100), ")", sep = "")),
     vjust = -0.5, size = 3.5) + facet_grid(qtr ~
     .)
https://learnr.files.wordpress.com/2009/04/budget_actual_simple.png

As you can see, default formatting needs to be tweaked a little to eliminate the grey background, adjust the facet labels and remove redundant axes titles. Facet labels format is specified using by first turning the qtr variable into a factor and then making use of the factor’s labels property.

> dfm2$qtr <- factor(dfm2$qtr)
> flabels <- format.yearqtr(as.numeric(levels(dfm2$qtr)),
     "Q%q%y")
> dfm2$qtr <- factor(dfm2$qtr, labels = flabels)
> no_axes_titles <- opts(axis.title.x = theme_blank(),
     axis.title.y = theme_blank())
> last_plot() %+% dfm2 + theme_bw() + no_axes_titles
https://learnr.files.wordpress.com/2009/04/budget_actual_simple_by_period.png

Should one wish to compare performance across cost centres, the same code can be used after swapping the qtr and Center variables.

> p2 <- ggplot(dfm2, aes(qtr))
> p2 + theme_bw() + no_axes_titles + geom_bar(aes(y = Actual),
     fill = "grey85") + geom_errorbar(aes(ymin = Budget,
     ymax = Budget), size = 1) + geom_text(aes(y = 1,
     label = paste(BuAc, " (", percent(BuAcPc/100),
         ")", sep = "")), vjust = -0.5, size = 3.5) +
     facet_grid(Center ~ .)
https://learnr.files.wordpress.com/2009/04/budget_actual_simple_by_centre.png

Contest Entry # 3

https://learnr.files.wordpress.com/2009/04/budget-vs-actual-lee1.png

I also liked entry #3. Agreeing with PHD recommendations I changed the variance calculation method.

A few additional changes to the data are needed. The as.data.frame.cast_df function strips off cast related attributes so data frame becomes a normal data frame that can be melted again. After melting the data again into a long format, an additional variable is added to the data indicating which variables are to be plotted on the same graph.

> dfm3 <- as.data.frame.cast_df(dfm2)
> dfm3 <- melt(dfm3, id.vars = 1:2)
> dfm3$variable2 <- combine_factor(dfm3$variable,
     c(1, 1, 2, 3))

If the data is laid out properly, plotting is not complicated at all.

> p3 <- ggplot(dfm3, aes(factor(qtr), value))
> p3 + geom_line(aes(group = variable, colour = variable),
     size = 1) + facet_grid(variable2 ~ Center,
     scales = "free_y")
https://learnr.files.wordpress.com/2009/04/budget_actual_creative.png

Again, little formatting tweaks to adjust the colours and x-axis labels.

> last_plot() + theme_bw() + scale_colour_brewer(palette = "Set1") +
     scale_x_discrete(labels = gsub("(Q[1-4])([0-9]{2})",
         "\\1\n\\2", levels(dfm3$qtr))) + opts(legend.position = "none") +
     no_axes_titles
https://learnr.files.wordpress.com/2009/04/budget_actual_creative1.png
4 Comments leave one →
  1. April 26, 2009 10:13 pm

    Hi…

    A very good attempt I must say. I dont know r about R. But I guess given my passion for visualization and excel, I would endup learning R. Thanks for sharing your ideas with the world.

  2. DavidH permalink
    October 2, 2009 7:30 am

    Odd that none of the budget – actual numbers are negative!

    • learnr permalink*
      October 4, 2009 9:37 am

      Well spotted!
      Obviously this is not real data – otherwise there would certainly be some negative numbers as well.

Trackbacks

  1. Fix Geom_errorbar Position Dodge Windows XP, Vista, 7, 8 [Solved]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: