Access to Excel: Financial Forecasting Tool

[BLUE_CHIP]

Hello,



I work for a company that produces a perishable food product that requires time to mature before sale. As such, we are required to have a detailed sales forecast mechanism in order to meet our demand requirements ahead of despatch time and to allow for maturing and so as not to product excess product.

Our current system uses a series of excel spreadsheets in order to deal with the data and financial and production calculations:

Data Sets:

Customer Listings

Sales Actual [weekly] (Sales to date)

Sales Forecast [monthly: up to 5 years hence]

Production [daily] (actual product produced)

Costs (Financial)

Calculated data:

Profit and Loss Actual

Profit and Loss Forecast

Production Forecast

Budgeting

Because the product is perishable and requires time to mature the calculated data sources it's data from multiple data sets, including calculated data sets. This means there exists a dense bundle of cross dependencies between data sets.

The problem arises in that in order to maintain this financial tool the data must be updated frequently by multiple people who have had no part in the design of the spreadsheet. This leads to a high number of instances of data inconsistency and formulae corruption when attempts are made to expand the system, i.e. to incorporate new data or increase it's temporal scope. Due to the complex nature of the data relationships it becomes very difficult to identify the source of the errors.

So I come to my question:

Is it possible to set up a system that incorporates Access as a robust front end, containing the data sets, data entry forms and queries, and Excel as a calculational tool which sources it's data from the access database on a real time or semi-real time basis.

This, if possible, would allow the people to update and query all the data without directly modifying the Excel spreadsheet, thus increasing data integrity.

I understand that in principal this is possible however my reservations lie with the complexity of in calculations involved in Excel back end.

Any advice would be much appreciated.

[SoftwareMatters]

I think what you're asking is possible but it would be a big job and I would consider if you are going down the Access route why not do it all in Access.

Your best bet is to be speak to an expert and talk through what you have and what you need in order to give you a ballpark figure. Then it's a question of cost over requirement.

[evander]

It appears you want a complete general ledger system (or accounting software). You can do everything in Microsoft Access (this is the kind of job I have right now, actually).

You can actually link Excel to Access using the data commands available, such as PivotTable.

In my experience, your project can get completed in 6 months by one person, that is, if you're starting from scratch and you allow some slack time. It may even be shorter since you already have spreadsheets you can model the system from.

Good luck.