Financial Modelling with Access - is the following possible?

[bo8482]

Dear all

I have a general question surrounding the feasibility of the project I am about to undertake. Hoping to save myself a lot of grief in case Access is not the tool for the job!!

Basically, I have a very large financial model in Excel that currently has about 1,000 different inputs (costs, performance variables, time variables, region variables etc). It works well at the moment in that all these inputs are collated, calcs are done, and a P&L and Balance Sheet comes out the other end.

My issue now is that their are 9 additional regions which need to be accounted for in the Excel model (i.e another 9 sets of inputs!). What I was hoping to do was to collate all the inputs in an Access database, and then set up a tab in the Excel model which pulls the inputs from the database and produces a P&L/BS accordingly in Excel. I was also hoping to perform sensitivity analysis in line with a number of different scenarios. This would save me having 9 different models and keeping tabs on what changes needed to be made across all regions.

My question is this: is the above possible/feasible? Is this the best way of going about it? What would be the best table structure for this (I was thinking a table for regions, and a table for each group of inputs, and then run a query which was linked to excel to drive region specific calculations).

I hope some of that made sense - any help or guidance on the above would be much appreciated!

Many thanks
Brian

 

[MarkK]

This is the critical, and non-trivial, question:

What would be the best table structure for this

There's no question that Access can handle it.
IMO

 

[bo8482]

Hmmmm yes I think it should work in theory just needed a little bit of confirmation that I was going down the right route as I have never linked Access -> Excel before! I think I will plough ahead making tables for each of the subset of inputs and then see where I get to with exporting to Excel....I envisage I will be doing a lot of searching/posting on the forums in the next few days!!

Cheers
Brian

 

[gemma-the-husky]

eminently feasible - but I am sure you will find that your data is not presented in the best way for access to do this.

eg, no doubt you have columns in your spreadsheet for each month, and a year total. This just isnt the way you would want to represent this data in Access (or any DBS) - so you need to deconstruct your data, to get an access implementation.

This just wont be a 5 minute job.

if you are happy in excel, you could just write excel vba code to handle/automate all the processing. If you want to move to access, its probably a fair amount of work.

 

[bo8482]

Hi Gemma
Yes it does appear feasible and you are right, it has been time consuming translating my inputs into Access.

I now have a problem in that I'm stuck as to how to get Excel and Access to interact, as I'm encountering 'another user has the database open' errors when trying to refresh my data in Excel when Access is open.

What I'm trying to do is this:
I have a form in Access which drives a Make Table query which provides me with a table called tbl_Scenario. In Excel, I then link my inputs to tbl_Scenario in Access. This works fine if Access is closed, but given that Access is open, the data won't refresh without an error message saying that the database is open.

Ideally, I need to be able to flip scenarios in Access easily to see the effect on the numbers in Excel (i.e. keep Access open and run the Make Table query for various scenarios). Do you know if this is possible, or do I have to close the database down each time??

Any help you can provide is gratefully received!!

Many thanks
Brian

 

[bo8482]

OK I managed to get this sussed - here is the solution for anyone with a similar problem:

Open the database in Access first.

Then open your spreadsheet you want to import the data into.
To create this link in Excel, click the Data Tab, then click the From Access button.
Select the database you are linking to.
The Data Link Properties window should now be open.
If you click the Test Connection button you should get the error message you mention.
Click the Advanced Tab.
In the Access permissions list, untick all permissions.
Tick only the Read permission.
Go back to the Connection Tab & click Test Connection again.
It should work now.