Extrapolating data using excell

Aloreth · 24 Jun 2011 at 14:26

Hi guys,

I am managing a team of people (anywhere from 5 to 25 at a time) over the next year or so and one of the things that I need to do monitor the budget allocated against actual expense and in turn predict approximately at a given point in time what the ultimate results will be.

Is this something that I can do in excell? I like to think that my knowledge of excel is very good generally. If not what sort of software is useful?

Any help much appreciated

Mike

Aloreth · 27 Jun 2011 at 09:22

I'll simplify my question a little:

Can anyone point me towards some decent guides on how to extrapolate data within excel? even just what formlas to use would be great

hood1971 · 27 Jun 2011 at 10:32

Aloreth said:
I'll simplify my question a little:

Can anyone point me towards some decent guides on how to extrapolate data within excel? even just what formlas to use would be great

For the most part you would be using VLOOKUP, IF and SUMIF to set conditions for calculations. The actual extrapolatations would depend the weighting you have for accounting periods.

It all depends on how your accounting data is stored really. Ours is a sql database, so have have created views and stored procs to do the calcs needed and simply call these from excel in a pivot table.

In the sql database we set our accounting periods in a table, join the actuals and budget with these to get period budget/actuals, Year to Date budget/actuals and then work out Budget remaining. As we are seasonal we weight periods to forecast results.

Participant · 27 Jun 2011 at 11:44

Definitely become familiar with pivot tables and pivot charts if you want to be able to quickly summarise the data and plot budget spend vs. actual spend.

With the chart you can add trend lines and Excel also handles automatic regression etc. if you want to get that deep. Or you can just do a linear plot and forecast future spend to see when budget is likely to be exceeded.

muon · 27 Jun 2011 at 12:05

edscdk · 27 Jun 2011 at 12:07

go and buy excel for dummies its a great book if you dont know excel...

expences and budget? surely you will only need + and - and maybe sum() and a / (divide) and a multiply or two...

maybe I have the wrong picture in my head about what is required.... but all you want to do is add up whats been spent and work out averages so you can see whats likley to be spent...

charts and stuff look nice but I would leave them until you have sorted the actual workings

pivot tables are only going to help you analyse the data you have entered so they are something you can mess about with later..

Participant · 27 Jun 2011 at 12:34

edscdk said:
go and buy excel for dummies its a great book if you dont know excel...

expences and budget? surely you will only need + and - and maybe sum() and a / (divide) and a multiply or two...

maybe I have the wrong picture in my head about what is required.... but all you want to do is add up whats been spent and work out averages so you can see whats likley to be spent...

That's true really. What the OP wants to do is about the easiest thing you can possibly do in Excel, you only really need + and - formulae as a bare minimum.

Aloreth · 27 Jun 2011 at 21:24

Hi guys,

Thanks for the responses, I think perhaps you are underestimating the complexity of the task. The charge out rate for the staff working on the job range from £80 per hour up to £2500 per hour, flexing projected cost depending on hours charged will make a massive difference (from hundreds of thousands of profit to.....well not as much).

Im fairly well versed with all kinds of lookup, sumifs and pivot tables. I guess i hadn't really thought about using them to extrapolate date, will have a crack and come back to you all!

Participant · 28 Jun 2011 at 20:54

Aloreth said:
The charge out rate for the staff working on the job range from £80 per hour up to £2500 per hour, flexing projected cost depending on hours charged will make a massive difference (from hundreds of thousands of profit to.....well not as much).

Im fairly well versed with all kinds of lookup, sumifs and pivot tables. I guess i hadn't really thought about using them to extrapolate date, will have a crack and come back to you all!

Yo dawg, I'd probably do something like this:

I've done a ctrl ` to show the formulae, the months are actually months not numbers. It doesn't matter how many names you have or whether you want to analyse the hours on a daily or monthly basis, same principle applies. The best way for you to extrapolate your data would be to estimate the hours and fees for each person under your management, far more accurate than any regression formula etc.

The 'sum of variance' cell in the pivot table on the right, will always give you the year to date over or under spend. You could also flex the hourly rates by month, by increasing the size of the variables table then adjusting the VLOOKUPs to compare the monthly hours to the monthly rate.

Also have a read of this: http://academic.pgcc.edu/psc/Excel_booklet.pdf. It applies to an old version of Excel, but the basics remain the same.

Aloreth · 28 Jun 2011 at 22:53

I heard you like spreadsheets in your spreadsheets....wait i cant really do that joke. Looks nice, think i will try something like that