Financial modelling from Excel to Access
 

Hi there,

I'm working for a manufacturing company in FMCG industry. One of my
jobs is to build and maintain an activity based financial model in
Excel which simulates the whole group's financial performance based on
current/projected sales & manufacturing conditions. The model can
enable user to drill down to SKU level to analyse the profitability.
Because I don't have access to our ERP system's database, I have to
replicate all master data I need in various tables in the model
workbook. That means the model has its build-in database in Excel
worksheets. The model is 100% dynamic which means the workbook is full
of formulae and any change in one area will ripple through the whole
modle and impact the bottom line. The model works fine so far to
simulate the performance down to contribution level (meaning
calculation of profit after taking account of promotion, warehousing &
distribution costs). Guess what my model looks like now - has more than
1 million formulae and more than 100MB in size and take more than 30
minutes to do a full-scale recalculation (if successful at all)! But my
company wants me progress to EBIT level, which means I need take in more
& more data and build more formula and store more data. I know I've
reached the critical point beyond which will be out of Excel's
capacity. I remember I saw an article on Internet about the superiosity
of Access to Excel regarding financial modelling. But I just could find
it now by all means.

My questions: could anyone show me how to do modelling in Access? I
mean those dynamic things in Access as in Excel. Can I have a sample
Access DB for financial modelling? Or is there any other software that
can handle industrial level modelling like mine?

Any comment is welcome.

Cheers

Huyeote


--
Huyeote
------------------------------------------------------------------------
Huyeote's Profile: http://www.excelforum.com/member.php...o&userid=13894
View this thread: http://www.excelforum.com/showthread...hreadid=473254

Hi Huyeote,

A Database can handle efficiently a lot more rows than Excel, but it is
difficult to do calculations on more than one row at a time, and cumbersome
to do some time-phased calculations.
I would suggest you consider an approach of storing the data in a database
like access, and doing the calculations in Excel based on subsets of the
data retrieved from/stored back the database.
Alternatively you could reprogram the calculations in visual basic working
on tables in the database,

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"Huyeote" wrote in
message ...

Hi there,

I'm working for a manufacturing company in FMCG industry. One of my
jobs is to build and maintain an activity based financial model in
Excel which simulates the whole group's financial performance based on
current/projected sales & manufacturing conditions. The model can
enable user to drill down to SKU level to analyse the profitability.
Because I don't have access to our ERP system's database, I have to
replicate all master data I need in various tables in the model
workbook. That means the model has its build-in database in Excel
worksheets. The model is 100% dynamic which means the workbook is full
of formulae and any change in one area will ripple through the whole
modle and impact the bottom line. The model works fine so far to
simulate the performance down to contribution level (meaning
calculation of profit after taking account of promotion, warehousing &
distribution costs). Guess what my model looks like now - has more than
1 million formulae and more than 100MB in size and take more than 30
minutes to do a full-scale recalculation (if successful at all)! But my
company wants me progress to EBIT level, which means I need take in more
& more data and build more formula and store more data. I know I've
reached the critical point beyond which will be out of Excel's
capacity. I remember I saw an article on Internet about the superiosity
of Access to Excel regarding financial modelling. But I just could find
it now by all means.

My questions: could anyone show me how to do modelling in Access? I
mean those dynamic things in Access as in Excel. Can I have a sample
Access DB for financial modelling? Or is there any other software that
can handle industrial level modelling like mine?

Any comment is welcome.

Cheers

Huyeote


--
Huyeote
------------------------------------------------------------------------
Huyeote's Profile:
http://www.excelforum.com/member.php...o&userid=13894
View this thread: http://www.excelforum.com/showthread...hreadid=473254

My questions: could anyone show me how to do modelling in
Access? I mean those dynamic things in Access as in Excel.

For starters, I would suggest that you post this inquiry
in an Access or database forum, not an Excel forum.

Or is there any other software that
can handle industrial level modelling like mine?

That would be my suggestion: get some software designed
to work with the database of your choice. Off-hand, I do
not have any specific recommendations.

It might be prudent and cost-effective for your company
to hire a consultant to help you research your options.

Eventually, the conversion of the large amount of data
that you describe might be a project in itself. A
consultant might help you estimate that cost, as well.

Financial modelling from Excel to Access
 

Huyeote wrote on 10/04/2005 22:18 ET :
Hi there,

I'm working for a manufacturing company in FMCG industry. One of m
jobs is to build and maintain an activity based financial model i
Excel which simulates the whole group's financial performance based o
current/projected sales & manufacturing conditions. The model ca
enable user to drill down to SKU level to analyse the profitability
Because I don't have access to our ERP system's database, I have t
replicate all master data I need in various tables in the mode
workbook. That means the model has its build-in database in Exce
worksheets. The model is 100% dynamic which means the workbook is ful
of formulae and any change in one area will ripple through the whol
modle and impact the bottom line. The model works fine so far t
simulate the performance down to contribution level (meanin
calculation of profit after taking account of promotion, warehousing
distribution costs). Guess what my model looks like now - has more tha
1 million formulae and more than 100MB in size and take more than 3
minutes to do a full-scale recalculation (if successful at all)! But m
company wants me progress to EBIT level, which means I need take in mor
& more data and build more formula and store more data. I know I'v
reached the critical point beyond which will be out of Excel'
capacity. I remember I saw an article on Internet about the superiosit
of Access to Excel regarding financial modelling. But I just could fin
it now by all means.

My questions: could anyone show me how to do modelling in Access?
mean those dynamic things in Access as in Excel. Can I have a sampl
Access DB for financial modelling? Or is there any other software tha
can handle industrial level modelling like mine?

Any comment is welcome.

Cheers

Huyeot

Huyeot
Huyeote's Profile:
http://www.excelforum.com/member.php...info&userid89
View this thread: http://www.excelforum.com/showthread.php?threadidG325

Its really great that the free seminars and road shows are performed at
Kolkata
and Delhi. All this promotional activities are really going to be helpfull for
financial modeling activities in India. Great post thanks.
http://www.cbacademy.in/courses/