help creating budget roll-up from multiple spreadsheets and updating with 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.
0
mrd18 (1)
1/28/2008 5:36:22 PM
excel.newusers 15348 articles. 2 followers. Follow

0 Replies
303 Views

Similar Articles

[PageSpeed] 56

Reply:

Similar Artilces:

Missing Measures in .cub file created in Excel
After using Excel's Offline OLAP features which create a local cube file (.cub), many of the desired measures which I included in the wizard setup are not displayed. I'm looking for a reason for this. The data populating this cube file is from a virtual cube, so background calculations take place. I considered that those measures requiring calculation would not be included once seperated from the original virtual cube... but many calculated measures WERE included so I don't think that's the complete explination. I've varied the measures included in the .cub file...

SDI with multiple views using the wizard
Hi all, I would like to build a Doc/View SDI Visual C++6 project using multiple views as (CFrameWnd) for the same document. In some documentation is mentioned just MDI support multiple views. Could someone please explain the steps for doing that, using the wizard? Thank you, Chris ...

How to create a report based on multiple queries
Hi all, I am trying to create a report based on 30 queries . These 30 queries are based on one table. When I use the Report wizard and select more than one query I get the message " You have chosen fields from record source which the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so try choosing fields from only tha table or only the query". I do not know what to do.? Any guidance will help thanks, Dave On Wed, 29 Aug 2007 08:21:02 -0700, davidstevans@gmail.com wrote: >Hi all, > >I am trying to create a rep...

Multiple Sections of a Circle Shape/text box to follow circle patt
Help! 2 part question, and I've been to Help, Google, and MVP blogs for hours and can't find an answer. As a Visio 11 Standard (2003?) user newbie, I'm having trouble resizing and aligning the 'partial' shapes of a circle, when attempting to make a circle with more than 4 sections. (Partial circles work well when you have four distinct sections, as the rotate/flip function is adequate.) My frustration is mounting, as I'm dealing with these sections as part of an 'onion' template of concentric circles, and I've now got sections for each circle all...

multiple reminders for one calendar event?
I would like to schedule a reminder about any work appointment, and then have a 2nd reminder for 45 days from the appt. to remind me when a payment check is available. I am using Outlook 2002, but will upgrade to 2003 in the near future. Is this possible in either version?Thanks, David ...

Multiple lowercase b's
As i am working with my spreadsheet multiple lower case b's are appearing as i highlight a cell or box a group of cells. Working with Win XP Pro and Excel 2003 What happens if you change the font to black? I have no idea why you'd see a bunch of lower-case b's. :) ************ Anne Troy www.OfficeArticles.com "alchemist" <alchemist@discussions.microsoft.com> wrote in message news:44E8D628-B5FE-40E1-864B-B1D8EDAAD6A8@microsoft.com... > As i am working with my spreadsheet multiple lower case b's are appearing > as > i highlight a cell or box a grou...

Money 2005
Hello, I use Microsoft Money 2005 now for a while, and everytime I exit the app, it connects and uploads the changes to MSN Money. Is there a way to disable this setting? I would like to delete all of my financial data on MSN Money and not have MS Money upload it there again. If there is a way to stop it, please let me know. Thanks, Brett No problem: Tools: Settings: Access your Money data on the Web. Then you'll be able to disable it. -- "Brett" <baisley@h o t ma i .com> wrote in message news:O9vVj9ZqFHA.1336@TK2MSFTNGP11.phx.gbl... > Hello, > >...

How do I create an area and line combination chart?
I want tocreate a combination chart, using the chart types area and line, it is not listed as a custom type in Excel - is it possible? Hi, Ignore the built-in combinations and create your own. Start with creating a Line chart with your data. Then select the line series that you want as an area chart. Right click and pick Chart Type to select the required Area chart type. Cheers Andy Nuala wrote: > I want tocreate a combination chart, using the chart types area and line, it > is not listed as a custom type in Excel - is it possible? -- Andy Pope, Microsoft MVP - Excel http://ww...

Month display in Microsoft Outlook 2003's Calendar
When using Microsoft Outlook 2003's Calendar in Month view, Saturday and Sunday are combined as one square (yes, they do have the date numbers and a line between them). This may be convenient for people using Outlook only for business purposes, but if someone were to have a job that involved weekends, or was not using Outlook for business purposes at all (which is my case), this could be extremely annoying. I would like to know if there is a way to display the Month view as the standard Sunday on the left and Saturday on the right, with Monday through Friday in between. I want all d...

emails being lost
Hi we run an SBS2003 server with exchange. We also have GFI Mail essentials One student is sending us an email with an attachments. I can see that GFI are receiving the email and processing it OK if I then look at Exchange Tools > Message Tracking Centre, I can see the email. there are 5 entries: SMTP: Message submitted to advanced queue SMTP: Started message submission to advanced queue SMTP: Message submitted to catagorizer SMTP: Message catagorized and queued for routing SMTP: Non-delivery report generated. Can some tell me what is happening please? This student can send messag...

"create mscrm.sql" error on 3rd (re-)install
I have installed CRM 1.2 on SBS2K3 twice as "advetureworks cycle" and "msdn subscriber" with only 90% success because of conflicts on the web site. I have removed the conflicts but now CRM installer errors trying to create the new database as (default) local service. My login has SQLServer db_creator permissions and I can create a DB, so I'm totally confused. Can I workaround this somehow ? What login needs to be given db_creator rights so Install can run te SQL scripts ? Hi Marcus, have you deleate the complete the old date from AWC? Have you delete the ...

Multiple Conditions Formula Help Needed
I currently have a formula that I have used to calculate commisio rates. We had a 2 rate plan. If sales were below a certail threshol then apply this rate and if above apply this rate. I used an IF/THE formula. We have recently switched to a 4 rate plan, and I am unsur how to create a formula to reflect this. Please advise.. thanks dav -- Message posted from http://www.ExcelForum.com One approach would be a table to use with VLOOKUP. In the formula, where you now have a rate, use VLOOKUP. On Wed, 21 Jul 2004 16:53:11 -0500, chiwavdg <<chiwavdg.19rkim@excelforum-nospam.com>...

Help with linking information from one sheet to another
I'm trying to setup an excel workbook like this: First sheet is a master list with peoples names in the first row and and their stats in 5-6 cataogries along with a final point total in the colums to the left of each name. Then I want to use a second sheet so that when I type in the first couple of letters in a persons name it will finish the name for me and automatically copy the players stats and total into the colums on the left. I know it's possible to do since I've seen it before I just have no clue how they did it and was hoping someone hear could help me. Thanks in advan...

How do I update Excel 2000 macros to work in Excel 2002?
Working in Excel 2002/XP on an Excel worksheet which was created in Excel 2000, I get an error message "Subscript out of range". How do I update the macro to work in Excel 2002? Hi normally the macro should work without a problem. Best to post the part of your code which generates this error -- Regards Frank Kabel Frankfurt, Germany "BobPetrich" <BobPetrich@discussions.microsoft.com> schrieb im Newsbeitrag news:9CF34907-0A45-4392-8F04-556EFB1F89E4@microsoft.com... > Working in Excel 2002/XP on an Excel worksheet which was created in Excel > 2000, I get a...

Budget account problems with transfers
Transfers out of budget accounts are distorting my budget reports, yet I can't add the relevant accounts (e.g account created for a loan to my business) to the list of budget accounts. Using Money 2005 UK. Thanks for help! ...

How do I create Folders/Sub folders from User Form control button
I have a User Form that I would like to take one of the spare control buttons and have it set up my job folder on the hard drive. I am not sure how to code it to achive the following results: These are my text box ID's Main Folder --------------------------------------------------------------------- User Selects drive: \ CES_No_1 \ CLLI_Code_1\ TEO_No_1 Sub Folders inside Main Folder ---------------------------------------------------------------------- Completed Drawings Elec Job Folder Misc Job Documents Site Pictures If you can dig up a copy of the VBS doc...

help #9
???? There... all better now? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Having searched the archives, hygdd@eedd2.com <hygdd@eedd2.com> typed: | ???? This is a multi-part message in MIME format. ------=_NextPart_000_0078_01C35EC3.E4226980 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable with what exactly? I only see a questionmark? -- Please go to my website: The Site Resource Vote For The Site Resource! <hygdd@eedd2.com> wrote in message = news:a34bjvk9fvkgdb...

Need help plz! Unable to Send/Recv emails in OE.
I'm sorry if this is a dumb question but I just set up my Outlook Express and I'm unable to Send/Recv emails which is strange because I already have an msn.com email account which works perfectly but I wish to make OE my default email and that doesn't work. Whenever I try to send an email or press the Send/Recv button I keep getting these two error messages: 1: "The service is currently unavailable. Try again later. Account: 'MSN Mail Server', Server: 'http://services.msn.com/svcs/hotmail/httpmail.asp', Protocol: HTTPMail, Server Response: 'Service Unavail...

Forward generic email to multiple accounts?
Hi! We have several "generic" email accounts; "info@xyz.com", "sales@xyz.com", etc. We need to make sure several different individuals receive any email sent to these accounts. The forwarding feature in the user account properties only permits one address as a forwarded recipient. Permitting users to "also check these mailboxes" in Outlook seems to direct the email to whomever checks the generic mailbox first. How do we get mail the generic mail to several people simutaneously?? Thanks! create a mailing list and forward to that list "S...

how do I print multiple print areas on one page
I have a wide worksheet that is not very deep i.e. lots of columns but not many rows. I have it in landscape but it is still too wide to go on one page. I would like to be able to print the right hand side of the worksheet underneath the left hand side of the worksheet, on one page, rather than having to print out two pages. Is this possible? This is a common question. The only way to do this is to create what you want printed. In other words, copy all of your data to a blank sheet, then cut/paste to get what you want, then print it. You can do this with a macro and nev...

Publisher Template page??? help
Okay I'm working in Publisher 2003 with a document that somebody else created. Whenever I go to "Insert page... " (by right clicking on the pages tabs at the bottom) and everytime I do I get a "Newsletter Pages" window asking me what kind of Page I want to insert. I have the options of selecting story, order form calender, etc and each option comes up with a different layout for the page. I can tell they are custom because each page has information specific to this company. My question is how to I modify (edit) these pages? They seem to be templates but only fo...

Month function
Hello, I have Excel 97 with the first service pack at home. I don't have an internet connection at home, so can't install the second service pack. Now that said... In A1 I have: 10/8/04 in A2 I have this: =MONTH(A1) A2 actually says "January" without the quotes. No matter what date I put in A1, A2 always says January. Is there any way at all to fix this? TIA!!! Cindy Hi is your value in A1 really a date value? -- Regards Frank Kabel Frankfurt, Germany CindyH wrote: > Hello, > > I have Excel 97 with the first service pack at home. I don't have an > ...

Stop Chart Style (Format) Update
Hi TWIMC, I'm using Excel 2000 and I have a pivot table that aggregates data based on REGION, COUNTRY, ZONE etc etc. Once I have set up the chart they way that I would like it to look, if I then make a change to the selection using the pivot chart buttons, e.g. I select a country and the chart re-formats itself back to what appears to be the default style. I've set my chart style to the default but it still doesn't keep my chart style. What I want is that the chart style remains the same but the new values are just plotted onto the chart. I'm sure there is an option to turn ...

multiple splits
I have a spreadsheet that has several different headers, and I would like to have each individual section have a set location, then when a section is choosen I can scroll within that section w/o affecting any of the other sections. Thanx jo3l jo31 I think the closest you can come is to use Window>New Window>Arrange. Gord Dibben Excel MVP - XL97 SR2 & XL2002 On Thu, 10 Jul 2003 12:51:20 -0700, "jo3l" <joel@summitintegrated.com> wrote: >I have a spreadsheet that has several different headers, >and I would like to have each individual section have a &g...

Help #18
Hi again I am using a 4 x 4 table ( A1:D4), concatenating 8 inputs ( A1 to H1) so say I concatenate A1 with B1, all the way to H1, anyway the result in the tables will be similar to llb, lbb, llbb, b etc. Now my problem is that each of these results must be converted to usable name eg...llb = Purple, lbb = Pink, llbb = Silver etc. I need to display these colour results in say A10:A26, if there are cells in the results table showing llbb then it must display: 12.5% Silver 25.0% Purple etc Thanks in advance Colli -- Message posted from http://www.ExcelForum.com Cannot understand your ...