Whiteboarding a Cross Regional Model

[Adamzurick]

All,

I'm beginning to draw up the plans for a budget model I need to build in about a month and am stuck on a rather large piece of it. Hoping to get your best practice guidance as I'm sure many of you have run through similar projects in the past. Ultimately, I will be holding the full model but will need to push out certain pieces of it to the different areas of the business where my analysts will update it and send it back to me.

The model will consist of an Inputs tab for all areas of the business, along with calculation tabs for each of the areas that pull from the Inputs tab.

I'm not great with VBA and know that's one answer. But wondering what you all have done in the past and what you have found to be the easiest way of working through these without a ton of complexity.

Thanks,
Adam

 

[Fazza]

Yes, Adam, VBA can sure add a lot of complexity.

It is great you're aiming for a well structured file with input tabs specific to each business area. Hopefully keep each one well structured - so set up as much as possible with tables of data, like a database. Then, even without VBA, these are easy to update into your budget model when the business areas send you revisions. Little more than a simple copy & paste (maybe values only) from the tab supplied into the budget model worksheet & fields.

Perhaps a revision-controlled [so have a master list rev 001, and when it changes reissue as rev 002, and then rev 003, etc so everyone has the right list] list of master codes would be part of the system too. Such as codes for cost categories, names, whatever the model has to cover. Having consistent codes + names for all users/inputs to standardise the data will assist consolidation/reporting.

Anyway, the basic idea - that maybe is not adding much to your current plan - is to work with tables like a database.

 

[iliace]

I have worked in FP&A for some years, where we collect budget/forecast information from each department / cost center / business unit (whatever you call it) to put together an annual budget.

If you do not have a system to support you - i.e. in the budgets & planning paradigm, an automated forecasting system - I assume you will be relying strictly on Excel to get the job done. This presents several challenges.

Distribution - you will need some mechanism to isolate the different areas. This can be done from your system of record, and where that is not possible, via a macro. The distribution process can be performed in several ways; most common is by saving the specific file in a network location authorized by your IT Security team. It can also be automatically e-mailed.

Documentation - there needs to be some way for the business areas to document, in the event changes are major, why certain forecast items changed and where should there be an offset, if any (e.g. costs going up related to new business deals not considered in the original plan)

Collection - the submissions from each business area will need to be collected somehow. Consider setting up a catch-all e-mail box, with your team as the e-mail address (e.g. Budgets_Planning@yourorg.com).

Processing - set up an Excel-based tracking sheet that lists each individual area you expect a file from. You can then write a macro, to either (1) process all items in your e-mail box, or (2) find file patterns based on department/area name. The macro would then try to find each file associated with the department/area name, save any associated file (if applicable), and check the Received column if the item already exists. This enables you to add tracking metrics, such as "what is the percentage received for SVP", and others.

Reviewing - on the tracking sheet, you need a box that indicates that the submissions received has been reviewed by a member of your department.

Uploading - depending on your system, you will most likely either upload the submitted sheet (e.g. SAP), or export a feed that loads into your Budget system (e.g. PeopleSoft). This should only happen for items on tracking sheet that are marked Received and Reviewed.

Reporting - you need to be able to see what has been loaded, and any variances from Plan and previous Forecast.