Budget v Spend Pivot Table - can this be automated?

Hello

My mission is this….to create a Budget v Actual Expenditure Pivot Table, which sounds simple enough.

Some background info on what I am trying to achieve is – I work for a commercial estate agent’s and for most properties which we are marketing, we have a marketing budget, which is currently held in hard copy form only. I want to be able to see quickly which properties say have a budget for advertising, which again is fairly simple (and I have no problem creating a Pivot Table for this).

On the flip side of this, we currently keep a record of any activity which we spend on a property (kept in one Excel Sheet) and again, from the data/experience I have I can pull the relevant information out of this worksheet and create a separate pivot table showing expenditure for a property.

The bit where is starts getting slightly more difficult for me is I really want to see the Budget v Spend (and calculate what the remaining budget is) on one Pivot Table. I have been able to accomplish this in a round about way but to me it is kind of clunky and there is a lack of automation.

I’ve attached the workbook showing what I’ve achieved so far. The activity worksheet shows all current expenditure for every property. From that I have pulled the relevant data out & dumped into the worksheet named Spend_Data (each time I add a new line to Activity, I will have to copy data over to Spend_Data). In Merged Data, I have added my Budget data and also copied across the Spend_Data table (each time a line is added to Spend_Data I will need to manually copy & paste into Merged Data – if these two can be automated…great!). As and when new properties are bought onto the market, I will manually input their budgets into Merged Data.

From Merged Data, I have then created a Pivot Table which calculates what I have spent across various categories and sub-categories and shows how much budget I have left to spend…Great. But to me it feels clunky and I’m sure someone out there can point me in the right direction to make it easier.

The other thing I would like to show on the Pivot Table is to conditionally format the property name, red if the budget hasn’t been approved or colour the property cell yellow (only an example) if the Budget has expired or is due to expired within 30 days.

I have created this Workbook in Excel 2011 for MAC but we use Excel 2003 in the office. Therefore ideally it needs to be compatible with 2003…..worse case scenario, I only view/use on my MAC!

Any help/pointers/opinions that anyone can give would be hugely appreciated.

Many thanks in advance.
[/SIZE]

I am not so convinced a pivot is the way to go here, maybe, maybe not. I think I would rather a couple of simple tables and charts, dashboard style.

I am assuming you have VBA on the Mac? Is that okay in your offic?

The reason that i've gone for a Pivot Table rather than a chart is so that I can drill down through the info & see exactly how much i've spent on Advertising, Mailings etc, etc. I can also see (if needed), what date/month the spend was. Also if needed, I can filter this table so i can show the info for one particular property, should i need to. My feeling was that a pivot table was more versatile than a chart. Also I need to compare the spend v budget side by side so to speak. Having the info in table format isn't as easy to see what i need to see.

Yes, I have VBA on the MAC. Can't recall in the office but if this info just needs to be on my MAC (personal laptop) then so be it.

Thanks for your help.

Wouldn't something like this work for you. I need to add the bufget amount (where is the amount BTW), and automate the update, but that is doable.

The Budget amount is on the Merged Data sheet - each property will have budgets associated with various categories and sub-categories. Whilst I can appreciate what you were trying to achieve on your sample, i believe it wouldn't be as easy to read/work with as a pivot table. With the pivot table I have created, i can see all properties at a glance and the amount i've spent against the budget as well as the remaining amount of budget for the property as a whole and each category. There are a number of times when i need to see this information at a glance for all properties i.e. when I need to find how many properties have budgets remaining for say advertising in Business Magazine. I then would need to know whether the budget is a) approved and b) current. Hope that makes sense.

Another point to note is that i may have spent money on a property but not have a budget set for it.

I just find the pivot you have far too busy that nothing comes out. All the points you make are covered in my example, aside from the unbudgetted spend (all but 1 have no budget that I can see0, which is easily fixable, and the budget amount - ditto. See this image

Bob, sorry but my workbook does not have all the data inputted yet as i was only testing the theory. However in my sample, there are a number of properties listed that do not have a budget (£0.00 does not = unbudgetted, it represents the fact that the client would not incur a charge for that particular item which we have carried out. In my sample data, there are only 3 properties that I have inputted budgets for but a number of other properties that we have spent money on).

I guess the pivot table works for me as I know exactly what it represents & believe me, my colleagues will also be able to work it out too without changing any data. I just need to find a way to get any additional lines added to the Activity worksheet into the Spend_Data worksheet & then automatically transfer any new data on the Spend_Data worksheet to the Merged_Data worksheet.

The end result of the Pivot Table is what I need, I just need to find a way to get all the information i require into the worksheet to base the Pivot Table off.