Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUM for Budget, Actuals, & Variance YTD columns | Excel Worksheet Functions | |||
monthly budget | Excel Discussion (Misc queries) | |||
how to record macro to roll over monthly data | New Users to Excel | |||
Divide Monthly Sales Budget to Day Budget | Excel Worksheet Functions | |||
landlord monthly rent roll tracking? | Setting up and Configuration of Excel |