I am a finance professional with some database experience (solid
Access and some SQL Server experience). I need some help/ advice
about a database I am thinking about creating. My company uses a
fairly complex Excel worksheet for new product investments that
calculate a 5-year cash flow, P&L, IRR, NPV and a few other
calculations. I have been asked to look at the worksheet, which has
not been changed much since its creation about 5-6 years ago, and
identify improvements that can be made. The mains issues with it as
you might imagine include the following:

-Difficult to control universal assumptions. Assumptions that are
baked into the worksheet often end up being different between
worksheets do to when the worksheet was created and who created it.
Among other issues that this causes it makes it difficult to filter
changes to these assumptions to all of the worksheets.
-Can not report on any of the data. It would be useful I believe to
be able to say run a report that gives all of the open projects sorted
by their IRR. This can not be done now.
-Different people contribute to the worksheet so control over
ownership and version is difficult.
-Numerous calculations in the worksheet make it slow.

Based on what I know I think this should absolutely be something that
should reside in a database. My questions to this group (please let
me know if this audience is not appropriate for this question) are:

-Do you agree a database is the right place for this, based on what I
have described?
-Since there are so many calculations what should the database look
like? My understanding is that putting calculations into a database
table is not good practice. The problem is the calculations are so
numerous (which is why I am questioning whether this should be in a
database) that running reporting for say IRR take a very long time.
Should I include no, all or some summary tables?
-What is the best way to handle all of the calculations?

Thanks in advance for any help you can give.

-Scott

On 2009-01-19, sweez <SSweez (AT) gmail (DOT) com> wrote:
(1)
(2)
hey! (1) seems to conflict with (2)

verb "filter" conflicts with verb "to" (to all of the worksheets)

do you mean filter-from or propogate-to

database can solve either of those, not both.

too soon to call: your description needs work.

how many degrees of freedom are there?
fully normalised you would have that many columns and do everything
else with stored procedures. This may be slower than the spreadsheets
and may not it depends, largely on how well the procedures are written.

Scott,

I can just offer some general thoughts here:

Excel is handy when you have one user only, but on the other hand you
could put the Excel sheet on a central file share and make it
available for multi user input - Extras -> release worksheet in order
to maintain one copy only.

If that does not fit I would search the web for some ready made
software packages which do what you need.

If this also fails, then well, you might want to look at creating your
own one. Access is easy for doing something quick and not too complex.
The multi user support is not spectacular, neither are complex
queries. Using one of the bigger products might be a better choice,
but also means a lot of work. You would have a backend database, for
example sql server where you could include calculations into VIEWs and
then doing the rest by a front end reporting tool (e. g. ms report
viewer / crystal reports). Microsoft has some financial samples here:

http://www.microsoft.com/downloads/d...displaylang=en

You would also have to create a front end executable or web interface
in order to let the users make the input.

Good luck!

brgds

Philipp Post