help creating budget roll-up from multiple spreadsheets and updatingwith actuals monthly
 

I'm searching for the best solution for our budgeting and monthly
reporting. I have decent excel skills but don't know how to write VBA
and have never used arrays. I have created simple pivot tables. I
don't have any access training.

My reporting is done once annually when we create the unit budget and
then monthly with a comparison to actuals.

At budget time, I receive 156 different spreadsheets with the
budgets. See Exhibit A below. I would just receive the budget
numbers at this time. Currently, I manually type the Project totals
into one master spreadsheet. See Exhibit B below (only budget nos. at
this time) Any ideas on how to copy/merge/other function ??? the
totals of each budget into one spreadsheet. It would be nice too if I
could use these spreadsheets as the basis for my monthly actual
reporting.

Once a month, I pull ytd actual expenses off a data warehouse. These
expenses each have an object code assigned to them. Each object code
rolls up to a different expense categories. I can convert the object
codes using a vlookup table. For example, object codes 235 and 236
both roll-up to the travel expense category.

The download looks like this simplified:

Proj No. Obj Code Posting Date Amount Description Fund
23 235 1/23/08 100
Nittany Lion travel

Here's my dilemma, how can I "automate" preparing a report that will
look like Exhibit A below? I would need 156 of these.

Exhibit A
XYZ Project
Project No. 23

Expense 0708 Budget 0708 Actual Variance
Travel 500 150
Books 200 50
Repairs 400 100
Rents 600 200

Total 1700 500

I then have to create a monthly report that totals all of these by
project numbers. The report looks like this:


Exhibit B
Project Name 0708 Budget 0708 Actual Variance

22 ABC 3000 500
23 XYZ 1700 500
24 DEF 2000 500

Total 6700 1500

This one is pretty easy because I can subtotal the download I get from
the data warehouse by project no. and do a vlookup on the project
number. It's the individual reporting by expense category and
comparing to budget that I'm having trouble with.


I hope this makes sense and I apologize for the length. Thanks for
any help.